CODEWITHSUNDEEP
sql-workbench-jamazon-athena
Reece Markowsky
Reece Markowsky

Reputation: 123

Amazon Athena ALTER TABLE ADD PARTITION query not working

I am trying to use SQLWorkBenchJ to add partition to my table in Amazon Athena. The query works fine when run in the Athena Query Editor. It does not work when I run it using SQLWorkbench

ALTER TABLE "AwsDataCatalog".mydb.mytable ADD IF NOT EXISTS 
PARTITION (folder = '10036', full = 'fullvalue') LOCATION 's3://my-s3-folder/10036/fullvalue/';

Note: I have also tried using ALTER EXTERNAL_TABLE

sqlworkbench just returns:

An error occurred when executing the SQL command:

ALTER TABLE "AwsDataCatalog".mydb.mytable ADD PARTITION (folder = '10036', full >= 'fullvalue') LOCATION 's3://my-s3-folder...
Failed to run query
1 statement failed.

running in query editor

ALTER TABLE mydb.mytable ADD PARTITION (folder = '10039', full = 'fullvalue') >LOCATION 's3://my-s3-folder/10039/fullvalue/';

Query successful.

any body know how to make this work so I can batch some add partition queries together?

Upvotes: 0

Views: 9882

Answers (2)

Theo
Theo

Reputation: 132972

If you want to batch add partitions you can repeat the part including and after PARTITION as many times as you need:

ALTER TABLE mydb.mytable ADD IF NOT EXISTS 
  PARTITION (folder = '10036', full = 'fullvalue') LOCATION 's3://my-s3-folder/10036/fullvalue/'
  PARTITION (folder = '10037', full = 'fullvalue') LOCATION 's3://my-s3-folder/10037/fullvalue/'
  PARTITION (folder = '10038', full = 'fullvalue') LOCATION 's3://my-s3-folder/10038/fullvalue/';

Skip the "AwsDataCatalog" part of the table name, it should not be needed, and is probably the reason for your problem in SQLWorkbench.

Upvotes: 1

James
James

Reputation: 11931

I have not had success qualifying the database and table names with "AwsDataCatalog" in ALTER or SHOW PARTITION statements (but it worked in a SELECT). How about just:

ALTER TABLE "AwsDataCatalog".mydb.mytable ADD IF NOT EXISTS 
PARTITION (folder = '10036', full = 'fullvalue')
LOCATION 's3://my-s3-folder/10036/fullvalue/';

Would that work for you?

Upvotes: 0

Related Questions