hive行转列,列转行
实例一:来源:
https://www.cnblogs.com/kimbo/p/6208973.html
行转列 (对某列拆分,一列拆多行)
使用函数:lateral view explode(split(column, ',')) num
eg: 如表:t_row_to_column_tmp 数据如下,对tag列进行拆分
![](https://img2018.cnblogs.com/blog/1608104/201902/1608104-20190221124513529-2146767368.png)
结果:
![](https://img2018.cnblogs.com/blog/1608104/201902/1608104-20190221124526706-1574373740.png)
列转行 (根据主键,进行多行合并一列)
使用函数:concat_ws(',',collect_set(column))
说明:collect_list 不去重,collect_set 去重。 column 的数据类型要求是 string
eg:如表:t_column_to_row ,根据id,对tag_new 进行合并
![](https://img2018.cnblogs.com/blog/1608104/201902/1608104-20190221124558970-1961176264.png)
实例二:
表合并,行转列
collect_set这就用到了hive中的行转列的知识,需要用到两个内置UDF: collect_set, concat_ws,
建表:
create table user_basic_info(id string, name string);
create table user_address(name string, address string);
加载数据:
load data local inpath '/home/jthink/work/workspace/hive/row_col_tran/data1' into table user_basic_info;
load data local inpath '/home/jthink/work/workspace/hive/row_col_tran/data2' into table user_address;
执行合并:
select max(ubi.id), ubi.name, concat_ws(',', collect_set(ua.address)) as address from user_basic_info ubi join user_address ua on ubi.name=ua.name group by ubi.name;
![](https://img2018.cnblogs.com/blog/1608104/201902/1608104-20190221124838571-308903448.png)
表拆分,列转行