如何避免将CSV文件上传至BigQuery时产生重复行？

问题场景 #

使用Google官方示例代码将Cloud Storage中的CSV文件上传至BigQuery，但CSV本身包含大量重复数据，导致目标表出现重复行。你考虑过两种思路：一是在现有代码中添加逻辑避免重复上传；二是通过定时服务事后删除重复行，但后者针对数百GB数据成本高、效率低。

最优解决方案：在加载流程中前置去重 #

直接在代码中通过临时表+去重插入的方式，从根源避免重复行进入目标表，无需额外服务，效率更高且成本更低。

修改后的代码示例

from google.cloud import bigquery

client = bigquery.Client()
# 替换为你的目标表ID
table_id = "your-project.your_dataset.your_table_name"
# 创建临时表用于中转CSV数据
temp_table_id = f"{table_id}_temp"

# 配置CSV加载到临时表的参数
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    skip_leading_rows=1,
    source_format=bigquery.SourceFormat.CSV,
    # 每次加载覆盖临时表，避免临时表残留数据
    write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv"

# 第一步：将CSV加载到临时表
load_job = client.load_table_from_uri(uri, temp_table_id, job_config=job_config)
load_job.result()  # 等待加载完成

# 第二步：去重后插入目标表（以name+post_abbr作为唯一标识，根据实际业务调整）
# 方案1：使用INSERT DISTINCT + NOT EXISTS过滤已存在行
insert_query = f"""
INSERT INTO `{table_id}`
SELECT DISTINCT name, post_abbr
FROM `{temp_table_id}`
WHERE NOT EXISTS (
    SELECT 1 
    FROM `{table_id}` t
    WHERE t.name = `{temp_table_id}`.name 
      AND t.post_abbr = `{temp_table_id}`.post_abbr
)
"""

# 方案2：使用MERGE（更适合有明确主键的场景，支持跳过/更新重复行）
# insert_query = f"""
# MERGE INTO `{table_id}` t
# USING (SELECT DISTINCT name, post_abbr FROM `{temp_table_id}`) s
# ON t.name = s.name AND t.post_abbr = s.post_abbr
# WHEN NOT MATCHED THEN
#   INSERT (name, post_abbr) VALUES (s.name, s.post_abbr)
# """

# 执行去重插入
query_job = client.query(insert_query)
query_job.result()

# 清理临时表
client.delete_table(temp_table_id)

# 验证结果
destination_table = client.get_table(table_id)
print(f"Loaded {destination_table.num_rows} rows (已自动去重).")

方案说明

1. 临时表中转：先将CSV加载到临时表，避免直接操作目标表时的重复追加问题；
2. 去重逻辑：通过DISTINCT先过滤CSV本身的重复数据，再用NOT EXISTS或MERGE跳过目标表已存在的行；
3. 成本控制：BigQuery处理批量数据的效率远高于事后全表扫描删除，且临时表使用后立即删除，不会产生额外存储成本。

不推荐事后删除的原因 #

针对数百GB级别的数据，定时执行全表扫描删除重复行：

• 会产生高额的计算费用（全表扫描按数据量计费）；
• 执行速度慢，且可能影响业务查询；
• 无法从根源避免重复数据进入表中，属于被动补救方案。

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