CODEWITHSUNDEEP
amazon-web-servicesamazon-s3aws-glueamazon-athena
Elliveny
Elliveny

Reputation: 2113

How do I execute the SHOW PARTITIONS command on an Athena table?

I'm using AWS Athena with AWS Glue for the first time, with S3 providing a 'folder' structure which maps to the partitions in my data - I'm getting into the concepts so please excuse any mistaken description!

I'm looking at what happens when I add data to my S3 bucket and see that new folders are ignored. Digging deeper I came across the 'SHOW PARTITIONS' command, as described here https://docs.aws.amazon.com/athena/latest/ug/show-partitions.html, and I'm trying to execute this against my test tables using the Athena query editor, with a mind that I'll go onto use the 'ALTER TABLE ADD PARTITION' command to add the new S3 folders.

I'm trying to execute the 'SHOW PARTITIONS' command in the AWS Athena Console Query Editor:

SHOW PARTITIONS "froms3_customer-files"."unit"

but when I try to execute it I see this message:

line 1:17: missing {'from', 'in'} at '"froms3_customer-files"' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: c0c0c351-2d42-4da4-b1f3-223b1733db65)

I'm struggling to understand what this is telling me, can anyone help me here?

Upvotes: 1

Views: 2447

Answers (2)

Ravi Joshi
Ravi Joshi

Reputation: 661

If you want to see all the partitions that are created till now you can use following command 

SHOW PARTITIONS DB_NAME.TABLE_NAME

If you want to view the keys along which table is partitioned you can view it through UI in following way:

1. Click on the table menu options.

enter image description here

2. Click on Show Properties

enter image description here

3. Click on partitions to see partition.

Upvotes: 0

Harsh Bafna
Harsh Bafna

Reputation: 2224

Athena does not supports hyphen in database name.

Athena table, view, database, and column names cannot contain special characters, other than underscore (_).

Also remove the double quotes from the show partitions command.

SHOW PARTITIONS froms3_customer_files.unit

References :

Athena table and database naming convention

Athena show partitions

Upvotes: 1

Related Questions