CREATE HYBRID TABLE¶

Syntaxe¶

CREATE [ OR REPLACE ] HYBRID TABLE [ IF NOT EXISTS ] <table_name>
  ( <col_name> <col_type>
    [
      {
        DEFAULT <expr>
          /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
        | { AUTOINCREMENT | IDENTITY }
          [
            {
              ( <start_num> , <step_num> )
              | START <num> INCREMENT <num>
            }
          ]
          [ { ORDER | NOORDER } ]
      }
    ]
    [ NOT NULL ]
    [ inlineConstraint ]
    [ , <col_name> <col_type> [ ... ] ]
    [ , outoflineIndex ]
    [ , ... ]
  )
  [ COMMENT = '<string_literal>' ]

Où :

inlineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ] } }
  [ <constraint_properties> ]

Copy

Important

• Une table hybride doit avoir une contrainte de clé primaire.

• Les contraintes de clé étrangère sont appliquées aux tables hybrides.

• Les contraintes de clé unique et de clé étrangère créent chacune leur propre index sous-jacent. L’index permet de stocker des données supplémentaires. Les index peuvent être définis au moment de la création de la table, soit en définissant des contraintes uniques, soit en définissant des contraintes de clé étrangère, soit en définissant des index. La définition de l’index hors ligne ci-dessous a été étendue pour permettre la création d’index de couverture.

• Les index ne peuvent être définis que sur des colonnes qui ne sont pas semi-structurées (c’est-à-dire variante, objet, tableau). Les colonnes incluses sont soumises aux mêmes restrictions. Cela est dû aux contraintes d’espace associées aux moteurs de stockage sous-jacents pour la clé de chaque enregistrement. Les colonnes incluses ne peuvent être spécifiées que lors de la création d’une table.

outoflineIndex ::=
  INDEX <index_name> ( <col_name> [ , <col_name> , ... ] )

Copy

Pour plus de détails sur les contraintes en ligne, voir CREATE | ALTER TABLE … CONSTRAINT.

Pour plus d’informations sur les limitations concernant les contraintes des tables hybrides, voir Fonctionnalités non prises en charge et limitations concernant les tables hybrides.

Syntaxe des variantes¶

CREATE HYBRID TABLE … AS SELECT (également appelé CTAS)

Crée une nouvelle table hybride contenant les résultats d’une requête sur une autre table hybride :

CREATE [ OR REPLACE ] HYBRID TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , ... ) ]
  AS SELECT <query>
  [ ... ]

Copy

Note

Lorsque vous utilisez CTAS pour créer une table hybride, définissez explicitement le schéma de la table, y compris la clé primaire, les index et les contraintes. Ne vous fiez pas à la déduction du schéma à partir d’une instruction SELECT.

CREATE HYBRID TABLE … LIKE

Crée une nouvelle table hybride avec les mêmes définitions de colonnes qu’une table hybride existante, mais sans copier les données de la table existante.

Les noms de colonnes, les types, les valeurs par défaut et les contraintes sont copiés dans la nouvelle table :

CREATE [ OR REPLACE ] HYBRID TABLE <table_name> LIKE <source_table>
  [ ... ]

Copy

Note

Actuellement, CREATE HYBRID TABLE … LIKE ne prend en charge qu’une autre table hybride en tant que type de table source.

CREATE HYBRID TABLE … LIKE pour une table avec une séquence d’auto-incrémentation à laquelle on accède par un partage de données est actuellement non pris en charge.

Paramètres requis¶

name

Indique l’identificateur (c’est-à-dire le nom) de la table ; doit être unique pour le schéma dans lequel la table est créée.

De plus, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (par exemple, "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus d’informations, consultez Exigences relatives à l’identificateur.

col_name

Indique l’identificateur de colonne (c’est-à-dire le nom). Toutes les exigences relatives aux identificateurs de table s’appliquent également aux identificateurs de colonne.

Pour plus de détails, voir Exigences relatives à l’identificateur et Mots clés réservés et limités.

Note

En plus des mots clés réservés standard, les mots clés suivants ne peuvent pas être utilisés comme identificateurs de colonnes, car ils sont réservés aux fonctions de contexte standard ANSI :

• CURRENT_DATE

• CURRENT_ROLE

• CURRENT_TIME

• CURRENT_TIMESTAMP

• CURRENT_USER

Pour la liste des mots clés réservés, voir Mots clés réservés et limités.

col_type

Spécifie le type de données pour la colonne.

Pour plus de détails sur les types de données qui peuvent être spécifiés pour les colonnes de la table, voir Référence de types de données SQL.

query

Requis si vous utilisez AS SELECT (ex. CTAS)

Chaîne qui spécifie l’instruction SELECT utilisée pour remplir la table.

Paramètres facultatifs¶

DEFAULT ... ou . AUTOINCREMENT ...

Indique si une valeur par défaut est automatiquement insérée dans la colonne lorsqu’une valeur n’est pas spécifiée explicitement via une instruction INSERT ou CREATE HYBRID TABLE AS SELECT :

DEFAULT expr

La valeur par défaut de la colonne est définie par l’expression spécifiée, qui peut être l’une des suivantes :

• Valeur constante.

• Expression simple.

• Référence de séquence (seq_name.NEXTVAL).

Une expression simple est une expression qui affiche une valeur scalaire ; cependant, l’expression ne peut pas contenir de références aux éléments suivants :

• Sous-requêtes.

• Agrégats.

• Des fonctions de fenêtre.

• Fonctions externes.

{ AUTOINCREMENT | IDENTITY } . [ { ( start_num , step_num ) | START num INCREMENT num } ] . [ { ORDER | NOORDER } ]

Quand AUTOINCREMENT est utilisé, la valeur par défaut de la colonne commence par un nombre spécifié et chaque valeur successive augmente automatiquement. Les valeurs générées par une colonne AUTOINCREMENT sont garanties uniques. La différence entre n’importe quelle paire de valeurs générées est garantie comme étant un multiple du montant de l’incrément.

Les paramètres facultatifs ORDER et NOORDER indiquent si les valeurs générées fournissent ou non des garanties d’ordre comme spécifié dans Sémantique de séquence. NOORDER est l’option par défaut pour les colonnes AUTOINCREMENT des tables hybrides. L’option NOORDER offre généralement de meilleures performances pour les écritures ponctuelles.

Ces paramètres ne peuvent être utilisés qu’avec des colonnes avec des types de données numériques.

AUTOINCREMENT et IDENTITY sont synonymes. Si l’une ou l’autre est spécifiée pour une colonne, Snowflake utilise une séquence pour générer les valeurs de la colonne. Pour plus d’informations sur les séquences, voir Utilisation de séquences.

La valeur par défaut pour le démarrage et les paliers/incréments est de 1.

Par défaut : aucune valeur (la colonne n’a pas de valeur par défaut)

Note

• DEFAULT et AUTOINCREMENT s’excluent mutuellement ; une seule des deux peut être spécifiée pour une colonne.

• Pour les charges de travail sensibles aux performances, NOORDER est l’option recommandée pour les colonnes AUTOINCREMENT.

CONSTRAINT ...

Définit une contrainte en ligne pour la ou les colonnes spécifiées dans la table.

Pour plus d’informations sur la syntaxe, voir CREATE | ALTER TABLE … CONSTRAINT. Pour plus d’informations sur les contraintes, voir Contraintes.

COMMENT = 'string_literal'

Spécifie un commentaire pour la table.

Par défaut : aucune valeur

Notes sur l’utilisation¶

• Une table hybride doit avoir une contrainte de clé primaire.

• Pour recréer ou remplacer une table hybride, appelez la fonction GET_DDL pour voir la définition de la table hybride avant d’exécuter une commande CREATE [ OR REPLACE ].

• Un schéma ne peut pas contenir de tables et/ou de vues portant le même nom. Lors de la création d’une table :

  • Si une vue portant le même nom existe déjà dans le schéma, une erreur est renvoyée et la table n’est pas créée.

  • Si une table portant le même nom existe déjà dans le schéma, une erreur est renvoyée et la table n’est pas créée, sauf si le mot clé facultatif OR REPLACE est inclus dans la commande.

  Important

  Utiliser OR REPLACE équivaut à utiliser DROP TABLE sur la table existante, puis à créer une nouvelle table avec le même nom.

  Notez que les actions de destruction et de création se produisent en une seule opération atomique. Cela signifie que toutes les requêtes simultanées à l’opération CREATE OR REPLACE TABLE utilisent soit l’ancienne soit la nouvelle version de la table.

  La recréation ou le remplacement d’une table entraîne la destruction de ses données de modification.

• Comme les mots clés réservés, les noms de fonctions réservés ANSI (CURRENT_DATE, CURRENT_TIMESTAMP, etc.) ne peuvent pas être utilisés comme noms de colonnes.

• CREATE HYBRID TABLE … AS SELECT (CTAS) :

  • Si les alias des noms de colonnes de la liste SELECT sont des colonnes valides, les définitions de colonnes ne sont pas requises dans l’instruction CTAS ; si elles sont omises, les noms et types de colonnes sont déduits de la requête sous-jacente :

    CREATE HYBRID TABLE <table_name> AS SELECT ...
    

    Copy

    Alternativement, les noms peuvent être explicitement spécifiés à l’aide de la syntaxe suivante :

    CREATE HYBRID TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
    

    Copy

    Le nombre de noms de colonnes spécifiés doit correspondre au nombre d’éléments de la liste SELECT de la requête ; les types de colonnes sont déduits des types produits par la requête.

  • Si vous voulez que la table soit créée avec des lignes dans un ordre spécifique, utilisez une sous-clause ORDER BY dans la clause SELECT du CTAS.

  • Bien que vous puissiez déterminer le schéma d’une table à partir de l’instruction CTAS, il est recommandé de spécifier le schéma de manière explicite.

• Concernant les métadonnées :

  Attention

  Les clients doivent s’assurer qu’aucune donnée personnelle (autre que pour un objet utilisateur), donnée sensible, donnée à exportation contrôlée ou autre donnée réglementée n’est saisie comme métadonnée lors de l’utilisation du service Snowflake. Pour plus d’informations, voir Champs de métadonnées dans Snowflake.

• Index :

  • Vous pouvez définir un index pour une table hybride au moment de la création en définissant des contraintes uniques, des contraintes de clé étrangère ou des index. Notez que la commande CREATE HYBRID TABLE permet de définir un paramètre outoflineIndex. Pour plus d’informations sur la création d’un index pour une table hybride, voir CREATE INDEX.

  • En raison des contraintes d’espace associées aux moteurs de stockage sous-jacents pour la clé de chaque enregistrement, vous ne pouvez définir des index que sur des colonnes qui ne sont pas semi-structurées (Variante, Objet, Tableau).

Utiliser des colonnes incluses¶

L’utilisation de colonnes incluses avec un index secondaire peut être particulièrement utile lorsque vous avez des requêtes fréquentes dans lesquelles un ensemble de colonnes est présent dans la liste de projection mais pas dans la liste des prédicats. Par exemple :

-- Create the employee table with a covering index
CREATE HYBRID TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name STRING,
    employee_department STRING,
    INDEX idx_department (employee_department) INCLUDE (employee_name)
);

-- Sample data insertion
INSERT INTO employee VALUES
  (1, 'John Doe', 'Marketing'),
  (2, 'Jane Smith', 'Sales'),
  (3, 'Bob Johnson', 'Finance'),
  (4, 'Alice Brown', 'Marketing');

-- Example Queries using covering index
SELECT employee_name FROM employee WHERE employee_department = 'Marketing';
SELECT employee_name FROM employee WHERE employee_department in ('Marketing', 'Sales');

Dans cet exemple, les deux requêtes SELECT employee_name FROM employee WHERE employee_department = 'Marketing'; et SELECT employee_name FROM employee WHERE employee_department in ['Marketing','Sales']; bénéficieront de l’index couvrant en évitant les consultations de la table de base. Il convient toutefois de noter que l’utilisation de colonnes incluses dans les index peut entraîner une augmentation de la consommation de stockage, étant donné que des colonnes supplémentaires seront stockées dans l’index secondaire.

Exemples¶

Créer une table hybride dans la base de données actuelle avec une contrainte unique sur email et un index secondaire sur full_name :

CREATE HYBRID TABLE mytable (
  customer_id INT AUTOINCREMENT PRIMARY KEY,
  full_name VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  extended_customer_info VARIANT,
  INDEX index_full_name (full_name)
);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

Insérer une ligne dans la table créée dans l’exemple précédent :

INSERT INTO mytable (customer_id, full_name, email, extended_customer_info)
  SELECT 100, 'Jane Doe', 'jdoe@gmail.com',
    parse_json('{"address": "1234 Main St", "city": "San Francisco", "state": "CA", "zip":"94110"}');

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

La clé primaire doit être unique. Par exemple, si vous essayez d’insérer une deuxième fois la même clé primaire que dans l’exemple précédent, vous obtenez l’erreur suivante :

200001 (22000): Primary key already exists

L’adresse e-mail doit également respecter la contrainte UNIQUE en ligne. Par exemple, si vous tentez d’insérer deux enregistrements avec la même adresse e-mail, l’instruction échoue avec l’erreur suivante :

Duplicate key value violates unique constraint "SYS_INDEX_MYTABLE_UNIQUE_EMAIL"

Affichez les propriétés et les métadonnées de la table. Notez la valeur de la colonne is_hybrid :

SHOW TABLES LIKE 'mytable';

+-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
| created_on                    | name    | database_name | schema_name | kind  | is_hybrid | comment | cluster_by | rows | bytes | owner  | retention_time | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external |
|-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------|
| 2022-02-23 23:53:19.707 +0000 | MYTABLE | MYDB          | PUBLIC      | TABLE | Y         |         |            | NULL |  NULL | MYROLE | 10             | OFF                  | OFF             | OFF                 |                         NULL |                      NULL | N           |
+-------------------------------+---------+---------------+-------------+-------+-----------+---------+------------+------+-------+--------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+

Affichez les détails de toutes les tables hybrides :

SHOW HYBRID TABLES;

+-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------+
| created_on                    | name                      | database_name | schema_name | owner        | datastore_id | rows | bytes | comment |
|-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------|
| 2022-02-24 02:07:31.877 +0000 | MYTABLE                   | DEMO_DB       | PUBLIC      | ACCOUNTADMIN |         2002 | NULL |  NULL |         |
+-------------------------------+---------------------------+---------------+-------------+--------------+--------------+------+-------+---------+

Affichez les informations sur les colonnes de la table :

DESC TABLE mytable;

+-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name              | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| CUSTOMER_ID       | NUMBER(38,0) | COLUMN | N     | NULL    | Y           | N          | NULL  | NULL       | NULL    | NULL        |
| FULL_NAME         | VARCHAR(256) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| APPLICATION_STATE | VARIANT      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+-------------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

Sélectionnez des données à partir de la table :

SELECT customer_id, full_name, email, extended_customer_info FROM mytable
  WHERE extended_customer_info['state'] = 'CA';

+-------------+-----------+----------------+------------------------------+
| CUSTOMER_ID | FULL_NAME | EMAIL          | EXTENDED_CUSTOMER_INFO       |
|-------------+-----------+----------------+------------------------------|
|         100 | Jane Doe  | jdoe@gmail.com | {                            |
|             |           |                |   "address": "1234 Main St", |
|             |           |                |   "city": "San Francisco",   |
|             |           |                |   "state": "CA",             |
|             |           |                |   "zip": "94110"             |
|             |           |                | }                            |
+-------------+-----------+----------------+------------------------------+