hive列转行和行转列

1. 假设我们在hive中有两张表&＃xff0c;其中一张表是存用户基本信息&＃xff0c;另一张表是存用户的地址信息等&＃xff0c;表数据假设如下&＃xff1a;

user_basic_info:

id	name
1	a
2	b
3	c
4	d

user_address;

name	address
a	add1
a	add2
b	add3
c	add4
d	add5

我们可以看到同一个用户不止一个地址&＃xff08;这里是假设的&＃xff09;&＃xff0c;我们需要把数据变为如下格式&＃xff1a;

id	name	address
1	a	add1,add2
2	b	add3
3	c	add4
4	d	add5

collect_set
这就用到了hive中的行转列的知识&＃xff0c;需要用到两个内置UDF: collect_set, concat_ws,

两个函数解释如下见&＃xff1a;http://www.cnblogs.com/end/archive/2012/06/18/2553682.html

建表:

create table user_basic_info(id string, name string);
create table user_address(name string, address string);
加载数据&＃xff1a;

load data local inpath &＃39;/home/jthink/work/workspace/hive/row_col_tran/data1&＃39; into table user_basic_info;
load data local inpath &＃39;/home/jthink/work/workspace/hive/row_col_tran/data2&＃39; into table user_address;


执行合并&＃xff1a;

select max(ubi.id), ubi.name, concat_ws(&＃39;,&＃39;, collect_set(ua.address)) as address from user_basic_info ubi join user_address ua on ubi.name&＃61;ua.name group by ubi.name;


运行结果&＃xff1a;

1       a       add1,add2
2       b       add3
3       c       add4
4       d       add5

2. 假设我们有一张表&＃xff1a;

user_info:

id	name	address
1	a	add1,add2
2	b	add3
3	c	add4
4	d	add5

我们需要拆分address&＃xff0c;变为&＃xff1a;

id	name	address
1	a	add1
1	a	add2
2	b	add3
3	c	add4
4	d	add5

我们很容易想到用UDTF&＃xff0c;explode()&＃xff1a;

select explode(address) as address from user_info;

这样执行的结果只有address&＃xff0c; 但是我们需要完整的信息&＃xff1a;

select id, name, explode(address) as address from user_info;

这样做是不对的&＃xff0c; UDTF&＃39;s are not supported outside the SELECT clause, nor nested in expressions

所以我们需要这样做&＃xff1a;

select id, name, add from user_info ui lateral view explode(ui.address) adtable as add;

结果为&＃xff1a;

1       a       add1
1       a       add2
2       b       add3
3       c       add4
4       d       add5