I had a client upload a malformed table with a name like foo.bar into an Athena instance. What syntax can I use to drop the table? If I try

drop table if exists `foo.bar`

The command silently fails, presumably because the parser interprets foo as the database name. If I try adding the database name explicitly as

drop table if exists dbname."foo.bar"

or

drop table if exists dbname.`foo.bar`

I get a parse error from Athena.

Unfortunately, I don't have access to the Glue console to remove the table from there so I was wondering if it's possible to drop such a table via Athena SQL. Thanks!

3

2 Answers

You could do drop table `database."foo.bar"` You need backticks to drop delta tables in Athena and "" to capture foo.bar as one word.

Even if you don't have access to the Glue console you can use the the AWS CLI to delete the table directly using the Glue API:

aws glue delete-table --database-name dbname --name foo.bar

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.