BigQuery如何修改分区表_PARTITIONTIME值且兼容现有查询逻辑？

该操作可以实现，无需修改任何基于_PARTITIONTIME的外部查询语句。

BigQuery的摄入时间分区表的_PARTITIONTIME伪列支持在写入数据时主动赋值覆盖默认的摄入时间，你只需要重写存量数据时指定_PARTITIONTIME为my_timestamp对应的日期即可，后续新写入数据不主动指定该字段的话，仍会默认使用摄入时间作为分区值。

注意：BigQuery不支持直接通过UPDATE语句修改_PARTITIONTIME伪列的取值，因此必须通过整表重写的方式批量调整存量数据的分区归属，这是目前唯一可行的方案。

操作步骤 #

• 步骤1：备份原表避免操作失误丢失数据
  执行SQL：

  CREATE OR REPLACE TABLE `你的项目ID.你的数据集.原表名_backup`
  AS SELECT * FROM `你的项目ID.你的数据集.原表名`;
  

• 步骤2：生成修复后的临时表，替换存量数据的分区时间
  保持原表的分区规则不变，将_PARTITIONTIME赋值为my_timestamp对应的日期：

  CREATE OR REPLACE TABLE `你的项目ID.你的数据集.临时修复表`
  PARTITION BY DATE(_PARTITIONTIME)
  AS
  SELECT
    * EXCEPT(my_timestamp),
    DATE(my_timestamp) AS _PARTITIONTIME
  FROM `你的项目ID.你的数据集.原表名`;
  

• 步骤3：用修复后的临时表覆盖原表

  CREATE OR REPLACE TABLE `你的项目ID.你的数据集.原表名`
  PARTITION BY DATE(_PARTITIONTIME)
  AS SELECT * FROM `你的项目ID.你的数据集.临时修复表`;
  

• 步骤4：验证分区正确性
  执行以下查询检查各分区行数是否符合业务预期：

  SELECT
    _PARTITIONTIME AS partition_date,
    COUNT(*) AS row_count
  FROM `你的项目ID.你的数据集.原表名`
  GROUP BY 1
  ORDER BY 1 DESC;
  

注意事项 #

• 后续新写入数据时无需做任何额外配置，只要写入语句不主动指定_PARTITIONTIME的值，BigQuery会默认使用数据摄入时间作为分区值，完全符合你的需求
• 整个操作不会修改原表的字段结构、分区规则，所有外部连接器、查询语句都无需调整，兼容性无问题
• 若表数据量极大，可按my_timestamp的日期范围分批写入临时表，避免单次操作占用过多配额或超时

内容的提问来源于stack exchange，提问作者Nakeuh