CODEWITHSUNDEEP
google-bigquery
JasonB
JasonB

Reputation: 312

Delete BigQuery tables with wildcard

Is there any way to delete tables from the command line with a wildcard? Say I have a set of dated tables that I want to delete, with the same prefix - can I delete them all in one go, without writing my own shell script?

Upvotes: 13

Views: 7291

Answers (3)

Marco Rossi
Marco Rossi

Reputation: 239

Following @henry answer, I couldn't do it in sql (don't know why) so I adapted the concat to create a bunch of lines to feed all together to the shell

select concat("bq rm -f -t ",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

Then just 'Save results' => ' Copy to clipboard' => open BQ Shell => paste!

Upvotes: 1

Henry Munro
Henry Munro

Reputation: 249

Not the nicest but you can generate a query to delete the tables via: 

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

After running that click "Save Results", select "Copy to clipboard" from the dropdown.

Upvotes: 22

Jeremy Condit
Jeremy Condit

Reputation: 7046

Nope, gotta script it. You can delete an entire dataset at once, but there's no way to use a wildcard to delete a subset of the tables in a dataset.

Upvotes: 3

Related Questions