目录

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');

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。