此中应用场景常见于特征值表
HiveSQL 行列转换
场景
特征值表：
要求：表1 用hivesql变为表2 不能用union all 使用不止一种方法解
UNION ALL的解法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| SELECT
a, "k1",k1 as sel
FROM
t_row_column
UNION ALL
SELECT
a, "k2",k2 as sel
FROM
t_row_column
UNION ALL
SELECT
a, "k3",k3 as sel
FROM
t_row_column
|
1
2
3
4
5
6
7
| HIVE 的 UNION 和 UNION ALL
UNION用于联合多个select语句的结果集，合并为一个独立的结果集，结果集去重。
UNION ALL也是用于联合多个select语句的结果集。但是不能消除重复行。现在hive只支持UNION ALL。
这里需要特别注意，每个select语句返回的列的数量和名字必须一样，同时字段类型必须完全匹配，否则会抛出语法错误。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| example
例一：字段名完全一样
select a,b,c from t1
union all
select a,b,c from t2
例二：字段名前面有表名不一致，其他一致
select t1.a,t2.b,t2.c from t1
inner join t2 on t1.a = t2.a
union all
select t3.a,t4.b,t4.c from t3
inner join t4 on t3.a = t4.a
这两个例子都不报错
但
例三：第一个查询第二个字段重命名为k，与第二个查询字段名不一样了，此时会报错
select a,'' as k,c from t1
union all
select a,b,c from t2
会报编译错误
编译错误：SemanticException The abstract syntax tree is null
|
使用行列转换SQL
1
2
3
4
5
6
7
8
| SELECT
a,key,value
FROM
t_row_column
LATERAL VIEW
explode(map('k1',k1,'k2',k2,'k3',k3)) tmp
as
key,value;
|
建表语句
1
2
3
4
5
6
7
| create table t_row_column (
a int,
k1 int,
k2 int,
k3 int
)
row format delimited fields terminated by ',';
|
拓展知识
collect_list 和 collect_set
建表语句：
1
2
3
4
5
| create table t_visit_video (
username string,
video_name string
) partitioned by (day string)
row format delimited fields terminated by ',';
|
文件内容：
1
2
3
4
5
6
7
8
| 张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间
|
加载数据:
1
| load data inpath '/tmp/visit.data' into table t_visit_video
|
数据展示:
按用户分组，取出每个用户每天看过的所有视频的名字：
1
2
| select username, collect_list(video_name)[0] from t_visit_video group by username;
|
优化：
1
2
3
4
| SELECT
username, collect_set(video_name)
FROM t_visit_video
GROUP BY username
|
如果希望第二列仅出现一个数值的话：
1
| select username, collect_list(video_name)[0] from t_visit_video group by username;
|
concat 和 concat_ws
1
2
3
4
5
| select concat('大','小') as size from 表
查询出结果为：大小
select concat('大', NULL) as size from 表
查询出结果为：null
concat中又一个参数为NULL，查出来的就为NULL
|
1
2
3
4
| select concat_ws('_','大','小','中') as size from 表
查询出结果为：大_小_中
select concat_ws('_','大','小',NULL) as size from 表
查询出结果为：大_小
|
explode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
select explode(array('liubei','zhangfei','guanyu'));
+
| col |
+
| liubei |
| zhangfei |
| guanyu |
+
select explode(map('liubei','18','zhangfei','19'));
+
| key | value |
+
| liubei | 18 |
| zhangfei | 19 |
+
|
但是如果我们直接查询
1
| select movie，explode(category) from movie_info;
|
这样会报错，因为movie的查询结果只有三条，但是explode()出来有多条语句，两者数量无法对齐
那么，我们由此引入LATERAL VIEW函数：
1
2
3
4
5
| LATERAL VIEW:
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
3.主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
4.语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
|
示例：
1
2
3
4
| select movie,category_name
from movie_info
LATERAL VIEW explode(category) tmpTable as category_name;
|