如何在BigQuery存储过程中创建表及优化分区超限查询

最优查询改写（无需循环） #

原循环遍历数据集的方案会生成多个独立结果集，且多次查询元数据效率低。BigQuery支持区域级INFORMATION_SCHEMA视图，可一次性拉取指定区域下所有数据集的分区元数据，直接生成统一汇总结果：

-- 请替换代码中<你的项目ID>、<区域名>、<存储汇总表的数据集名>为实际值
CREATE OR REPLACE TABLE `<你的项目ID>.<存储汇总表的数据集名>.partition_overlimit_summary` AS
SELECT
  table_catalog AS project_id,
  table_schema AS dataset_name,
  table_name,
  COUNT(partition_id) AS partition_total_count
FROM
  `<你的项目ID>.<区域名>.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  partition_id IS NOT NULL
  -- 排除不需要统计的数据集
  AND table_schema NOT IN ('dp_sample','test_dataset','spark_bigquery_staging')
GROUP BY
  project_id, dataset_name, table_name
-- 3600为预警阈值，预留缓冲避免触及4000硬限制
HAVING
  partition_total_count > 3600
ORDER BY
  partition_total_count DESC;

该方案优势：

• 仅执行1次元数据查询，执行效率远高于循环遍历方案
• 结果直接写入指定的汇总表，无需手动合并多个分散结果
• 可直接扩展过滤、排序逻辑，适配自定义统计需求

存储过程封装版本 #

如果需要定时调度或复用查询逻辑，可以封装为存储过程：

-- 创建存储过程
CREATE OR REPLACE PROCEDURE `<你的项目ID>.<存储存储过程的数据集名>.sp_check_partition_limit`(
  OUT output_table_path STRING,
  IN excluded_datasets ARRAY<STRING>,
  IN warning_threshold INT64
)
BEGIN
  -- 定义汇总表输出路径
  SET output_table_path = '<你的项目ID>.<存储汇总表的数据集名>.partition_overlimit_summary';

  EXECUTE IMMEDIATE FORMAT("""
    CREATE OR REPLACE TABLE %s AS
    SELECT
      table_catalog AS project_id,
      table_schema AS dataset_name,
      table_name,
      COUNT(partition_id) AS partition_total_count,
      CURRENT_TIMESTAMP() AS check_time
    FROM
      `<你的项目ID>.<区域名>.INFORMATION_SCHEMA.PARTITIONS`
    WHERE
      partition_id IS NOT NULL
      AND table_schema NOT IN UNNEST(@excluded_datasets)
    GROUP BY
      project_id, dataset_name, table_name
    HAVING
      partition_total_count > @warning_threshold
    ORDER BY
      partition_total_count DESC
  """, output_table_path)
  USING excluded_datasets AS excluded_datasets, warning_threshold AS warning_threshold;
END;

-- 调用存储过程示例
CALL `<你的项目ID>.<存储存储过程的数据集名>.sp_check_partition_limit`(
  @output_table,
  ['dp_sample','test_dataset','spark_bigquery_staging'],
  3600
);

-- 查看汇总结果
SELECT * FROM `<你的项目ID>.<存储汇总表的数据集名>.partition_overlimit_summary`;

额外建议 #

• 可以将存储过程配置为BigQuery定时任务，每日自动运行生成预警结果
• 对分区数超过3600的表，建议调整为更粗粒度的分区规则（如按周/月分区）、配置分区过期策略清理老旧数据，避免触及4000分区硬限制导致写入失败

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