Gérer les index de recherche

Un index de recherche est une structure de données conçue pour permettre une recherche très efficace avec la fonction SEARCH. Un index de recherche peut également optimiser certaines requêtes qui utilisent des fonctions et opérateurs compatibles.

Tout comme le sommaire de l'index, un index de recherche d'une colonne de données de chaîne agit comme une table auxiliaire comportant une colonne pour les mots uniques et une autre pour l'emplacement des données.

créer un index de recherche ;

Pour créer un index de recherche, utilisez l'instruction LDD CREATE SEARCH INDEX. Pour spécifier les types de données primitifs à indexer, consultez la section Créer un index de recherche et spécifier les colonnes ainsi que les types de données. Si vous ne spécifiez aucun type de données, BigQuery va indexer par défaut les colonnes des types suivants, contenant des données de type STRING :

STRING
ARRAY<STRING>
STRUCT contenant au moins un champ imbriqué de type STRING ou ARRAY<STRING>
JSON

Lorsque vous créez un index de recherche, vous pouvez spécifier le type d'analyseur de texte à utiliser. L'analyseur de texte contrôle la façon dont les données sont tokenisées pour l'indexation et la recherche. La valeur par défaut est LOG_ANALYZER. Cet analyseur fonctionne bien pour les journaux générés par une machine et possède des règles spéciales concernant les jetons couramment utilisés dans les données d'observabilité, telles que les adresses IP ou les adresses e-mail. Utilisez NO_OP_ANALYZER lorsque vous souhaitez faire correspondre exactement des données prétraitées. PATTERN_ANALYZER extrait les jetons d'un texte à l'aide d'une expression régulière.

Créer un index de recherche avec l'analyseur de texte par défaut

Dans l'exemple suivant, un index de recherche est créé sur les colonnes a et c de la table simple_table, qui utilise l'analyseur de texte LOG_ANALYZER par défaut :

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);

Créer un index de recherche sur toutes les colonnes avec l'analyseur `NO_OP_ANALYZER`

Lorsque vous créez un index de recherche sur ALL COLUMNS, toutes les données STRING ou JSON de la table sont indexées. Si la table ne contient aucune donnée de ce type, par exemple si toutes les colonnes contiennent des entiers, la création de l'index échoue. Lorsque vous spécifiez une colonne STRUCT à indexer, tous les sous-champs imbriqués sont indexés.

Dans l'exemple suivant, un index de recherche est créé sur a, c.e et c.f.g, qui utilise l'analyseur de texte NO_OP_ANALYZER :

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

Comme l'index de recherche a été créé sur ALL COLUMNS, toutes les colonnes ajoutées à la table sont automatiquement indexées si elles contiennent des données STRING.

Créer un index de recherche et spécifier les colonnes ainsi que les types de données

Lorsque vous créez un index de recherche, vous pouvez spécifier les types de données à utiliser. Les types de données contrôlent les types de colonnes et les sous-champs des colonnes JSON et STRUCT, pour l'indexation. Le type de données par défaut pour l'indexation est STRING. Pour créer un index de recherche avec davantage de types de données (par exemple, des types numériques), utilisez l'instruction CREATE SEARCH INDEX avec l'option data_types incluse.

Dans l'exemple suivant, un index de recherche est créé sur les colonnes a, b, c et d d'une table nommée simple_table : Les types de données compatibles pour les données présentes dans ces colonnes sont STRING, INT64et TIMESTAMP.

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON, d TIMESTAMP);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, b, c, d)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

Créer un index de recherche sur toutes les colonnes et spécifier les types de données

Lorsque vous créez un index de recherche sur ALL COLUMNS avec l'option data_types spécifiée, toutes les colonnes correspondant à l'un des types de données spécifiés vont être indexées. Pour les colonnes JSON et STRUCT, tous les sous-champ imbriqués correspondant à l'un des types de données spécifiés vont être indexés.

Dans l'exemple suivant, un index de recherche est créé sur ALL COLUMNS avec des types de données spécifiés. Les colonnes a, b, c, d.e, d.f, d.g.h et d.g.i d'une table nommée my_table sont indexées :

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c TIMESTAMP,
  d STRUCT <e INT64,
            f ARRAY<STRING>,
            g STRUCT<h STRING, i INT64>>)
AS (
  SELECT
    'hello' AS a,
    10 AS b,
    TIMESTAMP('2008-12-25 15:30:00 UTC') AS c,
    (20, ['x', 'y'], ('z', 30)) AS d;
)

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

Comme l'index de recherche a été créé sur ALL COLUMNS, toutes les colonnes ajoutées à la table sont automatiquement indexées si elles correspondent à l'un des types de données spécifiés.

Comprendre l'actualisation de l'index

Les index de recherche sont entièrement gérés par BigQuery et actualisés automatiquement lorsque la table est modifiée. Les modifications de schéma suivantes sur la table peuvent déclencher une actualisation complète :

Une nouvelle colonne indexable est ajoutée à une table avec un index de recherche sur ALL COLUMNS.
Une colonne indexée est mise à jour en raison d'une modification du schéma de table.

Si vous supprimez la seule colonne indexée d'une table ou renommez la table elle-même, l'index de recherche est automatiquement supprimé.

Les index de recherche sont conçus pour les grandes tables. Si vous créez un index de recherche sur une table de taille inférieure à 10 Go, l'index n'est pas renseigné. De même, si vous supprimez les données d'une table indexée et que sa taille est inférieure à 10 Go, l'index est temporairement désactivé. Dans ce cas, les requêtes de recherche n'utilisent pas l'index et le code IndexUnusedReason est BASE_TABLE_TOO_SMALL. Cela se produit que vous utilisiez ou non votre propre réservation pour vos tâches de gestion d'index. Lorsque la taille d'une table indexée dépasse 10 Go, son index est automatiquement renseigné. Vous n'êtes pas facturé pour le stockage tant que l'index de recherche n'est pas renseigné et actif. Les requêtes qui utilisent la fonction SEARCH renvoient toujours des résultats corrects, même si certaines données ne sont pas encore indexées.

En savoir plus sur les index de recherche

Vous pouvez vérifier l'existence et la disponibilité d'un index de recherche en interrogeant INFORMATION_SCHEMA. Deux vues contiennent des métadonnées sur les index de recherche. La vue INFORMATION_SCHEMA.SEARCH_INDEXES contient des informations sur chaque index de recherche créé sur un ensemble de données. La vue INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS contient des informations sur les colonnes de chaque table de l'ensemble de données.

L'exemple suivant montre tous les index de recherche actifs sur les tables de l'ensemble de données my_dataset, situées dans le projet my_project. Cela inclut leur nom, les instructions LDD utilisées pour les créer, leur pourcentage de couverture et leur analyseur de texte. Si une table de base indexée est inférieure à 10 Go, son index n'est pas renseigné, auquel cas coverage_percentage est égal à 0.

SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';

Les résultats doivent se présenter sous la forme suivante :

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| table_name  | index_name  | ddl                                                                                  | coverage_percentage | analyzer       |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names)      | 0                   | NO_OP_ANALYZER |
| large_table | logs_index  | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100                 | LOG_ANALYZER   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

L'exemple suivant permet de créer un index de recherche sur toutes les colonnes de my_table.

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS);

La requête suivante extrait des informations sur les champs indexés. index_field_path indique le champ d'une colonne indexée. Cela diffère de index_column_name uniquement dans le cas d'un STRUCT, où le chemin d'accès complet au champ indexé est fourni. Dans cet exemple, la colonne c contient un champ ARRAY<STRING> e et un autre STRUCT appelé f contenant un champ STRING g. Chacune d'elles est indexée.

SELECT table_name, index_name, index_column_name, index_field_path
FROM my_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

Le résultat ressemble à ce qui suit :

+------------+------------+-------------------+------------------+
| table_name | index_name | index_column_name | index_field_path |
+------------+------------+-------------------+------------------+
| my_table   | my_index   | a                 | a                |
| my_table   | my_index   | c                 | c.e              |
| my_table   | my_index   | c                 | c.f.g            |
+------------+------------+-------------------+------------------+

La requête suivante joint la vue INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS aux vues INFORMATION_SCHEMA.SEARCH_INDEXES et INFORMATION_SCHEMA.COLUMNS afin d'inclure l'état de l'index de recherche et le type de données de chaque colonne :

SELECT
  index_columns_view.index_catalog AS project_name,
  index_columns_view.index_SCHEMA AS dataset_name,
  indexes_view.TABLE_NAME AS table_name,
  indexes_view.INDEX_NAME AS index_name,
  indexes_view.INDEX_STATUS AS status,
  index_columns_view.INDEX_COLUMN_NAME AS column_name,
  index_columns_view.INDEX_FIELD_PATH AS field_path,
  columns_view.DATA_TYPE AS data_type
FROM
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES indexes_view
INNER JOIN
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS index_columns_view
  ON
    indexes_view.TABLE_NAME = index_columns_view.TABLE_NAME
    AND indexes_view.INDEX_NAME = index_columns_view.INDEX_NAME
LEFT OUTER JOIN
  mydataset.INFORMATION_SCHEMA.COLUMNS columns_view
  ON
    indexes_view.INDEX_CATALOG = columns_view.TABLE_CATALOG
    AND indexes_view.INDEX_SCHEMA = columns_view.TABLE_SCHEMA
    AND index_columns_view.TABLE_NAME = columns_view.TABLE_NAME
    AND index_columns_view.INDEX_COLUMN_NAME = columns_view.COLUMN_NAME
ORDER BY
  project_name,
  dataset_name,
  table_name,
  column_name;

Le résultat ressemble à ce qui suit :

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| project    | dataset    | table    | index_name | status | column_name | field_path | data_type                                                     |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| my_project | my_dataset | my_table | my_index   | ACTIVE | a           | a          | STRING                                                        |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.e        | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.f.g      | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

Options de gestion des index

Pour créer des index et laisser BigQuery les gérer, deux options s'offrent à vous :

Utiliser le pool d'emplacements partagés par défaut : lorsque les données que vous prévoyez d'indexer sont inférieures à votre limite par organisation, vous pouvez utiliser le pool d'emplacements partagé gratuit pour la gestion des index.
Utiliser votre propre réservation : pour obtenir une progression d'indexation plus prévisible et cohérente sur vos charges de travail de production plus importantes, vous pouvez utiliser vos propres réservations pour la gestion des index.

Utiliser les emplacements partagés

Si vous n'avez pas configuré votre projet pour utiliser une réservation dédiée pour l'indexation, la gestion des index est gérée dans le pool d'emplacements partagés gratuit, qui est soumis aux contraintes suivantes.

Si vous ajoutez des données à une table, ce qui entraîne le dépassement de la limite des tables indexées pour votre organisation, BigQuery suspend la gestion des index pour toutes les tables indexées. Dans ce cas, le champ index_status de la vue INFORMATION_SCHEMA.SEARCH_INDEXES affiche PENDING DISABLEMENT et l'index est mis en file d'attente pour suppression. Tant que l'index est en attente de désactivation, il est toujours utilisé dans les requêtes et le stockage des index vous est facturé. Une fois l'index supprimé, le champ index_status affiche l'état de l'index comme étant TEMPORARILY DISABLED. Dans cet état, les requêtes n'utilisent pas l'index et le stockage d'index ne vous est pas facturé. Dans ce cas, le code IndexUnusedReason est BASE_TABLE_TOO_LARGE.

Si vous supprimez des données de la table et que la taille totale des tables indexées est inférieure à la limite par organisation, la gestion des index est réactivée pour toutes les tables indexées. Le champ index_status dans la vue INFORMATION_SCHEMA.SEARCH_INDEXES est ACTIVE, les requêtes peuvent utiliser l'index et le stockage d'index vous est facturé.

BigQuery ne garantit pas la capacité disponible du pool partagé ni le débit d'indexation que vous voyez. Pour les applications de production, vous pouvez utiliser des emplacements dédiés pour le traitement des index.

Utiliser votre propre réservation

Au lieu d'utiliser le pool d'emplacements partagés par défaut, vous pouvez éventuellement définir votre propre réservation pour indexer vos tables. L'utilisation de votre propre réservation garantit des performances prévisibles et cohérentes pour les jobs de gestion des index, telles que la création, l'actualisation et les optimisations en arrière-plan.

Il n'y a aucune limite de taille de table lorsqu'un job d'indexation s'exécute dans votre réservation.
L'utilisation de votre propre réservation vous donne une certaine flexibilité dans la gestion de vos index. Si vous devez créer un index très volumineux ou effectuer une mise à jour majeure d'une table indexée, vous pouvez ajouter temporairement d'autres emplacements à l'attribution.

Pour indexer les tables d'un projet avec une réservation désignée, créez une réservation dans la région où se trouvent vos tables. Ensuite, attribuez le projet à la réservation en définissant job_type sur BACKGROUND :

SQL

Utilisez l'instruction LDD CREATE ASSIGNMENT :

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

Accéder à BigQuery
Dans l'éditeur de requête, saisissez l'instruction suivante :
```
CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');
```
Remplacez les éléments suivants :
- ADMIN_PROJECT_ID : ID du projet d'administration propriétaire de la ressource de réservation
- LOCATION : emplacement de la réservation.
- RESERVATION_NAME : nom de la réservation
- ASSIGNMENT_ID : ID de l'attribution
  
  L'ID doit être unique au projet et à l'emplacement. Il doit commencer et se terminer par une lettre minuscule ou un chiffre, et ne doit contenir que des lettres minuscules, des chiffres et des tirets.
- PROJECT_ID : ID du projet contenant les tables à indexer. Ce projet est attribué à la réservation.
Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq mk :

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_assignment \
    --reservation_id=RESERVATION_NAME \
    --assignee_id=PROJECT_ID \
    --job_type=BACKGROUND \
    --assignee_type=PROJECT

Remplacez les éléments suivants :

ADMIN_PROJECT_ID : ID du projet d'administration propriétaire de la ressource de réservation
LOCATION : emplacement de la réservation.
RESERVATION_NAME : nom de la réservation
PROJECT_ID : ID du projet à attribuer à cette réservation

Afficher vos tâches d'indexation

Un job d'indexation est créée chaque fois qu'un index est créé ou mis à jour sur une table unique. Pour afficher des informations sur la tâche, interrogez les vues INFORMATION_SCHEMA.JOBS*. Vous pouvez filtrer les tâches d'indexation en définissant job_type IS NULL AND SEARCH(job_id, '`search_index`') dans la clause WHERE de votre requête. L'exemple suivant répertorie les cinq tâches d'indexation les plus récentes du projet my_project :

SELECT *
FROM
 region-us.INFORMATION_SCHEMA.JOBS
WHERE
  project_id  = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
ORDER BY
 creation_time DESC
LIMIT 5;

Choisir votre taille de réservation

Pour choisir le nombre approprié d'emplacements pour votre réservation, vous devez prendre en compte le moment où les tâches de gestion des index sont exécutées, ainsi que le nombre d'emplacements utilisés et votre utilisation au fil du temps. BigQuery déclenche une tâche de gestion des index dans les situations suivantes :

Vous créez un index sur une table.
Des données sont modifiées dans une table indexée.
Le schéma d'une table change, ce qui affecte les colonnes indexées.
Les données et les métadonnées d'index sont régulièrement optimisées ou mises à jour.

Le nombre d'emplacements dont vous avez besoin pour exécuter une tâche de gestion des index sur une table dépend des facteurs suivants :

Taille de la table
Taux d'ingestion de données dans la table
Taux d'instructions LMD appliquées à la table
Délai acceptable pour créer et gérer l'index
Complexité de l'index, généralement déterminée par les attributs des données, tels que le nombre de termes en double

Estimation initiale

Les estimations suivantes peuvent vous aider à déterminer le nombre d'emplacements requis pour votre réservation. En raison de la nature très variable de l'indexation des charges de travail, vous devez réévaluer vos exigences après avoir commencé à indexer des données.

Données existantes : avec une réservation de 1 000 emplacements, une table existante dans BigQuery peut être indexée à une fréquence moyenne de 4 Gio par seconde, soit environ 336 Tio par jour.
Données nouvellement ingérées : l'indexation nécessite généralement plus de ressources sur les données nouvellement ingérées, car la table et son index passent par plusieurs séries d'optimisations transformatives. En moyenne, l'indexation des données nouvellement ingérées consomme trois fois plus de ressources que l'indexation initiale de remplissage des mêmes données.
Données rarement modifiées : les tables indexées avec peu ou pas de modifications de données nécessitent beaucoup moins de ressources pour une maintenance continue des index. Un point de départ recommandé consiste à conserver un cinquième des emplacements requis pour l'indexation initiale de remplissage des mêmes données, et au moins 250 emplacements.
La progression de l'indexation évolue de manière linéaire avec la taille de la réservation. Toutefois, nous vous déconseillons d'utiliser des réservations de moins de 250 emplacements pour l'indexation, car cela pourrait entraîner une inefficacité pouvant ralentir la progression de l'indexation.
Ces estimations peuvent varier en fonction des caractéristiques, des optimisations et de votre utilisation réelle.
Si la taille totale de la table de votre organisation dépasse la limite d'indexation de votre région, vous devez conserver une réservation différente de zéro pour l'indexation. Sinon, l'indexation peut revenir au niveau par défaut, ce qui entraîne la suppression accidentelle de tous les index.

Surveiller l'utilisation et la progression

Le meilleur moyen d'évaluer le nombre d'emplacements dont vous avez besoin pour exécuter efficacement vos tâches de gestion des index consiste à surveiller l'utilisation des emplacements et à ajuster la taille de la réservation en conséquence. La requête suivante permet d'obtenir l'utilisation quotidienne des emplacements pour les tâches de gestion des index. Seuls les 30 derniers jours sont inclus dans la région us-west1 :

SELECT
  TIMESTAMP_TRUNC(job.creation_time, DAY) AS usage_date,
  -- Aggregate total_slots_ms used for index-management jobs in a day and divide
  -- by the number of milliseconds in a day. This value is most accurate for
  -- days with consistent slot usage.
  SAFE_DIVIDE(SUM(job.total_slot_ms), (1000 * 60 * 60 * 24)) AS average_daily_slot_usage
FROM
  `region-us-west1`.INFORMATION_SCHEMA.JOBS job
WHERE
  project_id = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
GROUP BY
  usage_date
ORDER BY
  usage_date DESC
limit 30;

Lorsque les emplacements sont insuffisants pour exécuter des tâches de gestion des index, un index peut ne plus être synchronisé avec sa table. Par conséquent, les tâches d'indexation peuvent échouer. Dans ce cas, BigQuery recrée l'index à partir de zéro. Pour éviter la désynchronisation d'index, assurez-vous de disposer de suffisamment d'emplacements pour gérer les mises à jour d'index liées à l'ingestion et à l'optimisation des données. Pour en savoir plus sur l'utilisation des emplacements, consultez la page Graphiques de ressources pour les administrateurs.

Bonnes pratiques

Les index de recherche sont conçus pour les grandes tables. Les gains de performances offerts par un index de recherche augmentent avec la taille de la table.
N'indexez pas les colonnes ne contenant qu'un très petit nombre de valeurs uniques.
N'indexez pas de colonnes que vous n'avez jamais l'intention d'utiliser avec la fonction SEARCH, ni avec les autres fonctions et opérateurs compatibles.
Soyez prudent lorsque vous créez un index de recherche sur ALL COLUMNS. Chaque fois que vous ajoutez une colonne contenant des données STRING ou JSON, la colonne est indexée.
Vous devez utiliser votre propre réservation pour la gestion des index dans les applications de production. Si vous choisissez d'utiliser le pool d'emplacements partagés par défaut pour vos tâches de gestion des index, les limites de dimensionnement par organisation s'appliquent.

Supprimer un index de recherche

Lorsque vous n'avez plus besoin d'un index de recherche ou que vous souhaitez modifier les colonnes indexées sur une table, vous pouvez supprimer l'index actuellement en place sur cette table. Utilisez l'instruction LDD DROP SEARCH INDEX :

Si une table indexée est supprimée, son index est automatiquement supprimé.

Exemple :

DROP SEARCH INDEX my_index ON dataset.simple_table;

Étapes suivantes

Pour obtenir une présentation des cas d'utilisation de l'index de recherche, des tarifs, des autorisations requises et des limites, consultez la page Présentation de la recherche dans BigQuery.
Pour savoir comment effectuer des recherches efficaces des colonnes indexées, consultez la page Effectuer des recherches avec un index.