赞
踩
Hive中支持的分区类型有两种,静态分区(static partition)与动态分区(dynamic partition),本文主要讲针对不同分区情况,如何正确地使用insert into/insert overwrite 将数据插入表里对应的分区。
建立分区表
create table test_1
(id string,
name string,
classes string,
scores int)
partitioned by (city_code string,cur_day string)
row format delimited fields terminated by ',';
辅助分区表的测试表
create table test_1_tmp(
id string,
name string,
classes string,
scores int,
city_code string,
cur_day string)
row format delimited fields terminated by ',';
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 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'
;
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
;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite 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
;
insert into table 插入动态分区,如果被插入表原来就有该分区,则追加插入数据;否则就正常插入数据。
insert overwrite table 插入动态分区,如果被插入表原来就有该分区,删除原来该分区下数据,重新插入;否则就正常插入数据。
举例:
未将test_2_tmp数据插入到test_1时,test_1表的数据情况:
select * from test_1;
表test_2_tmp的情况:
desc test_2_tmp;
+------------+------------+----------+
| col_name | data_type | comment |
+------------+------------+----------+
| id | string | |
| name | string | |
| classes | string | |
| scores | int | |
| city_code | string | |
| cur_day | string | |
+------------+------------+----------+
select * from test_2_tmp;
用insert overwrite table重写写入(city_code='200’与cur_day='20231111’分区)和(city_code='763’与cur_day='20231112’分区),如下:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite 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_2_tmp t
;
select * from test_1;
注意:
1、hive支持全动态分区,但是在使用前必须设置以下参数:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
2、字段和目标表动态分区的对应关系是由字段顺序决定,并不是由列名称决定的。
3、向动态分区插入数据时,动态分区必须在select列表中,否则会执行失败。
insert into table test_1
partition(city_code,cur_day)
select
t.id,
t.name,
t.classes,
t.scores
from test_1_tmp t
;
报错:
Error: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into target table because column number/types are different 'cur_day': Table insclause-0 has 6 columns, but query has 4 columns. (state=42000,code=10044)
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=strict;
insert into table test_1
partition(city_code='763',cur_day)
select
t.id,
t.name,
t.classes,
t.scores,
t.cur_day
from test_1_tmp t
;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=strict;
insert overwrite table test_1
partition(city_code='763',cur_day)
select
t.id,
t.name,
t.classes,
t.scores,
t.cur_day
from test_1_tmp t
;
insert into table 插入动静混合分区,如果被插入表原来就有该分区,则追加插入数据;否则就正常插入数据。
insert overwrite table 插入动静混合分区,如果被插入表原来就有该分区,删除原来该分区下数据,重新插入;否则就正常插入数据。
举例:
未将test_2_tmp数据插入到test_1时,test_1表的数据情况:
select * from test_1;
表test_2_tmp的情况:
select * from test_2_tmp;
用insert into追加插入(city_code=‘763’,cur_day)分区的情况,如下:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=strict;
insert into table test_1
partition(city_code='763',cur_day)
select
t.id,
t.name,
t.classes,
t.scores,
t.cur_day
from test_2_tmp t
;
用insert overwrite重写(city_code=‘763’,cur_day)分区的情况,如下:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=strict;
insert overwrite table test_1
partition(city_code='763',cur_day)
select
t.id,
t.name,
t.classes,
t.scores,
t.cur_day
from test_2_tmp t
;
注意: 动静态分区混合插入,静态分区要在动态分区前面。
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=strict;
insert into table test_1
partition(cur_day,city_code='763')
select
t.id,
t.name,
t.classes,
t.scores,
t.cur_day
from test_1_tmp t
;
报错:
Error: Error while compiling statement: FAILED: ValidationFailureSemanticException summary_fz_province.test_1: Partition spec {city=763, cur_day=null} contains non-partition columns (state=42000,code=40000)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。