Drop AWS Athena table with `.` in the name

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 

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobmxtaG2BcYOOnamoqF2WxLR5wK2fnqaRYsGirsueZLChpJ16qrqMrZ%2BeZZ6WuqY%3D