hive 高级数据类型使用
用了许久的hive,但是一直都是简单的sql join,sort, order by等,今天有一个业务场景需要使用array数据类型存储数据并进行横表转纵表的转换。mark下以后用了可以查询。
数据样子是这样的。
ID | type_flag | tags |
---|---|---|
10001 | 3 | 11_20_30,11_22_34,12_23_30,13_24_36 |
10002 | 2 | 11_20,11_22,12_23,13_24 |
10003 | 1 | 11,12 |
表格1
需要转化成的样子如下:
ID | type_flag | tag1 | tag2 | tag3 |
---|---|---|---|---|
10001 | 3 | 11 | 20 | 30 |
10001 | 3 | 11 | 22 | 34 |
10001 | 3 | 12 | 23 | 30 |
10001 | 3 | 13 | 24 | 36 |
10002 | 2 | 11 | 20 | |
10002 | 2 | 11 | 22 | |
10002 | 2 | 12 | 23 | |
10002 | 2 | 13 | 24 | |
10003 | 1 | 11 | ||
10003 | 1 | 12 |
表格2
- 创建表tmp_type_tags存储表格1的数据
create table tmp.tmp_type_tags
(
id bigint ,
type_flag string,
tags array < string >
)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',' stored as textfile;
- 创建表tmp_type_tag_split存储转化中间的数据
create table tmp.tmp_type_tag_split
(
id bigint ,
type_flag string,
tag array < string >
)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY '_' stored as textfile;
- 第一步横转纵
将数据按照第一层分隔符,转化成数据size那么多的行数。sql如下
insert overwrite table tmp.tmp_type_tag_split
select
id ,
type_flag,
split(tag0, '_')
from
tmp.tmp_type_tags lateral view explode(tags) r1 as tag0
数据结果:
- 第二步按照类型进行分列
create table tmp.tmp_type_tag_split_info as
select
id ,
type_flag,
(case when type_flag in(1, 2, 3) then tag[0] else '' end) as tag1,
(case when type_flag in(2, 3) then tag[1] else '' end) as tag2,
(case when type_flag in(3) then tag[2] else '' end) as tag3
from
tmp.tmp_type_tag_split
最终表数据结果如下:转换完成