BigQuery现有分区表如何设置分区过期并删除30天以上历史分区

数据集层级的分区过期策略仅对设置后新创建的分区表生效，已经存在的表不会自动继承该规则，所以之前的配置不生效属于预期行为。

方案1：批量更新已有表的分区过期时间（推荐，自动生效无需手动删数据） #

配置后BigQuery会自动在后台清理过期分区，无需人工干预，后续新产生的分区也会自动按规则过期，适合长期维护测试环境的场景。

• 第一步：先查询当前测试数据集中未配置30天分区过期的表，校验数据范围：

SELECT
  table_catalog,
  table_schema,
  table_name,
  partition_expiration_days
FROM
  `你的测试项目名.你的测试数据集名.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  partition_expiration_days IS NULL OR partition_expiration_days != 30
GROUP BY 1,2,3,4

• 第二步：单表修改分区过期时间可直接执行DDL语句：

ALTER TABLE `你的测试项目名.你的测试数据集名.目标表名`
SET OPTIONS(
  partition_expiration_days = 30
);

• 第三步：如果数据集中表数量较多，可在bq命令行工具中执行批量修改命令：

bq ls --format=json 你的测试项目名:你的测试数据集名 | jq -r '.[] | select(.type == "TABLE" and .timePartitioning != null) | .id' | while read table_id; do
  bq update --time_partitioning_expiration 2592000 $table_id
done

注：2592000为30天对应的秒数，BigQuery命令行的分区过期参数以秒为单位

配置完成后，最长24小时内BigQuery会自动完成所有历史过期分区的清理。

方案2：手动批量删除过期分区（适合需要立即释放存储的场景） #

如果不想等待自动过期的执行周期，需要立刻清理存量过期数据，可以直接批量生成删除语句执行：

• 执行以下查询生成所有过期分区的删除SQL：

SELECT
  CONCAT("ALTER TABLE `", table_catalog, ".", table_schema, ".", table_name, "` DROP PARTITION FOR_DATE('", partition_id, "');") AS drop_sql
FROM
  `你的测试项目名.你的测试数据集名.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  partition_id NOT LIKE '%__NULL__%'
  AND DATE(partition_id) < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

• 导出查询结果中的所有DROP语句，统一执行即可一次性清理所有早于30天的分区。

注意事项 #

• 上述规则同时适配按业务日期分区、按摄入时间（ingestion time）分区的表
• 若部分表有特殊保留周期需求，可单独为其配置不同的partition_expiration_days值，表级配置优先级高于数据集级配置
• 建议保留之前设置的数据集层级分区过期规则，后续新同步到测试环境的表会自动继承该规则，无需重复手动配置

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