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