How can I check the partition list from Athena in AWS?

Question

I want to check the partition lists in Athena.

I used query like this.

show partitions table_name

But I want to search specific table existed.

So I used query like below but there was no results returned.

show partitions table_name partition(dt='2010-03-03')

Because dt contains hour data also.

dt='2010-03-03-01', dt='2010-03-03-02', ...........

So is there any way to search when I input '2010-03-03' then it search '2010-03-03-01', '2010-03-03-02'?

Do I have to separate partition like this?

dt='2010-03-03', dh='01'

And show partitions table_name returned only 500 rows in Hive. Is the same in Athena also?

Answer 1

In Athena v2:

Use this SQL:

SELECT dt
FROM db_name."table_name$partitions"
WHERE dt LIKE '2010-03-03-%'

(see the official aws docs)

---

In Athena v1:

There is a way to return the partition list as a resultset, so this can be filtered using LIKE. But you need to use the internal information_schema database like this:

SELECT partition_value
FROM information_schema.__internal_partitions__
WHERE table_schema = '<DB_NAME>'
        AND table_name = '<TABLE_NAME>'
        AND partition_value LIKE '2010-03-03-%'