Drop a few tables that have similar names by using wildcard

Question

I want to drop all tables starts name with "__am" in BigQuery dataset. Before this I'm using this query, but it takes a long query to do that.

DROP TABLE `sales.__am-123`;
DROP TABLE `sales.__am-134`;
DROP TABLE `sales.__am-145`;
DROP TABLE `sales.__am-156`;
DROP TABLE `sales.__am-167`;
DROP TABLE `sales.__am-178`;

How can I use wildcards for this problem?

Answer 1

1) In the BigQuery Console you can query the INFORMATION_SCHEMA to find all tables that you would like to delete using LIKE in the where clause to define your wildcards.
Then create a DROP TABLE statement around that, like this:

SELECT 'DROP TABLE ' || 'your_project_id.' || table_schema || '.' || table_name || ';'
FROM `your_project_id.your_dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'your_table_name_202202%'
ORDER BY table_name ASC;

2) The result of this query will look like this:

DROP TABLE your_project_id.your_dataset.sales.__am-123;
DROP TABLE your_project_id.your_dataset.sales.__am-134;
etc etc

3) Now copy the result of this query to the clipboard.

4) Then paste that to a new query and run that to drop all the tables.

See also here: Delete BigQuery tables with wildcard

Answer 2

You can follow this approach:

from google.cloud import bigquery

# TODO(developer) 
client = bigquery.Client.from_service_account_json('/folder/my_service_account_credentials.json')

dataset_id = 'project_id.dataset_id'
dataset = client.get_dataset(dataset_id)

# Creating a list of all tables in the above dataset
tables = list(client.list_tables(dataset))  


# Filtering out relevant wildcard tables to be deleted
# Give the string common in all your tables that you want to delete

tables_to_delete = ["{}.{}.{}".format(dataset.project, dataset.dataset_id, table.table_id) 
                    for table in tables if "search_pattern" in format(table.table_id)]

for table in tables_to_delete:
    client.delete_table(table)
    print("Deleted table {}".format(table))