
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!
32 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