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

Question

Bigquery documentation says its possible to update the partition time expiry for a partitioned table. Whereas I'm able to do that only for ingestion time partitioned tables. I tried the below:

  bq query --use_legacy_sql=false ' 
  CREATE TABLE IF NOT EXISTS [DATASET].partitioned_table_ingestion_time ( 
      event_date DATE NOT NULL, 
      event_id INT64) 
  PARTITION BY DATE(_PARTITIONTIME)
  OPTIONS( 
     partition_expiration_days=10, 
     description="table partitioned by ingestion time and with expiry" 
   )' 

Update query and result:

  # update expiry to 7 days = 7 * 24 * 60 * 60 = 604800 s
  bq update --time_partitioning_expiration 604800 [PROJECT-ID]:[DATASET].partitioned_table_ingestion_time

  Table 'PROJECT-ID]:[DATASET].partitioned_table_ingestion_time' successfully updated.

Now for the partitioned table:

  bq query --use_legacy_sql=false ' 
  CREATE TABLE IF NOT EXISTS [DATASET].partitioned_table ( 
      event_date DATE NOT NULL, 
      event_id INT64) 
  PARTITION BY event_date
  OPTIONS( 
     partition_expiration_days=10, 
     description="table partitioned by event_date with expiry" 
   )' 

The update fails in this case

  # update expiry to 7 days
  bq update --time_partitioning_expiration 604800 [PROJECT-ID]:[DATASET].partitioned_table 

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

How can I update the partition time expiry here?

Answer 1

Try this instead, 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

Edit: you can now use the ALTER TABLE SET OPTIONS statement to change the partition expiration as well. For example:

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

Answer 2

In my case, When I added time_partitioning_type and time_partitioning_field option to command, it was successfully updated.

Answer 3

if you want to remove partition expiration completely, set NULL for partition_expiration_days option

ALTER TABLE `project-name`.dataset_name.table_name
SET OPTIONS (partition_expiration_days=NULL);