Resolver problemas com visualizações materializadas

Este documento ajuda a resolver problemas comuns relacionados a visualizações materializadas no BigQuery, incluindo erros ao criar visualizações materializadas, falhas de atualização e performance de consulta inesperada.

Fluxo de trabalho de diagnóstico

Ao investigar um problema com uma visualização materializada, siga estas etapas de diagnóstico para identificar a causa raiz:

1. Verifique o tipo de tabela e os metadados. Confirme se a tabela de destino é uma visualização materializada e verifique as opções de configuração dela:

   SELECT
    table_name,
    table_type
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLES
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   Substitua:

   • PROJECT_ID: o projeto que contém a visualização materializada.
   • DATASET: o conjunto de dados que contém a visualização materializada.
   • MATERIALIZED_VIEW: o nome da visualização materializada.

   Para inspecionar opções de configuração como enable_refresh, refresh_interval_minutes e max_staleness, consulte a visualização INFORMATION_SCHEMA.TABLE_OPTIONS:

   SELECT
    table_name,
    option_name,
    option_value
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

2. Verifique o status da última atualização. Consulte a visualização INFORMATION_SCHEMA.MATERIALIZED_VIEWS para verificar quando ela foi atualizada pela última vez e se a última atualização automática encontrou erros:

   SELECT
    table_name,
    last_refresh_time,
    refresh_watermark,
    last_refresh_status
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   Se last_refresh_status não for NULL, o último job de atualização automática falhou. Se last_refresh_time for NULL ou antigo, a atualização da visualização materializada nunca foi concluída ou está falhando.

3. Inspecionar o histórico e os erros do job de atualização. Consulte a visualização INFORMATION_SCHEMA.JOBS_BY_PROJECT para inspecionar jobs de atualização automática recentes:

   SELECT
    job_id,
    creation_time,
    end_time,
    state,
    error_result.reason AS error_reason,
    error_result.message AS error_message,
    total_slot_ms,
    total_bytes_processed
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id LIKE '%materialized_view_refresh_%'
    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
   ORDER BY
    creation_time DESC
   LIMIT 50;

   Substitua REGION pela região do conjunto de dados, por exemplo, us ou europe-west3.

4. Examine as estatísticas de execução de consultas e ajuste inteligente. Se uma consulta estiver sendo executada mais lentamente do que o esperado, examine o campo materialized_view_statistics nas estatísticas do job para verificar se o otimizador de consultas usou a visualização materializada:

   SELECT
    job_id,
    total_slot_ms,
    total_bytes_billed,
    materialized_view_statistics
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id = 'JOB_ID';

   Substitua JOB_ID pelo ID do job de consulta.

Resolver problemas de erros na criação de visualizações materializadas

Esta seção descreve os erros que podem ocorrer ao criar visualizações materializadas, além das causas e etapas de resolução.

Operador ou sintaxe SQL sem suporte

Mensagem de erro:

Unsupported operator in materialized view: KEYWORD

ou

Materialized view queries do not support FEATURE

Causa:

As visualizações materializadas incrementais são compatíveis com um subconjunto restrito da sintaxe SQL para permitir a manutenção incremental e o ajuste inteligente. Esse erro pode ocorrer se a consulta que define sua visualização materializada incluir recursos incompatíveis, como os seguintes:

• Funções não determinísticas (por exemplo, CURRENT_TIMESTAMP(), RAND() ou SESSION_USER())
• Funções da janela analítica com OVER()
• Cláusulas ORDER BY ou LIMIT
• DISTINCT sem agregação
• Subconsultas nas cláusulas WHERE ou SELECT
• Funções definidas pelo usuário (UDFs)

Resolução:

• Confira a lista de recursos SQL sem suporte.

• Se a consulta exigir recursos mais amplos do SQL, crie uma visualização materializada não incremental definindo allow_non_incremental_definition = true e um intervalo max_staleness:

  CREATE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
  allow_non_incremental_definition = true
  ) AS
  SELECT
  ...

  Substitua:

  • PROJECT_ID: o projeto que contém a visualização materializada.
  • DATASET: o conjunto de dados que contém a visualização materializada.
  • MATERIALIZED_VIEW: o nome da visualização materializada.

  As visualizações materializadas não incrementais são compatíveis com um conjunto mais amplo de consultas SQL, mas sempre fazem atualizações completas e não oferecem suporte a ajustes inteligentes.

• Se a sintaxe SQL necessária não for compatível com visualizações materializadas não incrementais, use uma visualização lógica ou uma consulta programada para gravar resultados em uma tabela de destino.

max_staleness inválido com tabela base da CDC

Mensagem de erro:

Materialized view PROJECT_ID:DATASET.MATERIALIZED_VIEW has a CDC table as base table PROJECT_ID:DATASET.TABLE but does not have valid max_staleness. Materialized views over CDC tables must have max_staleness set at least 2 times the base table's max_staleness: 0-0 0 0:0:0

Causa:

Quando você cria uma visualização materializada em uma tabela base de captura de dados alterados (CDC), a opção max_staleness da visualização materializada precisa ser configurada para pelo menos o dobro do valor max_staleness da tabela base.

Resolução:

1. Verifique o valor max_staleness da tabela de CDC base consultando a visualização INFORMATION_SCHEMA.TABLE_OPTIONS.
2. Defina a opção max_staleness da visualização materializada como um valor que seja pelo menos duas vezes o valor max_staleness da tabela de base. Por exemplo, se a tabela de CDC base tiver um valor max_staleness de 15 minutos, defina o valor max_staleness da visualização materializada como pelo menos 30 minutos. Para mais informações, consulte "Instrução ALTER MATERIALIZED VIEW SET OPTIONS" em Instruções da linguagem de definição de dados (DDL) no GoogleSQL.

Visualização materializada particionada em uma tabela base não particionada

Mensagem de erro:

Partitioned incremental materialized view must be created on top of partitioned managed storage base table.

Causa:

Para criar uma visualização materializada incremental particionada, a tabela base subjacente também precisa ser particionada, e a coluna de particionamento da visualização materializada precisa estar alinhada com a coluna de particionamento da tabela base.

Resolução:

• Se você quiser que a visualização materializada seja particionada, verifique se a tabela base está particionada e configure a visualização materializada para usar a mesma coluna de particionamento. Para mais informações, consulte Alinhamento de partição.
• Se a tabela base não for particionada, crie a visualização materializada sem uma cláusula PARTITION BY.
• Se você precisar de uma visualização particionada em uma tabela não particionada, crie uma visualização materializada não incremental com allow_non_incremental_definition = true e max_staleness. As visualizações materializadas não incrementais não exigem alinhamento de partição com tabelas base.

A réplica de conjunto de dados entre regiões é somente leitura

Mensagem de erro:

The dataset replica of the cross region dataset 'PROJECT_ID:DATASET' in region 'REGION' is read-only because it's not the primary replica.

Causa:

Quando você usa a replicação de conjuntos de dados entre regiões, as réplicas secundárias são somente leitura. Não é possível criar uma visualização materializada em uma região de réplica secundária.

Resolução:

Crie a visualização materializada na região principal do conjunto de dados replicado. Se você precisar da visualização materializada na região da réplica, crie uma réplica de visualização materializada nessa região. Para mais informações, consulte Gerenciar réplicas de visualização materializada.

Exceder o limite da tabela base

Mensagem de erro:

Materialized views support at most 10 source tables, query has NUMBER_OF_SOURCE_TABLES

Causa:

As visualizações materializadas do BigQuery aceitam junções em até 10 tabelas de base.

Resolução:

Refatore a consulta que define a visualização materializada para referenciar 10 ou menos tabelas de base. Se a arquitetura exigir a junção de mais de 10 tabelas, considere fazer isso com antecedência em tabelas estáticas ou de dimensão em tabelas intermediárias ou use uma consulta programada ou um pipeline do Dataform.

Recursos excedidos durante a criação da visualização materializada

Mensagem de erro:

Resources exceeded during query execution: The data accessed in this query is too large; consider accessing fewer tables, or for partitioned tables, fewer partitions.

Causa:

Ao criar uma visualização materializada, o BigQuery realiza uma atualização completa inicial para preencher a visualização. Se a tabela base subjacente contiver grandes quantidades de dados não particionados ou se a visualização produzir agregações intermediárias de alta cardinalidade, a atualização inicial poderá exceder a memória de slot ou os limites de consulta.

Resolução:

• Adicione condições de filtro na cláusula WHERE da visualização materializada para limitar o escopo dos dados verificados ao subconjunto necessário.
• Alinhe o particionamento da visualização materializada com o da tabela base para remover partições durante as atualizações.
• Se você estiver usando a computação sob demanda, considere usar as edições do BigQuery com reservas de slot dedicadas para fornecer capacidade de computação suficiente para atualizações grandes.

Problemas com tabelas do BigLake e cache de metadados

Sintomas:

As visualizações materializadas em tabelas externas do BigLake falham durante a criação ou não são atualizadas.

Causa:

As visualizações materializadas em tabelas externas têm requisitos arquitetônicos específicos:

• As visualizações materializadas são compatíveis apenas com tabelas do BigLake com o armazenamento em cache de metadados ativado.
• O valor max_staleness da visualização materializada precisa ser maior que o valor max_staleness da tabela base do BigLake subjacente.
• Uma visualização materializada pode referenciar tabelas externas do BigLake ou tabelas de armazenamento gerenciado do BigQuery, mas não pode misturar tipos em uma única visualização materializada.

Resolução:

1. Verifique se o armazenamento em cache de metadados está ativado em todas as tabelas de base do BigLake.
2. Configure max_staleness na visualização materializada com um valor maior que o intervalo de cache de metadados das tabelas de base. Por exemplo, se o intervalo de cache da tabela base for de 30 minutos, defina o max_staleness da visualização materializada como pelo menos 45 minutos para permitir um buffer para a execução da atualização.
3. Não misture tabelas externas e gerenciadas em uma definição de visualização materializada.

Resolver problemas de atualização

Esta seção descreve causas comuns de falhas de atualização e atrasos de performance para visualizações materializadas.

Mudanças no esquema da tabela base (invalidQuery)

Sintomas:

A coluna last_refresh_status em INFORMATION_SCHEMA.MATERIALIZED_VIEWS mostra um erro invalidQuery, e as atualizações automáticas param de ser executadas.

Causa:

Se o esquema de uma tabela base mudar (por exemplo, remover uma coluna referenciada pela visualização materializada, renomear uma coluna ou alterar o tipo de dados de uma coluna), a consulta subjacente que define a visualização materializada será invalidada.

Resolução:

O BigQuery não permite alterar o esquema de coluna de uma visualização materializada. Para resolver a invalidação do esquema, faça o seguinte:

1. Recrie a visualização materializada usando a instrução CREATE OR REPLACE MATERIALIZED VIEW:

   CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
   OPTIONS (
    enable_refresh = true,
    refresh_interval_minutes = 30
   ) AS
   SELECT
    ...

   Substitua:

   • PROJECT_ID: o projeto que contém a visualização materializada.
   • DATASET: o conjunto de dados que contém a visualização materializada.
   • MATERIALIZED_VIEW: o nome da visualização materializada.

2. Verifique se a nova definição corresponde ao esquema atualizado da tabela base.

Validade, truncamento ou mudanças de DML da partição da tabela base

Sintomas:

As atualizações de visualizações materializadas falham ou as consultas na visualização materializada revertem para a tabela base e são executadas lentamente.

Causa:

As seguintes operações de tabela base invalidam os dados da visualização materializada:

• Truncar uma tabela base ou partição de tabela base (TRUNCATE TABLE)
• Expiração de partição em uma tabela base
• Instruções de linguagem de manipulação de dados (DML) DELETE ou MERGE em tabelas não particionadas ou tabelas de base unidas secundárias

Quando essas operações ocorrem, as partições afetadas (ou toda a visualização materializada para tabelas não particionadas) são marcadas como inválidas.

Resolução:

1. Acione manualmente uma atualização para restaurar a visualização materializada a um estado válido:

   CALL BQ.REFRESH_MATERIALIZED_VIEW('PROJECT_ID.DATASET.MATERIALIZED_VIEW');

2. Se você executar pipelines de ETL em lote que executam instruções DML ou truncam dados regularmente, desative a atualização automática e chame BQ.REFRESH_MATERIALIZED_VIEW no final do pipeline de ETL. Para mais informações, consulte Atualização automática.

Atualizar jobs com tempo limite excedido

Sintomas:

Os jobs de atualização falham com um erro de tempo limite após serem executados por várias horas (até 12 horas).

Causa:

À medida que as tabelas de base crescem, o volume de dados processados durante uma atualização aumenta. Se a consulta de visualização materializada não filtrar linhas ou se a visualização não puder fazer atualizações incrementais devido à invalidação completa, cada atualização exigirá uma verificação completa das tabelas de base, o que pode esgotar o tempo de slot.

Resolução:

• Adicione critérios de filtro na cláusula WHERE da visualização materializada para restringir dados históricos desnecessários.
• Verifique se a visualização materializada está alinhada por partição com a tabela base para que apenas partições modificadas sejam atualizadas de forma incremental.
• Alocar uma reserva de slot com capacidade suficiente para acomodar a carga de trabalho de atualização.

Mensagem de atualização duplicada

Mensagem:

Materialized view is already being refreshed.

Causa:

Se as tabelas de base em uma visualização materializada JOIN forem atualizadas simultaneamente ou se uma atualização manual for acionada enquanto uma atualização automática já estiver em andamento, o BigQuery vai detectar a atualização simultânea e cancelar o job duplicado.

Resolução:

Esse comportamento é normal e temporário. O job duplicado é interrompido para evitar processamento redundante, e você não recebe cobranças pela tentativa de atualização duplicada. Você não precisa fazer nada.

Atrasos na atualização de dados de streaming (armazenamento otimizado para gravação)

Sintomas:

As consultas em tabelas base com dados de streaming de alta velocidade não aparecem imediatamente na visualização materializada, ou as consultas voltam para a tabela base.

Causa:

Os dados transmitidos para o BigQuery usando a API Storage Write são armazenados inicialmente no armazenamento otimizado para gravação (buffer de streaming). Os jobs de atualização da visualização materializada processam os dados depois que eles são confirmados e convertidos do buffer de streaming para o armazenamento colunar otimizado.

Para manter a consistência em tempo real, as consultas que leem da visualização materializada leem dados confirmados da visualização materializada e, simultaneamente, leem o delta diretamente do buffer de streaming da tabela base.

Resolução:

• Se for necessária consistência de leitura em tempo real nos dados de streaming, o planejador de consultas vai combinar automaticamente os dados da visualização materializada com os deltas da tabela base.
• Se a consistência em tempo real não for necessária e você quiser evitar a verificação do buffer de streaming em todas as consultas, defina max_staleness na visualização materializada (por exemplo, max_staleness = INTERVAL "15" MINUTE). Assim, as consultas podem ler diretamente da visualização materializada pré-calculada sem processamento delta.

Resolver problemas de desempenho de consultas e ajuste inteligente

Nesta seção, descrevemos como solucionar problemas de consultas que são executadas mais lentamente do que o esperado ou não aproveitam o ajuste inteligente.

Verificar o uso do ajuste inteligente

Ao consultar uma tabela base, o BigQuery usa o ajuste inteligente para reescrever automaticamente a consulta e usar uma visualização materializada disponível, se isso melhorar o desempenho e reduzir o custo.

Para verificar se uma consulta usou uma visualização materializada, inspecione o campo materialized_view_statistics nos detalhes do job de consulta ou consulte a visualização INFORMATION_SCHEMA.JOBS_BY_PROJECT:

SELECT
  job_id,
  total_slot_ms,
  total_bytes_billed,
  mv.table_reference.dataset_id,
  mv.table_reference.table_id,
  mv.chosen,
  mv.rejected_reason
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  job_id = 'JOB_ID';

Substitua:

• REGION: a região do conjunto de dados (por exemplo, us ou europe-west3).
• JOB_ID: o ID do job de consulta.

No objeto materialized_view_statistics, cada entrada na matriz materialized_view contém os seguintes campos:

• table_reference: identifica o candidato a visualização materializada.
• chosen: um booleano que indica se o otimizador de consultas selecionou a visualização materializada para execução (true) ou a rejeitou (false).
• estimated_bytes_saved: os bytes estimados que a consulta evitou verificar usando a visualização materializada.
• rejected_reason: se chosen for false, especifica o motivo pelo qual o otimizador rejeitou a visualização materializada.

Para mais informações sobre os motivos de rejeição e a enumeração rejected_reason, consulte Entenda por que as visualizações materializadas foram rejeitadas.

Motivos comuns para a rejeição de uma visualização materializada

Quando chosen for false, examine o valor de rejected_reason para diagnosticar a causa:

Visualização materializada não considerada (estrutura de consulta incompatível)

Se uma visualização materializada não estiver listada em materialized_view_statistics, o otimizador de consultas terá determinado durante a análise da sintaxe que o padrão de consulta não corresponde à definição da visualização materializada.

Estas são algumas causas comuns:

1. Incompatibilidade de agregação ou filtro. A consulta usa funções de agregação, colunas de agrupamento ou predicados de filtro que não podem ser calculados com base nas agregações pré-calculadas na visualização materializada.
   • Resolução: alinhe as funções de agregação e os agrupamentos entre as consultas e a definição da visualização materializada.
2. Visualizações materializadas não incrementais. As visualizações criadas com allow_non_incremental_definition = true não são compatíveis com o ajuste inteligente.
   • Resolução: consulte visualizações materializadas não incrementais diretamente especificando o nome da visualização na cláusula FROM.
3. Consulta direta em uma visualização desatualizada. Se você consultar diretamente uma visualização materializada que tenha max_staleness definido, a consulta vai retornar resultados pré-calculados desatualizados até max_staleness sem processamento delta das tabelas de base.

Erro de sketch HyperLogLog incompatível

Mensagem de erro:

Invalid or incompatible sketch in HLL_COUNT.MERGE_PARTIAL

Causa:

Quando você usa funções de agregação aproximadas, como HLL_COUNT.INIT e HLL_COUNT.MERGE_PARTIAL, o BigQuery usa esboços do HyperLogLog. Se o parâmetro de precisão especificado na consulta não corresponder ao parâmetro definido na visualização materializada, a operação de fusão de sketch vai falhar.

Resolução:

Verifique se o parâmetro de precisão (por exemplo, HLL_COUNT.INIT(x, 12)) é idêntico na definição da visualização materializada e nas consultas que fazem referência ou reescrevem a visualização.

Resolver problemas de alteração de visualização e modificações de esquema

Esta seção descreve os problemas que podem ocorrer ao modificar o esquema ou as opções de uma visualização materializada.

Como editar o esquema de visualização materializada

Problema:

A tentativa de adicionar ou modificar colunas em uma visualização materializada usando ALTER TABLE ou o console Cloud de Confiance resulta em um erro, ou a opção Editar esquema não está disponível.

Causa:

O BigQuery não permite modificar diretamente o esquema de coluna de uma visualização materializada.

Resolução:

• É possível modificar as opções de visualização materializada (como enable_refresh, refresh_interval_minutes e max_staleness) usando a instrução ALTER MATERIALIZED VIEW SET OPTIONS:

  ALTER MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  SET OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 20
  );

  Substitua:

  • PROJECT_ID: o projeto que contém a visualização materializada.
  • DATASET: o conjunto de dados que contém a visualização materializada.
  • MATERIALIZED_VIEW: o nome da visualização materializada.

• Para mudar a definição da consulta SQL, adicionar colunas ou mudar os tipos de dados das colunas, recrie a visualização usando CREATE OR REPLACE MATERIALIZED VIEW:

  CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  AS SELECT
  ...

A seguir

• Saiba como criar visualizações materializadas.
• Saiba como usar visualizações materializadas e ajuste inteligente.
• Saiba como gerenciar e atualizar visualizações materializadas.
• Saiba como monitorar atualizações e uso de visualizações materializadas.
• Saiba como resolver problemas gerais de desempenho de consultas.