一、列转行
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 |
user_info
id | name | address |
1 | a | add1,add2 |
2 | b | add3 |
3 | c | add4 |
4 | d | add5 |
建表语句:
create table user_basic_info(
id string,
name string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ; --指定每一行中的字段用\t 做分隔符,
create table user_address(name string, address string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ; --指定每一行中的字段用\t 做分隔符,
create table user_info(
id string,
name string ,
address array<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',' ;--集合按照指定分割符号分割
导入数据:
load data local inpath '/usr/local/hive/user_basic_info_file' into table user_basic_info;
load data local inpath '/usr/local/hive/user_address_file' into table user_address;
列转行,并将数据插入到表 user_info中
insert overwrite table user_info
select max(ubi.id), ubi.name, collect_set(ua.address) as address from user_basic_info ubi
join user_address ua on ubi.name=ua.name group by ubi.name;
二、行转列
创建新表 user_info2
create table user_info2(
id string,
name string,
address string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
行转列语句:
insert into user_info2 select id, name, add
from user_info ui lateral view explode(ui.address) adtable as add;