CREATE MATERIALIZED VIEW

Si applica a: Databricks SQL

Una vista materializzata è una vista in cui i risultati precompilati sono disponibili per la query e possono essere aggiornati in modo da riflettere le modifiche nell'input. Ogni volta che viene aggiornata una vista materializzata, i risultati delle query vengono ricalcolati per riflettere le modifiche nei set di dati upstream. Tutte le viste materializzate sono supportate da una pipeline DLT. È possibile aggiornare le viste materializzate manualmente o su una pianificazione.

Per altre informazioni su come eseguire un aggiornamento manuale, vedere REFRESH (MATERIALIZED VIEW o STREAMING TABLE).

Per altre informazioni su come pianificare un aggiornamento, vedere Esempi o ALTER MATERIALIZED VIEW.

Nota

Le operazioni di creazione e aggiornamento su viste materializzate e tabelle di streaming sono basate su una pipeline di tabelle Delta Live serverless. È possibile usare Esplora cataloghi per visualizzare i dettagli sulle pipeline di supporto nell'interfaccia utente. Si veda Che cos'è Esplora Catalog?.

Sintassi

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  [schedule_clause]
  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 [, ...]) |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    WITH { ROW FILTER clause } } [...]

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

Parametri

• REPLACE

  Se specificato, sostituisce la vista e il relativo contenuto, se già esistente.

• SE NON ESISTE

  Creare la vista se è inesistente. Se esiste già una vista con questo nome, l'istruzione CREATE MATERIALIZED VIEW viene ignorata.

  È possibile specificare al massimo uno di IF NOT EXISTS o OR REPLACE.

• view_name

  Nome della vista appena creata. Il nome completo della vista deve essere univoco.

• column_list

  Facoltativamente, etichetta le colonne nel risultato della query della vista. Se si specifica un elenco di colonne, il numero di alias di colonna deve corrispondere al numero di espressioni nella query. Se non viene specificato alcun elenco di colonne, gli alias vengono derivati dal corpo della vista.

  • column_name

    I nomi delle colonne devono essere univoci ed eseguire il mapping alle colonne di output della query.

  • column_type

    Specifica il tipo di dati della colonna. Non tutti i tipi di dati supportati da Azure Databricks sono supportati dalle viste materializzate.

  • column_comment

    Valore letterale facoltativo STRING che descrive il nome della colonna. Questa opzione deve essere specificata insieme a column_type. Se il tipo di colonna non viene specificato, il commento della colonna viene ignorato.

  • column_constraint

    Aggiunge una chiave primaria informativa o un vincolo di chiave esterna informativa alla colonna in una vista materializzata. Se il tipo di colonna non viene specificato, il vincolo di colonna viene ignorato.

  • Clausola MASK

    Importante

    Questa funzionalità è disponibile in anteprima pubblica.

    Aggiunge una funzione maschera di colonna per rendere anonimi i dati sensibili. Tutte le query successive da tale colonna ricevono il risultato della valutazione di tale funzione sulla colonna al posto del valore originale della colonna. Ciò può essere utile per scopi di controllo di accesso con granularità fine, in cui la funzione può esaminare l'identità o le appartenenze a gruppi dell'utente che richiama per decidere se revisionare il valore. Se il tipo di colonna non viene specificato, la maschera di colonna viene ignorata.

• table_constraint

  Aggiunge una chiave primaria informativa o un vincolo di chiave esterna informativa alla tabella in una vista materializzata. Se il tipo di colonna non viene specificato, il vincolo di tabella viene ignorato.

• view_clauses

  Facoltativamente, specifica partizionamento, commenti, proprietà definite dall'utente e una pianificazione di aggiornamento per la nuova vista materializzata. Ogni clausola secondaria può essere specificata una sola volta.

  • PARTIZIONATO DA

    Elenco facoltativo di colonne della tabella per partizionare la tabella.

  • COMMENT view_comment

    Valore STRING letterale per descrivere la tabella.

  • TBLPROPERTIES

    Facoltativamente, imposta una o più proprietà definite dall'utente.

    Usare questa impostazione per specificare il canale di runtime delta live tables usato per eseguire questa istruzione. Impostare il valore della pipelines.channel proprietà su "PREVIEW" o "CURRENT". Il valore predefinito è "CURRENT". Per altre informazioni sui canali di tabelle live Delta, vedere Canali di runtime di Tabelle live Delta.

  • SCHEDULE [ REFRESH ] schedule_clause

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

      Importante

      Questa funzionalità è disponibile in anteprima pubblica.

      Per pianificare un aggiornamento che si verifica periodicamente, usare la EVERY sintassi. Se EVERY si specifica la sintassi, la tabella di streaming o la vista materializzata viene aggiornata periodicamente all'intervallo specificato in base al valore specificato, ad esempio HOUR, HOURSDAY, DAYS, , WEEKo WEEKS. Nella tabella seguente sono elencati i valori integer accettati per number.

      Time unit	Valore intero
      HOUR or HOURS	1 <= H <= 72
      DAY or DAYS	1 <= D <= 31
      WEEK or WEEKS	1 <= W <= 8

      Nota

      Le forme singolari e plurali dell'unità temporale inclusa sono semanticamente equivalenti.

    • CRON cron_string [ AT TIME ZONE timezone_id ]

      Per pianificare un aggiornamento utilizzando un valore cron di quarzi. Vengono accettati time_zone_values validi. AT TIME ZONE LOCAL non è supportata.

      Se AT TIME ZONE è assente, viene usato il fuso orario della sessione. Se AT TIME ZONE è assente e il fuso orario della sessione non è impostato, viene generato un errore. SCHEDULE è semanticamente equivalente a SCHEDULE REFRESH.

  • Con clausola ROW FILTER

    Importante

    Questa funzionalità è disponibile in anteprima pubblica.

    Aggiunge una funzione di filtro di riga alla tabella. Tutte le query successive da tale tabella ricevono un subset delle righe per cui la funzione restituisce TRUE booleano. Ciò può essere utile per scopi di controllo di accesso con granularità fine, in cui la funzione può esaminare l'identità o le appartenenze ai gruppi dell'utente chiamante per decidere se filtrare determinate righe.

• Query AS

  Query che costruisce la vista da tabelle di base o da altre viste.

Autorizzazioni necessarie

L'utente che crea una vista materializzata (VM) è il proprietario della visualizzazione materializzata e deve disporre delle autorizzazioni seguenti:

• Privilegio SELECT sulle tabelle di base a cui fa riferimento la VM.
• Privilegio USE CATALOG per il catalogo padre e il privilegio USE SCHEMA per lo schema padre.
• Privilegio CREATE MATERIALIZED VIEW sullo schema per la VM.

Affinché un utente sia in grado di aggiornare la VM, è necessario:

• Privilegio USE CATALOG per il catalogo padre e il privilegio USE SCHEMA per lo schema padre.
• Proprietà della VM o privilegio REFRESH sulla VM.
• Il proprietario della VM deve avere il privilegio SELECT sulle tabelle di base a cui fa riferimento la VM.

Affinché un utente sia in grado di eseguire una query sulla VM, è necessario:

• Privilegio USE CATALOG per il catalogo padre e il privilegio USE SCHEMA per lo schema padre.
• Privilegio SELECT sulla vista materializzata.

Filtri di riga e maschere di colonna

Importante

Questa funzionalità è disponibile in anteprima pubblica.

I filtri di riga consentono di specificare una funzione che viene applicata come filtro ogni volta che un'analisi di tabella recupera righe. Questi filtri assicurano che le query successive restituiscano solo righe per le quali il predicato di filtro restituisce TRUE.

Le maschere di colonna consentono di mascherare i valori di una colonna ogni volta che un'analisi di tabella recupera le righe. Tutte le query future che coinvolgono tale colonna riceveranno il risultato della valutazione della funzione sulla colonna, sostituendo il valore originale della colonna.

Per altre informazioni su come usare filtri di riga e maschere di colonna, vedere Filtrare i dati delle tabelle sensibili usando filtri di riga e maschere di colonne.

Gestire filtri di riga e maschere di colonna

I filtri di riga e le maschere di colonna nelle viste materializzate devono essere aggiunti tramite l'istruzione CREATE.

Comportamento

• Aggiornare come Definer: quando l’istruzione REFRESH MATERIALIZED VIEW aggiorna una vista materializzata, le funzioni di filtro di riga vengono eseguite con i diritti del definitore (in quanto proprietario della tabella). Ciò significa che l'aggiornamento della tabella usa il contesto di sicurezza dell'utente che ha creato la vista materializzata.
• Query: mentre la maggior parte dei filtri viene eseguita con i diritti del definer, le funzioni che controllano il contesto utente (ad esempio CURRENT_USER e IS_MEMBER) costituiscono un’eccezione. Queste funzioni vengono eseguite come invoker. Questo approccio applica controlli di accesso e sicurezza dei dati specifici dell'utente in base al contesto dell'utente corrente.
• Quando si creano viste materializzate sulle tabelle di origine che contengono filtri di riga e maschere di colonna, l'aggiornamento della vista materializzata comporta sempre un aggiornamento completo. Un aggiornamento completo rielabora tutti i dati disponibili nell'origine con le definizioni più recenti. Ciò garantisce che i criteri di sicurezza nelle tabelle di origine vengano valutati e applicati con i dati e le definizioni più aggiornati.

Osservabilità

Usare DESCRIBE EXTENDED, INFORMATION_SCHEMA o Esplora cataloghi per esaminare i filtri di riga e le maschere di colonna esistenti applicabili a una determinata vista materializzata. Questa funzionalità consente agli utenti di controllare ed esaminare le misure di accesso e protezione dei dati nelle viste materializzate.

Limiti

• Quando a una vista materializzata con un'aggregazione sum su una colonna in grado di valori NULL viene rimosso l'ultimo valore non NULL da tale colonna (e quindi solo i valori NULL rimangono in tale colonna), il valore di aggregazione risultante della vista materializzata restituisce zero anziché NULL.
• Il riferimento a colonne non richiede un alias. Le espressioni di riferimento non di colonna richiedono un alias, come nell'esempio seguente:
  • Consentito: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
  • Non consentito: SELECT col1, SUM(col2) FROM t GROUP BY col1
• NOT NULL deve essere specificato manualmente insieme a PRIMARY KEY per essere un'istruzione valida.
• Le viste materializzate non supportano colonne Identity o chiavi sostitutive.
• Le viste materializzate non supportano i comandi OPTIMIZE e VACUUM. La manutenzione avviene automaticamente.
• Le viste materializzate non supportano le aspettative per definire i vincoli di qualità dei dati.

Esempi

-- 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 daily at midnight.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE CRON '0 0 0 * * ? *'
  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;

Articoli correlati

• VISTA MATERIALIZZATA ALTER

• clausole di vincolo

• DROP VIEW

• SHOW CREATE TABLE

• Filtrare i dati delle tabelle sensibili usando filtri di riga e maschere di colonna

• DESCRIBE TABLE

• REFRESH