【博学谷学习记录】超强总结，用心分享|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;

later view侧视图关键字

和explode一起使用,可以把一列数据拆成多行数据,并且可以对拆分后的数据进行聚合

演示数据

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

创建表并加载数据

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


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

查询验证

select * from hive_array;

使用lateral view查询

select name,locations,loca from hive_array lateral view explode(locations) emp as loca;

reflect函数

演示数据

1,2
4,3
6,4
7,5
5,6

创建表并加载数据

create table reflect(
    col1 int,
    col2 int
)
    row format delimited
        fields terminated by ',';


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

查询验证

select * from reflect;

使用reflect查询

select reflect("java.lang.Math","max",col1,col2) as max from reflect;