hive中的lateral view 与 explode函数的使用 行转列

hive中的lateral view 与 explode函数的使用

explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），
本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，
用json存储的概率比较大，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。

explode作用是处理map结构的字段，使用案例如下（hive自带map，struct，array字段类型，但是需要先定义好泛型，所以在此案例不使用）：
建表语句：
drop table explode_lateral_view;
create table explode_lateral_view
(`area` string,
`goods_id` string,
`sale_info` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS textfile;

导入数据：
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
表内数据如下

explode的使用：
我们只拆解array字段，语句为select explode(split(goods_id,',')) as goods_id from explode_lateral_view;
结果如下

拆解map字段，语句为select explode(split(area,',')) as area from explode_lateral_view;
我们会得到如下结果：

拆解json字段

这个时候要配合一下get_json_object

我们想获取所有的monthSales，第一步我们先把这个字段拆成list，并且拆成行展示：
select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from explode_lateral_view;

然后我们想用get_json_object来获取key为monthSales的数据：
select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from explode_lateral_view;
然后挂了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能写在别的函数内
如果你这么写，想查两个字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的时候，只支持一个字段，这时候就需要LATERAL VIEW出场了

LATERAL VIEW的使用：
侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。
select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

其中LATERAL VIEW explode(split(goods_id,','))goods相当于一个虚拟表，与原表explode_lateral_view笛卡尔积关联。
也可以多重使用
select goods_id2,sale_info,area2
from explode_lateral_view 
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 
LATERAL VIEW explode(split(area,','))area as area2;
也是三个表笛卡尔积的结果

现在我们解决一下上面的问题，从sale_info字段中找出所有的monthSales并且行展示
select get_json_object(concat('{',sale_info_r,'}'),'$.monthSales') as monthSales from explode_lateral_view 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_r;

最终，我们可以通过下面的句子，把这个json格式的一行数据，完全转换成二维表的方式展现

select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
     get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
     get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
     get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
  from explode_lateral_view 

LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

 

1）. Lateral View语法

 

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

2). Lateral View用于UDTF(user-defined table generating functions)中将行转成列，例如explode().

3). 目前Lateral View不支持有上而下的优化。如果使用Where子句，查询可能将不被编译。解决方法见：

此时，在查询之前执行et hive.optimize.ppd=false;

4).例子

pageAds。它有两个列：pageid，adid_list:

 

string pageid	Array<int> adid_list
"front_page"	[1, 2, 3]
"contact_page"	[3, 4, 5]

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

将输出如下结果：

string pageid	int adid
"front_page"	1
"front_page"	2
"front_page"	3
"contact_page"	3
"contact_page"	4
"contact_page"

count/group可以被使用：

SELECT adid, count(1) FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid GROUP BY adid;

结果如下：

 

int adid	count(1)
1	1
2	1
3	2
4	1
5	1

5). 多个Lateral View

例如下面的HQL:

SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

假设使用的base表如下：

 

Array<int> col1	Array<string> col2
[1, 2]	[a", "b", "c"]
[3, 4]	[d", "e", "f"]

HQL:

SELECT myCol1, col2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1;

 

将产生如下结果：

 

int mycol1	Array<string> col2
1	[a", "b", "c"]
2	[a", "b", "c"]
3	[d", "e", "f"]
4	[d", "e", "f"]

多个Lateral View的HQL：

1. SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1

2. LATERAL VIEW explode(col2) myTable2 AS myCol2;

 

将产生如下结果：

int myCol1	string myCol2
1	"a"
1	"b"
1	"c"
2	"a"
2	"b"
2	"c"
3	"d"
3	"e"
3	"f"
4	"d"
4	"e"
4	"f"