hive行转列、列转行——头歌_头歌第1关:行转列、列转行

任务描述

本关任务：对表格进行行转列、列转行的操作。

相关知识

为了完成本关任务，你需要掌握：1.行列转换的应用场景，2.如何行转列，3，如何列转行。

行列转换的应用场景

在 Hive 实际开发过程中，我们会遇到“行转列“和“列转行”的场景。比如下面一个例子： 1、 需要取“订单号”对应的所有商品“sku 号”，商品“sku 号”放在一列，即从 table1 查询出 table2； 2、 当商品“sku 号”均在一列的时候，需要查询每个“sku 号”对应的“订单号”，即从 table2 查询出 table1。

表 1 示例表

工具函数

可以执行desc function explode;查看相关函数说明；

1. explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns
2. 可以将数组炸开成多行，或者将map炸开成多行多列，是Hive内置的UDTF
4. split(str, regex) - Splits str around occurances that match regex
5. 按照正则规则去切割字符串
7. collect_list(x) - Returns a list of objects with duplicates
8. 返回不去重的集合
10. collect_set(x) - Returns a set of objects with duplicate elements eliminated
11. 返回一个去重的集合
13. concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator
14. 返回一个特定分隔符的拼接字符串
16. max(expr) - Returns the maximum value of expr
17. 返回表达式的最大值

行转列语法

Hive 行转列的示例语法如下:

select col1,concat_ws(连接符,collect_set(被转字段名)) as 列别名 from 表名 group by col1;

继续使用上面的例子，想要行转列则使用语句：

1. select
2. order_id,concat_ws(',',collect_set(item_sku_id))as item_sku_id
3. from table1
4. group by order_id

列转行语法

Hive 列转行的示例语法如下:

虚拟表 lateral view explode(split(tag,分隔符)) 表别名 as 列别名

继续使用上面的例子，想要列转行则使用语句：

1. SELECT
2. order_id,sku_id
3. FROM table2
4. lateral view explode(split(item_sku_id,',')) adTable as sku_id

编程要求

现有表 test1 的数据如下,字段名分别为 col1,col2,col3,字段之间用空格" "分隔：

a b 1 a b 2 a b 3 c d 4 c d 5 c d 6

test2 的数据如下，字段名分别为 col1,col2,col3，字段之间用空格" "分隔，col3 中多个数据之间用逗号","分隔：

a b 1,3,5 c d 2,4,6

在右侧编辑器补充代码,实现 test1 的行转列操作，test2 的列转行操作。

测试说明

平台已经建好需要的表，你只需要完善Hive SQL语句，平台会对你编写的代码进行测试。

代码如下 

-- BEGIN 
select col1,col2,concat_ws(',',collect_set(col3)) from test1 group by col1,col2;
select col1,col2,test.col4 from test2 lateral view explode(split(col3,',')) test as col4;
-- END