换表加分区字段的步骤

0.统计总数,分区数,保存建表语句

select count(1) as num from table1
show partitions table1
show create table table1

1.复制原有结构,新建临时表

CREATE TABLE table2 like table1

2.复制原有表数据

SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE table2 partition(x)
select * from table1

3.删除原有表

drop table table1

4.新建新分区表 加上需要的分区字段

CREATE TABLE table1

5.插入临时表的数据到新建分区表

SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode = 100000;
INSERT OVERWRITE table1 partition(x,xx)
select * from table2(有些要抽字段顺序)

6.统计总数,分区数 是否正常

select count(1) as num from table1
show partitions table1

7.删除临时表

drop table table2

s8fh26h3
18 声望1 粉丝