explode和LATERAL VIEW的使用

一：explode使用

1.1解释

hive wiki对于expolde的解释如下：
explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW.
中文翻译：explode（）接受一个数组（或一个map）作为输入，并将数组元素（map）作为单独的行输出。 UDTF可以在SELECT表达式列表中使用，也可以作为LATERAL VIEW的一部分使用。

1.2测试：

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;

1.3本地数据导入：

load data local inpath ‘/home/hadoop/app/data/mockdata.txt’ into table explode_lateral_view;

1.4查询

select split(goods_id,’,’) from explode_lateral_view;
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组

select explode(split(regexp_replace(regexp_replace(sale_info,’\[\{’,’’),’}]’,’’),’},\{’)) as sale_info from explode_lateral_view;

语法解释：
首先把[{替换成空，然后把}]替换成空。最后根据},{拆分数据
替换前：[{“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”}]
替换后：“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”
然后使用split对数据按照},{拆分后,返回一个数组的格式就可以使用explode函数。

1.5错误案例

**错误使用一：UDTF explode不能写在别的函数内
使用json函数获取某一个值：
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的时候，只支持一个字段
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’
多个字段就需要LATERAL VIEW出场了

二：LATERAL VIEW的使用：

2.1概述

lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。(行转列的函数)
lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
Lateral view语法如下：
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*
fromClause: FROM baseTable (lateralView)*

2.2使用

单个使用:
select goods_id2,sale_info,area from explode_lateral_view LATERAL VIEW explode(split(goods_id,’,’))goods as goods_id2;

多个一起使用:
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 sale_info_r from explode_lateral_view
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,’\[\{’,’’),’}]’,’’),’},\{’))sale_info as sale_info_r;
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,’}’),’ .