Use exibições materializadas no Databricks SQL

Visualização

Este recurso está em visualização pública. Para se inscrever para acesso, preencha este formulário.

Este artigo descreve como criar e usar view materializada 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 visualizações materializadas?

No Databricks SQL, view materializada são tabelas gerenciadas Unity Catalog que permitem aos usuários pré-calcular os resultados com base na versão mais recente dos dados nas tabelas de origem. view materializada no Databricks difere de outras implementações, pois os resultados retornados refletem o estado dos dados quando a view materializada foi atualizada pela última vez, em vez de sempre atualizar os resultados quando a view materializada é query. Você pode refresh manualmente view materializada ou refresh programar.

view materializada é poderosa para cargas de trabalho de processamento de dados, como processamento de extração, transformação e carregamento (ETL). view materializada fornece uma maneira simples e declarativa de processar dados para compliance, correções, agregações ou captura geral de dados de alterações (CDC) (CDC). view materializada reduz o custo e melhora a latência da query pré-computando query lentas e cálculos usados com frequência. view materializada também permite transformações fáceis de usar, limpando, enriquecendo e desnormalizando tabelas base. view materializada pode reduzir os custos e, ao mesmo tempo, fornecer uma experiência simplificada ao usuário final porque, em alguns casos, eles podem compute de forma incremental as alterações das tabelas base.

view materializadas foram suportadas pela primeira vez na plataforma Databricks Data Intelligence com o lançamento do Delta Live Tables. Quando você cria uma view materializada em um armazém Databricks SQL , um pipeline Delta Live Tables é criado para processar refresh para a view materializada. Você pode monitorar o status das operações refresh na UI do Delta Live Tables, na API do Delta Live Tables ou na CLI do Delta Live Tables. Consulte Visualizar o status de uma refreshde visualização materializada.

Requisitos

  • Você deve usar um armazém Databricks SQL habilitado para Catálogo Unity para criar e refresh view materializada.

Para saber mais sobre as restrições ao usar view materializada com Databricks SQL, consulte Limitações.

Criar uma visualização materializada

Para criar um view materializado, use a instrução CREATE MATERIALIZED VIEW. Consulte CREATE MATERIALIZED VIEW na referência Databricks SQL. Para enviar uma instrução de criação, use o editor SQL na interface do usuário do Databricks, a CLI do Databricks SQL ou a API do Databricks SQL.

Observação

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

  • SELECT privilégio nas tabelas base referenciadas pela view materializada.

  • USE CATALOG e privilégios USE SCHEMA no catálogo e esquema contendo as tabelas de origem para a view materializada.

  • USE CATALOG e USE SCHEMA privilégios no catálogo de destino e esquema para a view materializada.

  • CREATE TABLE e privilégios CREATE MATERIALIZED VIEW no esquema que contém a view materializada.

O exemplo a seguir cria a view 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 visualizações materializadas são criadas?

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

Carregar dados de sistemas externos

Databricks recomenda carregar o uso de dados externo lakehouse Federation para fontes de dados suportadas. Para obter informações sobre o carregamento de dados de fontes não suportadas pela lakehouse Federation, consulte Opções de formato de dados.

Atualizar uma visualização materializada

As operações REFRESH atualizam o site materializado view para refletir as últimas alterações na tabela base. Para refresh um view materializado, use a instrução REFRESH MATERIALIZED VIEW. Consulte REFRESH (MATERIALIZED VIEW e STREAMING TABLE) na referência Databricks SQL. Para enviar uma instrução refresh, use o editor SQL na interface do usuário do Databricks, a CLI do Databricks SQL ou a API do Databricks SQL.

Somente o proprietário pode REFRESH view materializada .

O exemplo a seguir refresh a view materializada mv1 :

REFRESH MATERIALIZED VIEW mv1;

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

view materializada do Databricks SQL usa Delta Live Tables para operações refresh . Quando a view materializada é refresh , uma atualização no pipeline Delta Live Tables que gerencia a view materializada começa a processar a refresh.

refresh view materializada do Databricks SQL é assíncrona. Quando uma refresh view materializada é iniciada, um Job de segundo plano começa em clusters Delta Live Tables e o comando retorna imediatamente antes da conclusão do carregamento de dados.

Como a refresh é gerenciada por um pipeline Delta Live Tables, o Databricks SQL warehouse usado para criar a view materializada não é usado e não precisa ser executado durante as operações refresh .

Algumas query podem ser atualizadas de forma incremental. Consulte Atualização incremental da viewmaterializada. Se uma refresh incremental não puder ser executada, uma refresh completa será executada.

programar atualizações de visualização materializada

Você pode configurar uma view materializada SQL do Databricks para refresh automaticamente com base em um programar definido. Configure este programar com a cláusula SCHEDULE ao criar a visualização materializada ou inclua um programar com a instrução ALTER VIEW . Quando um programar é criado, um novo Job do Databricks é configurado automaticamente para processar a atualização. Você pode view o programar a qualquer momento com a instrução DESCRIBE EXTENDED.

Atualize a definição de uma visualização materializada

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

Solte uma visualização materializada

Observação

Para enviar o comando para descartar uma view materializada, você deve ser o proprietário dessa view materializada.

Para eliminar uma visualização materializada, use a instrução DROP VIEW. Para enviar uma instrução DROP, o senhor pode usar o editor SQL na interface do usuário do Databricks, a CLI do Databricks SQL ou a API do Databricks SQL. O exemplo a seguir elimina o mv1 materializado view:

DROP MATERIALIZED VIEW mv1;

Descrever uma visão materializada

Para recuperar as colunas e os tipos de dados de um site materializado view, 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 refresh de um site materializado view, use DESCRIBE EXTENDED. Para enviar uma instrução DESCRIBE, use o editor SQL na interface do usuário do Databricks, a CLI do Databricks SQL ou a API do Databricks SQL.

Visualize o status de uma atualização de visualização materializada

Observação

Como um pipeline Delta Live Tables gerencia refresh view materializada, há latência incorrida pelo tempo startup do pipeline. Esse tempo pode ser de segundos a minutos, além do tempo necessário para executar a refresh.

Você pode view o status de uma refresh de view materializada viewo pipeline que gerencia a view materializada na IU do Delta Live Tables ou viewa informaçãorefresh retornada pelo comando DESCRIBE EXTENDED para a view materializada.

Você também pode view o histórico refresh de uma view materializada consultando os logs de eventos do Delta Live Tables. view a história de atualização para uma viewmaterializada.

Visualize o status de atualização na IU do Delta Live Tables

Por default, o pipeline Delta Live Tables que gerencia uma view materializada não é visível na IU do Delta Live Tables. Para view o pipeline na IU 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 SQL, siga o link no painel Resultados .

  • Siga o link retornado pela instrução DESCRIBE EXTENDED .

  • Na tablinhagem da view materializada, clique em Pipelines e, em seguida, clique no link do pipeline.

Parar uma atualização ativa

Para interromper uma refresh ativa na UI do Delta Live Tables, na página de detalhes do pipeline , clique em Parar para interromper a atualização do pipeline. Você também pode interromper a refresh com a CLI do Databricks ou o POST /api/2.0/pipeline/{pipeline}/stop operações na API pipeline .

Controle o acesso a visualizações materializadas

view materializada oferece suporte a controles de acesso avançados para oferecer suporte ao compartilhamento de dados, evitando a exposição de dados potencialmente privados. Um proprietário view materializada pode conceder privilégios SELECT a outros usuários. Os usuários com acesso SELECT à view materializada não precisam de acesso SELECT às tabelas referenciadas pela view materializada . Esse controle de acesso permite o compartilhamento de dados enquanto controla o acesso aos dados subjacentes.

Conceder privilégios a uma visualização materializada

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

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

O tipo_de_privilégio pode ser:

  • SELECT - o usuário pode SELECT a view materializada.

  • REFRESH - o usuário pode REFRESH a view materializada. refresh são executadas usando as permissões do proprietário.

O exemplo a seguir cria uma view materializada e concede privilégios de seleção e refresh 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 visão materializada

Para revogar o acesso de uma view 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 view materializada ou de qualquer outro usuário que tenha recebido SELECT privilégios para a view materializada, ou a tabela base é descartada, o proprietário view materializada ou usuário com acesso concedido ainda é capaz de query a view materializada. No entanto, ocorre o seguinte comportamento:

  • O proprietário view materializada ou outras pessoas que perderam o acesso a uma view materializada não podem mais REFRESH essa view materializada e a view materializada se tornará obsoleta.

  • Se automatizado com um programar, o próximo programador REFRESH falha ou não é executado.

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

REVOKE SELECT ON mv1 FROM user1;

Atualização incremental de visualizações materializadas

view materializada sempre retorna os resultados semanticamente corretos para a query de definição com base na última versão Snapshot dos dados disponíveis nas tabelas base no momento da última refresh. Quando possível, os resultados são atualizados de forma incremental, mas os resultados são idênticos aos que seriam entregues pelo recálculo completo.

Embora qualquer query possa ser definida em uma view materializada, apenas um subconjunto da query é atualizado de forma incremental. Se a view materializada não puder ser refreshincrementalmente, o processo refresh usará uma refresh completa. Para determinar qual modo é usado, consulte Determinando se uma atualização incremental ou completa é usada.

Que tipo de visualizações materializadas são atualizadas de forma incremental?

Observação

view materializada que query determinados tipos de tabelas base tem um escopo maior de refresh incremental. Consulte (Avançado) Definindo view materializada que pode ser atualizada incrementalmente.

O seguinte descreve as expressões, palavras-chave e cláusulas que suportam refresh incremental.

  • A view materializada pode query apenas uma única tabela ou executar INNER JOIN e UNION ALL (ou combinações de INNER JOIN e UNION ALL) em várias tabelas.

  • A view materializada deve ter um GROUP BY na cláusula de seleção principal.

  • A cláusula view materializada SELECT pode incluir apenas as seguintes funções agregadas:

    • SOMA

    • CONTAR

  • As funções usadas na query para criar uma view materializada devem ser determinísticas. Por exemplo, usar CURRENT_TIMESTAMP não é permitido.

A seguir são descritas as limitações para refresh incremental da view materializada.

  • refresh incremental não é compatível com view materializadas que incluem:

    • Funções da janela.

    • HAVING cláusulas.

    • Subconsultas em cláusulas SELECT ou WHERE .

    • LEFT JOINs ou OUTER JOINs.

  • refresh incremental não é suportada para view materializada criada usando o recurso Delta Lake viagem do tempo .

Além disso, é necessário alterar o feed de dados nas tabelas de base da visualização materializada, exceto em determinados casos de uso avançado. Para ativar o feed de dados de alteração em uma tabela base, defina a propriedade delta.enableChangeDataFeed table usando a seguinte sintaxe:

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

(Avançado) Definição de visualizações materializadas que podem ser atualizadas de forma incremental

A seguir, descrevemos casos de uso avançados em que view materializada pode ser atualizada de forma incremental. O feed de dados de alteração não é necessário para refresh incremental nos casos de uso avançados a seguir.

Observação

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

  • As funções usadas na query para criar uma view materializada devem ser determinísticas. Por exemplo, usar CURRENT_TIMESTAMP não é permitido.

  • view materializada não pode ser criada usando o recurso Delta Lake viagem do tempo .

  • LEFT JOINs e OUTER JOINs não são suportados.

query sobre tabelas com partições

view materializada pode aproveitar as partições para evitar refresh completa. Quando uma view materializada compartilha a mesma key de partição que suas tabelas base, a view materializada pode detectar partições alteradas e modificar apenas as partições na view materializada que são necessárias para materializar o resultado. Para tabelas grandes, isso pode economizar tempo e recursos significativos.

A seguir estão as expressões, palavras-chave e cláusulas que oferecem suporte refresh incremental ao definir view materializada em tabelas com partições. Nos cenários a seguir, uma view materializada é “coparticionada” com uma tabela base se a view materializada compartilhar pelo menos uma key de partição com as tabelas base.

  • Uma view materializada particionada pode query uma única tabela.

    • A view materializada deve ser coparticionada com a tabela base.

  • Uma view materializada particionada pode fazer um UNION ALL em várias tabelas.

    • A view materializada deve ser coparticionada com todas as tabelas base.

  • Uma view materializada particionada pode usar um INNER JOIN em uma tabela de fatos com uma tabela de dimensões.

    • A view materializada deve ser coparticionada 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 view materializada será totalmente atualizada.

query sobre tabelas que são apenas anexadas

Um padrão comum para tabelas na camada de bronze é que novas linhas são inseridas apenas na tabela. view materializada detecta automaticamente tabelas base apenas anexadas e refresh incrementalmente inserindo apenas novas linhas na view materializada. Para tabelas grandes, isso pode economizar tempo e recursos significativos.

Os itens a seguir são necessários para dar suporte refresh incremental ao definir view materializada sobre as tabelas somente anexadas:

  • Uma view materializada pode query 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 anexadas.

  • Se uma linha for atualizada ou excluída na tabela base da view materializada por um sistema externo, a view materializada será totalmente atualizada.

Veja a história de atualização para uma visão materializada

Para view o status das operações REFRESH em uma view materializada, incluindo refresh atual e passada, query os logs 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 view materializada, incluindo o catálogo e o esquema.

Consulte O que são os logs de eventos do Delta Live Tables?.

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

Para otimizar o desempenho da refresh view materializada, Databricks usa um modelo de custo para selecionar a técnica usada para a refresh. A tabela a seguir descreve essas técnicas:

Técnica

refresh incremental?

Descrição

FULL_RECOMPUTE

Não

A view materializada foi totalmente recalculada

NO_OP

Não aplicável

A view materializada não foi atualizada porque nenhuma alteração na tabela base foi detectada.

ROW_BASED ou PARTITION_OVERWRITE

Sim

A view materializada foi atualizada de forma incremental usando a técnica especificada.

Para determinar a técnica usada, query os logs de eventos 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 view materializada, incluindo o catálogo e o esquema.

Consulte O que são os logs de eventos do Delta Live Tables?.

Limitações

  • Existem restrições sobre como as MVs podem ser gerenciadas e onde podem ser query:

    • view materializada do Databricks SQL só pode ser criada e atualizada no SQL warehouse e SQL warehouse serverless.

    • Um view materializado em SQL da Databricks só pode ser atualizado a partir do workspace que o criou.

    • view materializada do Databricks SQL só pode ser query no Databricks SQL warehouse, Delta Live Tables e clusters compartilhados executando o Databricks Runtime 11.3 ou superior. Você não pode query view materializada de clusters de modo de acesso de usuário único.

  • As tabelas base para a view materializada devem ser cadastradas no Unity Catalog como gerenciadas ou tabelas externas.

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

  • view materializada não oferece suporte a colunas de identidade ou key substituta.

  • Você não pode executar comandos ad hoc OPTIMIZE ou VACUUM contra view materializada.

  • Se uma view materializada usar um agregado de soma em uma coluna NULL-able e apenas NULL valores permanecerem nessa coluna, o valor agregado resultante view materializada será zero em vez de NULL.

  • Embora as referências de coluna não exijam aliases, as expressões de referência que não são de coluna exigem um alias. Por exemplo, a seguinte instrução 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.

  • Você não pode usar filtros de linha ou máscaras de coluna com Databricks SQL materializada view.

  • Os arquivos subjacentes que suportam view materializada podem incluir dados de tabelas upstream (incluindo possíveis informações de identificação pessoal) que não aparecem na definição view materializada. Esses dados são adicionados automaticamente ao armazenamento subjacente para oferecer suporte à atualização incremental da view materializada. Como os arquivos subjacentes de uma view materializada podem expor dados de tabelas upstream que não fazem parte do esquema view materializada, 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 view materializada inclua uma cláusula COUNT(DISTINCT field_a). Mesmo que a definição view materializada inclua apenas a cláusula agregada COUNT DISTINCT, os arquivos subjacentes conterão uma lista dos valores reais de field_a.