如何根据GCS存储的映射文件自动更新BigQuery表数据

我有一份存储在GCS的映射文件和一张BigQuery表，映射文件会频繁变动，需要实现映射文件更新时，BigQuery表的Values字段同步按映射规则更新。

数据结构 #

映射表df_mapping结构 #

BigQuery表BQ_Table结构 #

每次映射文件变更时触发对应函数，读取BigQuery表中除Value列外的所有数据，再读取更新后的映射文件，按Id字段左连接得到更新后的values字段，随后删除旧的BigQuery表，再插入新拼接的完整数据。

现有代码 #

query =  """
            SELECT
            Id,
            Country,
            Sales,
            Value
            FROM `project.dataset.tbl` 
        """

bqclient = bigquery.Client()
df = (
    bqclient.query(query)
    .result()
    .to_dataframe(create_bqstorage_client=True)
    )

df_mapping = pd.read_csv("gs://path/mapping.csv")
df_final = pd.merge(df, df_mapping, on='Id', how='left')
-- 不确定删表再插入数据的操作是否安全

• 删除旧表后，插入新数据的过程中可能出现报错
• 待处理的数据量较大，约有100万行
• 方案可扩展性差
• 存在数据丢失的风险

方案1：使用BigQuery外部表直接关联映射文件（推荐，无需同步更新） #

该方案完全不需要修改BQ原生表的Values字段，查询时直接关联GCS上的映射文件即可，永远自动取最新的映射规则：

1. 首先在BigQuery中创建映射文件的外部表，直接绑定GCS的csv路径：

CREATE OR REPLACE EXTERNAL TABLE `project.dataset.mapping_ext`
OPTIONS (
  format = 'CSV',
  uris = ['gs://path/mapping.csv'],
  skip_leading_rows = 1, -- csv文件含表头时添加该配置
  field_delimiter = ','
);

2. 后续所有查询需要用到Values字段时，直接用BQ原生表左关联外部表即可，永远取最新的映射值：

SELECT 
  a.Id,
  a.Country,
  a.Market,
  a.Sales,
  b.Values
FROM `project.dataset.tbl` a
LEFT JOIN `project.dataset.mapping_ext` b
ON a.Id = b.Id

优势：

• 完全不需要额外的同步逻辑，映射文件更新后外部表自动读取最新内容，无额外延迟
• 无数据丢失风险，不需要修改原表的任何数据
• 性能不受100万行数据量影响，BigQuery关联操作效率极高
• 可扩展性强，后续映射文件加字段只需要修改外部表定义即可

方案2：使用BigQuery DML UPDATE语句增量更新原表（需同步但更安全） #

如果业务要求必须把最新的Values字段固化到原表中，不要用删表重建的方式，直接用UPDATE语句批量更新即可：

1. 先把更新后的映射文件加载到一张临时BQ表project.dataset.mapping_temp
2. 执行UPDATE语句批量更新原表的Values字段：

UPDATE `project.dataset.tbl` a
SET a.Values = b.Values
FROM `project.dataset.mapping_temp` b
WHERE a.Id = b.Id;

优势：

• 原子操作，要么全部更新成功要么失败，不会出现中间状态数据丢失
• 不需要全量读取导出数据再导入，100万行数据更新耗时仅需几秒
• 不需要删除原表，不会影响原表的正常访问

方案3：用视图封装关联逻辑 #

如果不想每次查询都写关联语句，可以直接创建一个视图代替原表的访问：

CREATE OR REPLACE VIEW `project.dataset.tbl_with_latest_mapping`
AS
SELECT 
  a.Id,
  a.Country,
  a.Market,
  a.Sales,
  b.Values
FROM `project.dataset.tbl` a
LEFT JOIN `project.dataset.mapping_ext` b
ON a.Id = b.Id

后续所有业务直接访问这个视图即可，自动使用最新的映射值。

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