BigQuery (BQ) - Drop Partition

Question

I'm using the BQ CLI (https://cloud.google.com/bigquery/bq-command-line-tool).

I didn't find how to Delete DAY Partition data.

For example, i have a DAY PARTITIONED table that holds data for dates 2016-09-01 and until 2016-09-30.

I need to delete the "2016-09-15" partition completely.

Is this possible through CLI command ?

Answer 1

If you do want to delete a partition you can use the bq rm command while specifying a $YYYYMMDD table decorator. For your case the command would look like:

bq rm -t 'dataset.table$20160915'

Be careful to include the single quotes, otherwise the decorator may get expanded to an empty variable and you'll delete the full table.

Answer 2

You likely do not have to delete it to re-insert the data. See this link for details about how to re-state data in a specific portion of your date partitioned table.

Basically, just use the --replace flag with the $YYYYMMDD table decorator with the bq CLI tool.