BigQuery表敏感数据脱敏：如何统一替换两表员工ID维持数据关联

方案1：BigQuery原生SQL实现（无需创建物理映射表） #

核心采用BigQuery内置的确定性哈希函数或临时CTE映射逻辑，全程在BigQuery内部完成，性能最优，适合各种数据量场景。

场景1：接受非连续新ID #

使用FARM_FINGERPRINT函数，同一个原员工ID永远返回相同的整数哈希值，天然保证两张表替换规则完全一致，无需额外维护映射关系。可自定义拼接固定盐值提升安全性，避免反向破解。
示例查询：

-- 生成Table1替换后结果，替换为Table2表名即可得到Table2的替换结果
SELECT
  -- 可拼接自定义盐值，修改引号内内容即可
  FARM_FINGERPRINT(CONCAT(CAST(员工ID AS STRING), "自定义固定盐值")) AS 新员工ID,
  -- 保留除原员工ID外的所有字段
  * EXCEPT(员工ID)
FROM `你的项目ID.你的数据集名.Table1`

场景2：需要新ID为从1开始的连续整数 #

用CTE临时生成两张表所有唯一原员工ID的映射关系，无需存储为物理表，查询结束后自动释放。
示例查询：

WITH 全局ID映射 AS (
  SELECT
    原员工ID,
    DENSE_RANK() OVER(ORDER BY 原员工ID) AS 新员工ID
  FROM (
    -- 合并两张表的所有唯一员工ID
    SELECT DISTINCT 员工ID AS 原员工ID FROM `你的项目ID.你的数据集名.Table1`
    UNION DISTINCT
    SELECT DISTINCT 员工ID AS 原员工ID FROM `你的项目ID.你的数据集名.Table2`
  )
)
-- 生成Table1替换后结果，修改下方表名为Table2即可得到Table2的替换结果
SELECT
  m.新员工ID,
  t.* EXCEPT(员工ID)
FROM `你的项目ID.你的数据集名.Table1` t
LEFT JOIN 全局ID映射 m ON t.员工ID = m.原员工ID

方案2：Colab + Python实现（适合需要自定义映射规则的场景） #

如果需要自定义新ID的生成逻辑，可通过BigQuery Python SDK完成，全程在Colab内运行，映射关系存在内存中，无需额外存储。
示例代码：

from google.cloud import bigquery
import pandas as pd

# 初始化BigQuery客户端（Colab已默认配置权限，无需额外传参）
bq_client = bigquery.Client()
PROJECT_ID = "你的项目ID"
DATASET = "你的数据集名"

# 读取两张表所有唯一的原员工ID
all_id_query = f"""
SELECT DISTINCT 员工ID AS 原_id FROM `{PROJECT_ID}.{DATASET}.Table1`
UNION DISTINCT
SELECT DISTINCT 员工ID AS 原_id FROM `{PROJECT_ID}.{DATASET}.Table2`
"""
all_ids = bq_client.query(all_id_query).to_dataframe()["原_id"].tolist()

# 生成映射字典，可自定义新ID生成逻辑，此处示例为从1开始的连续整数
id_mapping = {old_id: idx + 1 for idx, old_id in enumerate(all_ids)}

# 处理第一张表
t1_df = bq_client.query(f"SELECT * FROM `{PROJECT_ID}.{DATASET}.Table1`").to_dataframe()
t1_df["员工ID"] = t1_df["员工ID"].map(id_mapping)
# 写回BigQuery，指定新表名，避免覆盖原表
t1_df.to_gbq(f"{DATASET}.Table1_新ID", project_id=PROJECT_ID, if_exists="replace")

# 处理第二张表，复用同一个映射字典，保证规则一致
t2_df = bq_client.query(f"SELECT * FROM `{PROJECT_ID}.{DATASET}.Table2`").to_dataframe()
t2_df["员工ID"] = t2_df["员工ID"].map(id_mapping)
t2_df.to_gbq(f"{DATASET}.Table2_新ID", project_id=PROJECT_ID, if_exists="replace")

注意事项 #

• 正式操作前建议先采样小批量数据测试，验证两张表的关联关系是否正常
• 若表数据量超过10GB，不建议全量读取到Colab内存，优先选择BigQuery原生SQL方案
• 如需保留原表数据，不要直接覆盖原表，将替换结果写入新表即可

内容的提问来源于stack exchange，提问作者André Cordeiro