lateralview函数,interview later
hive中lateral view json_tuple的用法
select * from t1 lateral view json_tuple(t1.info,'apply_time','pro_code') t2 as apply_time,pro_code
意思是：t1中的info信息通过lateral view进行进行解析。
lateral view：如果指定字段名则需要把lateral view查询出的列写到select中，才能在结果中出现；如果直接是select * 则自动会把lateral view查询出的列追加在后面。
要确保被解析的字段是string类型才可以使用json解析.解析map类型不能使用json解析，解析map类型可以使用col_name['key']获取对应key的value.
hive解析json\tuple网址:
hive解析json网址：
关于在Hive中将特定字符分隔的字符串拆分成多行的应用
Subject ：关于在 Hive 中将特定字符分隔的字符串拆分成多行的应用
Keys ： lateral view 、 split 、 explode 、 HQL 、 Hive 、数据拆分
1、案例描述
?假设：
有问卷p1,p2,p3,每个问卷含有问题q1,q2,q3...,每个问题对应答案a11,a21,a31，问题与答案之间,用':'分隔,每个问题之间以','分隔.例如:q1:a11,q2:a21,q3:a31:a32
问题：
将问题与答案拆分成独立的列，如：
P1 q1 a11
P1 q2 a21
…
解决方案：
使用lateral View结合Explode实现数据拆分。
2、小知识：
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
1）. Lateral View语法
lateral View: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
from Clause: FROM baseTable(lateralView)*?
2). Lateral View用于UDTF(user-defined table generating functions)中将行转成列，例如explode().
3). 目前Lateral View不支持有上而下的优化。如果使用Where子句，查询可能将不被编译。解决方法见：此时，在查询之前执行et hive.optimize.ppd=false;
3、实现步骤:
2.1 创建测试数据
drop table temp_bigdata.test_p1;
create table temp_bigdata.test_p1 as
select 'p1' as p,'q1:a11,q2:a21,q3:a31:a32' as qa from default.dual union all
select 'p2' as p,'q1:a11,q2:a21:a22,q3:a31:a32' as qa from default.dual union all
select 'p3' as p,'q1:a11,q2:a21,q3:' as qa from default.dual;
2.2 查看数据内容
select * from temp_bigdata.test_p1;
?
? p?? qa
? p3?? q1:a11,q2:a21,q3
? p2?? q1:a11,q2:a21:a22,q3:a31:a32
? p1?? q1:a11,q2:a21,q3:a31:a32
2.3 测试explode函数
select explode(split(qa,',')) as qa1 from temp_bigdata.test_p1;
? q1:a11
? q2:a21
? q3:
? q1:a11
? q2:a21:a22
? q3:a31:a32
? q1:a11
? q2:a21
? q3:a31:a32
2.4 开始处理,先将问题拆分成独立行
drop table temp_bigdata.test_p1_adid;
create table temp_bigdata.test_p1_adid as
select row_number() over(order by p,adid) rid,p, adid
? from temp_bigdata.test_p1 LATERAL VIEW explode(split(qa,',')) adtable AS adid;
select * from temp_bigdata.test_p1_adid;
rid??? p ??? adid
1??? p1??? q1:a11
2??? p1??? q2:a21
3??? p1??? q3:a31:a32
4??? p2??? q1:a11
5??? p2??? q2:a21:a22
6??? p2??? q3:a31:a32
7??? p3??? q1:a11
8??? p3??? q2:a21
9??? p3??? q3:
2.5 再将每个问题中的问题及答案拆分成多行
create table temp_bigdata.test_p1_adid2 as
select rid,
?????? p,
?????? adid, --拆分后的问题和答案
?????? split(adid,':')[0] as q,? --取出问题
?????? adid2?--拆分答案为行
? from temp_bigdata.test_p1_adid
? LATERAL VIEW explode(split(adid,':')) adttable2 as adid2;
select * from temp_bigdata.test_p1_adid2;
rid ??? p??? adid??? q ??? adid2
1??? p1??? q1:a11??? q1??? q1
1??? p1??? q1:a11??? q1??? a11
2??? p1??? q2:a21??? q2??? q2
2??? p1??? q2:a21??? q2??? a21
3??? p1??? q3:a31:a32??? q3??? q3
3??? p1??? q3:a31:a32??? q3??? a31
3??? p1??? q3:a31:a32??? q3??? a32
4??? p2??? q1:a11??? q1??? q1
4??? p2??? q1:a11??? q1??? a11
5??? p2??? q2:a21:a22??? q2??? q2
5??? p2??? q2:a21:a22??? q2??? a21
5??? p2??? q2:a21:a22??? q2??? a22
6??? p2??? q3:a31:a32??? q3??? q3
6??? p2??? q3:a31:a32??? q3??? a31
6??? p2??? q3:a31:a32??? q3??? a32
7??? p3??? q1:a11??? q1??? q1
7??? p3??? q1:a11??? q1??? a11
8??? p3??? q2:a21??? q2??? q2
8??? p3??? q2:a21??? q2??? a21
9??? p3??? q3:??? q3??? q3
9??? p3??? q3:??? q3
2.6 取出结果,将多余行过滤,及问题列=拆分后的答案列
select * from temp_bigdata.test_p1_adid2 where qadid2 order by rid,adid;
? rid ??? p??? adid??? q ??? adid2
1 ??? p1 ??? q1:a11 ??? q1 ??? a11
2 ??? p1 ??? q2:a21 ??? q2 ??? a21
3 ??? p1 ??? q3:a31:a32 ??? q3 ??? a32
3 ??? p1 ??? q3:a31:a32 ??? q3 ??? a31
4 ??? p2 ??? q1:a11 ??? q1 ??? a11
5 ??? p2 ??? q2:a21:a22 ??? q2 ??? a22
5 ??? p2 ??? q2:a21:a22 ??? q2 ??? a21
6 ??? p2 ??? q3:a31:a32 ??? q3 ??? a32
6 ??? p2 ??? q3:a31:a32 ??? q3 ??? a31
7 ??? p3 ??? q1:a11 ??? q1 ??? a11
8 ??? p3 ??? q2:a21 ??? q2 ??? a21
9 ??? p3 ??? q3: ??? q3 ??? Null
OK ，得到了想要的结果，说明成功了，可以看到最后一条记录，也能正确的被处理。
4、总结:
1、写法有点怪，一定不要写错；
2、不要忘记起别名，要不select没列可写。
Hive行转列(explode/lateral view)
explode称之为Hive爆炸函数，意思就是将一行数据炸开。
Usage:explode(array/map) explode函数传递的参数必须是一个array或者是map。
一、具体例子进行实践
现在有一张表有以下字段
需要将cureses转成列的形式。
二、扩展
上面可以看到表中还有一个用户ID，正常情况下肯定是用户对应课程的。正常的想法是以下的SQL
但是执行会报错
通常工作中，explode会结合laderal view使用。因为我们不可能只取explode里面的值，还要取其他的字段。
那么正常SQL应该是下面的
EXPLODE和LATERAL VIEW
或许我们想直接这样写SQL，结果出错，因为explode是UDTF，输入一行会输出多行，这个时候我们就需要用到LATERAL VIEW。
我们可以用下面的SQL语句即可得到我们想要的结果。
LATERAL VIEW的用法为：original_table LATERAL VIEW (outer) udtf(expression) tableAlias AS columnAlias，其中outer用于当udtf(expression)输出结果为null的时候保留左侧结果
SQL：LATERAL VIEW函数解析多嵌套的json
数据处理中，一遇到json就头大，很长一段时间里，明知lateral view函数是个好东西，但就是很抗拒去学，都是找数仓的同事先理好字段直接用，顺便以菜鸡的身份，同情和膜拜一下埋头洗脏数的数仓同学，大佬辛苦。。
前段时间加入到一个数据建设的项目中作为先锋军打头阵，没办法遇到json还是硬着头皮终于学会了lateral view用法，感受：困难只是心中的一座大山！也不过如此！
我肯定我过不了几天一定会忘掉(其实已经忘掉一点了。。)，没有好记性拿起烂笔头，记录在这里吧。
假设T表中有个json_txt字段取值格式如下：
如果我要得到每个学生的所有信息字段，则需要将json中的信息解析出来。
得到结果如下：
但是score和rank是以数列形式存储在同一行，不方便计算，用trans_array()函数可以解决啦：
得到的结果就是纵列的分数明细：