Créer et gérer des colonnes générées

Une colonne générée est une colonne qui est toujours calculée à partir d'autres colonnes d'une ligne. Ces colonnes peuvent simplifier une requête, économiser le coût d'évaluation d'une expression au moment de la requête, et être indexées ou utilisées en tant que clé étrangère. Cette page explique comment gérer ce type de colonne dans votre base de données pour les bases de données de dialecte GoogleSQL et les bases de données de dialecte PostgreSQL.

Ajouter une colonne générée à une nouvelle table

Dans l'extrait de code CREATE TABLE suivant, nous allons créer une table pour stocker des informations sur les utilisateurs. Nous avons des colonnes pour FirstName et LastName, et définissons une colonne générée pour FullName, qui est la concaténation de FirstName et LastName. Le code SQL entre parenthèses est appelé expression de génération.

Une colonne générée peut être marquée comme STORED pour économiser le coût d'évaluation de l'expression au moment de la requête. Par conséquent, la valeur de FullName n'est calculée que lorsqu'une nouvelle ligne est insérée ou lorsque FirstName ou LastName est mis à jour pour une ligne existante. La valeur calculée est stockée avec les autres colonnes de la table.

GoogleSQL 

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (FirstName || ' ' || LastName) STORED
) PRIMARY KEY (Id);

PostgreSQL 

CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);

Vous pouvez créer une colonne générée non stockée en omettant l'attribut STORED dans le LDD. Ce type de colonne générée est évalué au moment de la requête et peut simplifier une requête. Dans PostgreSQL, vous pouvez créer une colonne générée non stockée à l'aide de l'attribut VIRTUAL.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

  • expression peut être n'importe quelle expression SQL valide pouvant être attribuée au type de données de la colonne avec les restrictions suivantes.

    • L'expression ne peut référencer que des colonnes dans la même table.

    • L'expression ne peut utiliser que des fonctions immuables. Une fonction immuable est une fonction qui renvoie les mêmes résultats chaque fois qu'elle est appelée avec les mêmes valeurs d'argument. Pour en savoir plus, consultez la section Volatilité des fonctions.

    • L'expression doit être scalaire, ce qui signifie qu'elle renvoie une seule valeur.

    • L'expression ne peut pas contenir de sous-requêtes.

    • Vous ne pouvez pas modifier l'expression d'une colonne générée STORED ou indexée.

  • Pour les bases de données de dialecte GoogleSQL, une colonne générée non stockée de type STRING ou BYTES doit avoir une longueur de MAX.

  • Pour les bases de données de dialecte PostgreSQL, une colonne générée non stockée, ou virtuelle, de type VARCHAR doit avoir une longueur de MAX.

  • L'attribut STORED qui suit l'expression stocke le résultat de l'expression avec les autres colonnes de la table. Les mises à jour ultérieures de l'une des colonnes référencées entraînent la réévaluation et le stockage de l'expression par Spanner.

  • Les colonnes générées qui ne sont pas STORED ne peuvent pas être marquées comme NOT NULL.

  • Les écritures directes sur les colonnes générées ne sont pas autorisées.

  • L'option de colonne allow_commit_timestamp n'est pas autorisée sur les colonnes générées ni sur les colonnes référencées par des colonnes générées.

  • Pour les colonnes STORED ou générées indexées, vous ne pouvez pas modifier le type de données de la colonne ni des colonnes référencées par la colonne générée.

  • Vous ne pouvez pas supprimer une colonne référencée par une colonne générée.

  • Vous pouvez utiliser une colonne générée comme clé primaire avec les restrictions supplémentaires suivantes :

    • La clé primaire générée ne peut pas référencer d'autres colonnes générées.

    • La clé primaire générée peut référencer au maximum une colonne non clé.

    • La clé primaire générée ne peut pas dépendre d'une colonne non clé avec une clause DEFAULT.

  • Les règles suivantes s'appliquent lorsque vous utilisez des colonnes de clé générées :

    • API de lecture : vous devez spécifier entièrement les colonnes de clé, y compris les colonnes de clé générées.
    • API de mutation : pour INSERT, INSERT_OR_UPDATE et REPLACE, Spanner ne vous permet pas de spécifier des colonnes de clé générées. Pour UPDATE, vous pouvez éventuellement spécifier des colonnes de clé générées. Pour DELETE, vous devez spécifier entièrement les colonnes de clé, y compris les clés générées.
    • LMD : vous ne pouvez pas écrire explicitement dans les clés générées dans les instructions INSERT ou UPDATE.
    • Requête : en général, nous vous recommandons d'utiliser la colonne de clé générée comme filtre dans votre requête. Si l'expression de la colonne de clé générée n'utilise qu'une seule colonne comme référence, la requête peut appliquer une condition d'égalité (=) ou IN à la colonne référencée. Pour en savoir plus et obtenir un exemple, consultez Créer une clé unique dérivée d'une colonne de valeurs.

La colonne générée peut être interrogée comme n'importe quelle autre colonne, comme illustré dans l'exemple suivant.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

La requête utilisant Fullname est équivalente à la requête avec l'expression générée. Par conséquent, une colonne générée peut simplifier la requête.

GoogleSQL 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Créer un index sur une colonne générée

Vous pouvez également indexer ou utiliser une colonne générée comme clé étrangère.

Pour faciliter les recherches dans la colonne générée FullName, nous pouvons créer un index secondaire, comme indiqué dans l'extrait suivant.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Ajouter une colonne générée à une table existante

À l'aide de l'instruction ALTER TABLE suivante, nous pouvons ajouter une colonne générée à la table Users pour générer et stocker les initiales de l'utilisateur.

GoogleSQL 

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL 

ALTER TABLE users ADD COLUMN initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(firstname, 0, 1) || SUBSTR(lastname, 0, 1)) STORED;

Si vous ajoutez une colonne générée stockée à une table existante, une opération de longue durée permettant de remplir les valeurs de la colonne est lancée. Lors du remplissage, les colonnes générées stockées ne peuvent pas être lues ni interrogées. L'état du remplissage est reflété dans la table INFORMATION_SCHEMA.

Créer un index partiel à l'aide d'une colonne générée

Imaginons que nous souhaitions n'interroger que les utilisateurs de plus de 18 ans. Une analyse complète de la table serait inefficace, c'est pourquoi nous utilisons un index partiel.

  1. Utilisez l'instruction suivante pour ajouter une autre colonne générée, qui renvoie l'âge de l'utilisateur s'il a plus de 18 ans et renvoie NULL dans le cas contraire.

    GoogleSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL));

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;

  2. Créez un index sur cette nouvelle colonne, puis désactivez l'indexation des NULL valeurs avec le NULL_FILTERED mot clé dans GoogleSQL ou le IS NOT NULL prédicat dans PostgreSQL. Cet index partiel est plus petit et plus efficace qu'un index normal, car il exclut tous les utilisateurs âgés de 18 ans ou moins.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Pour récupérer les propriétés Id et Age de tous les utilisateurs âgés de plus de 18 ans, exécutez la requête suivante.

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 IS NOT NULL;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 IS NOT NULL;

  4. Pour filtrer sur un autre âge, par exemple, pour extraire tous les utilisateurs de plus de 21 ans, utilisez le même index et le même filtre sur la colonne générée comme suit :

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 > 21;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

    Une colonne générée indexée peut économiser le coût d'évaluation d'une expression au moment de la requête et éviter de stocker les valeurs deux fois (dans la table de base et l'index) par rapport à une colonne générée STORED.

Supprimer une colonne générée

L'instruction LDD suivante supprime une colonne générée à partir de la table Users :

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Modifier une expression de colonne générée

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

La mise à jour de l'expression d'une colonne générée STORED ou d'une colonne générée non stockée indexée n'est pas autorisée.

Créer une clé primaire sur une colonne générée

Dans Spanner, vous pouvez utiliser une colonne générée STORED dans la clé primaire.

L'exemple suivant montre une instruction LDD qui crée la table UserInfoLog avec une colonne générée ShardId. La valeur de la colonne ShardId dépend d'une autre colonne. Elle est dérivée à l'aide d'une fonction MOD sur la colonne UserId. ShardId est déclaré comme faisant partie de la clé primaire.

GoogleSQL 

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL 

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

Normalement, pour accéder efficacement à une ligne spécifique, vous devez spécifier toutes les colonnes de clé. Dans l'exemple précédent, cela signifie fournir à la fois un ShardId et un UserId. Toutefois, Spanner peut parfois déduire la valeur de la colonne de clé primaire générée si elle dépend d'une seule autre colonne et si la valeur de la colonne dont elle dépend est entièrement déterminée. Cela s'applique si la colonne référencée par la colonne de clé primaire générée remplit l'une des conditions suivantes :

  • Elle est égale à une valeur constante ou à un paramètre lié dans la clause WHERE.
  • Sa valeur est définie par un opérateur IN dans la clause WHERE.
  • Sa valeur provient d'une condition de jointure d'égalité.

Par exemple, pour la requête suivante :

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner peut déduire la valeur de ShardId à partir du UserId fourni. La requête précédente est équivalente à la requête suivante après optimisation :

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

L'exemple suivant montre comment créer la table Students et utiliser une expression qui récupère le champ id de la colonne JSON StudentInfo et l'utilise comme clé primaire :

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

Afficher les propriétés d'une colonne générée

L'élément INFORMATION_SCHEMA de Spanner contient des informations sur les colonnes générées de votre base de données. Voici quelques exemples de questions auxquelles vous pouvez répondre lorsque vous interrogez le schéma d'information.

Quelles colonnes générées sont définies dans ma base de données ?

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

IS_STORED est YES pour les colonnes générées stockées, NO pour les colonnes générées non stockées ou NULL pour les colonnes non générées.

Quel est l'état actuel des colonnes générées dans le tableau Users ?

Si vous avez ajouté une colonne générée à une table existante, vous pouvez transmettre SPANNER_STATE dans une requête pour connaître l'état actuel de la colonne. SPANNER_STATE renvoie les valeurs suivantes :

  • COMMITTED : la colonne est entièrement utilisable.
  • WRITE_ONLY : la colonne est en cours de remplissage. Aucune lecture n'est autorisée.

Utilisez la requête suivante pour connaître l'état d'une colonne :

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

Remarque : Une colonne générée non stockée n'est accessible qu'à l'aide de la requête SQL. Toutefois, si elle est indexée, vous pouvez utiliser l' API de lecture pour accéder à la valeur à partir de l'index.

Performances

Une colonne générée STORED n'affecte pas les performances d'une opération de lecture ou de requête. Toutefois, les colonnes générées non stockées utilisées dans une requête peuvent avoir un impact sur ses performances en raison de la surcharge liée à l'évaluation de l'expression de la colonne générée.

Les performances des opérations d'écriture (instructions LMD et mutations) sont affectées lorsque vous utilisez une colonne générée STORED ou une colonne générée indexée. La surcharge est due à l'évaluation de l'expression de la colonne générée lorsque l'opération d'écriture insère ou modifie l'une des colonnes référencées dans l'expression de la colonne générée. Étant donné que la surcharge varie en fonction de la charge de travail d'écriture de l'application, de la conception du schéma et des caractéristiques de l'ensemble de données, nous vous recommandons de comparer les performances de vos applications avant d'utiliser une colonne générée.

Étape suivante