hive 列转行 和 行转列

案例分析一：

列转行

测试数据的格式如下：

hive> select * from col_lie limit 10;
OK
col_lie.user_id    col_lie.order_id
104399            1715131
104399            2105395
104399            1758844
104399            981085
104399            2444143
104399            1458638
104399            968412
104400            1609001
104400            2986088
104400            1795054

把相同user_id的order_id按照逗号转为一行

select user_id,
concat_ws(',',collect_list(order_id)) as order_value 
from col_lie
group by user_id
limit 10;

//结果(简写)
user_id    order_value
104399    1715131,2105395,1758844,981085,2444143

总结

使用函数：concat_ws(',',collect_set(column))  

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是string

案例分析二：

行转列

测试数据格式如下：

hive> select * from lie_col;
OK
lie_col.user_id    lie_col.order_value
104408    2909888,2662805,2922438,674972,2877863,190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406    1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405    153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404    1815641,108556,3110738,2536910,1977293,424564
104403    253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402    3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401    814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400    1609001,2986088,1795054,429550,1812893
104399    1715131,2105395,1758844,981085,2444143,1458638,968412
Time taken: 0.065 seconds, Fetched: 10 row(s)

将order_value的每条记录切割为单元素

select user_id,order_value,order_id from lie_col
lateral view explode(split(order_value,',')) num as order_id
limit 10;

//结果
user_id    order_value                                                                          order_id
104408    2909888,2662805,2922438,674972,2877863,190237                                         2909888
104408    2909888,2662805,2922438,674972,2877863,190237                                         2662805
104408    2909888,2662805,2922438,674972,2877863,190237                                         2922438
104408    2909888,2662805,2922438,674972,2877863,190237                                         674972
104408    2909888,2662805,2922438,674972,2877863,190237                                         2877863
104408    2909888,2662805,2922438,674972,2877863,190237                                         190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   2982655
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   814964
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   1484250
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128   2323912
Time taken: 0.096 seconds, Fetched: 10 row(s)

mysql跟hive列转行/行转列一样,但是多行转一行,一行转多行就不太一样了

启动hive时 ./hive -S (去除MR打印日志)
hive命令行 set hive.exec.mode.local.auto=true; 设置本地模式
数据准备:

create table student_score(s_id int,s_name string,s_sub string,s_score bigint);
insert into student_score values
(1,'张三','数学',90),
(2,'张三','语文',85),
(3,'张三','英语',92),
(4,'李四','数学',88