AWS Athena：删除日期范围之间的分区 [英] AWS Athena: Delete partitions between date range

问题描述

我有一个基于日期的分区的雅典娜表，如下所示：

I have an athena table with partition based on date like this:

20190218

我想删除去年创建的所有分区。

I want to delete all the partitions that are created last year.

我尝试过

ALTER TABLE tblname DROP PARTITION (partition1 < '20181231');

ALTER TABLE tblname DROP PARTITION (partition1 > '20181010'), Partition (partition1 < '20181231');

推荐答案

根据 https://docs.aws.amazon.com/athena/latest/ug/alter -table-drop-partition.html ， ALTER TABLE tblname DROP PARTITION 采用分区规范，因此不允许使用范围。

According to https://docs.aws.amazon.com/athena/latest/ug/alter-table-drop-partition.html, ALTER TABLE tblname DROP PARTITION takes a partition spec, so no ranges are allowed.

在Presto中，您可以从tblname WHERE中删除 ... ，但不删除

In Presto you would do DELETE FROM tblname WHERE ..., but DELETE is not supported by Athena either.

由于这些原因，您需要利用一些外部解决方案。

For these reasons, you need to do leverage some external solution.

例如：

1. 列出文件，如 https： //stackoverflow.com/a/48824373/65458


2. 删除文件并包含目录


3. 更新分区信息（ https://docs.aws。 amazon.com/athena/latest/ug/msck-repair-table.html 应该会有所帮助）

1. list the files as in https://stackoverflow.com/a/48824373/65458
2. delete the files and containing directories
3. update partitions information (https://docs.aws.amazon.com/athena/latest/ug/msck-repair-table.html should be helpful)

这篇关于AWS Athena：删除日期范围之间的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！