Visualização SEARCH_INDEX_COLUMNS

A visualização INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS contém uma linha para cada coluna do índice de pesquisa em cada tabela de um conjunto de dados.

Permissões necessárias

Para ver os metadados do índice de pesquisa, é preciso ter a permissão bigquery.tables.get ou bigquery.tables.list do Identity and Access Management (IAM) na tabela com o índice. Cada um dos seguintes papéis predefinidos do IAM inclui pelo menos uma destas permissões:

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.dataViewer
  • roles/bigquery.metadataViewer
  • roles/bigquery.user

Para mais informações sobre as permissões do BigQuery, consulte Controle de acesso com o IAM.

Esquema

Os resultados das consultas na visualização INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS contêm uma linha para cada coluna indexada de cada tabela do conjunto de dados.

A visualização INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS tem o seguinte esquema:

Nome da coluna Tipo de dados Valor
index_catalog STRING Nome do projeto que contém o conjunto de dados.
index_schema STRING O nome do conjunto de dados que contém o índice.
table_name STRING O nome da tabela base em que o índice é criado.
index_name STRING O nome do índice.
index_column_name STRING O nome da coluna indexada de nível superior.
index_field_path STRING O caminho completo do campo indexado expandido, começando com o nome da coluna. Os campos são separados por um ponto.

Escopo e sintaxe

As consultas nesta visualização precisam ter um qualificador de conjunto de dados. A tabela a seguir explica o escopo da região dessa visualização:

Acessar nome Escopo do recurso Escopo da região
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS Nível do conjunto de dados Local do conjunto de dados
Substitua:

  • Opcional: PROJECT_ID: o ID do seu projeto do Google Cloud. Se não for especificado, o projeto padrão será usado.

Exemplo

-- Returns metadata for search indexes in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS;

Exemplos

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