时间戳

首页 > 解决方案 > 获取插入到雪花数据仓库中的行的标识

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

问题描述

如果我有一个带有自动递增 ID 列的表，我希望能够在该表中插入一行，并获取我刚刚创建的行的 ID。我知道，一般而言，StackOverflow 问题需要某种尝试或研究工作的代码，但我不确定从 Snowflake 开始的地方。我已经翻阅了他们的文档，但对此一无所获。

到目前为止，我能做的最好的事情是 try result_scan()and last_query_id()，但是这些并没有给我任何有关插入的行的相关信息，只是确认插入了一行。

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

是否有适用于 MS SQL Server 的 Snowflake 等效功能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我刚刚插入的这一行的自动增量。

标签： sqlsnowflake-cloud-data-platform

解决方案

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

原始答案

雪花不提供SCOPE_IDENTITY今天的等价物。

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

这是一个例子：

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开始，有点超前
  • Q2开始
  • Q11使用IDENTITY列中的值创建一行
  • Q22使用IDENTITY列中的值创建一行
  • Q2领先Q1-这是关键部分
  • Q2提交，在时间被标记为完成T2
  • Q1提交，在时间被标记为完成T1

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

解决方法可能是unique identifier给每个添加一个INSERT（例如从一个单独的 SEQUENCE 对象），然后使用一个MAX.

对不起。分布式事务很难:)

推荐阅读