A friend of mine once consulted me on how to implement a multi-column into a row in Impala, in fact, the self-contained function in Impala can be implemented without custom functions.
Let me start with a demo:
-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) Cloudera, Inc. All rights reserved.
(Shell build Version:impala shell V1.4.2-cdh5 (eac952d) built on Tue Sep 19:15:40 PDT 2014)
[cdha:21000] >
CREATE table Student (name String,subject string,score decimal (4,1)); -------------Create a demo table: Student (four years away from school, not forgetting that he is a student ^_^)
Query:create table Student (name String,subject string,score decimal (4,1))
Returned 0 row (s) in 1.97s
[cdha:21000] >
Insert into student values (' xiaoming ', ' math ', 89.5)----------------Insert presentation data (Xiao Ming and Flower's study scores, their feelings are better)
Query:insert into student values (' xiaoming ', ' math ', 89.5)
Inserted 1 rows in 2.56s
[cdha:21000] >
INSERT into student values (' xiaoming ', ' 中文版 ', the ");
Query:insert into student values (' xiaoming ', ' 中文版 ', 92)
Inserted 1 rows in 0.39s
[cdha:21000] >
INSERT into student values (' xiaoming ', ' Chinese ', 98);
Query:insert into student values (' xiaoming ', ' Chinese ', 98)
Inserted 1 rows in 0.42s
[cdha:21000] >
INSERT into student values (' Huahua ', ' Chinese ', +);
Query:insert into student values (' Huahua ', ' Chinese ', 80)
Inserted 1 rows in 0.40s
[cdha:21000] >
INSERT into student values (' Huahua ', ' math ', 89.5);
Query:insert into student values (' Huahua ', ' math ', 89.5)
Inserted 1 rows in 0.29s
[cdha:21000] >
select * from student; ----------every test, the teacher reported the score is very nervous
Query:select * FROM Student
+----------+---------+-------+
| name | Subject | Score |
+----------+---------+-------+
| Xiaoming | 中文版 | 92.0 |
| Huahua | Chinese | 80.0 |
| Xiaoming | Chinese | 98.0 |
| Huahua | Math | 89.5 |
| Xiaoming | Math | 89.5 |
+----------+---------+-------+
Returned 5 row (s) in 0.23s
[cdha:21000] >
Select Name,group_concat (Subject, ', ') from the student group by name; ------------Small try Sledgehammer, see, Multi-column stitching 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.38s
------------below to demonstrate a valuable example, show the master of the trick, hehe
------------This can be on a line, see Xiao Ming and flowers of the various subjects of the results
[cdha:21000] >
Select Name,group_concat (concat_ws (' = ', Subject,cast (score as String)), ', ') from the 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.39s
[cdha:21000] >
Row and column conversions for hive see: http://blog.csdn.net/jiangshouzhuang/article/details/46810529
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Multi-column to row in Impala
