Langage de définition de données

Utilisez le langage de définition de données (DDL, Data Definition Language) de Cloud Spanner pour :

  • créer une base de données ;
  • créer, modifier ou supprimer des tables dans une base de données ;
  • ajouter, modifier ou supprimer des colonnes dans une table ;
  • créer ou supprimer des index dans une base de données.

Syntaxe DDL

statement:
    { create_database | create_table | create_index | alter_table | drop_table | drop_index }

create_database:
    CREATE DATABASE database_id

create_table:
    CREATE TABLE table_name (
    [column_def, ...] )
    primary_key [, cluster]

column_def:
    column_name {scalar_type | array_type} [NOT NULL] [options_def]

primary_key:
    PRIMARY KEY ( [key_part, ...] )

key_part:
    column_name [{ ASC | DESC }]

cluster:
    INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ]

scalar_type:
    { BOOL | INT64 | FLOAT64 | STRING( length ) | BYTES( length ) | DATE | TIMESTAMP }

length:
    { int64_value | MAX }

array_type:
    ARRAY< scalar_type >

options_def:
  OPTIONS (allow_commit_timestamp = { true | null })

create_index:
    CREATE [UNIQUE] [NULL_FILTERED] INDEX index_name
    ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ]

storing_clause:
    STORING ( column_name [, ...] )

interleave_clause:
    INTERLEAVE IN table_name

alter_table:
    ALTER TABLE table_name { table_alteration | table_column_alteration }

table_alteration:
{ ADD COLUMN column_def | DROP COLUMN column_name |
      SET ON DELETE { CASCADE | NO ACTION } }

table_column_alteration:
    ALTER COLUMN column_name { { scalar_type | array_type } [NOT NULL] | SET options_def }

drop_table:
    DROP TABLE table_name

drop_index:
    DROP INDEX index_name

int64_value:
    { decimal_value | hex_value }

decimal_value:
    [-]0—9+

hex_value:
    [-]0x{0—9|a—f|A—F}+

database_id:
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

table_name, column_name, index_name:
    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

Notation :

  • Les crochets "[]" indiquent des clauses facultatives.
  • Les parenthèses "()" indiquent des parenthèses littérales.
  • La barre verticale "|" indique un opérateur logique "OR".
  • Les accolades "{}" renferment un ensemble d'options.
  • Une virgule suivie de points de suspension indique que l'élément précédent peut être répété dans une liste d'éléments séparés par une virgule. item [, ...] correspond à un ou plusieurs éléments, tandis que [item, ...] représente zéro ou plusieurs éléments.
  • Une virgule "," indique la virgule littérale.
  • Les crochets obliques "<>" indiquent des chevrons littéraux.
  • Un tiret cadratin "—" indique une plage de valeurs délimitée par les éléments situés de chaque côté de celui-ci.
  • Le signe plus "+" indique que l'élément précédent peut être répété.

Mots clés réservés

Certains mots, tels que les noms de type, sont réservés dans le langage DDL de Cloud Spanner. Si vous devez utiliser un mot clé réservé comme identifiant dans votre schéma, placez-le entre guillemets (`). Pour obtenir la liste complète des mots clés réservés dans Cloud Spanner, consultez la page Structure lexicale et syntaxe.

Exemple :

CREATE TABLE MyTable (
  RowId INT64 NOT NULL,
  `Int64` INT64
) PRIMARY KEY (RowId)

Instructions DATABASE

CREATE DATABASE

Lors de la création d'une base de données Cloud Spanner, vous devez fournir une instruction CREATE DATABASE, qui définit l'ID de la base de données :

CREATE DATABASE database_id

database_id:
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

Les ID de base de données :

  • doivent commencer par une lettre minuscule ;
  • peuvent contenir des lettres minuscules, des chiffres, des traits de soulignement et des traits d'union, mais pas de lettres majuscules ;
  • ne peuvent pas se terminer par un trait de soulignement ni un trait d'union ;
  • doivent être entourés de guillemets (`) s'il s'agit d'un mot réservé ou s'ils contiennent un trait d'union ;
  • peuvent contenir entre 2 et 30 caractères ;
  • ne peuvent pas être modifiés après avoir été créés.

Types de données

Scalaires

La syntaxe permettant d'utiliser un type scalaire dans le langage DDL est la suivante :

{ BOOL | INT64 | FLOAT64 | STRING( length ) | BYTES( length ) | DATE | TIMESTAMP }

length:
    { int64_value | MAX }

int64_value:
    { decimal_value | hex_value }

decimal_value:
    [-]0—9+

hex_value:
    [-]0x{0—9|a—f|A—F}+

Les valeurs int64_value :

  • peuvent être comprises entre -9,223,372,036,854,775,808 et 9,223,372,036,854,775,807 ;
  • peuvent être un chiffre hexadécimal, précédé de "0x" (sensible à la casse).

STRING

STRING est une chaîne de caractères Unicode de longueur variable. Sa valeur doit être une chaîne de caractères Unicode valides. La longueur est requise et représente le nombre maximal de caractères Unicode (pas d'octets) pouvant être stockés dans le champ.

Remarques :

  • Les écritures dans la colonne sont soumises à validation et seront rejetées si la nouvelle valeur n'est pas une chaîne Unicode valide ou dépasse la longueur spécifiée.

  • La valeur length peut être un entier compris dans la plage [1, 2621440] (2,5 Mo).

  • Pour un champ dont la longueur est imprévisible ou ne nécessite pas de contrainte, vous pouvez définir la longueur length sur la valeur d'usage MAX, ce qui équivaut à 2621440 à des fins de validation.

    Seule la longueur réelle de la chaîne stockée a une incidence sur les coûts de stockage. La valeur MAX n'utilise pas d'espace de stockage supplémentaire.

  • Cloud Spanner exige que les chaînes Unicode soient encodées au format UTF-8 lors de leur réception sur le serveur.

  • Le classement est effectué par valeur numérique de caractère Unicode (sur un plan technique, la méthode utilisée est celle du point de code, qui diffère légèrement du fait de la combinaison de caractères). Pour les chaînes ASCII, il s'agit de l'ordre de tri traditionnel.

  • Vous pouvez réduire la longueur d'une colonne après la création de la table, mais pour cela Cloud Spanner doit vérifier que les données existantes respectent la contrainte de longueur.

BYTES

BYTES est une chaîne binaire de longueur variable. La longueur est requise et représente le nombre maximal d'octets pouvant être stockés dans le champ.

Remarques :

  • Les écritures dans la colonne sont soumises à validation et seront rejetées si la nouvelle valeur dépasse la longueur spécifiée.

  • La valeur length peut être un entier compris dans la plage [1, 10485760] (10 Mo) ou la valeur d'usage MAX, équivalente à 10485760 à des fins de validation.

    Seuls les octets réellement stockés ont une incidence sur les coûts de stockage. La valeur MAX n'utilise pas d'espace de stockage supplémentaire.

  • Vous pouvez réduire la longueur d'une colonne après la création de la table, mais pour cela Cloud Spanner doit vérifier que les données existantes respectent la contrainte de longueur.

DATE

  • Date indépendante du fuseau horaire.
  • La plage [0001-01-01, 9999-12-31] est l'intervalle de dates légal. Une écriture dans une colonne de date est rejetée si la valeur se trouve en dehors de cet intervalle.
  • Pour plus d'informations à ce sujet et sur le format canonique, consultez la section Types de données.

TIMESTAMP

  • Horodatage avec une précision à la nanoseconde près.
  • Indépendant du fuseau horaire, compris dans la plage [de 0001-01-01 00:00:00 à 10000-01-01 00:00:00].
  • Pour plus d'informations à ce sujet et sur le format canonique, consultez la section Types de données.

ARRAY

La syntaxe permettant d'utiliser le type ARRAY dans le langage DDL est la suivante :

ARRAY< scalar_type >

Cloud Spanner accepte les tableaux de valeurs scalaires. Les tableaux ont pour objectif principal de stocker une série de valeurs de manière à optimiser l'espace. Ils ne sont pas conçus pour fournir un accès individuel à un élément. Pour lire ou écrire un seul élément, vous devez lire ou écrire le tableau entier.

Si votre application utilise des structures de données telles que des vecteurs ou des champs répétés, vous pouvez facilement conserver leur état dans un tableau Cloud Spanner.

Voici un exemple d'une autre définition de la table Singers, qui utilise plusieurs colonnes de type ARRAY :

CREATE TABLE Singers (
  SingerId INT64,
  FeaturedSingerIds ARRAY<INT64>,
  SongNames ARRAY<STRING(MAX)>,
) PRIMARY KEY (SingerId) ...;

Remarques :

  • Les tableaux de sous-type ARRAY (tableaux imbriqués) ne sont pas acceptés.
  • Les tableaux, comme les valeurs scalaires, ne peuvent jamais dépasser 10 Mio au total.
  • Les tableaux ne peuvent pas être utilisés comme colonnes de clé.

Instructions TABLE

CREATE TABLE

Utilisez l'instruction CREATE TABLE pour définir des tables.

CREATE TABLE table_name(
[column_def, ...] )
primary_key [, cluster]

column_def:
    column_name {scalar_type | array_type} [NOT NULL] [options_def]

primary_key:
    PRIMARY KEY ( [key_part, ...] )

key_part:
    column_name [{ ASC | DESC }]

cluster:
    INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ]

table_name and column_name:
    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

options_def:
  OPTIONS (allow_commit_timestamp = { true | null })

Remarques :

  • L'ajout de l'annotation DESC à un nom de colonne de clé primaire modifie la disposition physique des données, passant d'un ordre croissant (par défaut) à un ordre décroissant.

  • INTERLEAVE IN PARENT définit une relation de table "enfant à parent", qui entraîne l'entrelacement physique des lignes parent et enfant. Les colonnes de clé primaire d'un parent doivent correspondre, en termes de nom et de type, à un préfixe des colonnes de clé primaire d'un enfant. L'ajout de lignes à la table enfant échoue si la ligne parent correspondante n'existe pas. La ligne parent peut soit déjà exister dans la base de données, soit être ajoutée plus tôt dans la même transaction qui ajoute une ligne à la table enfant.

  • La clause facultative ON DELETE définit le comportement des lignes dans ChildTable lorsqu'une mutation tente de supprimer la ligne parente. Les options acceptées sont les suivantes :

    • CASCADE : les lignes enfants sont supprimées.
    • NO ACTION : les lignes enfants ne sont pas supprimées. Si la suppression d'un parent laisse des lignes enfants orphelines, violant ainsi l'intégrité du référentiel parent-enfant, l'écriture échouera.

    Vous pouvez omettre la clause ON DELETE, auquel cas la valeur par défaut ON DELETE NO ACTION est utilisée.

  • Une annotation NOT NULL rend une colonne obligatoire pour toutes les mutations qui insèrent une nouvelle ligne. Vous ne pouvez pas ajouter une colonne NOT NULL à une table existante. (Toutefois, pour contourner le problème, vous pouvez ajouter une colonne pouvant accepter la valeur Null, remplir cette colonne avec des écritures sur toutes les lignes, puis mettre à jour votre schéma avec une annotation NOT NULL sur cette colonne.)

  • Les noms de table et de colonne :

    • peuvent contenir entre 1 et 128 caractères ;
    • doivent commencer par une lettre majuscule ou minuscule ;
    • peuvent contenir des lettres majuscules et minuscules, des chiffres et des traits de soulignement, mais pas de traits d'union ;
    • ne sont pas sensibles à la casse. Par exemple, vous ne pouvez pas créer de tables nommées mytable et MyTable dans la même base de données ni de colonnes nommées mycolumn et MyColumn dans la même table.
  • L'option allow_commit_timestamp permet aux opérations d'insertion et de mise à jour de demander à Cloud Spanner d'écrire l'horodatage du commit de la transaction dans la colonne. Pour plus d'informations, consultez la page Horodatages de commit.

ALTER TABLE

Utilisez l'instruction ALTER TABLE pour modifier des définitions de table.

ALTER TABLE table_name { table_alteration | table_column_alteration }

table_alteration:
{ ADD COLUMN column_def | DROP COLUMN column_name |
      SET ON DELETE { CASCADE | NO ACTION } }

table_column_alteration:
  ALTER COLUMN column_name { { scalar_type | array_type } [NOT NULL] | SET options_def }

options_def:
  OPTIONS (allow_commit_timestamp = { true | null })

L'option (allow_commit_timestamp=true) permet aux opérations d'insertion et de mise à jour de demander à Cloud Spanner d'écrire l'horodatage du commit de la transaction dans la colonne. Pour plus d'informations, consultez la page Horodatages de commit.

DROP TABLE

Utilisez l'instruction DROP TABLE pour supprimer des tables. L'action DROP TABLE est irréversible.

DROP TABLE table_name

Instructions INDEX

CREATE INDEX

Utilisez l'instruction CREATE INDEX pour définir des index secondaires.

Syntaxe

CREATE [UNIQUE] [NULL_FILTERED] INDEX index_name
ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ]

storing_clause:
    STORING ( column_name [, ...] )

interleave_clause:
    INTERLEAVE IN table_name

index_name:
    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

Remarques :

  • UNIQUE indique que cet index secondaire applique une contrainte UNIQUE aux données indexées. La contrainte UNIQUE entraîne le rejet de toute transaction débouchant sur la duplication d'une clé d'index. Pour plus d'informations, consultez la section Index uniques.

  • NULL_FILTERED indique que cet index secondaire n'indexe pas les valeurs NULL. Pour plus d'informations, consultez la section Indexer les valeurs NULL.

  • INTERLEAVE IN définit une table dans laquelle entrelacer l'index. Si T est la table dans laquelle l'index est entrelacé, alors :

    • T doit être un parent de la table indexée ; et
    • la clé primaire de T doit être le préfixe de clé de l'index.

    Quand faut-il créer un index entrelacé ? Si la clé d'index que vous souhaitez utiliser pour les opérations d'index correspond à la clé d'une table, vous pouvez entrelacer l'index dans cette table si la ligne de la table doit avoir une relation de localité de données avec les lignes indexées correspondantes.

    Par exemple, si vous souhaitez indexer toutes les lignes de la table Songs pour une ligne particulière de la table Singers, vos clés d'index contiendront les éléments SingerId et SongName. Votre index serait alors un bon candidat à entrelacer dans Singers si vous extrayez fréquemment des informations sur un chanteur lorsque vous recherchez ses chansons dans l'index. La définition de SongsBySingerSongName de la section Créer un index secondaire constitue un exemple de création d'index entrelacé.

    Comme les tables entrelacées, les entrées d'un index entrelacé sont stockées avec la ligne correspondante de la table parent. Pour plus de précisions, consultez la section Divisions de base de données.

  • DESC définit l'ordre d'analyse décroissant pour la colonne d'index correspondante. Lors de l'analyse d'une table à l'aide d'une colonne d'index marquée DESC, les lignes analysées apparaissent dans l'ordre décroissant par rapport à cette colonne d'index. Si vous ne spécifiez pas d'ordre de tri, celui-ci est par défaut ascendant (ASC).

  • STORING fournit un mécanisme pour dupliquer les données de la table dans un ou plusieurs index secondaires de cette table. Cela occasionne certes un stockage supplémentaire, mais peut réduire la latence de lecture lors de la recherche de données à l'aide d'un index secondaire, car il n'est plus nécessaire de récupérer les données de la table principale après avoir trouvé les entrées souhaitées dans l'index. Reportez-vous à la clause STORING pour obtenir un exemple.

  • Les noms d'index :

    • peuvent contenir entre 1 et 128 caractères ;
    • doivent commencer par une lettre majuscule ou minuscule ;
    • peuvent contenir des lettres majuscules et minuscules, des chiffres et des traits de soulignement, mais pas de traits d'union ;
    • ne sont pas sensibles à la casse. Par exemple, vous ne pouvez pas créer d'index nommés myindex et MyIndex dans la même base de données.

DROP INDEX

Utilisez l'instruction DROP INDEX pour supprimer un index secondaire.

DROP INDEX index_name