---

---

往期好文推薦：
🔶🔷Hadoop深入淺出 ——三大組件HDFS、MapReduce、Yarn框架結構的深入決議式地詳細學習【建議收藏！！！】

🔶🔷Redis從青銅到王者，從環境搭建到熟練使用，看這一篇就夠了，超全整理詳細決議，趕緊收藏吧！！！

🔶🔷硬核整理四萬字，學會資料庫只要一篇就夠了，盤它！MySQL基本操作以及常用的內置函式匯總整理

🔶🔷Redis主從復制 以及 集群搭建 詳細步驟決議，趕快收藏練手吧！

🔶🔷Hadoop集群HDFS、YARN高可用HA詳細配置步驟說明，附Zookeeper搭建詳細步驟【建議收藏！！！】

🔶🔷SQL進階-深入理解MySQL，JDBC連接MySQL實作增刪改查，趕快收藏吧！

🔶🔷初識鴻蒙OS，你好，HarmonyOS！

🔶🔷【小白學Java】D25 》》》Java中的各種集合大匯總，學習整理

🔶🔷hadoop入門簡介

---

💜🧡💛制作不易，各位大佬們給點鼓勵！
🧡💛💚點贊👍 ? 收藏? ? 關注?
💛💚💙歡迎各位大佬指教，一鍵三連走起！

》》》本篇文章主要是與大家分享，Hive的一些常見操作，磁區，分桶，視窗函式等等，以及Hive的HQL的使用練習，如有錯誤，煩請大佬指教，希望大家能夠喜歡！

---

目錄

🧡一、了解Hive
💜1、Hive的概念及架構
💜2、Hive與傳統資料庫比較
💜3、Hive的資料存盤格式
💜4、Hive操作客戶端
🧡二、Hive的基本語法
💜1、Hive建表語法
💜2、Hive加載資料
💜3、Hive 內部表（Managed tables）vs 外部表（External tables）
💜4、Hive 磁區
💜5、Hive動態磁區
💜6、Hive分桶
💜7、Hive連接JDBC
🧡三、Hive的資料型別
💜1、基本資料型別
💜2、日期型別
💜3、復雜資料型別
🧡四、Hive HQL使用語法
💜1、HQL語法-DDL
💜2、HQL語法-DML
🧡五、Hive HQL使用注意
🧡六、Hive 的函式使用
💜1、Hive-常用函式
💚（1）關系運算
💚（2）數值計算
💚（3） 條件函式
💚（4）日期函式
💚（5) 字串函式
💜2、Hive-高級函式
💚（1）視窗函式（開窗函式）：用戶分組中開窗
💚（2）Hive 行轉列
💚（3）Hive 列轉行
💚（4）Hive自定義函式UserDefineFunction
? UDF：一進一出
?UDTF：一進多出
?UDAF：多進一出
💜3、Hive 中的wordCount
🧡七、Hive 的Shell使用

---

一、了解Hive

1、Hive的概念及架構

點我回傳目錄

Hive 是建立在 Hadoop 上的資料倉庫基礎構架，它提供了一系列的工具，可以用來進行資料提取轉化加載（ETL ），這是一種可以存盤、查詢和分析存盤在 Hadoop 中的大規模資料的機制，Hive 定義了簡單的類 SQL 查詢語言，稱為 HQL ，它允許熟悉 SQL 的用戶查詢資料，同時，這個語言也允許熟悉 MapReduce 的開發者開發自定義的 mapper 和 reducer 來處理內建的 mapper 和 reducer 無法完成的復雜的分析作業，Hive是SQL決議引擎，它將SQL陳述句轉譯成Map/Reduce Job然后在Hadoop執行，Hive的表其實就是HDFS的目錄，按表名把檔案夾分開，如果是磁區表，則磁區值是子檔案夾，可以直接在Map/Reduce Job里使用這些資料，Hive相當于hadoop的客戶端工具，部署時不一定放在集群管理節點中，也可以放在某個節點上，

資料倉庫，英文名稱為Data Warehouse，可簡寫為DW或DWH，資料倉庫，是為企業所有級別的決策制定程序，提供所有型別資料支持的戰略集合，它出于分析性報告和決策支持目的而創建，為需要業務智能的企業，提供指導業務流程改進、監視時間、成本、質量以及控制，

Hive的版本介紹：
0.13和.14版本，穩定版本，但是不支持更新洗掉操作，
1.2.1和1.2.2 版本，穩定版本，為Hive2版本（是主流版本）
1.2.1的程式只能連接hive1.2.1 的hiveserver2

2、Hive與傳統資料庫比較

點我回傳目錄

查詢語言	HiveQL	SQL
資料存盤位置	HDFS	Raw Device or 本地FS
資料格式	用戶定義	系統決定
資料更新	不支持（1.x以后版本支持）	支持
索引	新版本有，但弱	有
執行	MapReduce	Executor
執行延遲	高	低
可擴展性	高	低
資料規模	大	小

1. 查詢語言，類 SQL 的查詢語言 HQL，熟悉 SQL 開發的開發者可以很方便的使用 Hive 進行開發，
2. 資料存盤位置，所有 Hive 的資料都是存盤在 HDFS 中的，而資料庫則可以將資料保存在塊設備或者本地檔案系統中，
3. 資料格式，Hive 中沒有定義專門的資料格式，而在資料庫中，所有資料都會按照一定的組織存盤，因此，資料庫加載資料的程序會比較耗時，
4. 資料更新，Hive 對資料的改寫和添加比較榷訓，0.14版本之后支持，需要啟動配置項，而資料庫中的資料通常是需要經常進行修改的，
5. 索引，Hive 在加載資料的程序中不會對資料進行任何處理，因此訪問延遲較高，資料庫可以有很高的效率，較低的延遲，由于資料的訪問延遲較高，決定了 Hive 不適合在線資料查詢，
6. 執行計算，Hive 中執行是通過 MapReduce 來實作的而資料庫通常有自己的執行引擎，
7. 資料規模，由于 Hive 建立在集群上并可以利用 MapReduce 進行并行計算，因此可以支持很大規模的資料；對應的，資料庫可以支持的資料規模較小，

3、Hive的資料存盤格式

點我回傳目錄

• Hive的資料存盤基于Hadoop HDFS，

• Hive沒有專門的資料檔案格式，常見的有以下幾種：TEXTFILE、SEQUENCEFILE、AVRO、RCFILE、ORCFILE、PARQUET，

---

下面我們詳細的看一下Hive的常見資料格式：

---

• TextFile:
  TEXTFILE 即正常的文本格式，是Hive默認檔案存盤格式，因為大多數情況下源資料檔案都是以text檔案格式保存（便于查看驗數和防止亂碼），此種格式的表檔案在HDFS上是明文，可用hadoop fs -cat命令查看，從HDFS上get下來后也可以直接讀取，
  TEXTFILE 存盤檔案默認每一行就是一條記錄，可以指定任意的分隔符進行欄位間的分割，但這個格式無壓縮，需要的存盤空間很大， 雖然可以結合Gzip、Bzip2、Snappy等使用，使用這種方式，Hive不會對資料進行切分，從而無法對資料進行并行操作，一般只有與其他系統由資料互動的介面表采用TEXTFILE 格式，其他事實表和維度表都不建議使用，

• RCFile:
  Record Columnar的縮寫，是Hadoop中第一個列檔案格式， 能夠很好的壓縮和快速的查詢性能，通常寫操作比較慢，比非列形式的檔案格式需要更多的記憶體空間和計算量， RCFile是一種行列存盤相結合的存盤方式， 首先，其將資料按行分塊，保證同一個record在一個塊上，避免讀一個記錄需要讀取多個block，其次，塊資料列式存盤，有利于資料壓縮和快速的列存取，

• ORCFile:
  Hive從0.11版本開始提供了ORC的檔案格式，ORC檔案不僅僅是一種列式檔案存盤格式，最重要的是有著很高的壓縮比，并且對于MapReduce來說是可切分（Split）的，因此，在Hive中使用ORC作為表的檔案存盤格式，不僅可以很大程度的節省HDFS存盤資源，而且對資料的查詢和處理性能有著非常大的提升，因為ORC較其他檔案格式壓縮比高，查詢任務的輸入資料量減少，使用的Task也就減少了，ORC能很大程度的節省存盤和計算資源，但它在讀寫時候需要消耗額外的CPU資源來壓縮和解壓縮，當然這部分的CPU消耗是非常少的，

• Parquet:
  通常我們使用關系資料庫存盤結構化資料，而關系資料庫中使用資料模型都是扁平式的，遇到諸如List、Map和自定義Struct的時候就需要用戶在應用層決議，但是在大資料環境下，通常資料的來源是服務端的埋點資料，很可能需要把程式中的某些物件內容作為輸出的一部分，而每一個物件都可能是嵌套的，所以如果能夠原生的支持這種資料，這樣在查詢的時候就不需要額外的決議便能獲得想要的結果，Parquet的靈感來自于2010年Google發表的Dremel論文，文中介紹了一種支持嵌套結構的存盤格式，并且使用了列式存盤的方式提升查詢性能，Parquet僅僅是一種存盤格式，它是語言、平臺無關的，并且不需要和任何一種資料處理框架系結，這也是parquet相較于orc的僅有優勢：支持嵌套結構，Parquet 沒有太多其他可圈可點的地方,比如他不支持update操作(資料寫成后不可修改),不支持ACID等.

• SEQUENCEFILE:
  SequenceFile是Hadoop API 提供的一種二進制檔案，它將資料以<key,value>的形式序列化到檔案中， 這種二進制檔案內部使用Hadoop 的標準的Writable 介面實作序列化和反序列化，它與Hadoop API中的MapFile 是互相兼容的，Hive 中的SequenceFile 繼承自Hadoop API 的SequenceFile，不過它的key為空，使用value 存放實際的值， 這樣是為了避免MR 在運行map 階段的排序程序， SequenceFile支持三種壓縮選擇：NONE, RECORD, BLOCK， Record壓縮率低，一般建議使用BLOCK壓縮， SequenceFile最重要的優點就是Hadoop原生支持較好，有API，但除此之外平平無奇，實際生產中不會使用，

• AVRO:
  Avro是一種用于支持資料密集型的二進制檔案格式，它的檔案格式更為緊湊，若要讀取大量資料時，Avro能夠提供更好的序列化和反序列化性能，并且Avro資料檔案天生是帶Schema定義的，所以它不需要開發者在API 級別實作自己的Writable物件，Avro提供的機制使動態語言可以方便地處理Avro資料，最近多個Hadoop 子專案都支持Avro 資料格式，如Pig 、Hive、Flume、Sqoop和Hcatalog，

其中的TextFile、RCFile、ORC、Parquet為Hive最常用的四大存盤格式
它們的 存盤效率及執行速度比較如下：
ORCFile存盤檔案讀操作效率最高，耗時比較（ORC<Parquet<RCFile<TextFile）
ORCFile存盤檔案占用空間少，壓縮效率高(ORC<Parquet<RCFile<TextFile)

4、Hive操作客戶端

點我回傳目錄

常用的客戶端有兩個：CLI，JDBC/ODBC

• CLI，即Shell命令列

• JDBC/ODBC 是 Hive 的Java，與使用傳統資料庫JDBC的方式類似，

1. Hive 將元資料存盤在資料庫中(metastore)，目前只支持 mysql、derby， Hive 中的元資料包括表的名字，表的列和磁區及其屬性，表的屬性（是否為外部表等），表的資料所在目錄等；由解釋器、編譯器、優化器完成 HQL 查詢陳述句從詞法分析、語法分析、編譯、優化以及查詢計劃（plan）的生成，生成的查詢計劃存盤在 HDFS 中，并在隨后由 MapReduce 呼叫執行，

2. Hive 的資料存盤在 HDFS 中，大部分的查詢由 MapReduce 完成（包含 * 的查詢，比如 select * from table 不會生成 MapRedcue 任務）

Hive的metastore

metastore是hive元資料的集中存放地，
metastore默認使用內嵌的derby資料庫作為存盤引擎
Derby引擎的缺點：一次只能打開一個會話
使用MySQL作為外置存盤引擎，可以多用戶同時訪問`

元資料庫詳解見：查看mysql SDS表和TBLS表
連接地址：https://blog.csdn.net/haozhugogo/article/details/73274832

二、Hive的基本語法

1、Hive建表語法

點我回傳目錄

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] 
   // 指定Hive儲存格式：textFile、rcFile、SequenceFile 默認為：textFile
   [STORED AS file_format]
   
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定儲存位置
  [LOCATION hdfs_path]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL對hbase資料進行查詢，當然速度比較慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

建表格式1：全部使用默認建表方式

點我回傳目錄

create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 
// 必選，指定列分隔符 

建表格式2：指定location （這種方式也比較常用）

點我回傳目錄

create table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; 
// 指定Hive表的資料的存盤位置，一般在資料已經上傳到HDFS，想要直接使用，會指定Location，
//通常Locaion會跟外部表一起使用，內部表一般使用默認的location

建表格式3：指定存盤格式

點我回傳目錄

create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; 
// 指定儲存格式為rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，
//如果不指定，默認為textfile，
//注意：除textfile以外，其他的存盤格式的資料都不能直接加載，需要使用從表加載的方式，

建表格式4：create table xxxx as select_statement(SQL陳述句) (這種方式比較常用)

點我回傳目錄

注意：

1. 新建表不允許是外部表，
2. select后面表需要是已經存在的表，建表同時會加載資料，
3. 會啟動mapreduce任務去讀取源表資料寫入新表

create table students4 as select * from students2;

建表格式5：create table xxxx like table_name 只想建表，不需要加載資料

create table students5 like students;

2、Hive加載資料

點我回傳目錄

1)、使用hdfs dfs -put '本地資料' 'hive表對應的HDFS目錄下'

2)、使用 load data inpath

從hdfs匯入資料，路徑可以是目錄，會將目錄下所有檔案匯入，但是檔案格式必須一致

// 將HDFS上的/input1目錄下面的資料 移動至 students表對應的HDFS目錄下
// 注意是 移動！移動！移動！
load data inpath '/input1/students.txt' into table students;

// 清空表
truncate table students;

從本地檔案系統匯入

// 加上 local 關鍵字 可以將Linux本地目錄下的檔案 上傳到 hive表對應HDFS 目錄下 原檔案不會被洗掉
load data local inpath '/usr/local/soft/data/students.txt' into table students;
// overwrite 覆寫加載
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

3)、create table xxx as SQL陳述句，表對表加載

4)、insert into table xxxx SQL陳述句 （沒有as），表對表加載:

// 將 students表的資料插入到students2 
//這是復制 不是移動 students表中的表中的資料不會丟失
insert into table students2 select * from students;

// 覆寫插入 把into 換成 overwrite
insert overwrite table students2 select * from students;

注意：
1，如果建表陳述句沒有指定存盤路徑，不管是外部表還是內部表，存盤路徑都是會默認在hive/warehouse/xx.db/表名的目錄下，
加載的資料如果在HDFS上會移動到該表的存盤目錄下，注意是移動，不是復制
2，洗掉外部表，檔案不會洗掉，對應目錄也不會洗掉

3、Hive 內部表（Managed tables）vs 外部表（External tables）

點我回傳目錄

外部表和普通表的區別

1. 外部表的路徑可以自定義，內部表的路徑需要在 hive/warehouse/目錄下
2. 洗掉表后，普通表資料檔案和表資訊都洗掉，外部表僅洗掉表資訊

1)、建表陳述句：

// 內部表
create table students_internal
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';

// 外部表
create external table students_external
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';

2)、加載資料：

hive> dfs -put /usr/local/soft/data/students.txt /input2/;
hive> dfs -put /usr/local/soft/data/students.txt /input3/;

3)、洗掉表：

hive> drop table students_internal;
Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/Current
OK
Time taken: 0.474 seconds
hive> drop table students_external;
OK
Time taken: 0.09 seconds

1、可以看出，洗掉內部表的時候，表中的資料（HDFS上的檔案）會被同表的元資料一起洗掉；洗掉外部表的時候，只會洗掉表的元資料，而不會洗掉表中的資料（HDFS上的檔案）
2、一般在公司中，使用外部表多一點，因為資料可以需要被多個程式使用，避免誤刪，通常外部表會結合location一起使用
3、外部表還可以將其他資料源中的資料 映射到 hive中，比如說：hbase，ElasticSearch…
4、設計外部表的初衷就是 讓 表的元資料 與 資料 解耦

4、Hive 磁區

點我回傳目錄

磁區表實際上是在表的目錄下在以磁區命名，建子目錄；作用：進行磁區裁剪，避免全表掃描，減少MapReduce處理的資料量，提高效率
一般在公司的hive中，所有的表基本上都是磁區表，通常按日期磁區、地域磁區；磁區表在使用的時候記得加上磁區欄位；磁區也不是越多越好，一般不超過3級，根據實際業務衡量

磁區的概念和磁區表：
磁區表指的是在創建表時指定磁區空間，實際上就是在hdfs上表的目錄下再創建子目錄，
在使用資料時如果指定了需要訪問的磁區名稱，則只會讀取相應的磁區，避免全表掃描，提高查詢效率，

1)、建立磁區表：

create external table students_pt1
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(pt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

2)、增加一個磁區：

alter table students_pt1 add partition(pt='20210904');

3)、洗掉一個磁區：

alter table students_pt drop partition(pt='20210904');

4)、查看某個表的所有磁區

 // 推薦這種方式（直接從元資料中獲取磁區資訊）
show partitions students_pt;

// 不推薦
select distinct pt from students_pt; 

5)、往磁區中插入資料：

insert into table students_pt partition(pt='20210902') select * from students;

load data local inpath '/usr/local/soft/data/students.txt' into table students_pt partition(pt='20210902');

6)、查詢某個磁區的資料：

// 全表掃描，不推薦，效率低
select count(*) from students_pt;


// 使用where條件進行磁區裁剪，避免了全表掃描，效率高
select count(*) from students_pt where pt='20210101';


// 也可以在where條件中使用非等值判斷
select count(*) from students_pt where pt<='20210112' and pt>='20210110';

5、Hive動態磁區

點我回傳目錄

有的時候我們原始表中的資料里面包含了 ‘‘日期欄位 dt’’，我們需要根據dt中不同的日期，分為不同的磁區，將原始表改造成磁區表，

hive默認不開啟動態磁區

動態磁區：根據資料中某幾列的不同的取值 劃分 不同的磁區

# 表示開啟動態磁區
hive> set hive.exec.dynamic.partition=true;

# 表示動態磁區模式：strict（需要配合靜態磁區一起使用）、nostrict

# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nostrict;

# 表示支持的最大的磁區數量為1000，可以根據業務自己調整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

1)、建立原始表并加載資料

create table students_dt
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

2)、建立磁區表并加載資料

create table students_dt_p
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

3)、使用動態磁區插入資料

// 磁區欄位需要放在 select 的最后，如果有多個磁區欄位 同理，
//它是按位置匹配，不是按名字匹配
insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,dt from students_dt;

// 比如下面這條陳述句會使用age作為磁區欄位，而不會使用student_dt中的dt作為磁區欄位
insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;

4)、多級磁區

create table students_year_month
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    year string,
    month string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

create table students_year_month_pt
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
PARTITIONED BY(year string,month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

insert into table students_year_month_pt partition(year,month) select id,name,age,gender,clazz,year,month from students_year_month;

有關磁區好文分享：上單講磁區：https://developer.aliyun.com/article/81775

6、Hive分桶

點我回傳目錄

分桶實際上是對檔案（資料）的進一步切分；Hive默認關閉分桶；分桶的作用：在往分桶表中插入資料的時候，會根據 clustered by 指定的欄位 進行hash分組 對指定的buckets個數 進行取余，進而可以將資料分割成buckets個數個檔案，以達到資料均勻分布，可以解決Map端的“資料傾斜”問題，方便我們取抽樣資料，提高Map join效率；分桶欄位 需要根據業務進行設定

1)、開啟分桶開關

hive> set hive.enforce.bucketing=true;

2)、建立分桶表

create table students_buks
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
CLUSTERED BY (clazz) into 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 

3)、往分桶表中插入資料

// 直接使用load data 并不能將資料打散
load data local inpath '/usr/local/soft/data/students.txt' into table students_buks;

// 需要使用下面這種方式插入資料，才能使分桶表真正發揮作用
insert into students_buks select * from students;

Hive關于分桶好文分享， Hive分桶表的使用場景以及優缺點分析:https://zhuanlan.zhihu.com/p/93728864

7、Hive連接JDBC

點我回傳目錄

1)、啟動hiveserver2的服務

hive --service hiveserver2 &

2)、 新建maven專案并添加兩個依賴

    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>

3)、 撰寫JDBC代碼

import java.sql.*;

public class HiveJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/test3");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from students limit 10");
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String gender = rs.getString(4);
            String clazz = rs.getString(5);
            System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
        }
        rs.close();
        stat.close();
        conn.close();
    }
}

三、Hive的資料型別

1、基本資料型別

點我回傳目錄

數值型：

TINYINT — 微整型，只占用1個位元組，只能存盤0-255的整數，
SMALLINT– 小整型，占用2個位元組，存盤范圍–32768 到 32767，
INT– 整型，占用4個位元組，存盤范圍-2147483648到2147483647，
BIGINT– 長整型，占用8個位元組，存盤范圍-2^63到2^63-1，

布爾型

BOOLEAN — TRUE/FALSE

浮點型

FLOAT– 單精度浮點數，
DOUBLE– 雙精度浮點數，

字串型

STRING– 不設定長度，

2、日期型別

點我回傳目錄

• 時間戳 timestamp
• 日期 date

create table testDate(
    ts timestamp
    ,dt date
) row format delimited fields terminated by ',';

// 2021-01-14 14:24:57.200,2021-01-11

• 時間戳與時間字串轉換

// from_unixtime 傳入一個時間戳以及pattern（yyyy-MM-dd） 
//可以將 時間戳轉換成對應格式的字串
select from_unixtime(1630915221,'yyyy年MM月dd日 HH時mm分ss秒')

// unix_timestamp 傳入一個時間字串以及pattern，
//可以將字串按照pattern轉換成時間戳
select unix_timestamp('2021年09月07日 11時00分21秒','yyyy年MM月dd日 HH時mm分ss秒');
select unix_timestamp('2021-01-14 14:24:57.200')

3、復雜資料型別

點我回傳目錄

主要有三種復雜資料型別：Structs，Maps，Arrays ，可以參考：https://blog.csdn.net/woshixuye/article/details/53317009

四、Hive HQL使用語法

點我回傳目錄

我們知道SQL語言可以分為5大類：
(1）DDL(Data Definition Language) 資料定義語言
用來定義資料庫物件：資料庫，表，列等，
關鍵字：create，drap,alter等
( 2）DML(Data Manipulation Language) 資料操作語言
用來對資料庫中表的資料進行增刪改，
關鍵字：insert,delete,update等
( 3）DQL(Data Query Language)資料查詢語言
用來查詢資料庫表的記錄（資料），
關鍵字：select,where 等
( 4）DCL(Data Control Language) 資料控制語言
用來定義資料庫的訪問權限和安全級別，及創建用戶，
關鍵字：GRANT，REVOKE等
(5)TCL(Transaction Control Language) 事務控制語言
T CL經常被用于快速原型開發、腳本編程、GUI和測驗等方面，
關鍵字: commit、rollback等，

1、HQL語法-DDL

點我回傳目錄

創建資料庫 create database xxxxx;
查看資料庫 show databases；
洗掉資料庫 drop database tmp;
強制洗掉資料庫：drop database tmp cascade;
查看表：SHOW TABLES；
查看表的元資訊：
    desc test_table;
    describe extended test_table;
    describe formatted test_table;
查看建表陳述句：show create table table_XXX
重命名表：
    alter table test_table rename to new_table;
修改列資料型別：alter table lv_test change column colxx string;
增加、洗掉磁區：
    alter table test_table add partition (pt=xxxx) 
    alter table test_table drop if exists partition(...);

2、HQL語法-DML

點我回傳目錄

where  用于過濾，磁區裁剪，指定條件

join  用于兩表關聯，left outer join ，join，mapjoin（1.2版本后默認開啟）

group by  用于分組聚合，通常結合聚合函式一起使用

order by  用于全域排序，要盡量避免排序，是針對全域排序的，即對所有的reduce輸出是有序的

sort by  :當有多個reduce時，只能保證單個reduce輸出有序，不能保證全域有序

cluster by = distribute by + sort by

distinct 去重

order by、distribute by、sort by、cluster by詳解
文章鏈接：?Hive中order、sort、distribute、cluster by區別與聯系 https://zhuanlan.zhihu.com/p/93747613

五、Hive HQL使用注意

點我回傳目錄

1. count(*)、count(1) 、count(‘欄位名’) 的區別

2. HQL 執行優先級：
   from、where、 group by 、having、order by、join、select 、limit

3. where 條件里不支持不等式子查詢，實際上是支持 in、not in、exists、not exists

4. hive中大小寫不敏感

5. 在hive中，資料中如果有null字串，加載到表中的時候會變成 null （不是字串）
   如果需要判斷 null，使用 某個欄位名 is null 這樣的方式來判斷;或者使用 nvl() 函式，不能 直接 某個欄位名 == null

6. 使用explain查看SQL執行計劃

六、Hive 的函式使用

點我回傳目錄

1、Hive-常用函式

點我回傳目錄

（1）關系運算

點我回傳目錄

// 等值比較 = == <=>
// 不等值比較 != <>
// 區間比較： select * from default.students where id between 1500100001 and 1500100010;
// 空值/非空值判斷：is null、is not null、nvl()、isnull()
// like、rlike、regexp用法

Hive中rlike,like,not like，regexp區別與使用詳解

（2）數值計算

點我回傳目錄

取整函式(四舍五入)：round
向上取整：ceil
向下取整：floor

（3） 條件函式

點我回傳目錄

• if： if(運算式,如果運算式成立的回傳值,如果運算式不成立的回傳值)

select if(1>0,1,0); 
select if(1>0,if(-1>0,-1,1),0);

• COALESCE

select COALESCE(null,'1','2'); // 1 從左往右 一次匹配 直到非空為止
select COALESCE('1',null,'2'); // 1

• case when … then … else … end

select  score
        ,case when score>120 then '優秀'
              when score>100 then '良好'
              when score>90 then '及格'
        else '不及格'
        end as pingfen
from default.score limit 20;

#  注意條件的順序

（4）日期函式

點我回傳目錄

select from_unixtime(1610611142,'YYYY/MM/dd HH:mm:ss');

select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');

// '2021年01月14日' -> '2021-01-14'
select from_unixtime(unix_timestamp('2021年01月14日','yyyy年MM月dd日'),'yyyy-MM-dd');
// "04牛2021數加16逼" -> "2021/04/16"
select from_unixtime(unix_timestamp("04牛2021數加16逼","MM牛yyyy數加dd逼"),"yyyy/MM/dd");

（5) 字串函式

點我回傳目錄

concat('123','456'); // 123456
concat('123','456',null); // NULL

select concat_ws('#','a','b','c'); // a#b#c
select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且會自動忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;

select substring("abcdefg",1); // abcdefg HQL中涉及到位置的時候 是從1開始計數
// '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));

select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c

select explode(split("abcde,fgh",",")); // abcde
										//  fgh

// 決議json格式的資料
select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); // 100

2、Hive-高級函式

點我回傳目錄

（1）視窗函式（開窗函式）：用戶分組中開窗

點我回傳目錄

在sql中有一類函式叫做聚合函式,例如sum()、avg()、max()等等,這類函式可以將多行資料按照規則聚集為一行,一般來講聚集后的行數是要少于聚集前的行數的.但是有時我們想要既顯示聚集前的資料,又要顯示聚集后的資料,這時我們便引入了視窗函式，（開創函式，我們一般用于分組中求 TopN問題）

好文分享，Hive視窗函式

樣例演示：

資料：
111,69,class1,department1
112,80,class1,department1
113,74,class1,department1
114,94,class1,department1
115,93,class1,department1
121,74,class2,department1
122,86,class2,department1
123,78,class2,department1
124,70,class2,department1
211,93,class1,department2
212,83,class1,department2
213,94,class1,department2
214,94,class1,department2
215,82,class1,department2
216,74,class1,department2
221,99,class2,department2
222,78,class2,department2
223,74,class2,department2
224,80,class2,department2
225,85,class2,department2

建表：
create table new_score(
    id  int
    ,score int
    ,clazz string
    ,department string
) row format delimited fields terminated by ",";

row_number()：無并列排名

使用格式：
 select xxxx, row_number() over(partition by 分組欄位 order by 排序欄位 desc) as rn from tb group by xxxx

dense_rank()：有并列排名，并且依次遞增

rank()：有并列排名，不依次遞增

percent_rank()：(rank的結果-1)/(磁區內資料的個數-1)

cume_dist()：計算某個視窗或磁區中某個值的累積分布，

假定升序排序，則使用以下公式確定累積分布： 小于等于當前值x的行數 / 視窗或partition磁區內的總行數，其中，x 等于 order by 子句中指定的列的當前行中的值，

NTILE(n)：對磁區內資料再分成n組，然后打上組號

max()、min()、avg()、count()、sum()等函式：是基于每個partition磁區內的資料做對應的計算

視窗幀：用于從磁區中選擇指定的多條記錄，供視窗函式處理

點我回傳目錄

Hive 提供了兩種定義視窗幀的形式：ROWS 和 RANGE，兩種型別都需要配置上界和下界，
例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示選擇磁區起始記錄到當前記錄的所有行；
SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 則通過 欄位差值 來進行選擇，
如當前行的 close 欄位值是 200，那么這個視窗幀的定義就會選擇磁區中 close 欄位值落在 100 至 400 區間的記錄，
以下是所有可能的視窗幀定義組合，如果沒有定義視窗幀，則默認為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，
注意：視窗幀只能運用在max、min、avg、count、sum、FIRST_VALUE、LAST_VALUE這幾個視窗函式上

測驗1：

SELECT id
     ,score
     ,clazz
     ,SUM(score) OVER w as sum_w
     ,round(avg(score) OVER w,3) as avg_w
     ,count(score) OVER w as cnt_w
FROM new_score
WINDOW w AS (PARTITION BY clazz ORDER BY score rows between 2 PRECEDING and 2 FOLLOWING);

測驗2：

select  id
        ,score
        ,clazz
        ,department
        ,row_number() over (partition by clazz order by score desc) as rn_rk
        ,dense_rank() over (partition by clazz order by score desc) as dense_rk
        ,rank() over (partition by clazz order by score desc) as rk
        ,percent_rank() over (partition by clazz order by score desc) as percent_rk
        ,round(cume_dist() over (partition by clazz order by score desc),3) as cume_rk
        ,NTILE(3) over (partition by clazz order by score desc) as ntile_num
        ,max(score) over (partition by clazz order by score desc range between 3 PRECEDING and 11 FOLLOWING) as max_p
from new_score;

LAG(col,n)：往前第n行資料

LEAD(col,n)：往后第n行資料

FIRST_VALUE：取分組內排序后，截止到當前行，第一個值

LAST_VALUE：取分組內排序后，截止到當前行，最后一個值，對于并列的排名，取最后一個

測驗3：

select  id
        ,score
        ,clazz
        ,department
        ,lag(id,2) over (partition by clazz order by score desc) as lag_num
        ,LEAD(id,2) over (partition by clazz order by score desc) as lead_num
        ,FIRST_VALUE(id) over (partition by clazz order by score desc) as first_v_num
        ,LAST_VALUE(id) over (partition by clazz order by score desc) as last_v_num
        ,NTILE(3) over (partition by clazz order by score desc) as ntile_num
from new_score;

（2）Hive 行轉列

點我回傳目錄

使用關鍵字： lateral view explode

樣例演示：

建表：
create table testArray2(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

樣例資料：
孫悟空	"150","170","180"
唐三藏	"150","180","190"

select name,col1  from testarray2 lateral view explode(weight) t1 as col1;

select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;

select name,col1,col2  from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;

select name,pos,col1  from testarray2 lateral view posexplode(weight) t1 as pos,col1;

（3）Hive 列轉行

點我回傳目錄

資料：

孫悟空  150
孫悟空  170
孫悟空  180
唐三藏  150
唐三藏  180
唐三藏  190

建表：
create table testLieToLine(
    name string,
    col1 int
)row format delimited 
fields terminated by '\t';

測驗1：

select name,collect_list(col1) from testLieToLine group by name;

測驗2：

select  t1.name
        ,collect_list(t1.col1) 
from (
    select  name
            ,col1 
    from testarray2 
    lateral view explode(weight) t1 as col1
) t1 group by t1.name;

（4）Hive自定義函式UserDefineFunction

點我回傳目錄

? UDF：一進一出

點我回傳目錄

• 創建maven專案，并加入依賴

    <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>1.2.1</version>
        </dependency>

• 撰寫代碼，繼承org.apache.hadoop.hive.ql.exec.UDF，實作evaluate方法，在evaluate方法中實作自己的邏輯

import org.apache.hadoop.hive.ql.exec.UDF;

public class HiveUDF extends UDF {
    // hadoop => #hadoop$
    public String evaluate(String col1) {
    // 給傳進來的資料 左邊加上 # 號 右邊加上 $
        String result = "#" + col1 + "$";
        return result;
    }
}

• 打成jar包并上傳至Linux虛擬機(小北路徑：/usr/local/soft/jars/)
• 在hive shell中，使用 add jar 路徑將jar包作為資源添加到hive環境中

add jar /usr/local/soft/jars/HiveUDF2-1.0.jar;

• 使用jar包資源注冊一個臨時函式，fxxx1是你的函式名，'MyUDF’是主類名

create temporary function fxxx1 as 'MyUDF';

• 使用函式名處理資料

select fxx1(name) as fxx_name from students limit 10;

#施笑槐$
#呂金鵬$
#單樂蕊$
#葛德曜$
#宣谷芹$
#邊昂雄$
#尚孤風$
#符半雙$
#沈德昌$
#羿彥昌$

?UDTF：一進多出

點我回傳目錄

樣例資料：

"key1:value1,key2:value2,key3:value3"

key1 value1

key2 value2

key3 value3

方法一：使用 explode+split

select split(t.col1,":")[0],split(t.col1,":")[1] 
from (select 
explode(split("key1:value1,key2:value2,key3:value3",",")) as 
col1) t;

方法二：自定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;

public class HiveUDTF extends GenericUDTF {
    // 指定輸出的列名 及 型別
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        ArrayList<String> filedNames = new ArrayList<String>();
        ArrayList<ObjectInspector> filedObj = new ArrayList<ObjectInspector>();
        filedNames.add("col1");
        filedObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        filedNames.add("col2");
        filedObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(filedNames, filedObj);
    }

    // 處理邏輯 my_udtf(col1,col2,col3)
    // "key1:value1,key2:value2,key3:value3"
    // my_udtf("key1:value1,key2:value2,key3:value3")
    public void process(Object[] objects) throws HiveException {
        // objects 表示傳入的N列
        String col = objects[0].toString();
        // key1:value1  key2:value2  key3:value3
        String[] splits = col.split(",");
        for (String str : splits) {
            String[] cols = str.split(":");
            // 將資料輸出
            forward(cols);
        }

    }

    // 在UDTF結束時呼叫
    public void close() throws HiveException {

    }
}

SQL:

select my_udtf("key1:value1,key2:value2,key3:value3");

舉例說明：

欄位：id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12
共13列

資料：

a,1,2,3,4,5,6,7,8,9,10,11,12

b,11,12,13,14,15,16,17,18,19,20,21,22

c,21,22,23,24,25,26,27,28,29,30,31,32

轉成3列：id,hours,value

例如：

a,1,2,3,4,5,6,7,8,9,10,11,12

a,0時,1

a,2時,2

a,4時,3

a,6時,4

…

建表：

create table udtfData(
    id string
    ,col1 string
    ,col2 string
    ,col3 string
    ,col4 string
    ,col5 string
    ,col6 string
    ,col7 string
    ,col8 string
    ,col9 string
    ,col10 string
    ,col11 string
    ,col12 string
)row format delimited fields terminated by ',';

java代碼：

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;

public class HiveUDTF2 extends GenericUDTF {
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        ArrayList<String> filedNames = new ArrayList<String>();
        ArrayList<ObjectInspector> fieldObj = new ArrayList<ObjectInspector>();
        filedNames.add("col1");
        fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        filedNames.add("col2");
        fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(filedNames, fieldObj);
    }

    public void process(Object[] objects) throws HiveException {
        int hours = 0;
        for (Object obj : objects) {
            hours = hours + 1;
            String col = obj.toString();
            ArrayList<String> cols = new ArrayList<String>();
            cols.add(hours + "時");
            cols.add(col);
            forward(cols);
        }
    }

    public void close() throws HiveException {

    }
}

添加jar資源:

add jar /usr/local/soft/HiveUDF2-1.0.jar;

注冊udtf函式：

create temporary function my_udtf as 'MyUDTF';

SQL:

select id
       ,hours
       ,value from udtfData lateral view   
my_udtf(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12)
t as hours,value ;

?UDAF：多進一出

點我回傳目錄

好文分享： hive自定義函式學習

3、Hive 中的wordCount

點我回傳目錄

建表：
create table words(
    words string
)row format delimited fields terminated by '|';

資料：
hello,java,hello,java,scala,python
hbase,hadoop,hadoop,hdfs,hive,hive
hbase,hadoop,hadoop,hdfs,hive,hive

select word,count(*) from (select explode(split(words,',')) word from words) a group by a.word;

七、Hive 的Shell使用

點我回傳目錄

第一種shell

hive -e "select * from test03.students limit 10"

第二種shell

hive -f hql檔案路徑

# 將HQL寫在一個檔案里，再使用 -f 引數指定該檔案