CODEWITHSUNDEEP
google-bigquery
Jonathan Yeo
Jonathan Yeo

Reputation: 29

Delete tables with Partitions in Bigquery

I have a dataset that has a few different tables in it. For one of the tables, it has 100 partitions. I would like to drop/delete all the partitions of a table with a specific name.

For example, I have the below in one dataset. So far, I can only delete a partition at a time. Is there anyway for me to delete all all TableA partitions?

tableA_20161220
tableA_20161221
tableA_20161223
tableB_20161220
tableB_20161221
tableB_20161223

Upvotes: 2

Views: 1845

Answers (3)

akilesh raj
akilesh raj

Reputation: 674

This is a slightly modified version of what Graham has provided.

bq ls <PROJECT_ID>:<DATASET> | grep "<SHARDED_TABLE_NAME>" | awk '{print $1}' | xargs "-I%" bq rm -f <PROJECT_ID>:<DATASET>."%"

Explaining this script,

1.List all the tables in the dataset

bq ls <PROJECT_ID>:<DATASET>

2.Filter the dataset that has the sharded tables.

grep "<SHARDED_TABLE_NAME>"

3.Print just the first column(the tables in the dataset)

awk '{print $1}'

4.For each table in the output, perform the delete operation

xargs "-I%" bq rm -f <PROJECT_ID>:<DATASET>."%"

More on xargs here and more on bq command line here

The solution to your problem will be,

bq ls <PROJECT_ID>:<DATASET> | grep "tableA" | awk '{print $1}' | xargs "-I%" bq rm -f <PROJECT_ID>:<DATASET>."%"

And as Graham had already mentioned, be careful about the dataset you are deleting.

Upvotes: 1

Graham Polley
Graham Polley

Reputation: 14791

As Mikhail has already pointed out, you write a script using the API.

We regularly use the CLI for such tasks. This little snippet should be enough to get you going. Just be careful using it because it performs a 'force' delete i.e. no confirmation:

bq ls --format=csv <DATASET> | awk '{if(NR>1)print}' | awk -F, '{print $1}' | grep 'A' | xargs -n 1 -P 4 -i bq rm -f <DATASET>.{}

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

From the Web UI you can delete only one table at a time.

Overall, BigQuery tables.delete API accepts only one table at a time

Using client of your choice or command line you can "script" to make this process fully or semi-automated to delete needed tables in a loop.

Upvotes: 0

Related Questions