hive复杂数据类型使用

array

建表

--创建基础表
create table stu_sub(id integer, subject string) 
row format delimited fields terminated by '\t';
--创建集合表
create table stu_subs(id integer, subjects array<string>) 
row format delimited fields terminated by '\t' 
collection items terminated by '|';

插入数据

--往基础表插入两份一样的数据，为后续使用
insert into table stu_sub values(1001, 'java'),(1001, 'c++'),(1001,'python'),(1002,'c'),(1002,'html');
insert into table stu_sub values(1001, 'java'),(1001, 'c++'),(1001,'python'),(1002,'c'),(1002,'html');

第一种，collect_list()，集合不去重

insert into table stu_subs
select id, collect_list(subject) subjects from stu_sub group by id;

--查看数据：
id  |subjects                                     |
----+---------------------------------------------+
1001|["java","c++","python","java","c++","python"]|
1002|["c","html","c","html"]                      |

第二种，collect_set()，集合去重

--刚才插入过了，这里覆盖掉
insert overwrite table stu_subs
select id, collect_set(subject) subjects