シーケンスの使用

シーケンスは、同時ステートメントを含むセッションおよびステートメント全体で一意の番号を生成するために使用されます。主キーまたは一意の値を必要とする列の値を生成するために使用できます。

重要

Snowflakeでは、ギャップのないシーケンス番号の生成は保証されません。

データ型の正の最大整数値に達した後、シーケンスはラップアラウンドします。

このトピックの内容:

シーケンスのセマンティクス

Snowflakeのシーケンスは現在、次のセマンティクスを利用しています。

  • シーケンスによって生成されるすべての値は、シーケンス間隔の符号が変わらない限り、グローバルに一意です。同時クエリで同じ値が観察されることはなく、1つのクエリ内の値は常に区別されます。シーケンス間隔を正から負に(例えば、 1 から -1 に)、またはその逆に変更すると、重複する可能性があります(つまり、最初のクエリがシーケンス値 123 を返す場合、間隔が 1 から -1 に変更され、次のステートメントは値 321 を返します)。

  • 生成された各シーケンス値は、シーケンス間隔に応じて、「ステップ」とも呼ばれる値を追加で予約します。予約された値は、シーケンスから <value> + (sign(<step>) * abs(<step>)) に及びます。したがって、値 100 が生成される場合は、次のようになります。

    • ステップが 2 の場合、値 100 および 101 が予約されています。

    • ステップが 10 の場合、値 100109 が予約されています。

    • ステップが -5 の場合、値 96100 が予約されています。

    ステップ/間隔が修正 されない 限り、シーケンスによって予約値が生成されることはありません。

  • シーケンスによって生成される値は、現在のステートメントを送信する前に、前のステートメントが完了し、確認が受信された場合、前のステートメント if によって生成された最大値より大きいことが保証されます。この保証は、間隔の符号が変更された場合(正から負、または負から正)には あてはまりません

シーケンスからの値が連続している(ギャップがない)、またはシーケンス値が特定の順序で割り当てられているという保証はありません。単一行のステートメントを使用する以外に、指定された順序でシーケンスの値を行に割り当てる方法はありません(ギャップについての保証はありません)。

シーケンス値は、64ビットの2の補数整数(-2^63 から 2^63-1)を表すことがあります。シーケンスの次の値の内部表現がこの範囲を超える場合(どちらの方向でも)エラーが発生し、クエリは失敗します。これにより、これらのシーケンス値が 失われる ことに注意してください。

この状況では、より小さい(大きさの)増分値を使用するか、より小さい開始値で新しいシーケンスを作成する必要があります。ギャップが発生する可能性があるため、返されるシーケンス値がすべて許容範囲内であっても、次の値の内部表現が許容範囲を超える場合があります。このエラーを回避する方法に関する明示的な保証は提供していませんが、当社では一意の値を正しく提供するシーケンスオブジェクトのサポートに努めています。開始値 1 および増分値 1 で作成されたシーケンスオブジェクトが、シーケンス値の許容範囲を使い果たすことはありません。

シーケンスの参照

currval はサポートされていません

多くのデータベースは currval シーケンス参照を提供していますが、Snowflakeではサポートされていません。他のシステムの currval は通常、テーブル間のプライマリ外部キー関係を作成するために使用されます。最初のステートメントは、キーを作成するシーケンスを使用して、ファクトテーブルに単一行を挿入します。後続のステートメントは、 currval を使用してディメンションテーブルに行を挿入し、ファクトテーブルのキーを参照します。

このパターンはSnowflakeのベストプラクティスとは逆で、小さな単一行クエリより一括クエリを優先する必要があります。同じタスクは、マルチテーブル INSERT とネストされたサブクエリでのシーケンス参照を使用してより良く達成することができます。詳細な例については、(このトピックの) 非正規化データの取り込みと正規化 をご参照ください。

式としてのシーケンス

シーケンスは、クエリで seq_name.NEXTVAL という形式の式としてアクセスできます。シーケンスが出現するたびに、一連の異なる値が生成されます。これは、シーケンスの NEXTVAL への複数の参照が各行に対して同じ値を返すという、他の多くのデータベースが提供するものとは異なります。

例えば、次のクエリは、列 a および b に異なる値を返します。

CREATE OR REPLACE SEQUENCE seq1;

SELECT seq1.NEXTVAL a, seq1.NEXTVAL b FROM DUAL;

同じ生成されたシーケンス値を持つ2つの列を返すには、ネストされたサブクエリとビューを使用します。

CREATE OR REPLACE SEQUENCE seq1;

SELECT seqRef.a a, seqRef.a b FROM (SELECT seq1.NEXTVAL a FROM DUAL) seqRef;

ネストされたサブクエリは、サブクエリによって返される行と同じ数の異なるシーケンス値を生成します(したがって、複数の結合を持つクエリブロックのシーケンス参照は、結合されたオブジェクトではなく、クエリブロックの出力を参照します)。これらの生成された値は、関連する行が後でフィルターで除外された場合は観察されないか、(上記の例のように)シーケンス列またはインラインビューが複数回参照された場合、値が2回観察されます。

注釈

複数テーブルの挿入の場合、挿入値は VALUES 句と SELECT 入力の両方で提供できます。

  • 入力 SELECT からエイリアスされたシーケンス値を参照する VALUES 句は、同じ値を受け取ります。

  • シーケンス NEXTVAL への直接参照を含む VALUES 句は、異なる値を受け取ります。

対照的に、Oracleはシーケンス参照を VALUES 句のみに制限します。

テーブル関数としてのシーケンス

シーケンス参照を含むネストされたクエリは、理解するのが難しく、冗長であることがよくあります。共有参照(行の2列が同じシーケンス値を受け取る必要がある場合)は、追加レベルのクエリのネストが必要です。ネストされたクエリの構文を簡素化するために、Snowflakeは、次の例のようにテーブル関数 GETNEXTVAL を使用してシーケンスを生成する追加の方法を提供します。

CREATE OR REPLACE SEQUENCE seq1;

CREATE OR REPLACE TABLE foo (n NUMBER);

INSERT INTO foo VALUES (100), (101), (102);

SELECT n, s.nextval FROM foo, TABLE(GETNEXTVAL(seq1)) s;

GETNEXTVAL SELECT は、ステートメント内の他のオブジェクトに一意の値を生成(およびこの値を結合)する特別な1行のテーブル関数です。GETNEXTVAL の呼び出しにはエイリアスが必要です。それ以外の場合、生成された値を参照できません。このエイリアスにアクセスすることにより、複数の列が生成された値を参照する場合があります。GETNEXTVAL エイリアスには、 NEXTVAL という名前の属性が含まれます。

さらに、GETNEXTVAL テーブル関数を使用すると、多くのテーブルを結合するときにシーケンス生成を正確に制御できます。 FROM 句のオブジェクトの順序により、値が生成される場所が決まります。シーケンス値は、FROM 句の GETNEXTVAL より前にリストされているすべてのオブジェクト間の結合の結果に対して生成されます。結果の行は、右側のオブジェクトに結合されます。GETNEXTVAL と FROM 句の他のすべてのオブジェクトとの間には、暗黙的な横方向の依存関係があります。結合は GETNEXTVAL を中心に並べ替えることはできません。通常、オブジェクトの順序はクエリのセマンティクスに影響を与えないため、これは SQL の例外です。

テーブル t1t2t3、および t4 を使用した次の例を考えてみましょう。

CREATE OR REPLACE SEQUENCE seq1;

SELECT t1.*, t2.*, t3.*, t4.*, s.NEXTVAL FROM t1, t2, TABLE(GETNEXTVAL(seq1)) s, t3, t4;

このクエリは、 t1t2 に結合し、結果の一意の値を生成してから、結果の関係を t3 および t4 に対して結合します。内部結合は結合的であるため、シーケンス後の関係、 t3、および t4 間の結合の順序は指定されません。

注釈

これらのセマンティクスには注意が必要です。混乱を避けるため、可能かつ適切な場合は、 FROM 句の最後に GETNEXTVAL を使用することをお勧めします。

シーケンスを使用したデフォルト列値の作成

シーケンスをテーブルで使用して、テーブル列の主キーを生成できます。次のツールは、これを行う簡単な方法を提供します。

列のデフォルト式

列のデフォルト式は、シーケンス参照にすることができます。insertステートメントで列を省略するか、insertまたはupdateステートメントで値を DEFAULT に設定すると、行の新しいシーケンス値が生成されます。

例:

CREATE OR REPLACE SEQUENCE seq1;

CREATE OR REPLACE TABLE foo (k NUMBER DEFAULT seq1.NEXTVAL, v NUMBER);

-- insert rows with unique keys (generated by seq1) and explicit values
INSERT INTO foo (v) VALUES (100);
INSERT INTO foo VALUES (DEFAULT, 101);

-- insert rows with unique keys (generated by seq1) and reused values
-- new keys are distinct from preexisting keys
INSERT INTO foo (v) SELECT v FROM foo;

-- insert row with explicit values for both columns
INSERT INTO foo VALUES (1000, 1001);

SELECT * FROM foo;

+------+------+
|    K |    V |
|------+------|
|    1 |  100 |
|    2 |  101 |
|    3 |  100 |
|    4 |  101 |
| 1000 | 1001 |
+------+------+

シーケンスを列のデフォルト値として使用する利点は、シーケンスを他の場所で参照でき、さらに複数の列および複数のテーブルのデフォルト値になることです。シーケンスに列のデフォルト式として名前を付け、その後、デフォルト値を使用したテーブル挿入/更新の試行を中止すると、識別子が見つからないというエラーが発生します。

非正規化データの取り込みと正規化

peoplecontact の2つのテーブルがあるスキーマを考えてみましょう。

  • people テーブルには次が含まれます。

    • 主キーの一意の識別子: id

    • 2つの文字列の列: firstName および lastName

  • contact テーブルには次が含まれます。

    • 主キーの一意の識別子: id

    • この連絡先エントリを個人にリンクする外部キー: p_id

    • 2つの文字列の列:

      • 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);
    
  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\',
         }
       ]
     }
    ]'
    );
    
  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
    );
    
  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 |
    +----+------+--------+----------------------+
    

ここで見るように、行は people.idcontact.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     |
 +----+------+--------+----------------------+