How to update partition expiration time for a partitioned table in Google Bigquery?

What is Partition expiration in BigQuery ?

Google Bigquery has the unique feature called "Partition expiration", If you setup this feature and provide an expiration time, any data older than that will automatically be removed from the table. This feature will be very handy for managing Staging of Intermediate tables

You can set this partition time expiry during table creation as well as after creating the table using update command

How to specify partition expiry days while table creation in Bigquery

CREATE TABLE Dataset.TableName
(
   EmployeeNo INT64 NOT NULL,
   FirstName STRING,
   LastName STRING,
   DOB DATE
   )
PARTITION BY DOB   
CLUSTER BY EmployeeNo\nOPTIONS(partition_expiration_days=7);

How to update the partition expiry days after table creation in Biquery:

Try the below command, specifying the partitioning field as part of the bq update command:

bq update --time_partitioning_field=event_date \\n  --time_partitioning_expiration 604800 [PROJECT-ID]:[DATASET].partitioned_table\n

Alternatively, you can also issue the below ALTER Statement:

ALTER TABLE `project-name`.dataset_name.table_name\nSET OPTIONS (partition_expiration_days=7);\n

Related Errors:

•   BigQuery error in update operation: Cannot change partitioning spec for a partitioned table

Mike-Barn

posted on