1.hive的行列转换

1、建表

[sql] view plain copy

1. create table if not exists temp.lateral_test  
2. (id    string,    
3.  value string  
4. )    
5. ROW format delimited FIELDS TERMINATED BY ',' ;  

2、插入数据

[sql] view plain copy

1. load date local inpath 'latearl.txt' overwrite into table temp.lateral_test;  
2.   
3. select * from lateral_test;  

数据如下：

3.1、collect_set：去重、变数组(列传行）

[sql] view plain copy

1. select id,collect_set(value) as a from temp.lateral_test group by id  

 

3.2、数组前加序号访问对应元素，从0开始

[sql] view plain copy

1. select id,  
2.         a[0] a0,  
3.         a[1] a1   
4. from   
5.     (select id,collect_set(value) as a from temp.lateral_test group by id) b  

4、利用lateral view  explode 对3.1的数据实现行转列（k、hh别名不可少）

[sql] view plain copy

1. select id,  
2.         hh  
3. from   
4.     (select id,collect_set(value)as a from temp.lateral_test group by id)t  
5. lateral view explode(a)k as hh  

PS:explode 可以把单行数组类型数据转为列形式：

[sql] view plain copy

1. select explode(split(concat_ws(',','1','2','3','4'),','))  

---------------------------------------------------------------------------

**

Pivot using Hivemall to_map function.

**

SELECT
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
  SELECT uid, to_map(key, value) kv
  FROM vtable
  GROUP BY uid
) t

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

uid c1 c2 c3
101 11 12 13
102 21 22 23

Unpivot

SELECT t1.uid, t2.key, t2.value
FROM htable t1
LATERAL VIEW explode (map(
  'c1', c1,
  'c2', c2,
  'c3', c3
)) t2 as key, value

• 1
• 2
• 3
• 4
• 5
• 6
• 7

uid key value
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23

----------------------------------------------------------------------------------------------------------

1、演示多列转为单行

数据文件及内容： student.txt
xiaoming|english|92.0
xiaoming|chinese|98.0
xiaoming|math|89.5
huahua|chinese|80.0
huahua|math|89.5

创建表studnet：
create table student(name string,subject string,score decimal(4,1))
row format delimited
fields terminated by '|';

导入数据：
load data local inpath '/home/hadoop/hivetestdata/student.txt' into table student;


列转为行演示：
hive (hive)> select name,concat_ws(',',collect_set(subject)) from student group by name;
huahua    chinese,math
xiaoming english,chinese,math


hive (hive)> select name,concat_ws(',',collect_set(concat(subject,'=',score))) from student group by name;
huahua chinese=80,math=89.5
xiaoming english=92,chinese=98,math=89.5


2、演示单行转为多列
数据文件及内容：student2.txt
huahua|chinese=80,math=89.5
xiaoming|english=92,chinese=98,math=89.5

创建表：
create table student2(name string,subject_score_list string)
row format delimited

fields terminated by '|';

导入数据：
load data local inpath '/home/hadoop/hivetestdata/student2.txt' into table student2;

行转为列演示：
hive (hive)> select * from student2;
student2.name student2.subject_score_list
huahua             chinese=80,math=89.5
xiaoming          english=92,chinese=98,math=89.5


hive (hive)> select name, subject_list from student2 stu2 lateral view explode(split(stu2.subject_score_list,','))stu_subj as subject_list; ----别名一定不要忘记
huahua    chinese=80
huahua    math=89.5
xiaoming english=92
xiaoming chinese=98
xiaoming math=89.5


Impala的行列转换请查看： http://blog.csdn.net/jiangshouzhuang/article/details/46809931