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.

Permissões necessárias

Antes de trabalhar com uma visualização materializada, você precisa ter as permissões necessárias para a 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:

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

   Acesse a página do BigQuery

2. Clique em Escrever nova consulta.

3. 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.

4. 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.

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.

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.

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.

Java

Antes de testar essa amostra, siga as instruções de configuração para 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. O plano de consulta mostra que a ela 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:

1. Abra a página do BigQuery no Console do Cloud.
   Acessar a página do BigQuery

2. Clique em Escrever nova consulta.

3. 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.

4. 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

Antes de testar essa amostra, siga as instruções de configuração para 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.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.

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

Java

Antes de testar essa amostra, siga as instruções de configuração para 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.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.

Observação: certifique-se de usar o ID da tabela da visualização materializada, não o ID da tabela base, para excluir uma visualização materializada.

Console

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

1. Abra a página do BigQuery no Console do Cloud.
   Acessar a página do BigQuery

2. Clique em Escrever nova consulta.

3. 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.

4. 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

Antes de testar essa amostra, siga as instruções de configuração para 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.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());
    }
  }
}

Cuidado: se você excluir a tabela base sem antes excluir a visualização materializada, as consultas na visualização materializada falharão, assim como as atualizações. Se decidir recriar a tabela base, também será necessário recriar a visualização materializada.

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 sobre uma única tabela. O uso de GROUP BY é opcional.

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

SELECT
  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

As visualizações materializadas podem ser particionadas pela mesma coluna de partição configurada na tabela base.

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

Veja a seguir como criar uma tabela base com uma partição DATE na coluna transaction_time:

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)

Veja a seguir como criar uma visualização materializada particionada pela mesma coluna transaction_time:

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;

Quando 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.

Veja a seguir como criar uma tabela base particionada por tempo de processamento:

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

Veja a seguir como criar uma visualização materializada agrupada e particionada pelo tempo de ingestão da tabela base:

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;

Quando a tabela base é particionada por TIMESTAMP, a visualização materializada pode truncar esse carimbo de data/hora até a granularidade de DAY e, em seguida, usar o carimbo de data/hora truncado como uma coluna de particionamento.

Veja a seguir como criar uma visualização materializada particionada na coluna transaction_hour, que é um truncamento da coluna transaction_time da tabela base. Use a função TIMESTAMP_TRUNC para truncar o carimbo de data/hora.

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;

Veja a seguir a granularidade que pode ser usada para o truncamento do carimbo de data/hora:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY

Para informações gerais sobre truncamento de carimbo de data/hora, consulte TIMESTAMP_TRUNC.

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)

Observação: ativar a atualização automática aciona imediatamente uma atualização automática da visualização materializada.

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 poderá 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')

Atenção: não realize mais de uma atualização por vez. Se executar várias atualizações simultaneamente para a mesma visualização materializada, somente a primeira será concluída.

Próximas etapas

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