關於hive核心

愛吃大抄手的黃大超發表於2021-07-24
Hive

一、DDL資料定義

1、建立資料庫

1）建立一個資料庫，資料庫在 HDFS 上的預設儲存路徑是/user/hive/warehouse/*.db。

hive (default)> create database db_hive;

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;

3）建立一個資料庫，指定資料庫在 HDFS 上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

2、查詢資料庫

顯示資料庫

1．顯示資料庫

hive> show databases;

2．過濾顯示查詢的資料庫

hive> show databases like 'db_hive*'; 

OK 
db_hive 
db_hive_1
檢視資料庫詳情

1．顯示資料庫資訊

hive> desc database db_hive; 
OK 
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER

2．顯示資料庫詳細資訊，extended

hive> desc database extended db_hive; 
OK
db_hive		hdfs://master:8020/usr/hive/warehouse/db_hive.db	root	USER
切換當前資料庫
hive (default)> use db_hive;

3、修改資料庫

使用者可以使用 ALTER DATABASE 命令為某個資料庫的 DBPROPERTIES 設定鍵-值對屬性值，來描述這個資料庫的屬性資訊。

資料庫的其他後設資料資訊都是不可更改的，包括資料庫名和資料庫所在的目錄位置。

hive (default)>alter hive set database dbproperties('createtime'='20200830');

在 hive 中檢視修改結果

hive> desc database extended db_hive;

db_name comment location owner_name owner_type parameters 
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db chaosUSER {createtime=20200830}

4、刪除資料庫

1．刪除空資料庫

hive>drop database db_hive2;

2．如果刪除的資料庫不存在，最好採用 if exists 判斷資料庫是否存在

hive> drop database db_hive;

FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

hive> drop database if exists db_hive2;

3．如果資料庫不為空，可以採用 cascade 命令，強制刪除

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> drop database db_hive cascade;

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]
2）欄位解釋
•CREATE TABLE 建立一個指定名字的表。如果相同名字的表已經存在，則丟擲異常；使用者可以用 IF NOT EXIST 選項來忽略這個異常
•EXTERNAL 關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION）,Hive 建立內部表時，會將資料移動到hadfs指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在刪除表的時候，內部表的後設資料和資料會被一起刪除，而外部表只刪除後設資料，不刪除資料。
•LIKE 允許使用者複製現有的表結構，但是不復制資料
•COMMENT可以為表與欄位增加註釋描述
•PARTITIONED BY  建立分割槽表,指定分割槽
•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 確定表的具體的列的資料。 
•STORED AS 
　　SEQUENCEFILE //序列化檔案
　　| TEXTFILE //普通的文字檔案格式
　　| RCFILE　　//行列儲存相結合的檔案
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定義檔案格式
　　如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用 STORED AS SEQUENCE 。
•LOCATION指定表在HDFS的儲存路徑

6、修改表

重新命名錶
ALTER TABLE table_name RENAME TO new_table_name
增加/修改/替換列資訊

更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替換列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注：ADD 是代表新增一欄位，欄位位置在所有列後面(partition 列前)，REPLACE 則是表示替換表中所有欄位。

簡單示例

（1）查詢表結構

hive> desc dept_partition;

（2）新增列

hive (default)> alter table dept_partition add columns(deptdesc string);

（3）更新列

hive (default)> alter table dept_partition change column deptdesc desc int;

（4）替換列

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

注：hive不支援刪除欄位

7、刪除表

注意：內部表和外部表刪除的區別

 drop table dept_name;

8、例項

1）關於內部表與外部表

建表語句

建立部門表

create external table if not exists default.dept( deptno int, dname string, loc int )

row format delimited fields terminated by '\t';

建立員工表

create external table if not exists default.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int)
row format delimited fields terminated by '\t';

檢視建立的表

hive (default)> show tables; 
OK 
tab_name 
dept 
emp

向外部表中匯入資料匯入資料

hive (default)>load data inpath local '/opt/module/data/dept.txt' into table default.dept;
hive (default)>load data local inpath'/opt/module/data/emp.txt' into table default.emp;

查詢結果

hive (default)> select * from emp;
hive (default)> select * from dept;

檢視錶格式化資料

hive (default)> desc formatted dept;

Table Type: EXTERNAL_TABLE
管理表與外部表的互相轉換

（1）查詢表的型別

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

（2）修改內部表 student2 為外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

（3）查詢表的型別

hive (default)> desc formatted student2;

 Table Type: EXTERNAL_TABLE

（4）修改外部表 student2 為內部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

（5）查詢表的型別

hive (default)> desc formatted student2;

 Table Type: MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')為固定寫法，區分大小寫！

2）關於基礎分割槽表

1．引入分割槽表（需要根據日期對日誌進行管理）

/user/hive/warehouse/log_partition/20200702/20200702.log

/user/hive/warehouse/log_partition/20200703/20200703.log

/user/hive/warehouse/log_partition/20200704/20200704.log

2．建立分割槽表語法

create table dept_partition( deptno int, dname string, loc string) partitioned by (month string)
row format delimited fields terminated by '\t';

3．載入資料到分割槽表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202009');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202008');

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202007’);

4．查詢分割槽表中資料

單分割槽查詢

hive (default)> select * from dept_partition where month='202009';

多分割槽聯合查詢

hive (default)> select * from dept_partition where month='202009' union all select * from dept_partition where month='202008' union select * from dept_partition where month='202007';、
簡單寫法
hive (default)> select * from dept_partition where month>='202007'and month<='202009'

5．增加分割槽

建立單個分割槽

hive (default)> alter table dept_partition add partition(month='202006') ;

同時建立多個分割槽

hive (default)> alter table dept_partition add partition(month='202005') partition(month='202004');

6．刪除分割槽

刪除單個分割槽

hive (default)> alter table dept_partition drop partition (month='202004');

同時刪除多個分割槽

hive (default)> alter table dept_partition drop partition (month='202005'), partition (month='202006');
簡單寫法
hive (default)> alter table dept_partition drop partition (month>='202005',month<='202006');

7．檢視分割槽表有多少分割槽

hive> show partitions dept_partition;

8．檢視分割槽表結構

hive> desc formatted dept_partition;
3）關於多級分割槽表

hive中的多級分割槽表，可以理解為多級目錄，按照分割槽欄位順序將原本資料劃分為多級目錄

1．建立二級分割槽表

hive (default)> create table dept_partition2(deptno int, dname string, loc string)
partitioned by (month string, day string) 
row format delimited fields terminated by '\t';

2．正常的載入資料

（1）載入資料到二級分割槽表中

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='202009', day='13');

（2）查詢分割槽資料

hive (default)> select * from dept_partition2 where month='202009' and day='13';

3．把資料直接上傳到分割槽目錄上，讓分割槽表和資料產生關聯的三種方式

（1）方式一：上傳資料後修復

​ 上傳資料

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=12; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=12;

查詢資料（查詢不到剛上傳的資料）

hive (default)> select * from dept_partition2 where month='202009' and day='12';

執行修復命令

hive> msck repair table dept_partition2;

再次查詢資料

hive (default)> select * from dept_partition2 where month='202009' and day='12';

（2）方式二：上傳資料後新增分割槽

​ 上傳資料

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=11; 

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=202009/day=11;

執行新增分割槽

 hive (default)> alter table dept_partition2 add partition(month='202009',day='11');

查詢資料

hive (default)> select * from dept_partition2 where month='202009' and day='11';

（3）方式三：建立資料夾後 load 資料到分割槽

建立目錄

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202009/day=10;

上傳資料

hive (default)>load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='202009',day='10');

查詢資料

hive (default)> select * from dept_partition2 where month='202009' and day='10';
4）關於動態分割槽表

前面的分割槽建立方式為靜態分割槽，如果需要建立非常多的分割槽，或者根據指定欄位值分割槽，就需要使用動態分割槽，hive的動態分割槽可以基於查詢引數推斷出需要建立的分割槽名稱。

eg：

insert overwrite table employees partition(country,state)

select ...,se.cnty,se.st

from staged_employees se;

如上面例子，hive可以根據select語句中最後2列來確定分割槽欄位country和state的值。這也是為什麼在上面例子使用了不同的命名，就是為了強調源表欄位值和輸出分割槽值之間的關係是根據位置而不是根據命名來匹配的。

關於混合使用動態和靜態分割槽。

eg：

insert overwrite table employees partition(country='China',state)

select ...,se.cnty,se.st

from staged_employees se

where se.cnty='China';

如上面例子，指定了country欄位的值為靜態的中國，而分割槽欄位state是動態值

注意：靜態分割槽必須出現在動態分割槽鍵之前。

動態分割槽功能預設是關閉的，開啟後，也會預設是嚴格模式執行，在這種模式下要求至少有一列分割槽欄位是靜態的，這有助於因設計錯誤導致查詢產生大量的分割槽。

動態分割槽屬性

屬性名稱 預設值（預設值） 描述
hive.exec.dynamic.partition false 設定為true，表示開啟動態分割槽功能
hive.exec.dynamic.parititon.mode strict 設定為nonstrict，表示允許所有分割槽都是動態的
hive.exec.dynamic.partitions.pernode 100 每個mapper或reducer可以建立的最大動態分割槽個數，如果某個mapper或reducer嘗試建立超過這個值的分割槽的話，就會報錯
hive.exec.dynamic.parititons 1000 一個動態分割槽建立語句可以建立的最大動態分割槽個數。如果超過也會報錯
hive.exec.max.created.files 100000 全域性可以建立的最大檔案個數。有一個Hadoop計數器，會跟蹤記錄建立了多少個檔案，如果超過也會報錯。
5）關於傾斜表

通過指定一個或者多個列經常出現的值（嚴重偏斜），Hive 會自動將涉及到這些值的資料拆分為單獨的檔案。在查詢時，如果涉及到傾斜值，它就直接從獨立檔案中獲取資料，而不是掃描所有檔案，這使得效能得到提升。

create [exertnal] table 傾斜表名(欄位名 型別,欄位名 型別) skewed by (傾斜欄位) ON (對應常見傾斜值,對應常見傾斜值)

row format delimited fields terminated by 欄位分隔符;

9、檢視

當查詢變得長或複雜的時候，通過使用檢視將這個查詢語句分割成多個小的、更可控的片段可以降低這種複雜度。這點和在程式語言中使用函式或者軟體設計中的分層設計的概念是一致的。封裝複雜的部分可以是終端使用者通過重用重複的部分來構建複雜的查詢。

對於檢視來說一個常見的使用場景就是基於一個或多個列的值來限制輸出結果。有些資料庫允許將檢視作為一個安全機制，也就是不給使用者直接訪問具有敏感資料的原始表，而是提供給使用者一個通過WHERE子句限制了的檢視，以供訪問。Hive 目前並不支援這個功能，因為使用者必須具有能夠訪問整個底層原始表的許可權，這時檢視才能工作。然而，通過建立檢視來限制資料訪問可以用來保護資訊不被隨意查詢。

Hive 會先解析檢視，然後使用解析結果再來解析整個查詢語句。然而，作為Hive查詢優化器的一部分，查詢語句和檢視語句可能會合併成-一個單-一的實際查詢語句。這個概念檢視仍然適用於檢視和使用這個檢視的查詢語句都包含了一個ORDER BY子句或-一個LIMIT子句的情況。這時會在使用這個檢視的查詢語句之前對該檢視進行解析。例如，如果檢視語句含有一個LIMIT 100 子句，而同時使用到這個檢視的查詢含有一個LIMIT 200子句，那麼使用者最終最多隻能獲取100條結果記錄。因為定義一個檢視實際上並不會“具體化”操作任何實際資料，所以檢視實際上是對其所使用到的表和列的一個查詢語句固化過程。因此，如果檢視所涉及的表或者列不再存在時，會導致檢視查詢失敗。

一個檢視的名稱要和這個檢視所在的資料庫下的其他所有表和檢視的名稱不同。使用者還可以為所有的新列或部分新列增加一個COMMNET子句，進行寫註釋。這些註釋並非“ 繼承”原始表中的定義。同樣地，如果AS SELECT子句中包含沒有命名別名的表示式的話，例如size(cols)(計算cols中元素的個數)，那麼Hive將會使用_ _CN 作為新的列名，其中N表示從0開始的一個整數。如果AS SELECT語句不合法的話，那麼建立檢視過程將失敗。

eg:

CREATE VIEW IF NOT EXISTS shipments (time, part)

COMMENT ' Time and parts for shipments. '

TBLPROPERTIES ( 'creator' = 'me' )

AS SELECT ...;

在AS SELECT子句之前，使用者可以通過定義TBLPROPERTIES來定義表屬性資訊，這點和表相同。上例中，我們定義的屬性為“creator”, 表示這個檢視的建立者名稱。

CREATE [EXTERNAL] TABLE ... LIKE ..結構同樣適用於複製檢視，只需要在LIKE表示式裡面寫檢視名就可以了:

eg:

CREATE TABLE shipments2 LIKE shipments;

檢視不能夠作為INSERT語句或LOAD命令的目標表。檢視是隻讀的。對於檢視只允許改變後設資料中TBLPROPERTIES(表屬性)屬性資訊:

ALTER VIEW shipments SET TBLPROPERTIES ('created_ at' = ' some_ timestamp') ;

1）Hive 的檢視和關係型資料庫的檢視區別

和關係型資料庫一樣，Hive 也提供了檢視的功能，不過請注意，Hive 的檢視和關係型資料庫的資料還是有很大的區別：

　　（1）只有邏輯檢視，沒有物化檢視；

　　（2）檢視只能查詢，不能做載入資料操作，如：Load/Insert/Update/Delete 資料；

　　（3）檢視在建立時候，只是儲存了一份後設資料，當查詢檢視的時候，才開始執行檢視對應的那些子查詢

　　（4）view定義中若包含了ORDER BY/LIMIT語句，當查詢檢視時也進行ORDER BY/LIMIT語句操作，view當中定義的優先順序更高

​ • view: order by age asc;

​ • select order by age desc;

​ • select * from view order by age desc;

　　（5）view支援迭代檢視

​ • view1: select * from tb_user1;

​ • view2: select * from view1;

​ • view3: select * from view2;

2）Hive檢視的建立語句
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;
3）Hive檢視的檢視語句
show views;
desc view_test;-- 檢視某個具體檢視的資訊
4）Hive檢視的使用語句
select colums from view_test;
select * from view_test;
5）Hive檢視的刪除語句
DROP VIEW [IF EXISTS] [db_name.]view_name;
drop view view_test;

10、索引

Hive只有有限的索引功能。Hive中沒有普通關係型資料庫中鍵的概念，但是還是可以對一些欄位建立索引來加速某些操作的。一張表的索引資料儲存在另外一張表中。

索引處理模組被設計成為可以定製的Java編碼的外掛，因此，使用者可以根據需要對其進行實現，以滿足自身的需求。

當邏輯分割槽實際上太多太細而幾乎無法使用時，建立索引也就成為分割槽的另-一個選擇。建立索引可以幫助裁剪掉一張表的一些資料塊，這樣能夠減少MapReduce的輸人資料量。並非所有的查詢都可以通過建立索引獲得好處。通過EXPLAIN命令可以檢視某個查詢語句是否用到了索引。

Hive中的索引和那些關係型資料庫中的一樣， 需要進行仔細評估才能使用。維護索引也需要額外的儲存空間，同時建立索引也需要消耗計算資源。使用者需要在建立索引為查詢帶來的好處和因此而需要付出的代價之間做出權衡。

1）建立索引
create index t1_index on table tb_user(name) 

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 

in table t1_index_table;

as：指定索引器；

in table：指定索引表，若不指定預設生成在default_tb_user_t1_index表中

create index t2_index on table tb_user(name)

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

with deferred rebuild表示在執行alter index xxx_index on xxx rebuild時將呼叫generateIndexBuildTaskList獲取Index的MapReduce，並執行為索引填充資料。

2）顯示索引
show [formatted] index on tb_name;

關鍵字FORMATTED是可選的。增加這個關鍵字可以使輸出中包含有列名稱。使用者還可以替換INDEX為INDEXES,這樣輸出中就可以列舉出多個索引資訊了。

3）重建索引

建立新索引之後必須重建索引才能生效

ALTER INDEX t2_index ON tb_user REBUILD;
ALTER INDEX employees_index ON TABLE employees
PARTITION (country = 'China')
REBUILD;

如果省略掉PARTITION,那麼將會對所有分割槽進行重建索引。

還沒有一個內建的機制能夠在底層的表或者某個特定的分割槽發生改變時，自動觸發重建索引。但是，如果使用者具有一個工作流來更新表分割槽中的資料的話，那麼使用者可能已經在其中某處使用到了ALTER TABLE .. TOUCH PARTITION(..)功能，同樣地，在這個工作流中也可以對對應的索引執行重建索引語句ALTER INDEX ... REBUILD。

如果重建索引失敗，那麼在重建開始之前，索引將停留在之前的版本狀態。從這種意義上看，重建索引操作是原子性的。

4）刪除索引

如果有索引表的話，刪除一個索引將會刪除這個索引表:

DROP INDEX IF EXISTS t1_index ON tb_user;
DROP INDEX IF EXISTS employees_index ON TABLE employees;

Hive不允許使用者直接使用DROP TABLE語句之前刪除索引表。而通常情況下，IF EXISTS都是可選的，其用於當索引不存在時避免丟擲錯誤資訊。如果被索引的表被刪除了，那麼其對應的索引和索引表也會被刪除。同樣地，如果原始表的某個分割槽被刪除了，那麼這個分割槽對應的分割槽索引也同時會被刪除掉。

二、DML資料操作

1、資料匯入

1） 向表中裝載資料（Load）
load data [local] inpath '/opt/module/datas/table_name.txt' [overwrite] | into table table_name
[partition (partcol1=val1,…)];

（1）load data:表示載入資料

（2）local:表示從本地載入資料到 hive 表；否則從 HDFS 載入資料到 hive 表

（3）inpath:表示載入資料的路徑

（4）overwrite:表示覆蓋表中已有資料，否則表示追加

（5）into table:表示載入到哪張表

（6）student:表示具體的表

（7）partition:表示上傳到指定分割槽

2）Load示例

（1）建立一張表

hive (default)> create table student(id string, name string) 
row format delimited fields terminated by '\t';

（2）載入本地檔案到 hive

hive (default)>load data local inpath '/opt/module/datas/student.txt' 
into table default.student;

（3）載入 HDFS 檔案到 hive 中

上傳檔案到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

載入 HDFS 上資料

hive (default)>load data inpath '/user/chaos/hive/student.txt' into table default.student;

（4）載入資料覆蓋表中已有的資料

上傳檔案到 HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/chaos/hive;

載入資料覆蓋表中已有的資料

hive (default)>load data inpath '/user/chaos/hive/student.txt' overwrite into table default.student;
3） 通過查詢語句向表中插入資料（Insert）

1．建立一張分割槽表

create table student(id int, name string) partitioned by (month string) 
row format delimited fields terminated by '\t';

2．基本插入資料

hive (default)>insert into table student partition(month='202009') values(1,'wangwu');

3．基本模式插入（根據單張表查詢結果）

hive (default)> insert overwrite table student partition(month='202008') 
select id, name from student where month='202009';

4．多插入模式（根據多張表查詢結果）

from(select * from student
)t 
insert overwrite table student partition(month='202007') 
select id,name where month='202009' 
insert overwrite table student partition(month='202006')
select id, name where month='202009';
4） 查詢語句中建立表並載入資料（As Select）

根據查詢結果建立表（查詢的結果會新增到新建立的表中）

create table if not exists student3 as select id, name from student;
5）建立表時通過 Location 指定載入資料路徑

1．建立表，並指定在 hdfs 上的位置

hive (default)> create table if not exists student5(id int, name string)
row format delimited fields terminated by '\t' 
location '/user/hive/warehouse/student5';

2．上傳資料到 hdfs 上

hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

3．查詢資料

hive (default)> select * from student5;
6） Import 資料到指定 Hive 表中

注意：先用 export 匯出後，再將資料匯入。

hive (default)> import table student2 partition(month='202009') from '/user/hive/warehouse/export/student';

2、 資料匯出

1）Insert 匯出

（1）將查詢的結果匯出到本地

hive (default)> insert overwrite local directory '/opt/module/datas/export/student' 
select * from student;

（2）將查詢的結果格式化匯出到本地

hive(default)>insert overwrite local directory '/opt/module/datas/export/student1' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

（3）將查詢的結果匯出到 HDFS 上(與匯出到本地的區別是沒有 local)

hive (default)>insert overwrite directory '/user/chaos/student2' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;
2 ）Hadoop命令匯出到本地
hive (default)>dfs -get /user/hive/warehouse/student/month=202009/000000_0 /opt/module/datas/export/student3.txt;
3） Hive Shell 命令匯出基本語法：

（hive -f/-e 執行語句或者指令碼 > file）

[chaos@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
4）Export 匯出到 HDFS 上
hive(default)> export table default.student to '/user/hive/warehouse/export/student';

3、 清除表中資料（Truncate）

注意：Truncate 只能刪除管理表，不能刪除外部表中資料

hive (default)> truncate table student;

三、查詢

查詢語句語法：

[WITH CommonTableExpression (, CommonTableExpression)*](Note: Only available starting with Hive 0.13.0)
  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]

1、 基本查詢（Select…From）

1） 全表和特定列查詢

全表查詢

hive (default)> select * from emp;

選擇特定列查詢

hive (default)> select empno, ename from emp;

注意：

（1）SQL 語言大小寫不敏感。

（2）SQL 可以寫在一行或者多行

（3）關鍵字不能被縮寫也不能分行

（4）各子句一般要分行寫。

（5）使用縮排提高語句的可讀性。

2） 列別名

1．重新命名一個列

2．便於計算

3．緊跟列名，也可以在列名和別名之間加入關鍵字‘AS’

4．中文別名需要使用反引號 `

5．案例實操查詢名稱和部門，建立時間

select 
	ename AS name,
	deptno dn,
	fcd as `建立時間`
from emp;
3） 算術運算子
運算子 描述
A+B A 和 B 相加
A-B A 減去 B
A*B A 和 B 相乘
A/B A 除以 B
A%B A 對 B 取餘
A&B A 和 B 按位取與
A|B A 和 B 按位取或
A^B A 和 B 按位取異或
4）常用函式

1．求總數（count）

select count(*) cnt from emp;

2．求工資的最大值（max）

select max(sal) max_sal from emp;

3．求部門工資的最小值（min）

select deptno，min(sal) min_sal from emp group by deptno;

4．求工資的總和（sum）

select sum(sal) sum_sal from emp;

5．求工資的平均值（avg）

 select avg(sal) avg_sal from emp;
5 ）Limit 語句

典型的查詢會返回多行資料。LIMIT 子句用於限制返回的行數。

select * from emp limit 5;

2、Where 語句

1．使用 WHERE 子句，將不滿足條件的行過濾掉

2．WHERE 子句緊隨 FROM 子句

1） 比較運算子（Between/In/ Is Null）

下面表中描述了謂詞操作符，這些操作符同樣可以用於 JOIN…ON 和 HAVING 語句中。

操作符 支援的資料型別 描述
A=B 基本資料型別 如果 A 等於 B 則返回 TRUE，反之返回 FALSE
A<=>B 基本資料型別 如果 A 和 B 都為 NULL，則返回 TRUE，其他的和等號（=）操作符的結果一致，如果任一為 NULL 則結果為 NULL
A<>B, A!=B 基本資料型別 A 或者 B 為 NULL 則返回 NULL；如果 A 不等於 B，則返回 TRUE，反之返回 FALSE
A<B 基本資料型別 A 或者 B 為 NULL，則返回 NULL；如果 A 小於 B，則返回 TRUE，反之返回 FALSE
A<=B 基本資料型別 A 或者 B 為 NULL，則返回 NULL；如果 A 小於等於 B，則返回 TRUE，
反之返回 FALSE
A>B 基本資料型別 A 或者 B 為 NULL，則返回 NULL；如果 A 大於 B，則返回 TRUE，反之返回 FALSE
A>=B 基本資料型別 A 或者 B 為 NULL，則返回 NULL；如果 A 大於等於 B，則返回 TRUE，反之返回 FALSE
A [NOT] BETWEEN B AND C 基本資料型別 如果 A，B 或者 C 任一為 NULL，則結果為 NULL。如果 A 的值大於等於 B 而且小於或等於 C，則結果為 TRUE，反之為 FALSE。如果使用 NOT 關鍵字則可達到相反的效果。
A IS NULL 所有資料型別 如果 A 等於 NULL，則返回 TRUE，反之返回 FALSE
A IS NOT NULL 所有資料型別 如果 A 不等於 NULL，則返回 TRUE，反之返回 FALSE
IN(數值 1, 數值 2) 所有資料型別 使用 IN 運算顯示列表中的值
A [NOT] LIKE B STRING 型別 B 是一個 SQL 下的簡單正規表示式，如果 A 與其匹配的話，則返回 TRUE；反之返回 FALSE。B 的表示式說明如下：‘x%’表示 A 必須以字母‘x’開頭，‘%x’表示 A 必須以字母’x’結尾，而 ‘%x%’表示 A 包含有字母’x’,可以位於開頭，結尾或者字串中間。如果使用 NOT 關鍵字則可達到相反的效果。
A RLIKE B, A REGEXP B STRING 型別 B 是一個正規表示式，如果 A 與其匹配，則返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正規表示式介面實現的，因為正則也依據其中的規則。例如，正規表示式必須和整個字串 A 相匹配，而不是隻需與其字串匹配。
2） Like 和 RLike

1．使用 LIKE 運算選擇類似的值

2．選擇條件可以包含字元或數字:

% 代表零個或多個字元(任意個字元)。

_ 代表一個字元。

3．RLIKE 子句是 Hive 中這個功能的一個擴充套件，其可以通過 Java 的正規表示式這個更強大的語言來指定匹配條件。

eg:

（1）查詢以 2 開頭薪水的員工資訊

select * from emp where sal LIKE '2%';

（2）查詢第二個數值為 2 的薪水的員工資訊

select * from emp where sal LIKE '_2%';

（3）查詢薪水中含有 2 的員工資訊

select * from emp where sal RLIKE '[2]';

3）邏輯運算子（And/Or/Not）
操作符 含義
AND 邏輯並
OR 邏輯或
NOT 邏輯否

3、分組查詢

1）Group By 語句

GROUP BY 語句通常會和聚合函式一起使用，按照一個或者多個列隊結果進行分組，然後對每個組執行聚合操作。

eg：

（1）計算 emp 表每個部門的平均工資

select t.deptno, avg(t.sal) avg_sal

from emp t

group by t.deptno;

（2）計算 emp 每個部門中每個崗位的最高薪水

select t.deptno, t.job, max(t.sal) max_sal

from emp t

group by t.deptno, t.job;

2） Having 語句

having 與 where 不同點

（1）where 針對表中的列發揮作用，查詢資料；having 針對查詢結果中的列發揮作用，篩選資料。

（2）where 後面不能寫聚合函式，而 having 後面可以使用聚合函式。

（3）having 只用於 group by 分組統計語句。

eg：

求每個部門的平均薪水大於 2000 的部門求每個部門的平均工資

select deptno, avg(sal)

from emp

group by deptno;

求每個部門的平均薪水大於 2000 的部門

select deptno, avg(sal) avg_sal

from emp

group by deptno

having avg_sal > 2000;

4、 Join 語句

1）等值 Join

Hive 支援通常的 SQL JOIN 語句，但是只支援等值連線，不支援非等值連線。

eg:

select

​ e.empno,

​ e.ename,

​ d.deptno,

​ d.dname

from emp e

join dept d on e.deptno != d.deptno;

會報錯，'>'和'<' 這種也不支援

2） 表的別名

1．好處

（1）使用別名可以簡化查詢。

（2）使用表名字首可以提高執行效率。<提高的不多，不過也算可以優化提高的點，同時也增加sql的可讀性>

3） 內連線

只有進行連線的兩個表中都存在與連線條件相匹配的資料才會被保留下來。

4） 左外連線左外連線

JOIN 操作符左邊表中符合 WHERE 子句的所有記錄將會被返回。

5）右外連線右外連線

JOIN 操作符右邊表中符合 WHERE 子句的所有記錄將會被返回。

6） 滿外連線

將會返回所有表中符合 WHERE 語句條件的所有記錄。如果任一表的指定欄位沒有符合條件的值的話，那麼就使用 NULL 值替代。

7） 多表連線

注意：連線 n 個表，至少需要 n-1 個連線條件。

例如：連線三個表，至少需要兩個連線條件。

eg:

1．建立位置表

create table if not exists default.location( loc int, loc_name string) row format delimited fields terminated by '\t';

2．匯入資料

hive (default)>load data local inpath '/opt/module/data/location.txt' into table default.location;

3．多表連線查詢

SELECT e.ename, d.deptno, l.loc_name

FROM emp e

JOIN dept d ON d.deptno = e.deptno

JOIN location l ON d.loc = l.loc;

大多數情況下，Hive 會對每對 JOIN 連線物件啟動一個 MapReduce 任務。本例中會首先啟動一個 MapReduce job 對錶 e 和表 d 進行連線操作，然後會再啟動一個 MapReduce job 將第一個 MapReduce job 的輸出和表 l;進行連線操作。

注意：為什麼不是表 d 和表 l 先進行連線操作呢？這是因為 Hive 總是按照從左到右的順序執行的。

8） 笛卡爾積

1．笛卡爾集會在下面條件下產生

（1）省略連線條件

（2）連線條件無效

（3）所有表中的所有行互相連線

注意：開啟嚴格模式的話，笛卡爾積這種查詢會報錯

9） 連線謂詞中不支援 or
select
	e.empno,
	e.ename,
	d.deptno
from emp e 
join dept d on e.deptno=d.deptno or e.ename=d.dname;

FAILED: SemanticException [Error 10019]: Line 10:3 OR not supported in JOIN currently 'dname'

5、排序

1）全域性排序（Order By）

Order By：全域性排序，一個 Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（預設）

DESC（descend）: 降序

2．ORDER BY 子句在 SELECT 語句的結尾

2） 按照別名排序

eg:

按照員工薪水的 2 倍排序

select ename, sal*2 twosal from emp order by twosal;

3） 多個列排序

eg:

按照部門升序和工資降序排序

select ename, deptno, sal from emp order by deptno asc, sal desc ;

4） 每個 MapReduce 內部排序/區內排序（Sort By）

Sort By：每個 Reducer 內部進行排序，對全域性結果集來說不是排序。

eg:

1．設定 reduce 個數

hive (default)> set mapreduce.job.reduces=3;

2．檢視設定 reduce 個數

hive (default)> set mapreduce.job.reduces;

3．根據部門編號降序檢視員工資訊

hive (default)> select * from emp sort by empno desc;

4．將查詢結果匯入到檔案中（按照部門編號降序排序）

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

5） 分割槽排序（Distribute By）

Distribute By：類似 MR 中 partition，進行分割槽，結合 sort by 使用。

注意，Hive 要求 DISTRIBUTE BY 語句要寫在 SORT BY 語句之前。

對於 distribute by 進行測試，一定要分配多 reduce 進行處理，否則無法看到 distribute by 的效果。

eg：

根據部門編號檢視每個部門，再根據員工編號降序檢視每個部門中員工

先按照部門編號分割槽，再按照員工編號降序排序。

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

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;

注意：按照部門編號分割槽，不一定就是固定死的數值，可以是 20 號和 30 號部門分到一個分割槽裡面去。

6） 分桶及抽樣查詢<很少用>

分割槽針對的是資料的儲存路徑；分桶針對的是資料檔案。

分割槽提供一個隔離資料和優化查詢的便利方式。不過，並非所有的資料集都可形成合理的分割槽，特別是之前所提到過的要確定合適的劃分大小這個疑慮。

分桶是將資料集分解成更容易管理的若干部分的另一個技術。

eg:

先建立分桶表，通過直接匯入資料檔案的方式

（1）資料準備

student.txt

（2）建立分桶表,和一個普通表

create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';

create table stu(id int, name string) row format delimited fields terminated by '\t';

向普通的 stu 表中匯入資料

load data local inpath '/opt/module/datas/student.txt' into table stu;

（3）檢視錶結構

hive (default)> desc formatted stu_buck;

Num Buckets: 4

（4）設定屬性,通過子查詢的方式匯入資料

hive (default)> set hive.enforce.bucketing=true;

hive (default)> set mapreduce.job.reduces=-1;

hive (default)> insert into table stu_buck select id, name from stu;

分桶抽樣查詢

對於非常大的資料集，有時使用者需要使用的是一個具有代表性的查詢結果而不是全部結果。Hive 可以通過對錶進行抽樣來滿足這個需求。查詢表 stu_buck 中的資料。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample 是抽樣語句，語法：TABLESAMPLE(BUCKET x OUT OF y) 。

y 必須是 table 總 bucket 數的倍數或者因子。hive 根據 y 的大小，決定抽樣的比例。例如，table 總共分了 4 份，當 y=2 時，抽取(4/2=)2 個 bucket 的資料，當 y=8 時，抽取(4/8=)1/2 個 bucket 的資料。

x 表示從哪個 bucket 開始抽取，如果需要取多個分割槽，以後的分割槽號為當前分割槽號加上 y。

例如，table 總 bucket 數為 4，tablesample(bucket 1 out of 2)，表示總共抽取（4/2=）2 個 bucket的資料，抽取第 1(x)個和第 3(x+y)個 bucket 的資料。

注意：x 的值必須小於等於 y 的值，否則

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

6、常用查詢函式

1）條件函式
Return Type Name(Signature) Description
T if(boolean testCondition, T valueTrue, T valueFalseOrNull) Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.如果testCondition 為true就返回valueTrue,否則返回valueFalseOrNull ，（valueTrue，valueFalseOrNull為泛型）
T nvl(T value, T default_value) Returns default value if value is null else returns value (as of HIve 0.11).如果value值為NULL就返回default_value,否則返回value
T COALESCE(T v1, T v2, ...) Returns the first v that is not NULL, or NULL if all v's are NULL.返回第一非null的值，如果全部都為NULL就返回NULL 如：COALESCE (NULL,44,55)=44/strong>
T CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END* When a = b, returns c; when a = d, returns e; else returns f.如果a=b就返回c,a=d就返回e，否則返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 將返回4
T CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END* When a = true, returns b; when c = true, returns d; else returns e.如果a=ture就返回b,c= ture就返回d，否則返回e 如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 將返回5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 將返回0
boolean isnull( a ) Returns true if a is NULL and false otherwise.如果a為null就返回true，否則返回false
boolean isnotnull ( a ) Returns true if a is not NULL and false otherwise.如果a為非null就返回true，否則返回false
2）數學函式
Return Type Name (Signature) Description
DOUBLE round(DOUBLE a) Returns the rounded BIGINT value of a.返回對a四捨五入的BIGINT值
DOUBLE round(DOUBLE a, INT d) Returns a rounded to d decimal places.返回DOUBLE型d的保留n位小數的DOUBLW型的近似值
DOUBLE bround(DOUBLE a) Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4. 銀行家舍入法（14：舍，69：進，5->前位數是偶：舍，5->前位數是奇：進）
DOUBLE bround(DOUBLE a, INT d) Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. 銀行家舍入法,保留d位小數
BIGINT floor(DOUBLE a) Returns the maximum BIGINT value that is equal to or less than a向下取整，最數軸上最接近要求的值的左邊的值 如：6.10->6 -3.4->-4
BIGINT ceil(DOUBLE a), ceiling(DOUBLE a) Returns the minimum BIGINT value that is equal to or greater than a.求其不小於小給定實數的最小整數如：ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLE rand(), rand(INT seed) Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.每行返回一個DOUBLE型隨機數seed是隨機因子
DOUBLE exp(DOUBLE a), exp(DECIMAL a) Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0.返回e的a冪次方， a可為小數
DOUBLE ln(DOUBLE a), ln(DECIMAL a) Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0.以自然數為底d的對數，a可為小數
DOUBLE log10(DOUBLE a), log10(DECIMAL a) Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0.以10為底d的對數，a可為小數
DOUBLE log2(DOUBLE a), log2(DECIMAL a) Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0.以2為底數d的對數，a可為小數
DOUBLE log(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a) Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0.以base為底的對數，base 與 a都是DOUBLE型別
DOUBLE pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) Returns ap.計算a的p次冪
DOUBLE sqrt(DOUBLE a), sqrt(DECIMAL a) Returns the square root of a. Decimal version added in Hive 0.13.0.計算a的平方根
STRING bin(BIGINT a) Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin).計算二進位制a的STRING型別，a為BIGINT型別
STRING hex(BIGINT a) hex(STRING a) hex(BINARY a) If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)計算十六進位制a的STRING型別，如果a為STRING型別就轉換成字元相對應的十六進位制
BINARY unhex(STRING a) Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.)hex的逆方法
STRING conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).將GIGINT/STRING型別的num從from_base進位制轉換成to_base進位制
DOUBLE abs(DOUBLE a) Returns the absolute value.計算a的絕對值
INT or DOUBLE pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) Returns the positive value of a mod b.a對b取模
DOUBLE sin(DOUBLE a), sin(DECIMAL a) Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0.求a的正弦值
DOUBLE asin(DOUBLE a), asin(DECIMAL a) Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求d的反正弦值
DOUBLE cos(DOUBLE a), cos(DECIMAL a) Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0.求餘弦值
DOUBLE acos(DOUBLE a), acos(DECIMAL a) Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求反餘弦值
DOUBLE tan(DOUBLE a), tan(DECIMAL a) Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0.求正切值
DOUBLE atan(DOUBLE a), atan(DECIMAL a) Returns the arctangent of a. Decimal version added in Hive 0.13.0.求反正切值
DOUBLE degrees(DOUBLE a), degrees(DECIMAL a) Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0.獎弧度值轉換角度值
DOUBLE radians(DOUBLE a), radians(DOUBLE a) Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0.將角度值轉換成弧度值
INT or DOUBLE positive(INT a), positive(DOUBLE a) Returns a.返回a
INT or DOUBLE negative(INT a), negative(DOUBLE a) Returns -a.返回a的相反數
DOUBLE or INT sign(DOUBLE a), sign(DECIMAL a) Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0.如果a是正數則返回1.0，是負數則返回-1.0，否則返回0.0
DOUBLE e() Returns the value of e.數學常數e
DOUBLE pi() Returns the value of pi.數學常數pi
BIGINT factorial(INT a) Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20]. 求a的階乘
DOUBLE cbrt(DOUBLE a) Returns the cube root of a double value (as of Hive 1.2.0). 求a的立方根
INT BIGINT shiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b) Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按位左移
INTBIGINT shiftright(TINYINT|SMALLINT|INT a, INTb)shiftright(BIGINT a, INT b) Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按拉右移
INTBIGINT shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),shiftrightunsigned(BIGINT a, INT b) Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.無符號按位右移（<<<）
T greatest(T v1, T v2, ...) Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive 2.0.0). 求最大值
T least(T v1, T v2, ...) Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive 2.0.0). 求最小值
3）集合函式
Return Type Name(Signature) Description
int size(Map<K.V>) Returns the number of elements in the map type.求map的長度
int size(Array) Returns the number of elements in the array type.求陣列的長度
array map_keys(Map<K.V>) Returns an unordered array containing the keys of the input map.返回map中的所有key
array map_values(Map<K.V>) Returns an unordered array containing the values of the input map.返回map中的所有value
boolean array_contains(Array, value) Returns TRUE if the array contains value.如該陣列Array包含value返回true。，否則返回false
array sort_array(Array) Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0).按自然順序對陣列進行排序並返回
4）型別轉換函式
Return Type **Name(Signature) ** Description
binary binary(string|binary) Casts the parameter into a binary.將輸入的值轉換成二進位制
Expected "=" to follow "type" cast(expr as ) Converts the results of the expression expr to . For example, cast('1' as BIGINT) will convert the string '1' to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string.將expr轉換成type型別 如：cast("1" as BIGINT) 將字串1轉換成了BIGINT型別，如果轉換失敗將返回NULL
5）日期函式
Return Type Name(Signature) Description
string from_unixtime(bigint unixtime[, string format]) Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".將時間的秒值轉換成format格式（format可為“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
bigint unix_timestamp() Gets current Unix timestamp in seconds.獲取本地時區下的時間戳
bigint unix_timestamp(string date) Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail: unix_timestamp('2009-03-20 11:30:01') = 1237573801將格式為yyyy-MM-dd HH:mm:ss的時間字串轉換成時間戳 如unix_timestamp('2009-03-20 11:30:01') = 1237573801
bigint unix_timestamp(string date, string pattern) Convert time string with given pattern (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), return 0 if fail: unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.將指定時間字串格式字串轉換成Unix時間戳，如果格式不對返回0 如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400
string to_date(string timestamp) Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01".返回時間字串的日期部分
int year(string date) Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970.返回時間字串的年份部分
int quarter(date/timestamp/string) Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive 1.3.0). Example: quarter('2020-04-08') = 2.返回當前時間屬性哪個季度 如quarter('2020-04-08') = 2
int month(string date) Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11.返回時間字串的月份部分
int day(string date) dayofmonth(date) Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1.返回時間字串的天
int hour(string date) Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12.返回時間字串的小時
int minute(string date) Returns the minute of the timestamp.返回時間字串的分鐘
int second(string date) Returns the second of the timestamp.返回時間字串的秒
int weekofyear(string date) Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44.返回時間字串位於一年中的第幾個周內 如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int datediff(string enddate, string startdate) Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2.計算開始時間startdate到結束時間enddate相差的天數
string date_add(string startdate, int days) Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.從開始時間startdate加上days
string date_sub(string startdate, int days) Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'.從開始時間startdate減去days
timestamp from_utc_timestamp(timestamp, string timezone) Assumes given timestamp is UTC and converts to given timezone (as of Hive 0.8.0). For example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00.如果給定的時間戳並非UTC，則將其轉化成指定的時區下時間戳
timestamp to_utc_timestamp(timestamp, string timezone) Assumes given timestamp is in given timezone and converts to UTC (as of Hive 0.8.0). For example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00.如果給定的時間戳指定的時區下時間戳，則將其轉化成UTC下的時間戳
date current_date Returns the current date at the start of query evaluation (as of Hive 1.2.0). All calls of current_date within the same query return the same value.返回當前時間日期
timestamp current_timestamp Returns the current timestamp at the start of query evaluation (as of Hive 1.2.0). All calls of current_timestamp within the same query return the same value.返回當前時間戳
string add_months(string start_date, int num_months) Returns the date that is num_months after start_date (as of Hive 1.1.0). start_date is a string, date or timestamp. num_months is an integer. The time part of start_date is ignored. If start_date is the last day of the month or if the resulting month has fewer days than the day component of start_date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as start_date.返回當前時間下再增加num_months個月的日期
string last_day(string date) Returns the last day of the month which the date belongs to (as of Hive 1.1.0). date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.返回這個月的最後一天的日期，忽略時分秒部分（HH:mm:ss）
string next_day(string start_date, string day_of_week) Returns the first date which is later than start_date and named as day_of_week (as of Hive1.2.0). start_date is a string/date/timestamp. day_of_week is 2 letters, 3 letters or full name of the day of the week (e.g. Mo, tue, FRIDAY). The time part of start_date is ignored. Example: next_day('2020-01-14', 'TU') = 2020-01-20.返回當前時間的下一個星期X所對應的日期 如：next_day('2020-01-14', 'TU') = 2020-01-20 以2020-01-14為開始時間，其下一個星期二所對應的日期為2020-01-20
string trunc(string date, string format) Returns date truncated to the unit specified by the format (as of Hive 1.2.0). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc('2020-03-17', 'MM') = 2020-03-01.返回時間的最開始年份或月份 如trunc("2016-06-26",“MM”)=2016-06-01 trunc("2016-06-26",“YY”)=2016-01-01 注意所支援的格式為MONTH/MON/MM, YEAR/YYYY/YY
double months_between(date1, date2) Returns number of months between dates date1 and date2 (as of Hive 1.2.0). If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'. The result is rounded to 8 decimal places. Example: months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677返回date1與date2之間相差的月份，如date1>date2，則返回正，如果date1<date2,則返回負，否則返回0.0 如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 1997-02-28 10:30:00與1996-10-30相差3.94959677個月
string date_format(date/timestamp/string ts, string fmt) Converts a date/timestamp/string to a value of string in the format specified by the date format fmt (as of Hive 1.2.0). Supported formats are Java SimpleDateFormat formats –https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. The second argument fmt should be constant. Example: date_format('2020-04-08', 'y') = '2020'.date_format can be used to implement other UDFs, e.g.:dayname(date) is date_format(date, 'EEEE')dayofyear(date) is date_format(date, 'D')按指定格式返回時間date 如：date_format("2016-06-22","MM-dd")=06-22
6）字元函式
eturn Type Name(Signature) Description
int ascii(string str) Returns the numeric value of the first character of str.返回str中首個ASCII字串的整數值
string base64(binary bin) Converts the argument from binary to a base 64 string (as of Hive 0.12.0)..將二進位制bin轉換成64位的字串
string concat(string|binary A, string|binary B...) Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings..對二進位制位元組碼或字串按次序進行拼接
array<struct<string,double>> context_ngrams(array<array>, array, int K, int pf) Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of "context". See StatisticsAndDataMining for more information..與ngram類似，但context_ngram()允許你預算指定上下文(陣列)來去查詢子序列，具體看StatisticsAndDataMining(這裡的解釋更易懂)
string concat_ws(string SEP, string A, string B...) Like concat() above, but with custom separator SEP..與concat()類似，但使用指定的分隔符喜進行分隔
string concat_ws(string SEP, array) Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0).拼接Array中的元素並用指定分隔符進行分隔
string decode(binary bin, string charset) Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).使用指定的字符集charset將二進位制值bin解碼成字串，支援的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意輸入引數為NULL都將返回NULL
binary encode(string src, string charset) Encodes the first argument into a BINARY using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).使用指定的字符集charset將字串編碼成二進位制值，支援的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一輸入引數為NULL都將返回NULL
int find_in_set(string str, string strList) Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set('ab', 'abc,b,ab,c,def') returns 3..返回以逗號分隔的字串中str出現的位置，如果引數str為逗號或查詢失敗將返回0，如果任一引數為NULL將返回NULL回
string format_number(number x, int d) Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0).將數值X轉換成"#,###,###.##"格式字串，並保留d位小數，如果d為0，將進行四捨五入且不保留小數
string get_json_object(string json_string, string path) Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys *cannot start with numbers.* This is due to restrictions on Hive column names..從指定路徑上的JSON字串抽取出JSON物件，並返回這個物件的JSON格式，如果輸入的JSON是非法的將返回NULL,注意此路徑上JSON字串只能由數字 字母 下劃線組成且不能有大寫字母和特殊字元，且key不能由數字開頭，這是由於Hive對列名的限制
boolean in_file(string str, string filename) Returns true if the string str appears as an entire line in filename..如果檔名為filename的檔案中有一行資料與字串str匹配成功就返回true
int instr(string str, string substr) Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1..查詢字串str中子字串substr出現的位置，如果查詢失敗將返回0，如果任一引數為Null將返回null，注意位置為從1開始的
int length(string A) Returns the length of the string..返回字串的長度
int locate(string substr, string str[, int pos]) Returns the position of the first occurrence of substr in str after position pos..查詢字串str中的pos位置後字串substr第一次出現的位置
string lower(string A) lcase(string A) Returns the string resulting from converting all characters of B to lower case. For example, lower('fOoBaR') results in 'foobar'..將字串A的所有字母轉換成小寫字母
string lpad(string str, int len, string pad) Returns str, left-padded with pad to a length of len..從左邊開始對字串str使用字串pad填充，最終len長度為止，如果字串str本身長度比len大的話，將去掉多餘的部分
string ltrim(string A) Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '..去掉字串A前面的空格
array<struct<string,double>> ngrams(array<array>, int N, int K, int pf) Returns the top-k N-grams from a set of tokenized sentences, such as those returned by the sentences() UDAF. See StatisticsAndDataMining for more information..返回出現次數TOP K的的子序列,n表示子序列的長度，具體看StatisticsAndDataMining (這裡的解釋更易懂)
string parse_url(string urlString, string partToExtract [, string keyToExtract]) Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'..返回從URL中抽取指定部分的內容，引數url是URL字串，而引數partToExtract是要抽取的部分，這個引數包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') ='facebook.com'，如果引數partToExtract值為QUERY則必須指定第三個引數key 如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') =‘v1’
string printf(String format, Obj... args) Returns the input formatted according do printf-style format strings (as of Hive0.9.0)..按照printf風格格式輸出字串
string regexp_extract(string subject, string pattern, int index) Returns the string extracted using the pattern. For example, regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\s' is necessary to match whitespace, etc. The 'index' parameter is the Java regex Matcher group() method index. See docs/api/java/util/regex/Matcher.html for more information on the 'index' or Java regex group() method..抽取字串subject中符合正規表示式pattern的第index個部分的子字串，注意些預定義字元的使用，如第二個引數如果使用'\s'將被匹配到s,'\s'才是匹配空格
string regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\s' is necessary to match whitespace, etc..按照Java正規表示式PATTERN將字串INTIAL_STRING中符合條件的部分成REPLACEMENT所指定的字串，如裡REPLACEMENT這空的話，抽符合正則的部分將被去掉 如：regexp_replace("foobar", "oo|ar", "") = 'fb.' 注意些預定義字元的使用，如第二個引數如果使用'\s'將被匹配到s,'\s'才是匹配空格
string repeat(string str, int n) Repeats str n times..重複輸出n次字串str
string reverse(string A) Returns the reversed string..反轉字串
string rpad(string str, int len, string pad) Returns str, right-padded with pad to a length of len..從右邊開始對字串str使用字串pad填充，最終len長度為止，如果字串str本身長度比len大的話，將去掉多餘的部分
string rtrim(string A) Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(' foobar ') results in ' foobar'..去掉字串後面出現的空格
array<array> sentences(string str, string lang, string locale) Tokenizes a string of natural language text into words and sentences, where each sentence is broken at the appropriate sentence boundary and returned as an array of words. The 'lang' and 'locale' are optional arguments. For example, sentences('Hello there! How are you?') returns ( ("Hello", "there"), ("How", "are", "you") )..字串str將被轉換成單詞陣列，如：sentences('Hello there! How are you?') =( ("Hello", "there"), ("How", "are", "you") )
string space(int n) Returns a string of n spaces..返回n個空格
array split(string str, string pat) Splits str around pat (pat is a regular expression)..按照正規表示式pat來分割字串str,並將分割後的陣列字串的形式返回
map<string,string> str_to_map(text[, delimiter1, delimiter2]) Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ',' for delimiter1 and '=' for delimiter2..將字串str按照指定分隔符轉換成Map，第一個引數是需要轉換字串，第二個引數是鍵值對之間的分隔符，預設為逗號;第三個引數是鍵值之間的分隔符，預設為"="
string substr(string|binary A, int start) substring(string|binary A, int start) Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr('foobar', 4) results in 'bar' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..對於字串A,從start位置開始擷取字串並返回
string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) Returns the substring or slice of the byte array of A starting from start position with length len. For example, substr('foobar', 4, 1) results in 'b' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..對於二進位制/字串A,從start位置開始擷取長度為length的字串並返回
string substring_index(string A, string delim, int count) Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index('www.apache.org', '.', 2) = 'www.apache'..擷取第count分隔符之前的字串，如count為正則從左邊開始擷取，如果為負則從右邊開始擷取
string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translatefunction in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)Char/varchar support added as of Hive 0.14.0..將input出現在from中的字串替換成to中的字串 如：translate("MOBIN","BIN","M")="MOM"
string trim(string A) Returns the string resulting from trimming spaces from both ends of A. For example, trim(' foobar ') results in 'foobar'.將字串A前後出現的空格去掉
binary unbase64(string str) Converts the argument from a base 64 string to BINARY. (As of Hive 0.12.0.).將64位的字串轉換二進位制值
string upper(string A) ucase(string A) Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'..將字串A中的字母轉換成大寫字母
string initcap(string A) Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace. (As of Hive 1.1.0.).將字串A轉換第一個字母大寫其餘字母的字串
int levenshtein(string A, string B) Returns the Levenshtein distance between two strings (as of Hive 1.2.0). For example, levenshtein('kitten', 'sitting') results in 3..計算兩個字串之間的差異大小 如：levenshtein('kitten', 'sitting') = 3
string soundex(string A) Returns soundex code of the string (as of Hive 1.2.0). For example, soundex('Miller') results in M460..將普通字串轉換成soundex字串
7）聚合函式
Return Type Name(Signature) Description
BIGINT count(*), count(expr), count(DISTINCT expr[, expr...]) count(*) - Returns the total number of retrieved rows, including rows containing NULL values.統計總行數，包括含有NULL值的行count(expr) - Returns the number of rows for which the supplied expression is non-NULL.統計提供非NULL的expr表示式值的行數count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL. Execution of this can be optimized with hive.optimize.distinct.rewrite.統計提供非NULL且去重後的expr表示式值的行數
DOUBLE sum(col), sum(DISTINCT col) Returns the sum of the elements in the group or the sum of the distinct values of the column in the group.sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重後的列的和
DOUBLE avg(col), avg(DISTINCT col) Returns the average of the elements in the group or the average of the distinct values of the column in the group.avg(col),表示求指定列的平均值，avg(DISTINCT col)表示求去重後的列的平均值
DOUBLE min(col) Returns the minimum of the column in the group.求指定列的最小值
DOUBLE max(col) Returns the maximum value of the column in the group.求指定列的最大值
DOUBLE variance(col), var_pop(col) Returns the variance of a numeric column in the group.求指定列數值的方差
DOUBLE var_samp(col) Returns the unbiased sample variance of a numeric column in the group.求指定列數值的樣本方差
DOUBLE stddev_pop(col) Returns the standard deviation of a numeric column in the group.求指定列數值的標準偏差
DOUBLE stddev_samp(col) Returns the unbiased sample standard deviation of a numeric column in the group.求指定列數值的樣本標準偏差
DOUBLE covar_pop(col1, col2) Returns the population covariance of a pair of numeric columns in the group.求指定列數值的協方差
DOUBLE covar_samp(col1, col2) Returns the sample covariance of a pair of a numeric columns in the group.求指定列數值的樣本協方差
DOUBLE corr(col1, col2) Returns the Pearson coefficient of correlation of a pair of a numeric columns in the group.返回兩列數值的相關係數
DOUBLE percentile(BIGINT col, p) Returns the exact pth percentile of a column in the group (does not work with floating point types). p must be between 0 and 1. NOTE: A true percentile can only be computed for integer values. Use PERCENTILE_APPROX if your input is non-integral.返回col的p%分位數
8）表生成函式
Return Type Name(Signature) Description
Array Type explode(array<TYPE> a) For each element in a, generates a row containing that element.對於a中的每個元素，將生成一行且包含該元素
N rows explode(ARRAY) Returns one row for each element from the array..每行對應陣列中的一個元素
N rows explode(MAP) Returns one row for each key-value pair from the input map with two columns in each row: one for the key and another for the value. (As of Hive 0.8.0.).每行對應每個map鍵-值，其中一個欄位是map的鍵，另一個欄位是map的值
N rows posexplode(ARRAY) Behaves like explode for arrays, but includes the position of items in the original array by returning a tuple of (pos, value). (As of Hive 0.13.0.).與explode類似，不同的是還返回各元素在陣列中的位置
N rows stack(INT n, v_1, v_2, ..., v_k) Breaks up v_1, ..., v_k into n rows. Each row will have k/n columns. n must be constant..把M列轉換成N行，每行有M/N個欄位，其中n必須是個常數
tuple json_tuple(jsonStr, k1, k2, ...) Takes a set of names (keys) and a JSON string, and returns a tuple of values. This is a more efficient version of the get_json_object UDF because it can get multiple keys with just one call..從一個JSON字串中獲取多個鍵並作為一個元組返回，與get_json_object不同的是此函式能一次獲取多個鍵值
tuple parse_url_tuple(url, p1, p2, ...) This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:..返回從URL中抽取指定N部分的內容，引數url是URL字串，而引數p1,p2,....是要抽取的部分，這個引數包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:
inline(ARRAY<STRUCT[,STRUCT]>) Explodes an array of structs into a table. (As of Hive 0.10.).將結構體陣列提取出來並插入到表中
9）其它函式
collect

Hive中collect相關的函式有collect_list和collect_set。
它們都是將分組中的某列轉為一個陣列返回，不同的是collect_list不去重而collect_set去重。還可以利用collect來突破group by的限制，Hive中在group by查詢的時候要求出現在select後面的列都必須是出現在group by後面的，即select列必須是作為分組依據的列，但是有的時候我們想根據A進行分組然後隨便取出每個分組中的一個B，比如按照使用者進行分組，然後隨便拿出一個他看過的視訊名稱：

select username, collect_list(video_name)[0] 
from user_visit_video 
group by username;

注：與之對應的是explode，行轉列

Lateral View

lateral view是Hive中提供給表生成函式的結合，它可以解決表生成函式不能新增額外的select列的問題。
lateral view其實就是用來和類似explode這種表生成函式函式聯用的，lateral view會將表生成函式生成的結果放到一個虛擬表中，然後這個虛擬表會和輸入行進行join來達到連線表生成函式外的select欄位的目的。

  • 格式一
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

lateral view在UDTF前使用，表示連線UDTF所分裂的欄位。

UDTF(expression)：使用的表生成函式，例如explode()。

tableAlias：表示表生成函式轉換的虛擬表的名稱。

columnAlias：表示虛擬表的虛擬欄位名稱，如果分裂之後有一個列，則寫一個即可；如果分裂之後有多個列，按照列的順序在括號中宣告所有虛擬列名，以逗號隔開。

eg:

統計人員表中共有多少種愛好、多少個城市?

select count(distinct(myCol1)), count(distinct(myCol2)) from psn

LATERAL VIEW explode(likes) myTable1 AS myCol1

LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;

  • 格式二
from basetable (lateral view)*

在from子句中使用，一般和格式一搭配使用，這個格式只是說明了lateral view的使用位置。
from子句後面也可以跟多個lateral view語句，使用空格間隔就可以了。

  • 格式三
from basetable (lateral view outer)*

它比格式二隻是多了一個outer，這個outer的作用是在表生成函式轉換列的時候將其中的空也給展示出來，UDTF預設是忽略輸出空的，加上outer之後，會將空也輸出，顯示為NULL。這個功能是在Hive0.12是開始支援的。

7、視窗函式

普通的聚合函式聚合的行集是組,視窗函式聚合的行集是視窗。因此,普通的聚合函式每組(Group by)只返回一個值，而視窗函式則可為視窗中的每行都返回一個值。簡單理解就是對查詢的結果多出一列，這一列可以是聚合值，也可以是排序值。 視窗函式一般分為兩類——聚合視窗函式和排序視窗函式。

視窗函式的呼叫格式為：

函式名(列) OVER(選項)

OVER 關鍵字表示把函式當成視窗函式而不是聚合函式。SQL標準允許將所有聚合函式用做視窗函式，使用OVER關鍵字來區分這兩種用法。

OVER()

指定分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化；

在over()裡面用的：

​ CURRENT ROW：當前行；

​ n PRECEDING：往前 n 行資料；

​ n FOLLOWING：往後 n 行資料；

​ UNBOUNDED：起點，

​ UNBOUNDED PRECEDING 表示從前面的起點，

​ UNBOUNDED FOLLOWING 表示到後面的終點；

在over()前用的:

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

​ LEAD(col,n)：往後第 n 行資料；

​ NTILE(n)：把有序分割槽中的行分發到指定資料的組中，各個組有編號，編號從 1 開始，對於每一行，NTILE 返回此行所屬的組的編號。注意：n 必須為 int 型別。

聚合視窗函式
SUM

1．資料準備：

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4

2．建立本地 business.txt，匯入資料

[chaos@hadoop102 datas]$ vi cookie1.txt

3．建立 hive 表並匯入資料
create table cookie1(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/opt/module/datas/cookie1.txt" into table cookie1;

4．查詢示例

select
cookieid,
createtime,
pv,
sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,
sum(pv) over (partition by cookieid order by createtime) as pv2,
sum(pv) over (partition by cookieid) as pv3,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,
sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,
sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from cookie1;

說明：

pv1: 分組內從起點到當前行的pv累積，如，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號
pv2: 同pv1
pv3: 分組內(cookie1)所有的pv累加
pv4: 分組內當前行+往前3行，如，11號=10號+11號， 12號=10號+11號+12號， 13號=10號+11號+12號+13號， 14號=11號+12號+13號+14號
pv5: 分組內當前行+往前3行+往後1行，如，14號=11號+12號+13號+14號+15號=5+7+3+2+4=21
pv6: 分組內當前行+往後所有行，如，13號=13號+14號+15號+16號=3+2+4+4=13，14號=14號+15號+16號=2+4+4=10

如果不指定ROWS BETWEEN,預設為從起點到當前行
如果不指定ORDER BY，則將分組內所有值累加
注：其他AVG，MIN，MAX，和SUM用法一樣

AVG

select
cookieid,
createtime,
pv,
avg(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 預設為從起點到當前行
avg(pv) over (partition by cookieid order by createtime) as pv2, --從起點到當前行，結果同pv1
avg(pv) over (partition by cookieid) as pv3, --分組內所有行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --當前行+往前3行
avg(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --當前行+往前3行+往後1行
avg(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --當前行+往後所有行
from cookie1;

MIN

select
cookieid,
createtime,
pv,
min(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 預設為從起點到當前行
min(pv) over (partition by cookieid order by createtime) as pv2, --從起點到當前行，結果同pv1
min(pv) over (partition by cookieid) as pv3, --分組內所有行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --當前行+往前3行
min(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --當前行+往前3行+往後1行
min(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --當前行+往後所有行
from cookie1;

MAX

select
cookieid,
createtime,
pv,
max(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, -- 預設為從起點到當前行
max(pv) over (partition by cookieid order by createtime) as pv2, --從起點到當前行，結果同pv1
max(pv) over (partition by cookieid) as pv3, --分組內所有行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, --當前行+往前3行
max(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, --當前行+往前3行+往後1行
max(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 --當前行+往後所有行
from cookie1;

排序視窗函式（序列函式）

表示根據COL1分組，在分組內部根據COL2排序， 而此函式計算的值就表示每組內部排序後的順序編號 （該編號在組內是連續並且唯一的)。

注意： 序列函式不支援WINDOW子句。（ROWS BETWEEN）

NTILE

NTILE(n)，用於將分組資料按照順序切分成n片，返回當前切片值
NTILE不支援ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均勻，預設增加第一個切片的分佈

eg:

select
cookieid,
createtime,
pv,
ntile(2) over (partition by cookieid order by createtime) as rn1, --分組內將資料分成2片
ntile(3) over (partition by cookieid order by createtime) as rn2, --分組內將資料分成2片
ntile(4) over (order by createtime) as rn3 --將所有資料分成4片
from cookie.cookie2
order by cookieid,createtime;

統計一個cookie，pv數最多的前1/3的天

select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn
from cookie.cookie2;

--rn = 1 的記錄，就是我們想要的結果

ROW_NUMBER

ROW_NUMBER() –從1開始，按照順序，生成分組內記錄的序列
ROW_NUMBER() 的應用場景非常多，再比如，獲取分組內排序第一的記錄;獲取一個session中的第一條refer等。

eg:

-- 按照pv降序排列，生成分組內每天的pv名次

select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;

-- 所以如果需要取每一組的前3名，只需要rn<=3即可，適合TopN

RANK/DENSE_RANK

—RANK() 生成資料項在分組中的排名，排名相等會在名次中留下空位
—DENSE_RANK() 生成資料項在分組中的排名，排名相等會在名次中不會留下空位

eg:

select
cookieid,
createtime,
pv,
rank() over (partition by cookieid order by pv desc) as rn1,
dense_rank() over (partition by cookieid order by pv desc) as rn2,
row_number() over (partition by cookieid order by pv desc) as rn3
from cookie.cookie2
where cookieid='cookie1';

結果：

rn1 rn2 rn3
1 1 1
2 2 2
3 3 3
3 3 4
5 4 5
6 5 6
7 6 7

ROW_NUMBER、RANK和DENSE_RANK的區別

row_number： 按順序編號，不留空位
rank： 按順序編號，相同的值編相同號，留空位
dense_rank： 按順序編號，相同的值編相同的號，不留空位

例項：

1．資料準備：

name，orderdate，cost

jack,2021-01-01,10

tony,2021-01-02,15

jack,2021-02-03,23

tony,2021-01-04,29

jack,2021-01-05,46

jack,2021-04-06,42

tony,2021-01-07,50

jack,2021-01-08,55

mart,2021-04-08,62

mart,2021-04-09,68

neil,2021-05-10,12

mart,2021-04-11,75

neil,2021-06-12,80

mart,2021-04-13,94

2．建立本地 business.txt，匯入資料

[chaos@hadoop102 datas]$ vi business.txt

3．建立 hive 表並匯入資料

create table business(name string, orderdate string, cost int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

4．按需求查詢資料

（1）查詢在 2020年 4 月份購買過的顧客及總人數

（2）查詢顧客的購買明細及月購買總額

（3）上述的場景,要將 cost 按照日期進行累加

（4）檢視顧客上次的購買時間

（5）查詢前 20%時間的訂單資訊

相關文章