Administra índices de búsqueda

Un índice de búsqueda es una estructura de datos diseñada para habilitar una búsqueda muy eficiente con la función SEARCH. Un índice de búsqueda también puede optimizar algunas consultas que usan operadores y funciones compatibles.

Al igual que el índice que encontrarías en la parte posterior de un libro, un índice de búsqueda de una columna de datos de string actúa como una tabla auxiliar que tiene una columna para palabras únicas y otra para el lugar en el que aparecen esas palabras en los datos.

Crear un índice de búsqueda

Para crear un índice de búsqueda, usa la declaración DDL CREATE SEARCH INDEX. Para especificar los tipos de datos primitivos que se indexarán, consulta Cómo crear un índice de búsqueda y especificar las columnas y los tipos de datos. Si no especificas ningún tipo de datos, BigQuery indexa las columnas de los siguientes tipos que contienen datos STRING:

STRING
ARRAY<STRING>
STRUCT que contenga al menos un campo anidado de tipo STRING o ARRAY<STRING>
JSON

Cuando creas un índice de búsqueda, puedes especificar el tipo de analizador de texto que usarás. El analizador de texto controla cómo se asignan tokens a los datos para la indexación y la búsqueda. El predeterminado es LOG_ANALYZER. Este analizador funciona bien para los registros generados por máquina y tiene reglas especiales sobre los tokens que se encuentran comúnmente en los datos de observabilidad, como direcciones IP o correos electrónicos. Usa NO_OP_ANALYZER cuando tengas datos procesados con anterioridad que desees que coincidan con exactitud. PATTERN_ANALYZER extrae tokens del texto mediante una expresión regular.

Crea un índice de búsqueda con el analizador de texto predeterminado

En el siguiente ejemplo, se crea un índice de búsqueda en las columnas a y c de simple_table y se usa el analizador de texto LOG_ANALYZER de forma predeterminada:

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

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

Crea un índice de búsqueda en todas las columnas con el analizador `NO_OP_ANALYZER`

Cuando creas un índice de búsqueda en ALL COLUMNS, se indexan todos los datos de STRING o JSON de la tabla. Si la tabla no contiene esos datos, por ejemplo, si todas las columnas contienen números enteros, la creación del índice falla. Cuando especificas una columna STRUCT para indexar, todos los subcampos anidados se indexan.

En el siguiente ejemplo, se crea un índice de búsqueda en a, c.e y c.f.g y se usa el analizador de texto 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');

Debido a que el índice de búsqueda se creó en ALL COLUMNS, cualquier columna que se agregue a la tabla se indexará de forma automática si contiene datos STRING.

Crea un índice de búsqueda y especifica las columnas y los tipos de datos

Cuando creas un índice de búsqueda, puedes especificar los tipos de datos que usarás. Los tipos de datos controlan los tipos de columnas y subcampos de las columnas JSON y STRUCT para la indexación. El tipo de datos predeterminado para la indexación es STRING. Para crear un índice de búsqueda con más tipos de datos (por ejemplo, tipos numéricos), usa la declaración CREATE SEARCH INDEX con la opción data_types incluida.

En el siguiente ejemplo, se crea un índice de búsqueda en las columnas a, b, c y d de una tabla llamada simple_table: Los tipos de datos admitidos son STRING, INT64 y 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']);

Crea un índice de búsqueda en todas las columnas y especificar los tipos de datos

Cuando creas un índice de búsqueda en ALL COLUMNS con la opción data_types especificada, se indexa cualquier columna que coincida con uno de los tipos de datos especificados. Para las columnas JSON y STRUCT, se indexa cualquier subcampo anidado que coincida con uno de los tipos de datos especificados.

En el siguiente ejemplo, se crea un índice de búsqueda en ALL COLUMNS con los tipos de datos especificados. Se indexan las columnas a, b, c, d.e, d.f, d.g.h y d.g.i de una tabla llamada my_table:

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']);

Debido a que el índice de búsqueda se creó en ALL COLUMNS, cualquier columna que se agregue a la tabla se indexará de forma automática si coincide con alguno de los tipos de datos especificados.

Comprende la actualización de índices

BigQuery administra completamente los índices de búsqueda y los actualizan de forma automática cuando cambia la tabla. Los siguientes cambios de esquema en la tabla pueden activar una actualización completa:

Se agrega una nueva columna indexable a una tabla con un índice de búsqueda en ALL COLUMNS.
Una columna indexada se actualiza debido a un cambio de esquema de tabla.

Si borras la única columna indexada en una tabla o cambias el nombre de la tabla, el índice de búsqueda se borra de forma automática.

Los índices de la búsqueda están diseñados para tablas grandes. Si creas un índice de búsqueda en una tabla de menos de 10 GB, el índice no se propaga. Del mismo modo, si borras datos de una tabla indexada y el tamaño de la tabla es inferior a 10 GB, el índice se inhabilitará de forma temporal. En este caso, las consultas de búsqueda no usan el índice y el código IndexUnusedReason es BASE_TABLE_TOO_SMALL. Esto sucede si usas o no tu propia reserva para trabajos de administración de índices. Cuando el tamaño de una tabla indexada supera los 10 GB, su índice se propaga de forma automática. No se te cobrará por el almacenamiento hasta que el índice de búsqueda se propague y esté activo. Las consultas que usan la función SEARCH siempre muestran resultados correctos incluso si algunos datos aún no están indexados.

Obtén información sobre los índices de búsqueda

Puedes verificar la existencia y la preparación de un índice de búsqueda mediante una consulta a INFORMATION_SCHEMA. Hay dos vistas que contienen metadatos en los índices de búsqueda. La vista INFORMATION_SCHEMA.SEARCH_INDEXES tiene información sobre cada índice de búsqueda creado en un conjunto de datos. La vista INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS tiene información sobre qué columnas de cada tabla del conjunto de datos están indexadas.

En el siguiente ejemplo, se muestran todos los índices de búsqueda activos en las tablas del conjunto de datos my_dataset, ubicado en el proyecto my_project. Incluye los nombres, las declaraciones DDL que se usan para crearlos, el porcentaje de cobertura y el analizador de texto. Si una tabla base indexada tiene menos de 10 GB, su índice no se propaga. En ese caso, coverage_percentage será 0.

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

Los resultados deberían verse así:

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

En el siguiente ejemplo, se crea un índice de búsqueda en todas las columnas 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);

En la siguiente consulta, se extrae información sobre qué campos se indexan. El index_field_path indica qué campo de una columna está indexado. Esto difiere de index_column_name solo en el caso de una STRUCT, en la que se proporciona la ruta completa al campo indexado. En este ejemplo, la columna c contiene un campo ARRAY<STRING> e y otro STRUCT llamado f, que contiene un campo STRING g, cada uno de los cuales está indexado.

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

El resultado es similar al siguiente:

+------------+------------+-------------------+------------------+
| 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 siguiente consulta une la vista INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS con las vistas INFORMATION_SCHEMA.SEARCH_INDEXES y INFORMATION_SCHEMA.COLUMNS para incluir el estado del índice de búsqueda y el tipo de datos de cada columna:

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;

El resultado es similar al siguiente:

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

Opciones de administración de índices

Para crear índices y hacer que BigQuery los mantenga, tienes dos opciones:

Usa el grupo de ranuras compartido predeterminado: Cuando los datos que planeas indexar están por debajo del límite por organización, puedes usar el grupo de ranuras compartido gratuito para la administración de índices.
Usa tu propia reserva: para lograr un progreso de indexación más predecible y coherente en tus cargas de trabajo de producción más grandes, puedes usar tus propias reservas para la administración de índices.

Usar ranuras compartidas

Si no configuraste tu proyecto para usar una reserva dedicada para la indexación, la administración de índices se controla en el grupo de ranuras compartido gratuito, sujeto a las siguientes restricciones.

Si agregas datos a una tabla que hace que el tamaño total de las tablas indexadas exceda el límite de tu organización, BigQuery pausa la administración de índices para todas las tablas indexadas. Cuando esto sucede, el campo index_status en la vista INFORMATION_SCHEMA.SEARCH_INDEXES muestra PENDING DISABLEMENT y el índice se pone en cola para su eliminación. Si bien la inhabilitación del índice está pendiente, este se usa en las consultas y se te cobra por el almacenamiento del índice. Después de borrar un índice, el campo index_status muestra el índice como TEMPORARILY DISABLED. En este estado, las consultas no usan el índice y no se te cobra por el almacenamiento de este. En este caso, el código IndexUnusedReason es BASE_TABLE_TOO_LARGE.

Si borras los datos de la tabla y el tamaño total de las tablas indexadas es inferior al límite por organización, la administración de índices se reanuda para todas las tablas indexadas. El campo index_status en la vista INFORMATION_SCHEMA.SEARCH_INDEXES es ACTIVE, las consultas de búsqueda pueden usar el índice de búsqueda y se te cobra por el almacenamiento del índice.

BigQuery no garantiza la capacidad disponible del grupo compartido ni la capacidad de procesamiento de indexación que ves. En aplicaciones de producción, se recomienda usar ranuras dedicadas para el procesamiento de índices.

Usa tu propia reserva

En lugar de usar el grupo de ranuras compartido predeterminado, tienes la opción de designar tu propia reserva para indexar tus tablas. El uso de tu propia reserva garantiza un rendimiento predecible y coherente de los trabajos de administración de índices, como la creación, la actualización y las optimizaciones en segundo plano.

No hay límites de tamaño de tabla cuando se ejecuta un trabajo de indexación en tu reserva.
El uso de tu propia reserva te brinda flexibilidad en la administración de índices. Si necesitas crear un índice muy grande o aplicar una actualización importante a una tabla indexada, puedes agregar más ranuras a la asignación de forma temporal.

Para indexar las tablas en un proyecto con una reserva designada, crea una reserva en la región en la que se encuentran las tablas. Luego, asigna el proyecto a la reserva con job_type configurado como BACKGROUND:

SQL

Usa la declaración DDL CREATE ASSIGNMENT.

En la consola de Google Cloud, ve a la página de BigQuery.

Ir a BigQuery
En el editor de consultas, escribe la siguiente oración:
```
CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');
```
Reemplaza lo siguiente:
- ADMIN_PROJECT_ID por el ID del proyecto de administración que posee el recurso de reserva
- LOCATION: la ubicación de la reserva
- RESERVATION_NAME por el nombre de la reserva
- ASSIGNMENT_ID por el ID de la asignación
  
  El ID debe ser único para el proyecto y la ubicación, debe empezar y terminar con una letra minúscula o un número y contener solo letras en minúscula, números y guiones.
- PROJECT_ID: el ID del proyecto que contiene las tablas que se indexarán. Este proyecto está asignado a la reserva.
Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

bq

Usa el comando 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

Reemplaza lo siguiente:

ADMIN_PROJECT_ID por el ID del proyecto de administración que posee el recurso de reserva
LOCATION: la ubicación de la reserva
RESERVATION_NAME por el nombre de la reserva
PROJECT_ID por el ID del proyecto que se asignará a esta reserva

Ve tus trabajos de indexación

Se crea un trabajo de indexación nuevo cada vez que se crea o se actualiza un índice en una sola tabla. Para ver información sobre el trabajo, consulta las vistas de INFORMATION_SCHEMA.JOBS*. Puedes filtrar los trabajos de indexación si configuras job_type IS NULL AND SEARCH(job_id, '`search_index`') en la cláusula WHERE de tu consulta. En el siguiente ejemplo, se enumeran los cinco trabajos de indexación más recientes en el proyecto 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;

Elige el tamaño de tu reserva

Para elegir la cantidad correcta de ranuras para tu reserva, debes considerar cuándo se ejecutan los trabajos de administración de índices, cuántas ranuras usan y cómo se ve tu uso en el tiempo. BigQuery activa un trabajo de administración de índices en las siguientes situaciones:

Debes crear un índice en una tabla.
Los datos se modifican en una tabla indexada.
El esquema de una tabla cambia y esto afecta qué columnas se indexan.
Los datos y metadatos del índice se optimizan o actualizan de forma periódica.

La cantidad de ranuras que necesitas para un trabajo de administración de índices en una tabla depende de los siguientes factores:

El tamaño de la tabla
La frecuencia de la transferencia de datos a la tabla
La tasa de declaraciones DML que se aplican a la tabla
El retraso aceptable para compilar y mantener el índice
La complejidad del índice, que por lo general se determina según los atributos de los datos, como la cantidad de términos duplicados

Estimación inicial

Las siguientes estimaciones pueden ayudarte a aproximar la cantidad de ranuras que requiere tu reserva. Debido a la naturaleza altamente variable de las cargas de trabajo de indexación, debes volver a evaluar tus requisitos después de empezar a indexar datos.

Datos existentes: con una reserva de 1,000 ranuras, una tabla existente en BigQuery se puede indexar a una tasa promedio de hasta 4 GiB por segundo, que es alrededor de 336 TiB por día.
Datos transferidos recientemente: por lo general, la indexación requiere más recursos para los datos transferidos recientemente, ya que la tabla y su índice pasan por varias rondas de optimizaciones transformadoras. En promedio, indexar los datos recién transferidos consume el triple de recursos en comparación con la indexación inicial de reabastecimiento de los mismos datos.
Datos modificados con poca frecuencia: las tablas indexadas que tienen pocos o ningún cambio en los datos necesitan menos recursos para el mantenimiento continuo de los índices. Un punto de partida recomendado es mantener 1/5 de las ranuras necesarias para la indexación inicial de reabastecimiento de los mismos datos y no menos de 250 ranuras.
El progreso de indexación escala de manera aproximada con el tamaño de la reserva. Sin embargo, no recomendamos usar reservas de menos de 250 ranuras para la indexación, ya que esto podría provocar ineficiencias que puedan ralentizar el progreso de la indexación.
Estas estimaciones pueden cambiar a medida que las características, las optimizaciones y tu uso real varían.
Si el tamaño total de la tabla de tu organización supera el límite de indexación de tu región, debes mantener una reserva distinta de cero asignada para la indexación. De lo contrario, la indexación podría volver al nivel predeterminado, lo que genera una eliminación no deseada de todos los índices.

Supervisa el uso y el progreso

La mejor manera de evaluar la cantidad de ranuras que necesitas para ejecutar los trabajos de administración de índices de forma eficiente es supervisar el uso de las ranuras y ajustar el tamaño de reserva según corresponda. La siguiente consulta produce el uso diario de las ranuras para los trabajos de administración de índices. Solo se incluyen los últimos 30 días en la región 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;

Cuando no hay suficientes ranuras para ejecutar trabajos de administración de índices, un índice puede dejar de sincronizarse con su tabla y los trabajos de indexación pueden fallar. En este caso, BigQuery vuelve a compilar el índice desde cero. Para evitar tener un índice fuera de sincronización, asegúrate de tener suficientes ranuras para admitir actualizaciones de índice desde la transferencia y optimización de datos. Para obtener más información sobre la supervisión del uso de ranuras, consulta los gráficos de recursos de administrador.

Prácticas recomendadas

Los índices de la búsqueda están diseñados para tablas grandes. Las mejoras de rendimiento de un índice de búsqueda aumentan con el tamaño de la tabla.
No indexes columnas que contengan una pequeña cantidad de valores únicos.
No indexes columnas que nunca deseas usar con la función SEARCH ni con ninguna de las otras funciones y operadores compatibles.
Ten cuidado cuando crees un índice de búsqueda en ALL COLUMNS. Cada vez que agregas una columna que contiene datos STRING o JSON, se indexa.
Debes usar tu propia reserva para la administración de índices en aplicaciones de producción. Si eliges usar el grupo de ranuras compartido predeterminado para tus trabajos de administración de índices, se aplican los límites de tamaño de cada organización.

Borra un índice de búsqueda

Cuando ya no necesites un índice de búsqueda o quieras cambiar qué columnas se indexan en una tabla, puedes borrar el índice actual en esa tabla. Usa la declaración DDL DROP SEARCH INDEX.

Si se borra una tabla indexada, el índice se borra de forma automática.

Ejemplo:

DROP SEARCH INDEX my_index ON dataset.simple_table;

¿Qué sigue?

Para obtener una descripción general de los casos de uso de índices de búsqueda, los precios, los permisos necesarios y las limitaciones, consulta Introducción a la búsqueda en BigQuery.
Para obtener información sobre la búsqueda eficiente de las columnas indexadas, consulta Busca con un índice.