Open navigation

How to delete multiple BigQuery tables

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

  1. 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


  2. Open BigQuery. Paste in the code.
  3. 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.



  4. Click Run.
  5. Click Save results. This will open the Save Query Results dialog box.
  6. Select Google Sheets from the dropdown menu.
  7. Click Save.
  8. Open your file in Google Sheets, and copy the drop statements from it.
  9. Return to BigQuery. Add /* at the start of the copied code, and */ at the end of it.
  10. Paste the drop statements below the code.


  11. 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.
  12. Once you've commented out the original code, run the query. It'll remove your tables. 


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.