Hive的内置函数parse_url_tuple/lateral view/explode

大数据技术普及后，很多类似pv，uv的数据，在业务系统中以json的方式存储存贮在非关系型数据库中，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，Hive的lateral view内置函数在这种情景下应运而生。
【1】parse_url_tuple()
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])，parse_url_tuple()功能类似parse_url()，但它可以同时提取多个部分并返回。
返回值：String
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO。

hive> select parse_url_tuple(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY:k1’, ‘QUERY:k2’);
>>v1 v2

【2】lateral view
  是将右边的表或者结果合并到左边表，和左边表整合成一张表。

create table t_ods_tmp_referurl as
SELECT a.*,b.* FROM ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, “”", “”), ‘HOST’, ‘PATH’,‘QUERY’, ‘QUERY:id’) b as host, path, query, query_id;

相当于拓展ods_weblog_origin表中的 http_referer字段。原始表（左边表数据字段保持不变，新增拓展字段，并将原始表数据 导入新表中）。

Insert into table ods_weblog_detail partition(datestr=‘20191105’)
select c.valid,c.remote_addr,c.remote_user,c.time_local,
substring(c.time_local,0,10) as daystr,
substring(c.time_local,12) as tmstr,
substring(c.time_local,6,2) as month,
substring(c.time_local,9,2) as day,
substring(c.time_local,11,3) as hour,
c.request,c.status,c.body_bytes_sent,c.http_referer,c.ref_host,c.ref_path,c.ref_query,c.ref_query_id,c.http_user_agent
from
(SELECT
a.valid,a.remote_addr,a.remote_user,a.time_local,
a.request,a.status,a.body_bytes_sent,a.http_referer,a.http_user_agent,b.ref_host,b.ref_path,b.ref_query,b.ref_query_id
FROM ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, “”", “”), ‘HOST’, ‘PATH’,‘QUERY’, ‘QUERY:id’) b as ref_host, ref_path, ref_query, ref_query_id) c;

是将原始表中的 url 和时间字段拓展，构成一张明细表，并查询填充数据.

【3】Hive行转列 （Lateral View explode()）

原始数据：
visit_id    crowds
abc         [100,101,102]
def         [100,101]
abe         [101,105]

遍历数组中的每一列

select visit_id,crowd 
from user_crowd_info t lateral view explode(split(t.crowds,',')) adtable as crowd;

列转行
使用concat_ws函数即可

select visit_id,concat_ws(,crowd) from user_crowd_info group by visit_id;

原数据表如下图

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

explode作用是处理map结构的字段，使用案例如下：

//建表语句
create table movie_info(
movie string,
category array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';
//加载数据
load data local inpath '/opt/data/movie.txt' into table movie_info;

看下explode函数效果，以拆解category为例，可与原数据表结构对比。

select explode(category) from movie_info;

LATERAL VIEW的使用：
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

select movie,category_name
from movie_info
lateral view explode(category) table_emp as category_name;

效果如下：

其中lateral view explode(category) table_emp相当于一个虚拟表，与原表movie_info笛卡尔积关联，也可以多重使用。那么问题就这样解决了。