Partilhar via

CREATE MATERIALIZED VIEW

Aplica-se a:Marcado como Sim Databricks SQL

Uma vista materializada é uma vista onde os resultados pré-calculados estão disponíveis para consulta e podem ser atualizados para refletir as alterações na entrada. Cada vez que uma exibição materializada é atualizada, os resultados da consulta são recalculados para refletir as alterações nos conjuntos de dados upstream. Todas as visualizações materializadas são suportadas por um pipeline de ETL. Você pode atualizar exibições materializadas manualmente ou em uma agenda.

Para saber mais sobre como executar uma atualização manual, consulte REFRESH (MATERIALIZED VIEW ou STREAMING TABLE).

Para saber mais sobre como agendar uma atualização, consulte Exemplos ou ALTER MATERIALIZED VIEW.

As visões materializadas só podem ser criadas usando um armazém SQL Pro ou Serverless, ou dentro de um pipeline.

Nota

As operações de criação e atualização em vistas materializadas e tabelas de streaming são alimentadas por pipelines declarativos Lakeflow Spark sem servidor. Você pode usar o Gerenciador de Catálogos para exibir detalhes sobre os pipelines de backup na interface do usuário. Consulte O que é o Catalog Explorer?.

Sintaxe

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
      [ , table_constraint ] [...])

   column_properties
      { NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]

view_clauses
  { PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    COMMENT view_comment |
    DEFAULT COLLATION UTF8_BINARY |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    schedule |
    WITH { ROW FILTER clause } } [...]

schedule
  { SCHEDULE [ REFRESH ] schedule_clause |
    TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }

schedule_clause
  { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
    CRON cron_string [ AT TIME ZONE timezone_id ] }

Parâmetros

  • SUBSTITUIR

    Se estiver especificado, substitui a vista e o seu conteúdo, se já existirem.

  • SE NÃO EXISTIR

    Cria o modo de exibição se ele não existir. Se já existir uma vista com este nome, a CREATE MATERIALIZED VIEW instrução é ignorada.

    Você pode especificar no máximo um dos IF NOT EXISTS ou OR REPLACE.

  • view_name

    O nome da vista recém-criada. O nome do modo de exibição totalmente qualificado deve ser exclusivo.

  • column_list

    Opcionalmente, rotula as colunas no resultado da consulta da visualização. Se fornecer uma lista de colunas, o número de alias das colunas deve corresponder ao número de expressões na consulta. Se nenhuma lista de colunas for especificada, os aliases serão derivados do corpo da vista.

    • nome_da_coluna

      Os nomes das colunas devem ser exclusivos e corresponder às colunas de saída da consulta.

    • tipo_de_coluna

      Especifica o tipo de dados da coluna. Nem todos os tipos de dados suportados pelo Azure Databricks são suportados por vistas materializadas.

    • column_comment

      Um STRING literal opcional descrevendo a coluna. Esta opção deve ser especificada juntamente com column_type. Se o tipo de coluna não for especificado, o comentário da coluna é ignorado.

    • column_constraint

      Adiciona uma chave primária informativa ou uma restrição de chave estrangeira informativa à coluna numa visão materializada. Se o tipo de coluna não for especificado, a restrição de coluna será ignorada.

    • Cláusula MASK

      Adiciona uma função de máscara de coluna para anonimizar dados confidenciais. Todas as consultas subsequentes dessa coluna recebem o resultado da avaliação dessa função sobre a coluna no lugar do valor original da coluna. Isso pode ser útil para fins de controle de acesso refinado, onde a função pode inspecionar a identidade ou as associações de grupo do usuário que invoca para determinar se o valor deve ser redigido. Se o tipo de coluna não for especificado, a máscara de coluna será ignorada.

  • restrição_de_tabela

    Adiciona uma chave primária informacional ou uma restrição de chave estrangeira informativa à tabela numa vista materializada. Se o tipo de coluna não for especificado, a restrição de tabela será ignorada.

  • view_clauses

    Opcionalmente, especifique particionamento, comentários, propriedades definidas pelo usuário e uma agenda de atualização para a nova exibição materializada. Cada subcláusula só pode ser especificada uma vez.

    • PARTICIONADO POR

      Uma lista opcional de colunas da tabela para particionar a tabela por.

      Nota

      O agrupamento de líquidos fornece uma solução flexível e otimizada para agrupamento. Considere usar CLUSTER BY em vez de PARTITIONED BY para visualizações materializadas.

    • CLUSTER BY

      Uma cláusula opcional para agrupar por um subconjunto de colunas. Use o clustering líquido automático com CLUSTER BY AUTOo , e o Databricks escolhe chaves de clustering de forma inteligente para otimizar o desempenho da consulta. Veja Utilizar clustering líquido para tabelas.

      O agrupamento de líquidos não pode ser combinado com PARTITIONED BY.

    • VER COMENTÁRIO view_comment

      Um STRING literal para descrever a tabela.

    • AGRUPAMENTO PADRÃO UTF8_BINARY

      Aplica-se a:check marked yes Databricks SQL check marked yes Databricks Runtime 17.1 e superior

      Força o agrupamento padrão da exibição materializada para UTF8_BINARY. Esta cláusula é obrigatória se o esquema no qual a vista é criada tiver um agrupamento padrão diferente de UTF8_BINARY. A ordenação padrão da vista materializada é utilizada como a ordenação padrão dentro do corpo da vista.

    • TBLPROPERTIES

      Opcionalmente, define uma ou mais propriedades definidas pelo usuário.

      Use essa configuração para especificar o canal de tempo de execução Lakeflow Spark Declarative Pipelines usado para executar essa instrução. Defina o valor da propriedade pipelines.channel como "PREVIEW" ou "CURRENT". O valor predefinido é "CURRENT". Para obter mais informações sobre os canais do Lakeflow Spark Declarative Pipelines, consulte Lakeflow Spark Declarative Pipelines runtime channels.

    • Horário

      O cronograma pode ser uma SCHEDULE declaração ou uma TRIGGER declaração.

      • HORÁRIO [ REFRESH ] cláusula_de_horário

        • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

          Para agendar uma atualização que ocorre periodicamente, use a sintaxe EVERY. Se a sintaxe EVERY for especificada, a tabela de streaming ou a vista materializada será atualizada periodicamente no intervalo especificado com base no valor fornecido, como HOUR, HOURS, DAY, DAYS, WEEKou WEEKS. A tabela a seguir lista os valores inteiros aceitos para number.

          Unidade de tempo Valor inteiro
          HOUR or HOURS <1 = H <= 72
          DAY or DAYS <1 = D <= 31
          WEEK or WEEKS <= 1 W <= 8

          Nota

          As formas singular e plural da unidade de tempo incluída são semanticamente equivalentes.

        • CRON cron_string [ AT TIME ZONE timezone_id ]

          Para agendar uma atualização de dados usando um valor de cron quartz. São aceites time_zone_values válidos. AT TIME ZONE LOCAL não é suportado.

          Se AT TIME ZONE estiver ausente, o fuso horário da sessão será usado. Se AT TIME ZONE estiver ausente e o fuso horário da sessão não estiver definido, um erro será lançado. SCHEDULE é semanticamente equivalente a SCHEDULE REFRESH.

      • GATILHO EM UPDATE [ NO MÁXIMO A CADA trigger_interval ]

        Importante

        O TRIGGER ON UPDATE recurso está em versão Beta.

        Opcionalmente, defina a tabela para atualizar quando uma fonte de dados upstream for atualizada, no máximo uma vez a cada minuto. Defina um valor para AT MOST EVERY exigir pelo menos um tempo mínimo entre as atualizações.

        As fontes de dados de origem devem ser tabelas Delta externas ou geridas (incluindo vistas materializadas ou tabelas de streaming) ou vistas geridas cujas dependências são limitadas a tipos de tabela suportados.

        Habilitar eventos de arquivo pode tornar os gatilhos mais eficientes e aumenta alguns dos limites nas atualizações de gatilho.

        A trigger_interval é uma instrução INTERVAL que é de pelo menos 1 minuto.

        TRIGGER ON UPDATE tem as seguintes limitações:

        • Não mais do que 10 fontes de dados upstream por visualização materializada ao usar TRIGGER ON UPDATE.
        • Máximo de 1000 tabelas de streaming ou visualizações materializadas podem ser especificadas com TRIGGER ON UPDATE.
        • O AT MOST EVERY padrão da cláusula é de 1 minuto e não pode ser inferior a 1 minuto.

    • COM ROW FILTER cláusula

      Adiciona uma função de filtro de linha à tabela. Todas as consultas subsequentes dessa tabela recebem um subconjunto das linhas para as quais a função é avaliada como booleano TRUE. Isso pode ser útil para fins de controle de acesso refinado, onde a função pode inspecionar a identidade ou as associações de grupo do usuário que invoca para determinar se determinadas linhas devem ser filtradas.

  • AS consulta

    Uma consulta que constrói a vista a partir de tabelas base ou outras vistas.

Permissões obrigatórias

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

  • SELECT privilégio sobre as tabelas base referenciadas pela VM.
  • o privilégio USE CATALOG no catálogo pai e o privilégio USE SCHEMA no esquema pai.
  • CREATE MATERIALIZED VIEW privilégio no schema para o MV.

Para que um usuário possa atualizar a MV, eles precisam:

  • o privilégio USE CATALOG no catálogo pai e o privilégio USE SCHEMA no esquema pai.
  • Propriedade da MV ou REFRESH privilégio sobre a MV.
  • O proprietário do MV deve ter o privilégio de SELECT sobre as tabelas base referenciadas pelo MV.

Para que um usuário possa consultar a MV, eles precisam:

  • o privilégio USE CATALOG no catálogo pai e o privilégio USE SCHEMA no esquema pai.
  • SELECT privilégio sobre a visão materializada.

Filtros de linha e máscaras de coluna

Os filtros de linha permitem especificar uma função que se aplica como um filtro sempre que uma verificação de tabela busca linhas. Esses filtros garantem que as consultas subsequentes retornem apenas linhas para as quais o predicado do filtro seja avaliado como true.

As máscaras de coluna permitem mascarar os valores de uma coluna sempre que uma varredura de tabela busca linhas. Todas as consultas futuras envolvendo essa coluna receberão o resultado da avaliação da função sobre a coluna, substituindo o valor original da coluna.

Para obter mais informações sobre como usar filtros de linha e máscaras de coluna, consulte Filtros de linha e máscaras de coluna.

Gerenciando filtros de linha e máscaras de coluna

Filtros de linha e máscaras de coluna em exibições materializadas devem ser adicionados por meio da instrução CREATE.

Comportamento

  • Atualizar como Definidor: Quando a instrução REFRESH MATERIALIZED VIEW atualiza uma vista materializada, as funções de filtro de linha são executadas com os direitos do definidor (como o proprietário da tabela). Isso significa que a atualização da tabela usa o contexto de segurança do usuário que criou a exibição materializada.
  • Consulta: Embora a maioria dos filtros seja executada com os direitos do definidor, as funções que verificam o contexto do usuário (como CURRENT_USER e IS_MEMBER) são exceções. Essas funções são executadas na qualidade de invocador. Essa abordagem impõe segurança de dados específicos do usuário e controles de acesso com base no contexto do usuário atual.
  • Ao criar exibições materializadas em tabelas de origem que contêm filtros de linha e máscaras de coluna, a atualização da exibição materializada é sempre uma atualização completa. Uma atualização completa reprocessa todos os dados disponíveis na fonte com as definições mais recentes. Isso garante que as políticas de segurança nas tabelas de origem estejam a ser avaliadas e aplicadas com os dados e definições mais atualizados.

Observabilidade

Utilize DESCRIBE EXTENDED, INFORMATION_SCHEMAou o Explorador de Catálogo para examinar os filtros de linha e as máscaras de coluna existentes que se aplicam a uma determinada visualização materializada. Essa funcionalidade permite que os usuários auditem e revisem o acesso a dados e as medidas de proteção em visualizações materializadas.

Limitações

  • Quando uma vista materializada com uma agregação sum sobre uma coluna passível de NULL tem o último valor não-NULL removido dessa coluna - e, assim, apenas os valores NULL permanecem nessa coluna - o valor agregado resultante da vista materializada retorna zero em vez de NULL.
  • A referência de coluna não requer um alias. Expressões de referência que não sejam de coluna exigem um alias, como no exemplo a seguir:
    • Permitido: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
    • Não permitido: SELECT col1, SUM(col2) FROM t GROUP BY col1
  • NOT NULL deve ser especificado manualmente junto com PRIMARY KEY para ser uma instrução válida.
  • As visualizações materializadas não suportam colunas de identidade ou chaves substitutas.
  • As vistas materializadas não suportam OPTIMIZE e VACUUM comandos. A manutenção acontece automaticamente.
  • As visualizações materializadas não suportam expectativas para definir restrições de qualidade de dados.

Exemplos

-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed whenever the
-- upstream data is updated
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  TRIGGER ON UPDATE
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed every day.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE EVERY 1 DAY
  AS SELECT date AS date, sum(sales) AS sumOfSales
       FROM table1
       GROUP BY date;

-- Sets the runtime channel to "PREVIEW"
> CREATE MATERIALIZED VIEW mv_preview
  TBLPROPERTIES(pipelines.channel = "PREVIEW")
  AS SELECT * FROM RANGE(10)

-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id int NOT NULL,
    full_name string,
    movie_title string,
    CONSTRAINT movie_pk PRIMARY KEY(member_id)
  )
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
  PARTITIONED BY (member_id)
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;
  • Last updated on