Introdução às visualizações materializadas

Beta

Este produto é coberto pelos Termos de ofertas pré-disponibilidade geral dos Termos de Serviço do Google Cloud Platform. Os produtos pré-disponibilidade geral podem ter suporte limitado, e as mudanças realizadas podem não ser compatíveis com outras versões pré-GA. Para mais informações, consulte as descrições das fases de lançamento.

Nesta página, você encontra uma visão geral das visualizações materializadas do BigQuery.

Introdução

As visualizações materializadas são pré-computadas e armazenam em cache os resultados de uma consulta periodicamente para aumentar o desempenho e a eficiência. O BigQuery usa resultados pré-computados de visualizações materializadas e, sempre que possível, lê somente alterações delta da tabela base para computar resultados atualizados. As visualizações materializadas podem ser consultadas diretamente ou usadas pelo otimizador do BigQuery para processar consultas nas tabelas base.

As consultas que usam visualizações materializadas geralmente são mais rápidas e consomem menos recursos do que consultas que recuperam os mesmos dados apenas da tabela base. As visualizações materializadas melhoram significativamente o desempenho de cargas de trabalho com consultas repetidas e de frequência constante.

Veja a seguir as principais características da visualização materializada do BigQuery:

• Manutenção zero: uma visualização materializada é reprocessada em segundo plano quando a tabela base é alterada. Todas as alterações incrementais de dados das tabelas base são adicionadas automaticamente às visualizações materializadas. Nenhuma entrada do usuário é necessária.

• Sempre atualizada: uma visualização materializada é sempre consistente com a tabela base, incluindo tabelas de streaming do BigQuery. Se uma tabela base for modificada por meio de atualização, mesclagem, truncamento ou vencimento da partição, o BigQuery invalidará as partes afetadas da visualização materializada e vai reler na íntegra a parte correspondente da tabela base. Para uma visualização materializada não particionada, o BigQuery invalidará toda a visualização materializada e vai reler toda a tabela de base. Para uma visualização materializada e particionada, o BigQuery invalidará as partições afetadas da visualização materializada e vai reler todas as partições correspondentes da tabela base. As partições somente de anexação não são invalidadas e são lidas no modo delta. Resumindo: nunca haverá uma situação em que a consulta de uma visualização materializada resulte em dados desatualizados.

• Ajuste inteligente: se uma consulta ou parte de uma consulta na tabela de origem puder ser resolvida consultando a visualização materializada, o BigQuery reescreverá (redirecionará) a consulta para usar a visualização materializada, o que melhora o desempenho e/ou eficiência.

Vantagens

As visualizações materializadas do BigQuery oferecem os seguintes benefícios:

• Redução no tempo de execução e custo para consultas com funções de agregação. O maior benefício é obtido quando o custo de computação de uma consulta é alto e o conjunto de dados resultante é pequeno.

• Otimização automática e transparente do BigQuery, já que o otimizador usa uma visualização materializada, se disponível, para melhorar o plano de execução da consulta. A otimização não requer alterações nas consultas.

• A mesma resiliência e alta disponibilidade das tabelas do BigQuery.

Casos de uso

O BigQuery Materialized View se destina aos seguintes casos de uso:

• Desempenho de consulta mais rápido: as visualizações materializadas do BigQuery são perfeitas para casos onde você tem uma tabela bruta e executa uma agregação de estilo de processamento analítico on-line (OLAP, na sigla em inglês) que exija processamento significativo, e tem consultas previsíveis e repetidas, como de extração, transformação e carregamento (ETL) ou pipelines de business intelligence (BI).

• Agregação de dados em tempo real: se você quiser acessar dados para decisões em tempo real, o BigQuery oferece suporte nativo a recursos de streaming. As visualizações materializadas do BigQuery se integram ao streaming do BigQuery e realizam agregações em tempo real para fornecer informações atualizadas.

Como criar visualizações materializadas

É possível criar uma visualização materializada com o Console do Cloud, a ferramenta de linha de comando bq ou a API BigQuery. Como exemplo, no Console do Cloud, é possível usar a seguinte instrução DDL:

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

Para mais detalhes sobre como usar o Console do Cloud, a ferramenta de linha de comando bq ou a API BigQuery para criar uma visualização materializada, consulte Criar e usar visualizações materializadas.

Custos

Os custos são associados às visualizações materializadas do BigQuery para os seguintes componentes:

• Como consultar visualizações materializadas
• Conservação de visualizações materializadas, como quando a visualização é atualizada.
• Armazenamento de tabelas de visualização materializadas.

Componente	Preços sob demanda	Preço fixo
Consulta	Bytes processados pela visualização materializada e por qualquer parte necessária da tabela base. ^1	Sem custo adicional. Slots são consumidos durante o tempo de consulta.
Manutenção	Bytes processados. Slots são consumidos para a atualização incremental da visualização.	Sem custo adicional. Slots são consumidos durante o tempo de consulta.
Armazenamento	Bytes armazenados em uma visualização materializada.	Bytes armazenados em uma visualização materializada.

¹ Se a tabela base for somente de anexação, apenas o delta desde a última atualização da visualização será processado a partir da tabela base. Veja mais informações em Atualizações incrementais.

Detalhes do custo de armazenamento

Para valores agregados AVG, ARRAY_AGG, APPROX_COUNT_DISTINCT em uma visualização materializada, o valor final não é armazenado diretamente. Em vez disso, o BigQuery armazena internamente uma visualização materializada como um rascunho intermediário usado para produzir o valor final.

Por exemplo, considere uma visualização materializada criada com o seguinte comando:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT date, AVG(net_paid) AS avg_paid
FROM project-id.my_dataset.my_base_table
GROUP BY date

Enquanto a coluna avg_paid será processada como NUMERIC ou FLOAT64 para o usuário, internamente ela será armazenada como BYTES, com seu conteúdo em forma de rascunho intermediário em formato reservado. Para fins de cálculo do tamanho dos dados, a coluna será tratada como BYTES.

Controle de acesso

O acesso concedido a uma visualização materializada é definido 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.

Como monitorar visualizações materializadas

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

Para a descoberta de visualizações materializadas, liste-as chamando o método tables.list. Para recuperar o esquema e outras propriedades de uma tabela, chame o método tables.get.

Observação: não há compatibilidade para descoberta por meio de INFORMATION_SCHEMA no momento.

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.

Comparação com outras técnicas do BigQuery

A tabela a seguir resume as semelhanças e diferenças entre o armazenamento em cache do BigQuery, as consultas programadas, as visualizações padrão e as visualizações materializadas.

Componente	Armazenamento em cache	Consultas programadas	Visualizações padrão	Visualizações materializadas
Desempenho	Sim	Não	Não	Sim
Compatibilidade de consulta	Tudo	Tudo	Tudo	Agregação: filtragem e agrupamento
Particionamento e agrupamento em cluster	Não	Sim	N/A	Sim
Atualização incremental	Não	Não	Não	Sim
Armazenamento adicional	Não	Sim	Não	Sim
Reescrever consulta	Não	Não	Não	Sim
Custos de manutenção	Não	Sim	N/A	Sim
Inatividade dos dados	Nunca	Sim	Nunca	Nunca
Limitada a uma única tabela	Não	Não	Não	Sim

Interação com outros recursos do BigQuery

Os seguintes recursos do BigQuery funcionam de forma transparente com visualizações materializadas:

• Explicação do plano de consulta: o plano de consulta vai refletir quais visualizações materializadas foram verificadas (se houver) e mostrará quantos bytes foram lidos ao todo da visualização materializada e da tabela base.

• Cache de consulta: os resultados de uma consulta reescrita usando uma visualização materializada podem ser armazenados sujeitos às limitações padrão (uso de funções determinísticas, streaming na tabela base etc).

• Cálculo de custos: com o faturamento sob demanda, se a consulta foi reescrita usando visualizações materializadas e leu menos bytes do que a tabela base, a consulta ficará mais barata.

• Restrição de custo: se você tiver definido um valor para o máximo de bytes cobrados e uma consulta ler bytes além do limite, a consulta vai falhar sem incorrer em cobrança, seja por uso da visualização materializada, da tabela base, ou ambas.

• Estimativa de custo com a simulação: uma simulação repete a lógica de uma consulta reescrita, usando as visualizações materializadas disponíveis para fornecer uma estimativa de custo precisa. É possível usá-la como forma de testar se uma consulta específica utiliza visualizações materializadas.

Limitações

• Não é possível manipular diretamente ou ler o conteúdo bruto de uma visualização materializada. Isso significa que as seguintes ações não são compatíveis:

  • Copiar uma visualização materializada, como uma fonte de destino de um job de cópia.
  • Exportar uma visualização materializada.
  • Carregar dados em uma visualização materializada
  • Gravar um resultado de consulta em uma visualização materializada
  • Executar instruções DML em uma visualização materializada.

• Uma visualização materializada precisa residir na mesma organização da tabela base ou no mesmo projeto se o projeto não pertencer a uma organização.

• Cada tabela base pode ser referenciada por até 20 visualizações materializadas do mesmo conjunto de dados, até 100 visualizações materializadas do mesmo projeto e até 500 visualizações materializadas de toda a organização.

• Somente visualizações materializadas do mesmo conjunto de dados são consideradas na regravação automática de consulta (ou no ajuste inteligente).

• As visualizações materializadas são compatíveis com uma sintaxe SQL restrita e um conjunto limitado de funções de agregação. Para mais informações, consulte Visualizações materializadas compatíveis.

• Uma visualização materializada pode fazer referência apenas a uma única tabela e não pode usar mesclagens.

• As visualizações materializadas não podem ser aninhadas em outras visualizações materializadas.

• Só é possível usar o dialeto SQL padrão para visualizações materializadas.

• Instruções DDL SHOW não são compatíveis com visualizações materializadas.

• Instruções DDL ALTER não são compatíveis com visualizações materializadas, exceto ALTER com SET OPTIONS.

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

• Depois que uma visualização materializada é criada sobre 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, primeiro exclua todas as visualizações materializadas criadas nessa tabela.

Considerações sobre ARRAY_AGG

Selecionar colunas ARRAY_AGG de uma visualização materializada envolve as mesmas limitações que usar ARRAY_AGG ao selecionar a partir da tabela base. No caso de RESPECT NULLS, que é definido por padrão, as matrizes de saída não podem conter elementos NULL. Um erro será gerado se uma matriz de saída contiver NULL para um elemento.

Nesta visualização materializada:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT
  column_1, ARRAY_AGG(column_2 ORDER BY column_3 DESC LIMIT 4) column_arr
FROM `project.dataset.base_table`
GROUP BY column_1

Essa consulta vai falhar.

SELECT * FROM project-id.my_dataset.my_mv_table

Os detalhes do erro dizem:

query: Array cannot have a null element; error in writing field

Para evitar esse erro, adicione IGNORE NULLS a ARRAY_AGG:

ARRAY_AGG(col_2 ORDER BY col_3 DESC LIMIT 4 IGNORE NULLS) col

Como alternativa, formate a coluna da matriz de saída com o especificador '%t' ou '%T'.

SELECT column_1, FORMAT('%T', column_arr) FROM project-id.my_dataset.my_mv_table

Considerações sobre JOIN

Embora no momento não haja compatibilidade para junções, é possível aproveitar as visualizações materializadas para reduzir o custo e a latência de uma consulta que faz agregação sobre a junção. Por exemplo, pense em um caso em que você une uma tabela de fatos grande a poucas tabelas de dimensões pequenas e, em seguida, agregando sobre a junção. Pode ser prático reescrever a consulta para realizar primeiro a agregação na tabela de fatos usando chaves estrangeiras como chaves de agrupamento, depois juntar o resultado com a tabela de dimensões e, por fim, realizar uma pós-agregação.

Para demonstrar essa abordagem, vamos usar a consulta nº 52 do comparativo de mercado TPC-DS.

Esta é a consulta original:

SELECT  dt.d_year,
   item.i_brand_id brand_id,
   item.i_brand brand,
 SUM(ss_ext_sales_price) ext_price
 FROM project.tpcds_10T.date_dim dt,
   project.tpcds_10T.store_sales,
   project.tpcds_10T.item
 WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
    and store_sales.ss_item_sk = item.i_item_sk
    and item.i_manager_id = 1
    and dt.d_moy=12
    and dt.d_year=1998
 GROUP BY dt.d_year,
   item.i_brand,
   item.i_brand_id
 ORDER BY dt.d_year,
   ext_price DESC,
   brand_id
LIMIT 100

Esta é a consulta alternativa:

WITH sales_summary AS (
  SELECT ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk
)
SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) AS ext_price
FROM sales_summary,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

Veja como criar a visualização:

CREATE OR REPLACE MATERIALIZED VIEW project-id.tpcds_10T.sales_summary_mv AS
  SELECT
    ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project-id.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk

A seguir, veja como juntar a visualização materializada às tabelas de dimensões:

SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) as ext_price
FROM sales_summary_mv,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

Próximas etapas

• Leia as perguntas frequentes sobre visualizações materializadas.
• Saiba como criar e usar visualizações materializadas.
• Saiba mais sobre as práticas recomendadas do BigQuery Materialized View.