Hive中行列轉換，Hive行列轉換

Hive中行列轉換，Hive行列轉換
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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。