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 ?

were you able to delete a partition of your choice? – proprius Nov 08 '16 at 13:46 — proprius, Nov 08 '16 at 13:46

hamdog · Answer 1 · 2020-01-07T18:07:15.060

5

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.

edited Jan 07 '20 at 18:07

answered May 28 '19 at 21:25

hamdog

991
2
10
24

score 4 · Accepted Answer · answered Oct 10 '16 at 14:16

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.

score 1 · Answer 3 · edited Jun 12 '20 at 14:52

1

Simple way we can Update it Through CLI:-

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

edited Jun 12 '20 at 14:52

David Buck

3,752
35
31
35

answered Jun 12 '20 at 14:35

Kumar Pankaj Dubey

1,541
3
17
17

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.``` – Quockhanh Pham Nov 07 '22 at 02:47
Try: ```bq update --default_partition_expiration integer project_id:dataset``` – Quockhanh Pham Nov 07 '22 at 02:51

Giorgos Myrianthous · Answer 4 · 2022-06-12T11:39:36.903

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

BigQuery (BQ) - Drop Partition

4 Answers4