Buscar datos indexados

En esta página, se proporcionan ejemplos de búsqueda en BigQuery. Cuando indexas tus datos, BigQuery puede optimizar algunas consultas que usan laSEARCH función or o cualquier otra función y operador, como =, IN, LIKE y STARTS_WITH.

Las consultas en SQL muestran resultados correctos de todos los datos transferidos, incluso si algunos de los datos aún no están indexados. Sin embargo, el rendimiento de las consultas se puede mejorar en gran medida con un índice. El ahorro en bytes procesados y milisegundos de ranura se maximizan cuando la cantidad de resultados de la búsqueda representa una fracción relativamente pequeña del total de filas en tu tabla porque se analizan menos datos. Para determinar si se usó un índice para una consulta, visita Uso del índice de la búsqueda.

Crear un índice de búsqueda

La siguiente tabla llamada Logs se usa para mostrar diferentes formas de usar la función SEARCH. Esta tabla de ejemplo es bastante pequeña, pero en la práctica, las mejoras de rendimiento que obtienes con SEARCH aumentan con el tamaño de la tabla.

CREATE TABLE my_dataset.Logs (Level STRING, Source STRING, Message STRING)
AS (
  SELECT 'INFO' as Level, '65.177.8.234' as Source, 'Entry Foo-Bar created' as Message
  UNION ALL
  SELECT 'WARNING', '132.249.240.10', 'Entry Foo-Bar already exists, created by 65.177.8.234'
  UNION ALL
  SELECT 'INFO', '94.60.64.181', 'Entry Foo-Bar deleted'
  UNION ALL
  SELECT 'SEVERE', '4.113.82.10', 'Entry Foo-Bar does not exist, deleted by 94.60.64.181'
  UNION ALL
  SELECT 'INFO', '181.94.60.64', 'Entry Foo-Baz created'
);

La tabla se ve de la siguiente manera:

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 65.177.8.234   | Entry Foo-Bar created                                 |
| WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 |
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
| INFO    | 181.94.60.64   | Entry Foo-Baz created                                 |
+---------+----------------+-------------------------------------------------------+

Crea un índice de búsqueda en la tabla Logs con el analizador de texto predeterminado:

CREATE SEARCH INDEX my_index ON my_dataset.Logs(ALL COLUMNS);

Para obtener más información sobre los índices de búsqueda, consulta Administra los índices de búsqueda.

Usa la función `SEARCH`

La función SEARCH proporciona una búsqueda con asignación de tokens en los datos. SEARCH está diseñado para usarse con un índice para optimizar las búsquedas. Puedes usar la función SEARCH para buscar en una tabla completa o restringir la búsqueda a columnas específicas.

Busca en una tabla completa

La siguiente consulta busca en todas las columnas de la tabla Logs el valor bar y muestra las filas que contienen este valor, sin importar las mayúsculas. Dado que el índice de búsqueda usa el analizador de texto predeterminado, no es necesario que lo especifiques en la función SEARCH.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, 'bar');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 65.177.8.234   | Entry Foo-Bar created                                 |
| WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 |
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

La siguiente consulta busca en todas las columnas de la tabla Logs el valor `94.60.64.181` y muestra las filas que contienen este valor. Los acentos graves permiten una búsqueda exacta, por lo que se omite la última fila de la tabla Logs que contiene 181.94.60.64.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

Busca un subconjunto de columnas

SEARCH facilita la especificación de un subconjunto de columnas para buscar datos. La siguiente consulta busca en la columna Message de la tabla Logs el valor 94.60.64.181 y muestra las filas que contienen este valor.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Message, '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

La siguiente consulta busca en las columnas Source y Message de la tabla Logs. Muestra las filas que contienen el valor 94.60.64.181 de cualquiera de las columnas.

SELECT * FROM my_dataset.Logs WHERE SEARCH((Source, Message), '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

Excluye columnas de una búsqueda

Si una tabla de tabla tiene muchas columnas y deseas buscar la mayoría de ellas, puede ser más fácil especificar solo las columnas para excluir de la búsqueda. La siguiente consulta busca en todas las columnas de la tabla Logs, excepto en la columna Message. Muestra las filas de cualquier columna que no sea Message que contenga el valor 94.60.64.181.

SELECT *
FROM my_dataset.Logs
WHERE SEARCH(
  (SELECT AS STRUCT Logs.* EXCEPT (Message)), '`94.60.64.181`');

+---------+----------------+---------------------------------------------------+
| Level   | Source         | Message                                           |
+---------+----------------+---------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                             |
+---------+----------------+---------------------------------------------------+

Usar un analizador de texto diferente

En el siguiente ejemplo, se crea una tabla llamada contact_info con un índice que usa el analizador de texto NO_OP_ANALYZER:

CREATE TABLE my_dataset.contact_info (name STRING, email STRING)
AS (
  SELECT 'Kim Lee' AS name, 'kim.lee@example.com' AS email
  UNION ALL
  SELECT 'Kim' AS name, 'kim@example.com' AS email
  UNION ALL
  SELECT 'Sasha' AS name, 'sasha@example.com' AS email
);
CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

+---------+---------------------+
| name    | email               |
+---------+---------------------+
| Kim Lee | kim.lee@example.com |
| Kim     | kim@example.com     |
| Sasha   | sasha@example.com   |
+---------+---------------------+

La siguiente consulta busca Kim en la columna name y kim en la columna email. Dado que el índice de búsqueda no usa el analizador de texto predeterminado, debes pasar el nombre del analizador a la función SEARCH.

SELECT
  name,
  SEARCH(name, 'Kim', analyzer=>'NO_OP_ANALYZER') AS name_Kim,
  email,
  SEARCH(email, 'kim', analyzer=>'NO_OP_ANALYZER') AS email_kim
FROM
  my_dataset.contact_info;

NO_OP_ANALYZER no modifica el texto, por lo que la función SEARCH solo muestra TRUE para coincidencias exactas:

+---------+----------+---------------------+-----------+
| name    | name_Kim | email               | email_kim |
+---------+----------+---------------------+-----------+
| Kim Lee | FALSE    | kim.lee@example.com | FALSE     |
| Kim     | TRUE     | kim@example.com     | FALSE     |
| Sasha   | FALSE    | sasha@example.com   | FALSE     |
+---------+----------+---------------------+-----------+

Configura las opciones del analizador de texto

Los analizadores de textos LOG_ANALYZER y PATTERN_ANALYZER se pueden personalizar si agregas una string con formato JSON a las opciones de configuración. Puedes configurar los analizadores de texto en la función SEARCH, la declaración DDL CREATE SEARCH INDEX y la función TEXT_ANALYZE.

En el siguiente ejemplo, se crea una tabla llamada complex_table con un índice que usa el analizador de texto LOG_ANALYZER. Usa una string con formato JSON para configurar las opciones del analizador:

CREATE TABLE dataset.complex_table(
  a STRING,
  my_struct STRUCT<string_field STRING, int_field INT64>,
  b ARRAY<STRING>
);

CREATE SEARCH INDEX my_index
ON dataset.complex_table(a, my_struct, b)
OPTIONS (analyzer = 'LOG_ANALYZER', analyzer_options = '''{
  "token_filters": [
    {
      "normalization": {"mode": "NONE"}
    }
  ]
}''');

En las siguientes tablas, se muestran ejemplos de llamadas a la función SEARCH con diferentes analizadores de texto y sus resultados. La primera tabla llama a la función SEARCH mediante el analizador de texto predeterminado, LOG_ANALYZER:

Llamada a función	Devoluciones	Motivo
SEARCH('foobarexample', NULL)	ERROR	El valor de search_terms es `NULL`.
SEARCH('foobarexample', '')	ERROR	No hay tokens en search_terms.
SEARCH('foobar-example', 'foobar example')	VERDADERO	'-' y ' ' son delimitadores.
SEARCH('foobar-example', 'foobarexample')	FALSO	El elemento search_terms no está dividido.
SEARCH('foobar-example', 'foobar\\&example')	VERDADERO	La barra inversa doble escapa el signo et, que es un delimitador.
SEARCH('foobar-example', R'foobar\&example')	VERDADERO	La barra inversa única escapa el signo et en una cadena sin procesar.
SEARCH('foobar-example', '`foobar&example`')	FALSO	Los acentos graves requieren una coincidencia exacta para foobar&example.
SEARCH('foobar&example', '`foobar&example`')	VERDADERO	Se encontró una coincidencia exacta.
SEARCH('foobar-example', 'example foobar')	VERDADERO	No importa el orden de los prefijos.
SEARCH('foobar-example', 'foobar example')	VERDADERO	Los tokens están en minúsculas.
SEARCH('foobar-example', '`foobar-example`')	VERDADERO	Se encontró una coincidencia exacta.
SEARCH('foobar-example', '`foobar`')	FALSO	Los acentos graves conservan el uso de mayúsculas.
SEARCH('`foobar-example`', '`foobar-example`')	FALSO	Los acentos graves no tienen un significado especial para data_to_search y
SEARCH('foobar@example.com', '`example.com`')	VERDADERO	Se encuentra una coincidencia exacta después del delimitador en data_to_search.
SEARCH('a foobar-example b', '`foobar-example`')	VERDADERO	Se encuentra una coincidencia exacta entre los delimitadores de espacio.
SEARCH(['foobar', 'example'], 'foobar example')	FALSO	Ninguna entrada de arreglo coincide con todos los términos de búsqueda.
SEARCH('foobar=', '`foobar\\=`')	FALSO	El valor de search_terms es equivalente a foobar\=.
SEARCH('foobar=', R'`foobar\=`')	FALSO	Esto equivale al ejemplo anterior.
SEARCH('foobar=', 'foobar\\=')	VERDADERO	El signo igual es un delimitador en los datos y la consulta.
SEARCH('foobar=', R'foobar\=')	VERDADERO	Esto equivale al ejemplo anterior.
SEARCH('foobar.example', '`foobar`')	VERDADERO	Se encontró una coincidencia exacta.
SEARCH('foobar.example', '`foobar.`')	FALSO	`foobar.` no se analiza debido a los acentos graves; no es
SEARCH('foobar..example', '`foobar.`')	VERDADERO	`foobar.` no se analiza debido a los acentos graves; le sigue

En la siguiente tabla, se muestran ejemplos de llamadas a la función SEARCH mediante el analizador de texto NO_OP_ANALYZER y las razones de varios valores de retorno:

Llamada a función:	Devoluciones	Motivo
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	VERDADERO	Se encontró una coincidencia exacta.
SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER')	FALSO	Los acentos graves no son caracteres especiales para NO_OP_ANALYZER.
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSO	El uso de mayúsculas no coincide.
SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSO	No hay delimitadores para NO_OP_ANALYZER.
SEARCH('', '', analyzer=>'NO_OP_ANALYZER')	VERDADERO	No hay delimitadores para NO_OP_ANALYZER.

Otros operadores y funciones

Puedes realizar optimizaciones de índices de búsqueda con varios operadores, funciones y predicados.

Optimización con operadores y funciones de comparación

BigQuery puede optimizar algunas consultas que usan el operador igual (=), el operador IN, el operador LIKE o la STARTS_WITH función para comparar literales de cadena con datos indexados.

Optimiza con predicados de cadena

Los siguientes predicados son aptos para la optimización del índice de búsqueda:

column_name = 'string_literal'
'string_literal' = column_name
struct_column.nested_field = 'string_literal'
string_array_column[OFFSET(0)] = 'string_literal'
string_array_column[ORDINAL(1)] = 'string_literal'
column_name IN ('string_literal1', 'string_literal2', ...)
STARTS_WITH(column_name, 'prefix')
column_name LIKE 'prefix%'

Realiza optimizaciones con predicados numéricos

Para obtener asistencia durante la vista previa, envía un correo electrónico a bq-search-team@google.com.

Si el índice de búsqueda se creó con tipos de datos numéricos, BigQuery puede optimizar algunas consultas que usan el operador igual (=) o el operador IN con datos indexados. Los siguientes predicados son aptos para la optimización del índice de búsqueda:

INT64(json_column.int64_field) = 1
int64_column = 1
int64_array_column[OFFSET(0)] = 1
int64_column IN (1, 2)
struct_column.nested_int64_field = 1
struct_column.nested_timestamp_field = TIMESTAMP "2024-02-15 21:31:40"
timestamp_column = "2024-02-15 21:31:40"
timestamp_column IN ("2024-02-15 21:31:40", "2024-02-16 21:31:40")

Optimiza las funciones que producen datos indexados

BigQuery admite la optimización del índice de búsqueda cuando se aplican ciertas funciones a los datos indexados. Si el índice de búsqueda usa el analizador de texto LOG_ANALYZER predeterminado, puedes aplicar las funciones UPPER o LOWER a la columna, como UPPER(column_name) = 'STRING_LITERAL'.

Para datos de cadenas escalares JSON extraídos de una columna JSON indexada, puedes aplicar la función STRING o su versión segura, SAFE.STRING. Si el valor JSON extraído no es una cadena, la función STRING produce un error y la función SAFE.STRING muestra NULL.

Para los datos indexados de STRING en formato JSON (no JSON), puedes aplicar las siguientes funciones:

Por ejemplo, supongamos que tienes la siguiente tabla indexada llamada dataset.person_data con una columna JSON y una STRING:

+----------------------------------------------------------------+-----------------------------------------+
| json_column                                                    | string_column                           |
+----------------------------------------------------------------+-----------------------------------------+
| { "name" : "Ariel", "email" : "cloudysanfrancisco@gmail.com" } | { "name" : "Ariel", "job" : "doctor" }  |
+----------------------------------------------------------------+-----------------------------------------+

Las siguientes consultas son aptas para la optimización:

SELECT * FROM dataset.person_data
WHERE SAFE.STRING(json_column.email) = 'cloudysanfrancisco@gmail.com';

SELECT * FROM dataset.person_data
WHERE JSON_VALUE(string_column, '$.job') IN ('doctor', 'lawyer', 'teacher');

También se optimizan las combinaciones de estas funciones, como UPPER(JSON_VALUE(json_string_expression)) = 'FOO'.

Uso de Índice de la Búsqueda

A fin de determinar si se usó un índice de búsqueda para una consulta, revisa la Información del trabajo de la consulta en los resultados de la consulta. Los campos Modo de uso de índice y Motivos de índice no utilizado proporcionan información detallada sobre el uso del índice de búsqueda.

La información sobre el uso del índice de búsqueda también está disponible a través del campo searchStatistics en el método de la API Jobs.Get. El campo indexUsageMode en searchStatistics indica si se usó un índice de búsqueda con los siguientes valores:

UNUSED: No se usó ningún índice de búsqueda.
PARTIALLY_USED: Parte de la consulta usó los índices de búsqueda y parte no.
FULLY_USED: Cada función SEARCH en la consulta usó un índice de búsqueda.

Cuando indexUsageMode es UNUSED o PARTIALLY_USED, el campo indexUnusuedReasons contiene información sobre por qué los índices de búsqueda no se usaron en la consulta.

A fin de ver searchStatistics para una consulta, ejecuta el comando bq show.

bq show --format=prettyjson -j JOB_ID

Ejemplo

Supongamos que ejecutas una consulta que llama a la función SEARCH en los datos de una tabla. Puedes ver los detalles del trabajo de la consulta para encontrar el ID del trabajo y, luego, ejecutar el comando bq show para ver más información:

bq show --format=prettyjson --j my_project:US.bquijob_123x456_789y123z456c

El resultado contiene muchos campos, incluido searchStatistics, que es similar a lo siguiente. En este ejemplo, indexUsageMode indica que no se usó el índice. El motivo es que la tabla no tiene un índice de búsqueda. Para resolver este problema, crea un índice de búsqueda en la tabla. Consulta el campo code indexUnusedReason para obtener una lista de todos los motivos por los que un índice de búsqueda podría no usarse en una consulta.

"searchStatistics": {
  "indexUnusedReasons": [
    {
      "baseTable": {
        "datasetId": "my_dataset",
        "projectId": "my_project",
        "tableId": "my_table"
      },
      "code": "INDEX_CONFIG_NOT_AVAILABLE",
      "message": "There is no search index configuration for the base table `my_project:my_dataset.my_table`."
    }
  ],
  "indexUsageMode": "UNUSED"
},

Prácticas recomendadas

En las siguientes secciones, se describen las prácticas recomendadas para usar la función SEARCH.

Busca de forma selectiva

La búsqueda funciona mejor cuando tu búsqueda tiene pocos resultados. Haz que tus búsquedas sean lo más específicas posible.

Optimización de ORDER BY LIMIT

Las consultas que usan SEARCH, =, IN, LIKE o STARTS_WITH en una tabla particionada muy grande se pueden optimizar en los siguientes casos: cuando usas una cláusula ORDER BY en el campo particionado y una cláusula LIMIT. Para las consultas que no contienen la función SEARCH, puedes usar otros operadores y funciones para aprovechar la optimización. La optimización se aplica sin importar si la tabla está indexada o no. Esto funciona bien si buscas un término común. Por ejemplo, supongamos que la tabla Logs creada antes está particionada en una columna de tipo DATE adicional llamada day. La siguiente consulta está optimizada:

SELECT
  Level, Source, Message
FROM
  my_dataset.Logs
WHERE
  SEARCH(Message, "foo")
ORDER BY
  day
LIMIT 10;

Limita el alcance de tu búsqueda

Cuando usas la función SEARCH, solo busca las columnas de la tabla que esperas que contengan tus términos de búsqueda. Esto mejora el rendimiento y reduce la cantidad de bytes que se deben analizar.

Usa backticks

Cuando usas la función SEARCH con el analizador de texto LOG_ANALYZER, encierra tu consulta de búsqueda entre acentos graves. Esto es útil si tu búsqueda distingue mayúsculas de minúsculas o contiene caracteres que no deben interpretarse como interruptores. Por ejemplo, para buscar la dirección IP 192.0.2.1, usa `192.0.2.1`. Sin los acentos graves, la búsqueda muestra cualquier fila que contenga los tokens individuales 192, 0, 2 y 1, en cualquier orden.