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 | Muestra | 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: | Muestra | 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_namestruct_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
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) = 1int64_column = 1int64_array_column[OFFSET(0)] = 1int64_column IN (1, 2)struct_column.nested_int64_field = 1struct_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ónSEARCHen 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 realizar búsquedas.
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.