hive列转行 （collect_all()/collect_list() 不去重）、（collect_set去重）

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"]