Práticas recomendadas para visualizações materializadas

Este documento apresenta práticas recomendadas para visualizações materializadas ao usar o BigQuery. Antes de ler este documento, familiarize-se com Introdução às visualizações materializadas e Como criar e usar visualizações materializadas.

Considerações ao criar uma visualização materializada

Verifique se a definição da visualização materializada reflete os padrões de consulta em relação à tabela base. Como há no máximo 20 visualizações materializadas por tabela, você não deve criar uma visualização materializada para cada troca de uma consulta. Em vez disso, crie visualizações materializadas para veicular um conjunto mais amplo de consultas.

Por exemplo, considere uma consulta em uma tabela em que os usuários geralmente filtram por colunas user_id ou department. Você pode agrupar por essas colunas e, opcionalmente, agrupar por elas, em vez de adicionar filtros como user_id = 123 à visualização materializada.

Como outro exemplo, os usuários geralmente usam filtros de data, seja por data específica (where order_date = current_date()) ou período (where order_date between '2019-10-01' and '2019-10-31'). Como prática recomendada, adicione um filtro de período na visualização materializada que abrange os períodos esperados na consulta:

CREATE MATERIALIZED VIEW  ...
WHERE date > '2019-01-01' GROUP BY date

Se a tabela base for particionada e sua visualização materializada tiverem um tamanho significativo, a visualização materializada também deverá ser particionada. Como orientação geral, uma visualização materializada tem um tamanho significativo se for do mesmo tamanho ou maior que uma partição da tabela base.

Considerações ARRAY_AGG

Ao selecionar colunas ARRAY_AGG a partir de uma visualização materializada, você estará sujeito às mesmas limitações ao usar uma cláusula ARRAY_AGG para selecionar a tabela base. No caso do tratamento RESPECT NULLS, 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

a seguinte consulta falhará:

SELECT * FROM project-id.my_dataset.my_mv_table

Os detalhes da falha são os seguintes:

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

Para evitá-la, adicione IGNORE NULLS à cláusula 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 JOIN

Ainda que as mesclagens não sejam compatíveis, você pode aproveitar as visualizações materializadas para reduzir o custo e a latência de uma consulta que realiza a agregação sobre uma mesclagem. Por exemplo, pense no caso em que você une uma grande tabela de fatos a algumas tabelas pequenas e, em seguida, as agrega sobre a mesclagem. Pode ser prático reescrever a consulta para realizar primeiro a agregação sobre a tabela de fatos usando chaves externas como chaves de agrupamento, mesclar o resultado com a tabela 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

Em seguida, é possível mesclar a visualização materializada com as tabelas da seguinte forma:

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_mv.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary_mv.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

Considerações de manutenção para visualizações materializadas

Monitore o custo do job de atualização e ajuste o intervalo de atualização automática, se necessário. Monitore especificamente total_bytes_processed e total_slot_ms.

Por exemplo, se a taxa de ingestão na tabela base for relativamente pequena, convém atualizar a visualização com menos frequência. Caso contrário, se os dados subjacentes forem alterados rapidamente, convém atualizar com mais frequência.

Se a tabela base realiza a ingestão em pontos predefinidos no tempo, como extração, transformação e carregamento (ETL) de pipeline noturnos, considere assumir a propriedade total do cronograma de manutenção de visualização materializada, como a seguir:

  1. Desativar atualização automática.

  2. Execute uma atualização manual como parte do pipeline de ETL ou configurando uma consulta programada em horários específicos do dia.

Segurança das visualizações

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