Como criar e usar visualizações materializadas

Este documento descreve como criar e usar visualizações materializadas com o BigQuery Materialized View. É recomendado ler a Introdução às visualizações materializadas antes da leitura deste documento.

Como criar uma visualização materializada

É possível criar uma visualização materializada por meio da IU da Web do BigQuery, da ferramenta de linha de comando bq ou da API 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 Google Cloud, acesse a IU da Web do BigQuery.

    Acessar Console do Cloud

  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.

Como consultar uma visualização materializada

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 redirecionar automaticamente para usar o resultado armazenado em cache na visualização materializada. O plano de consulta mostrará que a consulta foi reescrita para usar a visualização materializada.

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

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 a estes 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.

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 da IU da Web do BigQuery 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 IU da Web do BigQuery no Console do Cloud.
    Acessar o Console do Cloud

  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

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.

Como excluir uma visualização materializada

É possível excluir uma visualização materializada por meio da IU da Web do BigQuery, 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:

  1. Abra a IU da Web do BigQuery no Console do Cloud.
    Acessar o Console do Cloud

  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.

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.

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

  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG
  • AVG
  • COUNT
  • HLL_COUNT.INIT
  • MAX
  • MIN
  • SUM

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

Os exemplos a seguir mostram consultas de tabela base e as definições de visualização materializadas compatíveis para regravação atualmente.

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 mudando somente para anexação, a consulta que usa a visualização materializada verificará todos os dados da visualização materializada e mais um delta na tabela base desde a última atualização da visualização. Isso se aplica se a visualização materializada é explicitamente referenciada ou selecionada pelo otimizador de consulta. Como resultado, as consultas são mais rápidas e mais baratas.

No entanto, se houver atualizações ou exclusões na tabela base desde a última atualização da visualização materializada, é 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. Em visualizações particionadas, apenas as partições afetadas serão invalidadas.

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 que têm anexos, atualizações ou exclusões nas partições correspondentes da tabela base. 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

A expiração da partição não pode ser determinada em visualizações materializadas. 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.

Como manter visualizações materializadas

Gerencie como as visualizações materializadas estão sendo atualizadas para atualizar os resultados pré-computados da tabela base por meio de atualizações automáticas e manuais. É possível definir as configurações de atualização ao criar uma visualização materializada, caso não queira usar valores padrão. É 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. Exemplos de alterações incluem inserções de linha 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)

Ativar a atualização automática acionará uma atualização automática na 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 definir 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 interface de linha de comando) para o valor que você quer.

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.

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 a ser executada BQ.REFRESH_MATERIALIZED_VIEW será concluída junto à conclusão da atualização.

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

Não é recomendado realizar mais de uma atualização por vez. Embora várias atualizações possam ser iniciadas simultaneamente para a mesma visualização materializada, apenas a primeira atualização a ser concluída será realizada com sucesso.

Permissões exigidas

Para atualizar manualmente uma visualização materializada, é preciso ter as seguintes permissões:

  • bigquery.tables.getData
  • bigquery.tables.update
  • bigquery.tables.updateData

Próximas etapas