a.moussa
a.moussa

Reputation: 3287

How to drop multiple tables with prefix

I have a database db1 containing many table

For exemple, I would like to drop all tables with prefix v1_ something like

DROP TABLE IF EXISTS v1_*;

Do you have any idea?

Upvotes: 2

Views: 2873

Answers (1)

Theo
Theo

Reputation: 132862

This is unfortunately not possible to do with Athena SQL.

You can, however, use the Glue API to achieve something similar, for example through AWS CLI:

aws glue get-tables \
  --region us-east-1 \
  --database-name my_database \
  --query 'TableList[].Name' \
  --output text \
  | grep -F v1_ \
  | xargs -n 1 aws glue delete-table \
    --region us-east-1 \
    --database-name my_database \
    --name

The command above lists all tables in a database called "my_database" in the us-east-1 region, and filters them by "v1_" (you might want to filter by regex to ensure that it only matches in the beginning). It then pipes the matching table names, one by one, into a command that deletes them.

Upvotes: 8

Related Questions