Hive_SQL 一次删除多个分区数据

目录

1.删除语法

2.元数据及数据存储变化

3.示例

3.1 单个分区字段表

3.1.1 删除单个分区单个分区数据

3.1.2 删除单个分区字段多个分区数据

3.2 多个分区字段表

3.2.1 删除多个分区字段 单个分区数据

3.2.2 删除多个分区字段  单个字段  多个分区范围数据

3.2.3 删除多个分区字段  多个字段  多个分区范围数据

1.删除语法

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]

2.元数据及数据存储变化

可以使用ALTER TABLE DROP PARTITION删除表的分区。将会删除该分区的数据和元数据。如果配置了Trash，数据实际上会被移动到.Trash/Current目录，除非指定了PURGE，但是元数据会完全丢失。

3.示例

测试数据以日期为测试分区字段

3.1 单个分区字段表

测试数据准备

-- 建表语句
create table if not exists test_dt (
    col string
)
partitioned by (dt string)
;

-- 测试数据
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test_dt partition (dt)
select 'a' as col,'2021-10-01' as dt 
union all
select 'a' as col,'2021-10-02' as dt 
union all
select 'a' as col,'2021-10-03' as dt 
union all
select 'a' as col,'2021-10-04' as dt 
union all
select 'a' as col,'2021-10-05' as dt 
union all
select 'a' as col,'2021-10-06' as dt 
;

3.1.1 删除单个分区单个分区数据

alter table test_dt drop partition (dt = '2021-10-01');

3.1.2 删除单个分区字段多个分区数据

alter table test_dt drop partition (dt >='2021-10-01' ,dt <='2021-10-04');

3.2 多个分区字段表

测试数据准备

-- 建表语句
drop table if exists test_year_month_day;
create table if not exists test_year_month_day (
    col string
)
partitioned by (year int,month int  ,day  int )
;

-- 测试数据
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test_year_month_day  partition (year ,month ,day)
select 'a' as col,'2021' as year, '10' as month ,'01' as day 
union all
select 'a' as col,'2021' as year, '01' as month ,'01' as day  
union all
select 'a' as col,'2021' as year, '01' as month ,'02' as day 
union all
select 'a' as col,'2021' as year, '01' as month ,'03' as day  
union all
select 'a' as col,'2021' as year, '01' as month ,'04' as day 
union all
select 'a' as col,'2021' as year, '02' as month ,'01' as day  
union all
select 'a' as col,'2021' as year, '02' as month ,'02' as day 
union all
select 'a' as col,'2021' as year, '02' as month ,'03' as day  
union all
select 'a' as col,'2021' as year, '03' as month ,'01' as day 
union all
select 'a' as col,'2021' as year, '03' as month ,'02' as day  
union all
select 'a' as col,'2021' as year, '03' as month ,'03' as day 
;

3.2.1 删除多个分区字段 单个分区数据

多个分区字段，删除分区时要写明前级分区字段值，以防止误删数据的情况

alter table test_year_month_day drop partition (year ='2021' ,month ='10' ,day='01');

3.2.2 删除多个分区字段  单个字段  多个分区范围数据

alter table test_year_month_day drop partition (year ='2021' ,month ='01',day >='01',day <= '02');

3.2.3 删除多个分区字段  多个字段  多个分区范围数据

alter table test_year_month_day drop partition (year ='2021' ,month >='01' ,month <= '03',day >='01',day <= '02');