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
. Si no se especifica un tipo de datos para una columna, puedes crear un índice de búsqueda en estos tipos de columnas:
STRING
ARRAY<STRING>
STRUCT
que contenga al menos un campo anidado de tipoSTRING
oARRAY<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.
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 reservaLOCATION
: la ubicación de la reservaRESERVATION_NAME
por el nombre de la reservaASSIGNMENT_ID
por el ID de la asignaciónEl 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 reservaLOCATION
: la ubicación de la reservaRESERVATION_NAME
por el nombre de la reservaPROJECT_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 datosSTRING
oJSON
, 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. Para ello, 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.