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. |
1 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.
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, excetoALTER
comSET 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.