BigQuery从外部表更新大表时内存资源超限问题求助

针对你用100万行外部表更新2亿行目标表时出现的内存超限错误，可通过以下几种方式解决：

• 将外部表转为BigQuery本地表
  外部表（如存储在GCS的非BQ托管表）在关联查询时，往往无法被BigQuery有效优化，容易导致内存占用过高。先把外部表数据导入到BQ本地临时表，再执行更新：

-- 创建本地临时表
CREATE OR REPLACE TABLE `your_project.your_dataset.temp_internal_table`
AS SELECT * FROM `external_table`;

-- 使用本地表执行更新
UPDATE `target_table` AS TARGET
SET target.string_to_update = source.string_to_update
FROM `temp_internal_table` AS SOURCE
WHERE target.id = source.id;

• 强制使用哈希连接优化执行计划
  BigQuery默认的连接策略可能选择了内存效率较低的方式（如嵌套循环），可以通过OPTIONS强制指定哈希连接，降低内存消耗：

UPDATE `target_table` AS TARGET
SET target.string_to_update = source.string_to_update
FROM `external_table` AS SOURCE
WHERE target.id = source.id
OPTIONS(join_type='HASH');

• 分批执行更新操作
  将100万行的更新数据拆分成多个批次，分多次执行更新，避免单次查询占用过多内存。比如按ID范围拆分：

-- 第一批：处理ID小于500000的记录
UPDATE `target_table` AS TARGET
SET target.string_to_update = source.string_to_update
FROM `external_table` AS SOURCE
WHERE target.id = source.id
AND source.id < 500000;

-- 第二批：处理ID大于等于500000的记录
UPDATE `target_table` AS TARGET
SET target.string_to_update = source.string_to_update
FROM `external_table` AS SOURCE
WHERE target.id = source.id
AND source.id >= 500000;

也可以用哈希取模的方式拆分更多批次，比如分成10批依次执行：

UPDATE `target_table` AS TARGET
SET target.string_to_update = source.string_to_update
FROM `external_table` AS SOURCE
WHERE target.id = source.id
AND MOD(source.id, 10) = 0; -- 依次替换0到9

• 优化外部表存储格式
  如果外部表使用CSV等非列式存储格式，读取时会占用更多内存。建议将外部表数据转换为Parquet或ORC等列式存储格式后再执行更新，或者在导入本地表时指定列式存储。

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