hive 高级数据类型使用之array（含横表转纵表）

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;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

• 创建表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;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

• 第一步横转纵 
  将数据按照第一层分隔符，转化成数据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

• 1
• 2
• 3
• 4
• 5
• 6
• 7

数据结果： 

• 第二步按照类型进行分列

 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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

最终表数据结果如下：转换完成 

来自： https://blog.csdn.net/dreamingfish2011/article/details/51250641