Using Sequences¶

Sequence Semantics¶

Snowflake sequences currently utilize the following semantics:

• All values generated by a sequence are globally unique as long as the sign of the sequence interval does not change (e.g. by changing the step size). Concurrent queries never observe the same value, and values within a single query are always distinct.

• Changing the sequence interval from positive to negative (e.g. from 1 to -1), or vice versa may result in duplicates. For example, if the first query(s) return sequence values 1, 2, and 3, and if the interval is changed from 1 to -1, then the next few values generated include 2, and 1, which were generated previously.

• Snowflake may calculate the next value for a sequence as soon as the current sequence number is used, rather than waiting until the next sequence number is requested.

  A consequence of this is that an ALTER SEQUENCE ... SET INCREMENT ... command might not affect the next operation that uses the sequence. For an example, see Understanding the Effects of Reversing the Direction of a Sequence.

• Each generated sequence value additionally reserves values depending on the sequence interval, also called the “step”. The reserved values span from the sequence value to

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

  (inclusive).

  Thus, if the value 100 is generated:

  • With a step of 2, values 100 and 101 are reserved.

  • With a step of 10, values 100 to 109 are reserved.

  • With a step of -5, values 96 to 100 are reserved.

  A reserved value is never generated by the sequence as long as the step/interval is never modified.

• Values generated by a sequence are greater than the maximum value produced by a previous statement (or less than the minimum value if the step size is negative) if the following are true:

  • The sequence does not have the NOORDER property.

    NOORDER specifies that the values are not guaranteed to be in increasing order.

    For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.

    NOORDER can improve performance when multiple INSERT operations are performed concurrently (for example, when multiple clients are executing multiple INSERT statements).

  • The previous statement completed, and an acknowledgment was received, prior to submitting the current statement.

  This behavior does not hold if the sign of the interval is changed (positive to negative or negative to positive).

There is no guarantee that values from a sequence are contiguous (gap-free) or that the sequence values are assigned in a particular order. There is, in fact, no way to assign values from a sequence to rows in a specified order other than to use single-row statements (this still provides no guarantee about gaps).

A sequence value can represent a 64-bit two’s complement integer (-2^63 to 2^63 - 1). If the internal representation of a sequence’s next value exceeds this range (in either direction) an error results and the query fails. Note that this may result in losing these sequence values.

In this situation, you must either use a smaller (in magnitude) increment value or create a new sequence with a smaller start value. As gaps may occur, the internal representation of the next value may exceed the allowable range even if the returned sequence values are all within the allowable range. Snowflake does not provide an explicit guarantee regarding how to avoid this error, but Snowflake supports sequence objects that correctly provide unique values. A sequence object created with a start value of 1 and an increment value of 1 is extremely unlikely to exhaust the allowable range of sequence values.

Referencing Sequences¶

Using Sequences to Create Default Column Values¶

Sequences can be used in tables to generate primary keys for table columns. The following tools provide a simple way to do this.

Ingesting and Normalizing Denormalized Data¶

Consider a schema with two tables, people and contact:

• The people table contains:

  • A primary key unique identifier: id

  • Two string columns: firstName and lastName

• The contact table contains:

  • A primary key unique identifier: id

  • A foreign key linking this contact entry to a person: p_id

  • Two string columns:

    • c_type: The type of contact (e.g. ‘email’ or ‘phone’).

    • data: The actual contact information.

Data in this format frequently is denormalized for ingestion or while processing semi-structured data.

This example illustrates ingesting JSON data, denormalizing it to extract the desired data, and normalizing the data as it is inserted into tables. At the same time, it is important to create unique identifiers on rows while maintaining the intended relationships across rows of tables. We accomplish this with sequences.

1. First, we set up the tables and sequences used in the example:

   -- 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. Next, we insert data from table 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. Then, we parse and flatten the JSON, generate unique identifiers for each person and contact entry, and insert the data while preserving relationships between people and contact entries:

   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. This produces the following data (unique IDs may change):

   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

As you can see, rows are linked, and can be joined, between people.id and contact.p_id.

If additional data is added, new rows continue to receive unique IDs. For example:

 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

Altering a Sequence¶

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 |
+---+---+