Usar visualizações materializadas no Databricks SQL

Importante

Esse recurso está em uma versão prévia.

Este artigo descreve como criar e usar exibições materializadas no Databricks SQL para melhorar o desempenho e reduzir o custo de suas cargas de trabalho de processamento e análise de dados.

O que são exibições materializadas?

No Databricks SQL, as exibições materializadas são tabelas gerenciadas do Catálogo do Unity que permitem que os usuários pré-computem resultados com base na versão mais recente dos dados nas tabelas de origem. As exibições materializadas no Azure Databricks diferem de outras implementações, pois os resultados retornados refletem o estado dos dados quando a exibição materializada foi atualizada pela última vez em vez de sempre atualizar os resultados quando a exibição materializada é consultada. Você pode atualizar manualmente exibições materializadas ou agendar atualizações.

As exibições materializadas são poderosas para cargas de trabalho de processamento de dados, como processamento etl (extração, transformação e carregamento). As exibições materializadas fornecem uma maneira simples e declarativa de processar dados para conformidade, correções, agregações ou CDC (captura geral de dados de alterações). As exibições materializadas reduzem o custo e melhoram a latência da consulta ao pré-computar consultas lentas e computações usadas com frequência. As exibições materializadas também permitem transformações fáceis de usar limpando, enriquecendo e desnormalizando tabelas base. As exibições materializadas podem reduzir os custos, fornecendo uma experiência simplificada do usuário final porque, em alguns casos, elas podem calcular incrementalmente as alterações das tabelas base.

As exibições materializadas receberam suporte pela primeira vez no Databricks Lakehouse com o lançamento das Tabelas Dinâmicas Delta. Quando você cria uma exibição materializada em um SQL warehouse do Databricks, um pipeline do Delta Live Tables é criado para processar atualizações para a exibição materializada. Você pode monitorar o status de operações de atualização na interface do usuário do Delta Live Tables, na API do Delta Live Tables ou na CLI do Delta Live Tables. Consulte Exibir a status de uma atualização de exibição materializada.

Requisitos

• Use um SQL warehouse do Databricks habilitado para o Catálogo do Unity a fim de criar e atualizar exibições materializadas.

• O workspace deve estar em uma região habilitada para uso sem servidor.

Para saber mais sobre restrições ao usar exibições materializadas com o SQL do Databricks, confira Limitações.

Criar uma exibição materializada

Para criar uma exibição materializada, use a instrução CREATE MATERIALIZED VIEW. Consulte CREATE MATERIALIZED VIEW na referência do Databricks SQL. Para enviar uma instrução create, use o editor do SQL na interface do usuário do Azure Databricks, na CLI do SQL do Databricks ou na API do SQL do Databricks.

Observação

O usuário que cria uma exibição materializada é o proprietário da exibição materializada e precisa ter as seguintes permissões:

• SELECT privilégio nas tabelas base referenciadas pela exibição materializada.
• USE CATALOG e USE SCHEMA privilégios no catálogo e no esquema que contém as tabelas de origem para a exibição materializada.
• USE CATALOG e USE SCHEMA privilégios no catálogo de destino e no esquema para a exibição materializada.
• CREATE TABLE e CREATE MATERIALIZED VIEW privilégios no esquema que contém a exibição materializada.

O exemplo a seguir cria a exibição materializada mv1 da tabela base base_table1:

CREATE MATERIALIZED VIEW mv1
AS SELECT
  date, sum(sales) AS sum_of_sales
FROM
  table1
GROUP BY
  date;

Como as exibições materializadas são criadas?

As operações de exibição CREATE materializadas do Databricks SQL usam um SQL warehouse do Databricks para criar e carregar dados na exibição materializada. Como a criação de uma exibição materializada é uma operação síncrona no SQL warehouse do Databricks, o CREATE MATERIALIZED VIEW comando é bloqueado até que a exibição materializada seja criada e a carga de dados inicial seja concluída. Um pipeline do Delta Live Tables é criado automaticamente para cada exibição materializada do Databricks SQL. Quando a exibição materializada é atualizada, uma atualização para o pipeline do Delta Live Tables é iniciada para processar a atualização.

Carregar dados de locais externos

O Databricks recomenda o carregamento de dados externos usando a Federação lakehouse para fontes de dados com suporte. Para obter informações sobre como carregar dados de fontes sem suporte da Federação de Lakehouse, confira Opções de formato de dados.

Atualizar uma exibição materializada

A operação REFRESH atualiza a exibição materializada para refletir as alterações mais recentes na tabela base. Para atualizar uma exibição materializada, use a instrução REFRESH MATERIALIZED VIEW. Consulte REFRESH (MATERIALIZED VIEW e STREAMING TABLE) na referência do SQL do Databricks. Para enviar uma instrução de atualização, use o editor de SQL na interface do usuário do Azure Databricks, na CLI do SQL do Databricks ou na API do SQL do Databricks.

Somente o proprietário pode REFRESH a exibição materializada.

O exemplo a seguir atualiza a exibição materializada mv1:

REFRESH MATERIALIZED VIEW mv1;

Como as exibições materializadas do Databricks SQL são atualizadas?

As exibições materializadas do Databricks SQL usam o Delta Live Tables para operações de atualização. Quando a exibição materializada é atualizada, uma atualização para o pipeline do Delta Live Tables que gerencia a exibição materializada é iniciada para processar a atualização.

As atualizações de exibição materializadas do Databricks SQL são assíncronas. Quando uma atualização de exibição materializada é iniciada, um trabalho em segundo plano é iniciado em um cluster do Delta Live Tables e o comando retorna imediatamente antes da conclusão da carga de dados.

Como a atualização é gerenciada por um pipeline do Delta Live Tables, o SQL Warehouse do Databricks usado para criar a exibição materializada não é usado e não precisa ser executado durante a operação de atualização.

Algumas consultas podem ser atualizadas incrementalmente. Confira Atualização incremental de exibições materializadas. Se uma atualização incremental não puder ser executada, uma atualização completa será executada.

Agendar atualizações de modo de exibição materializado

Você pode configurar uma exibição materializada do Databricks SQL para atualizar automaticamente com base em um agendamento definido. Configure esse agendamento com a SCHEDULE cláusula quando você criar a exibição materializada ou adicionar um agendamento com a instrução ALTER VIEW. Quando um agendamento é criado, um novo trabalho do Databricks é configurado automaticamente para processar a atualização. Você pode exibir a agenda a qualquer momento com a instrução DESCRIBE EXTENDED.

Atualizar a definição de uma exibição materializada

Para atualizar a definição de uma exibição materializada, primeiro você deve descartar e recriar a exibição materializada.

Remover uma exibição materializada

Observação

Para enviar o comando para remover uma exibição materializada, você deve ser o proprietário dessa exibição materializada.

Para remover uma exibição materializada, use a instrução DROP VIEW. Para enviar uma DROP instrução, você pode usar o editor de SQL na interface do usuário do Azure Databricks, na CLI do SQL do Databricks ou na API do SQL do Databricks. O exemplo a seguir descarta a exibição materializada mv1:

DROP MATERIALIZED VIEW mv1;

Descrever uma exibição materializada

Para recuperar as colunas e os tipos de dados para uma exibição materializada, use a instrução DESCRIBE. Para recuperar as colunas, os tipos de dados e os metadados, como proprietário, local, hora de criação e status de atualização para uma exibição materializada, use DESCRIBE EXTENDED. Para enviar uma DESCRIBE instrução, use o editor de SQL na interface do usuário do Azure Databricks, na CLI do SQL do Databricks ou na API do SQL do Databricks.

Exibir o status da atualização de uma exibição materializada

Observação

Como um pipeline do Delta Live Tables gerencia atualizações de exibição materializadas, há latência incorrida pelo tempo de inicialização do pipeline. Esse tempo pode ser de segundos a minutos, além do tempo necessário para executar a atualização.

Você pode exibir a status de uma atualização de exibição materializada exibindo o pipeline que gerencia a exibição materializada na interface do usuário do Delta Live Tables ou exibindo as Informações de Atualização retornadas pelo DESCRIBE EXTENDED comando para a exibição materializada.

Você também pode exibir o histórico de atualização de uma exibição materializada consultando o log de eventos do Delta Live Tables. Veja Exibir o histórico de atualização de uma exibição materializada.

Exibir o status de atualização na interface do usuário do Delta Live Tables

Por padrão, o pipeline do Delta Live Tables que gerencia uma exibição materializada não está visível na interface do usuário do Delta Live Tables. Para exibir o pipeline na interface do usuário do Delta Live Tables, você deve acessar diretamente o link para a página de detalhes do pipeline do pipeline. Para acessar o link:

• Se você enviar o comando REFRESH no editor do SQL, siga o link no painel Resultados.
• Siga o link retornado pela instrução DESCRIBE EXTENDED.
• Na guia linhagem da exibição materializada, clique em Pipelines e, em seguida, clique no link do pipeline.

Parar uma atualização ativa

Para interromper uma atualização ativa na interface do usuário do Delta Live Tables, na página Detalhes do pipeline, clique em Parar para interromper a atualização do pipeline. Você também pode interromper a atualização com a CLI do Databricks ou a operação POST /api/2.0/pipelines/{pipeline_id}/stop na API de Pipelines.

Controlar o acesso a exibições materializadas

As exibições materializadas dão suporte a controles de acesso avançados para dar suporte ao compartilhamento de dados, evitando expor dados potencialmente privados. Um proprietário de exibição materializado pode conceder SELECT privilégios a outros usuários. Os usuários com SELECT acesso à exibição materializada não precisam de SELECT acesso às tabelas referenciadas pela exibição materializada. Esse controle de acesso permite o compartilhamento de dados enquanto controla o acesso aos dados subjacentes.

Conceder privilégios a uma exibição materializada

Para conceder acesso a uma exibição materializada, use a instrução GRANT:

GRANT
  privilege_type [, privilege_type ] ...
  ON <mv_name> TO principal;

A privilege_type pode ser:

• SELECT – o usuário pode SELECT a exibição materializada.
• REFRESH – o usuário pode REFRESH a exibição materializada. As atualizações são executadas usando as permissões do proprietário.

O exemplo a seguir cria uma exibição materializada e concede privilégios de seleção e atualização a um usuário:

CREATE MATERIALIZED VIEW <mv_name> AS SELECT * FROM <base_table>;
GRANT SELECT ON <mv_name> TO user;
GRANT REFRESH ON <mv_name> TO user;

Revogar privilégios de uma exibição materializada

Para revogar o acesso de uma exibição materializada, use a instrução REVOKE:

REVOKE
  privilege_type [, privilege_type ]
  ON <name> FROM principal;

Quando os privilégios SELECT em uma tabela base são revogados do proprietário da exibição materializada ou de qualquer outro usuário que recebeu privilégios SELECT para a exibição materializada ou a tabela base é descartada, o proprietário da exibição materializada ou acesso concedido pelo usuário ainda é capaz de consultar a exibição materializada. No entanto, ocorre o seguinte comportamento:

• O proprietário da exibição materializada ou outras pessoas que perderam o acesso a uma exibição materializada não podem mais REFRESH essa exibição materializada, e a exibição materializada ficará obsoleta.
• Se automatizado com um agendamento, o próximo agendado falhará REFRESH ou não será executado.

O exemplo a seguir revoga o SELECT privilégio de mv1:

REVOKE SELECT ON mv1 FROM user1;

Atualização incremental de exibições materializadas

As exibições materializadas sempre retornam os resultados semanticamente corretos para a consulta de definição com base na versão mais recente instantâneo dos dados disponíveis nas tabelas base no momento da última atualização. Quando possível, os resultados são atualizados incrementalmente, mas os resultados são idênticos àqueles que seriam entregues por recomputação completa.

Embora qualquer consulta possa ser definida em uma exibição materializada, apenas um subconjunto de consultas é atualizado incrementalmente. Se a exibição materializada não puder ser atualizada incrementalmente, o processo de atualização usará uma atualização completa. Para determinar qual modo é usado, consulte Determinando se uma atualização incremental ou completa é usada.

Que tipo de exibições materializadas são atualizadas incrementalmente?

Observação

Exibições materializadas que consultam determinados tipos de tabelas base têm um escopo maior de atualização incremental. Consulte (Avançado) Definindo exibições materializadas que podem ser atualizadas incrementalmente.

A seguir, descreve as expressões, as palavras-chave e as cláusulas que dão suporte à atualização incremental.

• A exibição materializada pode consultar apenas uma única tabela ou executar um INNER JOIN e UNION ALL (ou combinações de INNER JOIN e UNION ALL) em várias tabelas.
• A exibição materializada deve ter um GROUP BY na cláusula de seleção principal.
• A cláusula SELECT exibição materializada pode incluir apenas as seguintes funções de agregação:
  • SUM
  • COUNT
• As funções usadas na consulta para criar uma exibição materializada devem ser determinísticas. Por exemplo, o uso de CURRENT_TIMESTAMP não é permitido.

A seguir, descrevemos as limitações para atualização incremental de exibições materializadas.

• A atualização incremental não tem suporte para exibições materializadas que incluem:
  • Funções de janela.
  • HAVING cláusulas.
  • Subconsultas em SELECT cláusulas ou WHERE.
  • LEFT JOINs ou OUTER JOINs.
• Não há suporte para atualização incremental de exibições materializadas criadas usando o recurso de viagem no tempo do Delta Lake.

Além disso, o feed de dados de alteração é necessário nas tabelas base de exibições materializadas, exceto para determinados casos de uso avançados. Para ativar o feed de dados alterados em uma tabela base, defina a propriedade delta.enableChangeDataFeed table usando a seguinte sintaxe:

ALTER TABLE table1 SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

(Avançado) Definindo exibições materializadas que podem ser atualizadas incrementalmente

A seguir, descreve casos de uso avançados em que as exibições materializadas podem ser atualizadas incrementalmente. O feed de dados de alterações não é necessário para atualização incremental nos seguintes casos de uso avançados.

Observação

As seguintes limitações ainda se aplicam aos casos de uso descritos aqui:

• As funções usadas na consulta para criar uma exibição materializada devem ser determinísticas. Por exemplo, o uso de CURRENT_TIMESTAMP não é permitido.
• Exibições materializadas não podem ser criadas usando o recurso de viagem no tempo do Delta Lake.
• não há suporte para LEFT JOINs e OUTER JOINs.

Consultas em tabelas com partições

Exibições materializadas podem aproveitar partições para evitar atualizações completas. Quando uma exibição materializada compartilha as mesmas chaves de partição que suas tabelas base, a exibição materializada pode detectar partições alteradas e modificar apenas as partições na exibição materializada necessárias para materializar o resultado. Para tabelas grandes, isso pode economizar tempo e recursos significativos.

Veja a seguir as expressões, palavras-chave e cláusulas que dão suporte à atualização incremental ao definir exibições materializadas em tabelas com partições. Nos cenários a seguir, uma exibição materializada será "co-particionada" com uma tabela base se a exibição materializada compartilhar pelo menos uma chave de partição com as tabelas base.

• Uma exibição materializada particionada pode consultar uma única tabela.
  • A exibição materializada deve ser particionada com a tabela base.
• Uma exibição materializada particionada pode fazer um UNION ALL em várias tabelas.
  • A exibição materializada deve ser particionada em conjunto com todas as tabelas base.
• Uma exibição materializada particionada pode usar um INNER JOIN em uma tabela de fatos com uma tabela de dimensões.
  • A exibição materializada deve ser particionada com a tabela de fatos.
  • A tabela de dimensões não precisa ser particionada. Se uma atualização for detectada na tabela de dimensões, a exibição materializada será totalmente atualizada.

Consultas em tabelas que são somente acréscimo

Um padrão comum para tabelas na camada bronze é que novas linhas são inseridas apenas na tabela. As exibições materializadas detectam automaticamente tabelas base somente de acréscimo e são atualizadas incrementalmente inserindo apenas novas linhas na exibição materializada. Para tabelas grandes, isso pode economizar tempo e recursos significativos.

Os itens a seguir são necessários para dar suporte à atualização incremental ao definir exibições materializadas sobre tabelas somente de acréscimo:

• Uma exibição materializada pode consultar apenas uma única tabela ou INNER JOIN e UNION ALL (ou combinações de INNER JOIN and UNION ALL) em várias tabelas.
• As tabelas base devem ser somente acréscimo.
• Se uma linha for atualizada ou excluída na tabela base da exibição materializada por um sistema externo, a exibição materializada será totalmente atualizada.

Exibir o histórico de atualização para uma exibição materializada

Para exibir o status das operações REFRESH em uma exibição materializada, incluindo atualizações atuais e passadas, consulte o log de eventos do Delta Live Tables:

SELECT
  *
FROM
  event_log(TABLE(<fully-qualified-table-name>))
WHERE
  event_type = "update_progress"
ORDER BY
  timestamp desc;

Substitua <fully-qualified-table-name> pelo nome totalmente qualificado da exibição materializada, incluindo o catálogo e o esquema.

Confira O que é o log de eventos do Delta Live Tables?.

Determinando se uma atualização incremental ou completa é usada

Para otimizar o desempenho das atualizações de exibição materializadas, o Azure Databricks usa um modelo de custo para selecionar a técnica usada para a atualização. A tabela a seguir descreve estas técnicas:

Técnica	Atualização incremental?	Descrição
FULL_RECOMPUTE	Não	A exibição materializada foi totalmente recomputada
NO_OP	Não aplicável	A exibição materializada não foi atualizada porque nenhuma alteração na tabela base foi detectada.
ROW_BASED ou PARTITION_OVERWRITE	Sim	A exibição materializada foi atualizada incrementalmente usando a técnica especificada.

Para determinar a técnica usada, consulte o log de eventos do Delta Live Tables onde event_type é planning_information:

SELECT
  timestamp,
  message
FROM
  event_log(TABLE(<fully-qualified-table-name>))
WHERE
  event_type = 'planning_information'
ORDER BY
  timestamp desc;

Substitua <fully-qualified-table-name> pelo nome totalmente qualificado da exibição materializada, incluindo o catálogo e o esquema.

Confira O que é o log de eventos do Delta Live Tables?.

Limitações

• Há restrições sobre como as MVs podem ser gerenciadas e onde elas podem ser consultadas:

  • As exibições materializadas do Databricks SQL só podem ser criadas e atualizadas em SQL warehouses profissionais e SQL warehouses sem servidor.
  • Uma exibição materializada do Databricks SQL só pode ser atualizada do espaço de trabalho que a criou.
  • As exibições materializadas do DATAbricks SQL só podem ser consultadas em SQL warehouses do Databricks, Delta Live Tables e clusters compartilhados que executam o Databricks Runtime 11.3 ou superior. Você não pode consultar exibições materializadas de clusters de modo de acesso de usuário único.

• As tabelas base para a exibição materializada devem ser registradas no Catálogo do Unity como tabelas gerenciadas ou externas.

• O proprietário da exibição materializada do Databricks SQL não pode ser alterado.

• As exibições materializadas não dão suporte a colunas de identidade ou chaves substitutas.

• Não é possível executar comandos ad hoc OPTIMIZE ou VACUUM em exibições materializadas.

• Se uma exibição materializada usar uma soma agregada em uma NULLcoluna capaz e apenas NULL os valores permanecerem nessa coluna, o valor agregado resultante das exibições materializadas será zero em vez de NULL.

• Embora as referências de coluna não exijam aliases, expressões de referência não coluna exigem um alias. Por exemplo, a instrução a seguir não é permitida: SELECT col1, SUM(col2) FROM t GROUP BY col1. Em vez disso, use a seguinte instrução: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1.

• Não é possível usar filtros de linha ou máscaras de coluna com exibições materializadas do Databricks SQL.

• Os arquivos subjacentes que dão suporte a exibições materializadas podem incluir dados de tabelas upstream (incluindo possíveis informações de identificação pessoal) que não aparecem na definição de exibição materializada. Esses dados são adicionados automaticamente ao armazenamento subjacente para dar suporte à atualização incremental de exibições materializadas. Como os arquivos subjacentes de uma exibição materializada podem correr o risco de expor dados de upstream tabelas que não fazem parte do esquema de exibição materializado, o Databricks recomenda não compartilhar o armazenamento subjacente com consumidores downstream não confiáveis. Por exemplo, suponha que a definição de uma exibição materializada inclua uma cláusula COUNT(DISTINCT field_a). Embora a definição de exibição materializada inclua apenas a cláusula de agregação COUNT DISTINCT, os arquivos subjacentes conterão uma lista dos valores reais de field_a.

• Não há suporte para exibições materializadas do Databricks SQL nas regiões Centro-Sul dos EUA e Oeste dos EUA 2.