如何在BigQuery中实现类似Oracle序列的自动递增列？

嘿，我来帮你搞定这个问题！针对你每日加载百万级数据、需要持续自动递增编号的需求，我整理了几个实用的方案，比你之前尝试的方法更贴合场景：

方案一：日期前缀+每日自增计数器（适合批量每日加载） #

这个方案简单直接，完美匹配你每日加载数据的模式，编号会基于历史最大值持续递增：

步骤1：获取当前表的最大编号 #

先查询现有表的最大ID，如果是首次加载就默认设为0：

DECLARE max_id INT64;
SET max_id = (SELECT IFNULL(MAX(auto_increment_id), 0) FROM `your-project.your-dataset.your-target-table`);

步骤2：加载当日数据并生成递增编号 #

用ROW_NUMBER()加上历史最大值，生成当日的连续编号：

INSERT INTO `your-project.your-dataset.your-target-table` (auto_increment_id, col1, col2, ...)
SELECT 
  max_id + ROW_NUMBER() OVER () AS auto_increment_id,
  source_col1, source_col2, ... -- 替换成你的源数据列
FROM `your-project.your-dataset.daily-source-table`;

补充：避免重复加载的幂等性处理

如果担心每日加载重试导致编号重复，可以用MERGE语句代替INSERT，先校验源数据是否已存在：

MERGE `your-project.your-dataset.your-target-table` AS target
USING (
  SELECT 
    max_id + ROW_NUMBER() OVER () AS auto_increment_id,
    source_col1, source_col2, ..., unique_key -- 用一个唯一键判断是否已加载
  FROM `your-project.your-dataset.daily-source-table`
) AS source
ON target.unique_key = source.unique_key
WHEN NOT MATCHED THEN
  INSERT (auto_increment_id, col1, col2, ..., unique_key)
  VALUES (source.auto_increment_id, source.source_col1, source.source_col2, ..., source.unique_key);

方案二：使用BigQuery内置序列（预览版，最接近Oracle序列） #

BigQuery现在提供了和Oracle序列几乎一致的CREATE SEQUENCE功能（预览阶段），支持原子性的递增，适合并发加载场景：

步骤1：创建序列 #

CREATE SEQUENCE `your-project.your-dataset.your-sequence`
START WITH 1 -- 初始值
INCREMENT BY 1 -- 每次递增1
MINVALUE 1
MAXVALUE 9223372036854775807 -- INT64的最大值
CYCLE FALSE; -- 不循环，到最大值后停止

步骤2：插入数据时调用序列 #

每次插入直接调用NEXT VALUE FOR获取下一个递增编号：

INSERT INTO `your-project.your-dataset.your-target-table` (auto_increment_id, col1, col2, ...)
SELECT 
  NEXT VALUE FOR `your-project.your-dataset.your-sequence` AS auto_increment_id,
  source_col1, source_col2, ...
FROM `your-project.your-dataset.daily-source-table`;

这个方案的优势是完全模拟Oracle序列的行为，支持多并发写入时的原子性，不会出现重复编号，百万级数据加载性能也没问题。

方案三：日期+自增字符串编号（备选，适合不需要纯数字的场景） #

如果你的业务允许编号是字符串格式，可以用日期前缀+当日自增的方式，既保证递增，又能直观看到数据加载日期：

INSERT INTO `your-project.your-dataset.your-target-table` (auto_increment_id, col1, col2, ...)
SELECT 
  CONCAT(FORMAT_DATE('%Y%m%d', CURRENT_DATE()), '-', ROW_NUMBER() OVER ()) AS auto_increment_id,
  source_col1, source_col2, ...
FROM `your-project.your-dataset.daily-source-table`;

生成的编号类似20240520-1、20240520-2... 次日自动变成20240521-1，同样满足递增需求。

注意事项 #

• 百万级数据量下，ROW_NUMBER()在BigQuery中是高效的，因为它是分布式计算，不用担心性能瓶颈
• 如果需要严格的全局连续递增，方案二的内置序列是最优选择；如果只是每日批量加载，方案一更简单易维护
• 无论用哪个方案，都要确保源数据是增量的，避免重复插入导致编号混乱

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