在Amazon Athena中按顺序显示分区 [英] SHOW PARTITIONS with order by in Amazon Athena

问题描述

我有这个查询：

SHOW PARTITIONS tablename;

结果是：

dt=2018-01-12
dt=2018-01-20
dt=2018-05-21
dt=2018-04-07
dt=2018-01-03

这给出了每个表的分区列表。该表的分区字段是 dt ，它是一个日期列。我想查看已排序的分区。

This gives the list of partitions per table. The partition field for this table is dt which is a date column. I want to see the partitions ordered.

文档没有说明如何执行此操作：
https://docs.aws.amazon.com/athena/latest/ug/show-partitions.html

The documentation doesn't explain how to do it: https://docs.aws.amazon.com/athena/latest/ug/show-partitions.html

我尝试通过以下方式添加订单：

I tried to add order by:

SHOW PARTITIONS tablename order by dt;

但是它给出了：

AmazonAthena;状态码：400；错误代码：InvalidRequestException;

AmazonAthena; Status Code: 400; Error Code: InvalidRequestException;

推荐答案

我刚刚遇到了同样的问题，并在 information_schema 数据库。如果您的表仅包含一个分区列，请使用以下查询获取有序列表：

I just faced the same issue and found a solution in information_schema database. If your table contains only one partitioning column, use the following query to get an ordered list:

SELECT partition_value
FROM information_schema.__internal_partitions__
WHERE table_schema = '<DB_NAME>'
        AND table_name = '<TABLE_NAME>'
ORDER BY partition_value

这篇关于在Amazon Athena中按顺序显示分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！