Utilisation de séquences

Les séquences sont utilisées pour générer des nombres uniques dans les sessions et les instructions, y compris des instructions simultanées. Elles peuvent être utilisées pour générer des valeurs pour une clé primaire ou n’importe quelle colonne qui nécessite une valeur unique.

Important

Snowflake ne garantit pas la génération de numéros de séquence sans espaces. Les numéros générés ne sont pas nécessairement contigus.

Dans ce chapitre :

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.

      NOORDER précise que l’ordre croissant des valeurs n’est pas garanti.

      Par exemple, si une séquence comporte START 1 INCREMENT 2, les valeurs générées peuvent être 1, 3, 101, 5, 103, etc.

      NOORDER peut améliorer les performances lorsque plusieurs opérations d’insertion doivent être effectuées simultanément (par exemple, lorsque plusieurs clients exécutent plusieurs instructions INSERT).

    • 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

currval non pris en charge

De nombreuses bases de données fournissent une référence de séquence currval. Cependant, Snowflake n’en fournit pas. currval dans d’autres systèmes est généralement utilisé pour créer des relations clé primaire-étrangère entre les tables : une première instruction insère une ligne unique dans la table de faits en utilisant une séquence pour créer une clé. Les instructions suivantes insèrent des lignes dans les tables de dimension en utilisant currval pour se référer à la clé de la table de faits.

Ce modèle est contraire aux bonnes pratiques de Snowflake. Les requêtes en masse devraient être privilégiées par rapport aux petites requêtes à une seule ligne. Pour plus d’efficacité, la tâche peut être accomplie en utilisant l’insertion multi-tables INSERT et des références de séquence dans des sous-requêtes imbriquées. Voir Intégration et normalisation de données dénormalisées (dans ce chapitre) pour un exemple détaillé.

Séquences en tant qu’expressions

Les séquences sont accessibles dans les requêtes en tant qu’expressions du formulaire. seq_name.NEXTVAL. Chaque occurrence d’une séquence génère un ensemble de valeurs distinctes. Cela est différent de ce que fournissent de nombreuses autres bases de données, où plusieurs références à NEXTVAL d’une séquence renvoient la même valeur pour chaque ligne.

Par exemple, la requête suivante renvoie des valeurs distinctes pour les colonnes a et b :

CREATE OR REPLACE SEQUENCE seq1;

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

Pour renvoyer deux colonnes avec la même valeur de séquence générée, utilisez des sous-requêtes et des vues imbriquées :

CREATE OR REPLACE SEQUENCE seq1;

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

Les sous-requêtes imbriquées génèrent autant de valeurs de séquence distinctes que de lignes renvoyées par la sous-requête (ainsi une référence de séquence dans un bloc de requête avec plusieurs liaisons ne se réfère à aucun des objets joints, mais à la sortie du bloc de requête). Ces valeurs générées peuvent ne pas être prises en compte si les lignes associées sont filtrées ultérieurement, ou si les valeurs peuvent être prises en compte deux fois (comme dans l’exemple ci-dessus) si la colonne de séquence ou la vue de ligne sont mentionnées plusieurs fois.

Note

Pour les insertions multi-tables, les valeurs d’insertion peuvent être fournies dans les clauses VALUES et la sortie SELECT :

  • Les clauses VALUES se référant à une valeur de séquence aliasée à partir de l’entrée SELECT reçoivent la même valeur.

  • Les clauses VALUES contenant une référence directe à une séquence NEXTVAL reçoivent des valeurs distinctes.

En revanche, Oracle limite les références de séquence uniquement aux clauses VALUES.

Séquences en tant que fonctions de table

Les requêtes imbriquées avec des références de séquence sont souvent difficiles à comprendre et verbeuses. Toute référence partagée (où deux colonnes d’une ligne devraient recevoir la même valeur de séquence) nécessite un niveau supplémentaire d’imbrication des requêtes. Pour simplifier la syntaxe des requêtes imbriquées, Snowflake fournit une méthode supplémentaire pour générer des séquences en utilisant la fonction de table GETNEXTVAL, comme dans l’exemple suivant :

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 est une fonction spéciale de table à une ligne qui génère une valeur unique (et joint cette valeur) aux autres objets de l’instruction SELECT. Un appel à GETNEXTVAL doit être aliasé, sinon les valeurs générées ne peuvent pas être référencées. Plusieurs colonnes peuvent se référer à une valeur générée en accédant à cet alias. L’alias GETNEXTVAL contient un attribut également nommé NEXTVAL.

La fonction table GETNEXTVAL permet en outre un contrôle précis de la génération de séquences lorsque plusieurs tables sont assemblées. L’ordre des objets dans la clause FROM détermine où les valeurs sont générées. Les valeurs de séquence sont générées selon le résultat des liaisons entre tous les objets énumérés avant GETNEXTVAL dans la clause FROM. Les lignes résultantes sont ensuite reliées aux objets à droite. Il existe une dépendance latérale implicite entre GETNEXTVAL et tous les autres objets de la clause FROM. Les liaisons ne peuvent pas être réorganisées autour de GETNEXTVAL. Il s’agit d’une exception dans SQL, car l’ordre des objets n’affecte généralement pas la sémantique de la requête.

Considérons l’exemple suivant avec les tables t1, t2, t3, et t4 :

CREATE OR REPLACE SEQUENCE seq1;

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

Cette requête va joindre t1 à t2, générer une valeur unique du résultat, puis joindre la relation résultante avec t3 et t4. L’ordre des liaisons entre la relation post-séquence, t3, et t4 n’est pas spécifié, car les liaisons internes sont associatives.

Note

Cette sémantique peut être complexe. Nous recommandons d’utiliser GETNEXTVAL à la fin de la clause FROM lorsque cela est possible et pertinent afin d’éviter toute confusion.

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.

Expressions de colonne par défaut

L’expression de colonne par défaut peut être une référence de séquence. L’omission de la colonne dans une instruction d’insertion ou le réglage de la valeur sur DEFAULT dans une instruction d’insertion ou de mise à jour génère une nouvelle valeur de séquence pour la ligne.

Par exemple :

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

L’avantage d’utiliser des séquences comme valeur de colonne par défaut est que la séquence peut être référencée dans d’autres emplacements, et même être la valeur par défaut pour plusieurs colonnes et dans plusieurs tables. Si une séquence est nommée comme expression par défaut d’une colonne, puis détruite, toute tentative d’insertion/de mise à jour de la table à l’aide de la valeur par défaut entraînera une erreur indiquant que l’identificateur ne peut être trouvé.

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

Comprendre les effets de l’inversion du sens d’une séquence

L’exemple suivant montre ce qui se passe lorsque vous inversez le sens d’une séquence.

Cela montre également qu’en raison du précalcul des valeurs de la séquence, une commande ALTER SEQUENCE peut sembler ne prendre effet qu’après la seconde utilisation de la séquence après l’exécution de la commande ALTER SEQUENCE.

Créez la séquence et utilisez-la comme valeur par défaut pour une colonne dans un tableau :

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

Charger le tableau :

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

Afficher les valeurs de la séquence dans la colonne j :

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

Modifier l’incrément (taille du palier) de la séquence :

ALTER SEQUENCE test_sequence_wraparound_low SET INCREMENT = -4;
Copy

Insérer deux lignes supplémentaires :

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

Afficher les valeurs de la séquence. Notez que la première ligne insérée après le ALTER SEQUENCE a la valeur 4 et non -1. La deuxième ligne insérée après ALTER SEQUENCE tient compte de la nouvelle taille de palier.

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