【hive】- 使用insert into insert overwrite插入数据到静态分区、动态分区、动静态分区_hive insert into_hive中分区表数据的更新insert overwrite

scores int,
city_code string,
cur_day string)
row format delimited fields terminated by ‘,’;

### 静态分区插入数据


* insert into，在city\_code=‘200’,cur\_day='20231111’分区下追加数据。



1
2
3
4
5
6
7
8

insert into table test_1
partition(city_code=‘200’,cur_day=‘20231111’)
select
t.id,
t.name,
t.classes,
t.scores
from test_1_tmp t where city_code=‘200’
;

* insert overwrite，删除在city\_code=‘200’,cur\_day='20231111’分区下数据，重新写入数据。



1
2
3
4
5

insert overwrite table test_1
partition(city_code=‘200’,cur_day=‘20231111’)
select
t.id,
t.name,
t.classes,
t.scores
from test_1_tmp t where city_code=‘200’
;

### 动态分区插入数据


* insert into / insert overwrite



1
2
3
4
5
6
7
8

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table test_1
partition(city_code,cur_day)
select
t.id,
t.name,
t.classes,
t.scores,
t.city_code,
t.cur_day
from test_1_tmp t
;

1
2

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test_1
p