Optimiser les requêtes avec des colonnes virtuelles pour les expressions

Cette page explique comment utiliser des colonnes virtuelles pour les expressions dans le moteur de données en colonnes afin d'accélérer les requêtes.

Le moteur de données en colonnes peut concrétiser et mettre en cache les résultats des expressions fréquemment utilisées. En précalculant et en stockant ces résultats, AlloyDB évite l'analyse et l'évaluation répétées des mêmes expressions dans plusieurs requêtes. Ce processus améliore les performances des requêtes et réduit la consommation du processeur, en particulier pour les charges de travail analytiques sur de grands ensembles de données.

Voici quelques cas d'utilisation des colonnes virtuelles pour les expressions :

Filtrer les attributs JSON : lorsque vous filtrez fréquemment les requêtes en fonction de paires clé/valeur spécifiques dans une colonne JSON.
Évaluation d'expressions complexes : pour les requêtes impliquant des expressions complexes ou gourmandes en ressources de calcul.

Lorsque vous activez les colonnes virtuelles pour l'analyse des expressions, les requêtes qui utilisent le moteur de données en colonnes et contiennent des expressions fréquemment utilisées sont optimisées automatiquement. Le moteur de colonnes actualise automatiquement ces colonnes virtuelles lorsqu'il actualise les colonnes de base.

Expressions acceptées dans les colonnes virtuelles

AlloyDB est compatible avec les opérateurs -> et ->> sur les colonnes JSON et JSONB dans la version Preview.

Avant de commencer

Activez le moteur de données en colonnes pour votre cluster AlloyDB. Pour en savoir plus, consultez Configurer le moteur de données en colonnes.
Identifiez les expressions fréquemment utilisées dans vos requêtes AlloyDB à l'aide des insights sur les requêtes.
Ajoutez les colonnes de base des expressions au moteur de données en colonnes. Pour en savoir plus, consultez Gérer manuellement le contenu du store orienté colonnes.

Activer les colonnes virtuelles pour les expressions

Vous pouvez activer la prise en charge des colonnes virtuelles pour les expressions d'une instance à l'aide de la commande gcloud beta alloydb instances update.

Pour utiliser la gcloud CLI, vous pouvez installer et initialiser la Google Cloud CLI, ou utiliser Cloud Shell.

    gcloud beta alloydb instances update INSTANCE_ID \
        --region=REGION_ID \
        --cluster=CLUSTER_ID \
        --project=PROJECT_ID \
        --update-mode=INPLACE \
        --add-database-flags="google_columnar_engine.enable_virtual_columns_scan=on"

Remplacez les éléments suivants :

INSTANCE_ID : ID de votre instance AlloyDB.
REGION_ID : région de votre instance AlloyDB.
CLUSTER_ID : ID de cluster de votre instance AlloyDB.
PROJECT_ID : ID de votre projet Google Cloud .

Pour définir cet indicateur au niveau de la session, exécutez la commande suivante :

SET google_columnar_engine.enable_virtual_columns_scan=on;

Ajouter des colonnes virtuelles pour les expressions

Pour ajouter des expressions courantes au moteur columnar, utilisez le client psql pour appeler la fonction google_columnar_engine_add. Ces expressions sont insérées dans le moteur de données en colonnes en tant que colonnes, en plus de celles qui existent déjà dans la base de données.

Vous pouvez spécifier plusieurs expressions sous la forme de valeurs séparées par des virgules.

    SELECT google_columnar_engine_add(
    relation => 'DB.SCHEMA.TABLE_NAME',
    columns => 'COLUMN_NAME, COLUMN_NAME',
    expressions => 'EXP1, EXP2, EXP3'
    );

Remplacez les éléments suivants :

DB.SCHEMA : schéma de la base de données dans laquelle votre table est stockée.
TABLE_NAME : nom de la table dans laquelle votre colonne est stockée.
COLUMN_NAME : nom des colonnes contenant les expressions.
EXP1, EXP2, EXP3 par une liste d'expressions séparées par une virgule. Les expressions d'extraction JSON acceptées sont -> et ->>.

Par exemple, pour ajouter des expressions user ->> 'email' et user ->> 'name' à la table employee dans le schéma public, utilisez la requête suivante :
```
    SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );
```
Syntaxe des expressions :
- Placez la valeur de l'expression entière entre guillemets simples (par exemple, expressions => 'EXP1,EXP2,EXP3').
- Séparez les différentes expressions par des virgules.
- Placez chaque expression individuelle entre guillemets doubles.
- Échappez les guillemets simples dans une expression en utilisant un autre guillemet simple.
Par exemple, pour ajouter l'expression col -> 'level1' et col -> 'level1' ->> 'level2', utilisez le format suivant :
```
    expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'
```

Exemple de colonnes virtuelles pour les expressions

Cet exemple montre comment utiliser la fonctionnalité de colonnes virtuelles pour les expressions. Créez une table users avec une colonne JSONB profile et remplissez-la avec des exemples de données. Ensuite, en fonction de l'analyse des requêtes, ajoutez l'expression profile ->> 'email' fréquemment utilisée au moteur de données en colonnes avec la fonction google_columnar_engine_add. Le moteur en colonnes utilise ensuite cette expression fréquente pour optimiser les requêtes suivantes.

Pour ajouter cet exemple d'expression fréquente au moteur columnar, procédez comme suit :

Dans la console Google Cloud , accédez à la page Clusters.

accéder aux clusters
Dans la colonne Nom de la ressource, cliquez sur le nom de votre cluster.
Dans le volet de navigation, cliquez sur AlloyDB Studio.
Pour créer la table users avec une colonne JSONB profile, exécutez la commande suivante :
```
CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);
```

Pour remplir la table users avec des exemples de données, exécutez la commande suivante :

INSERT INTO users (id, username, profile)
SELECT
    i,
    'user' || i,
    jsonb_build_object(
        'name', 'User ' || i,
        'email', 'user' || i || '@example.com',
        'active', (i % 2 = 0)
    )
FROM generate_series(1, 100000) AS i;

Pour améliorer les performances de la requête qui inclut l'expression fréquemment utilisée, ajoutez l'expression profile ->> 'email' au moteur de données en colonnes :
```
SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);
```
Exécutez une requête qui utilise l'expression fréquente et observez le temps nécessaire à son exécution.
```
SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';
```
Activez la fonctionnalité de colonnes virtuelles pour les expressions.
```
SET google_columnar_engine.enable_virtual_columns_scan=on;
```
Réexécutez la requête qui utilise l'expression fréquente et observez le temps nécessaire à son exécution.
```
SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';
```

Le temps d'exécution des requêtes est plus rapide après l'activation des colonnes virtuelles pour les expressions.

Afficher les colonnes virtuelles pour les expressions

Pour trouver toutes les expressions ajoutées pour une table spécifique, interrogez la vue g_columnar_virtual_columns :

    SELECT * FROM g_columnar_virtual_columns;

Le résultat ressemble à ce qui suit, où le champ description affiche toutes les expressions ajoutées pour une table (relation).

SELECT * FROM g_columnar_virtual_columns;
category           | expression
database_name      | testdb
schemas            | {public}
relations          | {users}
description        | profile->>'email'
column_data_type   | text
status             | Usable
last_accessed_time | 2026-02-04 06:25:32.499601+00
num_times_accessed | 1

Supprimer les colonnes virtuelles pour les expressions

Pour supprimer une expression, appelez la fonction google_columnar_engine_drop() :

    SELECT google_columnar_engine_drop(
      relation => 'DB.SCHEMA.TABLE_NAME',
      expressions => 'EXP1, EXP2, EXP3'
    );

Remplacez EXP1, EXP2, EXP3 par une liste d'expressions séparées par une virgule dans le tableau, au même format que celui utilisé lors de l'ajout d'expressions.