Convert multiple columns into one row, and convert multiple columns into one row.
A friend asked me how to convert multiple columns into one row in Impala. In fact, the built-in functions in Impala can be implemented without using custom functions.
The following is a demonstration:
-Bash-4.1 $ impala-shell
Starting Impala Shell without Kerberos authentication
Connected to cdha: 21000
Server version: impalad version 1.4.2-cdh5 RELEASE (build eac952d4ff674663ec3834778c2b981b252aec78)
Welcome to the Impala shell. Press TAB twice to see a list of available commands.
Copyright (c) 2012 Cloudera, Inc. All rights reserved.
(Shell build version: Impala Shell v1.4.2-cdh5 (eac952d) built on Tue Sep 16 19:15:40 PDT 2014)
[Cdha: 21000]>
Create table student (name string, subject string, score decimal (); ------------- create a demo table: student (I have been away from school for four years, but I still don't forget that I am a student ^_^)
Query: create table student (name string, subject string, score decimal (4, 1 ))
Returned 0 row (s) in 1.97 s
[Cdha: 21000]>
Insert into student values ('xiaoming', 'Math', 89.5); -------------- insert demo data (Xiao Ming and Hua's learning scores, they have good feelings)
Query: insert into student values ('xiaoming', 'Math', 89.5)
Inserted 1 rows in 2.56 s
[Cdha: 21000]>
Insert into student values ('xiaoming', 'inc', 92 );
Query: insert into student values ('xiaoming', 'inc', 92)
Inserted 1 rows in 0.39 s
[Cdha: 21000]>
Insert into student values ('xiaoming', 'China', 98 );
Query: insert into student values ('xiaoming', 'China', 98)
Inserted 1 rows in 0.42 s
[Cdha: 21000]>
Insert into student values ('shanghai', 'China', 80 );
Query: insert into student values ('shanghai', 'China', 80)
Inserted 1 rows in 0.40 s
[Cdha: 21000]>
Insert into student values ('shanghai', 'Math', 89.5 );
Query: insert into student values ('shanghai', 'Math', 89.5)
Inserted 1 rows in 0.29 s
[Cdha: 21000]>
Select * from student; ---------- after each test, the teacher is very nervous when reporting the score
Query: select * from student
+ ---------- + --------- + ------- +
| Name | subject | score |
+ ---------- + --------- + ------- +
| Xiaoming | english | 92.0 |
| Huahua | chinese | 80.0 |
| Xiaoming | China | 98.0 |
| Huahua | math | 89.5 |
| Xiaoming | math | 89.5 |
+ ---------- + --------- + ------- +
Returned 5 row (s) in 0.23 s
[Cdha: 21000]>
Select name, group_concat (subject, ',') from student group by name; ------------ let's see it. Splice multiple columns together.
Query: select name, group_concat (subject, ',') from student group by name
+ ---------- + ---------------------------- +
| Name | group_concat (subject, ',') |
+ ---------- + ---------------------------- +
| Xiaoming | english, chinese, math |
| Huahua | chinese, math |
+ ---------- + ---------------------------- +
Returned 2 row (s) in 0.38 s
------------ The following is a valuable example to show the tricks of experts.
------------ In this way, you can see the scores of Xiao Ming and Hua's subjects on a line.
[Cdha: 21000]>
Select name, group_concat (concat_ws ('=', subject, cast (score as string), ',') from student group by name;
Query: select name, group_concat (concat_ws ('=', subject, cast (score as string), ',') from student group by name
+ ---------- + ------------------------------------------------------------------- +
| Name | group_concat (concat_ws ('=', subject, cast (score as string), ',') |
+ ---------- + ------------------------------------------------------------------- +
|
Xiaoming | english = 92.0, chinese = 98.0, math = 89.5|
|
Huahua | chinese = 80.0, math = 89.5|
+ ---------- + ------------------------------------------------------------------- +
Returned 2 row (s) in 0.39 s
[Cdha: 21000]>
Hive column and column conversion see: http://blog.csdn.net/jiangshouzhuang/article/details/46810529
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
