Utilisation de séquences¶

Sémantique de séquence¶

Les séquences Snowflake utilisent actuellement la sémantique suivante :

• Toutes les valeurs générées par une séquence sont globalement uniques tant que le signe de l’intervalle de séquence ne change pas (par exemple, en modifiant la taille de l’étape). Les requêtes simultanées n’observent jamais la même valeur et les valeurs d’une même requête sont toujours distinctes.

• Le fait de changer l’intervalle de la séquence de positif à négatif (par exemple, de 1 à -1), ou vice versa, peut entraîner des doublons. Par exemple, si la ou les premières requêtes renvoient les valeurs de séquence 1, 2 et 3, et si l’intervalle passe de 1 à -1, les prochaines valeurs générées comprennent 2 et 1, qui ont été générées précédemment.

• Snowflake peut calculer la valeur suivante pour une séquence dès que le numéro de séquence actuel est utilisé, plutôt que d’attendre que le numéro de séquence suivant soit demandé.

  Une conséquence de ceci est qu’une commande ALTER SEQUENCE ... SET INCREMENT ... peut ne pas affecter l’opération suivante qui utilise la séquence. Pour un exemple, voir Comprendre les effets de l’inversion du sens d’une séquence.

• Chaque valeur de séquence générée réserve en outre des valeurs en fonction de l’intervalle de séquence, également appelées « étapes ». Les valeurs réservées vont de la valeur de séquence jusqu’à

  <valeur>  +  (sign(<étape>) * abs(<étape>))  -  (sign(<étape>) * 1)

  (inclusivement).

  Ainsi, si la valeur 100 est générée :

  • Avec une étape de 2, les valeurs 100 et 101 sont réservées.

  • Avec une étape de 10, les valeurs 100 à 109 sont réservées.

  • Avec une étape de -5, les valeurs 96 à 100 sont réservées.

  Une valeur réservée n’est jamais générée par la séquence tant que l’étape/l’intervalle n’est jamais modifié(e).

• Les valeurs générées par une séquence sont garanties supérieures à la valeur maximale produite par une instruction précédente (ou inférieures à la valeur minimale si la taille de l’étape est négative) si les conditions suivantes sont réunies :

  • La séquence n’a pas la propriété NOORDER.

  • L’instruction précédente a été complétée et un accusé de réception a été reçu avant la soumission de l’instruction actuelle.

  Ce comportement ne tient pas si le signe de l’intervalle est modifié (positif à négatif ou négatif à positif).

Il n’y a aucune garantie que les valeurs d’une séquence sont contiguës (sans discontinuité) ou que les valeurs de la séquence sont affectées dans un ordre particulier. En fait, il n’y a aucun moyen d’affecter des valeurs d’une séquence à des lignes dans un ordre spécifié autre qu’en utilisant des instructions à une ligne (ce qui n’offre également aucune garantie quant à la discontinuité).

Une valeur de séquence peut représenter un entier de complément à deux 64 bits (-2^63 à 2^63 - 1). Si la représentation interne de la valeur suivante d’une séquence dépasse cette plage (dans les deux sens), une erreur se produira et la requête échouera. Notez que cela peut avoir comme conséquence la perte de ces valeurs de séquence.

Dans ce cas, vous devez soit utiliser une valeur d’incrément plus petite (en magnitude), soit créer une nouvelle séquence avec une valeur de départ plus petite. Comme des écarts peuvent se produire, la représentation interne de la valeur suivante peut dépasser la plage autorisée même si les valeurs de la séquence renvoyée se situent toutes dans la plage autorisée. Snowflake ne fournit pas de garantie explicite sur la façon d’éviter cette erreur, mais Snowflake prend en charge les objets de séquence qui fournissent correctement des valeurs uniques. Il est très peu probable qu’un objet de séquence créé avec une valeur de départ de 1 et une valeur d’incrément de 1 épuise la plage de valeurs de séquence autorisée.

Référencement de séquences¶

Utilisation de séquences pour créer des valeurs de colonne par défaut¶

Les séquences peuvent être utilisées dans des tables pour générer des clés primaires pour des colonnes de table. Les outils suivants offrent un moyen simple de réaliser cette opération.

Intégration et normalisation de données dénormalisées¶

Considérons un schéma avec deux tables, people et contact :

• La table people contient :

  • Un identificateur unique de clé primaire : id

  • Deux colonnes de chaîne : firstName et lastName

• La table contact contient :

  • Un identificateur unique de clé primaire : id

  • Une clé étrangère reliant cette entrée de contact à une personne : p_id

  • Deux colonnes de chaîne :

    • c_type : le type de contact (par exemple, « e-mail » ou « téléphone »).

    • data : les informations de contact réelles.

Les données dans ce format sont souvent dénormalisées en vue d’être intégrées, ou lors du traitement de données semi-structurées.

Cet exemple illustre l’intégration de données JSON, les dénormalisant pour en extraire les données souhaitées, et les normalisant à mesure qu’elles sont insérées dans les tables. Par ailleurs, il est important de créer des identificateurs uniques sur les lignes tout en maintenant les relations prévues entre les lignes de table. Nous accomplissons cela avec des séquences.

1. Tout d’abord, nous définissons les tables et les séquences utilisées dans l’exemple :

   -- 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. Ensuite, nous insérons les données de la 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. Ensuite, nous analysons et aplatissons les JSON, générons des identificateurs uniques pour chaque personne et chaque entrée de contact, et insérons les données tout en préservant les relations entre les personnes et les entrées de contact :

   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. Ceci produit les données suivantes (les IDs uniques peuvent changer) :

   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

Comme vous pouvez le voir, les lignes sont liées, et peuvent être jointes entre people.id et contact.p_id.

Si des données supplémentaires sont ajoutées, les nouvelles lignes continuent de recevoir un des IDs uniques. Par exemple :

 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

Modification d’une séquence¶

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