Hive 之函數 01-常用查詢函數（一）

一、空字段指派
二、時間類
- 2.1 date_format：格式化時間
- 2.2 date_add：時間跟天數相加
- 2.3 date_sub：時間跟天數相減（跟 date_add 記一個就行）
- 2.4 datediff：兩個時間相減
- 2.5 注意：
三、 case when
四、行轉列
- 4.1 CONCAT
- 4.2 CONCAT_WS
- 4.3 COLLECT_SET
- 4.4 生産中的應用
五、列轉行
- 5.1 EXPLODE
- 5.2 lateral view
- 5.3 額外拓展

一、空字段指派

NVL 函數：給值為 Null 的函數指派，它的格式是

NVL(string1, replace_with)

，它的功能是如果 string1 的值不為 Null，則傳回 string1 的值，否則，将傳回 replace_with 的值，如果 replace_with 的值也為 Null，則傳回 Null。

hive (default)> select comm from emp;
OK
comm
NULL
300.0
500.0
NULL
1400.0
NULL
NULL
NULL
NULL
0.0
NULL
NULL
NULL
NULL
Time taken: 0.892 seconds, Fetched: 14 row(s)

hive (default)> select NVL(comm, '--') from emp;
OK
_c0
--
300.0
500.0
--
1400.0
--
--
--
--
0.0
--
--
--
--
Time taken: 0.072 seconds, Fetched: 14 row(s)

【注】NVL 函數的第二個參數也可以是表中的某個字段：

hive (default)> select empno, nvl(comm, empno) from emp;
OK
empno	_c1
7369	7369.0
7499	300.0
7521	500.0
7566	7566.0
7654	1400.0
7698	7698.0
7782	7782.0
7788	7788.0
7839	7839.0
7844	0.0
7876	7876.0
7900	7900.0
7902	7902.0
7934	7934.0
Time taken: 0.112 seconds, Fetched: 14 row(s)

二、時間類

2.1 date_format：格式化時間

hive(default)> select date_format('1990-02-12', 'yyyy-MM-dd HH:mm:ss');
OK
_c0
1990-02-12 00:00:00
Time taken: 0.471 seconds, Fetched: 1 row(s)

2.2 date_add：時間跟天數相加

hive (default)> select date_add('2020-02-26', 4);
OK
_c0
2020-03-01
Time taken: 0.154 seconds, Fetched: 1 row(s)

hive (default)> select date_add('2020-02-26', -4);
OK
_c0
2020-02-22
Time taken: 0.079 seconds, Fetched: 1 row(s)

2.3 date_sub：時間跟天數相減（跟 date_add 記一個就行）

hive (default)> select date_sub('2020-02-26', 4);
OK
_c0
2020-02-22
Time taken: 0.103 seconds, Fetched: 1 row(s)

hive (default)> select date_sub('2020-02-26', -4);
OK
_c0
2020-03-01
Time taken: 0.069 seconds, Fetched: 1 row(s)

2.4 datediff：兩個時間相減

第一個參數減去第二個參數。

hive(default)> select datediff('2020-03-03', '2020-02-28');
OK
_c0
4
Time taken: 0.125 seconds, Fetched: 1 row(s)

hive (default)> select datediff('2020-02-28', '2020-03-03');
OK
_c0
-4
Time taken: 0.057 seconds, Fetched: 1 row(s)

2.5 注意：

以上關于時間的計算隻接受中橫線作為時間的連接配接符， / 作為連接配接符的時間會得出 Null 的結果：

hive (default)> select date_add('2020/02/26', -4);
OK
_c0
NULL
Time taken: 0.07 seconds, Fetched: 1 row(s)

想要将 / 轉換為 - 可以使用 regexp_replace() 函數：

hive (default)> select regexp_replace('2020/02/26', '/', '-');
OK
_c0
2020-02-26
Time taken: 0.068 seconds, Fetched: 1 row(s)

三、 case when

建立表并導入資料：

hive (default)> create table if not exists emp_sex(
              > name string, 
              > dept_id string, 
              > sex string)
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.423 seconds

hive (default)> load data local inpath '/opt/module/data/emp_sex.txt' 
              > into table emp_sex;
Loading data to table default.emp_sex
Table default.emp_sex stats: [numFiles=1, totalSize=78]
OK
Time taken: 0.251 seconds

查詢資料：

hive (default)> select * from emp_sex;
OK
emp_sex.name	emp_sex.dept_id	emp_sex.sex
悟空	A	男
大海	A	男
宋宋	B	男
鳳姐	A	女
婷姐	B	女
婷婷	B	女
Time taken: 0.068 seconds, Fetched: 6 row(s)

求出每個部門男女各多少人：

hive (default)> select dept_id, 
              > sum(case sex when '男' then 1 else 0 end) male_count, 
              > sum(case sex when '女' then 1 else 0 end) female_count
              > from emp_sex
              > group by dept_id;

... ...
OK
dept_id	male_count	female_count
A	2	1
B	1	2
Time taken: 34.979 seconds, Fetched: 2 row(s)

也可以改用if 函數：

hive (default)> select dept_id, 
              > sum(if(sex='男', 1, 0)) male_count, 
              > sum(if(sex='女', 1, 0)) female_count
              > from emp_sex
              > group by dept_id;

OK
dept_id	male_count	female_count
A	2	1
B	1	2
Time taken: 57.188 seconds, Fetched: 2 row(s)

四、行轉列

4.1 CONCAT

CONCAT(string A/col, string B/col, …)：傳回輸入字元串連接配接後的結果，支援任意個輸入字元串；

如：

hive (default)> select * from dept;
OK
dept.deptno	dept.dname	dept.loc
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
Time taken: 3.63 seconds, Fetched: 4 row(s)

hive (default)> select concat(deptno, '--', dname, '--', loc) from dept;
OK
_c0
10--ACCOUNTING--1700
20--RESEARCH--1800
30--SALES--1900
40--OPERATIONS--1700
Time taken: 0.057 seconds, Fetched: 4 row(s)

4.2 CONCAT_WS

可了解成 concat_with_separator，用什麼分隔符來連接配接字元串。

如果分隔符是 NULL，傳回值也将為 NULL。這個函數會跳過分隔符參數後的任何 NULL。分隔符将被加到被連接配接的字元串之間。

hive (default)> select concat_ws(Null, 'a', 'b');
OK
_c0
NULL
Time taken: 0.186 seconds, Fetched: 1 row(s)

hive (default)> select concat_ws('=', 'a', 'b', Null, '1', '', '2', ' ', '3');
OK
_c0
a=b=1==2= =3
Time taken: 0.084 seconds, Fetched: 1 row(s)

4.3 COLLECT_SET

COLLECT_SET(col)：函數隻接受基本資料類型，它的主要作用是将某字段的值進行【去重彙總】。産生 array 類型字段。

hive (default)> select * from dept;
OK
dept.deptno	dept.dname	dept.loc
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
Time taken: 0.236 seconds, Fetched: 4 row(s)

hive (default)> select collect_set(deptno) from dept;

... ...
OK
_c0
[10,20,30,40]
Time taken: 17.226 seconds, Fetched: 1 row(s)

hive (default)> select collect_set(loc) from dept;

... ...
OK
_c0
[1700,1800,1900]
Time taken: 19.131 seconds, Fetched: 1 row(s)

concat_ws() 和 collect_set() 連用：

hive (default)> select concat_ws('==', collect_set(dname)) from dept;

... ...
OK
_c0
ACCOUNTING==RESEARCH==SALES==OPERATIONS
Time taken: 23.323 seconds, Fetched: 1 row(s)

4.4 生産中的應用

需求：

原始資料：

name	constellation	blood_type
孫悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
豬八戒	白羊座	A
鳳姐	射手座	A

找出同星座、同血型的人員，并用 “|” 将其隔開，如下：

射手座,A	大海|鳳姐
白羊座,A	孫悟空|豬八戒
白羊座,B	宋宋

開搞！

建立表：

hive (default)> create table person_info(
              > name string, 
              > constellation string,
              > blood_type string)
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.036 seconds

導入資料：

hive (default)> load data local inpath
              > '/opt/module/data/constellation.txt'
              > into table person_info;
Loading data to table default.person_info
Table default.person_info stats: [numFiles=1, totalSize=101]
OK
Time taken: 0.211 seconds

查詢資料：

hive (default)> select * from person_info;
OK
person_info.name	person_info.constellation	person_info.blood_type
孫悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
豬八戒	白羊座	A
鳳姐	射手座	A
Time taken: 0.031 seconds, Fetched: 5 row(s)

第一步：先搞成下面的樣子：

hive (default)> select concat(constellation, ',', blood_type) constellation_blood, 
              > name
              > from person_info;
OK
constellation_blood	name
白羊座,A	孫悟空
射手座,A	大海
白羊座,B	宋宋
白羊座,A	豬八戒
射手座,A	鳳姐
Time taken: 0.055 seconds, Fetched: 5 row(s)

第二步：将第一步的查詢作為一個嵌套語句：

hive (default)> select constellation_blood, 
              > concat_ws('|', collect_set(name))
              > from 
              > (
              > select concat(constellation, ',', blood_type) constellation_blood,
              > name
              > from person_info
              > ) t1
              > group by constellation_blood;

... ... 
OK
constellation_blood	_c1
射手座,A	大海|鳳姐
白羊座,A	孫悟空|豬八戒
白羊座,B	宋宋
Time taken: 26.626 seconds, Fetched: 3 row(s)

五、列轉行

需求：

原始資料：

《疑犯追蹤》 懸疑,動作,科幻,劇情
《Lie to me》 懸疑,警匪,動作,心理,劇情
《戰狼 2》 戰争,動作,災難

最終結果：

将電影分類中的數組資料展開。結果如下：

《疑犯追蹤》 懸疑
《疑犯追蹤》 動作
《疑犯追蹤》 科幻
《疑犯追蹤》 劇情
《Lie to me》 懸疑
《Lie to me》 警匪
《Lie to me》 動作
《Lie to me》 心理
《Lie to me》 劇情
《戰狼 2》 戰争
《戰狼 2》 動作
《戰狼 2》 災難

開搞！

5.1 EXPLODE

EXPLODE(col)

：炸裂。将 hive 一列中複雜的 array 或者 map 結構拆分成多行。

建表：

hive (default)> create table movie_info(
              > movie string,
              > category array<string>)
              > row format delimited fields terminated by '\t'
              > collection items terminated by ',';
OK
Time taken: 0.081 seconds

導入資料：

hive (default)> load data local inpath
              > '/opt/module/data/movie.txt'
              > into table movie_info;
Loading data to table default.movie_info
Table default.movie_info stats: [numFiles=1, totalSize=134]
OK
Time taken: 0.211 seconds

查詢資料：

hive (default)> select * from movie_info;
OK
movie_info.movie	movie_info.category
《疑犯追蹤》	["懸疑","動作","科幻","劇情"]
《Lie to me》	["懸疑","警匪","動作","心理","劇情"]
《戰狼 2》	["戰争","動作","災難"]
Time taken: 0.072 seconds, Fetched: 3 row(s)

僅僅使用 explode 的話，與電影對不上：

hive (default)> select explode(category) from movie_info;
OK
col
懸疑
動作
科幻
劇情
懸疑
警匪
動作
心理
劇情
戰争
動作
災難
Time taken: 0.062 seconds, Fetched: 12 row(s)

如果直接加上電影名稱字段則報錯：

hive (default)> select movie, explode(category) from movie_info;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

這個時候就需要下面的函數了。

5.2 lateral view

側寫。

用法：

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用于和 split、 explode 等 UDTF 一起使用。它能夠将一列資料拆成多行資料。在此基礎上可以對拆分後的資料進行聚合。

hive (default)> select movie, category_alias
              > from movie_info
              > lateral view explode(category) tbl_tmp as category_alias;
OK
movie	category_alias
《疑犯追蹤》	懸疑
《疑犯追蹤》	動作
《疑犯追蹤》	科幻
《疑犯追蹤》	劇情
《Lie to me》	懸疑
《Lie to me》	警匪
《Lie to me》	動作
《Lie to me》	心理
《Lie to me》	劇情
《戰狼 2》	戰争
《戰狼 2》	動作
《戰狼 2》	災難
Time taken: 0.046 seconds, Fetched: 12 row(s)

5.3 額外拓展

将每個類型與用 “|” 隔開的對應的（多個）電影名稱展示出來：

hive (default)> select category_alias, concat_ws('|', collect_set(movie))
              > from 
              > (
              > select movie, category_alias
              > from movie_info
              > lateral view explode(category) tbl_tmp as category_alias
              > ) t1
              > group by category_alias;

... ...
OK
category_alias	_c1
劇情	《疑犯追蹤》|《Lie to me》
動作	《疑犯追蹤》|《Lie to me》|《戰狼 2》
心理	《Lie to me》
懸疑	《疑犯追蹤》|《Lie to me》
戰争	《戰狼 2》
災難	《戰狼 2》
科幻	《疑犯追蹤》
警匪	《Lie to me》
Time taken: 42.121 seconds, Fetched: 8 row(s)

Hive 之函數 01-常用查詢函數（一）一、空字段指派二、時間類三、 case when四、行轉列五、列轉行

Hive 之函數 01-常用查詢函數（一）

一、空字段指派

二、時間類

2.1 date_format：格式化時間

2.2 date_add：時間跟天數相加

2.3 date_sub：時間跟天數相減（跟 date_add 記一個就行）

2.4 datediff：兩個時間相減

2.5 注意：

三、 case when

四、行轉列

4.1 CONCAT

4.2 CONCAT_WS

4.3 COLLECT_SET

4.4 生産中的應用

五、列轉行

5.1 EXPLODE

5.2 lateral view

5.3 額外拓展

繼續閱讀

luogu1078 文化之旅

Hadoop離線_Hive的基本操作

Hive中内部表、外部表、分區、分桶以及SQL的執行順序

Hive中的内部表外部表及分區表

Hive---外部分區表的建立

Hive學習筆記 3 Hive的資料模型：内部表、分區表、外部表、桶表、視圖

HiveQL(二):分區表

Hive的分區表入門

Hive的分區表

Hive（二）--分區分桶，内部表外部表

大資料高頻面試題之Hive的小檔案合并

世界因大資料而改變

hive sql通過具體位址解析出行政區劃(省＞市＞區＞縣＞鄉＞鎮＞村)

Hive最全常見錯誤及解決方案hive --service metastore &

《Hive權威指南》第八章：HiveQL索引8 HiveQL：索引

HiveQl語句應用執行個體：WordCount具體步驟如下：

Hive 之 函數 01-常用查詢函數（一）一、 空字段指派二、 時間類三、 case when四、 行轉列五、 列轉行

Hive 之 函數 01-常用查詢函數（一）

一、 空字段指派

二、 時間類

2.1 date_format： 格式化時間

2.2 date_add： 時間跟天數相加

2.3 date_sub： 時間跟天數相減（跟 date_add 記一個就行）

2.4 datediff： 兩個時間相減

2.5 注意：

三、 case when

四、 行轉列

4.1 CONCAT

4.2 CONCAT_WS

4.3 COLLECT_SET

4.4 生産中的應用

五、 列轉行

5.1 EXPLODE

5.2 lateral view

5.3 額外拓展

繼續閱讀

Hive 之函數 01-常用查詢函數（一）一、空字段指派二、時間類三、 case when四、行轉列五、列轉行

Hive 之函數 01-常用查詢函數（一）

一、空字段指派

二、時間類

2.1 date_format：格式化時間

2.2 date_add：時間跟天數相加

2.3 date_sub：時間跟天數相減（跟 date_add 記一個就行）

2.4 datediff：兩個時間相減

四、行轉列

五、列轉行