google-bigquery - 谷歌 Bigquery : How to update partition expiration time for a partitioned table?

Question

Bigquery 文档说可以更新分区表的分区时间到期。而我只能为摄取时间分区表执行此操作。我尝试了以下方法:

  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 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.

现在对于分区表:

  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" 
   )' 

在这种情况下更新失败

  # 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.

如何在此处更新分区时间到期？

Answer 1

试试这个，将分区字段指定为 bq update 的一部分命令:

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

编辑:您现在可以使用 ALTER TABLE SET OPTIONS statement也可以更改分区过期时间。例如:

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