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?