Gerenciar índices de pesquisa

Um índice de pesquisa é uma estrutura de dados projetada para permitir uma pesquisa muito eficiente com a função SEARCH. Um índice da Pesquisa também pode otimizar algumas consultas que usam funções e operadores com suporte.

Assim como o índice encontrado no verso de um livro, um índice de pesquisa para uma coluna de dados de string funciona como uma tabela auxiliar que tem uma coluna para palavras únicas e outra para os dados nos quais palavras ocorrem.

Criar um índice de pesquisa.

Para criar um índice de pesquisa, use a instrução DDL CREATE SEARCH INDEX. Se um tipo de dados não for especificado para uma coluna, você poderá criar um índice de pesquisa nestes tipos de coluna:

  • STRING
  • ARRAY<STRING>
  • STRUCT contendo pelo menos um campo aninhado do tipo STRING ou ARRAY<STRING>
  • JSON

Ao criar um índice de pesquisa, você pode especificar o tipo de analisador de texto a ser usado. O analisador de texto controla como os dados são tokenizados para indexação e pesquisa. O padrão é LOG_ANALYZER. Esse analisador funciona bem para registros gerados por máquina e tem regras especiais sobre tokens geralmente encontrados em dados de observabilidade, como endereços IP ou e-mails. Use o NO_OP_ANALYZER quando tiver dados pré-processados que você quer corresponder exatamente. PATTERN_ANALYZER extrai tokens do texto usando uma expressão regular.

Criar um índice de pesquisa com o analisador de texto padrão

No exemplo a seguir, um índice de pesquisa é criado nas colunas a e c de simple_table e usa o analisador de texto LOG_ANALYZER por padrão:

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

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

Criar um índice de pesquisa em todas as colunas com o analisador NO_OP_ANALYZER

Quando você cria um índice de pesquisa em ALL COLUMNS, todos os dados de STRING ou JSON na tabela são indexados. Se a tabela não tiver esses dados, por exemplo, se todas as colunas contiverem números inteiros, a criação do índice falhará. Quando você especifica uma coluna STRUCT a ser indexada, todos os subcampos aninhados são indexados.

No exemplo a seguir, um índice de pesquisa é criado em a, c.e e c.f.g e usa o analisador 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');

Como o índice de pesquisa foi criado em ALL COLUMNS, quaisquer colunas adicionadas à tabela serão indexadas automaticamente se contiverem dados STRING.

Criar um índice de pesquisa e especificar as colunas e os tipos de dados

Ao criar um índice de pesquisa, você pode especificar os tipos de dados a serem usados. Os tipos de dados controlam tipos de colunas e subcampos das colunas JSON e STRUCT para indexação. O tipo de dados padrão para indexação é STRING. Para criar um índice de pesquisa com mais tipos de dados (por exemplo, tipos numéricos), use a instrução CREATE SEARCH INDEX com a opção data_types incluída.

No exemplo a seguir, um índice de pesquisa é criado nas colunas a, b, c e d de uma tabela chamada simple_table. Os tipos de dados compatíveis são STRING, INT64 e 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']);

Criar um índice de pesquisa em todas as colunas e especificar os tipos de dados

Quando você cria um índice de pesquisa em ALL COLUMNS com a opção data_types especificada, qualquer coluna que corresponde a um dos tipos de dados especificados é indexada. Para colunas JSON e STRUCT, todos os subcampos aninhados que correspondem a um dos tipos de dados especificados são indexados.

No exemplo a seguir, um índice de pesquisa é criado em ALL COLUMNS com os tipos de dados especificados. As colunas a, b, c, d.e, d.f, d.g.h e d.g.i de uma tabela chamada my_table são indexadas:

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

Como o índice de pesquisa foi criado em ALL COLUMNS, todas as colunas adicionadas à tabela serão indexadas automaticamente se corresponderem a qualquer um dos tipos de dados especificados.

A atualização do índice

Os índices de pesquisa são totalmente gerenciados pelo BigQuery e atualizados automaticamente quando a tabela é alterada. As seguintes alterações de esquema na tabela podem acionar uma atualização completa:

  • Uma nova coluna indexável é adicionada a uma tabela com um índice de pesquisa em ALL COLUMNS.
  • Uma coluna indexada foi atualizada devido a uma mudança no esquema da tabela.

Se você excluir a única coluna indexada de uma tabela ou renomeá-la, o índice de pesquisa será excluído automaticamente.

Os índices de pesquisa são projetados para tabelas grandes. Se você criar um índice de pesquisa em uma tabela menor que 10 GB, ele não será preenchido. Da mesma forma, se você excluir os dados de uma tabela indexada e o tamanho da tabela ficar abaixo de 10 GB, o índice será temporariamente desativado. Nesse caso, as consultas de pesquisa não usam o índice e o código IndexUnusedReason é BASE_TABLE_TOO_SMALL. Isso acontece independentemente de você usar ou não sua própria reserva para os jobs de gerenciamento de índice. Quando o tamanho de uma tabela indexada excede 10 GB, seu índice é preenchido automaticamente. Você não será cobrado pelo armazenamento até que o índice de pesquisa seja preenchido e esteja ativo. As consultas que usam a função SEARCH sempre retornam os resultados corretos, mesmo que alguns dados ainda não tenham sido indexados.

Receber informações sobre índices de pesquisa

É possível verificar a existência e a prontidão de um índice de pesquisa consultando INFORMATION_SCHEMA. Há duas visualizações que contêm metadados nos índices da pesquisa. A visualização INFORMATION_SCHEMA.SEARCH_INDEXES tem informações sobre cada índice de pesquisa criado em um conjunto de dados. A visualização INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS tem informações sobre quais colunas de cada tabela no conjunto de dados são indexadas.

O exemplo a seguir mostra todos os índices de pesquisa ativos nas tabelas do conjunto de dados my_dataset, localizada no projeto my_project. Isso inclui os nomes, as instruções DDL para usá-las, a porcentagem de cobertura e o analisador de texto. Se uma tabela base indexada for menor que 10 GB, o índice dela não será preenchido. Nesse 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';

Os resultados vão ter a aparência abaixo:

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

O exemplo a seguir cria um índice de pesquisa em todas as colunas 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);

A consulta a seguir extrai informações sobre quais campos são indexados. O index_field_path indica qual campo de uma coluna é indexado. Isso só difere do index_column_name no caso de um STRUCT, em que o caminho completo para o campo indexado é fornecido. Neste exemplo, a coluna c contém um campo ARRAY<STRING> e e outro STRUCT chamado f, que contém um campo STRING g, e cada um deles é indexado.

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

O resultado será semelhante ao seguinte:

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

A consulta a seguir mescla a visualização INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS com as visualizações INFORMATION_SCHEMA.SEARCH_INDEXES e INFORMATION_SCHEMA.COLUMNS para incluir o status do índice de pesquisa e o tipo de dados de cada coluna:

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;

O resultado será semelhante ao seguinte: 

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

Opções de gerenciamento de índice

Para criar índices vetoriais e fazer o BigQuery mantê-los, há duas opções:

  • Use o pool de slots compartilhado padrão: quando os dados que você planeja indexar estiverem abaixo do limite por organização, será possível usar o pool de slots compartilhado para gerenciamento de índices.
  • Use sua própria reserva: para atingir um progresso de indexação mais previsível e consistente em cargas de trabalho de produção maiores, use suas próprias reservas para o gerenciamento de índices.

Usar slots compartilhados

Se você não tiver configurado o projeto para usar uma reserva dedicada para indexação, o gerenciamento de índices será processado no pool de slots compartilhado e sem custos financeiros, sujeito às restrições a seguir.

Se você adicionar dados a uma tabela base que faça com que o tamanho total das tabelas indexadas excedam o limite da sua organização, o BigQuery pausará o gerenciamento de índice em todas as tabelas indexadas. Quando isso acontece, o campo index_status na visualização INFORMATION_SCHEMA.SEARCH_INDEXES exibe PENDING DISABLEMENT e o índice é colocado em fila para exclusão. Embora o índice esteja com a desativação pendente, ele ainda é usado nas consultas e há cobrança pelo armazenamento do índice. Depois que um índice é excluído, o campo index_status mostra o índice como TEMPORARILY DISABLED. Nesse estado, as consultas não usam o índice e não há cobrança pelo armazenamento dele. Nesse caso, o código IndexUnusedReason é BASE_TABLE_TOO_LARGE.

Se você excluir os dados da tabela base e o tamanho total das tabelas base indexadas estiver abaixo do limite por organização, o gerenciamento de índice será retomado para todas as tabelas base indexadas. O campo index_status na visualização INFORMATION_SCHEMA.SEARCH_INDEXES é ACTIVE, as consultas podem usar o índice e há cobrança pelo armazenamento do índice.

O BigQuery não oferece garantias sobre a capacidade disponível do pool compartilhado ou a capacidade de indexação que você vê. Para aplicativos de produção, convém usar slots dedicados para o processamento de índice.

Usar sua própria reserva

Em vez de usar o pool de slots compartilhado padrão, é possível designar sua própria reserva para indexar as tabelas. Usar sua própria reserva garante um desempenho previsível e consistente de jobs de gerenciamento de índice, como criação, atualização e otimizações em segundo plano.

  • Não há limites de tamanho de tabela quando um job de indexação for executado na reserva.
  • O uso da sua própria reserva oferece flexibilidade no gerenciamento do índice. Se você precisar criar um índice muito grande ou fazer uma grande atualização para uma tabela indexada, adicione temporariamente mais slots à atribuição.

Para indexar as tabelas em um projeto com uma reserva designada, crie uma reserva na região onde as tabelas base estão localizadas. Em seguida, atribua o projeto à reserva com o job_type definido como BACKGROUND:

SQL

Use a instrução DDL CREATE ASSIGNMENT.

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

    Substitua:

    • ADMIN_PROJECT_ID: o ID do projeto de administração que é proprietário do recurso de reserva.
    • LOCATION: o local da reserva
    • RESERVATION_NAME: o nome da reserva

    • ASSIGNMENT_ID: o ID da atribuição.

      Ele precisa ser exclusivo do projeto e do local, começar e terminar com uma letra minúscula ou um número e conter apenas letras minúsculas, números e traços.

    • PROJECT_ID: o ID do projeto que contém as tabelas a serem indexadas. Este projeto está atribuído à reserva.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Use o 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

Substitua:

  • ADMIN_PROJECT_ID: o ID do projeto de administração que é proprietário do recurso de reserva.
  • LOCATION: o local da reserva
  • RESERVATION_NAME: o nome da reserva
  • PROJECT_ID: o ID do projeto a ser atribuído a essa reserva.

Veja seus jobs de indexação

Um novo job de indexação é criado sempre que um índice é criado ou atualizado em uma única tabela. Para ver informações sobre o job, consulte as visualizações INFORMATION_SCHEMA.JOBS*. É possível filtrar por jobs de indexação definindo job_type IS NULL AND SEARCH(job_id, '`search_index`') na cláusula WHERE da sua consulta. O exemplo a seguir lista os cinco jobs de indexação mais recentes no projeto 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;

Escolha o tamanho da reserva

Para escolher o número certo de slots para sua reserva, considere quando os jobs de gerenciamento de índice são executados, quantos slots eles usam e como é o uso ao longo do tempo. O BigQuery aciona um job de gerenciamento de índice nas seguintes situações:

  • Você cria um índice em uma tabela.
  • Os dados são modificados em uma tabela indexada.
  • O esquema de uma tabela muda, e isso afeta as colunas que são indexadas.
  • Os dados e metadados do índice são otimizados ou atualizados periodicamente.

O número de slots necessários para um job de gerenciamento de índice em uma tabela depende dos seguintes fatores:

  • o tamanho da tabela
  • a taxa de ingestão de dados para a tabela
  • a taxa de instruções DML aplicadas à tabela
  • o atraso aceitável para a criação e manutenção do índice
  • A complexidade do índice, normalmente determinada por atributos dos dados, como o número de termos duplicados
Estimativa inicial

As estimativas a seguir podem ajudar você a aproximar a quantidade de slots necessária para sua reserva. Devido à natureza altamente variável das cargas de trabalho de indexação, é necessário reavaliar seus requisitos depois de começar a indexar dados.

  • Dados atuais: com uma reserva de 1.000 slots, uma tabela atual no BigQuery pode ser indexada a uma taxa média de até 4 GiB por segundo, o que representa aproximadamente 336 TiB por dia.
  • Dados recém-ingeridos: a indexação geralmente consome mais recursos em dados recém-ingeridos, porque a tabela e o índice dela passam por várias rodadas de otimizações transformadoras. Em média, a indexação de dados recebidos recentemente consome três vezes os recursos em comparação com a indexação de preenchimento inicial dos mesmos dados.
  • Dados modificados com pouca frequência: tabelas indexadas com pouca ou nenhuma modificação de dados precisam de muito menos recursos para manter a manutenção de índices contínua. Um ponto de partida recomendado é manter 1/5 dos slots necessários para a indexação de preenchimento inicial dos mesmos dados, e não menos do que 250 slots.
  • O progresso da indexação é escalonado de forma linear com o tamanho da reserva. No entanto, não recomendamos o uso de reservas com menos de 250 slots para indexação porque isso pode levar a ineficiências que atrasam o progresso da indexação.
  • Essas estimativas podem mudar à medida que os recursos, as otimizações e seu uso real variam.
  • Se o tamanho total da tabela da sua organização exceder o limite de indexação da sua região, você precisará manter uma reserva diferente de zero atribuída para indexação. Caso contrário, a indexação poderá voltar para o nível padrão, resultando na exclusão não intencional de todos os índices.
Monitore o uso e o progresso

A melhor maneira de avaliar o número de slots necessários para executar com eficiência os jobs de gerenciamento de índice é monitorar a utilização deles e ajustar o tamanho da reserva adequadamente. A consulta a seguir produz o uso diário de slots para jobs de gerenciamento de índice. Somente os últimos 30 dias são incluídos na região 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;

Quando não há slots suficientes para executar jobs de gerenciamento de índice, um índice pode ficar dessincronizado com a tabela, e os jobs de indexação poderão falhar. Nesse caso, o BigQuery recria o índice do zero. Para evitar um índice não sincronizado, verifique se você tem slots suficientes para oferecer suporte a atualizações de índice da ingestão e da otimização de dados. Para mais informações sobre o monitoramento do uso do slot, consulte gráficos de recursos de administrador.

Práticas recomendadas

  • Os índices de pesquisa são projetados para tabelas grandes. Os ganhos de desempenho aumentam com o tamanho da tabela.
  • Não indexe colunas que contenham somente um número muito pequeno de valores exclusivos.
  • Não indexe colunas que você nunca pretende usar com a função SEARCH ou qualquer uma das outras funções e operadores compatíveis.
  • Tenha cuidado ao criar um índice de pesquisa em ALL COLUMNS. Sempre que você adicionar uma coluna contendo dados de STRING ou JSON, ela será indexada.
  • Use sua própria reserva para o gerenciamento de índices em aplicativos de produção. Se você optar por usar o pool de slots compartilhado padrão para os jobs de gerenciamento de índice, os limites de tamanho por organização serão aplicados.

Excluir um índice de pesquisa

Quando você não precisar mais de um índice de pesquisa ou quiser alterar quais colunas estão indexadas em uma tabela, é possível excluir o índice atualmente na tabela. Use a instrução DDL DROP SEARCH INDEX.

Se uma tabela indexada for excluída, o índice dela será excluído automaticamente.

Exemplo:

DROP SEARCH INDEX my_index ON dataset.simple_table;

A seguir