hive表中array类型数据处理（行列转换）

hive表中array类型数据处理

hive表中数据如下：

1       ["20170101","20170102","20170201","20170203"]
2       ["20170102","20170102","20170201"]
3       ["20170104"]
2       ["20170201","20170102","20170204"]

创建表：

drop table data_m.tmp_lxm_test_array;
create table data_m.tmp_lxm_test_array(
num string,
day_id array<string>
)row format delimited fields terminated by '|';

数据如下：
2|20170201^B20170102^B20170204
2|20170102^B20170102^B20170201
3|20170104
1|20170101^B20170102^B20170201^B20170203
注意：现在看到的结果是在vim下的，其中^B是隐藏字符，ctrl+v+b可以在vi中打出来

导入数据：
load data local inpath '/home/data_m/data/lxm/data/test_data.txt' overwrite into table data_m.tmp_lxm_test_array;

准备好的数据查询结果如下：
2       ["20170201","20170102","20170204"]
2       ["20170102","20170102","20170201"]
3       ["20170104"]
1       ["20170101","20170102","20170201","20170203"]

利用concat_ws()函数将array转换为字符串

select num,concat_ws(',',day_id)
from data_m.tmp_lxm_test_array;

2       20170201,20170102,20170204
2       20170102,20170102,20170201
3       20170104
1       20170101,20170102,20170201,20170203

利用lateral view explode()函数行转列

#得到最终需要的数据形式
select num,day
from data_m.tmp_lxm_test_array
lateral view explode(day_id) atable as day

2       20170201
2       20170102
2       20170204
2       20170102
2       20170102
2       20170201
3       20170104
1       20170101
1       20170102
1       20170201
1       20170203