hive 常用函数 行拆列explode 列转行concat_ws concat_set date_format

原文：https://yq.aliyun.com/articles/654743

官方文档：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode

 

日期处理函数
1）date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2020-02-10','yyyy-MM');
2020-02
2）date_add函数（加减日期）
hive (gmall)> select date_add('2020-02-10',-1);
2020-02-09
hive (gmall)> select date_add('2020-02-10',1);
2020-02-11
3）next_day函数
    （1）取当前天的下一个周一
hive (gmall)> select next_day('2020-02-12','MO')
2020-02-18
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一
hive (gmall)> select date_add(next_day('2020-02-12','MO'),-7);
2020-02-11
4）last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-02-10');
2020-02-28

 

 

1.1 concat：concat函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL

hive> select concat('a','b');
OK
ab
Time taken: 0.477 seconds, Fetched: 1 row(s)
hive> select concat('a','b',null);
OK
NULL
Time taken: 0.181 seconds, Fetched: 1 row(s)

原文链接：https://blog.csdn.net/henrrywan/java/article/details/86543202

concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。concat_ws函数需要指定分隔符。

hive> select concat_ws('-','a','b');
OK
a-b
Time taken: 0.245 seconds, Fetched: 1 row(s)
hive> select concat_ws('-','a','b',null);
OK
a-b
Time taken: 0.177 seconds, Fetched: 1 row(s)
hive> select concat_ws('','a','b',null);
OK
ab
Time taken: 0.184 seconds, Fetched: 1 row(s)

collect_set函数

1）创建原数据表

hive (gmall)>

drop table if exists stud;
create table stud (name string, area string, course string, score int);

2）向原数据表中插入数据

hive (gmall)>
insert into table stud values('zhang3','bj','math',88);
insert into table stud values('li4','bj','math',99);
insert into table stud values('wang5','sh','chinese',92);
insert into table stud values('zhao6','sh','chinese',54);
insert into table stud values('tian7','bj','chinese',91);

3）查询表中数据

hive (gmall)> select * from stud;
stud.name       stud.area       stud.course     stud.score
zhang3  bj      math    88
li4     bj      math    99
wang5   sh      chinese 92
zhao6   sh      chinese 54
tian7   bj      chinese 91
4）把同一分组的不同行的数据聚合成一个集合 

hive (gmall)> select course, collect_set(area), avg(score) from stud group by course;

chinese ["sh","bj"]     79.0
math    ["bj"]  93.5

5） 用下标可以取某一个

hive (gmall)> select course, collect_set(area)[0], avg(score) from stud group by course;

chinese sh      79.0
 math    bj      93.5

 

 

2、explode

explode(ARRAY) 列表中的每个元素生成一行
explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列

限制：
1、No other expressions are allowed in SELECT

    SELECT pageid, explode(adid_list) AS myCol... is not supported

2、UDTF's can't be nested

    SELECT explode(explode(adid_list)) AS myCol... is not supported

3、GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported

    SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

2、lateral view

可使用lateral view解除以上限制，语法：

lateralView: LATERAL VIEW explode(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

案例：

table名称为pageAds

SELECT pageid, adid

FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

输出结果：

3、多个lateral view

from语句后面可以带多个lateral view语句

案例：

表名：baseTable

from后只有一个lateral view：

SELECT myCol1, col2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1;

结果：

多个lateral view：

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

结果：

4、Outer Lateral Views

如果array类型的字段为空，但依然需返回记录，可使用outer关键词。

比如：select * from src LATERAL VIEW explode(array()) C AS a limit 10;

这条语句中的array字段是个空列表，这条语句不管src表中是否有记录，结果都是空的。

而：select * from src LATERAL VIEW OUTER explode(array()) C AS a limit 10;

结果中的记录数为src表的记录数，只是a字段为NULL。

比如：

238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL