Faça a gestão das recomendações de vistas materializadas

Este documento descreve como funciona o recomendador de vistas materializadas e também mostra como ver e aplicar recomendações de vistas materializadas.

Introdução

O recomendador de vistas materializadas do BigQuery pode ajudar a melhorar o desempenho da carga de trabalho e a poupar o custo de execução da carga de trabalho. Estas recomendações baseiam-se nas caraterísticas de execução de consultas históricas dos últimos 30 dias.

As vistas materializadas são vistas pré-calculadas que armazenam em cache periodicamente os resultados de uma consulta para aumentar o desempenho e a eficiência. As vistas materializadas usam a ajustamento inteligente para reescrever de forma transparente consultas em tabelas de origem para usar vistas materializadas existentes para melhor desempenho e eficiência.

Como funciona o motor de recomendações

O recomendador gera recomendações diariamente para cada projeto que executa tarefas de consulta no BigQuery. As recomendações baseiam-se na análise da execução da carga de trabalho nos últimos 30 dias. O recomendador de vistas materializadas procura padrões de consultas repetitivos e calcula as poupanças que poderiam ser feitas se a subconsulta repetitiva pudesse ser movida para uma vista materializada incremental. O recomendador tem em conta as poupanças no momento da consulta e o custo de manutenção da conta para a vista materializada. Se estes fatores combinados mostrarem um resultado positivo significativo, o motor de recomendações faz uma recomendação.

Considere o seguinte exemplo de consulta:

WITH revenue   AS
(SELECT l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey)
SELECT s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
FROM
supplier,
revenue
WHERE s_suppkey = supplier_no
AND total_revenue =
  (SELECT max(total_revenue)
    FROM revenue)
ORDER BY s_suppkey

Este exemplo de consulta mostra informações sobre o fornecedor principal. A consulta contém uma expressão de tabela comum (CTE) denominada revenue que representa a receita total por cada fornecedor (l_suppkey). revenue é unida à tabela de fornecedores na condição de que o total_revenue do fornecedor corresponda a max(total_revenue) em todos os fornecedores. Como resultado, a consulta calcula informações (l_suppkey, s_name, s_address, s_phone, total_revenue) sobre o fornecedor com a receita total máxima.

A própria consulta é demasiado complicada para ser colocada numa vista materializada incremental. No entanto, a supplierCTE é uma agregação numa única tabela, um padrão de consulta suportado pelas vistas materializadas incrementais. O supplier CTE também é a parte mais dispendiosa em termos de computação da consulta. Por conseguinte, se a consulta de exemplo fosse executada repetidamente em tabelas de origem em constante mudança, o recomendador de vistas materializadas poderia sugerir colocar o CTE numa vista materializada.supplier A recomendação de vista materializada para a consulta de exemplo anterior pode ser semelhante à seguinte:

CREATE MATERIALIZED VIEW mv AS
SELECT l_suppkey as supplier_no,
         sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey

A API Recommender também devolve informações de execução de consultas sob a forma de estatísticas. As estatísticas são conclusões que ajudam a compreender a carga de trabalho do seu projeto, fornecendo mais contexto sobre como uma recomendação de vista materializada pode melhorar os custos da carga de trabalho.

Limitações

Antes de começar

Antes de poder ver ou aplicar recomendações de vistas materializadas, tem de ativar a API Recommender.

Autorizações necessárias

Para receber as autorizações de que precisa para aceder às recomendações de vistas materializadas, peça ao seu administrador para lhe conceder a função de IAM visualizador do recomendador de vistas materializadas do BigQuery (roles/recommender.bigqueryMaterializedViewViewer). Para mais informações sobre a atribuição de funções, consulte o artigo Faça a gestão do acesso a projetos, pastas e organizações.

Esta função predefinida contém as autorizações necessárias para aceder às recomendações de vistas materializadas. Para ver as autorizações exatas que são necessárias, expanda a secção Autorizações necessárias:

Autorizações necessárias

São necessárias as seguintes autorizações para aceder às recomendações de vistas materializadas:

  • recommender.bigqueryMaterializedViewRecommendations.get
  • recommender.bigqueryMaterializedViewRecommendations.list

Também pode conseguir estas autorizações com funções personalizadas ou outras funções predefinidas.

Para mais informações acerca das funções e autorizações do IAM no BigQuery, consulte o artigo Introdução ao IAM.

Veja recomendações de vistas materializadas

Esta secção descreve como ver recomendações e estatísticas de vistas materializadas através da Google Cloud consola, da Google Cloud CLI ou da API Recommender.

Selecione uma das seguintes opções:

Consola

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. No menu de navegação, clique em Recomendações.

  3. O painel Recomendações do BigQuery é aberto. Em Otimize o custo da carga de trabalho do BigQuery, clique em Ver detalhes.

    Ver detalhes para ver todas as recomendações do BigQuery

  4. É apresentada uma lista de recomendações que mostra todas as recomendações geradas para o projeto atual. Para ver mais informações sobre uma recomendação de vista materializada ou uma estatística da tabela específica, clique em Detalhes.

Em alternativa, pode ver todas as recomendações disponíveis para o seu projeto ou organização clicando em Recomendações no menu de navegação.

gcloud

Para ver recomendações de vistas materializadas para um projeto específico, use o comando gcloud recommender recommendations list:

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.materializedview.Recommender \
    --format=FORMAT_TYPE \

Substitua o seguinte:

  • PROJECT_NAME: o nome do projeto que executa tarefas de consulta
  • REGION_NAME: a região na qual as tarefas de consulta são executadas
  • FORMAT_TYPE: um formato de saída da CLI gcloud suportado, por exemplo, JSON
A tabela seguinte descreve os campos importantes da resposta `recommendations`:

Propriedade Relevante para o subtipo Descrição
recommenderSubtype CREATE_MATERIALIZED_VIEW O tipo de recomendação.
content.overview.sql CREATE_MATERIALIZED_VIEW Declaração DDL sugerida que cria uma vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Milissegundos de espaço estimados a serem poupados mensalmente pela vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Bytes estimados analisados para serem guardados mensalmente por vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reservado para utilização futura.

Para ver as estatísticas que originaram recomendações de vistas materializadas através da CLI gcloud, use o comando gcloud recommender insights list:

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.materializedview.Insight \
    --format=FORMAT_TYPE \

Substitua o seguinte:

  • PROJECT_NAME: o nome do projeto que executa tarefas de consulta
  • REGION_NAME: a região na qual as tarefas de consulta são executadas
  • FORMAT_TYPE: um formato de saída da CLI gcloud suportado, por exemplo, JSON
A tabela seguinte descreve os campos importantes da resposta da API Insights:

Propriedade Relevante para o subtipo Descrição
content.queryCount CREATE_MATERIALIZED_VIEW Número de consultas no período de observação com um padrão repetitivo que pode ser otimizado através da vista materializada.

API REST

Para ver recomendações de vistas materializadas para um projeto específico, use a API REST. Com cada comando, tem de fornecer um token de autenticação, que pode obter através da CLI gcloud. Para mais informações sobre como obter um token de autenticação, consulte Métodos para obter um token de ID.

Pode usar o pedido curl list para ver todas as recomendações de um projeto específico:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/recommenders/google.bigquery.materializedview.Recommender/recommendations

Substitua o seguinte:

  • PROJECT_NAME: o nome do projeto que contém a sua tabela do BigQuery
  • LOCATION: a localização onde o projeto está localizado.
A tabela seguinte descreve os campos importantes da resposta `recommendations`:

Propriedade Relevante para o subtipo Descrição
recommenderSubtype CREATE_MATERIALIZED_VIEW O tipo de recomendação.
content.overview.sql CREATE_MATERIALIZED_VIEW Declaração DDL sugerida que cria uma vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Milissegundos de espaço estimados a serem poupados mensalmente pela vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Bytes estimados analisados para serem guardados mensalmente por vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reservado para utilização futura.

Para ver as estatísticas que originaram recomendações de vistas materializadas através da API REST, execute o seguinte comando:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/insightTypes/google.bigquery.materializedview.Insight/insights

Substitua o seguinte:

  • PROJECT_NAME: o nome do projeto que contém a sua tabela do BigQuery
  • LOCATION: a localização onde o projeto está localizado.
A tabela seguinte descreve os campos importantes da resposta da API Insights:

Propriedade Relevante para o subtipo Descrição
content.queryCount CREATE_MATERIALIZED_VIEW Número de consultas no período de observação com um padrão repetitivo que pode ser otimizado através da vista materializada.

Ver recomendações com INFORMATION_SCHEMA

Também pode ver as suas recomendações e estatísticas através de INFORMATION_SCHEMAvistas. Por exemplo, pode usar a vista INFORMATION_SCHEMA.RECOMMENDATIONS para ver as três principais recomendações com base nas poupanças de posições, conforme mostrado no exemplo seguinte:

SELECT
   recommender,
   target_resources,
   LAX_INT64(additional_details.overview.bytesSavedMonthly) / POW(1024, 3) as est_gb_saved_monthly,
   LAX_INT64(additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) as slot_hours_saved_monthly,
  last_updated_time
FROM
  `region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS
WHERE
   primary_impact.category = 'COST'
AND
   state = 'ACTIVE'
ORDER by
   slot_hours_saved_monthly DESC
LIMIT 3;

O resultado é semelhante ao seguinte:

+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
|                    recommender                    |   target_resources      | est_gb_saved_monthly | slot_hours_saved_monthly |  last_updated_time
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
| google.bigquery.materializedview.Recommender      | ["project_resource"]    | 140805.38289248943   |        9613.139166666666 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_1"]    | 4393.7416711859405   |        56.61476777777777 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_2"]    |   3934.07264107652   |       10.499466666666667 |  2024-07-01 13:00:00
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+

Para obter mais informações, consulte os seguintes recursos:

Aplique recomendações de vistas materializadas

Pode aplicar uma recomendação para criar uma vista materializada executando a declaração DDL do CREATE MATERIALIZED VIEWtipo Google Cloud sugerida na consola.

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. No menu de navegação, clique em Recomendações.

  3. O painel Recomendações do BigQuery é aberto. Em Otimize o custo da carga de trabalho do BigQuery, clique em Ver detalhes.

    Ver detalhes para ver todas as recomendações do BigQuery

  4. É apresentada uma lista de recomendações que mostra todas as recomendações geradas para o projeto ou a organização atual, consoante o âmbito selecionado. Localize uma recomendação de vista materializada e clique em Detalhes.

  5. Clique em Ver no BigQuery Studio. É aberto um editor de SQL com uma declaração DDL.CREATE MATERIALIZED VIEW

  6. Na declaração CREATE MATERIALIZED VIEW fornecida, modifique o marcador de posição MATERIALIZED_VIEW com um nome de vista materializada exclusivo.

  7. Execute a CREATE MATERIALIZED VIEWdeclaração DDL para criar uma vista materializada recomendada.

Resolva problemas de recomendações

Problema: não são apresentadas recomendações para uma tabela específica.

As recomendações de vistas materializadas podem não aparecer nas seguintes circunstâncias:

  • Não foram encontrados padrões de consulta recorrentes entre as tarefas de consulta executadas por um projeto.
  • Os padrões de consulta recorrentes não satisfazem as limitações para vistas materializadas incrementais e não podem ser colocados numa vista materializada adequada para o ajuste inteligente.
  • A potencial vista materializada teria um custo de manutenção elevado. Por exemplo, as tabelas de origem são frequentemente modificadas por operações de linguagem de manipulação de dados (DML) e, por isso, uma vista materializada seria totalmente atualizada, incorrendo em custos adicionais.
  • Existe um número insuficiente de consultas com um padrão recorrente comum.
  • As poupanças mensais estimadas são demasiado insignificantes (inferiores a 1 espaço).
  • As tarefas de consulta executadas pelo projeto já usam vistas materializadas.

Preços

Não existe nenhum custo nem impacto negativo no desempenho da carga de trabalho quando vê as recomendações.

Quando aplica recomendações criando vistas materializadas, pode incorrer em custos de armazenamento, manutenção e consultas. Para mais informações, consulte os preços das vistas materializadas.