本文目錄（本文約六萬五千字）
（一）基本概念、安裝、數據類型
（二）DDL數據定義、DML數據操作
（三）查詢、分區表和分桶表
（四）函數、壓縮和存儲
（五）企業級調優、Hive實戰
（一）基本概念、安裝、數據類型
1 基本概念
1.1 什麼是HIVE
（1）Hive是由 Facebook 開源用於解決海量結構化日誌的數據統計工具；
（3）Hive的本質是將HQL轉化成MapReduce程序。
（4）Hive 處理的數據存儲在HDFS；
（5）Hive 分析數據底層的實現是MapReduce；
（6）執行程序運行在 Yarn 上。
1.2 Hive 的優缺點
優點
（1）操作接口採用類SQL 語法，提供快速開發的能力（簡單、容易上手）。
（2）避免了去寫MapReduce，減少開發人員的學習成本。
（3）Hive 的執行延遲比較高，因此Hive 常用於數據分析，對實時性要求不高的場合。
（4）Hive 優勢在於處理大數據，對於處理小數據沒有優勢，因為Hive 的執行延遲比較
（5）Hive 支持用戶自定義函數，用戶可以根據自己的需求來實現自己的函數。
缺點
（1）Hive 的HQL 表達能力有限，疊代式算法無法表達，數據挖掘方面不擅長，由於MapReduce 數據處理流程的限制，效率更高的算法卻無法實現。
（2）Hive 的效率比較低，Hive 自動生成的MapReduce 作業，通常情況下不夠智能化，Hive 調優比較困難，粒度較粗。
1.3 Hive 架構原理
（1）用戶接口：CLIent
CLI（command-line interface）、JDBC/ODBC(jdbc 訪問hive)、WEBUI（瀏覽器訪問hive）
（2）元數據：Metastore
默認存儲在自帶的derby 資料庫中，推薦使用MySQL 存儲Metastore
（3）Hadoop
使用HDFS 進行存儲，使用MapReduce 進行計算。
（4）驅動器
解析器（SQL Parser）：將SQL 字符串轉換成抽象語法樹AST，這一步一般都用第三方工具庫完成，比如antlr；對AST 進行語法分析，比如表是否存在、欄位是否存在、SQL語義是否有誤。
編譯器（Physical Plan）：將AST 編譯生成邏輯執行計劃。
優化器（Query Optimizer）：對邏輯執行計劃進行優化。
執行器（Execution）：把邏輯執行計劃轉換成可以運行的物理計劃。對於Hive 來說，就是MR/Spark。
Hive 通過給用戶提供的一系列交互接口，接收到用戶的指令(SQL)，使用自己的Driver，結合元數據(MetaStore)，將這些指令翻譯成MapReduce，提交到Hadoop 中執行，最後，將執行返回的結果輸出到用戶交互接口。
1.4 Hive 和資料庫比較
由於 Hive 採用了類似SQL 的查詢語言 HQL(Hive Query Language)，因此很容易將 Hive 理解為資料庫。其實從結構上來看， Hive 和資料庫除了擁有類似的查詢語言，再無類似之處。本文將從多個方面來闡述 Hive 和資料庫的差異。資料庫可以用在 Online 的應用中，但是Hive
是為數據倉庫而設計的，清楚這一點，有助於從應用角度理解 Hive 的特性。
（1）查詢語言
由於 SQL 被廣泛的應用在數據倉庫中，因此，專門針對 Hive 的特性設計了類 SQL 的查詢語言 HQL 。熟悉 SQL 開發的開發者可以很方便的使用 Hive 進行開發。
（2）數據更新
由於 Hive 是針對數據倉庫應用設計的，而數據倉庫的內容是讀多寫少的。 因此， Hive 中不建議對數據的改寫，所有的數據都是在加載的時候確定好的。 而資料庫中的數據通常是需要經常進行修改的，因此可以使用 INSERT INTO …
VALUES 添加數據，使用 UPDATE … SET 修改數據。
（3）執行延遲
Hive 在查詢數據的時候，由於沒有索引，需要掃描整個表，因此延遲較高。另外一個導致 Hive 執行延遲高的因素是 MapReduce 框架。由於 MapReduce 本身具有較高的延遲，因此在利用 MapReduce 執行 Hive 查詢時，也會有較高的延遲。相對的，資料庫的執行延遲較低。當然，這個低是有條件的，即數據規模較小，當數據規模大到超過資料庫的處理能力的時候，Hive
的並行計算顯然能體現出優勢。
（4）數據規模
由於Hive 建立在集群上並可以利用 MapReduce 進行並行計算，因此可以支持很大規模的數據；對應的，資料庫可以支持的數據規模較小。
2 Hive 安裝
2.1 Hive 安裝部署
1. 把 apache-hive-3.1.2-bin.tar.gz上傳到 Linux的 /opt/software目錄下
2. 解壓 apache-hive-3.1.2-bin.tar.gz到 /opt/module/目錄下面
[Tom@hadoop102 software]$ tar -zxvf /opt/software/apache-hive-3.1.2-bin.tar.gz -C /opt/module/
13. 修改 apache-hive-3.1.2-bin.tar.gz的名稱為 hive-3.1.2
[Tom@hadoop102 software]$ mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive-3.1.2
14. 修改 /etc/profile.d/my_env.sh，添加環境變量
[Tom@hadoop102 software]$ sudo vim etc/profile.d/my_env.sh
15. 添加內容
#HIVE_HOME
export HIVE_HOME=/opt/module/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin
1236. 解決日誌 Jar包衝突
[Tom@hadoop102 software]$ mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j slf4j-impl-2.10.0.bak
17. 初始化元資料庫
[Tomu@hadoop102 hive-3.1.2]$ bin/schematool -dbType derby -initSchema
18. 啟動並使用Hive
[Tom@hadoop102 hive-3.1.2]$ bin/hive
hive (default)> show databases;
hive (default)> show tables;
hive (default)> create table test(id int);
hive (default)> insert into test values(1);
hive (default)> select * from test;
OK
test.id
1
Time taken: 0.39 seconds, Fetched: 1 row(s)
12345678910在 CRT窗口中開啟另一個窗口，開啟 Hive，在 /tmp/atguigu目錄下監控 hive.log文件
Caused by: ERROR XSDB6:Another instance of Derby may have already booted the database /opt/module/hive/metastore_db.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.privGetJBMSLockOnDB(Unknown Source)
at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.run(Unknown Source)
...
1234567原因在於Hive 默認使用的元資料庫為derby ，開啟 Hive之後就會占用元資料庫， 且不與其他客戶端共享數據，所以我們需要將 Hive 的元數據地址改為 MySQL 。
2.2 MySQL 安裝
1. 檢查當前系統是否安裝過 MySQL
[Tom @hadoop102 ~]$ rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
## 如果存在通過如下命令卸載
[Tom @hadoop102 ~]$ sudo rpm -e --nodeps mariadb-libs
12342. 將 MySQL安裝包拷貝到 /opt/software目錄下，並解壓 MySQL安裝包
[Tom @hadoop102 software]# tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 Tom Tom 609556480 6月 30 22:20 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 Tom Tom 45109364 9月 30 2019 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 318768 9月 30 2019 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 7037096 9月 30 2019 mysql-community-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 49329100 9月 30 2019 mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 23354908 9月 30 2019 mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 136837816 9月 30 2019 mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 4374364 9月 30 2019 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 1353312 9月 30 2019 mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 208694824 9月 30 2019 mysql-community-server-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 133129992 9月 30 2019 mysql-community-test-5.7.28-1.el7.x86_64.rpm
1234567891011123. 在安裝目錄下執行rpm安裝
[Tom @hadoop102 software ]$
sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-compat-5.7.28 1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
123456注意：按照順序依次執行。如果Linux是最小化安裝的，在安裝 mysql-community-server-5.7.28-1.el7.x86_64.rpm時可能會出現如下錯誤
[Tom@hadoop102 software]$ sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 頭V3 DSA/SHA1 Signature, 密鑰ID 5072e1f5: NOKEY
錯誤：依賴檢測失敗：
libaio.so.1()(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
libaio.so.1(LIBAIO_0.1)(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
libaio.so.1(LIBAIO_0.4)(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
123456通過 yum安裝缺少的依賴 ,然後重新安裝 mysql-community-server-5.7.28-1.el7.x86_64即可
[Tom@hadoop102 software] yum install -y libaio
14. 刪除 /etc/my.cnf文件中 datadir指向的目錄下的所有內容，如果有內容的情況下 :
查看 datadir 的值
[mysqld]
datadir=/var/lib/mysql
12刪除 /var/lib/mysql目錄下的所有內容 :
[Tom @hadoop102 mysql] # cd /var/lib/mysql
[Tom @hadoop102 mysql] # sudo rm -rf ./*
125. 初始化資料庫
[Tom@hadoop102 opt]$ sudo mysqld --initialize --user=mysql
16. 查看臨時生成的 root用戶的密碼
[Tom @hadoop102 opt] $ sudo cat /var/log/mysqld.log
1
7. 啟動 MySQL服務
[Tom@hadoop102 opt]$ sudo systemctl start mysqld
18. 登錄 MySQL資料庫
[Tom@hadoop102 opt]$ mysql -uroot -p
Enter password: 輸入臨時生成的密碼
129. 必須先修改 root用戶的密碼 ，否則執行其他的操作會報錯
mysql> set password = password("新密碼");
1修改 mysql庫下的 user表中的 root用戶，允許任意 ip連接
mysql>update mysql.user set host='%' where user='root';
mysql> flush privileges;
122.3 Hive元數據配置到 MySQL
1. 拷貝驅動
將MySQL的 JDBC驅動拷貝到 Hive的 lib目錄下
[Tom@hadoop102 software]$ cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib
12. 配置 Metastore到 MySQL
（1）在 $HIVE_HOME/conf目錄下新建 hive-site.xml文件
[Tom@hadoop102 software]$ vim $HIVE_HOME/conf/hive site.xml
1添加如下內容
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<!--jdbc連接的URL-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
</property>
<!--jdbc連接的Driver-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!--jdbc連接的username-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!--jdbc連接的password-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>000000</value>
</property>
<!--Hive元數據存儲版本的驗證-->
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<!--元數據存儲授權-->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
<!--Hive默認在HDFS的工作目錄-->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
1234567891011121314151617181920212223242526272829303132333435363738394041424344（2）登錄MySQL
[Tom@hadoop102 software]$ mysql -uroot -p000000
1（3）新建 Hive元資料庫
mysql> create database metastore;
mysql> quit;
12（4）初始化Hive元資料庫
[Tom@hadoop102 software]$ schematool -initSchema -dbType mysql -verbose
13. 再次啟動Hive
（1）啟動Hive
[Tom@hadoop102 hive-3.1.2]$ bin/hive
1（2）使用Hive
hive> show databases;
hive> show tables;
hive> create table test (id int);
hive> insert into test values(1);
hive select * from test;
12345（3）在CRT窗口中開啟另一個窗口開啟 Hive
hive> show databases;
hive> show tables;
hive select * from aa;
1232.4 使用元數據服務的方式訪問 Hive
1. 在 hive-site.xml文件中添加如下配置信息
<!--指定存儲元數據要連接的地址-->
<property>
<name>hive.metastore.uris</name>
<value>thrift://hadoop102:9083</value>
</property>
123452. 啟動 metastore
[Tom@hadoop202 hive-3.1.2]$ hive --service metastore
2020-08-28 16:58:08: Starting Hive Metastore Server
12注意：啟動後窗口不能再操作 ，需打開一個新的 shell 窗口做別的操作
（3）啟動Hive
[Tom@hadoop103 hive-3.1.2]$ bin/hive
12.5 使用 JDBC方式訪問 Hive
（1）在 hive-site.xml文件中添加如下配置信息
<!--指定hiveserver2連接的host -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>hadoop102</value>
</property>
<!--指定hiveserver2連接的埠號-->
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
1234567891011（2）啟動hiveserver2
[Tom@hadoop102 hive-3.1.2]$ bin/hive--service hiveserver2
1（3）啟動 beeline客戶端（需要多等待一會）
[Tom@hadoop102 hive-3.1.2]$ bin/ beeline u jdbc:hive2://hadoop102:10000 -n Tom
1（4）看到如下界面
Connecting to jdbc:hive2://hadoop102:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://hadoop102:10000>
123456（5）編寫 hive服務啟動腳本
[Tom@hadoop102 hive-3.1.2]$ vim $HIVE_HOME/bin/hiveservices.sh
1#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs
if [ ! -d $HIVE_LOG_DIR ]
then
mkdir -p $HIVE_LOG_DIR
fi
#檢查進程是否運行正常，參數1為進程名，參數2為進程埠
function check_process()
{
pid=$(ps -ef 2>/dev/null| grep -v grep | grep -i $1 | awk '{print $2}')
ppid=$(netstat -nltp 2>/dev/null| grep $2 | awk '{print $7}' | cut -d '/' -f 1)
echo $pid
[[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}
function hive_start()
{
metapid=$(check_process HiveMetastore 9083)
cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
[ -z "$metapid" ] && eval $cmd || echo "Metastroe服務已啟動"
server2pid=$(check_process HiveServer2 10000)
cmd="nohup hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
[ -z "$server2pid" ] && eval $cmd || echo "HiveServer2服務已啟動"
}
function hive_stop()
{
metapid=$(check_process HiveMetastore 9083)
[ "$metapid" ] && kill $metapid || echo "Metastore服務未啟動"
server2pid=$(check_process HiveServer2 10000)
[ "$server2pid" ] && kill $server2pid || echo "HiveServer2服務未啟動"
}
case $1 in
"start")
hive_start
;;
"stop")
hive_stop
;;
"restart")
hive_stop
sleep 2
hive_start
;;
"status")
check_process HiveMetastore 9083 >/dev/null&& echo "Metastore服務運行正常" || echo "Metastore服務運行異常"
check_process HiveServer2 10000 >/dev/null&& echo "HiveServer2服務運行正常" || echo "HiveServer2服務運行異常"
;;
*)
echo Invalid Args!
echo 'Usage: '$(basename $0)' start|stop|restart|status'
;;
esac
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354添加執行權限
[Tom@hadoop102 hive-3.1.2]$ chmod +x $HIVE_HOME/bin/hiveservices.sh
1啟動 Hive後台服務
[Tom@hadoop102 hive-3.1.2]$ hiveservices.sh start
12.6 Hive 常用交互命令
[Tom@hadoop102 hive-3.1.2]$ bin/hive -help
usage: hive
-d,--define <key=value> Variable substitution to apply to Hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable substitution to apply to Hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)
1234567891011121314151. 「-e」不進入 hive的交互窗口執行 sql語句
[Tom@hadoop102 hive-3.1.2]$ bin/hive -e "select id from student;"
12. 「-f」執行腳本中的sql語句
（1）在 /opt/module/hive/下創建 datas目錄，並在 datas目錄下創建 hivef.sql文件
[Tom@hadoop102 datas]$ touch hivef.sql
1（2）文件中寫入正確的 sql 語句
select *from student;
1（3）執行文件中的 sql語句
[Tom@hadoop102 hive-3.1.2]$ bin/hive -f /opt/module/hive/datas/hivef.sql
1（4）執行文件中的 sql 語句並將結果寫入文件中
[Tom@hadoop102 hive-3.1.2]$ bin/hive -f /opt/module/ hive/ datas/hivef.sql > /opt/module/datas/hive_result.txt
13. 退出Hive窗口
hive(default)>exit;
hive(default)>quit;
124. 在 hive cli命令窗口中如何查看 hdfs文件系統
hive(default)>dfs ls /;
15. 查看在 hive中輸入的所有歷史命令
（1）進入到當前用戶的根目錄 /root或 /home/Tom
（2）查看 . hivehistory文件
[Tom@hadoop102 ~]$ cat .hivehistory
12.7 Hive 常見屬性配置
1. Hive 運行日誌信息配置
（1）Hive的 log 默認存放在 /tmp/Tom/hive.log目錄下（當前用戶名下）
（2）修改 hive的 log存放日誌到 /opt/module/hive-3.1.2/logs
修改 /opt/module/hive/conf/hive-log4j2.properties.template文件名稱為hive-log4j2.properties
[Tom@hadoop102 conf]$ pwd
/opt/module/hive-3.1.2/conf
[Tom@hadoop102 conf]$ mv hive-log4j2.properties.template hive-log4j2.properties
123在 hive-log4j2.properties文件中修改 log存放位置
hive.log.dir=/opt/module/hive/logs
12. 列印當前庫和表頭
在hive-site.xml中加入如下兩個配置 :
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
123456783. 參數配置方式
（1）查看當前所有的配置信息
hive>set;
1（2）參數的配置三種方式
Ⅰ默認配置文件：hive-default.xml
用戶自定義配置文件：hive-site.xml
注意：用戶自定義配置會覆蓋默認配置。 另外，Hive 也會讀入 Hadoop的配置， 因為 Hive是作為 Hadoop 的客戶端啟動的，Hive 的配置會覆蓋 Hadoop 的配置。配置文件的設定對本機啟動的所有 Hive 進程都有效。
Ⅱ 命令行參數方式
啟動Hive 時，可以在命令行添加 hiveconf param=value 來設定參數。如：
[Tom@hadoop102 hive-3.1.2]$ bin/hive -hiveconf mapred.reduce.tasks=10;
1注意：僅對本次hive 啟動有效
查看參數設置：
hive (default)> set mapred.reduce.tasks;
1Ⅲ 參數聲明方式
可以在HQL 中使用 SET 關鍵字設定參數，如：
[Tom@hadoop102 hive-3.1.2]$ bin/hive -hiveconf mapred.reduce.tasks=100;
1注意：僅對本次hive 啟動有效
查看參數設置：
hive (default)> set mapred.reduce.tasks;
1上述三種設定方式的優先級依次遞增。即配置文件<命令行參數<參數聲明。注意某些系統級的參數，例如 log4j 相關的設定，必須用前兩種方式設定，因為那些參數的讀取在會話建立以前已經完成了。
3 Hive 數據類型
3.1 基本數據類型
對於Hive 的 String 類型相當於資料庫的 varchar 類型，該類型是一個可變的字符串，不過它不能聲明其中最多能存儲多少個字符，理論上它可以存儲 2GB 的字符數。
3.2 集合數據類型
Hive有三種複雜數據類型：ARRAY 、MAP 和 STRUCT 。 ARRAY 和 MAP 與 Java 中的 Array 和 Map 類似，而 STRUCT 與 C 語言中的 Struct
類似，它封裝了一個命名欄位集合，複雜數據類型允許任意層次的嵌套。
案例實操
（1）假設某表有如下一行， 我們用 JSON 格式來表示其數據結構。在 Hive 下訪問的格式為
（2）基於上述數據結構，我們在 Hive里創建對應的表，並導入數據 。
創建本地測試文件 test.txt
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
12注意MAP，STRUCT和 ARRAY里的元素間關係都可以用同一個字符表示，這裡用 「_」。
（3）Hive上創建測試表 test
hive (default)> create table test(
> name string,
> friends array<string>,
> children map<string, int>,
> address struct<street:string, city:string>
> )
> row format delimited fields terminated by ','
> collection items terminated by '_'
> map keys terminated by ':'
> lines terminated by '\n';
OK
Time taken: 0.933 seconds
123456789101112欄位解釋：
row format delimited fields terminated by 『,』 – 列分隔符
collection items terminated by 『_』 --MAP STRUCT 和 ARRAY 的分隔符 (數據分割符號 )
map keys terminated by 『:』 – MAP中的 key與 value的分隔符
lines terminated by 『\n』; – 行分隔符
（4）導入文本數據到測試表
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/test.txt' into table test;
Loading data to table default.test
OK
Time taken: 0.893 seconds
1234（5）訪問三種集合列里的數據，以下分別是 ARRAY，MAP，STRUCT 的訪問方式
hive (default)> select friends[1], children['xiao song'], address.city from test
> where name="songsong";
OK
_c0 _c1 city
lili 18 beijing
Time taken: 0.386 seconds, Fetched: 1 row(s)
1234563.3 類型轉換
Hive的原子數據類型是可以進行隱式轉換的，類似於 Java 的類型轉換，例如某表達式使用 INT 類型， TINYINT 會自動轉換為 INT 類型，但是 Hive 不會進行反向轉化，例如，某表達式使用 TINYINT 類型， INT 不會自動轉換為 TINYINT 類型，它會返回錯誤，除非使用 CAST 操作。
1. 隱式類型轉換規則如下
（1）任何整數類型都可以隱式地轉換為一個範圍更廣的類型，如TINYINT可以轉換成INT，INT可以轉換成BIGINT。
（2）所有整數類型、FLOAT和STRING類型都可以隱式地轉換成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以轉換為FLOAT。
（4）BOOLEAN類型不可以轉換為任何其它的類型。
2. 可以使用CAST操作顯示進行數據類型轉換
例如CAST(『1』 AS INT)將把字符串』1』 轉換成整數1；如果強制類型轉換失敗，如執行CAST(『X』 AS INT)，表達式返回空值 NULL。1 基本概念
1.1 什麼是Hive
（1）Hive是由 Facebook 開源用於解決海量結構化日誌的數據統計工具；
（2）Hive是基於 Hadoop 的一個數據倉庫工具，可以將結構化的數據文件映射為一張表，並提供 類SQL 查詢功能；
（3）Hive的本質是將HQL轉化成MapReduce程序。
（4）Hive 處理的數據存儲在HDFS；
（5）Hive 分析數據底層的實現是MapReduce；
（6）執行程序運行在 Yarn 上。
1.2 Hive 的優缺點
優點
（1）操作接口採用類SQL 語法，提供快速開發的能力（簡單、容易上手）。
（2）避免了去寫MapReduce，減少開發人員的學習成本。
（3）Hive 的執行延遲比較高，因此Hive 常用於數據分析，對實時性要求不高的場合。
（4）Hive 優勢在於處理大數據，對於處理小數據沒有優勢，因為Hive 的執行延遲比較
高。
（5）Hive 支持用戶自定義函數，用戶可以根據自己的需求來實現自己的函數。
缺點
（1）Hive 的HQL 表達能力有限，疊代式算法無法表達，數據挖掘方面不擅長，由於MapReduce 數據處理流程的限制，效率更高的算法卻無法實現。
（2）Hive 的效率比較低，Hive 自動生成的MapReduce 作業，通常情況下不夠智能化，Hive 調優比較困難，粒度較粗。
1.3 Hive 架構原理
（1）用戶接口：Client
CLI（command-line interface）、JDBC/ODBC(jdbc 訪問hive)、WEBUI（瀏覽器訪問hive）
（2）元數據：Metastore
元數據包括：表名、表所屬的資料庫（默認是default）、表的擁有者、列/分區欄位、表的類型（是否是外部表）、表的數據所在目錄等；
默認存儲在自帶的derby 資料庫中，推薦使用MySQL 存儲Metastore
（3）Hadoop
使用HDFS 進行存儲，使用MapReduce 進行計算。
（4）驅動器
解析器（SQL Parser）：將SQL 字符串轉換成抽象語法樹AST，這一步一般都用第三方工具庫完成，比如antlr；對AST 進行語法分析，比如表是否存在、欄位是否存在、SQL語義是否有誤。
編譯器（Physical Plan）：將AST 編譯生成邏輯執行計劃。
優化器（Query Optimizer）：對邏輯執行計劃進行優化。
執行器（Execution）：把邏輯執行計劃轉換成可以運行的物理計劃。對於Hive 來說，就是MR/Spark。
Hive 通過給用戶提供的一系列交互接口，接收到用戶的指令(SQL)，使用自己的Driver，結合元數據(MetaStore)，將這些指令翻譯成MapReduce，提交到Hadoop 中執行，最後，將執行返回的結果輸出到用戶交互接口。
1.4 Hive 和資料庫比較
由於 Hive 採用了類似SQL 的查詢語言 HQL(Hive Query Language)，因此很容易將 Hive 理解為資料庫。其實從結構上來看， Hive 和資料庫除了擁有類似的查詢語言，再無類似之處。本文將從多個方面來闡述 Hive 和資料庫的差異。資料庫可以用在 Online 的應用中，但是Hive
是為數據倉庫而設計的，清楚這一點，有助於從應用角度理解 Hive 的特性。
（1）查詢語言
由於 SQL 被廣泛的應用在數據倉庫中，因此，專門針對 Hive 的特性設計了類 SQL 的查詢語言 HQL 。熟悉 SQL 開發的開發者可以很方便的使用 Hive 進行開發。
（2）數據更新
由於 Hive 是針對數據倉庫應用設計的，而數據倉庫的內容是讀多寫少的。 因此， Hive 中不建議對數據的改寫，所有的數據都是在加載的時候確定好的。 而資料庫中的數據通常是需要經常進行修改的，因此可以使用 INSERT INTO …
VALUES 添加數據，使用 UPDATE … SET 修改數據。
（3）執行延遲
Hive 在查詢數據的時候，由於沒有索引，需要掃描整個表，因此延遲較高。另外一個導致 Hive 執行延遲高的因素是 MapReduce 框架。由於 MapReduce 本身具有較高的延遲，因此在利用 MapReduce 執行 Hive 查詢時，也會有較高的延遲。相對的，資料庫的執行延遲較低。當然，這個低是有條件的，即數據規模較小，當數據規模大到超過資料庫的處理能力的時候，Hive
的並行計算顯然能體現出優勢。
（4）數據規模
由於Hive 建立在集群上並可以利用 MapReduce 進行並行計算，因此可以支持很大規模的數據；對應的，資料庫可以支持的數據規模較小。
2 Hive 安裝
2.1 Hive 安裝部署
1. 把 apache-hive-3.1.2-bin.tar.gz上傳到 linux的 /opt/software目錄下
2. 解壓 apache-hive-3.1.2-bin.tar.gz到 /opt/module/目錄下面
[Tom@hadoop102 software]$ tar -zxvf /opt/software/apache-hive-3.1.2-bin.tar.gz -C /opt/module/
13. 修改 apache-hive-3.1.2-bin.tar.gz的名稱為 hive-3.1.2
[Tom@hadoop102 software]$ mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive-3.1.2
14. 修改 /etc/profile.d/my_env.sh，添加環境變量
[Tom@hadoop102 software]$ sudo vim etc/profile.d/my_env.sh
15. 添加內容
#HIVE_HOME
export HIVE_HOME=/opt/module/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin
1236. 解決日誌 Jar包衝突
[Tom@hadoop102 software]$ mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j slf4j-impl-2.10.0.bak
17. 初始化元資料庫
[Tomu@hadoop102 hive-3.1.2]$ bin/schematool -dbType derby -initSchema
18. 啟動並使用Hive
[Tom@hadoop102 hive-3.1.2]$ bin/hive
hive (default)> show databases;
hive (default)> show tables;
hive (default)> create table test(id int);
hive (default)> insert into test values(1);
hive (default)> select * from test;
OK
test.id
1
Time taken: 0.39 seconds, Fetched: 1 row(s)
12345678910在 CRT窗口中開啟另一個窗口，開啟 Hive，在 /tmp/atguigu目錄下監控 hive.log文件
Caused by: ERROR XSDB6:Another instance of Derby may have already booted the database /opt/module/hive/metastore_db.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.privGetJBMSLockOnDB(Unknown Source)
at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.run(Unknown Source)
...
1234567原因在於Hive 默認使用的元資料庫為derby ，開啟 Hive之後就會占用元資料庫， 且不與其他客戶端共享數據，所以我們需要將 Hive 的元數據地址改為 MySQL 。
2.2 MySQL 安裝
1. 檢查當前系統是否安裝過 MySQL
[Tom @hadoop102 ~]$ rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
## 如果存在通過如下命令卸載
[Tom @hadoop102 ~]$ sudo rpm -e --nodeps mariadb-libs
12342. 將 MySQL安裝包拷貝到 /opt/software目錄下，並解壓 MySQL安裝包
[Tom @hadoop102 software]# tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 Tom Tom 609556480 6月 30 22:20 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 Tom Tom 45109364 9月 30 2019 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 318768 9月 30 2019 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 7037096 9月 30 2019 mysql-community-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 49329100 9月 30 2019 mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 23354908 9月 30 2019 mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 136837816 9月 30 2019 mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 4374364 9月 30 2019 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 1353312 9月 30 2019 mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 208694824 9月 30 2019 mysql-community-server-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 Tom Tom 133129992 9月 30 2019 mysql-community-test-5.7.28-1.el7.x86_64.rpm
1234567891011123. 在安裝目錄下執行rpm安裝
[Tom @hadoop102 software ]$
sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-compat-5.7.28 1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
123456注意：按照順序依次執行。如果Linux是最小化安裝的，在安裝 mysql-community-server-5.7.28-1.el7.x86_64.rpm時可能會出現如下錯誤
[Tom@hadoop102 software]$ sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 頭V3 DSA/SHA1 Signature, 密鑰ID 5072e1f5: NOKEY
錯誤：依賴檢測失敗：
libaio.so.1()(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
libaio.so.1(LIBAIO_0.1)(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
libaio.so.1(LIBAIO_0.4)(64bit) 被mysql-community-server-5.7.28-1.el7.x86_64 需要
123456通過 yum安裝缺少的依賴 ,然後重新安裝 mysql-community-server-5.7.28-1.el7.x86_64即可
[Tom@hadoop102 software] yum install -y libaio
14. 刪除 /etc/my.cnf文件中 datadir指向的目錄下的所有內容，如果有內容的情況下 :
查看 datadir 的值
[mysqld]
datadir=/var/lib/mysql
12刪除 /var/lib/mysql目錄下的所有內容 :
[Tom @hadoop102 mysql] # cd /var/lib/mysql
[Tom @hadoop102 mysql] # sudo rm -rf ./*
125. 初始化資料庫
[Tom@hadoop102 opt]$ sudo mysqld --initialize --user=mysql
16. 查看臨時生成的 root用戶的密碼
[Tom @hadoop102 opt] $ sudo cat /var/log/mysqld.log
1
7. 啟動 MySQL服務
[Tom@hadoop102 opt]$ sudo systemctl start mysqld
18. 登錄 MySQL資料庫
[Tom@hadoop102 opt]$ mysql -uroot -p
Enter password: 輸入臨時生成的密碼
129. 必須先修改 root用戶的密碼 ，否則執行其他的操作會報錯
mysql> set password = password("新密碼");
1修改 mysql庫下的 user表中的 root用戶，允許任意 ip連接
mysql>update mysql.user set host='%' where user='root';
mysql> flush privileges;
122.3 Hive元數據配置到 MySQL
1. 拷貝驅動
將MySQL的 JDBC驅動拷貝到 Hive的 lib目錄下
[Tom@hadoop102 software]$ cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib
12. 配置 Metastore到 MySQL
（1）在 $HIVE_HOME/conf目錄下新建 hive-site.xml文件
[Tom@hadoop102 software]$ vim $HIVE_HOME/conf/hive site.xml
1添加如下內容
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<!--jdbc連接的URL-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
</property>
<!--jdbc連接的Driver-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!--jdbc連接的username-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!--jdbc連接的password-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>000000</value>
</property>
<!--Hive元數據存儲版本的驗證-->
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<!--元數據存儲授權-->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
<!--Hive默認在HDFS的工作目錄-->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
1234567891011121314151617181920212223242526272829303132333435363738394041424344（2）登錄MySQL
[Tom@hadoop102 software]$ mysql -uroot -p000000
1（3）新建 Hive元資料庫
mysql> create database metastore;
mysql> quit;
12（4）初始化Hive元資料庫
[Tom@hadoop102 software]$ schematool -initSchema -dbType mysql -verbose
13. 再次啟動Hive
（1）啟動Hive
[Tom@hadoop102 hive-3.1.2]$ bin/hive
1（2）使用Hive
hive> show databases;
hive> show tables;
hive> create table test (id int);
hive> insert into test values(1);
hive select * from test;
12345（3）在CRT窗口中開啟另一個窗口開啟 Hive
hive> show databases;
hive> show tables;
hive select * from aa;
1232.4 使用元數據服務的方式訪問 Hive
1. 在 hive-site.xml文件中添加如下配置信息
<!--指定存儲元數據要連接的地址-->
<property>
<name>hive.metastore.uris</name>
<value>thrift://hadoop102:9083</value>
</property>
123452. 啟動 metastore
[Tom@hadoop202 hive-3.1.2]$ hive --service metastore
2020-08-28 16:58:08: Starting Hive Metastore Server
12注意：啟動後窗口不能再操作 ，需打開一個新的 shell 窗口做別的操作
（3）啟動Hive
[Tom@hadoop103 hive-3.1.2]$ bin/hive
12.5 使用 JDBC方式訪問 Hive
（1）在 hive-site.xml文件中添加如下配置信息
<!--指定hiveserver2連接的host -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>hadoop102</value>
</property>
<!--指定hiveserver2連接的埠號-->
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
1234567891011（2）啟動hiveserver2
[Tom@hadoop102 hive-3.1.2]$ bin/hive--service hiveserver2
1（3）啟動 beeline客戶端（需要多等待一會）
[Tom@hadoop102 hive-3.1.2]$ bin/ beeline u jdbc:hive2://hadoop102:10000 -n Tom
1（4）看到如下界面
Connecting to jdbc:hive2://hadoop102:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://hadoop102:10000>
123456（5）編寫 hive服務啟動腳本
[Tom@hadoop102 hive-3.1.2]$ vim $HIVE_HOME/bin/hiveservices.sh
1#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs
if [ ! -d $HIVE_LOG_DIR ]
then
mkdir -p $HIVE_LOG_DIR
fi
#檢查進程是否運行正常，參數1為進程名，參數2為進程埠
function check_process()
{
pid=$(ps -ef 2>/dev/null| grep -v grep | grep -i $1 | awk '{print $2}')
ppid=$(netstat -nltp 2>/dev/null| grep $2 | awk '{print $7}' | cut -d '/' -f 1)
echo $pid
[[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}
function hive_start()
{
metapid=$(check_process HiveMetastore 9083)
cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
[ -z "$metapid" ] && eval $cmd || echo "Metastroe服務已啟動"
server2pid=$(check_process HiveServer2 10000)
cmd="nohup hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
[ -z "$server2pid" ] && eval $cmd || echo "HiveServer2服務已啟動"
}
function hive_stop()
{
metapid=$(check_process HiveMetastore 9083)
[ "$metapid" ] && kill $metapid || echo "Metastore服務未啟動"
server2pid=$(check_process HiveServer2 10000)
[ "$server2pid" ] && kill $server2pid || echo "HiveServer2服務未啟動"
}
case $1 in
"start")
hive_start
;;
"stop")
hive_stop
;;
"restart")
hive_stop
sleep 2
hive_start
;;
"status")
check_process HiveMetastore 9083 >/dev/null&& echo "Metastore服務運行正常" || echo "Metastore服務運行異常"
check_process HiveServer2 10000 >/dev/null&& echo "HiveServer2服務運行正常" || echo "HiveServer2服務運行異常"
;;
*)
echo Invalid Args!
echo 'Usage: '$(basename $0)' start|stop|restart|status'
;;
esac
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354添加執行權限
[Tom@hadoop102 hive-3.1.2]$ chmod +x $HIVE_HOME/bin/hiveservices.sh
1啟動 Hive後台服務
[Tom@hadoop102 hive-3.1.2]$ hiveservices.sh start
12.6 Hive 常用交互命令
[Tom@hadoop102 hive-3.1.2]$ bin/hive -help
usage: hive
-d,--define <key=value> Variable substitution to apply to Hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable substitution to apply to Hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)
1234567891011121314151. 「-e」不進入 hive的交互窗口執行 sql語句
[Tom@hadoop102 hive-3.1.2]$ bin/hive -e "select id from student;"
12. 「-f」執行腳本中的sql語句
（1）在 /opt/module/hive/下創建 datas目錄，並在 datas目錄下創建 hivef.sql文件
[Tom@hadoop102 datas]$ touch hivef.sql
1（2）文件中寫入正確的 sql 語句
select *from student;
1（3）執行文件中的 sql語句
[Tom@hadoop102 hive-3.1.2]$ bin/hive -f /opt/module/hive/datas/hivef.sql
1（4）執行文件中的 sql 語句並將結果寫入文件中
[Tom@hadoop102 hive-3.1.2]$ bin/hive -f /opt/module/ hive/ datas/hivef.sql > /opt/module/datas/hive_result.txt
13. 退出Hive窗口
hive(default)>exit;
hive(default)>quit;
124. 在 hive cli命令窗口中如何查看 hdfs文件系統
hive(default)>dfs ls /;
15. 查看在 hive中輸入的所有歷史命令
（1）進入到當前用戶的根目錄 /root或 /home/Tom
（2）查看 . hivehistory文件
[Tom@hadoop102 ~]$ cat .hivehistory
12.7 Hive 常見屬性配置
1. Hive 運行日誌信息配置
（1）Hive的 log 默認存放在 /tmp/Tom/hive.log目錄下（當前用戶名下）
（2）修改 hive的 log存放日誌到 /opt/module/hive-3.1.2/logs
修改 /opt/module/hive/conf/hive-log4j2.properties.template文件名稱為hive-log4j2.properties
[Tom@hadoop102 conf]$ pwd
/opt/module/hive-3.1.2/conf
[Tom@hadoop102 conf]$ mv hive-log4j2.properties.template hive-log4j2.properties
123在 hive-log4j2.properties文件中修改 log存放位置
hive.log.dir=/opt/module/hive/logs
12. 列印當前庫和表頭
在hive-site.xml中加入如下兩個配置 :
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
123456783. 參數配置方式
（1）查看當前所有的配置信息
hive>set;
1（2）參數的配置三種方式
Ⅰ默認配置文件：hive-default.xml
用戶自定義配置文件：hive-site.xml
注意：用戶自定義配置會覆蓋默認配置。 另外，Hive 也會讀入 Hadoop的配置， 因為 Hive是作為 Hadoop 的客戶端啟動的，Hive 的配置會覆蓋 Hadoop 的配置。配置文件的設定對本機啟動的所有 Hive 進程都有效。
Ⅱ 命令行參數方式
啟動Hive 時，可以在命令行添加 hiveconf param=value 來設定參數。如：
[Tom@hadoop102 hive-3.1.2]$ bin/hive -hiveconf mapred.reduce.tasks=10;
1注意：僅對本次hive 啟動有效
查看參數設置：
hive (default)> set mapred.reduce.tasks;
1Ⅲ 參數聲明方式
可以在HQL 中使用 SET 關鍵字設定參數，如：
[Tom@hadoop102 hive-3.1.2]$ bin/hive -hiveconf mapred.reduce.tasks=100;
1注意：僅對本次hive 啟動有效
查看參數設置：
hive (default)> set mapred.reduce.tasks;
1上述三種設定方式的優先級依次遞增。即配置文件<命令行參數<參數聲明。注意某些系統級的參數，例如 log4j 相關的設定，必須用前兩種方式設定，因為那些參數的讀取在會話建立以前已經完成了。
3 Hive 數據類型
3.1 基本數據類型
對於Hive 的 String 類型相當於資料庫的 varchar 類型，該類型是一個可變的字符串，不過它不能聲明其中最多能存儲多少個字符，理論上它可以存儲 2GB 的字符數。
3.2 集合數據類型
Hive有三種複雜數據類型：ARRAY 、MAP 和 STRUCT 。 ARRAY 和 MAP 與 Java 中的 Array 和 Map 類似，而 STRUCT 與 C 語言中的 Struct
類似，它封裝了一個命名欄位集合，複雜數據類型允許任意層次的嵌套。
案例實操
（1）假設某表有如下一行， 我們用 JSON 格式來表示其數據結構。在 Hive 下訪問的格式為
（2）基於上述數據結構，我們在 Hive里創建對應的表，並導入數據 。
創建本地測試文件 test.txt
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
12注意MAP，STRUCT和 ARRAY里的元素間關係都可以用同一個字符表示，這裡用 「_」。
（3）Hive上創建測試表 test
hive (default)> create table test(
> name string,
> friends array<string>,
> children map<string, int>,
> address struct<street:string, city:string>
> )
> row format delimited fields terminated by ','
> collection items terminated by '_'
> map keys terminated by ':'
> lines terminated by '\n';
OK
Time taken: 0.933 seconds
123456789101112欄位解釋：
row format delimited fields terminated by 『,』 – 列分隔符
collection items terminated by 『_』 --MAP STRUCT 和 ARRAY 的分隔符 (數據分割符號 )
map keys terminated by 『:』 – MAP中的 key與 value的分隔符
lines terminated by 『\n』; – 行分隔符
（4）導入文本數據到測試表
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/test.txt' into table test;
Loading data to table default.test
OK
Time taken: 0.893 seconds
1234（5）訪問三種集合列里的數據，以下分別是 ARRAY，MAP，STRUCT 的訪問方式
hive (default)> select friends[1], children['xiao song'], address.city from test
> where name="songsong";
OK
_c0 _c1 city
lili 18 beijing
Time taken: 0.386 seconds, Fetched: 1 row(s)
1234563.3 類型轉換
Hive的原子數據類型是可以進行隱式轉換的，類似於 Java 的類型轉換，例如某表達式使用 INT 類型， TINYINT 會自動轉換為 INT 類型，但是 Hive 不會進行反向轉化，例如，某表達式使用 TINYINT 類型， INT 不會自動轉換為 TINYINT 類型，它會返回錯誤，除非使用 CAST 操作。
1. 隱式類型轉換規則如下
（1）任何整數類型都可以隱式地轉換為一個範圍更廣的類型，如TINYINT可以轉換成INT，INT可以轉換成BIGINT。
（2）所有整數類型、FLOAT和STRING類型都可以隱式地轉換成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以轉換為FLOAT。
（4）BOOLEAN類型不可以轉換為任何其它的類型。
2. 可以使用CAST操作顯示進行數據類型轉換
例如CAST(『1』 AS INT)將把字符串』1』 轉換成整數1；如果強制類型轉換失敗，如執行CAST(『X』 AS INT)，表達式返回空值 NULL。
（二）DDL數據定義、DML數據操作
1 DDL 數據定義
1.1 創建資料庫
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
1234（1）創建一個資料庫，資料庫在HDFS上的默認存儲路徑是/user/hive/warehouse/*.db。
hive (default)> create database db_hive;
OK
Time taken: 0.12 seconds
123（2）避免要創建的資料庫已經存在錯誤，增加if not exists判斷。（標準寫法）
hive (default)> create database db_hive;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists
hive (default)> create database if not exists db_hive;
OK
Time taken: 0.044 seconds
12345（3）創建一個資料庫，指定資料庫在HDFS上存放的位置
hive (default)> create database db_hive2 location '/db_hive2.db';
OK
Time taken: 0.07 seconds
1231.2 查詢資料庫
1. 顯示資料庫
（1）顯示資料庫
hive (default)> show databases;
OK
database_name
db_hive
db_hive2
default
Time taken: 0.054 seconds, Fetched: 3 row(s)
1234567（2）過濾顯示查詢的資料庫
hive (default)> show databases like 'db_hive*';
OK
database_name
db_hive
db_hive2
Time taken: 0.036 seconds, Fetched: 2 row(s)
1234562. 查看資料庫詳情
（1）顯示資料庫信息
hive (default)> desc database db_hive;
OK
db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db Tom USER
Time taken: 0.057 seconds, Fetched: 1 row(s)
12345（2）顯示資料庫詳細信息，extended
hive (default)> desc database extended db_hive;
OK
db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db Tom USER
Time taken: 0.074 seconds, Fetched: 1 row(s)
123453. 切換當前資料庫
hive (default)> use db_hive;
OK
Time taken: 0.057 seconds
hive (db_hive)>
12341.3 修改資料庫
用戶可以使用 ALTER DATABASE命令為某個資料庫的 DBPROPERTIES 設置鍵 -值對屬性值來描述這個資料庫的屬性信息。
hive (db_hive)> alter database db_hive
> set dbproperties('createtime'='20210828');
OK
Time taken: 0.101 seconds
1234在hive中查看修改結果
hive (db_hive)> desc database extended db_hive;
OK
db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db Tom USER {createtime=20210828}
Time taken: 0.072 seconds, Fetched: 1 row(s)
123451.4 刪除資料庫
（1）刪除空資料庫
hive (db_hive)> drop database db_hive2;
OK
Time taken: 0.095 seconds
123（2）如果刪除的資料庫不存在，最好採用 if exists判斷資料庫是否存在
hive (db_hive)> drop database db_hive2;
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive2
hive (db_hive)> drop database if exists db_hive2;
OK
Time taken: 0.025 seconds
12345（3）如果資料庫不為空，可以採用 cascade 命令，強制刪除
hive (db_hive)> drop database db_hive;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
hive (db_hive)> drop database db_hive cascade;
OK
Time taken: 0.364 seconds
123451.5 創建表
1. 建表語法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
12345678910112. 欄位解釋說明
（1）CREATE TABLE：創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常，用戶可以用 IF NOT EXISTS 選項來忽略這個異常。
（2） EXTERNAL 關鍵字：可以讓用戶創建一個外部表，在建表的同時，可以指定一個指向實際數據的路徑（ LOCATION ），在刪除表的時候，內部表的元數據和數據會被一起刪除，而外部表只刪除元數據，不刪除數據。
（3）COMMENT ：為表和列添加注釋。
（4）PARTITIONED BY：創建分區表。
（5）CLUSTERED BY：創建分桶表。
（6）SORTED BY：不常用，對桶中的一個或多個列另外排序。
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用戶在建表的時候可以自定義SerDe 或者使用自帶的 SerDe 。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED ，將會使用自帶的 SerDe 。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的數據。
SerDe 是 Serialize/Deserilize 的簡稱， hive 使用 Serde 進行行對象的序列與反序列化。
（8）STORED AS：指定存儲文件類型
常用的存儲文件類型：SEQUENCEFILE （二進位序列文件）、 TEXTFILE （文本）、 RCFILE （列
式存儲格式文件）
如果文件數據是純文本，可以使用STORED AS TEXTFILE 。如果數據需要壓縮，使用 STORED
AS SEQUENCEFILE 。
（9）LOCATION ：指定表在 HDFS 上的存儲位置。
（10）AS ：後跟查詢語句 根據查詢結果創建表 。
（11）LIKE：允許用戶複製現有的表結構，但是不複製數據。
1.5.1 管理表
1. 理論
默認創建的表都是所謂的管理表，有時也被稱為內部表。因為這種表，Hive 會（或多或少地）控制著數據的生命周期。 Hive 默認情況下會將這些表的數據存儲在由配置hive.metastore.warehouse.dir(例如，/user/hive/warehouse) 所定義的目錄的子目錄下。
當我們刪除一個管理表時，Hive 也會刪除這個表中數據。 管理表不適合和其他工具共享數據。
2. 案例實操
由於 Hive 是針對數據倉庫應用設計的，而數據倉庫的內容是讀多寫少的。 因此， Hive 中不建議對數據的改寫，所有的數據都是在加載的時候確定好的。 而資料庫中的數據通常是需要經常進行修改的，因此可以使用 INSERT INTO …
VALUES 添加數據，使用 UPDATE … SET 修改數據。
（1）普通創建表
hive (default)> create table if not exists student(
> id int, name string
> )
> row format delimited fields terminated by '\t'
> stored as textfile
> location '/user/hive/warehouse/student';
OK
Time taken: 0.397 seconds
12345678（2）根據查詢結果創建表（查詢的結果會添加到新創建的表中）
hive (default)> create table if not exists student2 as select id, name from student;
1（3）根據已經存在的表結構創建表
hive (default)> create table if not exists student3 like student;
OK
Time taken: 0.145 seconds
123（4）查詢表的類型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
121.5.2 外部表
1. 理論
因為表是外部表，所以 Hive並非認為其完全擁有這份數據。刪除該表並不會刪除掉這份數據，不過描述表的元數據信息會被刪除掉。
2. 管理表和外部表的使用場景
每天將收集到的網站日誌定期流入HDFS 文本文件。在外部表（原始日誌表）的基礎上做大量的統計分析，用到的中間表、結果表使用內部表存儲，數據通過 SELECT+INSERT 進入內部表。
3. 案例實操
創建學生表
student：
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss1
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16
12345678910111213141516（1）上傳數據到HDFS
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/hive-3.1.2/data/student.txt /student;
12（2）創建表並載入數據
hive (default)> create external table if not exists student(
> id int,
> name string
> )
> row format delimited fields terminated by '\t';
OK
Time taken: 0.091 seconds
hive (default)> load data inpath '/student/student.txt' into table default.student;
Loading data to table default.student
OK
Time taken: 0.419 seconds
1234567891011（3）查看表的類型
hive (default)> desc formatted student;
Table Type: EXTERNAL_TABLE
12（4）刪除外部表
hive (default)> drop table student;
OK
Time taken: 0.168 seconds
123外部表刪除後，hdfs中的數據還在，但是 metadata中 dept的元數據已被刪除。
1.5.3 管理表與外部表的互相轉換
（1）查詢表的類型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
12（2）修改內部表 student2為外部表
hive (default)> alter table student2 set tblproperties('EXTERNAL'='TRUE');
OK
Time taken: 0.174 seconds
123（3）查詢表的類型
Table Type: EXTERNAL_TABLE
1（4）修改外部表 student2為內部表
hive (default)> alter table student2 set tblproperties('EXTERNAL'='FALSE');
OK
Time taken: 0.153 seconds
123（5）查詢表的類型
Table Type: MANAGED_TABLE
1注意：(『EXTERNAL』=『TRUE』)和 (『EXTERNAL』=『FALSE』)為固定寫法，區分大小寫
1.6 修改表
1. 重命名表
（1）語法
ALTER TABLE table_name RENAME TO new_table_name
1（2）實操案例
hive (default)> alter table student2 rename to student4;
OK
Time taken: 0.258 seconds
1232. 增加/修改/替換列信息
更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name
column_type [COMMENT col_comment] [FIRST|AFTER column_name]
12增加和替換列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT
col_comment], ...)
12ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)，REPLACE則是表示替換表中所有欄位。
（1）查詢表結構
hive (default)> desc dept;
OK
col_name data_type comment
deptno int
dname string
loc int
Time taken: 0.099 seconds, Fetched: 3 row(s)
1234567（2）添加列
hive (default)> alter table dept add columns(deptdesc string);
OK
Time taken: 0.138 seconds
123（3）查詢表結構
hive (default)> desc dept;
OK
col_name data_type comment
deptno int
dname string
loc int
deptdesc string
Time taken: 0.056 seconds, Fetched: 4 row(s)
12345678（3）更新列
hive (default)> alter table dept change column deptdesc desc string;
OK
Time taken: 0.192 seconds
123（3）查詢表結構
hive (default)> desc dept;
OK
col_name data_type comment
deptno int
dname string
loc int
desc string
Time taken: 0.064 seconds, Fetched: 4 row(s)
12345678（3）替換列
hive (default)> alter table dept replace columns(deptno string, dname string, loc string);
OK
Time taken: 0.149 seconds
123（3）查詢表結構
hive (default)> desc dept;
OK
col_name data_type comment
deptno string
dname string
loc string
Time taken: 0.064 seconds, Fetched: 3 row(s)
12345671.7 刪除表
hive (default)> drop table dept;
OK
Time taken: 1.901 seconds
1232 DML 數據操作
2.1 數據導入
1. 向表中裝載數據（load）
hive> load data [local] inpath ' 數據的 path ' overwrite into table
student [partition (partcol1=val1,…)];
12（1）load data:表示加載數據
（2）local:表示從本地加載數據到 hive表，否則從 HDFS加載數據到 hive表
（3）inpath: 表示加載數據的路徑
（4）overwrite: 表示覆蓋表中已有數據，否則表示追加
（5）into table: 表示加載到哪張表
（6）student:表示具體的表
（7）partition:表示上傳到指定分區
2. 實操案例
（1）創建一張表
hive (default)> create table student(id string, name string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.878 seconds
1234（2）加載本地文件到 hive
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/student.txt' into table student;
Loading data to table default.student
OK
Time taken: 2.725 seconds
1234（3）加載 HDFS文件到 hive中
hive (default)> dfs -put /opt/module/hive-3.1.2/data/student.txt /student;
hive (default)> load data inpath '/student' into table student;
Loading data to table default.student
OK
Time taken: 0.712 seconds
12345（4）加載數據覆蓋表中已有的數據
hive (default)> dfs -put /opt/module/hive-3.1.2/data/student.txt /student;
hive (default)> load data inpath '/student/student.txt' overwrite into table student;
Loading data to table default.student
OK
Time taken: 0.501 seconds
123453. 通過查詢語句向表中插入數據（Insert）
（1）創建一張表
hive (default)> create table student_par(id int, name string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.225 seconds
1234（2）基本插入數據
hive (default)> insert into table student_par
> values(1, 'liubai'), (2, 'junmo');
12（3）基本模式插入（根據單張表查詢結果）
hive (default)> insert overwrite table student_par
> select id, name from student where month='201609';
12insert into:：以追加數據的方式插入到表或分區，原有數據不會刪除
insert overwrite：會覆蓋表中已存在的數據
注意：insert不支持插入部分欄位
（4）多表（多分區）插入模式（根據多張表查詢結果）
hive (default)> from student
> insert overwrite table student partition(month='201707')
> select id, name where month='201709'
> insert overwrite table student partition(month='201706')
> select id, name where month='201709';
123454. 查詢語句中創建表並加載數據（ As Select）
根據查詢結果創建表（查詢的結果會添加到新創建的表中）
hive (default)> create table if not exists student3
> as select id, name from student;
125. 創建表時通過 Location指定加載數據路徑
（1）上傳數據到 hdfs 上
hive (default)> dfs -put /opt/module/hive-3.1.2/data/student.txt /student;
1（2）創建表，並指定在 hdfs 上的位置
hive (default)> create external table if not exists student5(
> id int, name string
> )
> row format delimited fields terminated by '\t'
> location '/student';
Time taken: 0.111 seconds
123456（3）查詢數據
hive (default)> select * from student5;
OK
student5.id student5.name
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16
Time taken: 0.275 seconds, Fetched: 16 row(s)
12345678910111213141516171819206. Import數據到指定 Hive表中
注意: 先用 export導出後，再將數據導入。
hive (default)> import table student2 from
> '/student';
Copying data from hdfs://hadoop102:8020/student/data
Copying file: hdfs://hadoop102:8020/student/data/student.txt
Loading data to table default.student2
OK
Time taken: 1.234 seconds
12345672.2 數據導出
1. Insert 導出
（1）將查詢的結果導出到本地
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/student'
> select * from student;
12（2）將查詢的結果格式化導出到本地
hive (default)> insert overwrite local directory
> '/opt/module/hive-3.1.2/data/student'
> row format delimited fields terminated by '\t'
> select * from student;
1234（3）將查詢的結果導出到 HDFS上 (沒有 local)
hive (default)> insert overwrite directory '/student'
> row format delimited fields terminated by '\t'
> select * from student;
1232. Hadoop 命令導出到本地
hive (default)> dfs -get /user/hive/warehouse/student/student.txt /opt/module/hive-3.1.2/data/student;
13. Hive Shell 命令導出
基本語法：hive -f/-e 執行語句或者腳本 > file
[Tom@hadoop102 hive-3.1.2]$ bin/hive -e 'select * from default.student;' > /opt/module/hive-3.1.2/data/student/student2.txt;
14. Export導出到 HDFS上
export和 import主要用於 兩個 Hadoop平台集群之間 Hive表遷移。
hive (default)> export table default.student to
> '/student';
OK
Time taken: 1.434 seconds
12345. 清除表中數據（truncate）
注意：Truncate只能刪除管理表，不能刪除外部表中數據
hive (default)> truncate table student;
OK
Time taken: 0.353 seconds（三）查詢、分區表和分桶表
1 查詢
查詢語句語法：
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list]
| [DISTRIBUTE BY col_list] [SORT BY col_list]
[LIMIT number]
123456781.1 基本查詢（Select…From）
1.1.1 全表和特定列查詢
1. 數據準備
（1）原始數據
dept：
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
1234emp：
369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7534 MILLER CLERK 7782 1982-10-23 1300.00 50
123456789101112131415（2）創建部門表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
123456（3）創建員工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
12345678910（4）導入數據
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept;
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/emp.txt' into table emp;
122. 全表查詢
hive (default)> select * from emp;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7534 MILLER CLERK 7782 1982-10-23 1300.0 NULL 50
Time taken: 0.271 seconds, Fetched: 15 row(s)
hive (default)> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
OK
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7534 MILLER CLERK 7782 1982-10-23 1300.0 NULL 50
Time taken: 0.323 seconds, Fetched: 15 row(s)
12345678910111213141516171819202122232425262728293031323334353637383. 選擇特定列查詢
hive (default)> select empno, ename from emp;
OK
empno ename
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
7534 MILLER
Time taken: 0.279 seconds, Fetched: 15 row(s)
12345678910111213141516171819注意：
（1）SQL 語言 大小寫不敏感。
（2）SQL 可以寫在一行或者多行
（3）關鍵字不能被縮寫也不能分行
（4）各子句一般要分行寫。
（5）使用縮進提高語句的可讀性。
1.1.2 列別名
重命名一個列便於計算。命名方法為緊跟列名，也可以在列名和別名之間加入關鍵字AS。
實例：查詢名稱和部門
hive (default)> select ename as name, deptno dn from emp;
OK
name dn
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
MILLER 50
Time taken: 0.289 seconds, Fetched: 15 row(s)
123456789101112131415161718191.1.3 算術運算符
案例實操：查詢出所有員工的薪水後加 1 顯示。
hive (default)> select sal+1 from emp;
OK
_c0
801.0
1601.0
1251.0
2976.0
1251.0
2851.0
2451.0
3001.0
5001.0
1501.0
1101.0
951.0
3001.0
1301.0
1301.0
Time taken: 0.79 seconds, Fetched: 15 row(s)
123456789101112131415161718191.1.4 常用函數
（1）求總行數（count）
hive (default)> select count(*) cnt from emp;
OK
cnt
15
Time taken: 20.637 seconds, Fetched: 1 row(s)
12345（2）求工資的最大值（max）
hive (default)> select max(sal) max_sal from emp;
OK
max_sal
5000.0
Time taken: 19.305 seconds, Fetched: 1 row(s)
12345（3）求工資的最小值（min）
hive (default)> select min(sal) min_sal from emp;
OK
min_sal
800.0
Time taken: 31.402 seconds, Fetched: 1 row(s)
12345（4）求工資的總和（sum）
hive (default)> select sum(sal) sum_sal from emp;
OK
sum_sal
30325.0
Time taken: 8.185 seconds, Fetched: 1 row(s)
12345（5）求工資的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
OK
avg_sal
2021.6666666666667
Time taken: 8.706 seconds, Fetched: 1 row(s)
123451.1.5 Limit 語句
典型的查詢會返回多行數據。LIMIT 子句用於限制返回的行數。
hive (default)> select * from emp limit 3;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
Time taken: 0.318 seconds, Fetched: 3 row(s)
hive (default)> select sal from emp limit 5;
OK
sal
800.0
1600.0
1250.0
2975.0
1250.0
Time taken: 0.298 seconds, Fetched: 5 row(s)
123456789101112131415161.1.6 Where 語句
使用 WHERE 子句，將不滿足條件的行過濾掉。
WHERE 子句緊隨 FROM 子句。WHERE 子句中不能使用欄位別名。
例：查詢出薪水大於2000 的所有員工：
hive (default)> select * from emp where sal > 2000;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
Time taken: 0.324 seconds, Fetched: 6 row(s)
123456789101.1.7 比較運算符（Between / In / Is Null）
下面表中描述了謂詞操作符，這些操作符同樣可以用於 JOIN…ON和 HAVING語句中。
例：（1）查詢出薪水等於 5000 的所有員工
hive (default)> select * from emp where sal=5000;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
Time taken: 0.312 seconds, Fetched: 1 row(s)
12345（2）查詢工資在 800到 950 的員工信息
hive (default)> select * from emp where sal between 800 and 950;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
Time taken: 0.267 seconds, Fetched: 2 row(s)
123456（3）查詢 comm 為空的所有員工信息
hive (default)> select * from emp where comm is null;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7534 MILLER CLERK 7782 1982-10-23 1300.0 NULL 50
Time taken: 0.283 seconds, Fetched: 11 row(s)
123456789101112131415（4）查詢工資是 1500 或 5000 的員工信息
hive (default)> select * from emp where sal in (1500, 5000);
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
Time taken: 0.299 seconds, Fetched: 2 row(s)
1234561.1.8 Like和RLike
（1）使用 LIKE 運算選擇類似的值。
（2）選擇條件可以包含字符或數字。
% 代表零個或多個字符任意個字符 。
_ 代表一個字符。
（3）RLIKE 子句 是 Hive 中這個功能的一個擴展，其可以通過 Java 的正則表達式這個更強大的語言來指定匹配條件。
案例實操
查找名字以 A 開頭的員工信息
hive (default)> select * from emp where ename like 'A%';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
12345查找名字中第二個字母為 A 的員工信息
hive (default)> select * from emp where ename like '_A%';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
Time taken: 0.301 seconds, Fetched: 3 row(s)
1234567查找名字中帶有 A 的員工信息
hive (default)> select * from emp where ename rlike '[A]';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
Time taken: 0.247 seconds, Fetched: 7 row(s)
12345678910111.1.9 邏輯運算符（And / Or / Not）
案例實操
（1）查詢薪水大於 1000 ，部門是 30
hive (default)> select * from emp where sal>1000 and deptno=30;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
Time taken: 0.294 seconds, Fetched: 5 row(s)
123456789（2）查詢薪水大於 1000 ，或者部門是 30
hive (default)> select * from emp where sal>1000 or deptno=30;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7534 MILLER CLERK 7782 1982-10-23 1300.0 NULL 50
Time taken: 0.232 seconds, Fetched: 14 row(s)
123456789101112131415161718（3）查詢除了 20 部門和 30 部門以外的員工信息
hive (default)> select * from emp where deptno not in (20, 30);
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7534 MILLER CLERK 7782 1982-10-23 1300.0 NULL 50
Time taken: 0.257 seconds, Fetched: 4 row(s)
123456781.2 分組
1.2.1 Group By 語句
GROUP BY 語句通常會和聚合函數一起使用，按照一個或者多個列隊結果進行分組，然後對每個組執行聚合操作。
案例實操
（1）計算 emp 表每個部門的平均工資
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
OK
t.deptno avg_sal
10 2916.6666666666665
20 2175.0
30 1566.6666666666667
50 1300.0
Time taken: 33.449 seconds, Fetched: 4 row(s)
12345678（2）計算 emp 每個部門中每個崗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t
> group by t.deptno, t.job;
OK
t.deptno t.job max_sal
20 ANALYST 3000.0
10 CLERK 1300.0
20 CLERK 1100.0
30 CLERK 950.0
50 CLERK 1300.0
10 MANAGER 2450.0
20 MANAGER 2975.0
30 MANAGER 2850.0
10 PRESIDENT 5000.0
30 SALESMAN 1600.0
Time taken: 10.678 seconds, Fetched: 10 row(s)
1234567891011121314151.2.2 Having 語句
1. having與 where不同點
（1）where後面不能寫分組函數 而 having後面可以使用分組函數。
（2）having只用於 group by分組統計語句。
2. 案例實操
求每個部門的平均薪水大於 2000 的部門
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
OK
deptno avg_sal
10 2916.6666666666665
20 2175.0
Time taken: 21.843 seconds, Fetched: 2 row(s)
hive (default)> select deptno, avg_sal from(
> select deptno, avg(sal) avg_sal from emp
> group by deptno)t1
> where avg_sal>2000;
OK
deptno avg_sal
10 2916.6666666666665
20 2175.0
Time taken: 35.768 seconds, Fetched: 2 row(s)
123456789101112131415161.3 Join 語句
1.3.1 等值 Join
Hive支持通常的 SQL JOIN語句 。
例：根據員工表和部門表中的部門編號相等，查詢員工編號、員工名稱、部門編號和部門名稱
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
OK
e.empno e.ename d.deptno d.dname
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7844 TURNER 30 SALES
7900 JAMES 30 SALES
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7902 FORD 20 RESEARCH
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
Time taken: 44.828 seconds, Fetched: 14 row(s)
1234567891011121314151617181.3.2 表的別名
好處：使用別名可以簡化查詢；使用表名前綴可以提高執行效率。
1.3.3 內連接
內連接：只有進行連接的兩個表中都存在與連接條件相匹配的數據才會被保留下來。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
OK
e.empno e.ename d.deptno
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
7782 CLARK 10
7839 KING 10
7934 MILLER 10
Time taken: 27.323 seconds, Fetched: 14 row(s)
1234567891011121314151617181.3.4 左外連接
左外連接：JOIN 操作符左邊表中符合 WHERE 子句的所有記錄將會被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
OK
e.empno e.ename d.deptno
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
7782 CLARK 10
7839 KING 10
7934 MILLER 10
7534 MILLER NULL
Time taken: 14.959 seconds, Fetched: 15 row(s)
123456789101112131415161718191.3.5 右外連接
右外連接：JOIN 操作符右邊表中符合 WHERE 子句的所有記錄將會被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
OK
e.empno e.ename d.deptno
NULL NULL 40
7782 CLARK 10
7934 MILLER 10
7839 KING 10
7499 ALLEN 30
7900 JAMES 30
7844 TURNER 30
7698 BLAKE 30
7654 MARTIN 30
7521 WARD 30
7369 SMITH 20
7902 FORD 20
7876 ADAMS 20
7788 SCOTT 20
7566 JONES 20
Time taken: 14.322 seconds, Fetched: 15 row(s)
123456789101112131415161718191.3.6 滿外連接
滿外連接：將會返回所有表中符合WHERE 語句條件的所有記錄。如果任 一表的指定欄位沒有符合條件的值的話，那麼就使用 NULL 值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
OK
e.empno e.ename d.deptno
7934 MILLER 10
7782 CLARK 10
7839 KING 10
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
7521 WARD 30
7844 TURNER 30
7499 ALLEN 30
7900 JAMES 30
7698 BLAKE 30
7654 MARTIN 30
NULL NULL 40
7534 MILLER NULL
Time taken: 23.816 seconds, Fetched: 16 row(s)
12345678910111213141516171819201.3.7 多表連接
注意：連接n 個表，至少需要 n 1 個連接條件。例如：連接三個表，至少需要兩個連接條件。
數據準備：
1700 Beijing
1800 Shanghai
1900 Wuhan
123（1）創建位置表
hive (default)> create table if not exists location(
> loc int,
> loc_name string
> )
> row format delimited fields terminated by '\t';
OK
Time taken: 0.538 seconds
1234567（2）導入數據
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/loc.txt' into table location;
Loading data to table default.location
OK
Time taken: 0.837 seconds
1234（3）多表連接查詢
hive (default)> select e.ename, d.dname, l.loc_name
> from emp e
> join dept d
> on d.deptno = e.deptno
> join location l
> on d.loc = l.loc;
OK
e.ename d.dname l.loc_name
ALLEN SALES Wuhan
WARD SALES Wuhan
MARTIN SALES Wuhan
BLAKE SALES Wuhan
TURNER SALES Wuhan
JAMES SALES Wuhan
SMITH RESEARCH Shanghai
JONES RESEARCH Shanghai
SCOTT RESEARCH Shanghai
ADAMS RESEARCH Shanghai
FORD RESEARCH Shanghai
CLARK ACCOUNTING Beijing
KING ACCOUNTING Beijing
MILLER ACCOUNTING Beijing
Time taken: 34.34 seconds, Fetched: 14 row(s)
1234567891011121314151617181920212223大多數情況下Hive 會對每對 JOIN 連接對象啟動一個 MapReduce 任務。本例中會首先啟動一個 MapReduce job 對表 e 和表 d 進行連接操作，然後會再啟動一個 MapReduce job 將第一個 MapReduce job 的輸出和表 l 進行連接操作。
注意：為什麼不是表d 和表 l 先進行連接操作呢 ？
這是因為 Hive 總是按照從左到右的順序執行的。
優化：當對 3 個或者更多表進行 join 連接時，如果每個 on 子 句都使用相同的連接鍵的話，那麼只會產生一個 MapReduce job 。
1.3.8 笛卡爾積
笛卡爾集會在下麵條件下產生：
（1）省略連接條件
（2）連接條件無效
（3）所有表中的所有行互相連接
例：
hive (default)> select empno, dname from emp, dept;
OK
empno dname
7369 ACCOUNTING
7369 OPERATIONS
7369 SALES
7369 RESEARCH
7499 ACCOUNTING
7499 OPERATIONS
7499 SALES
7499 RESEARCH
7521 ACCOUNTING
7521 OPERATIONS
7521 SALES
7521 RESEARCH
7566 ACCOUNTING
7566 OPERATIONS
7566 SALES
7566 RESEARCH
7654 ACCOUNTING
7654 OPERATIONS
7654 SALES
7654 RESEARCH
7698 ACCOUNTING
7698 OPERATIONS
7698 SALES
7698 RESEARCH
7782 ACCOUNTING
7782 OPERATIONS
7782 SALES
7782 RESEARCH
7788 ACCOUNTING
7788 OPERATIONS
7788 SALES
7788 RESEARCH
7839 ACCOUNTING
7839 OPERATIONS
7839 SALES
7839 RESEARCH
7844 ACCOUNTING
7844 OPERATIONS
7844 SALES
7844 RESEARCH
7876 ACCOUNTING
7876 OPERATIONS
7876 SALES
7876 RESEARCH
7900 ACCOUNTING
7900 OPERATIONS
7900 SALES
7900 RESEARCH
7902 ACCOUNTING
7902 OPERATIONS
7902 SALES
7902 RESEARCH
7934 ACCOUNTING
7934 OPERATIONS
7934 SALES
7934 RESEARCH
7534 ACCOUNTING
7534 OPERATIONS
7534 SALES
7534 RESEARCH
Time taken: 21.636 seconds, Fetched: 60 row(s)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263641.4 排序
1.4.1 全局排序（Order By）
Order By：全局排序，只有一個 Reducer
（1）使用 ORDER BY 子句排序
ASC(ascend)：升序（默認）
DESC(descend)：降序
（2）ORDER BY 子句在 SELECT語句的結尾
案例實操：
查詢員工信息按工資升序排列
hive (default)> select * from emp order by sal limit 5;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
Time taken: 30.52 seconds, Fetched: 5 row(s)
123456789查詢員工信息按工資降序排列
hive (default)> select * from emp order by sal desc limit 5;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
Time taken: 10.401 seconds, Fetched: 5 row(s)
1234567891.4.2 按照別名排序
按照員工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal limit 5;
OK
ename twosal
SMITH 1600.0
JAMES 1900.0
ADAMS 2200.0
WARD 2500.0
MARTIN 2500.0
Time taken: 9.991 seconds, Fetched: 5 row(s)
1234567891.4.3 多個列排序
按照部門和工資升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal limit 5;
OK
ename deptno sal
MILLER 10 1300.0
CLARK 10 2450.0
KING 10 5000.0
SMITH 20 800.0
ADAMS 20 1100.0
Time taken: 9.81 seconds, Fetched: 5 row(s)
1234567891.4.4 每個 Reduce內部排序（Sort By）
Sort By：對於大規模的數據集 order by的效率非常低。在很多情況下並不需要全局排序，此時可以使用 sort by。Sort by為每個reducer產生一個排序文件。 每個Reducer內部進行排序，對全局結果集來說不是排序。
（1）設置 reduce個數並查看
hive (default)> set mapreduce.job.reduces;
mapreduce.job.reduces=-1
hive (default)> set mapreduce.job.reduces=3;
hive (default)> set mapreduce.job.reduces;
mapreduce.job.reduces=3
12345（2）根據部門編號降序查看員工信息
hive (default)> select ename, deptno from emp sort by deptno desc;
OK
ename deptno
MILLER 50
MARTIN 30
TURNER 30
BLAKE 30
SCOTT 20
CLARK 10
KING 10
WARD 30
ALLEN 30
JAMES 30
JONES 20
ADAMS 20
MILLER 10
FORD 20
SMITH 20
Time taken: 15.318 seconds, Fetched: 15 row(s)
12345678910111213141516171819（3）將查詢結果導入到文件中（按照部門編號降序排序）
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/sortby-result'
> select ename, deptno from emp sort by deptno desc;
121.4.5 分區（Distribute By）
Distribute By：在有些情況下，我們需要控制某個特定行應該到哪個 reducer，通常是為了進行後續的聚集操作。 distribute by 子句可以做這件事 。 distribute by 類似 MR 中 partition（自定義分區 ），進行分區，結合 sort by 使用。
對於 distribute by 進行測試，一定要分配多 reduce 進行處理，否則無法看到 distribute by 的效果。
例：先按照部門編號分區，再按照員工編號降序排序。
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/distribute-result'
> select * from emp distribute by deptno sort by empno desc;
12注意：distribute by 的分區規則是根據分區欄位的 hash 碼與 reduce 的個數進行模除後，餘數相同的分到一個區 。Hive 要求 DISTRIBUTE BY 語句要寫在 SORT BY 語句之前。
1.4.6 Cluster By
當 distribute by 和 sorts by 欄位相同時，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外還兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序規則為 ASC 或者 DESC。
以下兩種寫法等價：
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
12注意：按照部門編號分區，不一定就是固定死的數值，可以是 20 號和 30 號部門分到一個分區裡面去。
2 分區表和分桶表
2.1 分區表
分區表實際上就是對應一個 HDFS 文件系統上的獨立的文件夾，該文件夾下是該分區所有的數據文件。 Hive 中的分區就是分目錄，把一個大的數據集根據業務需要分割成小的數據集。在查詢時通過 WHERE 子句中的表達式選擇查詢所需要的指定的分區，這樣的查詢效率會提高很多。
2.1.1 分區表基本操作
1. 創建分區表
hive (default)> create table if not exists dept_partition(
> deptno int, dname string, loc string
> )
> partitioned by (day string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.238 seconds
1234567注意：分區欄位不能是表中已經存在的數據，可以將分區欄位看作表的偽列。
2. 加載數據到分區表中
（1）數據準備：
dept1.txt：
10 ACCOUNTING 1700
20 RESEARCH 1800
dept2.txt：
30 SALES 1900
40 OPERATIONS 1700
dept3.txt：
50 TEST 2000
60 DEV 1900
1234567891011（2）加載數據：
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept1.txt' into table dept_partition
> partition(day='20210901');
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept2.txt' into table dept_partition
> partition(day='20210902');
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept3.txt' into table dept_partition
> partition(day='20210903');
12345678注意：分區表加載數據時，必須指定分區
3. 查詢分區表中數據
單分區查詢
dept_partition.deptno dept_partition.dname dept_partition.loc dept_partition.day
10 ACCOUNTING 1700 20210901
20 RESEARCH 1800 20210901
123多分區聯合查詢
hive (default)> select * from dept_partition where day='20210901'
> union
> select * from dept_partition where day='20210902'
> union
> select * from dept_partition where day='20210903';
hive (default)> select * from dept_partition where day='20210901' or day='20210902' or day='20210903';
12345674. 增加分區
創建單個分區
hive (default)> alter table dept_partition add partition(day='20210904');
OK
Time taken: 0.348 seconds
123同時創建多個分區
hive (default)> alter table dept_partition add partition(day='20210905') partition(day='20210906');
OK
Time taken: 3.758 seconds
1235.刪除分區
刪除單個分區
hive (default)> alter table dept_partition drop partition(day='20210906');
Dropped the partition day=20210906
OK
Time taken: 1.283 seconds
1234同時刪除多個分區
hive (default)> alter table dept_partition drop partition(day='20210904'), partition(day='20210905');
Dropped the partition day=20210904
Dropped the partition day=20210905
OK
Time taken: 0.764 seconds
123456.查看分區表有多少分區
hive (default)> show partitions dept_partition;
OK
partition
day=20210901
day=20210902
day=20210903
Time taken: 0.636 seconds, Fetched: 3 row(s)
12345677. 查看分區表結構
hive (default)> desc formatted dept_partition;
# Partition Information
# col_name data_type comment
day string
12342.1.2 二級分區
思考：如果一天的日誌數據量也很大，如何再將數據拆分?
1. 創建二級分區表
hive (default)> create table dept_partition2(
> dept int, dname string, loc string
> )
> partitioned by (day string, hour string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.209 seconds
12345672. 正常的加載數據
（1）加載數據到二級分區表中
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept1.txt' into table dept_partition2
> partition(day='20210901', hour='16');
Loading data to table default.dept_partition2 partition (day=20210901, hour=16)
OK
Time taken: 1.622 seconds
12345（2）查詢分區數據
hive (default)> select * from dept_partition2 where day='20210901' and hour='16';
OK
dept_partition2.dept dept_partition2.dname dept_partition2.loc dept_partition2.day dept_partition2.hour
10 ACCOUNTING 1700 20210901 16
20 RESEARCH 1800 20210901 16
Time taken: 3.496 seconds, Fetched: 2 row(s)
1234563. 把數據直接上傳到分區目錄上，讓分區表和數據產生關聯的三種方式
（1）方式一：上傳數據後修復
上傳數據
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/day=20210901/hour=17;
hive (default)> dfs -put /opt/module/hive-3.1.2/data/dept1.txt /user/hive/warehouse/dept_partition2/day=20210901/hour=17;
12查詢數據（查詢不到剛上傳的數據）
hive (default)> select * from dept_partition2 where day='20210901' and hour='17';
OK
dept_partition2.dept dept_partition2.dname dept_partition2.loc dept_partition2.day dept_partition2.hour
Time taken: 0.431 seconds
1234執行修復命令
hive (default)> msck repair table dept_partition2;
OK
Partitions not in metastore: dept_partition2:day=20210901/hour=17
Repair: Added partition to metastore dept_partition2:day=20210901/hour=17
Time taken: 0.314 seconds, Fetched: 2 row(s)
12345再次查詢數據
hive (default)> select * from dept_partition2 where day='20210901' and hour='17';
OK
dept_partition2.dept dept_partition2.dname dept_partition2.loc dept_partition2.day dept_partition2.hour
10 ACCOUNTING 1700 20210901 17
20 RESEARCH 1800 20210901 17
Time taken: 0.34 seconds, Fetched: 2 row(s)
123456（2）方式二：上傳數據後添加分區
上傳數據
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/day=20210901/hour=18;
hive (default)> dfs -put /opt/module/hive-3.1.2/data/dept1.txt /user/hive/warehouse/dept_partition2/day=20210901/hour=18;
12執行添加分區
hive (default)> alter table dept_partition2 add partition(day='20210901', hour='18');
OK
Time taken: 0.198 seconds
123查詢數據
hive (default)> select * from dept_partition2 where day='20210901' and hour='18';
OK
dept_partition2.dept dept_partition2.dname dept_partition2.loc dept_partition2.day dept_partition2.hour
10 ACCOUNTING 1700 20210901 18
20 RESEARCH 1800 20210901 18
Time taken: 0.438 seconds, Fetched: 2 row(s)
123456（3）方式三：創建文件夾後 load 數據到分區
創建目錄
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/day=20210901/hour=19;
1上傳數據
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/dept1.txt' into table dept_partition2
> partition(day='20210901',hour='19');
Loading data to table default.dept_partition2 partition (day=20210901, hour=19)
OK
Time taken: 0.778 seconds
12345查詢數據
hive (default)> select * from dept_partition2 where day='20210901' and hour='19';
OK
dept_partition2.dept dept_partition2.dname dept_partition2.loc dept_partition2.day dept_partition2.hour
10 ACCOUNTING 1700 20210901 19
20 RESEARCH 1800 20210901 19
Time taken: 0.313 seconds, Fetched: 2 row(s)
1234562.1.3 動態分區調整
關係型資料庫中，對分區表 Insert數據時候，資料庫自動會根據分區欄位的值，將數據插入到相應的分區中，Hive中也提供了類似的機制，即動態分區 (Dynamic Partition），只不過，使用 Hive的動態分區，需要進行相應的配置。
1. 開啟動態分區參數設置
（1）開啟動態分區功能（默認 true ，開啟）
hive (default)> set hive.exec.dynamic.partition=true;
1（2）設置為非嚴格模式（動態分區的模式，默認 strict ，表示必須指定至少一個分區為靜態分區， nonstrict 模式表示允許所有的分區欄位都可以使用動態分區。）
hive (default)> set hive.exec.dynamic.partition.mode=nonstrict;
1（3）在所有執行 MR 的節點上，最大一共可以創建多少個動態分區。默認 1000
hive (default)> set hive.exec.max.dynamic.partitions=1000;
1（4）在每個執行 MR 的節點上，最大可以創建多少個動態分區。 該參數需要根據實際的數據來設定。比如：源數據中包含了一年的數據，即 day 欄位有 365 個值，那麼該參數就需要設置成大於 365 ，如果使用默認值 100 ，則會報錯。
hive (default)> set hive.exec.max.dynamic.partitions.pernode=100;
1（5）整個 MR Job 中，最大可以創建多少個 HDFS 文件。默認 100000
hive (default)> set hive.exec.max.created.files=100000;
1（6）當有空分區生成時，是否拋出異常。一般不需要設置。默認 false
hive (default)> set hive.error.on.empty.partition=false;
12. 案例實操
需求：將 dept 表中的數據按照地區（loc欄位）插入到目標表 dept_partition 的相應分區中。
（1）創建目標分區表
hive (default)> create table dept_partition_by(id int, name string)
> partitioned by (loc int)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.097 seconds
12345（2）設置動態分區
hive (default)> set hive.exec.dynamic.partition.mode = nonstrict;
hive (default)> insert into table dept_partition_by partition(loc)
> select deptno, dname, loc from dept;
123（3）查看目標分區表的分區情況
hive (default)> show partitions dept_partition_by;
OK
partition
loc=1700
loc=1800
loc=1900
Time taken: 0.152 seconds, Fetched: 3 row(s)
12345672.2 分桶表
分區提供一個隔離數據和優化查詢的便利方式。不過，並非所有的數據集都可形成合理的分區。 對於一張表或者分區， Hive 可以進一步組織成桶，也就是更為細粒度的數據範圍劃分 。
分桶是將數據集分解成更容易管理的若干部分的另一個技術。
分區針對的是數據的存儲路徑；分桶針對的是數據文件。
1. 創建分桶表
（1）數據準備
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16
12345678910111213141516（2）創建分桶表
hive (default)> create table stu_buck(id int, name string)
> clustered by(id)
> into 4 buckets
> row format delimited fields terminated by '\t';
OK
Time taken: 0.132 seconds
123456（3）查看表結構
hive (default)> desc formatted stu_buck;
Num Buckets: 4
12（4）導入數據到分桶表中，load的方式
hive (default)> load data inpath '/student/data/student.txt' into table stu_buck;
1（5）查看創建的分桶表中是否分成 4 個桶
（6）查詢分桶的數據
hive (default)> select * from stu_buck;
OK
stu_buck.id stu_buck.name
1016 ss16
1012 ss12
1008 ss8
1004 ss4
1013 ss13
1009 ss9
1005 ss5
1001 ss1
1014 ss14
1010 ss10
1006 ss6
1002 ss2
1015 ss15
1011 ss11
1007 ss7
1003 ss3
Time taken: 0.274 seconds, Fetched: 16 row(s)
1234567891011121314151617181920（7）分桶規則：
根據結果可知：Hive 的分桶採用對分桶欄位的值進行哈希，然後除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。
2. 分桶表操作需要注意的事項 :
（1）reduce 的個數設置為 1, 讓 Job 自行決定需要用多少個 reduce，或者將 reduce 的個數設置為大於等於分桶表的桶數。
（2）從 hdfs 中 load 數據到分桶表中，避免本地文件找不到問題。
（3）不要使用本地模式。
3. insert方式將數據導入分桶表
hive (default)> insert into table stu_buck select * from student_insert;
12.3 抽樣查詢
對於非常大的數據集，有時用戶需要使用的是一個具有代表性的查詢結果而不是全部結果。 Hive 可以通過對表進行抽樣來滿足這個需求。
語法：TABLESAMPLE(BUCKET x OUT OF y)
查詢表stu_buck 中的數據。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
OK
stu_buck.id stu_buck.name
1016 ss16
1004 ss4
1009 ss9
1002 ss2
1003 ss3
Time taken: 0.161 seconds, Fetched: 5 row(s)
123456789若總共有n桶，則抽取n/y桶數據，所以y需要是n的因子或倍數。從第x桶開始抽。本例中抽取4/4=1桶數據，從第1桶開始抽。
注意：x 的值必須小於等於 y 的值，否則
hive (default)> select * from stu_buck tablesample(bucket 4 out of 1 on id);
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample cl（四）函數、壓縮和存儲
1 函數
1.1 系統內置函數
1. 查看系統自帶的函數
hive (default)> show functions;
12. 顯示自帶的函數的用法
hive (default)> desc function upper;
13. 詳細顯示自帶的函數的用法
hive (default)> desc function extended upper;
11.2 常用內置函數
1.2.1 空欄位賦值
（1）函數說明
NVL：給值為 NULL的數據賦值，它的格式是 NVL(value，default_value)。它的功能是如果 value為 NULL，則 NVL函數返回 default_value 的值，否則返回 value 的值。如果兩個參數都為 NULL，則返回 NULL。
（2）數據準備：採用員工表
（3）查詢：如果員工的 comm為 NULL，則用 -1代替
hive (default)> select comm, nvl(comm, -1) from emp;
OK
comm _c1
NULL -1.0
300.0 300.0
500.0 500.0
NULL -1.0
1400.0 1400.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
0.0 0.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
Time taken: 2.891 seconds, Fetched: 15 row(s)
12345678910111213141516171819（4）查詢：如果員工的 comm 為 NULL，則用領導 id 代替
hive (default)> select comm, nvl(comm, mgr) from emp;
OK
comm _c1
NULL 7902.0
300.0 300.0
500.0 500.0
NULL 7839.0
1400.0 1400.0
NULL 7839.0
NULL 7839.0
NULL 7566.0
NULL NULL
0.0 0.0
NULL 7788.0
NULL 7698.0
NULL 7566.0
NULL 7782.0
NULL 7782.0
1234567891011121314151617181.2.2 CASE WHEN THEN ELSE END
（1）數據準備（人名來自電視劇《將夜》）
name dept_id sex
夫子 A 男
觀主 B 男
李慢慢 A 男
余簾 A 女
葉紅魚 B 女
君陌 A 男
葉青 B 男
寧缺 A 男
木柚 A 女
12345678910（2）需求：求出不同部門男女各多少人。
（3）創建 hive 表並導入數據
hive (default)> create table emp_sex(
> name string,
> dept_id string,
> sex string)
> row format delimited fields terminated by "\t";
OK
Time taken: 0.64 seconds
1234567（4）按需求查詢數據
hive (default)> select dept_id,
> sum(case sex when '男' then 1 else 0 end) male_count,
> sum(case sex when '女' then 1 else 0 end) female_count
> from emp_sex
> group by dept_id;
OK
dept_id male_count female_count
A 4 2
B 2 1
Time taken: 9.155 seconds, Fetched: 2 row(s)
123456789101.2.3 行轉列
1. 相關函數說明
CONCAT(string A/col, string B/col…）：返回輸入字符串連接後的結果，支持任意個輸入字符串 ；
CONCAT_WS(separator, str1, str2,…)：它是一個特殊形式的 CONCAT()。第一個參數表示剩餘參數間的分隔符。分隔符可以是與剩餘參數一樣的字符串。如果分隔符是 NULL ，返回值也將為 NULL 。這個函數會跳過分隔符參數後的任何 NULL 和空字符串。分隔符將被加到被連接的字符串之間;
注意: CONCAT_WS must be string or array<string>
COLLECT_SET(col)：函數隻接受基本數據類型，它的主要作用是將某欄位的值進行去重匯總，產生 Array類型欄位。
2. 數據準備（人名來自於電視劇《將夜》）
name constellation blood_type
柳白 白羊座 A
講經首座 射手座 A
陳某 白羊座 B
陳皮皮 白羊座 A
莫山山 射手座 A
熊初墨 白羊座 B
柯浩然 射手座 A
123456783. 需求
把星座和血型一樣的人歸類到一起。
4. 創建hive表並導入數據
hive (default)> create table person_info(
> name string,
> constellation string,
> blood_type string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.848 seconds
hive (default)> load data local inpath "/opt/module/hive-3.1.2/data/person_info.txt" into table person_info;
Loading data to table default.person_info
OK
Time taken: 0.682 seconds
1234567891011125. 按需求查詢數據
hive (default)> select t1.c_b, concat_ws("|", collect_set(t1.name))
> from (select name,concat_ws(',', constellation,blood_type) c_b
> from person_info) t1
> group by t1.c_b;
OK
t1.c_b _c1
射手座,A 講經首座|莫山山|柯浩然
白羊座,A 柳白|陳皮皮
白羊座,B 陳某|熊初墨
Time taken: 24.49 seconds, Fetched: 3 row(s)
123456789101.2.4 列轉行
1. 函數說明
EXPLODE( 將 hive 一 列中複雜的 Array 或者 Map 結構拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用於和 split, explode 等 UDTF 一起使用 它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。
2. 數據準備
movies category
《流浪地球》 懸疑,動作,科幻,劇情
《警察故事》 懸疑,警匪,動作,心理,劇情
《戰狼2》 戰爭,動作,災難
12343. 需求：將電影分類中的數組數據展開。
4. 創建 hive 表並導入數據
hive (default)> create table movie_info(
> movie string,
> category string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.143 seconds
hive (default)> load data local inpath "/opt/module/hive-3.1.2/data/movie_info.txt" into table movie_info;
Loading data to table default.movie_info
OK
Time taken: 0.518 seconds
12345678910115. 按需求查詢數據
hive (default)> select movie, category_name
> from movie_info
> lateral view
> explode(split(category, ",")) movie_info_tmp as category_name;
OK
movie category_name
《流浪地球》 懸疑
《流浪地球》 動作
《流浪地球》 科幻
《流浪地球》 劇情
《警察故事》 懸疑
《警察故事》 警匪
《警察故事》 動作
《警察故事》 心理
《警察故事》 劇情
《戰狼2》 戰爭
《戰狼2》 動作
《戰狼2》 災難
Time taken: 0.132 seconds, Fetched: 12 row(s)
123456789101112131415161718191.2.5 窗口函數（開窗函數）
1. 相關函數說明
OVER()：指定分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變而變化。
CURRENT ROW：當前行
n PRECEDING ：往前 n 行數據
n FOLLOWING ：往後 n 行數據
UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點，UNBOUNDED FOLLOWING 表示到後面的終點。
LAG(col,n,default_val)：往前第 n 行數據
LEAD(col,n,default_val ))：往後第 n 行數據
NTILE(n)：把有序窗口的行分發到指定數據的組中，各個組有編號，編號從 1 開始，對於每一行， NTILE 返回此行所屬的組的編號。 注意： n 必須為 int 類型。
2. 數據準備： name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
12345678910111213143. 創建 hive表並導入數據
hive (default)> create table business(
> name string,
> orderdate string,
> cost int
> ) row format delimited fields terminated by ',';
OK
Time taken: 0.864 seconds
hive (default)> load data local inpath "/opt/module/hive-3.1.2/data/business.txt" into table business;
Loading data to table default.business
OK
Time taken: 0.777 seconds
1234567891011124. 按需求查詢數據
（1）查詢在 2017 年 4 月份購買過的顧客及總人數
hive (default)> select name, count(*) over()
> from business
> where substring(orderdate,1,7)='2017-04'
> group by name;
OK
name count_window_0
jack 2
mart 2
123456789（2）查詢顧客的購買明細及月購買總額
hive (default)> select name, orderdate, cost, sum(cost) over(partition by month(orderdate))
> from business;
OK
name orderdate cost sum_window_0
jack 2017-01-01 10 205
tony 2017-01-02 15 205
tony 2017-01-04 29 205
jack 2017-01-05 46 205
tony 2017-01-07 50 205
jack 2017-01-08 55 205
jack 2017-02-03 23 23
mart 2017-04-13 94 341
mart 2017-04-08 62 341
mart 2017-04-09 68 341
mart 2017-04-11 75 341
jack 2017-04-06 42 341
neil 2017-05-10 12 12
neil 2017-06-12 80 80
Time taken: 31.353 seconds, Fetched: 14 row(s)
12345678910111213141516171819（3）將每個顧客的 cost 按照日期進行累加
hive (default)> select name, orderdate, cost,
> sum(cost) over() as sample,
> sum(cost) over(partition by name) as sample2,
> sum(cost) over(partition by name order by orderdate) as sample3,
> sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4,
> sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5,
> sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6,
> sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7
> from business;
OK
name orderdate cost sample sample2 sample3 sample4 sample5 sample6 sample7
jack 2017-01-01 10 661 176 10 10 10 56 176
jack 2017-01-05 46 661 176 56 56 56 111 166
jack 2017-01-08 55 661 176 111 111 101 124 120
jack 2017-02-03 23 661 176 134 134 78 120 65
jack 2017-04-06 42 661 176 176 176 65 65 42
mart 2017-04-08 62 661 299 62 62 62 130 299
mart 2017-04-09 68 661 299 130 130 130 205 237
mart 2017-04-11 75 661 299 205 205 143 237 169
mart 2017-04-13 94 661 299 299 299 169 169 94
neil 2017-05-10 12 661 92 12 12 12 92 92
neil 2017-06-12 80 661 92 92 92 92 92 80
tony 2017-01-02 15 661 94 15 15 15 44 94
tony 2017-01-04 29 661 94 44 44 44 94 79
tony 2017-01-07 50 661 94 94 94 79 79 50
Time taken: 26.212 seconds, Fetched: 14 row(s)
1234567891011121314151617181920212223242526sample表示所有行相加，sample2表示按name分組，組內數據相加，sample3表示按name分組，組內數據累加，sample4和sample3一樣，由起點到當前行的聚合，sample5表示當前行和前面一行聚合，sample表示當前行和前邊一行及後面一行聚合，sample7表示當前行及後面所有行聚合。
rows必須跟在 order by 子句之後，對排序的結果進行限制，使用固定的行數來限制分區中的數據行數量。
（4）查看顧客上次的購買時間
hive (default)> select name, orderdate, cost,
> lag(orderdate, 1, '1900-01-01') over(partition by name order by orderdate)
> as time1, lag(orderdate, 2) over (partition by name order by orderdate) as time2
> from business;
OK
name orderdate cost time1 time2
jack 2017-01-01 10 1900-01-01 NULL
jack 2017-01-05 46 2017-01-01 NULL
jack 2017-01-08 55 2017-01-05 2017-01-01
jack 2017-02-03 23 2017-01-08 2017-01-05
jack 2017-04-06 42 2017-02-03 2017-01-08
mart 2017-04-08 62 1900-01-01 NULL
mart 2017-04-09 68 2017-04-08 NULL
mart 2017-04-11 75 2017-04-09 2017-04-08
mart 2017-04-13 94 2017-04-11 2017-04-09
neil 2017-05-10 12 1900-01-01 NULL
neil 2017-06-12 80 2017-05-10 NULL
tony 2017-01-02 15 1900-01-01 NULL
tony 2017-01-04 29 2017-01-02 NULL
tony 2017-01-07 50 2017-01-04 2017-01-02
1234567891011121314151617181920time1表示顧客上次購買的時間，沒有的話用』1900-01-01』代替，time2表示顧客前兩次購買的時間。
（5）查詢前 20% 時間的訂單信息
hive (default)> select * from(
> select name, orderdate, cost, ntile(5) over(order by orderdate) sorted
> from business
> ) t
> where sorted = 1;
OK
t.name t.orderdate t.cost t.sorted
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
12345678910將數據按時間分為5個組，取第一個組的數據。
1.2.6 Rank
1. 函數說明
RANK()：排序相同時會重複，總數不會變
DENSE_RANK()：排序相同時會重複，總數會減少
ROW_NUMBER()：會根據順序計算
2. 數據準備（人名來自電視劇《將夜》）
柯浩然 語文 87
柯浩然 數學 95
柯浩然 英語 68
余簾 語文 94
余簾 數學 58
余簾 英語 84
顏瑟 語文 64
顏瑟 數學 86
顏瑟 英語 84
王景略 語文 65
王景略 數學 85
王景略 英語 78
1234567891011123. 創建 hive 表並導入數據
hive (default)> create table score(
> name string,
> subject string,
> score int)
> row format delimited fields terminated by "\t";
OK
Time taken: 0.672 seconds
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/score.txt' into table score;
Loading data to table default.score
OK
Time taken: 0.48 seconds
1234567891011124. 計算每門學科成績排名
hive (default)> select name, subject, score,
> rank() over(partition by subject order by score desc) rp,
> dense_rank() over(partition by subject order by score desc) drp,
> row_number() over(partition by subject order by score desc) rmp
> from score;
OK
name subject score rp drp rmp
柯浩然 數學 95 1 1 1
顏瑟 數學 86 2 2 2
王景略 數學 85 3 3 3
余簾 數學 58 4 4 4
余簾 英語 84 1 1 1
顏瑟 英語 84 1 1 2
王景略 英語 78 3 2 3
柯浩然 英語 68 4 3 4
余簾 語文 94 1 1 1
柯浩然 語文 87 2 2 2
王景略 語文 65 3 3 3
顏瑟 語文 64 4 4 4
Time taken: 18.441 seconds, Fetched: 12 row(s)
12345678910111213141516171819201.3 自定義函數
（1）Hive 自帶了一些函數，比如 max/min等，但是數量有限，自己可以通過自定義 UDF 來方便的擴展。
（2）當 Hive提供的內置函數無法滿足你的業務處理需要時 此時就可以考慮使用用戶自定義函數（UDF：user-defined function）。
（3）根據用戶自定義函數類別分為以下三種：
UDF(User-Defined-Function)：一進一出
UDAF(User-Defined Aggregation Function)：聚集函數，多進一出，類似於 count/max/min
UDTF(User-Defined Table-Generating Functions)：一進多出，如 lateral view explode()
（4）官方文檔地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
（5）編程步驟：
1）繼承 Hive 提供的類
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
2）實現類中的抽象方法
3）在 hive 的命令行窗口創建函數
添加jar，add jar linux_jar_path
創建function，create [temporary] function [dbname.]function_name AS class_name;
4）在 hive 的命令行窗口刪除函數
drop [temporary] function [if exists]
[dbname.]function_name;
1.4 自定義 UDF函數
1. 需求：自定義一個UDF 實現計算給定字符串的長度
如：
hive(default)> select my_len("abcd")
4
122. 創建一個 Maven 工程 Hive
3. 導入依賴
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
12345674. 創建一個類
package com.Tom.udf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
public class MyUDF extends GenericUDF {
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
// 判斷輸入參數的個數
if(arguments.length != 1){
throw new UDFArgumentLengthException("Input Args Length Error!!!");
}
// 判斷輸入參數的類型
if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
throw new UDFArgumentTypeException(0, "Input Args Type Error!!!");
}
//函數本身返回值為int，需要返回int類型的鑑別器對象
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
if(arguments[0].get() == null){
return 0;
}
return arguments[0].get().toString().length();
}
@Override
public String getDisplayString(String[] children) {
return "";
}
}
12345678910111213141516171819202122232425262728293031323334353637383940415. 打成 jar包上傳到伺服器 /opt/module/hive-3.1.2/myudf.jar
6. 將 jar包添加到 hive 的 classpath
hive (default)> add jar /opt/module/hive-3.1.2/hiveDemo-1.0-SNAPSHOT.jar;
17. 創建臨時函數與開發好的 java class 關聯
hive (default)> create temporary function my_len as "com.Tom.udf.MyUDF";
18. 即可在 hql中使用自定義的函數
hive (default)> select ename, my_len(ename) ename_len from emp;
OK
ename ename_len
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
JAMES 5
FORD 4
MILLER 6
MILLER 6
Time taken: 0.807 seconds, Fetched: 15 row(s)
123456789101112131415161718191.5 自定義 UDTF 函數
1. 需求：自定義一個UDTF 實現將一個任意分割符的字符串切割成獨立的單詞
例如：
hive(default)> select myudtf ("hello,world,hadoop,hive",",");
hello
world
hadoop
hive
123452. 代碼實現
package com.Tom.udtf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.List;
public class MyUDTF extends GenericUDTF {
private ArrayList<String> outList = new ArrayList<String>();
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
// 1.定義輸出數據的列名和類型
List<String> fieldsNames = new ArrayList<String>();
List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
//2.添加輸出數據的列名和類型
fieldsNames.add("lineToWord");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldsNames, fieldOIs);
}
@Override
public void process(Object[] args) throws HiveException {
//1. 獲取原始數據
String arg = args[0].toString();
//2.獲取數據傳入的第二個參數，此處為分隔符
String splitKey = args[1].toString();
//3.將原始數據按照傳入的分隔符進行切分
String[] fields = arg.split(splitKey);
//4.遍歷切分後的結果，並寫出
for (String field : fields){
//集合為復用的， 首先清空集合
outList.clear();
// 將每一個單詞添加至集合
outList.add(field);
//將集合內容寫出
forward(outList);
}
}
@Override
public void close() throws HiveException {
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758593. 打成 jar包上傳到伺服器 /opt/module/hive-3.1.2/myudf.jar
4. 將 jar包添加到 hive 的 classpath下
hive (default)> add jar /opt/module/hive-3.1.2/myudtf.jar;
15. 創建臨時函數與開發好的 java class 關聯
hive (default)> create temporary function myudtf as "com.Tom.udtf.MyUDTF";
16. 使用自定義的函數
hive (default)> select myudtf("hello, world, hadoop, hive", ",");
OK
linetoword
hello
world
hadoop
hive
Time taken: 0.704 seconds, Fetched: 4 row(s)
123456782 壓縮和存儲
2.1 Hadoop 壓縮配置
1. MR支持的壓縮編碼
為了支持多種壓縮/解壓縮算法，Hadoop引入了編碼/解碼器，如下表所示：
|
壓縮格式 |
對應的編碼/解碼器 |
|
DEFLATE |
org.apache.hadoop.io.compress.DefaultCodec |
|
gzip |
org.apache.hadoop.io.compress.GzipCodec |
|
bzip2 |
org.apache.hadoop.io.compress.BZip2Codec |
|
LZO |
com.hadoop.compression.lzo.LzopCodec |
|
Snappy |
org.apache.hadoop.io.compress.SnappyCodec |
壓縮性能的比較：
2. 壓縮參數配置
要在 Hadoop 中啟用壓縮 可以配置如下參數（ mapred-site.xml文件中）：
2.2 開啟 Map 輸出階段壓縮（MR引擎）
開啟 map 輸出階段壓縮可以減少 job 中 map 和 Reduce task 間數據傳輸量。具體配置如下：
案例實操
（1）開啟 hive 中間傳輸數據壓縮功能
hive (default)> set hive.exec.compress.intermediate=true;
1（2）開啟 mapreduce中 map輸出壓縮功能
hive (default)> set mapreduce.map.output.compress=true;
1（3）設置 mapreduce中 map輸出數據的壓縮方式
hive (default)> set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
1（4）執行查詢語句
hive (default)> select count(ename) name from emp;
OK
name
15
Time taken: 33.563 seconds, Fetched: 1 row(s)
123452.3 開啟 Reduce輸出階段壓縮
當 Hive 將輸出寫入到表中時，輸出內容同樣可以進行壓縮。屬性hive.exec.compress.output
控制著這個功能。用戶可能需要保持默認設置文件中的默認值 false，這樣默認的輸出就是非壓縮的純文本文件了。用戶可以通過在查詢語句或執行腳本中設置這個值為 true ，來開啟輸出結果壓縮功能。
案例實操
（1）開啟 hive 最終輸出數據壓縮功能
hive (default)> set hive.exec.compress.output=true;
1（2）開啟 mapreduce 最終輸出數據壓縮
hive (default)> set mapreduce.output.fileoutputformat.compress=true;
1（3）設置 mapreduce 最終數據輸出壓縮方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
1（4）設置 mapreduce 最終數據輸出壓縮為塊壓縮
hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;
1（5）測試一下輸出結果是否是壓縮文件
hive (default)> insert overwrite local directory
> '/opt/module/hive-3.1.2/data/distribute-result' select * from emp distribute by deptno sort by empno desc;
12[Tom@hadoop102 distribute-result]$ ll
總用量 4
-rw-r--r--. 1 Tom Tom 470 9月 5 10:43 000000_0.snappy
1232.4 文件存儲格式
1. 列式存儲和行式存儲
如圖所示左邊為邏輯表，右邊第一個為行式存儲，第二個為列式存儲。
（1）行存儲的特點
查詢滿足條件的一整行數據的時候，列存儲則需要去每個聚集的欄位找到對應的每個列的值，行存儲只需要找到其中一個值，其餘的值都在相鄰地方，所以此時行存儲查詢的速度更快。
（2）列存儲的特點
因為每個欄位的數據聚集存儲，在查詢只需要少數幾個欄位的時候，能大大減少讀取的數據量；每個欄位的數據類型一定是相同的，列式存儲可以針對性的設計更好的設計壓縮算法。
TEXTFILE和 SEQUENCEFILE 的存儲格式都是基於行存儲的；
ORC 和 PARQUET 是基於列式存儲的。
2. TextFile格式
默認格式，數據不做壓縮，磁碟開銷大，數據解析開銷大。可結合Gzip、Bzip2使用，但使用 Gzip 這種方式，hive 不會對數據進行切分，從而無法對數據進行並行操作。
3. Orc 格式
Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存儲格式。
如下圖所示，可以看到每個Orc文件由1個或多個stripe組成，每個stripe一般為 HDFS 的塊大小，每一個stripe 包含多條記錄，這些記錄按照列進行獨立存儲，對應到 Parquet 中的 row group 的概念。每個Stripe 里有三部分組成，分別是 Index Data，Row Data，Stripe Footer：
（1）Index Data ：一個輕量級的 index ，默認是 每隔 1W 行做一個索引 。這裡做的索引應該只是記錄某行的各欄位在 Row Data 中的 offset 。
（2）Row Data ：存的是具體的數據，先取部分行，然後對這些行按列進行存儲 。 對每個列進行了編碼，分成多個 Stream 來存儲 。
（3）Stripe Footer ：存的是各個 Stream 的類型，長度等信息。
每個文件有一個 File Footer ，這裡面存的是每個 Stripe 的行數，每個 Column 的數據類型信息等；每個文件的尾部是一個 PostScript ，這裡面記錄了整個文件的壓縮類型以及 FileFooter 的長度信息等。在讀取文件時，會 seek 到文件尾部讀 PostScript ，從裡面解析到 File Footer 長度，再讀 FileFooter ，從裡面解析到各個
Stripe 信息，再讀各個 Stripe ，即從後往前讀。
4. Parquet格式
Parquet 文件是以二進位方式存儲的，所以是不可以直接讀取的，文件中包括該文件的數據和元數據， 因此 Parquet 格式文件是自解析的。
（1）行組 (Row Group)：每一個行組包含一定的行數，在一個 HDFS 文件中至少存儲一個行組 ，類似於 orc 的 stripe 的概念。
（2）列塊 (Column Chunk)：在一個行組中每一列保存在一個列塊中，行組中的所有列連續的存儲在這個行組文件中。一個列塊中的值都是相同類型的，不同的列塊可能使用不同的算法進行壓縮。
（3）頁 ( Page)：每一個列塊劃分為多個頁，一個頁是最小的編碼的單位，在同一個列塊的不同頁可能使用不同的編碼方式。
通常情況下，在存儲 Parquet 數據的時候會按照 Block 大小設置行組的大小，由於一般情況下每一個 Mapper 任務處理數據的最小單位是一個 Block ，這樣可以把 每一個行組由一個 Mapper 任務處理，增大任務執行並行度 。 Parquet 文件的格式：
上圖展示了一個 Parquet 文件的內容，一個文件中可以存儲多個行組，文件的首位都是該文件的 Magic Code ，用於校驗它是否是一個 Parquet 文件， Footer length 記錄了文件元數據的大小，通過該值和文件長度可以計算出元數據的偏移量，文件的元數據中包括每一個行組的元數據信息和該文件存儲數據的Schema
信息。除了文件中每一個行組的元數據，每一頁的開始都會存儲該頁的元數據，在Parquet 中，有三種類型的頁：數據頁、字典頁和索引頁。數據頁用於存儲當前行組中該列的值，字典頁存儲該列值的編碼字典，每一個列塊中最多包含一個字典頁，索引頁用來存儲當前行組下該列的索引，目前Parquet 中還不支持索引頁。
5. 主流文件存儲格式對比實驗
從存儲文件的壓縮比和查詢速度兩個角度對比。
存儲文件的壓縮比測試：
（1）TextFile，首先創建表，指定存儲格式，然後加載數據，最後查看表中數據大小
hive (default)> create table log_text(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by "\t"
> stored as textfile;
OK
Time taken: 0.241 seconds
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/log.data' into table log_text;
Loading data to table default.log_text
OK
Time taken: 2.62 seconds
hive (default)> dfs -du -h /user/hive/warehouse/log_text;
18.1 M 54.4 M /user/hive/warehouse/log_text/log.data
123456789101112131415161718192021（2）Orc（設置 orc 存儲不使用壓縮）
hive (default)> create table log_orc(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by '\t'
> stored as orc
> tblproperties("orc.compress"="NONE");
OK
Time taken: 0.149 seconds
hive (default)> insert into table log_orc select * from log_text;
hive (default)> dfs -du -h /user/hive/warehouse/log_orc/;
7.7 M 23.1 M /user/hive/warehouse/log_orc/000000_0
12345678910111213141516171819（3）Parquet
hive (default)> create table log_parquet(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by '\t'
> stored as parquet;
OK
Time taken: 0.164 seconds
hive (default)> insert into table log_parquet select * from log_text;
hive (default)> dfs -du -h /user/hive/warehouse/log_parquet/;
13.1 M 39.3 M /user/hive/warehouse/log_parquet/000000_0
123456789101112131415161718存儲文件的對比總結：ORC > Parquet > textFile
存儲文件的查詢速度測試：
（1）TextFile
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/log_text' select substring(url,1,4) from log_text;
OK
_c0
Time taken: 18.312 seconds
1234（2）Orc
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/log_orc' select substring(url,1,4) from log_orc;
OK
_c0
Time taken: 10.412 seconds
1234（3）Parquet
hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/data/log_parquet' select substring(url,1,4) from log_parquet;
OK
_c0
Time taken: 9.799 seconds
1234存儲文件的查詢速度總結：查詢速度相近。
2.5 存儲和壓縮結合
官網：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC
ORC存儲方式的壓縮：
注意：所有關於ORCFile 的參數都是在 HQL 語句的 TBLPROPERTIES 欄位裡面出現
1. 創建一個 ZLIB 壓縮的 ORC 存儲方式
（1）建表語句
hive (default)> create table log_orc_zlib(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by '\t'
> stored as orc
> tblproperties("orc.compress"="ZLIB");
OK
Time taken: 0.118 seconds
1234567891011121314（2）插入數據
hive (default)> insert into log_orc_zlib select * from log_text;
1（3）查看插入後數據
hive (default)> dfs -du -h /user/hive/warehouse/log_orc_zlib/;
2.8 M 8.3 M /user/hive/warehouse/log_orc_zlib/000000_0
122. 創建一個 SNAPPY 壓縮的 ORC 存儲方式
（1）建表語句
hive (default)> create table log_orc_snappy(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by '\t'
> stored as orc
> tblproperties("orc.compress"="SNAPPY");
OK
Time taken: 0.138 seconds
1234567891011121314（2）插入數據
hive (default)> insert into log_orc_snappy select * from log_text;
1（3）查看插入後數據
hive (default)> dfs -du -h /user/hive/warehouse/log_orc_snappy/;
3.7 M 11.2 M /user/hive/warehouse/log_orc_snappy/000000_0
12ZLIB比 Snappy壓縮的還小。原因是 ZLIB採用的是 deflate壓縮算法 。比 snappy壓縮的壓縮率高。
3. 創建一個 SNAPPY 壓縮的 parquet 存儲方式
（1）建表語句
hive (default)> create table log_parquet_snappy(
> track_time string,
> url string,
> session_id string,
> referer string,
> ip string,
> end_user_id string,
> city_id string
> )
> row format delimited fields terminated by '\t'
> stored as parquet
> tblproperties("parquet.compression"="snappy");
OK
Time taken: 0.972 seconds
1234567891011121314（2）插入數據
hive (default)> insert into log_parquet_snappy select * from log_text;
1（3）查看插入後數據
hive (default)> dfs -du -h /user/hive/warehouse/log_parquet_snappy/;
6.4 M 19.2 M /user/hive/warehouse/log_parquet_snappy/000000_0
124. 存儲方式和壓縮總結
在實際的項目開發當中，hive 表的數據存儲格式一般選擇 orc 或 parquet 。壓縮方式一般選擇 snappy 或 lzo 。
（五）企業級調優、Hive實戰
1 企業級調優
1.1 執行計劃（Explain）
1. 基本語法
EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query
2. 案例實操
（1）查看下面這條語句的執行計劃
沒有生成 MR 任務的
hive (default)> explain select * from emp;
OK
Explain
Plan optimized by CBO.
Stage-0
Fetch Operator
limit:-1
Select Operator [SEL_1]
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
TableScan [TS_0]
Output:["empno","ename","job","mgr","hiredate","sal","comm","deptno"]
Time taken: 3.189 seconds, Fetched: 10 row(s)
1234567891011121314有生成 MR 任務的
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;
OK
Explain
Plan optimized by CBO.
Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE)
Stage-0
Fetch Operator
limit:-1
Stage-1
Reducer 2 vectorized
File Output Operator [FS_12]
Select Operator [SEL_11] (rows=1 width=7030)
Output:["_col0","_col1"]
Group By Operator [GBY_10] (rows=1 width=7030)
Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0
<-Map 1 [SIMPLE_EDGE] vectorized
SHUFFLE [RS_9]
PartitionCols:_col0
Group By Operator [GBY_8] (rows=1 width=7030)
Output:["_col0","_col1","_col2"],aggregations:["sum(sal)","count(sal)"],keys:deptno
Select Operator [SEL_7] (rows=1 width=7030)
Output:["sal","deptno"]
TableScan [TS_0] (rows=1 width=7030)
default@emp,emp,Tbl:COMPLETE,Col:NONE,Output:["sal","deptno"]
Time taken: 1.893 seconds, Fetched: 25 row(s)
1234567891011121314151617181920212223242526272829（2）查看詳細執行計劃
hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;
121.2 Fetch 抓取
Fetch抓取是指 ，Hive中對某些情況的查詢可以不必使用 MapReduce計算 。例如 SELECT * FROM employees;在這種情況下 Hive 可以簡單地讀取 employee對應的存儲目錄下的文件然後輸出查詢結果到控制台。在hive-default.xml.template文件中 hive.fetch.task.conversion默認是 more，老版本 hive默認是minimal，該屬性修改為 more以後，在全局查找、欄位查找、 limit查找等都不走 mapreduce。
<property>
<name>hive.fetch.task.conversion</name>
<value>more</value>
<description>
Expects one of [none, minimal, more].
Some select queries can be converted to single FETCH task minimizing latency.
Currently the query should be single sourced not having any subquery and should not haveany aggregations or distincts (which incurs RS), lateral views and joins.
0. none : disable hive.fetch.task.conversion
1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
</description>
</property>
123456789101112案例實操：
（1）把hive.fetch.task.conversion設置成 none ，然後執行查詢語句，都會執行 mapreduce 程序。
hive (default)> set hive.fetch.task.conversion=none;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;
1234（2）把hive.fetch.task.conversion設置成 more，然後執行查詢語句，如下查詢方式都不會執行 mapreduce 程序。
hive (default)> set hive.fetch.task.conversion=more;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;
12341.3 本地模式
大多數的 Hadoop Job 是需要 Hadoop 提供的完整的可擴展性來處理大數據集的。不過，有時 Hive 的輸入數據量是非常小的。在這種情況下，為查詢觸發執行任務消耗的時間可能會比實際 job 的執行時間要多的多。對於大多數這種情況， Hive 可以通過本地模式在單台機器上處理所有的任務。對於小數據集，執行時間可以明顯被縮短。
用戶可以通過設置hive.exec.mode.local.auto 的值為 true ，來讓 Hive 在適當的時候自動啟動這個優化。
set hive.exec.mode.local.auto=true; // 開啟本地 mr
// 設置 local mr 的最大輸入數據量，當輸入數據量小於這個值時採用 local mr 的方式，默認
// 為 134217728 ，即 128M
set hive.exec .mode.local.auto.inputbytes.max=50000000;
// 設置 local mr 的最大輸入文件個數，當輸入文件個數小於這個值時採用 local mr 的方式，默認為 4
set hive.exec.mode.local.auto.input.files.max=10;
123456案例實操
（1）關閉本地模式 （默認是關閉的 ），並執行查詢語句
hive (default)> select count(*) from emp group by deptno;
OK
_c0
3
5
6
1
Time taken: 8.67 seconds, Fetched: 4 row(s)
12345678（2）開啟本地模式，並執行查詢語句
hive (default)> set hive.exec.mode.local.auto=true;
hive (default)> select count(*) from emp group by deptno;
OK
_c0
3
5
6
1
Time taken: 9.457 seconds, Fetched: 4 row(s)
1234567891.4 表的優化
1.4.1 小表大表Join（MapJOIN）
將key 相對分散，並且數據量小的表放在join 的左邊，可以使用map join 讓小的維度表先進內存。在map 端完成 join。
實際測試發現：新版的 hive 已經對小表 JOIN 大表和大表 JOIN 小表進行了優化。小表放在左邊和右邊已經沒有區別。
1. 需求介紹
測試大表JOIN 小表和小表JOIN 大表的效率。
2. 開啟 MapJoin 參數設置
（1）設置自動選擇 Mapjoin， 默認為true
hive (default)> set hive.auto.convert.join = true;
1（2）大表小表的閾值設置（默認25M 以下認為是小表）：
hive (default)> set hive.mapjoin.smalltable.filesize = 25000000;
13. MapJoin 工作機制
4. 建大表、小表和 JOIN 後表的語句
// 創建大表
hive (default)> create table bigtable(id bigint, t bigint, uid string, keyword string,
> url_rank int, click_num int, click_url string) row format delimited
> fields terminated by '\t';
OK
Time taken: 0.193 seconds
// 創建小表
hive (default)> create table smalltable(id bigint, t bigint, uid string, keyword string,
> url_rank int, click_num int, click_url string) row format delimited
> fields terminated by '\t';
OK
Time taken: 0.162 seconds
// 創建 join 後的表
hive (default)> create table jointable(id bigint, t bigint, uid string, keyword string,
> url_rank int, click_num int, click_url string) row format delimited
> fields terminated by '\t';
OK
Time taken: 0.127 seconds
12345678910111213141516171819205. 分別向大表和小表中導入數據
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/bigtable' into table bigtable;
Loading data to table default.bigtable
OK
Time taken: 27.455 seconds
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/smalltable' into table smalltable;
Loading data to table default.smalltable
OK
Time taken: 1.51 seconds
1234567896. 小表 JOIN 大表語句
hive (default)> insert overwrite table jointable
> select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
> from smalltable s
> join bigtable b
> on b.id = s.id;
Loading data to table default.jointable
OK
b.id b.t b.uid b.keyword b.url_rank b.click_num b.click_url
Time taken: 97.527 seconds
1234567897. 大表 JOIN小表語句
hive (default)> insert overwrite table jointable
> select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
> from bigtable b
> join smalltable s
> on s.id = b.id;
Loading data to table default.jointable
OK
b.id b.t b.uid b.keyword b.url_rank b.click_num b.click_url
Time taken: 94.968 seconds
1234567891.4.2 大表 Join 大表
1. 空 KEY 過濾
有時 join 超時是因為某些 key 對應的數據太多，而相同 key 對應的數據都會發送到相同的 reducer 上，從而導致內存不夠。此時我們應該仔細分析這些異常的 key ，很多情況下這些 key 對應的數據是異常數據，我們需要在 SQL 語句中進行過濾。例如 key 對應的欄位為空，操作如下：
（1）配置歷史伺服器
配置mapred site.xml
<!--歷史伺服器端地址-->
<property>
<name>mapreduce.jobhistory.address</name>
<value>hadoop102:10020</value>
</property>
<!--歷史伺服器web端地址-->
<property>
<name>mapreduce.jobhistory.webapp.address</name>
<value>hadoop102:19888</value>
</property>
12345678910啟動歷史伺服器
sbin/mr-jobhistory daemon.sh start historyserver
1查看jobhistory
http://hadoop102:19888/jobhistory
（2）創建原始數據空 id 表
hive (default)> create table nullidtable(id bigint, t bigint, uid string, keyword string,
> url_rank int, click_num int, click_url string) row format delimited
> fields terminated by '\t';
OK
Time taken: 0.151 seconds
12345（3）分別加載原始數據和空 id 數據到對應表中
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/nullid' into table nullidtable;
Loading data to table default.nullidtable
OK
Time taken: 11.346 seconds
1234（4）測試不過濾空id
hive (default)> insert overwrite table jointable select n.* from
> nullidtable n left join bigtable o on n.id = o.id;
OK
n.id n.t n.uid n.keyword n.url_rank n.click_num n.click_url
Time taken: 182.221 seconds
12345（5）測試過濾空id
hive (default)> insert overwrite table jointable select n.* from (select * from nullidtable
> where id is not null) n left join bigtable o on n.id = o.id;
OK
n.id n.t n.uid n.keyword n.url_rank n.click_num n.click_url
Time taken: 152.913 seconds
123452. 空 key 轉換
有時雖然某個 key 為空對應的數據很多，但是相應的數據不是異常數據，必須要包含在 join 的結果中，此時我們可以表 a 中 key 為空的欄位賦一個隨機的值，使得數據隨機均勻地分不到不同的 reducer 上。例如：
不隨機分布空 null 值：
（1）設置 5 個 reduce 個數
hive (default)> set mapreduce.job.reduces = 5;
1（2）JOIN 兩張表
hive (default)> insert overwrite table jointable
> select n.* from nullidtable n left join bigtable b on n.id = b.id;
12結果：如下圖所示，可以看出來，出現了數據傾斜，某些 reducer 的資源消耗遠大於其他 reducer。
隨機分布空 null 值
（1）設置 5 個 reduce 個數
hive (default)> set mapreduce.job.reduces = 5;
1（2）JOIN 兩張表
insert overwrite table jointable
select n.* from nullidtable n full join bigtable o on
nvl(n.id,rand()) = o.id;
123（4）結果：如下圖所示
3. SMB(Sort Merge Bucket join)
（1）創建第二張大表
hive (default)> create table bigtable2(
> id bigint,
> t bigint,
> uid string,
> keyword string,
> url_rank int,
> click_num int,
> click_url string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.368 seconds
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/bigtable' into table bigtable2;
12345678910111213測試大表直接 JOIN
hive (default)> insert overwrite table jointable
> select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
> from bigtable s
> join bigtable2 b
> on b.id = s.id;
OK
b.id b.t b.uid b.keyword b.url_rank b.click_num b.click_url
Time taken: 396.839 seconds
12345678（2）創建分桶表 1,桶的個數不要超過可用 CPU 的核數
hive (default)> create table bigtable_buck1(
> id bigint,
> t bigint,
> uid string,
> keyword string,
> url_rank int,
> click_num int,
> click_url string)
> clustered by(id)
> sorted by(id)
> into 6 buckets
> row format delimited fields terminated by '\t';
OK
Time taken: 0.537 seconds
hive (default)> load data local inpath
> '/opt/module/hive-3.1.2/data/bigtable' into table
> bigtable_buck1;
123456789101112131415161718（3） 創建分桶表 2，桶的個數不要超過可用 CPU 的核數
hive (default)> create table bigtable_buck2(
> id bigint,
> t bigint,
> uid string,
> keyword string,
> url_rank int,
> click_num int,
> click_url string)
> clustered by(id)
> sorted by(id)
> into 6 buckets
> row format delimited fields terminated by '\t';
hive (default)> load data local inpath
> '/opt/module/hive-3.1.2/data/bigtable' into table
> bigtable_buck2;
12345678910111213141516（4）設置參數
hive (default)> set hive.optimize.bucketmapjoin = true;
hive (default)> set hive.optimize.bucketmapjoin.sortedmerge = true;
hive (default)> set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
123（5）測試
hive (default)> insert overwrite table jointable
> select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
> from bigtable_buck1 s
> join bigtable_buck2 b
> on b.id = s.id;
123451.4.3 Group By
默認情況下，Map 階段同一 Key 數據分發給一個 reduce ，當一個 key 數據過大時就傾斜了。
並不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端進行部分聚合，最後在 Reduce 端得出最終結果。
開啟 Map 端聚合參數設置
（1）是否在 Map 端進行聚合，默認為 True
hive (default)> set hive.map.aggr = true;
1（2）在 Map 端進行聚合操作的條目數目
hive (default)> set hive.groupby.mapaggr.checkinterval = 100000;
1（3）有數據傾斜的時候進行負載均衡（默認是 false）
hive (default)> set hive.groupby.skewindata = true;
1當選項設定為 true ，生成的查詢計劃會有兩個 MR Job 。 第一個 MR Job 中， Map 的輸出結果會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，並輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Reduce 中，從而達到負載均衡的目的；第二個 MR Job 再根據預處理的數據結果按照 Group By Key 分布到 Reduce 中（這個過程可以保證相同的 Group By Key 被分布到同一個 Reduce 中），最後完成最終的聚合操作。
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 21.7 sec HDFS Read: 37846 HDFS Write: 495 SUCCESS
Total MapReduce CPU Time Spent: 21 seconds 700 msec
OK
deptno
10
20
30
50
Time taken: 119.241 seconds, Fetched: 4 row(s)
123456789優化以後
hive (default)> set hive.groupby.skewindata = true;
hive (default)> select deptno from emp group by deptno;
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 18.73 sec HDFS Read: 35806 HDFS Write: 552 SUCCESS
Stage-Stage-2: Map: 1 Reduce: 5 Cumulative CPU: 14.54 sec HDFS Read: 22374 HDFS Write: 495 SUCCESS
Total MapReduce CPU Time Spent: 33 seconds 270 msec
OK
deptno
10
20
30
50
Time taken: 104.013 seconds, Fetched: 4 row(s)
1234567891011121.4.4 Count(Distinct) 去重統計
數據量小的時候無所謂，數據量大的情況下，由於 COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個 Reduce 需要處理的數據量太大，就會導致整個 Job 很難完成，一般 COUNT DISTINCT使用先 GROUP BY 再 COUNT的方式替換 ,但是需要注意 group by造成的數據傾斜問題
。
案例實操
（1）創建一張大表
hive (default)> create table bigtable (id bigint, times bigint, uid string,
> keyword string, url_rank int, click_num int, click_url string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.221 seconds
12345（2）加載數據
hive (default)> load data local inpath '/opt/module/hive-3.1.2/data/bigtable' into table bigtable;
Loading data to table default.bigtable
OK
Time taken: 11.977 seconds
1234（3）設置 5個 reduce 個數
hive (default)> set mapreduce.job.reduces = 5;
1（4）執行去重 id 查詢
hive (default)> select count(distinct id) from bigtable;
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 21.11 sec HDFS Read: 129179350 HDFS Write: 580 SUCCESS
Stage-Stage-2: Map: 1 Reduce: 1 Cumulative CPU: 4.07 sec HDFS Read: 8535 HDFS Write: 105 SUCCESS
Total MapReduce CPU Time Spent: 25 seconds 180 msec
OK
_c0
99947
Time taken: 96.086 seconds, Fetched: 1 row(s)
12345678（5）採用 GROUP by去重 id
hive (default)> select count(id) from (select id from bigtable group by id) a;
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 29.09 sec HDFS Read: 129191861 HDFS Write: 2033820 SUCCESS
Stage-Stage-2: Map: 1 Reduce: 5 Cumulative CPU: 19.62 sec HDFS Read: 2053225 HDFS Write: 580 SUCCESS
Stage-Stage-3: Map: 1 Reduce: 1 Cumulative CPU: 3.58 sec HDFS Read: 8543 HDFS Write: 105 SUCCESS
Total MapReduce CPU Time Spent: 52 seconds 290 msec
OK
_c0
99947
Time taken: 154.433 seconds, Fetched: 1 row(s)
123456789雖然會多用一個 Job 來完成，但在數據量大的情況下，這個絕對是值得的。
1.4.5 笛卡爾積
儘量避免笛卡爾積，join 的時候不加 on 條件，或者無效的 on 條件， Hive 只能使用 1 個 reducer 來完成笛卡爾積 。
1.4.6 行列過濾
列處理：在 SELECT中，只拿需要的列，如果有分區，儘量使用分區過濾，少用 SELECT *。
行處理：在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在
where 後面，那麼就會先全表關聯，之後再過濾，比如：
（1）測試先關聯兩張表，再用 where 條件過濾
hive (default)> select o.id from bigtable b
> join bigtable o on o.id = b.id
> where o.id <= 10;
Time taken: 53.697 seconds, Fetched: 1081 row(s)
1234（2）通過子查詢後，再關聯表
hive (default)> select b.id from bigtable b
> join (select id from bigtable where id <= 10) o on b.id = o.id;
Time taken: 50.658 seconds, Fetched: 1081 row(s)
1231.5 合理設置 Map及Reduce數
（1）通常情況下，作業會通過 input 的目錄產生一個或者多個 map 任務。
主要的決定因素有：input 的文件總個數， input 的文件大小，集群設置的文件塊大小。
（2）是不是 map 數越多越好？
答案是否定的。如果一個任務有很多小文件（遠遠小於塊大小128m ），則每個小文件也會被當做一個塊，用一個 map 任務來完成，而一個 map 任務啟動和初始化的時間遠遠大於邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執行的 map 數是受限的。
（3）是不是保證每個 map 處理接近 128M 的文件塊，就高枕無憂了？
答案也是不一定。比如有一個127M 的文件，正常會用一個 map 去完成，但這個文件只有一個或者兩個小欄位，卻有幾千萬的記錄，如果 map 處理的邏輯比較複雜，用一個 map 任務去做，肯定也比較耗時。
針對上面的問題 2 和 3 ，我們需要採取兩種方式來解決：即減少 map 數和增加 map 數；
1.5.1 複雜文件增加 Map 數
當 input 的文件都很大，任務邏輯複雜， map 執行非常慢的時候，可以考慮增加 Map 數，來使得每個 map 處理的數據量減少，從而提高任務的執行效率。
增加 map 的方法為：根據 computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，調整 maxSize 最大值。讓 maxSize 最大值低於 blocksize 就可以增加 map 的個數。
案例實操：
（1）執行查詢
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
12（2）設置最大切片值為 100 個字節
hive (default)> set mapreduce.input.fileinputformat.split.maxsize = 100;
Hadoop job information for Stage-1: number of mappers: 7; number of reducers: 1
121.5.2 小文件進行合併
（1）在 map 執行前合併小文件，減少 map 數：CombineHiveInputFormat具有對小文件進行合併的功能（系統默認的格式）。HiveInputFormat沒有對小文件合併功能。
hive (default)> set hive.input.format = org.apache.hadoop.hive.qi.io.CombineHiveInputFormat;
1（2）在 Map-Reduce 的任務結束時合併小文件的設置：
在 map-only 任務結束時合併小文件，默認 true
hive (default)> set hive.merge.mapfiles = true;
1在 map-reduce 任務結束時合併小文件，默認 false
hive (default)> set hive.merge.mapredfiles = true;
1合併文件的大小，默認 256M
hive (default)> set hive.merge.size.per.task = 268435456;
1當輸出文件的平均大小小於該值時，啟動一個獨立的 map-reduce 任務進行文件 merge
hive (default)> set hive.merge.smallfiles.avgsize = 16777216;
11.5.3 合理設置 Reduce 數
1. 調整 reduce 個數方法一
（1）每個 Reduce 處理的數據量默認是 256 MB
hive (default)> set hive.exec.reducers.bytes.per.reducer = 256000000;
1（2）每個任務最大的 reduce 數，默認為 1009
hive (default)> set hive.exec.reducers.max = 1009;
1（3）計算 reducer 數的公式
N=min(參數2 ，總輸入數據量/參數 1)
12. 調整 reduce 個數方法二
在 hadoop的mapred-default.xml文件中修改
設置每個 job 的 Reduce 個數
hive (default)> set mapreduce.job.reduces = 15;
13. reduce 個數並不是越多越好
（1）過多的啟動和初始化 reduce 也會消耗時間和資源；
（2）另外，有多少個 reduce ，就會有多少個輸出文件，如果生成了很多個小文件，那麼如果這些小文件作為下一個任務的輸入，則也會出現小文件過多的問題；在設置 reduce 個數的時候也需要考慮這兩個原則：處理大數據量利用合適的 reduce 數；使單個 reduce 任務處理數據量大小要合適。
1.6 並行執行
Hive 會將一個查詢轉化成一個或者多個階段。這樣的階段可以是 MapReduce 階段、抽樣階段、合併階段、 limit 階段。或者 Hive 執行過程中可能需要的其他階段。默認情況下，Hive 一次只會執行一個階段。不過，某個特定的 job
可能包含眾多的階段，而這些階段可能並非完全互相依賴的，也就是說有些階段是可以並行執行的，這樣可能使得整個 job 的執行時間縮短。不過，如果有更多的階段可以並行執行，那麼 job 可能就越快完成。
通過設置參數 hive.exec.parallel值為 true ，就可以開啟並發執行。不過，在共享集群中需要注意下，如果 job 中並行階段增多，那麼集群利用率就會增加。
hive (default)> set hive.exec.parallel = true; // 打開任務並行執行
hive (default)> set hive.exec.parallel.thread.number = 16; // 同一個 sql 允許最大並行度，默認為 8
12當然，得是在系統資源比較空閒的時候才有優勢，否則，沒資源，並行也起不來。
1.7 嚴格模式
Hive 可以通過設置防止一些危險操作：
1. 分區表不使用分區過濾
將 hive.strict.checks.no.partition.filter設置為 true 時， 對於分區表，除非
where語句中含有分區欄位過濾條件來限制範圍, 否則不允許執行。 換句話說，就是用戶不允許掃描所有分區。進行這個限制的原因是，通常分區表都擁有非常大的數據集，而且數據增加迅速。沒有進行分區限制的查詢可能會消耗令人不可接受的巨大資源來處理這個表。
2. 使用 order by 沒有 limit 過濾
將hive.strict.checks.orderby.no.limit設置為 true 時， 對於使用了 order by 語句的查詢，要求必須使用
limit 語句。 因為 order by 為了執行排序過程會將所有的結果數據分發到同一個Reducer 中進行處理，強制要求用戶增加這個 LIMIT 語句可以防止 Reducer 額外執行很長一段時間。
3. 笛卡爾積
將hive.strict.checks.cartesian.product設置為 true 時，會限制笛卡爾積的查詢。
對關係型資料庫非常了解的用戶可能期望在執行 JOIN 查詢的時候不使用 ON 語句而是使用 where 語句，這樣關係資料庫的執行優化器就可以高效地將 WHERE 語句轉化成那個 ON 語句。不幸的是， Hive 並不會執行這種優化，因此，如果表足夠大，那麼這個查詢就會出現不可控的情
況。
2 Hive 實戰
2.1 需求描述
統計矽谷影音視頻網站的常規指標，各種Top N 指標：
1 統計視頻觀看數 Top10
2 統計視頻類別熱度 Top10
3 統計出視頻觀看數最高的 20 個視頻的所屬類別以及類別包含 Top20 視頻的個數
4 統計視頻觀看數 Top50 所關聯視頻的所屬類別 排序
5 統計每個類別中的視頻熱度 Top10, 以 Music 為例
6 統計每個類別視頻觀看數 Top10
7 統計上傳視頻最多的用戶 Top10 以及他們上傳的視頻觀看次數在前 20 的視頻
2.2 數據結構
1. 視頻表
2. 用戶表
2.3 準備工作
2.3.1 準備表
1. 需要準備的表
創建原始數據表：gulivideo_ori，gulivideo_user_ori，
創建最終表：gulivideo_orc，gulivideo_user_orc
1. 創建原始數據表
（1）gulivideo_ori
hive (default)> create table gulivideo_ori(
> videoId string,
> uploader string,
> age int,
> category array<string>,
> length int,
> views int,
> rate float,
> ratings int,
> comments int,
> relatedId array<string>)
> row format delimited fields terminated by "\t"
> collection items terminated by "&"
> stored as textfile;
OK
Time taken: 0.131 seconds
12345678910111213141516（2）創建原始數據表 : gulivideo_user_ori
hive (default)> create table gulivideo_user_ori(
> uploader string,
> videos int,
> friends int)
> row format delimited
> fields terminated by "\t"
> stored as textfile;
OK
Time taken: 0.131 seconds
1234567892. 創建 orc 存儲格式帶 snappy 壓縮的表：
（1）gulivideo_orc
hive (default)> create table gulivideo_orc(
> videoId string,
> uploader string,
> age int,
> category array<string>,
> length int,
> views int,
> rate float,
> ratings int,
> comments int,
> relatedId array<string>)
> row format delimited fields terminated by '\t'
> collection items terminated by '&'
> stored as orc
> tblproperties("orc.compress"="snappy");
OK
Time taken: 0.144 seconds
1234567891011121314151617（2）gulivideo_user_orc
hive (default)> create table gulivideo_user_orc(
> uploader string,
> videos int,
> friends int)
> row format delimited fields terminated by '\t'
> stored as orc
> tblproperties("orc.compress"="snappy");
OK
Time taken: 0.127 seconds
123456789（3）向 ori 表插入數據
hive (default)> load data local inpath "/opt/module/hive-3.1.2/data/video" into table gulivideo_ori;
Loading data to table default.gulivideo_ori
OK
Time taken: 4.183 seconds
hive (default)> load data local inpath "/opt/module/hive-3.1.2/data/user.txt" into table gulivideo_user_ori;
Loading data to table default.gulivideo_user_ori
OK
Time taken: 6.083 seconds
123456789（4）向 orc 表插入數據
hive (default)> insert into table gulivideo_orc select * from gulivideo_ori;
hive (default)> insert into table gulivideo_user_orc select * from gulivideo_user_ori;
122.3.2 安裝 Tez 引擎
Tez 是 一個 Hive 的運行引擎，性能優於 MR 。
用 Hive 直接編寫 MR 程序，假設有四個有依賴關係的 MR 作業 ，上圖中，綠色是Reduce Task ，雲狀表示寫屏蔽，需要將中間結果持久化寫到 HDFS 。
Tez可以將多個有依賴的作業轉換為一個作業，這樣只需寫一次 HDFS ，且中間節點較少，從而大大提升作業的計算性能 。
（1）將 tez 安裝包拷貝到集群，並解壓 tar 包
[Tom@hadoop102 software]$ mkdir /opt/module/tez
[Tom@hadoop102 software]$ tar -zxvf /opt/software/tez-0.10.1-SNAPSHOT-minimal.tar.gz -C /opt/module/tez
12（2）上傳 tez 依賴到 HDFS
[Tom@hadoop102 software]$ hadoop fs -mkdir /tez
[Tom@hadoop102 software]$ hadoop fs -put /opt/software/tez-0.10.1-SNAPSHOT.tar.gz /tez
12（3）新建 tez-site.xml
[Tom@hadoop102 software]$ vim $HADOOP_HOME/etc/hadoop/tez-site.xml
1添加如下內容：
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>tez.lib.uris</name>
<value>${fs.defaultFS}/tez/tez-0.10.1-SNAPSHOT.tar.gz</value>
</property>
<property>
<name>tez.use.cluster.hadoop-libs</name>
<value>true</value>
</property>
<property>
<name>tez.am.resource.memory.mb</name>
<value>1024</value>
</property>
<property>
<name>tez.am.resource.cpu.vcores</name>
<value>1</value>
</property>
<property>
<name>tez.container.max.java.heap.fraction</name>
<value>0.4</value>
</property>
<property>
<name>tez.task.resource.memory.mb</name>
<value>1024</value>
</property>
<property>
<name>tez.task.resource.cpu.vcores</name>
<value>1</value>
</property>
</configuration>
1234567891011121314151617181920212223242526272829303132（4）修改 Hadoop環境變量
[Tom@hadoop102 software]$ vim $HADOOP_HOME/etc/hadoop/shellprofile.d/tez.sh
1添加 Tez的 Jar 包相關信息
hadoop_add_profile tez
function _tez_hadoop_classpath
{
hadoop_add_classpath "$HADOOP_HOME/etc/hadoop" after
hadoop_add_classpath "/opt/module/tez/*" after
hadoop_add_classpath "/opt/module/tez/lib/*" after
}
1234567（5）修改 Hive 的計算引擎
[Tom@hadoop102 software]$ vim $HIVE_HOME/conf/hive-site.xml
1<property>
<name>hive.execution.engine</name>
<value>tez</value>
</property>
<property>
<name>hive.tez.container.size</name>
<value>1024</value>
</property>
12345678（6）解決日誌 Jar 包衝突
[Tom@hadoop102 software]$ rm /opt/module/tez/lib/slf4j-log4j12-1.7.10.jar
12.4 業務分析
2.4.1 統計視頻觀看數 Top10
hive (default)> select videoId, views from gulivideo_orc order by views desc limit 10;
OK
videoid views
dMH0bHeiRNg 42513417
0XxI-hvPRRA 20282464
1dmVU08zVpA 16087899
RB-wUgnyGv0 15712924
QjA5faZF1A8 15256922
-_CSo1gOd48 13199833
49IDp76kjPw 11970018
tYnn51C3X_w 11823701
pv5zWaTEVkI 11672017
D2kJZOfq7zk 11184051
Time taken: 15.721 seconds, Fetched: 10 row(s)
12345678910111213142.4.2 統計視頻類別熱度 Top10
思路
（1） 即統計每個類別有多少個視頻，顯示出包含視頻最多的前 10 個類別。
（2） 我們需要按照類別 group by 聚合，然後 count 組內的 videoId 個數即可。
（3） 因為當前表結構為：一個視頻對應一個或多個類別。所以如果要 group by 類別，需要先將類別進行列轉行(展開)，然後再進行 count 即可。
（4）最後按照熱度排序，顯示前 10 條。
hive (default)> select t1.category_name,count(t1.videoId) hot
> from (select videoId, category_name
> from gulivideo_orc lateral view explode(category) gulivideo_orc_tmp as category_name) t1
> group by t1.category_name
> order by hot desc limit 10;
OK
t1.category_name hot
Music 5375
Entertainment 4557
Comedy 4443
Film 2953
Animation 2953
People 2208
Blogs 2208
News 2070
Politics 2070
Sports 1710
Time taken: 23.159 seconds, Fetched: 10 row(s)
1234567891011121314151617182.4.3 統計出視頻觀看數最高的 20 個視頻的所屬類別以及類別包含 Top20 視頻的個數
思路：
（1）先找到觀看數最高的 20 個視頻所屬條目的所有信息，降序排列
（2）把這 20 條信息中的 category 分裂出來(列轉行)
（3）最後查詢視頻分類名稱和該分類下有多少個 Top20 的視頻
hive (default)> select t2.category_name,count(t2.videoId) video_sum
> from(select t1.videoId, category_name from (
> select videoId, views, category from gulivideo_orc
> order by views desc limit 20) t1
> lateral view explode(t1.category) t1_tmp as category_name) t2
> group by t2.category_name;
OK
t2.category_name video_sum
Blogs 2
Comedy 6
Entertainment 6
Music 5
People 2
UNA 1
Time taken: 20.196 seconds, Fetched: 6 row(s)
1234567891011121314152.4.4 統計視頻觀看數 Top50 所關聯視頻的所屬類別排序
hive (default)> select category_name, count(*) ct
> from (select explode(category) category_name
> from (select g.category from (select explode(relatedId) related_id
> from (select relatedId, views
> from gulivideo_orc order by views desc limit 50)t1)t2
> join gulivideo_orc g on t2.related_id = g.videoId)t3)t4
> group by category_name order by ct desc;
OK
category_name ct
Comedy 203
Entertainment 181
Music 154
Animation 65
Film 65
People 49
Blogs 49
UNA 19
Politics 14
Travel 14
Sports 14
News 14
Places 14
DIY 10
Howto 10
Games 9
Gadgets 9
Animals 6
Pets 6
Autos 2
Vehicles 2
Time taken: 41.837 seconds, Fetched: 21 row(s)
123456789101112131415161718192021222324252627282930312.4.5 統計每個類別中的視頻熱度 Top10，以 Music 為例
思路：
（1) 要想統計 Music 類別中的視頻熱度 Top10，需要先找到 Music 類別，那麼就需要將 category 展開 ，所以可以創建一張表用於存放 categoryId 展開的數據。
（2）向 category 展開的表中插入數據。
（3）統計對應類別（Music）中的視頻熱度。
hive (default)> create table gulivideo_orc_category(
> videoId string,
> uploader string,
> age int,
> category string,
> length int,
> views int,
> rate float,
> ratings int,
> comments int,
> relatedId array<string>)
> stored as orc
> tblproperties("orc.compress"="snappy");
OK
Time taken: 0.944 seconds
hive (default)> insert into table gulivideo_orc_category
> select videoId, uploader, age, category_name, length, views, rate, ratings, comments, relatedId
> from gulivideo_orc lateral view explode(category) category_tmp as category_name;
hive (default)> select videoId, views from gulivideo_orc_category
> where category="Music"
> order by views desc limit 10;
OK
videoid views
QjA5faZF1A8 15256922
tYnn51C3X_w 11823701
pv5zWaTEVkI 11672017
8bbTtPL1jRs 9579911
UMf40daefsI 7533070
HSoVKUVOnfQ 6193057
NINJQ5LRh-0 3794886
FLn45-7Pn2Y 3604114
seGhTWE98DU 3296342
eiiU-Fky18s 3269875
Time taken: 16.568 seconds, Fetched: 10 row(s)
1234567891011121314151617181920212223242526272829303132333435362.4.6 統計每個類別視頻觀看數 Top3
hive (default)> select category, videoId, views
> from (select category, videoId, views, rank() over(partition by category order by views desc) rk
> from gulivideo_orc_category) t1
> where rk <= 3;
OK
category videoid views
Animals l9l19D2sIHI 1422837
Animals LHyJH1yGKZY 1085020
Animals Qz_nZixWX6Q 1027067
Animation sdUUx5FdySs 5840839
Animation 6B26asyGKDo 5147533
Animation 55YYaJIrmzo 3356163
Autos 46LQd9dXFRU 1262173
Autos pdiuDXwgrjQ 1013697
Autos aCamHfJwSGU 847442
Blogs -_CSo1gOd48 13199833
Blogs D2kJZOfq7zk 11184051
Blogs LB84A3zcmVo 4866739
Comedy dMH0bHeiRNg 42513417
Comedy 0XxI-hvPRRA 20282464
Comedy 49IDp76kjPw 11970018
DIY hut3VRL5XRE 2684989
DIY 6gmP4nk0EOE 1353059
DIY rZb2VlDyYvk 1084141
Entertainment 1dmVU08zVpA 16087899
Entertainment RB-wUgnyGv0 15712924
Entertainment vr3x_RRJdd4 10786529
Film sdUUx5FdySs 5840839
Film 6B26asyGKDo 5147533
Film 55YYaJIrmzo 3356163
Gadgets GxSdKF5Fd38 2468395
Gadgets gPutYwiiE0o 1633482
Gadgets 7wt5FiZQrgM 1399531
Games GxSdKF5Fd38 2468395
Games gPutYwiiE0o 1633482
Games 7wt5FiZQrgM 1399531
Howto hut3VRL5XRE 2684989
Howto 6gmP4nk0EOE 1353059
Howto rZb2VlDyYvk 1084141
Music QjA5faZF1A8 15256922
Music tYnn51C3X_w 11823701
Music pv5zWaTEVkI 11672017
News hr23tpWX8lM 4706030
News qSM_3fyiaxM 2291369
News qdS5lkeN8_8 2091042
People -_CSo1gOd48 13199833
People D2kJZOfq7zk 11184051
People LB84A3zcmVo 4866739
Pets l9l19D2sIHI 1422837
Pets LHyJH1yGKZY 1085020
Pets Qz_nZixWX6Q 1027067
Places bNF_P281Uu4 5231539
Places AlPqL7IUT6M 845180
Places _5QUdvUhCZc 819974
Politics hr23tpWX8lM 4706030
Politics qSM_3fyiaxM 2291369
Politics qdS5lkeN8_8 2091042
Sports q8t7iSGAKik 2735003
Sports 7vL19q8yL54 2527713
Sports P-bWsOK-h98 2268107
Travel bNF_P281Uu4 5231539
Travel AlPqL7IUT6M 845180
Travel _5QUdvUhCZc 819974
UNA aRNzWyD7C9o 8825788
UNA LIhbap3FlGc 2849832
UNA R0049_tDAU8 1204982
Vehicles 46LQd9dXFRU 1262173
Vehicles pdiuDXwgrjQ 1013697
Vehicles aCamHfJwSGU 847442
Time taken: 27.612 seconds, Fetched: 63 row(s)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869702.4.7 統計上傳視頻最多的用戶 Top10 以及他們上傳的視頻觀看次數在前 20 的視頻
思路：
（1）求出上傳視頻最多的 10 個用戶
（2）關聯 gulivideo_orc表，求出這 10 個用戶上傳的所有的視頻，按照觀看數取前 20
hive (default)> select uploader, videoId, views
> from (select uploader, videoId, views, rank() over(partition by uploader order by views desc) rk
> from (select t1.uploader, videoId, views
> from (select uploader from gulivideo_user_orc order by videos desc limit 10)t1
> join gulivideo_orc g on t1.uploader = g.uploader)t2)t3
> where rk <= 20;