获取雪花数据仓库中插入的行的标识 [英] Get identity of row inserted in Snowflake Datawarehouse

问题描述

如果我有一个具有自动递增ID列的表，我希望能够在该表中插入一行，并获得我刚刚创建的行的ID。我知道一般来说，StackOverflow问题需要某种尝试或研究工作的代码，但我不确定从Snowflake开始。我翻遍了他们的documentation，但什么也没找到。

到目前为止，我最多只能尝试result_scan()和last_query_id()，但是这两个选项不会为我提供有关插入的行的任何相关信息，只是确认插入了行。

我相信我所要求的是与MS SQL Server的SCOPE_IDENTITY()函数一脉相承的。

MS SQL Server的SCOPE_IDENTITY()是否有雪花等效功能？

编辑：为了在此处放置代码：

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。

推荐答案

注意：在极少数情况下，以下答案可能不是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()，也可以将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开始，稍微领先
• Q2开始
• Q1从IDENTITY列
创建值为1的行
• Q2从IDENTITY列
创建值为2的行
• Q2领先于Q1-这是关键部分
• Q2提交，在时间T2
标记为完成
• Q1提交，在时间T1标记为完成

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

解决方法可能是将unique identifier添加到每个INSERT(例如，从单独的序列对象)，然后使用MAX。

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

这篇关于获取雪花数据仓库中插入的行的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！