Esta página foi traduzida pela API Cloud Translation.

Pesquise dados indexados

Esta página oferece exemplos de pesquisas de dados de tabelas no BigQuery. Quando indexa os seus dados, o BigQuery pode otimizar algumas consultas que usam a função SEARCH ou outras funções e operadores, como =, IN, LIKE e STARTS_WITH.

As consultas SQL devolvem resultados corretos de todos os dados carregados, mesmo que alguns dos dados ainda não estejam indexados. No entanto, o desempenho das consultas pode ser muito melhorado com um índice. As poupanças em bytes processados e milissegundos de espaço são maximizadas quando o número de resultados da pesquisa representa uma fração relativamente pequena do total de linhas na tabela, porque são analisados menos dados. Para determinar se foi usado um índice para uma consulta, consulte a utilização do índice de pesquisa.

Crie um índice de pesquisa

A tabela seguinte, denominada Logs, é usada para mostrar diferentes formas de usar a função SEARCH. Esta tabela de exemplo é bastante pequena, mas, na prática, os ganhos de desempenho que obtém com SEARCH melhoram com o tamanho da tabela.

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

A tabela tem o seguinte aspeto:

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

Crie um índice de pesquisa na tabela Logs com o analisador de texto predefinido:

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

Para mais informações acerca dos índices de pesquisa, consulte o artigo Gerir índices de pesquisa.

Use a função `SEARCH`

A função SEARCH oferece uma pesquisa tokenizada nos dados. SEARCH foi concebido para ser usado com um índice para otimizar as pesquisas. Pode usar a função SEARCH para pesquisar uma tabela inteira ou restringir a pesquisa a colunas específicas.

Pesquise uma tabela inteira

A seguinte consulta pesquisa em todas as colunas da tabela Logs o valor bar e devolve as linhas que contêm este valor, independentemente das letras maiúsculas. Uma vez que o índice de pesquisa usa o analisador de texto predefinido, não tem de o especificar na função 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 |
+---------+----------------+-------------------------------------------------------+

A seguinte consulta pesquisa em todas as colunas da tabela Logs o valor `94.60.64.181` e devolve as linhas que contêm este valor. Os acentos graves permitem uma pesquisa exata, pelo que a última linha da tabela que contém 181.94.60.64 é omitida.Logs

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

Pesquise um subconjunto de colunas

SEARCH facilita a especificação de um subconjunto de colunas no qual pesquisar dados. A seguinte consulta pesquisa a coluna Message da tabela Logs para o valor 94.60.64.181 e devolve as linhas que contêm 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 |
+---------+----------------+-------------------------------------------------------+

A seguinte consulta pesquisa as colunas Source e Message da tabela Logs. Devolve as linhas que contêm o valor 94.60.64.181 de qualquer uma das colunas.

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

Exclua colunas de uma pesquisa

Se uma tabela tiver muitas colunas e quiser pesquisar a maioria delas, pode ser mais fácil especificar apenas as colunas a excluir da pesquisa. A seguinte consulta pesquisa em todas as colunas da tabela Logs, exceto na coluna Message. Devolve as linhas de quaisquer colunas que não sejam Message que contenham o 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                             |
+---------+----------------+---------------------------------------------------+

Use um analisador de texto diferente

O exemplo seguinte cria uma tabela denominada contact_info com um índice que usa o NO_OP_ANALYZER analisador de texto:

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

A seguinte consulta pesquisa Kim na coluna name e kim na coluna email. Uma vez que o índice de pesquisa não usa o analisador de texto predefinido, tem de transmitir o nome do analisador à função 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;

O NO_OP_ANALYZER não modifica o texto, pelo que a função SEARCH só devolve TRUE para correspondências exatas:

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

Configure as opções do analisador de texto

Os LOG_ANALYZER e os PATTERN_ANALYZER analisadores de texto podem ser personalizados adicionando uma string de formato JSON às opções de configuração. Pode configurar analisadores de texto na SEARCH função, na CREATE SEARCH INDEX declaração DDL> e na TEXT_ANALYZE função.

O exemplo seguinte cria uma tabela denominada complex_table com um índice que usa o analisador de texto LOG_ANALYZER. Usa uma string de formato JSON para configurar as opções do analisador:

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

As tabelas seguintes mostram exemplos de chamadas para a função SEARCH com diferentes analisadores de texto e os respetivos resultados. A primeira tabela chama a função SEARCH usando o analisador de texto predefinido, o LOG_ANALYZER:

Chamada de função	Devoluções	Motivo
PROCURAR("foobarexample"; NULL)	ERRO	O argumento search_terms é "NULL".
PROCURAR('foobarexample', '')	ERRO	O elemento search_terms não contém tokens.
PESQUISAR('foobar-example'; 'foobar example')	TRUE	"-" e " " são delimitadores.
PROCURAR('foobar-example'; 'foobarexample')	FALSE	O search_terms não está dividido.
PESQUISAR('foobar-example', 'foobar\\&example')	TRUE	A barra invertida dupla escapa o "E" comercial, que é um delimitador.
SEARCH('foobar-example', R'foobar\&example')	TRUE	A barra invertida única escapa o e comercial numa string bruta.
SEARCH('foobar-example', '`foobar&example`')	FALSE	Os acentos graves requerem uma correspondência exata para foobar&example.
SEARCH('foobar&example', '`foobar&example`')	TRUE	É encontrada uma correspondência exata.
PESQUISAR('foobar-example', 'example foobar')	TRUE	A ordem dos termos não é importante.
PESQUISAR('foobar-example'; 'foobar example')	TRUE	Os tokens são convertidos em minúsculas.
PESQUISAR('foobar-example', '`foobar-example`')	TRUE	É encontrada uma correspondência exata.
PESQUISAR('foobar-example', '`foobar`')	FALSE	Os acentos graves preservam as letras maiúsculas.
SEARCH('`foobar-example`', '`foobar-example`')	FALSE	Os acentos graves não têm um significado especial para data_to_search e
PESQUISAR('foobar@example.com', '`example.com`')	TRUE	É encontrada uma correspondência exata após o delimitador em dados_a_pesquisar.
SEARCH("a foobar-example b", "`foobar-example`")	TRUE	É encontrada uma correspondência exata entre os delimitadores de espaço.
PESQUISAR(['foobar', 'example'], 'foobar example')	FALSE	Nenhuma entrada de matriz única corresponde a todos os termos de pesquisa.
SEARCH('foobar=', '`foobar\\=`')	FALSE	O search_terms é equivalente a foobar\=.
SEARCH('foobar=', R'`foobar\=`')	FALSE	Isto é equivalente ao exemplo anterior.
PROCURAR('foobar=', 'foobar\\=')	TRUE	O sinal de igual é um delimitador nos dados e na consulta.
SEARCH('foobar=', R'foobar\=')	TRUE	Isto é equivalente ao exemplo anterior.
PESQUISAR('foobar.example', '`foobar`')	TRUE	É encontrada uma correspondência exata.
PESQUISAR('foobar.example', '`foobar.`')	FALSE	`foobar.` não é analisado devido aos acentos graves; não é
PESQUISAR('foobar..example'; '`foobar.`')	TRUE	`foobar.` não é analisado devido aos acentos graves; é seguido

A tabela seguinte mostra exemplos de chamadas à função SEARCH usando o analisador de texto NO_OP_ANALYZER e os motivos para vários valores de retorno:

Chamada de função	Devoluções	Motivo
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	TRUE	É encontrada uma correspondência exata.
SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER')	FALSE	Os acentos graves não são carateres especiais para NO_OP_ANALYZER.
SEARCH('Foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSE	As letras maiúsculas e minúsculas não correspondem.
SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSE	Não existem delimitadores para NO_OP_ANALYZER.
SEARCH('', '', analyzer=>'NO_OP_ANALYZER')	TRUE	Não existem delimitadores para NO_OP_ANALYZER.

Outros operadores e funções

Pode fazer otimizações do índice de pesquisa com vários operadores, funções e predicados.

Otimize com operadores e funções de comparação

O BigQuery pode otimizar algumas consultas que usam o operador igual a (=), o operadorIN, o operadorLIKE ou a função STARTS_WITH para comparar literais de strings com dados indexados.

Otimize com predicados de string

Os seguintes predicados são elegíveis para a otimização do índice de pesquisa:

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%'

Otimize com predicados numéricos

Se o índice de pesquisa tiver sido criado com tipos de dados numéricos, o BigQuery pode otimizar algumas consultas que usam o operador igual a (=) ou o operador IN com dados indexados. Os seguintes predicados são elegíveis para a otimização do índice de pesquisa:

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")

Otimize as funções que produzem dados indexados

O BigQuery suporta a otimização do índice de pesquisa quando determinadas funções são aplicadas a dados indexados. Se o índice de pesquisa usar o analisador de texto LOG_ANALYZER predefinido, pode aplicar as funções UPPER ou LOWER à coluna, como UPPER(column_name) = 'STRING_LITERAL'.

Para dados de strings escalares JSON extraídos de uma coluna JSON indexada, pode aplicar a função STRING ou a respetiva versão segura, SAFE.STRING. Se o valor JSON extraído não for uma string, a função STRING produz um erro e a função SAFE.STRING devolve NULL.

Para dados indexados no formato JSON STRING (não JSON), pode aplicar as seguintes funções:

Por exemplo, suponha que tem a seguinte tabela indexada denominada dataset.person_data com uma coluna JSON e uma coluna STRING:

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

As seguintes consultas são elegíveis para otimização:

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

As combinações destas funções também são otimizadas, como UPPER(JSON_VALUE(json_string_expression)) = 'FOO'.

Utilização do índice de pesquisa

Para determinar se foi usado um índice de pesquisa para uma consulta, pode ver os detalhes da tarefa ou consultar uma das vistas INFORMATION_SCHEMA.JOBS*.

Ver detalhes do trabalho

Em Informações do trabalho dos Resultados da consulta, os campos Modo de utilização do índice e Motivos de não utilização do índice fornecem informações detalhadas sobre a utilização do índice de pesquisa.

As informações sobre a utilização do índice de pesquisa também estão disponíveis através do campo searchStatistics no método da API Jobs.Get. O campo indexUsageMode em searchStatistics indica se foi usado um índice de pesquisa com os seguintes valores:

UNUSED: não foi usado nenhum índice de pesquisa.
PARTIALLY_USED: parte da consulta usou índices de pesquisa e parte não.
FULLY_USED: todas as funções SEARCH na consulta usaram um índice de pesquisa.

Quando indexUsageMode é UNUSED ou PARTIALLY_USED, o campo indexUnusuedReasons contém informações sobre o motivo pelo qual os índices de pesquisa não foram usados na consulta.

Para ver searchStatistics para uma consulta, execute o comando bq show.

bq show --format=prettyjson -j JOB_ID

Exemplo

Suponhamos que executa uma consulta que chama a função SEARCH em dados numa tabela. Pode ver os detalhes da tarefa da consulta para encontrar o ID da tarefa e, em seguida, executar o comando bq show para ver mais informações:

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

A saída contém muitos campos, incluindo searchStatistics, que se assemelham ao seguinte. Neste exemplo, indexUsageMode indica que o índice não foi usado. O motivo é que a tabela não tem um índice de pesquisa. Para resolver este problema, crie um índice de pesquisa na tabela. Consulte o campo indexUnusedReason code para ver uma lista de todos os motivos pelos quais um índice de pesquisa pode não ser usado numa 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"
},

Consultar visualizações de propriedades INFORMATION_SCHEMA

Também pode ver a utilização do índice de pesquisa para várias tarefas numa região nas seguintes vistas:

A seguinte consulta mostra informações sobre a utilização do índice para todas as consultas otimizáveis do índice de pesquisa nos últimos 7 dias:

SELECT
  job_id, search_statistics.index_usage_mode, index_unused_reason.code, index_unused_reason.base_table.table_id, index_unused_reason.index_name
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS, UNNEST(search_statistics.index_unused_reasons) AS index_unused_reason
WHERE
  end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP();

O resultado é semelhante ao seguinte:

+-----------+----------------------------------------+-----------------------+
| job_id    | index_usage_mode | code                | table_id | index_name |
+-----------+------------------+---------------------+-----------------------+
| bquxjob_1 | UNUSED           | BASE_TABLE_TOO_SMALL| my_table | my_index   |
| bquxjob_2 | FULLY_USED       | NULL                | my_table | my_index   |
+-----------+----------------------------------------+-----------------------+

Práticas recomendadas

As secções seguintes descrevem as práticas recomendadas para a pesquisa.

Pesquise seletivamente

A pesquisa funciona melhor quando tem poucos resultados. Torne as suas pesquisas o mais específicas possível.

Otimização ORDER BY LIMIT

As consultas que usam SEARCH, =, IN, LIKE ou STARTS_WITH numa tabela com partições muito grande podem ser otimizadas quando usa uma cláusula ORDER BY no campo com partições e uma cláusula LIMIT. Para consultas que não contêm a função SEARCH, pode usar os outros operadores e funções para tirar partido da otimização. A otimização é aplicada independentemente de a tabela estar ou não indexada. Esta opção funciona bem se estiver a pesquisar um termo comum. Por exemplo, suponhamos que a Logstabela criada anteriormente está particionada numa coluna de tipo DATE adicional denominada day. A seguinte consulta está otimizada:

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

Defina o âmbito da sua pesquisa

Quando usar a função SEARCH, pesquise apenas nas colunas da tabela que espera que contenham os seus termos de pesquisa. Isto melhora o desempenho e reduz o número de bytes que têm de ser analisados.

Use acentos graves

Quando usa a função SEARCH com o analisador de texto LOG_ANALYZER, colocar a consulta de pesquisa entre acentos graves força uma correspondência exata. Isto é útil se a sua pesquisa for sensível a maiúsculas e minúsculas ou contiver carateres que não devem ser interpretados como delimitadores. Por exemplo, para pesquisar o endereço IP 192.0.2.1, use `192.0.2.1`. Sem as acentuações graves, a pesquisa devolve qualquer linha que contenha os tokens individuais 192, 0, 2 e 1, em qualquer ordem.