collect_all() hive 0.12
collect_list() hive 0.13
**
一、问题
**
hive如何将
a b1
a b2
a b2
c d1
c d1
d d2
变为:
a [“b1”,“b2”,“b2”]
c [“d1”,“d1”]
d [“d2”]
二、数据
test.txt (空格分隔)
a b1
a b2
a b2
c d1
c d1
d d2
三、答案
- 1.建表
drop table tmp_jzl_20151027_test;
create table tmp_jzl_20151027_test
(
col1 string,
col2 string
)
row format delimited
fields terminated by ' '
stored as textfile;
-
2.导入数据
load data local inpath '/home/jiangzl/shell/test.txt' into table tmp_jzl_20151027_test;
-
3.计算
-
不去重
hive>
> select col1, collect_all(col2))
> from tmp_jzl_20151027_test
> group by col1;
a ["b1","b2","b2"]
c ["d1","d1"]
d ["d2"]
- 去重
hive>
> select col1, collect_set(col2))
> from tmp_jzl_20151027_test
> group by col1;
a ["b1","b2"]
c ["d1"]
d ["d2"]