SHOW PARTITIONS with order by in Amazon Athena

amazon-athena
jack picture jack · Jun 7, 2018 · Viewed 7.5k times · Source

I have this query:

SHOW PARTITIONS tablename;

Result is:

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

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.

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;

But it gives:

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

Answer

Mariusz picture Mariusz · Jan 2, 2019

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

Related questions

Amazon Athena: no viable alternative at input

While creating a table in Athena; it gives me following exception: no viable alternative at input

Read More
Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More