Verwenden von Sequenzen

Sequenzen werden verwendet, um eindeutige Nummern über Sitzungen und Anweisungen, einschließlich gleichzeitiger Anweisungen, hinweg zu generieren. Sie können verwendet werden, um Werte für einen Primärschlüssel oder für jede Spalte zu generieren, die einen eindeutigen Wert erfordert.

Wichtig

Snowflake garantiert nicht, dass die Generierung von Sequenznummern ohne Lücken erfolgt.

Sequenzen starten von vorn, nachdem der maximale positive ganzzahlige Wert für den Datentyp erreicht wurde.

Unter diesem Thema:

Semantik von Sequenzen

Snowflake-Sequenzen verwenden derzeit die folgende Semantik:

  • Alle von einer Sequenz generierten Werte sind global eindeutig, solange sich das Vorzeichen des Sequenzintervalls nicht ändert. Gleichzeitige Abfragen ermitteln nie den gleichen Wert, und Werte innerhalb einer einzelnen Abfrage sind immer unterschiedlich. Das Ändern des Sequenzintervalls von positiv in negativ (z. B. von 1 in -1) oder umgekehrt kann zu Duplikaten führen (d. h. wenn die erste Abfrage die Sequenzwerte 1, 2, 3 zurückgibt, dann wird das Intervall von 1 in -1 geändert, sodass die nächste Anweisung die Werte 3, 2, 1 zurückgibt).

  • Jeder generierte Sequenzwert reserviert zusätzlich Werte in Abhängigkeit vom Sequenzintervall, auch als „Schrittweite“ bezeichnet. Die reservierten Werte reichen von der Sequenz bis zu <Wert> + (sign(<Schritt>) * abs(<Schritt>)). Wenn also z. B. der Wert 100 generiert wird:

    • Bei einer Schrittweite von 2 werden die Werte 100 und 101 reserviert.

    • Bei einer Schrittweite von 10 werden die Werte 100 bis 109 reserviert.

    • Bei einer Schrittweite von -5 werden die Werte 96 bis 100 reserviert.

    Ein reservierter Wert wird von der Sequenz niemals generiert, solange die Schrittweite bzw. das Intervall nicht geändert wird.

  • Die durch eine Sequenz generierten Werte sind garantiert größer als der Maximalwert, der durch eine vorherige Anweisung generiert wurde, falls die vorherige Anweisung abgeschlossen und eine Bestätigung empfangen wurde, bevor die aktuelle Anweisung übermittelt wurde. Diese Garantie hält nicht, wenn das Vorzeichen des Intervalls geändert wird (positiv in negativ oder negativ in positiv).

Es gibt keine Garantie, dass Werte aus einer Sequenz zusammenhängend (lückenlos) sind oder dass die Sequenzwerte in einer bestimmten Reihenfolge zugeordnet werden. Es gibt in der Tat keine andere Möglichkeit, Zeilen in einer bestimmten Reihenfolge mit Werten aus einer Sequenz zu belegen, als Anweisungen für einzelne Zeilen zu verwenden (dies bietet immer noch keine Garantie für Lücken).

Ein Sequenzwert kann die beiden Komplemente einer 64-Bit-Ganzzahlen darstellen (-2^63 bis 2^63-1). Wenn die interne Darstellung des nächsten Wertes einer Sequenz diesen Bereich überschreitet (in beide Richtungen), kommt es zu einem Fehler und die Abfrage schlägt fehl. Beachten Sie, dass dies dazu führen kann, dass diese Sequenzwerte verloren gehen.

In dieser Situation müssen Sie entweder einen kleineren (in der Größe) Inkrementwert verwenden oder eine neue Sequenz mit einem kleineren Startwert erstellen. Da Lücken auftreten können, kann die interne Darstellung des nächsten Wertes den zulässigen Bereich überschreiten, auch wenn die zurückgegebenen Sequenzwerte alle innerhalb des zulässigen Bereichs liegen. Wir geben keine explizite Garantie, wie dieser Fehler vermieden werden kann, aber wir verpflichten uns, Sequenzobjekte zu unterstützen, die eindeutige Werte korrekt bereitstellen. Ein Sequenzobjekt, das mit einem Startwert von 1 und einem Inkrementwert von 1 erstellt wurde, sollte niemals den zulässigen Bereich von Sequenzwerten ausschöpfen.

Referenzieren von Sequenzen

currval – Nicht unterstützt

Viele Datenbanken bieten eine currval-Sequenzreferenz, Snowflake jedoch nicht. currval wird in anderen Systemen typischerweise verwendet, um Primär-/Fremdschlüsselbeziehungen zwischen Tabellen zu erstellen – eine erste Anweisung fügt eine einzelne Zeile in die Faktentabelle ein, wobei eine Sequenz zum Erstellen eines Schlüssels verwendet wird. Nachfolgende Anweisungen fügen mit currval Zeilen in die Dimensionstabellen ein, um auf den Schlüssel der Faktentabelle zu verweisen.

Dieses Muster steht im Gegensatz zu den Best Practices von Snowflake: Massenabfragen sind kleinen, einzeiligen Abfragen vorzuziehen. Die gleiche Aufgabe kann besser mithilfe von INSERT-Anweisungen für mehrere Tabellen und Sequenzreferenzen in verschachtelten Unterabfragen gelöst werden. Ein detailliertes Beispiel dazu finden Sie unter Aufnehmen und Normalisieren von denormalisierten Daten (unter diesem Thema).

Sequenzen als Ausdrücke

Auf Sequenzen kann in Abfragen als Ausdruck der Form Sequenzname.NEXTVAL zugegriffen werden. Jedes Auftreten einer Sequenz generiert eine Reihe von unterschiedlichen Werten. Dies unterscheidet sich von dem, was viele andere Datenbanken bereitstellen, bei denen mehrere Referenzen auf NEXTVAL einer Sequenz den gleichen Wert für jede Zeile zurückgeben.

Die folgende Abfrage gibt beispielsweise unterschiedliche Werte für die Spalten a und b zurück.

CREATE OR REPLACE SEQUENCE seq1;

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

Wenn Sie zwei Spalten mit dem gleichen generierten Sequenzwert zurückgeben möchten, verwenden Sie verschachtelte Unterabfragen und Ansichten:

CREATE OR REPLACE SEQUENCE seq1;

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

Verschachtelte Unterabfragen generieren so viele unterschiedliche Sequenzwerte wie Zeilen, die von der Unterabfrage zurückgegeben werden (so bezieht sich eine Sequenzreferenz in einem Abfrageblock mit mehreren Verknüpfungen (Joins) nicht auf eines der verbundenen Objekte, sondern auf die Ausgabe des Abfrageblocks). Diese generierten Werte können nicht ermittelt werden, wenn die zugehörigen Zeilen später herausgefiltert werden, oder die Werte können zweimal ermittelt werden (wie im obigen Beispiel), wenn auf die Sequenzspalte oder die Inline-Ansicht mehrfach verwiesen wird.

Bemerkung

Für das Einfügen in mehrere Tabellen können Einfügewerte sowohl in den VALUES-Klauseln als auch in der SELECT-Eingabe angegeben werden:

  • VALUES-Klauseln, die sich auf einen Sequenzwert beziehen, der vom Eingangs-SELECT mit Alias zugeordnet ist, erhalten den gleichen Wert.

  • VALUES-Klauseln, die einen direkten Bezug zu einem Sequenz-NEXTVAL enthalten, erhalten unterschiedliche Werte.

Im Gegensatz dazu sind in Oracle die Sequenzreferenzen auf VALUES-Klauseln beschränkt.

Sequenzen als Tabellenfunktionen

Geschachtelte Abfragen mit Sequenzreferenzen sind oft schwer zu verstehen, denn jede gemeinsame Referenz (bei der zwei Spalten einer Zeile den gleichen Sequenzwert erhalten sollten) erfordert eine zusätzliche Verschachtelungsebene in der Abfrage. Um die Syntax von geschachtelten Abfragen zu vereinfachen, bietet Snowflake mit der Tabellenfunktion GETNEXTVAL eine zusätzliche Methode zum Generieren von Sequenzen, wie im folgenden Beispiel:

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 ist eine spezielle 1-Zeilen-Tabellenfunktion, die einen eindeutigen Wert generiert (und diesen Wert mit anderen Objekten in der SELECT-Anweisung verbindet). Ein Aufruf von GETNEXTVAL muss mit Alias erfolgen, da die generierten Werte sonst nicht referenziert werden können. Durch Zugriff auf diesen Alias können mehrere Spalten auf einen generierten Wert verweisen. Der GETNEXTVAL-Alias enthält ein Attribut, das auch NEXTVAL genannt wird.

Die Tabellenfunktion GETNEXTVAL ermöglicht zusätzlich eine präzise Steuerung der Sequenzgenerierung, wenn viele Tabellen miteinander verknüpft sind. Die Reihenfolge der Objekte in der FROM-Klausel bestimmt, wo Werte generiert werden. Sequenzwerte werden anhand des Ergebnisses von Verknüpfungen (Joins) zwischen allen Objekten generiert, die vor GETNEXTVAL in der FROM-Klausel aufgeführt sind. Die Ergebniszeilen werden dann mit den Objekten auf der rechten Seite verknüpft. Es besteht eine implizite laterale Abhängigkeit zwischen GETNEXTVAL und allen anderen Objekten in der FROM-Klausel. Joins dürfen nicht um GETNEXTVAL neu geordnet werden. Dies ist eine Ausnahme in SQL, da die Reihenfolge der Objekte typischerweise keinen Einfluss auf die Abfragesemantik hat.

Betrachten Sie das folgende Beispiel mit den Tabellen t1, t2, t3 und t4:

CREATE OR REPLACE SEQUENCE seq1;

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

Diese Abfrage verbindet t1 mit t2, generiert einen eindeutigen Wert des Ergebnisses und verbindet dann die resultierende Beziehung mit t3 und t4. Die Reihenfolge der Joins zwischen der Post-Sequenz-Beziehung, t3 und t4 wird nicht angegeben, da innere Joins assoziativ sind.

Bemerkung

Diese Semantik kann schwierig sein. Wir empfehlen, wenn möglich und passend, GETNEXTVAL am Ende der FROM-Klausel zu verwenden, um Verwechslungen zu vermeiden.

Verwenden von Sequenzen zum Erstellen von Standard-Spaltenwerten

Sequenzen können in Tabellen verwendet werden, um Primärschlüssel für Tabellenspalten zu generieren. Die folgenden Tools bieten eine einfache Möglichkeit, dies zu tun.

Standardausdrücke für Spalten

Der Standardausdruck der Spalte kann eine Sequenzreferenz sein. Durch Weglassen der Spalte in einer Insert-Anweisung oder das Setzen des Wertes auf DEFAULT in einer Insert- oder Update-Anweisung wird ein neuer Sequenzwert für die Zeile generiert.

Beispiel:

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

Der Vorteil der Verwendung von Sequenzen als Spaltenstandardwert ist, dass auf die Sequenz an anderer Stelle verwiesen werden kann und die Sequenz sogar der Standardwert für mehrere Spalten und in mehreren Tabellen sein kann. Wenn eine Sequenz als Standardausdruck einer Spalte benannt und anschließend gelöscht wird, führt jeder Versuch, die Tabelle mit dem Standardwert einzufügen/zu aktualisieren, zu einem Fehler, bei dem der Bezeichner nicht gefunden werden kann.

Aufnehmen und Normalisieren von denormalisierten Daten

Betrachten Sie ein Schema mit zwei Tabellen, people und contact:

  • Die Tabelle people enthält:

    • Einen eindeutigen Bezeichner für den Primärschlüssel: id

    • Zwei Spalten mit Zeichenfolgen: firstName und lastName

  • Die Tabelle contact enthält:

    • Einen eindeutigen Bezeichner für den Primärschlüssel: id

    • Einen Fremdschlüssel, der diesen Kontakteintrag mit einer Person verknüpft: p_id

    • Zwei Spalten mit Zeichenfolgen:

      • c_type: Die Art des Kontakts (z. B. „E-Mail“ oder „Telefon“).

      • data: Die eigentlichen Kontaktinformationen.

Daten in diesem Format werden häufig zum Aufnehmen oder bei der Verarbeitung semistrukturierter Daten denormalisiert.

Dieses Beispiel veranschaulicht das Aufnehmen von JSON-Daten, deren Denormalisierung zur Extraktion der gewünschten Daten und die Normalisierung der Daten beim Einfügen in Tabellen. Gleichzeitig ist es wichtig, eindeutige Bezeichner für Zeilen zu erstellen und gleichzeitig die beabsichtigten Beziehungen zwischen den Zeilen der Tabellen aufrechtzuerhalten. Dazu können Sequenzen verwendet werden.

  1. Zuerst werden die im Beispiel verwendeten Tabellen und Sequenzen eingerichtet:

    -- 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. Als Nächstes werden Daten aus der Tabelle json eingefügt:

    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. Danach werden die JSON-Daten analysiert und vereinfacht, eindeutige Bezeichner für jede Person und jeden Kontakteintrag generiert und die Daten eingefügt, wobei die Beziehungen zwischen Personen und Kontakteinträgen erhalten bleiben:

    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. Dadurch ergeben sich folgende Daten (eindeutige IDs können sich ändern):

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

Wie Sie sehen können, sind die Zeilen miteinander verknüpft und können zwischen people.id und contact.p_id verbunden werden.

Wenn zusätzliche Daten hinzugefügt werden, erhalten neue Zeilen weiterhin eindeutige IDs. Beispiel:

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