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.

Index avec une précision au niveau des colonnes

Lorsque vous créez un index de recherche, vous pouvez spécifier la précision de la colonne pour une colonne indexée. La précision des colonnes permet à BigQuery d'optimiser certains types de requêtes de recherche en stockant des informations supplémentaires sur les colonnes dans votre index de recherche. Pour définir la précision de la colonne pour une colonne indexée, utilisez l'option index_granularity dans index_column_option_list lorsque vous exécutez une instruction CREATE SEARCH INDEX.

En interne, les tables BigQuery sont organisées en fichiers. Lorsque vous créez un index, BigQuery crée un mappage des jetons vers les fichiers qui les contiennent. Lorsque vous exécutez une requête de recherche, BigQuery analyse tous les fichiers contenant les jetons. Cette méthode peut être inefficace si votre jeton de recherche apparaît rarement dans la colonne que vous recherchez, mais est courant dans une autre colonne.

Par exemple, supposons que vous disposiez du tableau suivant contenant des offres d'emploi :

CREATE TABLE my_dataset.job_postings (job_id INT64, company_name STRING, job_description STRING);

Le mot compétences apparaît probablement souvent dans la colonne job_description, mais rarement dans la colonne company_name. Supposons que vous exécutiez la requête suivante :

SELECT * FROM my_dataset.job_postings WHERE SEARCH(company_name, 'skills');

Si vous avez créé un index de recherche sur les colonnes company_name et job_description sans spécifier la précision des colonnes, BigQuery analysera tous les fichiers dans lesquels le mot compétences apparaît dans la colonne job_description ou company_name. Pour améliorer les performances de cette requête, vous pouvez définir la précision de la colonne company_name sur COLUMN :

CREATE SEARCH INDEX my_index
ON my_dataset.job_postings (
  company_name OPTIONS(index_granularity = 'COLUMN'),
  job_description);

Désormais, lorsque vous exécutez la requête, BigQuery n'analyse que les fichiers dans lesquels le mot skills apparaît dans la colonne company_name.

Pour savoir quelles options sont définies dans les colonnes d'une table indexée, interrogez la vue INFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONS.

Le nombre de colonnes que vous pouvez indexer avec une précision au niveau des colonnes est limité. Pour en savoir plus, consultez la page Quotas et limites.

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. Une actualisation complète de l'index peut se produire dans les cas suivants :

  • La date d'expiration de la partition est mise à jour.
  • Une colonne indexée est mise à jour en raison d'une modification du schéma de table.
  • L'index est obsolète en raison du manque d'emplacements de réservation BACKGROUND pour les actualisations incrémentielles. Pour éviter l'obsolescence, vous pouvez utiliser l'autoscaling et surveiller la charge de travail afin de déterminer la taille de référence et la taille maximale de réservation les plus adaptées.

Si les données d'une colonne indexée sont mises à jour dans chaque ligne, par exemple lors d'une opération de remplissage, l'intégralité de l'index doit être mise à jour, ce qui équivaut à une actualisation complète. Nous vous recommandons d'effectuer les remplissages lentement, par exemple partition par partition, afin de minimiser tout impact négatif potentiel.

Si vous apportez une modification de schéma à la table de base qui empêche l'indexation d'une colonne indexée de manière explicite, l'index est définitivement désactivé.

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. Trois vues contiennent des métadonnées sur les index de recherche.

INFORMATION_SCHEMA.SEARCH_INDEXES Voir des exemples

Cette section inclut des exemples de requêtes de la vue INFORMATION_SCHEMA.SEARCH_INDEXES.

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   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS Voir des exemples

Cette section inclut des exemples de requêtes de la vue INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS.

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>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION Voir des exemples

Cette section inclut des exemples de requêtes de la vue INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION.

Vérifier si la consommation dépasse la limite dans une région donnée

L'exemple suivant illustre le cas où la taille totale des tables de base indexées dans une organisation, utilisant des emplacements partagés dans la zone multirégionale des États-Unis, dépasse 100 To :

WITH
 indexed_base_table_size AS (
 SELECT
   SUM(base_table.total_logical_bytes) AS total_logical_bytes
 FROM
   `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
 JOIN
   `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
 ON
   (search_index.table_name = base_table.table_name
     AND search_index.project_id = base_table.project_id
     AND search_index.index_schema = base_table.table_schema)
 WHERE
   TRUE
   -- Excludes search indexes that are permanently disabled.
   AND search_index.index_status != 'PERMANENTLY DISABLED'
   -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
   -- less than 10 GB. These tables don't count toward the limit.
   AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
   -- Excludes search indexes whose project has BACKGROUND reservation purchased
   -- for search indexes.
   AND search_index.use_background_reservation = false
 -- Outputs the total indexed base table size if it exceeds 100 TB,
 -- otherwise, doesn't return any output.
)
SELECT * FROM indexed_base_table_size
WHERE total_logical_bytes >= 109951162777600 -- 100 TB

Le résultat ressemble à ce qui suit :

+---------------------+
| total_logical_bytes |
+---------------------+
|     109951162777601 |
+---------------------+

Trouver la taille totale des tables de base indexées par projet dans une région

L'exemple suivant fournit la répartition de chaque projet dans une région multirégionale des États-Unis avec la taille totale des tables de base indexées :

SELECT
 search_index.project_id,
 search_index.use_background_reservation,
 SUM(base_table.total_logical_bytes) AS total_logical_bytes
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
JOIN
 `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
ON
 (search_index.table_name = base_table.table_name
   AND search_index.project_id = base_table.project_id
   AND search_index.index_schema = base_table.table_schema)
WHERE
 TRUE
  -- Excludes search indexes that are permanently disabled.
  AND search_index.index_status != 'PERMANENTLY DISABLED'
  -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
  -- less than 10 GB. These tables don't count toward limit.
 AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
GROUP BY search_index.project_id, search_index.use_background_reservation

Le résultat ressemble à ce qui suit :

+---------------------+----------------------------+---------------------+
|     project_id      | use_background_reservation | total_logical_bytes |
+---------------------+----------------------------+---------------------+
| projecta            |     true                   |     971329178274633 |
+---------------------+----------------------------+---------------------+
| projectb            |     false                  |     834638211024843 |
+---------------------+----------------------------+---------------------+
| projectc            |     false                  |     562910385625126 |
+---------------------+----------------------------+---------------------+

Identifier les index de recherche limités

L'exemple suivant renvoie tous les index de recherche limités dans l'organisation et la région :

SELECT project_id, index_schema, table_name, index_name
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION
WHERE
 -- Excludes search indexes that are permanently disabled.
 index_status != 'PERMANENTLY DISABLED'
 AND index_status_details.throttle_status IN ('ORGANIZATION_LIMIT_EXCEEDED', 'BASE_TABLE_TOO_LARGE')

Le résultat ressemble à ce qui suit :

+--------------------+--------------------+---------------+----------------+
|     project_id     |    index_schema    |  table_name   |   index_name   |
+--------------------+--------------------+---------------+----------------+
|     projecta       |     dataset_us     |   table1      |    index1      |
|     projectb       |     dataset_us     |   table1      |    index1      |
+--------------------+--------------------+---------------+----------------+

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 cette table. 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. 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é.

Vous pouvez utiliser la vue INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION pour comprendre votre consommation actuelle par rapport à la limite par organisation dans une région donnée, ventilée par projets et par tables.

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 :

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

    Accéder à BigQuery

  2. 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.

  3. 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