雪花云数据平台MERGE命令会导致序列号出现空白

Question

我试图利用 MERGE 命令来填充 Snowflake 中的维度。为了实现代理键，我创建了一个默认为序列号的列，每当插入新行时，该序列号都会自动递增。我在其他数据仓库平台中尝试了类似的方法，但它从未引起任何问题。但是，我注意到，每当我在 Snowflake 中使用 MERGE 命令时，MERGE 命令处理的每一行的序列号都会递增，无论它是否会导致 UPDATE 或 INSERT 操作。

以下是我所指的简单示例：

-- Sequence
CREATE OR REPLACE SEQUENCE seq1 START=1 INCREMENT=1;
-- Source table
CREATE OR REPLACE TABLE source_table
(
row_key int,
row_value string
);
-- Target table: Column ID uses the sequence
CREATE OR REPLACE TABLE target_table 
(
id int DEFAULT seq1.nextval,
row_key int,
row_value string
);
-- Initial data
INSERT INTO source_table VALUES 
(1,'One'),
(2,'Two'),
(3,'Three');
MERGE INTO target_table D 
USING source_table s 
ON D.row_key=s.row_key
WHEN MATCHED AND D.row_value!=s.row_value THEN UPDATE SET row_value=s.row_value 
WHEN NOT MATCHED THEN INSERT(row_key,row_value) VALUES (s.row_key,s.row_value);

运行这些命令后，输出表将包含以下行：

编号，ROW_KEY，ROW_VALUE
1,1，一
2,2，二
3,3，三

现在，让我们插入一个新行并再次运行相同的合并命令：

INSERT INTO source_table VALUES
(4,'Four');
MERGE INTO target_table D 
USING source_table s 
ON D.row_key=s.row_key
WHEN MATCHED AND D.row_value!=s.row_value THEN UPDATE SET row_value=s.row_value 
WHEN NOT MATCHED THEN INSERT(row_key,row_value) VALUES (s.row_key,s.row_value);

这一次，表的输出如下所示： 编号，ROW_KEY，ROW_VALUE
1,1，一
2,2，二
3,3，三
7,4，四

如果我插入另一行，下一个 MERGE 命令将插入其 ID 设置为 12 的新行，并且同样如此。看起来 MERGE 命令会递增它从源表中读取的每一行的序列号，即使它们最终根本没有插入到目标表中。

这是故意的吗？我尝试了 IDENTITY 功能而不是序列，它没有改变输出。

我附带的解决方法是将 MERGE 命令替换为多个 UPDATE 和 INSERT 语句，但我仍然想知道这种行为背后的原因。

Answer 1

这是 Snowflake 开发团队正在处理的一个已知问题。如您所提到的，解决方法是将 MERGE 命令替换为多个 UPDATE 和 INSERT 语句。

Answer 2

根据 Snowflake文档，Snowflake 不保证序列中没有间隙。 https://docs.snowflake.net/manuals/user-guide/querying-sequences.html。

Answer 3

您可能在其他事务数据库(Oracle，SQL Server(上执行此操作。 如果您在仓库/分析数据库(如 Netezza (上执行此操作，您还会发现类似的序列行为;这是因为这些系统是为速度和批量处理而构建的;因此，它获得一大块序列值，可能会也可能不会使用。 这确实留下了空白;但是考虑到序列和工作流程的最大值，您是否会在 30 或 300 年内达到天花板？ 可以说两者都不在乎。

这些分析数据库通常具有更高的固有成本来简单地运行任何查询;这在事务数据库中非常小。 因此，他们可以在每次需要序列值时简单地要求序列值(没有孔！( - 您可以通过进行单个插入很容易看到很大的差异 - 您可能已经知道 Snowflake 不鼓励这样做。 这里有一个简单的测试：创建一个表并制作 200 个插入语句，每个语句插入一行。 在您的笔记本电脑上的 mysql 中运行它;在中型雪花(或 XS，但只是说明一点(上运行它 - 笔记本电脑上的 mysql 只是为了这个特定的测试而粉碎雪花;因为它是它被设计来做的事情。 单个插入的时间差异会很大，您会看到即使只做一小批 200 行，累积的速度也会有多快。

请注意，合并本身是一个漂亮的事务命令，在这些类型的数据库上也并不总是得到支持。 简单地自己做单个操作可能会更快，也可能不会更快;如前所述，您最终可能会在单独的新文件运行之间留下漏洞，但是，在您的单个操作中，能够期望没有间隙地分配顺序序列。

update target from source where business key exists in target;
insert into target from soure where business key not exists in target;

更新实际上是删除+插入，如果您以某种序列 - 业务键映射保留序列，您也可以简化(也许加快？

insert sequence, key into map where key in source and not in target;
begin;
delete from target where key exists in source and target;
insert source joined to map on key to retrieve sequence into target;
commit;

如果实际更新会变得更加丑陋，可能值得考虑。 (也是一个奇怪的速度实验(

Answer 4

从文档中看，Snowflake 不保证生成没有间隙的序列号。序列将在达到数据类型的最大正整数值后环绕。

您可以尝试使用 row_number(( 作为解决方法 https://docs.snowflake.net/manuals/sql-reference/functions/row_number.html