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 OPTIONS(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 \
--time_partitioning_expiration 604800 [PROJECT-ID]:[DATASET].partitioned_table
Alternatively, you can also issue the below ALTER Statement:
ALTER TABLE `project-name`.dataset_name.table_name
SET OPTIONS (partition_expiration_days=7);
Related Errors:
- BigQuery error in update operation: Cannot change partitioning spec for a partitioned table
Mike-Barn
posted on 08 Apr 19Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment