Verwenden von Sequenzen¶

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 (z. B. durch Ändern der Schrittweite). 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. Wenn beispielsweise die ersten Abfragen die Sequenzwerte 1, 2 und 3 zurückgeben und das Intervall von 1 auf -1 geändert wird, dann werden als nächste Werte 2 und 1 generiert, die zuvor generiert wurden.

• Snowflake kann den nächsten Wert für eine Sequenz berechnen, sobald die aktuelle Sequenznummer verbraucht ist, und muss nicht warten, bis die nächste Sequenznummer angefordert wird.

  Dies hat zur Folge, dass ein ALTER SEQUENCE ... SET INCREMENT ...-Befehl möglicherweise keine Auswirkungen auf die nächste Operation hat, die diese Sequenz verwendet. Ein Beispiel dazu finden Sie unter Erläuterungen zu den Auswirkungen der Richtungsumkehr einer Sequenz.

• Jeder generierte Sequenzwert reserviert zusätzlich Werte in Abhängigkeit vom Sequenzintervall, auch als „Schrittweite“ bezeichnet. Die reservierten Werte reichen vom Sequenzwert bis

  <Wert>  +  (sign(<Schritt>) * abs(<Schritt>))  -  (sign(<Schritt>) * 1)

  (einschließlich).

  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 größer als der Maximalwert, der durch eine vorherige Anweisung generiert wurde (bzw. kleiner als der Minimalwert bei negativer Schrittweite), falls Folgendes gilt:

  • Die Sequenz weist nicht die Eigenschaft NOORDER auf.

  • Die vorherige Anweisung wurde abgeschlossen, und eine Bestätigung wurde empfangen, bevor die aktuelle Anweisung gesendet wurde.

  Dieses Verhalten gilt 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 hinsichtlich Lücken).

Ein Sequenzwert kann eine 64-Bit-Zweiterkomplement-Ganzzahl 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. Snowflake gibt keine explizite Garantie hinsichtlich einer Möglichkeit zur Vermeidung dieses Fehlers, aber Snowflake unterstützt die Verwendung von Sequenzobjekten, die eindeutige Werte korrekt bereitstellen. Bei einem Sequenzobjekt, das mit einem Startwert von 1 und einem Inkrementwert von 1 erstellt wurde, ist es äußerst unwahrscheinlich, dass der zulässige Bereich von Sequenzwerten ausgeschöpft wird.

Referenzieren von Sequenzen¶

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.

Erfassen 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 Erfassen 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);
   

   Copy

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

   Copy

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

   Copy

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

   Copy

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

Copy

Ändern einer Sequenz¶

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