蜂巢删除所有分区保留最近4天的分区 [英] hive drop all partitions keep recent 4 days paritions

问题描述

我有一个分区如下的表:
TABLE logs PARTITION(year = 2019, month = 06, day = 18)

I have a table with partitions like below :
TABLE logs PARTITION(year = 2019, month = 06, day = 18)

分区'year'，'month'和'day'均为字符串格式.

partitions 'year', 'month' and 'day' are in string format.

我需要删除分区，保留最近7天的分区. 并且需要每周运行一次作业，以便日志表在每周开始时会有7天的日志.

I need to drop partitions keeping last seven days partitions. and need to run the job every week so that, logs tables will have 7 days logs at the start of every week.

推荐答案

您可以在分区规范中使用< =运算符.

You can use <= operator in partition specification.

演示:

use mydb;
drop table test_partition_drop;
CREATE TABLE test_partition_drop
(col1 STRING)
PARTITIONED BY (part_year string, part_month string, part_day string);
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day='09') VALUES ('01');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day='10') VALUES ('01');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day=11) VALUES ('02');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day=12) VALUES ('03');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day=13) VALUES ('05');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day=14) VALUES ('06');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='06', part_day=15) VALUES ('06');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2018', part_month='06', part_day=14) VALUES ('01');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2018', part_month='06', part_day=15) VALUES ('02');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='05', part_day=14) VALUES ('03');
INSERT INTO TABLE  test_partition_drop PARTITION (part_year='2019', part_month='04', part_day=15) VALUES ('04');

计算要删除的最小分区键，然后传递给您的DROP PARTITION脚本:

Calculate min partition keys to be dropped and pass to your DROP PARTITION script:

var_year="$(date -d "7 days ago" +"%Y")"
var_month="$(date -d "7 days ago" +"%m")"
var_day="$(date -d "7 days ago" +"%d")"

hive -e "
use mydb;
ALTER TABLE test_partition_drop DROP IF EXISTS PARTITION (part_year<'${var_year}');
ALTER TABLE test_partition_drop DROP IF EXISTS PARTITION (part_year='${var_year}', part_month<'${var_month}'); 
ALTER TABLE test_partition_drop DROP IF EXISTS PARTITION (part_year='${var_year}', part_month<='${var_month}', part_day<='${var_day}');
"

结果:

OK
Time taken: 0.762 seconds
Dropped the partition part_year=2018/part_month=06/part_day=14
Dropped the partition part_year=2018/part_month=06/part_day=15
OK
Time taken: 1.643 seconds
Dropped the partition part_year=2019/part_month=04/part_day=15
Dropped the partition part_year=2019/part_month=05/part_day=14
OK
Time taken: 1.0 seconds
Dropped the partition part_year=2019/part_month=06/part_day=09
Dropped the partition part_year=2019/part_month=06/part_day=10
Dropped the partition part_year=2019/part_month=06/part_day=11
Dropped the partition part_year=2019/part_month=06/part_day=12
Dropped the partition part_year=2019/part_month=06/part_day=13
Dropped the partition part_year=2019/part_month=06/part_day=14
OK
Time taken: 2.097 seconds

这篇关于蜂巢删除所有分区保留最近4天的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！