【博学谷学习记录】超强总结，用心分享|Hive表生成函数

explode函数

explode(列): 将Hive表的某一列复杂类型的array或者map结构拆分成多行
explode(array): 数组中的每个元素生成一行
explode(map): map中每个键值对生成一行,key一列,value一列
注意: struct类不支持

Array类型数据演示

演示数据

zhangsan beijing,shanghai,tianjin,hangzhou
lisi xiamen,fuzhou,jiangxi,nanchang
wangwu changchun,chengdu,wuhan,beijing

创建一张包含array类型的表

create table work_locations(
  name string,
  citys array<string>
)
	row format delimited
	fields terminated by ' '
	collection items terminated by ',';

插入表数据

load data local inpath '/export/testdatas/emp.txt' into table emp;

查询验证

select * from emp;

使用explode查询

select explode (names) as name from emp;

Map 类型数据演示

演示数据

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

创建一张包含map类型的表

create table family (
    id      int,
    name    string,
    members map<string,string>,
    age     int
)
	row format delimited
	fields terminated by ','
	collection items terminated by '#'
	map keys terminated by ':';
	

加载表数据

load data local inpath '/export/testdatas/map.txt' into table family;

查询验证

select * from family;

使用explode查询

select explode(members) from family;