Como criar e usar visualizações materializadas

Este documento descreve como criar e usar visualizações materializadas com o BigQuery. Antes de ler este documento, familiarize-se com a Introdução às visualizações materializadas. As versões de prévia das visualizações materializadas sem agregação e visualizações materializadas com mesclas já estão disponíveis.

Permissões necessárias

Antes de trabalhar com uma visualização materializada, você precisa ter as permissões necessárias para a operação da visualização materializada. A tabela a seguir lista os tipos de operações que podem ser executadas em visualizações materializadas, os comandos e métodos para executar essas operações, as permissões necessárias para cada operação e os papéis padrão que incluem essas permissões.

Para mais informações sobre o BigQuery Identity and Access Management (IAM), consulte Papéis e permissões predefinidos.

Tipo de operação	Comando ou método	Permissões necessárias	Papéis padrão
Criar	`CREATE MATERIALIZED VIEW` `bq mk --materialized view` `tables.insert`	`bigquery.tables.create`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Query	Consulta SQL padrão `bq query`	`tables.getData`	`bigquery.dataViewer` `bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Atualizar	`ALTER MATERIALIZED VIEW` `bq query` `bq update` `tables.patch` `tables.update`	`bigquery.tables.get` `bigquery.tables.update`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Excluir	`DROP MATERIALIZED VIEW` `bq query` `bq rm` `tables.delete`	`bigquery.tables.get` `bigquery.tables.delete`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Atualização manual	`CALL BQ.REFRESH_MATERIALIZED_VIEW`	`bigquery.tables.getData` `bigquery.tables.update` `bigquery.tables.updateData`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`

Como criar visualizações materializadas

É possível criar visualizações materializadas do BigQuery por meio do Console do Google Cloud, da ferramenta de linha de comando bq ou da API do BigQuery.

Para os exemplos a seguir, suponha que sua tabela base se chame my_base_table e tenha o seguinte esquema:

Nome da coluna	Tipo
product_id	integer
clicks	integer

Suponha também que você queira uma visualização materializada que resuma o número de cliques por product_id. As etapas a seguir criam uma visualização materializada chamada my_mv_table que tem o esquema abaixo.

Nome da coluna	Tipo
product_id	integer
sum_clicks	integer

Para criar uma visualização materializada:

Console

Com as instruções de linguagem de definição de dados (DDL, na sigla em inglês), é possível criar e modificar tabelas e visualizações usando a sintaxe de consulta do SQL padrão.

Saiba mais sobre Como usar as instruções da linguagem de definição de dados.

Para criar uma visualização materializada no Console do Cloud usando uma instrução DDL:

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

Ir para o BigQuery
Clique em Escrever nova consulta.
Digite a instrução DDL CREATE MATERIALIZED VIEW na área de texto do Editor de consultas.
```
CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM  project-id.my_dataset.my_base_table
GROUP BY 1
```
onde:
- project-id é o ID do projeto;
- my_dataset é o id de um conjunto de dados no projeto;
- my_mv_table é o id da visualização materializada que você está criando;
- my_base_table é o id de uma tabela no conjunto de dados que serve como a tabela base para a visualização materializada;
- product_id é uma coluna da tabela base;
- clicks é uma coluna da tabela base;
- sum_clicks é uma coluna na visualização materializada que você está criando.
Clique em Executar.

A menos que você desative as atualizações automáticas, o BigQuery iniciará uma atualização completa assíncrona para a visualização materializada. A consulta pode retornar sucesso imediatamente, mas a atualização inicial ainda pode estar em execução. Quando a visualização materializada é criada, ela aparece no painel Conjuntos de dados.

bq

Use o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM  project-id.my_dataset.my_base_table
GROUP BY 1'

onde:

project-id é o ID do projeto;
my_dataset é o id de um conjunto de dados no projeto;
my_mv_table é o id da visualização materializada que você está criando;
my_base_table é o id de uma tabela no conjunto de dados que serve como a tabela base para a visualização materializada;
product_id é uma coluna da tabela base;
clicks é uma coluna da tabela base;
sum_clicks é uma coluna na visualização materializada que você está criando.

Como alternativa, é possível usar o comando bq mk com o argumento --materialized_view para criar uma visualização materializada. Os argumentos a seguir funcionam com o argumento --materialized_view:

--enable_refresh: especifica se a atualização automática está ativada.
--refresh_interval_ms: especifica o tempo, em milissegundos, para o intervalo de atualização.

API

Chame o método tables.insert com um recurso materializedView como parte da sua solicitação de API. O recurso materializedView contém um campo query. Por exemplo:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from project-id.my_dataset.my_base_table
                group by 1"
  }
}

onde:

project-id é o ID do projeto;
my_dataset é o id de um conjunto de dados no projeto;
my_mv_table é o id da visualização materializada que você está criando;
my_base_table é o id de uma tabela no conjunto de dados que serve como a tabela base para a visualização materializada;
product_id é uma coluna da tabela base;
clicks é uma coluna da tabela base;
sum_clicks é uma coluna na visualização materializada que você está criando.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Ver no GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

Controle de acesso

É possível conceder acesso a uma visualização materializada no nível do conjunto de dados, no nível da visualização ou no nível da coluna. Também é possível definir o acesso em um nível superior na hierarquia de recursos do IAM.

Consulta de visualizações materializadas.

Consulte visualizações materializadas diretamente da mesma forma que consulta uma tabela normal ou visualizações padrão.

Quando você consulta uma tabela base que tem uma visualização materializada, o otimizador de consultas pode reescrever automaticamente a consulta para usar o resultado armazenado em cache na visualização materializada. Essa substituição só pode ocorrer se a tabela base e as visualizações materializadas estiverem no mesmo conjunto de dados. O plano de consulta mostra que a consulta foi reescrita para usar a visualização materializada.

Se for possível usar várias visualizações materializadas para reescrever uma consulta, será usada a visualização materializada com o menor número estimado de linhas a serem verificadas.

Caso você consulte uma visualização materializada desatualizada, os dados da visualização materializada serão combinados com as alterações delta da tabela base para calcular os resultados atualizados. Uma visualização materializada pode estar desatualizada devido aos seguintes motivos:

A visualização materializada não está configurada para atualização automática porque o usuário decidiu controlar as atualizações manualmente.
A tabela base é atualizada com muita frequência, de forma que o limite de atualização de frequência já esteja sendo aplicado.
A tabela base foi alterada há poucos segundos, então a visualização materializada ainda não alcançou a tabela base.

Mesmo que uma visualização materializada não esteja atualizada devido a um ou mais desses motivos, as consultas na visualização materializada ainda estarão atualizadas, porque as alterações da tabela base também são incorporadas nos resultados da consulta.

Para informações sobre como executar consultas, consulte Visão geral da consulta de dados do BigQuery.

Como alterar uma visualização materializada

É possível alterar uma visualização materializada por meio do Console do Cloud ou da ferramenta de linha de comando bq usando DDL com ALTER MATERIALIZED VIEW e SET OPTIONS.

Veja a seguir um exemplo que define enable_refresh a true. Ajuste conforme necessário para seu caso de uso.

Console

Para alterar uma visualização materializada no Console do Cloud usando uma instrução DDL:

No Console do Cloud, abra a página do BigQuery.

Ir para o BigQuery

Clique em Escrever nova consulta.
Digite a instrução DDL ALTER MATERIALIZED VIEW na área de texto do Editor de consultas.
```
ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)
```
onde:
- project-id é o ID do projeto;
- my_dataset é o id de um conjunto de dados no projeto;
- my_mv_table é o código da visualização materializada que você está alterando.
Clique em Executar.

bq

Use o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)

onde:

project-id é o ID do projeto;
my_dataset é o id de um conjunto de dados no projeto;
my_mv_table é o código da visualização materializada que você está alterando.

Como alternativa, execute o comando bq update.

bq update \
--enable_refresh=true \
--refresh_interval_ms= \
project-id.my_dataset.my_mv_table

Java

Ver no GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryMaterializedView {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query = String.format("SELECT * FROM %s.%s", datasetName, materializedViewName);
    queryMaterializedView(query);
  }

  public static void queryMaterializedView(String query) throws InterruptedException {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableResult results = bigquery.query(QueryJobConfiguration.of(query));
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s\n", val.toString())));

      System.out.println("Query performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query was not performed. \n" + e.toString());
    }
  }
}

Como manipular uma visualização materializada

É possível manipular uma visualização materializada apenas pelas instruções CREATE, DROP ou ALTER, com as equivalentes na API, bem como por uma atualização manual.

As operações a seguir não são permitidas em visualizações materializadas.

Executar trabalhos de cópia, importação ou exportação onde a origem ou o destino sejam visualizações materializadas.
Gravar os resultados da consulta em uma visualização materializada.
Como chamar tabledata.list.
Como usar a API BigQuery Storage Read.

O exemplo a seguir altera a visualização material:

Java

Ver no GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

// Sample to alter materialized view
public class AlterMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    alterMaterializedView(datasetName, materializedViewName);
  }

  public static void alterMaterializedView(String datasetName, String materializedViewName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Alter materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view altered successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not altered. \n" + e.toString());
    }
  }
}

Como excluir uma visualização materializada

É possível excluir uma visualização materializada por meio do Console do Cloud, da ferramenta de linha de comando bq ou da API.

Console

Para excluir uma visualização materializada no Console do Cloud usando uma instrução DDL:

No Console do Cloud, abra a página do BigQuery.

Ir para o BigQuery

Clique em Escrever nova consulta.
Digite a instrução DDL DELETE MATERIALIZED VIEW na área de texto do Editor de consultas.
```
DROP MATERIALIZED VIEW project-id.my_dataset.my_mv_table
```
onde:
- project-id é o ID do projeto;
- my_dataset é o id de um conjunto de dados no projeto;
- my_mv_table é o id da visualização materializada que você está excluindo.
Clique em Executar.

bq

Use o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
DROP MATERIALIZED VIEW
project-id.my_dataset.my_mv_table'

onde:

project-id é o ID do projeto;
my_dataset é o id de um conjunto de dados no projeto;
my_mv_table é o id da visualização materializada que você está excluindo.

Como alternativa, use o comando bq rm para excluir a visualização materializada.

API

Chame o método tables.delete e especifique valores para os parâmetros projectId, datasetId e tableId:

Atribua o parâmetro projectId ao ID do projeto.
Atribua o parâmetro datasetId ao ID do conjunto de dados.
Atribua o parâmetro tableId ao ID da tabela da visualização materializada que será excluída.

Java

Ver no GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;

// Sample to delete materialized view
public class DeleteMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    deleteMaterializedView(datasetName, materializedViewName);
  }

  public static void deleteMaterializedView(String datasetName, String materializedViewName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      boolean success = bigquery.delete(tableId);
      if (success) {
        System.out.println("Materialized view deleted successfully");
      } else {
        System.out.println("Materialized view was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not found. \n" + e.toString());
    }
  }
}

Como monitorar visualizações materializadas

É possível receber informações sobre visualizações materializadas e sobre os respectivos jobs de atualização usando a API BigQuery.

Monitoramento de visualizações materializadas

Para a descoberta de visualizações materializadas, chame o método tables.list ou consulte a tabela INFORMATION_SCHEMA.TABLES.

Para recuperar as propriedades de uma visualização materializada, chame o método tables.get ou consulte a tabela INFORMATION_SCHEMA.TABLE_OPTIONS.

As visualizações materializadas não são listadas na tabela INFORMATION_SCHEMA.VIEWS.

Monitoramento de jobs de atualização de visualizações materializadas

Para descobrir jobs de atualização de visualização materializada, liste-os chamando o método jobs.list. Para recuperar detalhes sobre os jobs, chame o método jobs.get. Os jobs de atualização automática contêm o prefixo materialized_view_refresh no ID do job e são iniciados por uma conta de administrador do BigQuery.

Exemplo:

SELECT job_id, total_slot_ms, total_bytes_processed
FROM region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE "%materialized_view_refresh_%"
LIMIT 10

Visualizações materializadas compatíveis

É necessário que as visualizações materializadas sejam uma agregação de uma única tabela. O uso de GROUP BY é opcional. As versões de prévia de visualizações materializadas sem agregação e visualizações materializadas com mesclas já estão disponíveis.

As visualizações materializadas usam uma sintaxe SQL restrita. As consultas precisam usar o seguinte padrão:

SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

No momento, apenas as seguintes funções de agregação são compatíveis:

ANY_VALUE (mas não acima de STRUCT)
APPROX_COUNT_DISTINCT
ARRAY_AGG (mas não acima de ARRAY ou STRUCT)
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
SUM

Algumas subconsultas são aceitas, desde que não incluam nenhum cálculo ou filtragem acima de uma agregação. Por exemplo, as seguintes consultas SQL são aceitas:

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
SELECT ts_hour, COUNT(*) as cnt
FROM tmp
GROUP BY ts_hour

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT * FROM tmp

SELECT ts_hour, COUNT(*) as cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
GROUP BY ts_hour

SELECT * FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)

A seguinte consulta SQL não é compatível com visualizações materializadas porque aplica o cálculo sobre uma agregação:

-- Not supported for a materialized view
WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT TIMESTAMP_TRUNC(ts_hour, DAY) as ts_day, cnt+1 as cnt FROM tmp

A seguinte consulta SQL não é compatível com visualizações materializadas, porque aplica a filtragem sobre uma agregação:

-- Not supported for a materialized view
SELECT ts_hour, cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
WHERE ts_hour != TIMESTAMP(DATE('2020-01-01'))

Cláusula `FROM`

A cláusula FROM precisa incluir uma única tabela e pode desaninhar uma ou mais expressões de matriz.

Exemplos

FROM mytable

FROM mytable AS t, t.struct_column.array_field AS x

FROM mytable AS t LEFT JOIN UNNEST(t.array_column) AS x

FROM mytable AS t, t.array_column AS x, x.array_field AS y

FROM mytable AS t, UNNEST(SPLIT(t.string_column)) AS x

WITH OFFSET não é compatível.

Padrões de regravação de consulta compatíveis

Os seguintes padrões de reescrita de consulta são aceitos para consultas de tabelas base e definições de visualização materializadas.

Padrão 1

Esse padrão mostra que as chaves de agrupamento e os agregadores na consulta são um subconjunto dos presentes na visualização materializada.

Para esta consulta de tabela base:

SELECT
  ss_sold_date_sk,
  SUM(ss_net_profit) AS sum_profit
FROM store_sales
GROUP BY 1

Veja a seguir uma consulta de visualização materializada que mostra as chaves e os agregadores da consulta da tabela base como um subconjunto da consulta da visualização materializada.

SELECT
  ss_store_sk,
  ss_sold_date_sk,
  SUM(ss_net_paid) AS sum_paid,
  SUM(ss_net_profit) AS sum_profit,
  COUNT(*) AS cnt_sales
FROM store_sales
GROUP BY 1, 2

Padrão 2

Esse padrão mostra as chaves de agrupamento na consulta da tabela base que podem ser calculadas com as chaves de agrupamento na visualização materializada.

Para esta consulta de tabela base:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

Veja a seguir uma consulta de visualização materializada que calcula as chaves de agrupamento na tabela base:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

Padrão 3

Esse padrão mostra que as expressões de filtro na consulta podem ser derivadas de chaves de agrupamento na visualização materializada ou corresponder exatamente à visualização materializada.

Para esta consulta de tabela base:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE DATE(l_shipdate) >= DATE(2016, 01, 01) AND l_shipmode = "AIR"
GROUP BY 1

Veja a seguir uma consulta de visualização materializada que deriva uma expressão de filtro da tabela base:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE l_shipmode = "AIR"
GROUP BY 1

Padrão 4

Esse padrão mostra expressões na consulta da tabela base que selecionam um subconjunto dos dados na visualização materializada.

Para esta consulta de tabela base:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount > 20.0
GROUP BY 1

Veja a seguir uma consulta de visualização materializada cujos resultados são um superconjunto da consulta da tabela base:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount IS NOT NULL
GROUP BY 1

Atualizações incrementais

Nos casos em que a tabela base continua sendo alterada apenas com anexos, a consulta que usa a visualização materializada verifica todos os dados dessa visualização, além de um delta na tabela base desde a última atualização. Isso se aplica se a visualização materializada é explicitamente referenciada ou selecionada pelo otimizador de consulta, resultando em consultas mais rápidas e mais baratas em ambos os casos.

No entanto, se houver atualizações ou exclusões na tabela base desde a última atualização, é possível que a visualização materializada não seja verificada.

Veja a seguir exemplos de ações que causam uma atualização ou exclusão:

DML UPDATE
DML MERGE
DML DELETE
truncamento
expiração da partição
console, linha de comando bq e equivalentes da API dos itens anteriores nesta lista

Se essas ações ocorrerem, é possível que a consulta de visualização materializada não gere nenhuma economia até que a próxima atualização de visualização ocorra. Na verdade, qualquer atualização ou exclusão na tabela base invalida uma parte do estado da visualização materializada. Em visualizações não particionadas, a visualização inteira será invalidada. Na maioria dos casos, apenas as partições afetadas serão invalidadas em visualizações particionadas.

Os dados do buffer de streaming do BigQuery da tabela base não são salvos em uma visualização materializada. Um buffer de streaming ainda está sendo verificado, independentemente de uma visualização materializada ser usada.

Como trabalhar com tabelas particionadas e em cluster

Se a tabela base for particionada, particione uma visualização materializada na mesma coluna de particionamento. Para partições baseadas em tempo, a granularidade precisa corresponder (por hora, dia, mês ou ano). Para partições por intervalo de números inteiros, a especificação do intervalo precisa corresponder exatamente. Não é possível particionar uma visualização materializada em uma tabela base não particionada.

Se a tabela base é particionada por tempo de processamento, uma visualização materializada pode ser agrupada pela coluna _PARTITIONDATE da tabela base e também particionada por ela.

As visualizações materializadas podem ser agrupadas por colunas arbitrárias, sujeitas às limitações da tabela em cluster do BigQuery.

Exemplo 1

Neste exemplo, a tabela base é particionada na coluna transaction_time com partições diárias. A visualização materializada é particionada na mesma coluna e agrupada na coluna employee_id.

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
PARTITION BY DATE(transaction_time)
OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_time)
CLUSTER BY employee_id
AS SELECT
  employee_id,
  transaction_time,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Exemplo 2

Neste exemplo, a tabela base é particionada por tempo de processamento com partições diárias. A visualização materializada seleciona o tempo de ingestão como uma coluna chamada date. A visualização materializada é agrupada pela coluna date e particionada pela mesma coluna.

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64)
PARTITION BY _PARTITIONDATE
OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY date
CLUSTER BY employee_id
AS SELECT
  employee_id,
  _PARTITIONDATE as date,
  COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Exemplo 3

Neste exemplo, a tabela base é particionada em uma coluna TIMESTAMP chamada transaction_time, com partições diárias. A visualização materializada define uma coluna chamada transaction_hour, usando a função TIMESTAMP_TRUNC para truncar o valor para a hora mais próxima. A visualização materializada é agrupada por transaction_hour e também particionada por ela.

Observações:

A função de truncamento aplicada à coluna de particionamento precisa ser pelo menos tão granular quanto o particionamento da tabela base. Por exemplo, se a tabela base usa partições diárias, a função de truncamento não pode usar a granularidade MONTH ou YEAR.
Na especificação da partição da visualização materializada, a granularidade precisa corresponder à tabela base.

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_hour)
AS SELECT
  employee_id,
  TIMESTAMP_TRUNC(transaction_time, HOUR) as transaction_hour,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Alinhamento de partições

Se uma visualização materializada for particionada, o BigQuery garantirá que as partições estejam alinhadas com as partições da tabela base. Por exemplo, uma linha da partição 2020-01-01 da tabela base está sendo agregada a uma linha da partição 2020-01-01 da visualização materializada.

O alinhamento de partições ajuda a garantir que uma visualização materializada seja mantida de forma eficiente. Durante a atualização, as únicas partições da visualização materializada que serão atualizadas são aquelas com anexos, atualizações ou exclusões nas partições correspondentes da tabela base. No entanto, em alguns casos raros, uma exclusão ou atualização na tabela base pode fazer com que toda a visualização seja reprocessada. No momento da consulta, o alinhamento de particionamento garante que um filtro de partição seja aplicado à verificação da visualização materializada e à verificação da tabela base.

Expiração da partição

Nas visualizações materializadas, a expiração da partição não pode ser configurada. Uma visualização materializada herda implicitamente o prazo de validade da partição da tabela base. As partições de visualização materializadas são alinhadas com as partições de tabela base, por isso expiram de forma síncrona.

Depois que uma visualização materializada é criada em uma tabela base particionada, a validade da partição da tabela não pode ser alterada. Para alterar o prazo de validade da partição da tabela base, exclua todas as visualizações materializadas criadas nessa tabela.

Atualizar visualizações materializadas

É possível especificar se o BigQuery usa atualizações automáticas ou manuais para atualizar os resultados pré-computados de uma tabela base. Se não quiser usar os valores padrão, defina as configurações de atualização ao criar uma visualização materializada. É possível alterar as configurações de atualização após a criação da visualização materializada.

É possível atualizar manualmente uma visualização materializada a qualquer momento.

Atualização automática

Por padrão, as visualizações materializadas são atualizadas automaticamente em até cinco minutos após uma alteração na tabela base, mas não mais do que a cada 30 minutos. Exemplos de alterações incluem inserções ou exclusões de linha.

A atualização automática pode ser ativada ou desativada a qualquer momento.

Para desativar a atualização automática ao criar uma tabela, defina enable_refresh como false.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY RANGE_BUCKET(column, buckets)
OPTIONS (enable_refresh = false)
AS SELECT ...

Para uma visualização materializada existente, é possível modificar o valor enable_refresh usando ALTER MATERIALIZED VIEW.

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true)

Mesmo que a atualização automática esteja desativada, a visualização materializada pode ser atualizada manualmente a qualquer momento.

Como definir o limite de frequência

É possível configurar um limite de frequência para a execução de atualizações automáticas. Por padrão, as visualizações materializadas são atualizadas a intervalos de 30 minutos.

O limite de frequência de atualização pode ser alterado a qualquer momento.

Para configurar um limite de frequência de atualização ao criar uma visualização materializada, defina refresh_interval_minutes em DDL (ou refresh_interval_ms na API e na ferramenta de linha de comando bq) para o valor desejado.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT ...

Da mesma forma, é possível definir o limite de frequência ao modificar uma tabela. O exemplo a seguir pressupõe que você já tenha ativado a atualização automática e queira apenas alterar o limite de frequência:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (refresh_interval_minutes = 60)

O limite mínimo de frequência de atualização é de 1 minuto. O limite máximo de frequência de atualização é de sete dias.

É possível atualizar manualmente uma visualização materializada a qualquer momento, e seu tempo não está sujeito ao limite de frequência.

Melhor esforço

A atualização automática é realizada da melhor maneira possível. O BigQuery tenta iniciar uma atualização em até cinco minutos após uma alteração na tabela base, supondo que a atualização anterior tenha sido feita há mais de 30 minutos. No entanto, isso não significa que a atualização começará nesse prazo e nem garante quando ela estará concluída. A consulta de visualizações materializadas reflete o estado mais recente da tabela base, mas se a visualização não foi atualizada recentemente, o custo/latência da consulta poderá ser maior que o esperado.

A atualização automática é tratada de maneira semelhante a uma consulta com prioridade batch. Se o projeto da visualização materializada não tiver a capacidade no momento, a atualização será atrasada. Se o projeto tiver muitas visualizações com atualizações caras, cada uma delas pode ter um atraso significativo em relação à tabela base.

Atualização manual

Para atualizar os dados na visualização materializada, chame o procedimento de sistema BQ.REFRESH_MATERIALIZED_VIEW a qualquer momento. Quando esse procedimento é chamado, o BigQuery identifica as alterações realizadas na tabela base e as aplica à visualização materializada. A consulta que executará BQ.REFRESH_MATERIALIZED_VIEW termina quando a atualização estiver concluída.

CALL BQ.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')

Visualizações materializadas sem agregação (prévia)

As visualizações materializadas sem agregação (também conhecidas como visualizações materializadas de projeção) podem ser usadas para o re-cluster, filtrar ou computar dados com antecedência. As visualizações materializadas sem agregação funcionam como índices, permitindo que o BigQuery otimize consultas ao estimar se o número de bytes consultados é menor na tabela base ou em uma ou mais visualizações materializadas qualificadas disponíveis. As visualizações materializadas sem agregação também fornecem dados pré-filtrados ou pré-computados para otimizar o desempenho de consultas futuras.

Casos de uso

Os exemplos a seguir demonstram usos das visualizações materializadas sem agregação, supondo que você tenha a tabela a seguir:

CREATE TABLE dataset.base_table (
  x INT64,
  y INT64,
  string_field STRING)
CLUSTER BY x;

Reclustering de dados

Se você costuma emitir consultas que se beneficiariam de um esquema de clustering diferente da tabela base que tem uma visualização materializada, é possível melhorar o desempenho da consulta.

Acima, dataset.base_table está em cluster pela coluna x. Portanto, é otimizado para consultas que filtram ou agregam essa coluna. Se você geralmente executa consultas que filtram a coluna y, mas não a coluna x, seguir a visualização materializada pode melhorar o desempenho dessas consultas.

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY y
AS SELECT * FROM dataset.base_table;

A consulta SELECT COUNT(*) FROM dataset.base_table WHERE y = 123 provavelmente seria reescrita pelo otimizador do BigQuery para usar a visualização materializada, já que ela reduz a quantidade de dados que o BigQuery precisa verificar.

Pré-filtrar dados

Se você costuma executar consultas que leem somente um determinado subconjunto da tabela, uma visualização materializada pode melhorar o desempenho da consulta.

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY x
AS SELECT * FROM dataset.base_table
WHERE y < 1000;

Supondo que os valores y geralmente sejam maiores que 1.000, a consulta SELECT COUNT(*) FROM dataset.base_table WHERE x = 123 AND y < 500 provavelmente será reescrita pelo otimizador do BigQueryBigQuery para usar a visualização materializada. Observe que o predicado na consulta não precisa corresponder exatamente ao predicado na visualização. A consulta pode selecionar um subconjunto dos dados na visualização, conforme descrito em Padrões compatíveis de regravação de consulta.

Pré-computar dados

Se você costuma usar funções que são computacionalmente caras ou extrai pequenas quantidades de dados de uma coluna maior, uma visualização materializada pode melhorar o desempenho da consulta.

Se string_field contiver dados JSON e você tiver interesse no valor de um subcampo específico, a visualização materializada a seguir pode melhorar o desempenho dessas consultas.

CREATE MATERIALIZED VIEW dataset.mv
AS SELECT x, JSON_EXTRACT(string_field, "$.subfield1.subfield2") subfield2
FROM dataset.base_table;

A consulta SELECT JSON_EXTRACT(string_field, "$.subfield1.subfield2") FROM dataset.base_table WHERE x = 123 provavelmente seria reescrita pelo otimizador do BigQuery para usar a visualização materializada. O otimizador do BigQuery considera apenas o número de bytes consultados ao escolher uma consulta, não o custo computacional final.

Visualizações materializadas com mesclas internas (prévia)

As visualizações materializadas com mesclas podem melhorar o desempenho e reduzir os custos de conjuntos de dados com esquemas em estrela ou floco de neve, em que uma única tabela grande de fatos é mesclada frequentemente a uma ou mais tabelas de dimensões menores. As visualizações materializadas com mesclas podem reduzir ou eliminar a necessidade de mesclar dados no momento da consulta, juntando e agregando dados com antecedência. Os benefícios são maiores para consultas grandes ou complexas com altos custos de computação que retornam um conjunto de dados pequeno.

Limitações

Além das limitações de visualizações materializadas existentes, as visualizações materializadas com mesclas têm as seguintes limitações:

As visualizações materializadas devem ser uma agregação das tabelas unidas. Para ver a lista de funções de agregação compatíveis, consulte Visualizações materializadas compatíveis. Visualizações materializadas com junções e sem agregação não são compatíveis.
Apenas mesclas INNER são aceitas. As mesclas CROSS, FULL, LEFT e RIGHT não são aceitas.
As visualizações materializadas particionadas precisam receber a coluna de particionamento da primeira tabela ou da mais à esquerda na consulta.
Nesclas automáticas não são aceitas.

Criar visualizações materializadas com mesclas

É possível criar visualizações materializadas com mesclas, como uma visualização materializada de tabela única, usando uma instrução DDL CREATE MATERIALIZED VIEW ou a API. A ordem das tabelas na consulta afeta o desempenho. Para melhor desempenho, coloque a maior tabela primeiro. Se houver várias tabelas grandes, posicione a tabela com mais movimentações em primeiro lugar na ordem de mesclas, conforme descrito em Como otimizar a computação em consultas. Para mais informações, consulte Práticas recomendadas para visualizações materializadas com mesclas.

Consultar visualizações materializadas com mesclas

As visualizações materializadas com mesclas podem ser consultadas diretamente como uma visualização materializada de tabela única, uma tabela normal ou uma visualização padrão. Quando você emite uma consulta correspondente, o otimizador de consultas pode reescrever automaticamente a consulta para usar o resultado pré-computado armazenado na visualização materializada. O plano de consulta mostra que a consulta foi reescrita para usar a visualização materializada.

Padrões de regravação de consulta compatíveis

Para permitir que consultas sejam reescritas automaticamente, a consulta precisa usar exatamente o mesmo conjunto de tabelas que a visualização materializada, na mesma ordem. Por exemplo, usando a seguinte visualização materializada:

SELECT
 s_country,
 SUM(ss_net_paid) as sum_sales,
 COUNT(*) AS cnt_sales
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
GROUP BY 1;

A consulta a seguir seria reescrita automaticamente para usar a visualização materializada, economizando tempo e dinheiro.

SELECT
 SUM(ss_net_paid),
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"
GROUP BY 1;

No entanto, a consulta a seguir não seria reescrita porque faz referência a store e store_sales em uma ordem diferente.

SELECT
 SUM(ss_net_paid),
FROM store
INNER JOIN store_sales
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"

Atualizações incrementais para visualizações materializadas com mesclas

As visualizações materializadas com mesclas são compatíveis com consultas incrementais para a primeira tabela ou a mais à esquerda na consulta. Isso significa que o BigQuery pode usar os dados de visualização em cache, mesmo que os dados tenham sido anexados à primeira tabela ou a mais à esquerda desde que a visualização materializada foi atualizada pela última vez. No caso de atualizações ou exclusões na primeira tabela ou a mais à esquerda na visualização, ou de quaisquer alterações nas outras tabelas na visualização, o BigQuery não pode usar os dados armazenados em cache da visualização. O exemplo a seguir mostra como as atualizações incrementais funcionam em visualizações materializadas com mesclas.

CREATE MATERIALIZED VIEW dataset.mv AS SELECT
 s_country,
 SUM(ss_net_paid) as sum_sales,
 COUNT(*) AS cnt_sales
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
GROUP BY 1;

CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

INSERT INTO dataset.store_sales …;

-- This query and refresh will use the cached data, and read new data from
-- store_sales and all data from store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

DELETE FROM dataset.store_sales WHERE …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

INSERT INTO dataset.store …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

Nas visualizações particionadas, apenas as partições afetadas serão invalidadas no caso de uma atualização ou exclusão na primeira tabela ou da mais à esquerda. As alterações em outras tabelas mescladas invalidam toda a visualização.

Atualizar visualizações materializadas

As visualizações materializadas com mesclas podem ser atualizadas de forma automática ou manual, como as visualizações materializadas de tabela única. O BigQuery tentará manter as visualizações materializadas com mesclas de maneira incremental, mesclando a quantidade mínima de dados necessários. No entanto, se uma determinada tabela base for alterada desde a última atualização da visualização materializada, o BigQuery precisará verificar todos os dados das outras tabelas base.

Práticas recomendadas para visualizações materializadas com mesclas

Além das práticas recomendadas para visualizações materializadas existentes, as seguintes práticas recomendadas são específicas para visualizações materializadas com mesclas.

Colocar primeiro a tabela que muda com mais frequência

Garanta que a maior tabela seja a primeira ou a mais à esquerda a ser referenciada na consulta de visualização. As visualizações materializadas com mesclas são compatíveis com consultas incrementais e atualizam quando há alterações na primeira tabela ou na mais à esquerda da consulta, mas as alterações em outras tabelas invalidam totalmente o cache da visualização. Em esquemas em estrela ou floco de neve, essa provavelmente é a tabela de fatos.

Manter visualizações materializadas manualmente para um desempenho mais previsível

Se você atualizar ou excluir dados na primeira tabela ou a mais à esquerda, ou alterar as outras tabelas na consulta em horários previsíveis, considere assumir a propriedade da programação de manutenção de visualização executando uma atualização manual da visualização imediatamente após essas alterações.

Cluster nas chaves de agrupamento

A visualização de cluster por chaves de agrupamento pode melhorar o desempenho das consultas que incluem filtros nessas chaves.

Evitar mesclagem em chaves de clustering

As visualizações materializadas com mesclas funcionam melhor nos casos em que os dados são muito agregados ou a consulta de mescla original é cara. Para consultas seletivas, o BigQuery já é capaz de executar a mescla com eficiência e nenhuma visualização materializada é necessária. Por exemplo, considere as seguintes definições de visualização materializada.

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY s_marked_id
AS SELECT
 s_market_id,
 s_country,
 SUM(ss_net_paid) AS sum_sales,
 COUNT(*) AS cnt_sales
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
GROUP BY 1;

Se store_sales estiver em cluster em ss_store_sk, e você costuma executar consultas como esta:

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany'

então, a visualização materializada pode não ser tão eficiente quanto a consulta original. Para melhores resultados, execute um conjunto representativo de consultas, com e sem a visualização materializada. Consulte as perguntas frequentes para ver instruções sobre como executar uma consulta sem uma visualização materializada.

Segurança das visualizações

Para controlar o acesso às visualizações no BigQuery, consulte Como controlar o acesso às visualizações.

Próximas etapas

Saiba mais sobre Práticas recomendadas de visualizações materializadas.
Leia as Perguntas frequentes sobre visualizações materializadas.

Como criar e usar visualizações materializadas

Permissões necessárias

Como criar visualizações materializadas

Console

bq

API

Java

Controle de acesso

Consulta de visualizações materializadas.

Como alterar uma visualização materializada

Console

bq

Java

Como manipular uma visualização materializada

Java

Como excluir uma visualização materializada

Console

bq

API

Java

Como monitorar visualizações materializadas

Monitoramento de visualizações materializadas

Monitoramento de jobs de atualização de visualizações materializadas

Visualizações materializadas compatíveis

Cláusula FROM

Padrões de regravação de consulta compatíveis

Padrão 1

Padrão 2

Padrão 3

Padrão 4

Atualizações incrementais

Como trabalhar com tabelas particionadas e em cluster

Exemplo 1

Exemplo 2

Exemplo 3

Alinhamento de partições

Expiração da partição

Atualizar visualizações materializadas

Atualização automática

Como definir o limite de frequência

Melhor esforço

Atualização manual

Visualizações materializadas sem agregação (prévia)

Casos de uso

Reclustering de dados

Pré-filtrar dados

Pré-computar dados

Visualizações materializadas com mesclas internas (prévia)

Limitações

Criar visualizações materializadas com mesclas

Consultar visualizações materializadas com mesclas

Padrões de regravação de consulta compatíveis

Atualizações incrementais para visualizações materializadas com mesclas

Atualizar visualizações materializadas

Práticas recomendadas para visualizações materializadas com mesclas

Colocar primeiro a tabela que muda com mais frequência

Manter visualizações materializadas manualmente para um desempenho mais previsível

Cluster nas chaves de agrupamento

Evitar mesclagem em chaves de clustering

Segurança das visualizações

Próximas etapas

Cláusula `FROM`