使用序列¶

序列语义¶

Snowflake 序列目前使用以下语义：

• 只要序列间隔的符号不改变（如改变步长），序列生成的所有值都是全局唯一的。并发查询绝不会观察到相同的值，而单个查询中的值始终是不同的。

• 将序列间隔从正值改为负值（例如，从 1 到 -1），可能会导致重复，反之亦然。例如，如果第一次查询返回序列值 1、 2 和 3，然后将间隔从 1 更改为 -1，则接下来生成的几个值中会包括之前生成的 2 和 1。

• Snowflake 可能会在当前序列号使用完毕后立即计算序列的下一个值，而不是等到请求下一个序列号时才进行计算。

  这样做的结果是， ALTER SEQUENCE ... SET INCREMENT ... 命令可能不会影响使用该序列的下一个操作。有关示例，请参阅 了解逆转序列方向的影响。

• 每个生成的序列值都会根据序列间隔（也称为“步长”）额外保留值。保留值的范围从序列值到

  <value>  +  (sign(<step>) * abs(<step>))  -  (sign(<step>) * 1)

  （含）。

  因此，如果生成的值为 100：

  • 当步长为 2 时，则保留值 100 和 101。

  • 当步长为 10 时，则保留值 100 至 109。

  • 当步长为 -5 时，则保留值 96 至 100。

  只要 从不 修改步长/间隔，序列就不会生成保留值。

• 如果 满足以下条件，则序列生成的值大于前一条语句生成的最大值（如果步长为负值，则小于前一条语句生成的最小值）：

  • 序列没有 NOORDER 属性。

    NOORDER 指定不保证值按递增顺序排列。

    例如，如果序列中有 START 1 INCREMENT 2，生成的值可能是 1、3、101、5、103 等。

    NOORDER 可以在需要同时执行多个插入操作时提高性能（例如，在多个客户端执行多个 INSERT 语句时）。

  • 在提交当前语句之前，已完成前一条语句并收到确认。

  如果间隔的符号发生更改（正变为负或负变为正），则此行为 不 成立。

系统无法保证序列中的值是连续的（无间隙），也无法保证序列值是按照特定顺序分配的。事实上，要将序列中的值按指定顺序分配给行，只能使用单行语句（这仍然不能保证值之间无间隙）。

序列值可以表示 64 位二进制整数（-2^63 至 2^63 - 1）。如果序列中下一个值的内部表示超出此范围（在任一方向上），则会出现错误，导致查询失败。请注意，这可能会导致序列值 丢失。

在这种情况下，必须使用较小（量级）的增量值或创建具有较小起始值的新序列。由于值之间可能会出现间隙，即使返回的序列值都在允许范围内，下一个值的内部表示也可能会超出允许范围。Snowflake 不提供关于如何避免此错误的明确保证，但 Snowflake 支持正确提供唯一值的序列对象。使用起始值 1 和增量值 1 创建的序列对象极不可能超出允许的序列值范围。

引用序列¶

使用序列创建默认列值¶

序列可以在表中使用，以生成表列的主键。下面的工具提供了一种执行此操作的简单方法。

引入和规范化非规范数据¶

可考虑使用具有 people 和 contact 两个表的架构：

• people 表包括：

  • 主键唯一标识符： id

  • 两个字符串列： firstName 和 lastName

• contact 表包括：

  • 主键唯一标识符： id

  • 将此联系人条目链接到人员的外键： p_id

  • 两个字符串列：

    • c_type：联系人的类型（例如“电子邮件地址”或“电话”）。

    • data：实际联系信息。

这种格式的数据在引入或处理半结构化数据时，通常会被非规范化。

此示例说明了引入 JSON 数据、对其进行去规范化以提取所需的数据，以及在将数据插入表时对其进行规范化。同时，在保持表中各行之间的预期关系的同时，必须在行上创建唯一标识符。我们可以使用序列来实现这一点。

1. 首先，我们设置示例中使用的表和序列：

   -- primary data tables
   
   CREATE OR REPLACE TABLE people (id number, firstName string, lastName string);
   CREATE OR REPLACE TABLE contact (id number, p_id number, c_type string, data string);
   
   -- sequences to produce primary keys on our data tables
   
   CREATE OR REPLACE SEQUENCE people_seq;
   CREATE OR REPLACE SEQUENCE contact_seq;
   
   -- staging table for json
   
   CREATE OR REPLACE TABLE input (json variant);
   

   Copy

2. 下一步，我们从表 json 中插入数据：

   INSERT INTO input SELECT parse_json(
   '[
    {
      firstName : \'John\',
      lastName : \'Doe\',
      contacts : [
        {
          contactType : \'phone\',
          contactData : \'1234567890\',
        }
        ,
        {
          contactType : \'email\',
          contactData : \'jdoe@acme.com\',
        }
       ]
      }
   ,
     {
      firstName : \'Mister\',
      lastName : \'Smith\',
      contacts : [
        {
          contactType : \'phone\',
          contactData : \'0987654321\',
        }
        ,
        {
          contactType : \'email\',
          contactData : \'msmith@acme.com\',
        }
        ]
      }
    ,
      {
      firstName : \'George\',
      lastName : \'Washington\',
      contacts : [
        {
          contactType : \'phone\',
          contactData : \'1231231234\',
        }
        ,
        {
          contactType : \'email\',
          contactData : \'gwashington@acme.com\',
        }
      ]
    }
   ]'
   );
   

   Copy

3. 然后，我们对 JSON 进行解析和扁平化处理，为每个人员和联系人条目生成唯一标识符，并在插入数据的同时保留人员和联系人条目之间的关系：

   INSERT ALL
     WHEN 1=1 THEN
       INTO contact VALUES (c_next, p_next, contact_value:contactType, contact_value:contactData)
     WHEN contact_index = 0 THEN
       INTO people VALUES (p_next, person_value:firstName, person_value:lastName)
   
   SELECT * FROM
   (
     SELECT f1.value person_value, f2.value contact_value, f2.index contact_index, p_seq.NEXTVAL p_next, c_seq.NEXTVAL c_next
     FROM input, LATERAL FLATTEN(input.json) f1, TABLE(GETNEXTVAL(people_seq)) p_seq,
       LATERAL FLATTEN(f1.value:contacts) f2, table(GETNEXTVAL(contact_seq)) c_seq
   );
   

   Copy

4. 这将生成以下数据（唯一的 IDs 可能会更改）：

   SELECT * FROM people;
   
   +----+-----------+------------+
   | ID | FIRSTNAME | LASTNAME   |
   |----+-----------+------------|
   |  1 | John      | Doe        |
   |  2 | Mister    | Smith      |
   |  3 | George    | Washington |
   +----+-----------+------------+
   
   SELECT * FROM contact;
   
   +----+------+--------+----------------------+
   | ID | P_ID | C_TYPE | DATA                 |
   |----+------+--------+----------------------|
   |  1 |    1 | phone  | 1234567890           |
   |  2 |    1 | email  | jdoe@acme.com        |
   |  3 |    2 | phone  | 0987654321           |
   |  4 |    2 | email  | msmith@acme.com      |
   |  5 |    3 | phone  | 1231231234           |
   |  6 |    3 | email  | gwashington@acme.com |
   +----+------+--------+----------------------+
   

   Copy

可以看到，行与行之间是关联的，并且可以在 people.id 和 contact.p_id 之间连接。

如果添加了其他数据，新行将继续接收唯一的 IDs。例如：

 TRUNCATE TABLE input;

 INSERT INTO input SELECT PARSE_JSON(
 '[
  {
    firstName : \'Genghis\',
    lastName : \'Khan\',
    contacts : [
      {
        contactType : \'phone\',
        contactData : \'1111111111\',
      }
      ,
      {
        contactType : \'email\',
        contactData : \'gkahn@acme.com\',
      }
   ]
 }
,
 {
    firstName : \'Julius\',
    lastName : \'Caesar\',
    contacts : [
      {
        contactType : \'phone\',
        contactData : \'2222222222\',
      }
      ,
      {
        contactType : \'email\',
        contactData : \'gcaesar@acme.com\',
      }
    ]
  }
 ]'
 );

 INSERT ALL
   WHEN 1=1 THEN
     INTO contact VALUES (c_next, p_next, contact_value:contactType, contact_value:contactData)
   WHEN contact_index = 0 THEN
     INTO people VALUES (p_next, person_value:firstName, person_value:lastName)
 SELECT * FROM
 (
   SELECT f1.value person_value, f2.value contact_value, f2.index contact_index, p_seq.NEXTVAL p_next, c_seq.NEXTVAL c_next
   FROM input, LATERAL FLATTEN(input.json) f1, table(GETNEXTVAL(people_seq)) p_seq,
     LATERAL FLATTEN(f1.value:contacts) f2, table(GETNEXTVAL(contact_seq)) c_seq
 );

 SELECT * FROM people;

 +----+-----------+------------+
 | ID | FIRSTNAME | LASTNAME   |
 |----+-----------+------------|
 |  4 | Genghis   | Khan       |
 |  5 | Julius    | Caesar     |
 |  1 | John      | Doe        |
 |  2 | Mister    | Smith      |
 |  3 | George    | Washington |
 +----+-----------+------------+

 SELECT * FROM contact;

 +----+------+--------+----------------------+
 | ID | P_ID | C_TYPE | DATA                 |
 |----+------+--------+----------------------|
 |  1 |    1 | phone  | 1234567890           |
 |  2 |    1 | email  | jdoe@acme.com        |
 |  3 |    2 | phone  | 0987654321           |
 |  4 |    2 | email  | msmith@acme.com      |
 |  5 |    3 | phone  | 1231231234           |
 |  6 |    3 | email  | gwashington@acme.com |
 |  7 |    4 | phone  | 1111111111           |
 |  8 |    4 | email  | gkahn@acme.com       |
 |  9 |    5 | phone  | 2222222222           |
 | 10 |    5 | email  | gcaesar@acme.com     |
 +----+------+--------+----------------------+

Copy

更改序列¶

CREATE OR REPLACE SEQUENCE test_sequence_wraparound_low
   START = 1
   INCREMENT = 1
   ;

CREATE or replace TABLE test_seq_wrap_low (
    i int,
    j int default test_sequence_wraparound_low.nextval
    );

INSERT INTO test_seq_wrap_low (i) VALUES
     (1),
     (2),
     (3);

SELECT * FROM test_seq_wrap_low ORDER BY i;
+---+---+
| I | J |
|---+---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+

ALTER SEQUENCE test_sequence_wraparound_low SET INCREMENT = -4;

INSERT INTO test_seq_wrap_low (i) VALUES
    (4),
    (5);

SELECT * FROM test_seq_wrap_low ORDER BY i;
+---+---+
| I | J |
|---+---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 0 |
+---+---+