シーケンスの使用

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

重要

Snowflakeでは、ギャップのないシーケンス番号の生成は保証されません。生成される数字は必ずしも連続しているとは限りません。

このトピックの内容:

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

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

  • シーケンスによって生成されるすべての値は、シーケンス間隔の符号が変わらない限り(例: ステップサイズの変更)、グローバルで一意です。同時クエリで同じ値が観察されることはなく、1つのクエリ内の値は常に区別されます。

  • シーケンス間隔を正から負に(例: 1 から -1 に)、またはその逆に変更すると、重複の発生する可能性があります。たとえば、最初のクエリがシーケンス値 12、および 3 を返し、間隔が 1 から -1 に変更された場合、生成される次のいくつかの値には、 2 と以前に生成された 1 が含まれます。

  • Snowflakeは、次のシーケンス番号がリクエストされるまで待つのではなく、現在のシーケンス番号が使用されるとすぐにシーケンスの次の値を計算する場合があります。

    その結果として、 ALTER SEQUENCE ... SET INCREMENT ... コマンドはシーケンスを使用する次の操作に影響を与えない可能性があります。例については、 シーケンスを逆方向にすることの影響を理解 をご参照ください。

  • 生成された各シーケンス値は、「ステップ」とも呼ばれるシーケンス間隔に応じて、値を追加で予約します。予約された値は、次のシーケンス値の範囲です。

    <値>  +  (sign(<ステップ>) * abs(<ステップ>))  -  (sign(<ステップ>) * 1)

    (すべてを含む)。

    したがって、値 100 が生成される場合は、次のようになります。

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

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

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

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

  • 次の条件が当てはまる 場合に、シーケンスによって生成される値は、以前のステートメントによって生成された最大値より大きく(ステップサイズが負の場合は最小値より小さく)なります。

    • シーケンスが NOORDER プロパティを持たない。

      NOORDER は、値が昇順であることが保証されないことを指定します。

      たとえば、シーケンスに START 1 INCREMENT 2がある場合、生成される値は 131015103 などです。

      NOORDER は、複数の挿入操作を同時に実行する必要がある場合(例: 複数のクライアントが複数の INSERT ステートメントを実行する場合)に、パフォーマンスを向上させることができます。

    • 現在のステートメントを送信する前に、以前のステートメントが完了し、承認を受けていること。

    この動作は、間隔の符号が変更された場合(正から負、または負から正)には あてはまりません

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

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

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

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

CREATE OR REPLACE SEQUENCE seq1;

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

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

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;
Copy

このクエリは、 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 |
+------+------+
Copy

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

非正規化データのインジェスチョンと正規化

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);
    
    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.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     |
 +----+------+--------+----------------------+
Copy

シーケンスの変更

シーケンスを逆方向にすることの影響を理解

次の例は、シーケンスの方向を逆にしたときに何が起こるかを示しています。

これは、シーケンス値の事前計算により、 ALTER SEQUENCE コマンドの実行後にシーケンスを 2回 使用した後にのみ ALTER 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
    );
Copy

テーブルをロードします。

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

j にシーケンス値を表示します。

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

シーケンスの増分(ステップサイズ)を変更します。

ALTER SEQUENCE test_sequence_wraparound_low SET INCREMENT = -4;
Copy

さらに2つの行を挿入します。

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

シーケンス値を表示します。ALTER SEQUENCE の後に挿入される最初の行の値は、 -1 ではなく 4 であることに注意してください。ALTER SEQUENCE の後に挿入される2番目の行は、新しいステップサイズを考慮に入れています。

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