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;
运行结果:
1 a add1,add2
2 b add3
3 c add4
4 d add5
2. 假设我们有一张表:
user_info:
id
name
address
1
a
add1,add2
2
b
add3
3
c
add4
4
d
add5
我们需要拆分address,变为:
id
name
address
1
a
add1
1
a
add2
2
b
add3
3
c
add4
4
d
add5
我们很容易想到用UDTF,explode():
select explode(address) as address from user_info;
这样执行的结果只有address, 但是我们需要完整的信息:
select id, name, explode(address) as address from user_info;
这样做是不对的,UDTF's are not supported outside the SELECT clause, nor nested in expressions
所以我们需要这样做:
select id, name, add from user_info ui lateral view explode(ui.address) adtable as add;