Warning
If this process isn't carried out carefully, there's a risk that every table that your account has access to will be permanently deleted.
Supermetrics enables you to bring all your marketing data into BigQuery with just a few clicks. We shard the data by date to maximize performance, which means that dropping a table isn't quite as simple as it would otherwise be. If you find that for any reason you need to drop a table (all shards), follow the steps below.
This guide describes how to delete multiple BigQuery tables using a wildcard. This solution was provided by Henry Munro at Stack Overflow.
Instructions
- Copy this code:
select concat("drop table ",table_schema,".", table_name, ";" )
from <insert_your_dataset_name>.INFORMATION_SCHEMA.TABLES
where table_name like "INSERT_YOUR_TABLE_NAME_%"
order by table_name desc - Open BigQuery. Paste in the code.
- Replace <insert_your_dataset_name> with your dataset name, and INSERT_YOUR_TABLE_NAME_% with your table name. Below, we’ve used "Supermetrics" as the dataset name, and "GA_GA_V1" as the table name.
- Click Run.
- Click Save results. This will open the Save Query Results dialog box.
- Select Google Sheets from the dropdown menu.
- Click Save.
- Open your file in Google Sheets, and copy the drop statements from it.
- Return to BigQuery. Add /* at the start of the copied code, and */ at the end of it.
- Paste the drop statements below the code.
- Double-check your work. As noted above, it's essential to exercise caution when carrying out this process. If it's not done correctly, there's a risk that every table that your account has access to will be permanently deleted.
- Once you've commented out the original code, run the query. It'll remove your tables.