从AWS Redshift迁移至GCP BigQuery：替代IDENTITY列生成BIGINT代理键方案咨询

我之前处理过从Redshift到BigQuery的EDW迁移项目，刚好遇到过类似的代理键生成需求——既要保持BIGINT类型兼容下游，又要能识别重复的自然键。给你分享几个经过实践验证的方案：

方案1：BigQuery序列（SEQUENCE）+ MERGE语句（推荐增量加载） #

BigQuery虽然没有IDENTITY列，但可以用序列对象来生成自增的BIGINT值，结合MERGE语句既能生成新键，又能检测来自不同客户源的重复业务键。

步骤： #

1. 创建全局序列，初始值设为Redshift现有最大代理键值（保证历史数据和新生成键不冲突）：

CREATE SEQUENCE IF NOT EXISTS edw.proxy_key_seq
START WITH 1000000 -- 替换成你现有Redshift表的最大代理键值
INCREMENT BY 1
OPTIONS (
  description='Global sequence for EDW surrogate keys'
);

2. 用MERGE语句加载数据，自然键不存在时生成新代理键，存在时标记为重复：

MERGE INTO edw.target_table t
USING (
  SELECT 
    source_system_id,
    natural_key,
    col1, col2 -- 替换为你的业务字段
  FROM edw.staging_table
) s
ON t.natural_key = s.natural_key
WHEN NOT MATCHED THEN
  INSERT (proxy_key, source_system_id, natural_key, col1, col2, is_duplicate)
  VALUES (NEXTVAL(edw.proxy_key_seq), s.source_system_id, s.natural_key, s.col1, s.col2, FALSE)
WHEN MATCHED THEN
  UPDATE SET is_duplicate = TRUE; -- 标记重复自然键，方便后续排查

优点： #

• 完全在BigQuery内部完成，无需依赖外部工具
• 原子性操作，避免并发加载时的键冲突
• 自动标记重复自然键，符合你的需求

注意： #

• 若多个表需要独立代理键，需创建多个序列
• 务必确保序列起始值大于现有历史数据的最大键，避免冲突

方案2：窗口函数批量生成（适合历史数据迁移） #

如果是一次性迁移历史数据，可以用BigQuery的窗口函数批量生成唯一BIGINT代理键，同时识别重复自然键。

示例代码： #

WITH historical_data AS (
  SELECT
    source_system_id,
    natural_key,
    col1, col2,
    -- 先标记重复的自然键
    COUNT(*) OVER (PARTITION BY natural_key) AS duplicate_count
  FROM edw.redshift_historical_export
)
SELECT
  -- 生成自增代理键：ROW_NUMBER()加上现有最大键的偏移量
  ROW_NUMBER() OVER (ORDER BY natural_key, source_system_id) + 999999 AS proxy_key, -- 偏移量替换为你的实际最大键值
  source_system_id,
  natural_key,
  col1, col2,
  CASE WHEN duplicate_count > 1 THEN TRUE ELSE FALSE END AS is_duplicate
INTO edw.target_table
FROM historical_data;

优点： #

• 批量处理效率高，适合一次性迁移大量历史数据
• 纯SQL实现，无需依赖外部组件

注意： #

• 偏移量必须准确设置为Redshift现有最大代理键值，避免和后续增量键冲突
• 排序时加入source_system_id，保证重复自然键的排序稳定性

方案3：Spark优化版（针对你已考虑的方向） #

你提到的Spark方案可以优化，无需全量数据驻留内存——结合BigQuery分区加载和Spark分布式生成逻辑即可：

优化思路： #

1. 从BigQuery读取目标表当前最大代理键，作为Spark生成键的起始值
2. 将 staging 数据按自然键分区，在每个分区内生成自增键（起始值=全局最大值+分区偏移）
3. 用Spark窗口函数标记重复自然键
4. 批量写入BigQuery

示例Python代码片段： #

from pyspark.sql import SparkSession
from pyspark.sql.functions import row_number, count, col
from pyspark.sql.window import Window

spark = SparkSession.builder.appName("SurrogateKeyGenerator").getOrCreate()

# 读取BigQuery中现有最大代理键
max_key_df = spark.read.format("bigquery").option("table", "edw.target_table").selectExpr("MAX(proxy_key) as max_key")
max_key = max_key_df.collect()[0]["max_key"] or 0

# 读取staging数据
staging_df = spark.read.format("bigquery").option("table", "edw.staging_table").load()

# 标记重复自然键
duplicate_window = Window.partitionBy("natural_key")
staging_df = staging_df.withColumn("is_duplicate", (count("*").over(duplicate_window) > 1).cast("boolean"))

# 生成全局自增代理键
key_window = Window.orderBy("natural_key", "source_system_id")
final_df = staging_df.withColumn("proxy_key", row_number().over(key_window) + max_key)

# 写入BigQuery
final_df.write.format("bigquery").option("table", "edw.target_table").mode("append").save()

优点： #

• 利用Spark分布式处理能力，适合超大规模数据
• 可灵活处理复杂业务规则（比如不同源系统的差异化键逻辑）

注意： #

• 需处理并发写入场景，可通过幂等性加载逻辑或分布式锁避免键重复
• 避免重复运行生成重复键，建议加入批次号校验

总结 #

如果是常规EDW场景，方案1（序列+MERGE）是最省心的选择，完全在BigQuery内部实现，满足所有需求；如果是历史数据迁移，方案2效率最高；如果数据量极大或有复杂业务规则，优化后的方案3更合适。

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