Conversion of rows and columns in Hive
1. Demonstrate converting multiple columns into a single row
Data File and content: student.txt
Xiaoming | english | 92.0
Xiaoming | chinese | 98.0
Xiaoming | math | 89.5
Huahua | chinese | 80.0
Huahua | math | 89.5
Create a table studnet:
Create table student (name string, subject string, score decimal (4, 1 ))
Row format delimited
Fields terminated by '| ';
Import data:
Load data local inpath '/home/hadoop/hivetestdata/student.txt' into table student;
Column-to-row conversion:
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. Demonstrate converting a single row into multiple columns
Data File and content: student2.txt
Huahua | chinese = 80, math = 89.5
Xiaoming | english = 92, chinese = 98, math = 89.5
Create a table:
Create table student2 (name string, subject_score_list string)
Row format delimited
Fields terminated by '| ';
Import data:
Load data local inpath '/home/hadoop/hivetestdata/student2.txt' into table student2;
Demonstration of converting rows into columns:
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; ---- aliases must not be forgotten
Huahua chinese = 80
Huahua math = 89.5
Xiaoming english = 92
Xiaoming chinese = 98
Xiaoming math = 89.5
For column and column conversions for Impala, see: http://blog.csdn.net/jiangshouzhuang/article/details/46809931
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.