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 ?

Stu

were you able to delete a partition of your choice? – Evalyn

Answer 1

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.

Shabbir

Answer 2

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.

Celeski

Answer 3

Simple way we can Update it Through CLI:-

bq update --time_partitioning_expiration (time in second) Dataset.table_name

Schism

With partitioned table: BigQuery error in update operation: Cannot change partitioning/clustering spec for a partitioned/clustered table. With non partitioned table: Cannot convert non partitioned table to partitioned table. – Sikhism

Try: bq update --default_partition_expiration integer project_id:dataset – Sikhism

Answer 4

For people like myself who clicked for the title BigQuery (BQ) - Drop Partition but are looking to do it with api.

You drop a partition the same way you delete a table only adding ${partrition} to the end, i.e. my.table.id$20230101. (api doc for deleting a table)

example using python:

from google.cloud import bigquery

client.delete_table('my.table.id$20230101')

and a if anyone else wants to delete daily partritions

from google.cloud import bigquery
from datetime import date

def delete_day_partition(table_id: str, day: date):
    """
    https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/delete
    https://mcmap.net/q/2029565/-is-there-a-way-of-deleting-old-partitions-in-a-partitioned-table-using-bigquery-api
    """
    client = bigquery.Client()
    day = day.strftime('%Y%m%d')  # date() -> 'YYYYMMDD'
    partition = f'{table_id}${day}'
    client.delete_table(partition)

delete_day_partition('my.table.id', date(2023, 1, 1))

Weight

Answer 5

Step 1: Select the project in which the dataset (and thus table) exists

gcloud config set project <project-name>

Note: You can see the full list of available projects using gcloud projects list

Step 2: Delete the desired partition

bq rm -t 'dataset.table$partition'

---

Otherwise, you can even delete a partition from a table even when working on a separate project. But in this case you'd have to specify it in the command:

bq rm -t project_id:dataset.table$partition

Senn