炸裂函数explode

 

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

lateral viem 表的关联只能和explode使用

hive> select explode(info) from t_map where id=1;

hive> create table watch(id int,name string) row format delimited fields terminated by " ";

 vim movie.txt

1 CN_lldd
2 CN_sjd
3 CN_jp
4 CN_rpt
5 USA_yns
6 USA_sd
7 USA_ydfdf
8 USA_gdffd

load data local inpath "/movie.txt" into table watch;

//创建数据文件

1 lldq js|kh|dz|yq
2 sdyjq dz|js|js
3 yw ll|yq|dz

//上传数据

load data local inpath "/mv.txt" into table mv;

//将数据炸开

hive> select explode(split(types,"\\|")) from mv;

 

//求有几种类型

hive> select count(*) from (select explode(split(types,"\\|"))type from mv) t group by t.type;

hive> select count(*),type from (select explode(split(types,"\\|"))type from mv) t group by t.type;

//将炸裂的数据和 表关联 lateral view

hive> select id,name,type from mv lateral view explode(split(types,"\\|")) t as type 

 

 

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄