Oracle Merge Table场景下生成固定唯一ID的最佳实践咨询
问题解答
能否使用rownum分配唯一ID?
不建议使用rownum实现该需求。rownum是SQL查询结果集的动态临时行号,依赖每次查询的结果排序规则。由于你的源表采用truncate/insert的全量写入逻辑,如果没有固定的全局排序规则,同一条业务数据每次查询对应的rownum都可能发生变化,完全无法保证历史记录的ID固定,不符合你的需求。
该场景的最佳实践
前置前提
首先需要为源表的每条业务数据定义全局唯一且不变的业务主键:
- 如果上游系统已经生成了唯一业务标识(比如层级数据的编码、唯一资源ID等),直接复用即可
- 如果没有现成的业务主键,可以对源表中不会随同步变化的所有业务字段做哈希生成临时主键,例:
md5(concat_ws('|', col1, col2, col3...)) as biz_id,只要同一条业务数据的字段值不变,哈希值就不会变,可以作为匹配依据
目标表结构设计
目标表bi_ods.cat_table1需要包含3类核心字段:
- 业务主键字段(如
biz_id,用于和源表做匹配判断是存量还是增量) - 自增ID字段(如
auto_id,即要求固定不变的ID) - 其余业务字段和源表完全对齐
同步逻辑实现
你之前使用sequence出现ID全量更新的问题,是因为调用nextval的时机不对,不需要给所有行都生成新ID,只需要给未匹配到的新增行分配新ID即可,存量行的ID直接保留原值。
支持merge+sequence的数据库(如Oracle、PostgreSQL等)
直接使用merge语句实现:
MERGE INTO bi_ods.cat_table1 tgt USING bi_ods.table1 src -- 用业务主键做匹配条件 ON (tgt.biz_id = src.biz_id) WHEN MATCHED THEN -- 匹配到的存量数据,仅更新业务字段,ID保持不变 UPDATE SET tgt.col1 = src.col1, tgt.col2 = src.col2, -- 其余需要同步的业务字段 tgt.update_time = SYSDATE WHEN NOT MATCHED THEN -- 仅新增数据调用sequence生成新ID INSERT (auto_id, biz_id, col1, col2, create_time, update_time) VALUES (seq_cat_table1.nextval, src.biz_id, src.col1, src.col2, SYSDATE, SYSDATE);
大数据场景(无sequence支持,如Hive、SparkSQL)
可以先取目标表当前的最大ID,给新增行按行号累加生成新ID:
-- 取目标表当前最大ID WITH max_id AS ( SELECT COALESCE(MAX(auto_id), 0) AS current_max FROM bi_ods.cat_table1 ), -- 筛选本次同步的新增数据 new_data AS ( SELECT src.* FROM bi_ods.table1 src LEFT JOIN bi_ods.cat_table1 tgt ON src.biz_id = tgt.biz_id WHERE tgt.biz_id IS NULL ) -- 存量数据+新增带ID的数据合并写入目标表 INSERT OVERWRITE TABLE bi_ods.cat_table1 SELECT auto_id, biz_id, col1, col2, create_time, update_time FROM bi_ods.cat_table1 UNION ALL SELECT m.current_max + ROW_NUMBER() OVER(ORDER BY n.biz_id) AS auto_id, n.biz_id, n.col1, n.col2, CURRENT_TIMESTAMP() AS create_time, CURRENT_TIMESTAMP() AS update_time FROM new_data n, max_id m;
内容的提问来源于stack exchange,提问作者Ren Cantu
相关产品推荐
相关产品推荐

