准备工作:
- Hive建表:
CREATE TABLE IF NOT EXISTS A(
year STRING,
m1 STRING,
m2 STRING,
m3 STRING,
m4 STRING
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t'
STORED AS textfile;
- 插入数据:
INSERT INTO TABLE A VALUES('1991','1.1','1.2','1.3','1.4'),('1992','2.1','2.2','2.3','2.4');
需求:AB表互转
A表:
year | m1 | m2 | m3 | m4 |
---|---|---|---|---|
1991 | 1.1 | 1.2 | 1.3 | 1.4 |
1992 | 2.1 | 2.2 | 2.3 | 2.4 |
B表:
year | amount | month |
---|---|---|
1991 | 1.1 | 1 |
1991 | 1.2 | 2 |
1991 | 1.3 | 3 |
1991 | 1.4 | 4 |
1992 | 2.1 | 1 |
1992 | 2.2 | 2 |
1992 | 2.3 | 3 |
1992 | 2.4 | 4 |
A转B:
select year, amount,
row_number() over(partition by year) month
from A
lateral view explode(split(concat_ws(',',m1,m2,m3,m4),','))tmp as amount
B转A:
select year, amountList[0] m1,amountList[1] m2,amountList[2] m3
from(
select year,
collect_list(amount) amountList
from B
group by year
)tmp3;
为了方便测试,以下sql先A转B再转A:
select year, amountList[0] m1,amountList[1] m2,amountList[2] m3
from(
select year,
collect_list(amount) amountList
from(
select year,amount,
row_number() over(partition by year) month
from A
lateral view explode(split(concat_ws(',',m1,m2,m3,m4),','))tmp as amount
)tmp2
group by year
)tmp3;