AWS 目前(截至Nov 2020 https://aws.amazon.com/about-aws/whats-new/2020/11/amazon-athena-announces-availability-of-engine-version-2/)支持两个版本的 Athena 引擎。如何选择和排序分区取决于所使用的版本。
版本1:
Use the information_schema
桌子。假设你有year
, month
作为分区(使用一个分区键,这当然更简单):
WITH
a as (
SELECT partition_number as pn, partition_key as key, partition_value as val
FROM information_schema.__internal_partitions__
WHERE table_schema = 'my_database'
AND table_name = 'my_table'
)
SELECT
year, month
FROM (
SELECT val as year, pn FROM a WHERE key = 'year'
) y
JOIN (
SELECT val as month, pn FROM a WHERE key = 'month'
) m ON m.pn = y.pn
ORDER BY year, month
其输出:
year month
0 2018 10
0 2018 11
0 2018 12
0 2019 01
...
版本2:
使用内置的$partitions
功能,其中分区显式用作列,并且语法更简单:
SELECT year, month FROM my_database."my_table$partitions" ORDER BY year, month
year month
0 2018 10
0 2018 11
0 2018 12
0 2019 01
...
有关更多信息,请参阅:
https://docs.aws.amazon.com/athena/latest/ug/querying-glue-catalog.html#querying-glue-catalog-listing-partitions https://docs.aws.amazon.com/athena/latest/ug/querying-glue-catalog.html#querying-glue-catalog-listing-partitions