如何用Python在Google BigQuery中高效批量执行删除操作

针对14000条逐条执行的DELETE语句速度极慢的问题，核心优化思路是减少查询作业的数量（避免每条DELETE都启动独立作业的开销），以下是两种高效的实现方式：

方案一：合并删除条件为单条/少量DELETE语句 #

将所有删除条件整合到一个或少数几个DELETE语句中，利用IN子句匹配多组键值对，避免重复创建作业。

示例SQL #

DELETE FROM `center_shoppings.stores.orders` AS o
WHERE (o.store_reference, o.store_id, o.day_sold) IN (
  ('554151-16161-23230', 23613213, '2023-03-10'),
  ('156616-12303-2646213', 61623265, '2023-03-11'),
  ('6515156-26151651651-2616', 1652612, '2023-03-12'),
  -- 批量添加剩余13997组条件
)

注意事项 #

• BigQuery SQL语句有长度限制（最大10MB），14000组条件可能超出限制，建议按每500-1000组条件拆分一个DELETE语句，最终只需要14-28个作业，远少于14000个。
• 这种方式无需额外创建表，实现成本最低，适合条件数量适中的场景。

方案二：临时表关联删除（推荐用于大量条件） #

将所有需要删除的键值对存入临时表，通过USING关联目标表执行一次性删除，这是扩展性最好的方案，适合上万条条件的场景。

步骤1：准备待删除数据并写入临时表 #

假设你已经有包含所有删除条件的原始数据（如列表），用Python客户端批量写入临时表：

import pandas as pd
from google.cloud import bigquery

client = bigquery.Client()

# 假设delete_records是包含所有待删除键值对的列表
delete_records = [
    ('554151-16161-23230', 23613213, '2023-03-10'),
    ('156616-12303-2646213', 61623265, '2023-03-11'),
    # 剩余13998条记录
]

# 转换为DataFrame
df = pd.DataFrame(delete_records, columns=['store_reference', 'store_id', 'day_sold'])

# 加载数据到临时表（与目标表同区域，避免跨区开销）
temp_table_id = f"{client.project}.{client.location}.temp_delete_orders"
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("store_reference", "STRING"),
        bigquery.SchemaField("store_id", "INTEGER"),
        bigquery.SchemaField("day_sold", "DATE"),
    ],
    write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
)

# 执行加载作业
load_job = client.load_table_from_dataframe(df, temp_table_id, job_config=job_config)
load_job.result()  # 等待加载完成

步骤2：执行关联删除 #

# 执行DELETE语句
delete_query = f"""
DELETE FROM `center_shoppings.stores.orders` AS o
USING `{temp_table_id}` AS td
WHERE o.store_reference = td.store_reference
  AND o.store_id = td.store_id
  AND o.day_sold = td.day_sold
"""

delete_job = client.query(delete_query)
delete_job.result()

# 可选：删除临时表
client.delete_table(temp_table_id)

优势 #

• 仅需2个作业（加载临时表+删除），彻底消除14000个作业的开销。
• 不受SQL语句长度限制，支持任意数量的删除条件。
• 若目标表是按day_sold分区的表，BigQuery会自动扫描对应分区，进一步提升删除效率。

内容的提问来源于stack exchange，提问作者Mike William Dopp