Google BigQuery - how to drop table with bq command?

Question

Google BigQuery - bq command enable you to create, load, query and alter table.

I did not find any documentation regarding dropping table, will be happy to know how to do it.

I found the bq tool much easier to implement instead of writing python interface for each command.

Thanks.

Answer 1

found it :

bq rm -f -t data_set.table_name

-t for table, -f for force, -r remove all tables in the named dataset

great tool.

Answer 2

Is there a way to bulk delete multiple tables? – activelearner

In bash, you can do something like:

for i in $(bq ls -n 9999 my_dataset | grep keyword | awk '{print $1}'); do bq rm -ft my_dataset.$i; done;

Explanation:

• bq ls -n 9999 my_dataset - list up to 9999 tables in my dataset
• | grep keyword - pipe the results of the previous command into grep, search for a keyword that your tables have in common
• | awk '{print $1}' - pipe the results of the previous command into awk and print only the first column
• Wrap all that into a for loop
• do bq rm -ft my_dataset.$i; done; - remove each table from your dataset

I would highly recommend running the commands to list out the tables you want to delete before you add the 'do bq rm'. This way you can ensure you are only deleting the tables you actually want to delete.

UPDATE: The argument -ft now returns an error and should be simply -f to force the deletion, without a prompt:

for i in $(bq ls -n 9999 my_dataset | grep keyword | awk '{print $1}'); do bq rm -f my_dataset.$i; done;