sql - 获取雪花数据仓库中插入的行的标识

Question

如果我有一个带有自动递增 ID 列的表，我希望能够在该表中插入一行，并获取我刚刚创建的行的 ID。我知道，一般来说，StackOverflow 问题需要某种尝试过的代码或研究工作，但我不确定从哪里开始使用 Snowflake。我已经挖掘了他们的documentation我对此一无所获。

到目前为止我能做的最好的就是尝试 result_scan()和last_query_id() ，但这些并没有为我提供有关已插入行的任何相关信息，只是确认已插入行。

我相信我所要求的是 MS SQL Server 的 SCOPE_IDENTITY() 的内容功能。

MS SQL Server 的 SCOPE_IDENTITY() 是否有 Snowflake 等效函数？

编辑:为了在这里有代码:

CREATE TABLE my_db..my_table
(
    ROWID INT IDENTITY(1,1),
    some_number INT,
    a_time TIMESTAMP_LTZ(9),
    b_time TIMESTAMP_LTZ(9),
    more_data VARCHAR(10)
);
INSERT INTO my_db..my_table
(
    some_number,
    a_time,
    more_data
)
VALUES
(1, my_time_value, some_data);

我想要自动增量ROWID对于我刚刚插入的这一行。

Answer 1

注意:在某些极少数情况下，下面的答案可能不是 100% 正确，请参阅下面的更新部分

原始答案

Snowflake 目前不提供与 SCOPE_IDENTITY 等效的功能。

但是，您可以利用 Snowflake 的 time travel在执行给定语句后立即检索列的最大值。

这是一个例子:

create or replace table x(rid int identity, num int);
insert into x(num) values(7);
insert into x(num) values(9);
-- you can insert rows in a separate transaction now to test it
select max(rid) from x AT(statement=>last_query_id());
----------+
 MAX(RID) |
----------+
 2        |
----------+

如果您想稍后访问它，您还可以将 last_query_id() 保存到变量中，例如

insert into x(num) values(5);
set qid = last_query_id();
...
select max(rid) from x AT(statement=>$qid);

注意 - 它通常是正确的，但如果用户例如手动向 rid 插入较大的值，可能会影响本次查询的结果。

更新

注意，我意识到上面的代码很少会生成错误的答案。

由于像 Snowflake 这样的分布式系统中查询的各个阶段的执行顺序可能是不确定的，并且 Snowflake 允许并发 INSERT 语句，因此可能会发生以下情况

• 两个查询，Q1 和 Q2，执行简单的单行 INSERT，大致在同一时间开始
• 第一季度开始，有点领先
• 第二季度开始
• Q1 从 IDENTITY 列创建值为 1 的行
• Q2 从 IDENTITY 列创建值为 2 的行
• Q2 领先于 Q1 - 这是关键部分
• Q2 提交，在 T2 时间标记为完成
• Q1 提交，在时间 T1 标记为完成

请注意，T1 晚于 T2。现在，当我们尝试执行 SELECT ... AT(statement=>Q1) 时，我们将看到 T1 的状态，包括之前语句的所有更改，因此包括来自 Q2 的值 2。这不是我们想要的。

解决方法可能是为每个 INSERT 添加一个唯一标识符(例如，来自单独的 SEQUENCE 对象)，然后使用 MAX.

抱歉。分布式事务很难:)