Question:
I want to check the partition lists in Athena.
I used query like this.
|
1 2 |
show partitions table_name |
But I want to search specific table existed.
So I used query like below but there was no results returned.
|
1 2 |
show partitions table_name partition(dt='2010-03-03') |
Because dt contains hour data also.
|
1 2 |
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?
|
1 2 |
dt='2010-03-03', dh='01' |
And show partitions table_name returned only 500 rows in Hive. Is the same in Athena also?
Answer:
In Athena v2:
Use this SQL:
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 |
SELECT partition_value FROM information_schema.__internal_partitions__ WHERE table_schema = ' AND table_name = ' AND partition_value LIKE '2010-03-03-%' |