如何在BigQuery中清理超大规模分区表的重复行？

问题背景 #

你有多个BigQuery分区表存在大量重复行，需要保留唯一行。原SQL方案对大部分表有效，但两张150亿行的超大表执行时因数据量过大失败：

CREATE OR REPLACE TABLE
  `project.dataset.table`
PARTITION BY
  DATE(datestamp) AS
SELECT
  *
FROM
  `project.dataset.table` QUALIFY ROW_NUMBER() OVER (PARTITION BY id, CAST(value AS STRING), names, parameter) = 1

直接按分区过滤会误删历史数据，手动拆分分区建表再合并的方案工作量极大。

优化方案 #

1. 按分区分批处理，保留全量数据 #

核心思路是逐个处理每个分区，避免全量扫描带来的资源压力，同时保留所有历史数据。可以通过BigQuery脚本、Python客户端等工具循环遍历所有分区自动执行，示例SQL如下：

-- 替换[PARTITION_DATE]为具体分区日期，例如'2023-01-01'
CREATE OR REPLACE TABLE `project.dataset.table`
PARTITION BY DATE(datestamp)
AS
-- 处理目标分区的去重
SELECT *
FROM `project.dataset.table`
WHERE DATE(datestamp) = '[PARTITION_DATE]'
QUALIFY ROW_NUMBER() OVER (PARTITION BY id, CAST(value AS STRING), names, parameter) = 1
-- 保留其他未处理分区的原始数据
UNION ALL
SELECT *
FROM `project.dataset.table`
WHERE DATE(datestamp) != '[PARTITION_DATE]'

每次仅处理单个分区，资源占用可控，且无需手动拆分合并多张表。

2. 用DML逐分区删除重复行 #

无需重建整张表，直接在原表上针对每个分区删除重复项，只保留每组重复数据的第一行（以datestamp最小为例）：

-- 替换[PARTITION_DATE]为具体分区日期
DELETE FROM `project.dataset.table`
WHERE DATE(datestamp) = '[PARTITION_DATE]'
AND (id, CAST(value AS STRING), names, parameter, datestamp) NOT IN (
  SELECT id, CAST(value AS STRING), names, parameter, MIN(datestamp)
  FROM `project.dataset.table`
  WHERE DATE(datestamp) = '[PARTITION_DATE]'
  GROUP BY id, CAST(value AS STRING), names, parameter
)

这种方式更轻量化，适合不想重建表结构的场景，同样可通过脚本批量处理所有分区。

3. 快照分区+MERGE合并（安全优先） #

先为单个分区创建去重临时表，验证结果后再合并回原表，降低直接操作原表的风险：

-- 替换[PARTITION_DATE]为具体分区日期
-- 1. 创建目标分区的去重临时表
CREATE TEMP TABLE temp_dedup AS
SELECT *
FROM `project.dataset.table`
WHERE DATE(datestamp) = '[PARTITION_DATE]'
QUALIFY ROW_NUMBER() OVER (PARTITION BY id, CAST(value AS STRING), names, parameter) = 1;

-- 2. 合并回原表，替换对应分区的重复数据
MERGE `project.dataset.table` t
USING temp_dedup td
ON t.datestamp = td.datestamp 
   AND t.id = td.id 
   AND CAST(t.value AS STRING) = CAST(td.value AS STRING) 
   AND t.names = td.names 
   AND t.parameter = td.parameter
WHEN NOT MATCHED THEN INSERT ROW
WHEN MATCHED THEN DELETE;

临时表可提前校验去重结果，确保数据正确性后再执行合并操作。

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