Hive 3.x 配置&詳解
Hive
1. 數據倉庫概述
1.1 基本概念
-
數據倉庫（英語：Data Warehouse，簡稱數倉、DW），是一個用於存儲、分析、報告的數據系統。
-
數據倉庫的目的是構建面向分析的集成化數據環境，分析結果為企業提供決策支持（Decision Support）
數倉專註分析
- 數據倉庫本身並不「生產」任何數據，其數據來源於不同外部系統
- 同時數據倉庫自身也不需要「消費」任何的數據，其結果開放給各個外部應用使用
- 這也是為什麼叫「倉庫」，而不叫「工廠」的原因。
1.2 數倉為何而來？
為了分析數據而來，分析結果給企業決策提供支撐
案例：中國人手保險公司
1. 業務數據的存儲問題
- 中國人壽保險（集團）公司下轄多條業務線，包括：人壽險、財險、車險，養老險等。各業務線的業務正常運營需 要記錄維護包括客戶、保單、收付費、核保、理賠等信息。這麼多業務數據存儲在哪裡呢？
- 聯機事務處理系統（OLTP）正好可以滿足上述業務需求開展, 其主要任務是執行聯機事務處理。其基本特徵是前台接收的用戶數據可以立即傳送到後台進行處理，並在很短的時間內給出處理結果。
- 關係型數據庫（RDBMS）是OLTP典型應用，比如：Oracle、MySQL、SQL Server等
2. 分析型決策的制定
- 隨着集團業務的持續運營，業務數據將會越來越多。由此也產生出許多運營相關的困惑：
- 能夠確定哪些險種正在惡化或已成為不良險種？
- 能夠用有效的方式制定新增和續保的政策嗎？
- 理賠過程有欺詐的可能嗎？
- 現在得到的報表是否只是某條業務線的？集團整體層面數據如何？
- 為了能夠正確認識這些問題，制定相關的解決措施，瞎拍桌子是肯定不行的。
- 最穩妥辦法就是：基於業務數據開展數據分析，基於分析的結果給決策提供支撐。也就是所謂的數據驅動決策的制定。
OLTP環境開展分析可行嗎？
OLTP（On-line Transaction Processing）：操作型處理、稱為聯機事務處理，也可以稱為面向交易的處理系統，它是針對具體業務在數據庫聯機的日常操作，通常對少數記錄進行查詢、修改。用戶較為關心操作的響應時間、數據的安全性、完整性等問題。
可以，但是沒必要
- OLTP系統的核心是面向業務，支持業務，支持事務。所有的業務操作可以分為讀、寫兩種操作，一般來說讀的壓力明顯大於寫的壓力。如果在OLTP環境直接開展各種分析，有以下問題需要考慮：
- 數據分析也是對數據進行讀取操作，會讓讀取壓力倍增
- OLTP僅存儲數周或數月的數據
- 數據分散在不同系統不同表中，字段類型屬性不統一
數據倉庫面世
OLAP（On-line Analytical Processing）：分析型處理，稱為聯機分析處理，一般針對某些主題歷史數據進行分析，支持管理決策。
- 當分析所涉及數據規模較小的時候，在業務低峰期時可以在OLTP系統上開展直接分析。
- 但為了更好的進行各種規模的數據分析，同時也不影響OLTP系統運行，此時需要構建一個集成統一的數據分析平台。該平台的目的很簡單：面向分析，支持分析，並且和OLTP系統解耦合。
- 基於這種需求，數據倉庫的雛形開始在企業中出現了
數據倉庫的構建
- 如數倉定義所說，數倉是一個用於存儲、分析、報告的數據系統，目的是構建面向分析的集成化數據環境。我們把 這種面向分析、支持分析的系統稱之為OLAP（聯機分析處理）系統。當然，數據倉庫是OLAP系統的一種實現。
- 中國人壽保險公司就可以基於分析決策需求，構建數倉平台。
1.3 數倉的主要特徵
- 面向主題
- 集成性
- 非易失性
- 時變性
1. 面向主題性（Subject-Oriented）
主題是一個抽象的概念，是較高層次上數據綜合、歸類並進行分析利用的抽象
- 主題是一個抽象的概念，是較高層次上企業信息系統中的數據綜合、歸類並進行分析利用的抽象。在邏輯意義上， 它是對應企業中某一宏觀分析領域所涉及的分析對象。
- 傳統OLTP系統對數據的劃分並不適用於決策分析。而基於主題組織的數據則不同，它們被劃分為各自獨立的領域 ，每個領域有各自的邏輯內涵但互不交叉，在抽象層次上對數據進行完整、一致和準確的描述。
2. 集成性（Integrated）
主題相關的數據通常會分佈在多個操作型系統中，彼此分散、獨立、異構。需要集成到數倉主題下。
-
主題相關的數據通常會分佈在多個操作型系統中，彼此分散、獨立、異構。
-
因此在數據進入數據倉庫之前，必然要經過統一與綜合，對數據進行抽取、清理、轉換和匯總，這一步是數據倉庫 建設中最關鍵、最複雜的一步，所要完成的工作有：
- 要統一源數據中所有矛盾之處
- 如字段的同名異義、異名同義、單位不統一、字長不一致等等。
- 進行數據綜合和計算
- 數據倉庫中的數據綜合工作可以在從原有數據庫抽取數據時生成，但許多是在數據倉庫內部生成的，即進入數據倉庫以後進行綜合生成的
- 要統一源數據中所有矛盾之處
-
下圖說明了保險公司綜合數據的簡單處理過程，其中數據倉庫中與「承保」主題有關的數據來自於多個不同的操作型系統。
-
這些系統內部數據的命名可能不同，數據格式也可能不同。把不同來源的數據存儲到數據倉庫之前，需要去除這些 不一致
3. 非易失性、非異變性（Non-Volatile）
也叫非易變性。數據倉庫是分析數據的平台，而不是創造數據的平台。
- 數據倉庫是分析數據的平台，而不是創造數據的平台。我們是通過數倉去分析數據中的規律，而不是去創造修改其中的規律。因此數據進入數據倉庫後，它便穩定且不會改變。
- 數據倉庫的數據反映的是一段相當長的時間內歷史數據的內容，數據倉庫的用戶對數據的操作大多是數據查詢或比 較複雜的挖掘，一旦數據進入數據倉庫以後，一般情況下被較長時間保留。
- 數據倉庫中一般有大量的查詢操作，但修改和刪除操作很少。
4. 時變性
數據倉庫的數據需要隨着時間更新，以適應決策的需要。
- 數據倉庫包含各種粒度的歷史數據，數據可能與某個特定日期、星期、月份、季度或者年份有關。
- 當業務變化後會失去時效性。因此數據倉庫的數據需要隨着時間更新，以適應決策的需要。
- 從這個角度講，數據倉庫建設是一個項目，更是一個過程 。
1.4 SQL-數倉主流開發語言
數倉開發語言概述
- 數倉作為面向分析的數據平台，其主職工作就是對存儲在其中的數據開展分析，那麼如何讀取數據分析呢？
- 理論上來說，任何一款編程語言只要具備讀寫數據、處理數據的能力，都可以用於數倉的開發。比如大家耳熟能詳 的C、java、Python等
- 關鍵在於編程語言是否易學、好用、功能是否強大。遺憾的是上面所列出的C、Python等編程語言都需要一定的時間進行語法的學習，並且學習語法之後還需要結合分析的業務場景進行編碼，跑通業務邏輯。
- 不管從學習成本還是開發效率來說，上述所說的編程語言都不是十分友好的。
- 在數據分析領域，不得不提的就是SQL編程語言，應該稱之為分析領域主流開發語言。
SQL語言介紹
- 結構化查詢語言（Structured Query Language）簡稱SQL，是一種數據庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理數據。
- SQL語言使我們有能力訪問數據庫，並且SQL是一種ANSI（美國國家標準化組織）的標準計算機語言，各大數據庫 廠商在生產數據庫軟件的時候，幾乎都會去支持SQL的語法，以使得用戶在使用軟件時更加容易上手，以及在不同 廠商軟件之間進行切換時更加適應，因為大家的SQL語法都差不多。
- SQL語言功能很強，十分簡潔，核心功能只用了9個動詞。語法接近英語口語，所以，用戶很容易學習和使用。
數倉與SQL
- 雖然SQL語言本身是針對數據庫軟件設計的，但是在數據倉庫領域，尤其是大數據數倉領域，很多數倉軟件都會去支持SQL語法
- 原因在於一是用戶學習SQL成本低，二是SQL語言對於數據分析真的十分友好，愛不釋手
SQL全稱叫做結構化查詢語言，結構化是什麼意思？
結構化數據
- 結構化數據也稱作行數據，是由二維表結構來邏輯表達和實現的數據，嚴格地遵循數據格式與長度規範，主要通過關係型數據庫進行存儲和管理。
- 與結構化數據相對的是不適於由數據庫二維表來表現的非結構化數據，包括所有格式的辦公文檔、XML、HTML、 各類報表、圖片和音頻、視頻信息等。
- 通俗來說，結構化數據會有嚴格的行列對齊，便於解讀與理解。
SQL語法分類
SQL主要語法分為兩個部分：數據定義語言 (DDL)和數據操縱語言 (DML) 。
-
DDL語法使我們有能力創建或刪除表，以及數據庫、索引等各種對象，但是不涉及表中具體數據操作：
- CREATE DATABASE – 創建新數據庫
- CREATE TABLE – 創建新表
-
DML語法是我們有能力針對表中的數據進行插入、更新、刪除、查詢操作：
- SELECT – 從數據庫表中獲取數據
- UPDATE – 更新數據庫表中的數據
- DELETE – 從數據庫表中刪除數據
- INSERT – 向數據庫表中插入數據
2. Apache Hive
2.1 概述
什麼是Hive？
- Apache Hive是一款建立在Hadoop之上的開源數據倉庫系統，可以將存儲在Hadoop文件中的結構化、半結構化數據文件映射為一張數據庫表，基於表提供了一種類似SQL的查詢模型，稱為Hive查詢語言（HQL），用於訪問和 分析存儲在Hadoop文件中的大型數據集。
- Hive核心是將HQL轉換為MapReduce程序，然後將程序提交到Hadoop群集執行。
- Hive由Facebook實現並開源。
為什麼使用Hive？
- 使用Hadoop MapReduce直接處理數據所面臨的問題
- 人員學習成本太高，需要掌握java語言
- MapReduce實現複雜查詢邏輯開發難度太大
- 使用Hive處理數據的好處
- 操作接口採用類SQL語法，提供快速開發的能力（簡單、容易上手）
- 避免直接寫MapReduce，減少開發人員的學習成本
- 支持自定義函數，功能擴展很方便
- 背靠Hadoop，擅長存儲分析海量數據集
Hive和Hadoop關係
- 從功能來說，數據倉庫軟件，至少需要具備下述兩種能力：
- 存儲數據的能力
- 分析數據的能力
- Apache Hive作為一款大數據時代的數據倉庫軟件，當然也具備上述兩種能力。只不過Hive並不是自己實現了上述 兩種能力，而是藉助Hadoop
- Hive利用HDFS存儲數據
- Hive利用MapReduce查詢分析數據
- 這樣突然發現Hive沒啥用，不過是套殼Hadoop罷了。其實不然，Hive的最大的魅力在於用戶專註於編寫HQL， Hive幫您轉換成為MapReduce程序完成對數據的分析
2.2 如何模擬實現Hive功能？
如果讓您設計Hive這款軟件，要求能夠實現用戶只編寫sql語句，Hive自動將sql轉換MapReduce程序，處理位於HDFS上的結構化數據。如何實現？
案例
在HDFS文件系統上有一個文件，路徑為/data/china_user.txt
需求：統計來自於上海年齡大於25歲的用戶有多少個？
場景目的
重點理解下面兩點：
- Hive能將數據文件映射成為一張表，這個映射是指什麼？
- Hive軟件本身到底承擔了什麼功能職責？
映射信息記錄
- 映射在數學上稱之為一種對應關係，比如y=x+1，對於每一個x的值都有與之對應的y的值
- 在hive中能夠寫sql處理的前提是針對錶，而不是針對文件，因此需要將文件和表之間的對應關係描述記錄清楚。映射信息專業的叫法稱之為元數據信息（元數據是指用來描述數據的數據 metadata）
- 具體來看，要記錄的元數據信息包括：
- 表對應着哪個文件（位置信息）
- 表的列對應着文件哪一個字段（順序信息）
- 文件字段之間的分隔符是什麼
SQL語法解析、編譯
- 用戶寫完sql之後，hive需要針對sql進行語法校驗，並且根據記錄的元數據信息解讀sql背後的含義，制定執行計劃 。
- 並且把執行計劃轉換成MapReduce程序來具體執行，把執行的結果封裝返回給用戶。
對Hive的理解
- Hive能將數據文件映射成為一張表，這個映射是指什麼？
- 文件和表之間的對應關係
- Hive軟件本身到底承擔了什麼功能職責？
- SQL語法解析編譯稱為MapReduce
最終效果
- 基於上述分析，最終要想模擬實現的Hive的功能，大致需要下圖所示組件參與其中。
- 從中可以感受一下Hive承擔了什麼職責，當然，也可以把這個理解為Hive的架構圖
2.3 Apache Hive架構
Hive架構圖
用戶接口
- 包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)為shell命令行；Hive中的Thrift服務器允許外部客戶端通過網絡與Hive進行交互，類似於JDBC或ODBC協議。WebGUI是通過瀏覽器訪問Hive。
元數據存儲
- 通常是存儲在關係數據庫如 mysql/derby中。Hive 中的元數據包括表的名字，表的列和分區及其屬性，表的屬性（是否為外部表等），表的數據所在目錄等。
Driver驅動程序
- 包括語法解析器、計劃編譯器、優化器、執行器
- 完成 HQL 查詢語句從詞法分析、語法分析、編譯、優化以及查詢計劃的生成。生成的查詢計劃存儲在 HDFS 中，並在隨後有執行引擎調用執行
執行引擎
- Hive本身並不直接處理數據文件。而是通過執行引擎處理。當下Hive支持MapReduce、Tez、Spark執行引擎。
3. Apache Hive安裝部署
3.1 元數據
什麼是元數據
- 元數據（Metadata），又稱中介數據、中繼數據，為描述數據的數據（data about data），主要是描述數據屬性（property）的信息，用來支持如指示存儲位置、歷史數據、資源查找、文件記錄等功能。
Hive Metadata
- Hive Metadata即Hive的元數據
- 包含用Hive創建的database、table、表的位置、類型、屬性，字段順序類型等元信息
- 元數據存儲在關係型數據庫中。如hive內置的Derby、或者第三方如MySQL等
Hive Metatore
- Metastore即元數據服務。Metastore服務的作用是管理metadata元數據，對外暴露服務地址，讓各種客戶端通過連接metastore服務，由metastore再去連接MySQL數據庫來存取元數據。
- 有了metastore服務，就可以有多個客戶端同時連接，而且這些客戶端不需要知道MySQL數據庫的用戶名和密碼，只需要連接metastore 服務即可。某種程度上也保證了hive元數據的安全
metastore配置方式
- metastore服務配置有3種模式：內嵌模式、本地模式、遠程模式。
- 區分3種配置方式的關鍵是弄清楚兩個問題：
- Metastore服務是否需要單獨配置、單獨啟動？
- Metadata是存儲在內置的derby中，還是第三方RDBMS,比如MySQL
- 本文使用企業推薦模式–遠程模式部署。
metastore遠程模式
- 在生產環境中，建議用遠程模式來配置Hive Metastore。在這種情況下，其他依賴hive的軟件都可以通過Metastore訪問hive。由於還可以完全屏蔽數據庫層，因此這也帶來了更好的可管理性/安全性。
3.2 部署實戰
安裝前準備
- 由於Apache Hive是一款基於Hadoop的數據倉庫軟件，通常部署運行在Linux系統之上。因此不管使用何種方式配置Hive Metastore，必須要先保證服務器的基礎環境正常，Hadoop集群健康可用。
- 服務器基礎環境
- 集群時間同步、防火牆關閉、主機Host映射、免密登錄、JDK安裝
- Hadoop集群健康可用
- 啟動Hive之前必須先啟動Hadoop集群。特別要注意，需等待HDFS安全模式關閉之後再啟動運行Hive。
- Hive不是分佈式安裝運行的軟件，其分佈式的特性主要藉由Hadoop完成。包括分佈式存儲、分佈式計算。
Hadoop與Hive整合
- 因為Hive需要把數據存儲在HDFS上，並且通過MapReduce作為執行引擎處理數據
- 因此需要在Hadoop中添加相關配置屬性，以滿足Hive在Hadoop上運行。
- 修改Hadoop中core-site.xml，並且Hadoop集群同步配置文件，重啟生效。
<!-- 整合hive -->
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
Step1 MySQL安裝
Linux
-
命令行安裝
sudo apt-get install mysql-server -
查看運行狀態
systemctl status mysql # 顯示running說明已經在運行 -
查看默認用戶密碼
sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = XXXX password = XXXXX socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = XXXX password = XXXX socket = /var/run/mysqld/mysqld.sock -
使用默認用戶登陸
mysql -uXXX -p -
查看用戶權限
use mysql; select host, user, plugin from user; # 可以看到root用戶只能localhost登陸 -
設置root運行遠程登陸
# 更改localhost為 % update user set host='%' where user='root'; # 刷新權限 flush privileges; -
修改Mysql加密規則和root密碼
MySQL8.0之前的版本密碼加密規則：mysql_native_password
MySQL8.0密碼加密規則：caching_sha2_password# 修改加密方式 ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; # 重新設置root的密碼 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密碼'; FLUSH PRIVILEGES; select host, user, plugin from user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | root | mysql_native_password | | localhost | debian-sys-maint | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | +-----------+------------------+-----------------------+ -
修改bind-address開放遠程登陸
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf # 修改 bind-address = 0.0.0.0 -
重啟MySql服務
systemctl restart mysql -
查看防火牆是否放行3306端口
namp -p3306 localhost
如果不是open則需要，關閉防火牆
MySQL安裝後的目錄結構分析
(此結構只針對於使用apt-get install 在線安裝情況)
- 數據庫存放目錄：
/var/lib/mysql/ - 相關配置文件存放目錄：
/usr/share/mysql - 相關命令存放目錄：
/usr/bin(mysqladmin mysqldump等命令) - 啟動腳步存放目錄：
/etc/rc.d/init.d/
MySQL的卸載
# 停止進程
sudo kill $(pgrep mysql)
sudo apt-get remove --purge mysql-\*
Step2 Hive安裝
（node1安裝即可）
tar zxvf apache-hive-3.1.2-bin.tar.gz
mv apache-hive-3.1.2-bin/ hive
# 解決Hive與Hadoop之間guava版本差異
cd /export/server/apache-hive-3.1.2-bin/
rm -rf lib/guava-19.0.jar
cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar \
./lib/
Step3 修改hive-env.sh
cd /export/server/apache-hive-3.1.2-bin/conf
mv hive-env.sh.template hive-env.sh
vim hive-env.sh
export HADOOP_HOME=/opt/hadoop-3.3.0
export HIVE_CONF_DIR=/opt/hive-3.1.3/conf
export HIVE_AUX_JARS_PATH=/opt/hive-3.1.3/lib
Step4 新增hive-site.xml
<configuration>
<!-- 存儲元數據mysql相關配置 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://XXX(hive節點名):3306/hive3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>sql數據庫的用戶名</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>sql密碼需要自己填寫</value>
</property>
<!-- H2S運行綁定host -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>XXX(hive節點名)</value>
</property>
<!-- 遠程模式部署metastore metastore地址 -->
<property>
<name>hive.metastore.uris</name>
<value>thrift://XXX(hive節點名):9083</value>
</property>
<!-- 關閉元數據存儲授權 -->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
</configuration>
Step5 添加驅動、初始化
-
上傳MySQL JDBC驅動到Hive安裝包lib路徑下 下載鏈接
-
初始化Hive的元數據
cd /export/server/apache-hive-3.1.2-bin/ bin/schematool -initSchema -dbType mysql -verbos #初始化成功會在mysql中創建74張表
3.3 客戶端使用
1. 自帶客戶端
bin/hive,bin/beeline- Hive發展至今，總共歷經了兩代客戶端工具。
- 第一代客戶端（deprecated不推薦使用）：
$HIVE_HOME/bin/hive, 是一個shellUtil。主要功能：一是可用於以交互或批處理模式運行Hive查詢；二是用於Hive相關服務的啟動，比如metastore服務。 - 第二代客戶端（recommended 推薦使用）：
$HIVE_HOME/bin/beeline，是一個JDBC客戶端，是官方強烈推薦使用的Hive命令行工具，和第一代客戶端相比，性能加強安全性提高。
HiveServer2服務介紹
- 遠程模式下beeline通過 Thrift 連接到單獨的HiveServer2服務上，這也是官方推薦在生產環境中使用的模式
- HiveServer2支持多客戶端的並發和身份認證，旨在為開放API客戶端如JDBC、ODBC提供更好的支持
關係梳理
- HiveServer2通過Metastore服務讀寫元數據。所以在遠程模式下，啟動HiveServer2之前必須先首先啟動metastore服務
- 特別注意：遠程模式下，Beeline客戶端只能通過HiveServer2服務訪問Hive。而bin/hive是通過Metastore服務訪問的。具體關係如下：
bin/beeline客戶端使用
-
在hive安裝的服務器上，首先啟動metastore服務，然後啟動hiveserver2服務
#先啟動metastore服務 然後啟動hiveserver2服務 nohup /opt/hive-3.1.3/bin/hive --service metastore & nohup /opt/hive-3.1.3/bin/hive --service hiveserver2 & -
在node3上使用beeline客戶端進行連接訪問。需要注意hiveserver2服務啟動之後需要稍等一會才可以對外提供服務。
-
Beeline是JDBC的客戶端，通過JDBC協議和Hiveserver2服務進行通信，協議的地址是： jdbc:hive2://node1:10000
[root@node3 ~]# /opt/hive-3.1.3/bin/beeline Beeline version 3.1.2 by Apache Hive beeline> ! connect jdbc:hive2://node1:10000 Connecting to jdbc:hive2://node1:10000 Enter username for jdbc:hive2://node1:10000: root Enter password for jdbc:hive2://node1:10000: Connected to: Apache Hive (version 3.1.2) Driver: Hive JDBC (version 3.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://node1:10000>
2. Hive 可視化客戶端
- DataGrip、Dbeaver、SQuirrel SQL Client等
- 可以在Windows、MAC平台中通過JDBC連接HiveServer2的圖形界面工具
- 這類工具往往專門針對SQL類軟件進行開發優化、頁面美觀大方，操作簡潔，更重要的是SQL編輯環境優
- SQL語法智能提示補全、關鍵字高亮、查詢結果智能顯示、按鈕操作大於命令操作
Datagrip
- DataGrip是由JetBrains公司推出的數據庫管理軟件，DataGrip支持幾乎所有主流的關係數據庫產品，如DB2、Derby、MySQL、Oracle、SQL Server等，也支持幾乎所有主流的大數據生態圈SQL軟件，並且提供了簡單易用的界面，開發者上手幾乎不會遇到任何困難
3.4 Set命令的使用
-
Hive命令行下執行set命令【僅當前會話有效】
hive> set hive.cli.print.current.db=true; # 顯示當前數據庫名稱 -
Hive腳本
~/.hiverc中配置set命令【當前用戶有效】
3.5 日誌配置
-
Hive運行時日誌
cd /opt/hive-3.1.3/conf mv hive-log4j.properties.template hive-log4j.properties # 更改以下選項，日誌級別可以根據需要調整# list of properties property.hive.log.level = WARN property.hive.root.logger = DRFA # 更改路徑 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = hive.log property.hive.perflogger.log.level = INFO -
Hive任務執行日誌
cd /opt/hive-3.1.3/conf mv hive-exec-log4j.properties.template hive-exec-log4j.properties # 更改以下選項，日誌級別可以根據需要調整# list of properties property.hive.log.level = WARN property.hive.root.logger = FA property.hive.query.id = hadoop # 更改路徑 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = ${sys:hive.query.id}.log
4. Hive SQL語言
Hive數據模型總覽
4.1 數據庫&建庫
SQL中DDL語法的作用
- 數據定義語言 (Data Definition Language, DDL)，是SQL語言集中對數據庫內部的對象結構進行創建，刪除，修改等的操作語言，這些數據庫對象包括database、table等
- DDL核心語法由CREATE、ALTER與DROP三個所組成。DDL並不涉及表內部數據的操作
- Hive SQL（HQL）與標準SQL的語法大同小異，基本相通
- 基於Hive的設計、使用特點，HQL中create語法（尤其create table）將是學習掌握Hive DDL語法的重中之重
- 建表是否成功直接影響數據文件是否映射成功，進而影響後續是否可以基於SQL分析數據。通俗點說，沒有表，表沒有數據，你用Hive分析什麼呢？
數據庫database
- 在Hive中，默認的數據庫叫做default，存儲數據位置位於HDFS的
/user/hive/warehouse下 - 用戶自己創建的數據庫存儲位置是
/user/hive/warehouse/database_name.db下
create database
-
create database用於創建新的數據庫
- COMMENT：數據庫的注釋說明語句
- LOCATION：指定數據庫在HDFS存儲位置，默認
/user/hive/warehouse/dbname.db - WITH DBPROPERTIES：用於指定一些數據庫的屬性配置
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; -
例子：創建數據庫itcast
注意：如果需要使用location指定路徑的時候，最好指向的是一個新創建的空文件夾
create database if not exists itcast comment "this is my first db" with dbproperties ('createdBy'='Allen');
use database
- 選擇特定的數據庫：切換當前會話使用哪一個數據庫進行操作
drop database
-
刪除數據庫
- 默認行為是RESTRICT，這意味着僅在數據庫為空時才刪除它
- 要刪除帶有表的數據庫（不為空的數據庫），我們可以使用CASCADE
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
4.2 表&建表
表Table
- 一個數據庫通常包含一個或多個表。每個表由一個名字標識（例如「客戶」或者「訂單」）。
- 表包含帶有數據的記錄（行）
建表語法樹（基礎）
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];
-- 簡寫
CREATE TABLE table_name (col_name data_type);
數據類型
- Hive數據類型指的是表中列的字段類型
- 整體分為兩類：原生數據類型（primitive data type）和複雜數據類型（complex data type）
- 最常用的數據類型是字符串String和數字類型Int。
分隔符指定語法
-
LazySimpleSerDe是Hive默認的，包含4種子語法，分別用於指定字段之間、集合元素之間、map映射 kv之間、 換行的分隔符號
-
在建表的時候可以根據數據的特點靈活搭配使用
Hive默認分隔符
- Hive建表時如果沒有row format語法指定分隔符，則採用默認分隔符
- 默認的分割符是’\001’，是一種特殊的字符，使用的是ASCII編碼的值，鍵盤是打不出來的
- 在vim編輯器中，連續按下Ctrl+v/Ctrl+a即可輸入’\001′ ，顯示^A
- 在一些文本編輯器中將以SOH的形式顯示
實例練習
- 數據類型
- 分隔符指定
- 默認分隔符
實例1
- 文件archer.txt中記錄了手游《王者榮耀》射手的相關信息，包括生命、物防、物攻等屬性信息，其中字段之間分隔符為製表符\t,要求在Hive中建表映射成功該文件。
- 字段含義：id、name（英雄名稱）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻） 、defense_max（最大物防）、attack_range（攻擊範圍）、role_main（主要定位）、role_assist（次要定位）
- 字段都是基本類型，字段的順序需要注意一下
- 字段之間的分隔符是製表符，需要使用row format語法進行指定
建表
create database if not exists learnsql;
use learnsql;
create table t_archer (
id int comment "ID",
name string comment "英雄名稱",
hp_max int comment "最大生命",
mp_max int comment "最大法力",
attack_max int comment "最高物理攻擊",
defense_max int comment "最高物理防禦",
attack_range string comment "攻擊範圍",
role_main string comment "主要定位",
role_assist string comment "次要定位"
) comment "英雄信息"
ROW FORMAT delimited
fields terminated by '\t';
上傳文件
#把文件從課程資料中首先上傳到node1 linux系統上
#執行命令把文件上傳到HDFS表所對應的目錄下
hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer
查看錶
在bin/beeline客戶端查看 or 可視化工具
select * from t_archer;
---
1,后羿,5986,1784,396,336,remotely,archer,
2,馬可波羅,5584,200,362,344,remotely,archer,
3,魯班七號,5989,1756,400,323,remotely,archer,
4,李元芳,5725,1770,396,340,remotely,archer,
5,孫尚香,6014,1756,411,346,remotely,archer,
6,黃忠,5898,1784,403,319,remotely,archer,
7,狄仁傑,5710,1770,376,338,remotely,archer,
8,虞姬,5669,1770,407,329,remotely,archer,
9,成吉思汗,5799,1742,394,329,remotely,archer,
10,百里守約,5611,1784,410,329,remotely,archer,assassin
表類型
-
內部表
- Hive中的默認表類型，表數據默認存儲在warehouse目錄中
- 在加載數據的過程中，實際數據會被移動到warehouse目錄中
- 刪除表時，表中的數據和元數據將會被同時刪除
-
外部表
-
檢表語句中包含External的表叫做外部表
-
外部表在加載數據的時候，實際數據並不會移動到warehouse目錄中，只是與外部數據建立一個鏈接（映射關係）
-
當刪除一個外部表是，只刪除元數據，不刪除表中的數據，僅刪除表和數據之間的鏈接
create external table external_table( key string )location '/data/external'; -- 目錄是hdfs中的位置，若不存在，會自動創建 -- 添加數據會進入到此目錄下，不會進入到warehouse相互轉換
-- 內部錶轉外部表 alter table tblName set tblproperties('external'='true'); -- 外部錶轉內部表 alter table tblName set tblproperties('external'='false');
-
-
分區表
-
分區可以理解為分類，通過分區把不同類型數據放到不同目錄
-
分區的標準就是指定分區字段，分區字段可以有一個或多個
-
分區表的意義在於優化查詢，查詢時，盡量利用分區字段，如果不使用分區字段，就會全表掃描，最典型的一個場景就是把天作為分區字段，查詢的時候指定天
create table partition_1( id int, name string )partitioned by (dt string) row format delimited fields terminated by '\t'; -- 需要指定分區 load data local inpath '/root/Documents/partition_1.data' into table partition_1 partition(dt='20200101'); -- 展示分區情況 show partitions partition_1; -
外部分區表
工作中最常見的表：外部表+分區表
刪除分區時，分區數據不會被刪除
-
-
桶表
-
桶表是對數據進行哈希取值，然後放到不同文件中存儲
-
物理上，每個通就是表（或分區）里的一個文件
-
桶表的作用
- 數據抽樣
- 提高某些查詢效率。例如join
create table bucket_tb( id int )clustered by (id) into 4 buckets; -- 根據id值，分成4個桶 -- 直接使用load命令是沒有辦法分桶的 -- 設置有幾個桶就有幾個reduce任務 set hive.enforce.bucketing=true; -- 只能使用insert ... select進行插入數據 insert into table bucket_tb select id from b_source where id is not null; -- 抽樣數據 -- tablesample(bucket x out of y on id); -- 注意：y>=x -- y：表示把桶表中的數據隨機分為多少桶 -- x: 表示取出第幾桶的數據 select * from bucket_tb tablesample(bucket 1 out of 4 on id); -- 如果a、b表都是id的桶表，就不會產生全表的笛卡爾積，只會在同id內做笛卡爾積 select a.id,a.name,b.addr from a join b on a.id = b.id;
-
4.3 show
- Show相關的語句可以幫助用戶查詢相關信息。
- 比如我們最常使用的查詢當前數據庫下有哪些表 show tables.
--1、顯示所有數據庫 SCHEMAS和DATABASES的用法 功能一樣
show databases;
show schemas;
--2、顯示當前數據庫所有表
show tables;
SHOW TABLES [IN database_name]; --指定某個數據庫
--3、查詢顯示一張表的元數據信息
desc formatted t_team_ace_player;
4.4 DML—Load
Load加載數據
- 在Hive中建表成功之後，就會在HDFS上創建一個與之對應的文件夾，且文件夾名字就是表名
- 文件夾父路徑是由參數hive.metastore.warehouse.dir控制，默認值是/user/hive/warehouse
-
不管路徑在哪裡，只有把數據文件移動到對應的表文件夾下面，Hive才能映射解析成功
-
最原始暴力的方式就是使用
hadoop fs –put|-mv等方式直接將數據移動到表文件夾下 -
但是，Hive官方推薦使用Load命令將數據加載到表中
因為採用hadoop fs上傳文件就跨過了hive去操作底層，這不符合操作邏輯也不安全
Load語法
- Load英文單詞的含義為：加載、裝載
- 所謂加載是指：將數據文件移動到與Hive表對應的位置，移動時是純複製、移動操作
- local：純複製
- 非local：移動
- 純複製、移動指在數據load加載到表中時，Hive不會對表中的數據內容進行任何轉換，任何操作
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
語法規則之LOCAL
-
指定LOCAL， 將在本地文件系統中查找文件路徑。
- 若指定相對路徑，將相對於用戶的當前工作目錄進行解釋
- 用戶也可以為本地文件指定完整的URI-例如：
file:///user/hive/project/data1
-
沒有指定LOCAL關鍵字
- 如果
filepath指向的是一個完整的URI，會直接使用這個URI - 如果沒有指定schema，Hive會使用在hadoop配置文件中參數
fs.default.name指定的（不出意外，都是HDFS）
- 如果
-
LOCAL本地是哪裡？
-
如果對HiveServer2服務運行此命令
本地文件系統指的是Hiveserver2服務所在機器的本地Linux文件系統，不是Hive客戶端所在的本地文件系統
-
語法規則之filepath
filepath表示待移動數據的路徑。可以指向文件（在這種情況下，Hive將文件移動到表中），也可以指向目錄（在 這種情況下，Hive將把該目錄中的所有文件移動到表中）。filepath文件路徑支持下面三種形式，要結合LOCAL關鍵字一起考慮：- 相對路徑，例如：
project/data1 - 絕對路徑，例如：
/user/hive/project/data1 - 具有
schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1
- 相對路徑，例如：
實例1
--step1:建表
--建表student_local 用於演示從本地加載數據
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_local;
--建表student_HDFS 用於演示從HDFS加載數據
create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_HDFS;
--建議使用beeline客戶端 可以顯示出加載過程日誌信息
--step2:加載數據
-- 從本地加載數據 數據位於HS2（node1）本地文件系統 本質是hadoop fs -put上傳操作
LOAD DATA LOCAL INPATH '/home/node1/Document/students.txt' INTO TABLE student_local;
--從HDFS加載數據 數據位於HDFS文件系統根目錄下 本質是hadoop fs -mv 移動操作
--先把數據上傳到HDFS上 hadoop fs -put /home/node1/Document/students.txt
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
4.5 DML—Insert
慢
語法功能
- Hive官方推薦加載數據的方式
- 清洗數據成為結構化文件，再使用Load語法加載數據到表中。這樣的效率更高。
- 也可以使用insert語法把數據插入到指定的表中，最常用的配合是把查詢返回的結果插入到另一張表中
insert+select
- insert+select表示：將後面查詢返回的結果作為內容插入到指定表中
- 需要保證查詢結果列的數目和需要插入數據表格的列數目一致
- 如果查詢出來的數據類型和插入表格對應的列數據類型不一致，將會進行轉換，但是不能保證轉換一定成功，轉換失敗的數據將會為NULL
INSERT INTO TABLE tablename select_statement1 FROM from_statement;
4.6 DML—查詢數據
Select語法樹
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];
具體語法與sql類似，可參考此文章。
5. Hive函數
5.1 基本函數
查看所有內置函數
show functions;
查看指定函數信息
desc function FUNC;
查看指定函數擴展信息
desc function extended FUNC;
字符串函數
- 字符串長度函數：length
- 字符串反轉函數：reverse
- 字符串連接函數：concat
- 帶分隔符字符串連接函數：concat_ws
- 字符串截取函數：substr,substring
------------String Functions 字符串函數------------
select length("itcast");
select reverse("itcast");
select concat("angela","baby");
--帶分隔符字符串連接函數：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--字符串截取函數：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是從1開始的索引，如果為負數則倒着數
select substr("angelababy",2,2);
--分割字符串函數: split(str, regex)
select split('apache hive', ' ');
日期函數
----------- Date Functions 日期函數 -----------------
--獲取當前日期: current_date
select current_date();
--獲取當前UNIX時間戳函數: unix_timestamp
select unix_timestamp();
--日期轉UNIX時間戳函數: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期轉UNIX時間戳函數: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX時間戳轉日期函數: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比較函數: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函數: date_add
select date_add('2012-02-28',10);
--日期減少函數: date_sub
select date_sub('2012-01-1',10);
數學函數
----Mathematical Functions 數學函數-------------
--取整函數: round 返回double類型的整數值部分 （遵循四捨五入）
select round(3.1415926);
--指定精度取整函數: round(double a, int d) 返回指定精度d的double類型
select round(3.1415926,4);
--取隨機數函數: rand 每次執行都不一樣 返回一個0到1範圍內的隨機數
select rand();
--指定種子取隨機數函數: rand(int seed) 得到一個穩定的隨機數序列
select rand(3);
條件函數
主要用於條件判斷、邏輯判斷轉換這樣的場合
-----Conditional Functions 條件函數------------------
select * from student limit 3;
--if條件判斷: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空值轉換函數: nvl(T value, T default_value)
select nvl("allen","cool");
select nvl(null,"cool");
--條件轉換函數: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;
5.2 分組排序TopN
ROW_NUMBER()：對每一行賦其行值
OVER()：分組
取出每個學科中的前三名
-- student_score table
-- id,name,sub,score
-- 3,zs1,english,89
-- 4,zs2,chinese,60
-- 5,zs2,math,75
-- 6,zs2,english,80
-- 7,zs3,chinese,79
-- 8,zs3,math,83
-- 9,zs3,english,72
-- 10,zs4,chinese,90
-- 11,zs4,math,76
-- 12,zs4,english,80
-- 13,zs5,chinese,98
-- 14,zs5,math,80
-- 15,zs5,english,70
select *
from (
select *, row_number() over(partition by sub order by score desc) as num
from student_score
) s
where s.num<=3;
RANK()：排名，存在相等的排名，比如 1、2、2、4這樣的排名
DENSE_RANK()：密集排名，與rank不同的是1、2、2、3
5.3 行轉列&列轉行
行轉列
CONCAT_WS()：連接元素
COLLECT_LIST()：聚合元素成list
COLLECT_SET()：聚合元素成set
-- student_favor table
-- name favor
-- zs swing
-- zs footbal
-- zs sing
-- zs codeing
-- zs swing
-- 將上面的多行，轉換成單行
select name, collect_list(favor) as favor_list from student_favors group by name;
select name, collect_set(favor) as favor_list from student_favors group by name;
select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;
列轉行
SPLIT()：分割字符串，返回數組
EXPLODE()：將數組元素，轉換成多行，或者將map元素轉換為多行和多列
LATERAL VIEW：
1.Lateral View 用於和UDTF函數【explode,split】結合來使用。
2.首先通過UDTF函數將數據拆分成多行，再將多行結果組合成一個支持別名的虛擬表。
3..主要解決在select使用UDTF做查詢的過程中查詢只能包含單個UDTF，不能包含其它字段以及多個UDTF的情況。
4.語法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)
-- name favorlist
-- zs swing,footbal,sing
-- ls codeing,swing
-- 轉換成下面的形式
-- zs swing
-- zs footbal
-- zs sing
-- ls codeing
-- ls swing
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;
-- 這樣寫會直接報錯，因為name的個數與explode後的行數不等了
select name,explode(split(favorlist,',')) from student_favors_2 ;
5.4 排序函數
ORDER BY：全局有序
SORT BY：局部有序（單個reduce內有序）
DISTRIBUTE BY：對數據進行分區，一般和sort by結合使用
CLUSTER BY：cluster by id = distribute by id sort by id 等同於，但不支持desc
5.5 分組和去重
-- 統計order 表中name 去重之後的數據量
select count(distinct name) from order
-- 性能很低，要在一個reduce任務內完成
-- 分為兩步，可以使用多個reduce任務完成，效率高
select count(*) from (select name from order group by name) tmp
6. 擴展
數據傾斜小實例
之前在Hadoop三大組件中最後一個單元提到過
select a.Key, SUM(a.Cnt) as Cnt
from (
select Key, COUNT(*) as Cnt
From TableName
Group By Key,
-- 將key001佔比大的數據打散，可以根據其實際比例來進行，這樣可以分配到多個reduce任務中
CASE
When key = 'Key001' THEN Hash(Random()) % 50
ELSE 0
END
) a
GROUP by a.Key;
