mysql> flush privileges;-> alter user 'root'@'localhost' identified by '!@#123qwe';-> flush privileges;
vim /etc/my.cnf, 刪除 skip-grant-tables systemctl restart mysqld
mysql -u root -p 使用新密碼，再次登錄
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.01 sec)
在 MySQL 中創建 Hive 所需用戶和數據庫，并授權
mysql> create user 'dnn' identified by '!@#123Qwe';
mysql> create database hive;
Query OK,1 row affected (0.02 sec)
mysql> grant all privileges on hive.* to 'dnn'@'localhost' identified by '!@#123Qwe';
mysql> flush privileges;
hive 表創建成功
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || hive || mysql || performance_schema || sys |+--------------------+5 rows in set (0.00 sec)
<configuration><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value><description>JDBC connect string for a JDBC metastore.
ql.jdbc.Driver To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.For example, jdbc:postgresql://myhost/db?ssl=truefor postgres database.</description></property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.cj.jdbc.Driver</value><description>Driver classnamefor a JDBC metastore,com.mysql.jdbc.Driver is depricated</description></property><property><name>javax.jdo.option.ConnectionUserName</name><value>dnn</value><description>Username to use against metastore database</description></property><property><name>javax.jdo.option.ConnectionPassword</name><value>!@#123Qwe</value><description>password to use against metastore database</description></property></configuration>
初始化 Hive Metastore 在 hive 的 bin 目錄下 ./schematool -initSchema -dbType mysql
hive>> describe function upper;
OK
upper(str)- Returns str with all characters changed to uppercase
Time taken:0.007 seconds, Fetched:1row(s)
自定義函數，重寫 UDF 中的 evaluate()
package michael_package;import org.apache.hadoop.hive.ql.exec.UDF;publicclassSubextendsUDF{public Integer evaluate(Integer a, Integer b){if(a==null || b==null)return null;return a-b;}public Double evaluate(Double a, Double b){if(a==null || b==null)return null;return a-b;}public Integer evaluate(Integer a, Integer[] b){if(a==null || b==null)return null;int ans = a;for(int i =0; i < b.length;++i){if(b[i]!= null)ans -= b[i];}return ans;}}
表生成自定義函數 UDTF
聚集自定義函數 UDAF
4.2 Hive Shell
create table，show tables，describe 表name
hive> create table student(>id string,> name string,> sex string,> age tinyint,> dept string)> row format delimited fields terminated by '\t';
OK
Time taken: 0.464 seconds
hive> show tables;
OK
student
Time taken: 0.085 seconds, Fetched: 1 row(s)
hive> describe student;
OK
id string
name string
sex string
age tinyint
dept string
Time taken: 0.069 seconds, Fetched: 5 row(s)
編寫個數據文件
hadoop@dblab-VirtualBox:/usr/local/eclipse$ vim /home/hadoop/workspace/student.txt
hadoop@dblab-VirtualBox:/usr/local/eclipse$ cat /home/hadoop/workspace/student.txt
1 michael male 18 bigdata
2 ming male 19 AI
3 lili female 18 math
4 huahua female 20 AI
加載數據到表格
hive> load data local inpath '/home/hadoop/workspace/student.txt' into table student;
Loading data to table default.student
OK
Time taken: 1.158 seconds
select 操作表
hive>select * from student;
OK
1 michael male 18 bigdata
2 ming male 19 AI
3 lili female 18 math
4 huahua female 20 AI
Time taken: 0.789 seconds, Fetched: 4 row(s)
hive> select * from student where sex='male';
OK
1 michael male 18 bigdata
2 ming male 19 AI
Time taken:1.565 seconds, Fetched:2row(s)
HiveQL 將命令轉換為 MapReduce 操作
hive> select sex,count(*) from student group by sex;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20210331231151_0e76f173-ac54-46e0-8886-f018f8a7f480
Total jobs =1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size:1
In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:set mapreduce.job.reduces=<number>
Job running in-process (local Hadoop)2021-03-3123:11:52,943 Stage-1 map =100%, reduce =100%
Ended Job = job_local236806530_0001
MapReduce Jobs Launched:
Stage-Stage-1: HDFS Read:352 HDFS Write:0 SUCCESS
Total MapReduce CPU Time Spent:0 msec
OK
female 2
male 2
Time taken:1.55 seconds, Fetched:2row(s)
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || hive || mysql || performance_schema || spark || sys |+--------------------+6 rows in set (0.00 sec)mysql> use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;+---------------------------+| Tables_in_hive |+---------------------------+| AUX_TABLE || BUCKETING_COLS || CDS || COLUMNS_V2 || COMPACTION_QUEUE || COMPLETED_COMPACTIONS || COMPLETED_TXN_COMPONENTS || DATABASE_PARAMS || DBS || DB_PRIVS || DELEGATION_TOKENS || FUNCS || FUNC_RU || GLOBAL_PRIVS || HIVE_LOCKS || IDXS || INDEX_PARAMS || KEY_CONSTRAINTS || MASTER_KEYS || NEXT_COMPACTION_QUEUE_ID || NEXT_LOCK_ID || NEXT_TXN_ID || NOTIFICATION_LOG || NOTIFICATION_SEQUENCE || NUCLEUS_TABLES || PARTITIONS || PARTITION_EVENTS || PARTITION_KEYS || PARTITION_KEY_VALS || PARTITION_PARAMS || PART_COL_PRIVS || PART_COL_STATS || PART_PRIVS || ROLES || ROLE_MAP || SDS || SD_PARAMS || SEQUENCE_TABLE || SERDES || SERDE_PARAMS || SKEWED_COL_NAMES || SKEWED_COL_VALUE_LOC_MAP || SKEWED_STRING_LIST || SKEWED_STRING_LIST_VALUES || SKEWED_VALUES || SORT_COLS || TABLE_PARAMS || TAB_COL_STATS || TBLS || TBL_COL_PRIVS || TBL_PRIVS || TXNS || TXN_COMPONENTS || TYPES || TYPE_FIELDS || VERSION || WRITE_SET |+---------------------------+57 rows in set (0.00 sec)
非交互式，加載查詢腳本
hadoop@dblab-VirtualBox:~/workspace$ vim script.q
hadoop@dblab-VirtualBox:~/workspace$ cat script.q
select * from student;hadoop@dblab-VirtualBox:~/workspace$ hive -f script.q
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-2.1.0.jar!/hive-log4j2.properties Async:true
Wed Mar 3123:36:09 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:09 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:09 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:09 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:10 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:10 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:10 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Mar 3123:36:10 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
OK
1 michael male 18 bigdata
2 ming male 19 AI
3 lili female 18 math
4 huahua female 20 AI
Time taken:1.376 seconds, Fetched:4row(s)
-e 選項內嵌語句 hadoop@dblab-VirtualBox:~/workspace$ hive -e 'select * from student'
