Utilizzare le viste materializzate in Databricks SQL

Questo articolo descrive come creare e usare viste materializzate in Databricks SQL per migliorare le prestazioni e ridurre il costo dei carichi di lavoro di elaborazione e analisi dei dati.

Che cosa sono le viste materializzate?

In Databricks SQL le viste materializzate sono tabelle gestite del catalogo Unity che consentono agli utenti di precompilare i risultati in base alla versione più recente dei dati nelle tabelle di origine. Le viste materializzate in Azure Databricks differiscono da altre implementazioni, perché i risultati restituiti riflettono lo stato dei dati quando la vista materializzata è stata aggiornata per l'ultima volta anziché aggiornare sempre i risultati quando viene eseguita una query sulla vista materializzata. È possibile aggiornare manualmente le viste materializzate o pianificare gli aggiornamenti.

Le viste materializzate sono potenti per i carichi di lavoro di elaborazione dei dati, ad esempio l'estrazione, la trasformazione e il caricamento (ETL). Le viste materializzate offrono un modo semplice e dichiarativo per elaborare i dati per la conformità, le correzioni, le aggregazioni o l'acquisizione generale dei dati delle modifiche (CDC). Le viste materializzate riducono i costi e migliorano la latenza delle query precalcolando le query lente e i calcoli usati di frequente. Le viste materializzate consentono anche trasformazioni facili da usare, pulendo, arricchendo e denormalizzando le tabelle di base. Le viste materializzate possono ridurre i costi e fornire un'esperienza semplificata all'utente finale perché, in alcuni casi, possono calcolare in modo incrementale le modifiche dalle tabelle di base.

Le viste materializzate sono state supportate per la prima volta in Azure Databricks con il lancio di DLT. Quando si crea una vista materializzata in un Databricks SQL warehouse, viene creata una pipeline serverless per elaborare gli aggiornamenti alla vista materializzata. È possibile monitorare lo stato delle operazioni di aggiornamento nell'interfaccia utente DLT o nelle pipeline API. Si veda Visualizzare lo stato di aggiornamento di una vista materializzata.

Requisiti

Per creare o aggiornare viste materializzate:

• È necessario usare un warehouse SQL serverless o pro abilitato per il catalogo Unity.
• Per aggiornare una vista materializzata, è necessario trovarsi nell'area di lavoro che l'ha creata.

• L'area di lavoro deve trovarsi in una regione che supporta i warehouse SQL senza server.

Interrogare la vista materializzata.

• È necessario essere il proprietario della vista materializzata o avere SELECT nella vista materializzata, insieme ai relativi elementi padre USE SCHEMA e USE CATALOG.
• È necessario usare una delle seguenti risorse di calcolo seguenti:
  • SQL warehouse
  • Interfacce DLT
  • Calcolo in modalità di accesso standard (in precedenza modalità di accesso condiviso)
  • Modalità di accesso dedicato (in precedenza modalità di accesso utente singolo) in Databricks Runtime 15.4 e versioni successive, purché l'area di lavoro sia abilitata per il calcolo serverless. Vedere controllo di accesso con granularità fine in un ambiente di calcolo dedicato (in precedenza calcolo utente singolo).
  • Solo se si è il proprietario della vista materializzata: una risorsa di calcolo in modalità di accesso dedicata che esegue Databricks Runtime tra 14.3 e 15.3.

Per informazioni su altre restrizioni sull'uso di viste materializzate, si veda Limitazioni.

Creare una vista materializzata

Le operazioni CREATE di visualizzazione materializzata di Databricks SQL utilizzano un warehouse di Databricks SQL per creare e caricare i dati nella visualizzazione materializzata. La creazione di una vista materializzata è un'operazione sincrona, il che significa che il comando CREATE MATERIALIZED VIEW si blocca fino a quando non viene creata la vista materializzata e il caricamento iniziale dei dati termina. Viene creata automaticamente una pipeline DLT serverless per ogni vista materializzata di Databricks SQL. Quando la vista materializzata viene aggiornata, la pipeline DLT elabora l'aggiornamento.

Per creare una vista materializzata, usare l'istruzione CREATE MATERIALIZED VIEW. Per inviare un'istruzione CREATE, usare l'editor SQL nell'interfaccia utente di Azure Databricks, l'interfaccia della riga di comando SQL di Databricks o l'API SQL di Databricks.

L'esempio seguente crea la vista materializzata mv1 dalla tabella di base base_table1.

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

I commenti di colonna in una tabella di base vengono propagati automaticamente alla nuova vista materializzata. Per aggiungere una pianificazione, vincoli di tabella o altre proprietà, modificare la definizione della vista materializzata. Per informazioni dettagliate sulla sintassi per la definizione di una vista materializzata, vedere CREATE MATERIALIZED VIEW.

Impostare il canale di runtime

Le viste materializzate create con sql warehouse vengono aggiornate automaticamente tramite una pipeline DLT. Le pipeline DLT usano il runtime nel canale current per impostazione predefinita. Per informazioni sul processo di rilascio, vedere note sulla versione DLT e il processo di aggiornamento della versione.

Databricks consiglia di usare il current canale per i carichi di lavoro di produzione. Le nuove funzionalità vengono rilasciate per la prima volta al preview canale. È possibile impostare una pipeline sul canale DLT di anteprima per testare le nuove funzionalità specificando preview come proprietà della tabella. È possibile specificare questa proprietà quando si crea la tabella o dopo la creazione della tabella utilizzando un'istruzione ALTER.

L'esempio di codice seguente illustra come impostare il canale per l'anteprima in un'istruzione CREATE:

CREATE OR REPLACE MATERIALIZED VIEW foo.default.bar
TBLPROPERTIES ('pipelines.channel' = 'preview') as
SELECT
  *
FROM
  range(5)

Caricare dati da sistemi esterni

Databricks consiglia di caricare dati esterni usando la Federazione Lakehouse per le origini dati supportate. Per informazioni sul caricamento di dati da origini non supportate dalla federazione lakehouse, si veda Opzioni di formato dati.

Aggiornare una vista materializzata

L'operazione REFRESH aggiorna la vista materializzata per riflettere le ultime modifiche apportate alla tabella di base. L'operazione è sincrona per impostazione predefinita, ovvero il comando si blocca fino al completamento dell'operazione di aggiornamento. Per aggiornare una vista materializzata, usare l'istruzione REFRESH MATERIALIZED VIEW. Vedere REFRESH (MATERIALIZED VIEW o STREAMING TABLE) per informazioni dettagliate sulla sintassi e sui parametri SQL per questo comando. Per altre informazioni sui tipi di viste materializzate che possono essere aggiornate in modo incrementale, vedere Aggiornamento incrementale per le viste materializzate.

Per inviare un'istruzione di aggiornamento, utilizzare l'editor SQL nell'interfaccia utente di Azure Databricks, un notebook collegato a un warehouse SQL, l’interfaccia utente della ria di comando Databricks SQL o l'API Databricks SQL.

Solo il proprietario può REFRESH la visualizzazione materializzata.

Nell'esempio seguente viene aggiornata la vista materializzata mv1:

REFRESH MATERIALIZED VIEW mv1;

Come vengono aggiornate le viste materializzate di Databricks SQL?

Le viste materializzate creano e usano automaticamente pipeline DLT serverless per elaborare le operazioni di aggiornamento. L'aggiornamento viene gestito dalla pipeline DLT e l'aggiornamento viene monitorato dal databricks SQL Warehouse usato per creare la vista materializzata. Le viste materializzate possono essere aggiornate usando una pipeline DLT eseguita in base a una pianificazione. Vedere Triggered vs. continuous pipeline mode (Modalità pipeline attivata e continua).

Per impostazione predefinita, le operazioni di aggiornamento vengono eseguite in modo sincrono. È anche possibile impostare un'operazione di aggiornamento in modo asincrono. Questa impostazione può essere impostata usando il comando refresh. Vedere REFRESH (MATERIALIZED VIEW o STREAMING TABLE) Il comportamento associato a ogni approccio è il seguente:

• Sincrono: un aggiornamento sincrono impedisce ad altre operazioni di procedere fino al completamento dell'aggiornamento. Se il risultato è necessario per il passaggio successivo, ad esempio per la sequenziazione delle operazioni di aggiornamento in strumenti di orchestrazione come Databricks Jobs, usare un aggiornamento sincrono. Per orchestrare le viste materializzate con un job, usare il tipo di operazione SQL. Vedere Panoramica dell'orchestrazione in Databricks.
• asincrona: un aggiornamento asincrono avvia un processo in background nell'elaborazione DLT quando inizia il refresh di una vista materializzata, consentendo al comando di tornare prima del completamento del caricamento dei dati. Questo tipo di aggiornamento può risparmiare sui costi perché l'operazione non mantiene necessariamente la capacità di calcolo nel magazzino in cui viene avviato il comando. Se l'aggiornamento diventa inattivo e nessun'altra attività è in esecuzione, il warehouse può arrestarsi mentre l'aggiornamento usa altre risorse di calcolo disponibili. Inoltre, gli aggiornamenti asincroni supportano l'avvio di più operazioni in parallelo.

Alcune query possono essere aggiornate in modo incrementale. Consultare Aggiornamento incrementale per le viste materializzate. Se non è possibile eseguire un aggiornamento incrementale, viene eseguito un aggiornamento completo.

Pianificare il refresh delle viste materializzate.

È possibile configurare una vista materializzata di Databricks SQL per l'aggiornamento automatico in base a una pianificazione definita. Per impostare una pianificazione, eseguire una delle seguenti operazioni:

• Configurare la pianificazione con la clausola SCHEDULE quando si crea la vista materializzata
• Aggiungere una pianificazione con l'istruzione ALTER MATERIALIZED VIEW.

Quando viene creata una pianificazione, un nuovo processo di Databricks viene configurato automaticamente per elaborare l'aggiornamento.

Per visualizzare una pianificazione, eseguire una delle seguenti operazioni:

• Eseguire l'istruzione DESCRIBE EXTENDED dall'editor SQL nell'interfaccia utente di Azure Databricks.
• Usare Catalog Explorer per visualizzare la vista materializzata. La pianificazione è elencata nella scheda Panoramica, sotto Stato aggiornamento. Vedi Che cos'è Esplora cataloghi?.

Controllare lo stato di aggiornamento di una vista materializzata.

È possibile visualizzare lo stato di un aggiornamento della vista materializzata visualizzando la pipeline che gestisce la visualizzazione materializzata nell'interfaccia utente DLT o visualizzando le informazioni di aggiornamento restituite dal comando DESCRIBE EXTENDED per la visualizzazione materializzata.

È anche possibile visualizzare la cronologia degli aggiornamenti di una vista materializzata eseguendo una query sul registro eventi DLT. Si veda Visualizzare la cronologia di aggiornamento di una vista materializzata.

Monitorare le esecuzioni usando la cronologia delle query

È possibile usare la pagina cronologia query per accedere ai dettagli delle query e ai profili di query che consentono di identificare query con prestazioni scarse e colli di bottiglia nella pipeline DLT usata per eseguire gli aggiornamenti delle tabelle di streaming. Per una panoramica del tipo di informazioni disponibili per le cronologie delle query e i profili di query, vedere Cronologia query e Profilo di query.

Tutte le istruzioni correlate alle viste materializzate vengono visualizzate nella cronologia delle query. È possibile usare il filtro a discesa "Istruzione" per selezionare qualsiasi comando ed esaminare le query correlate. Tutte le istruzioni CREATE sono seguite da un'istruzione REFRESH eseguita in modo asincrono in una pipeline DLT. Le REFRESH istruzioni includono in genere piani di query dettagliati che forniscono informazioni dettagliate sull'ottimizzazione delle prestazioni.

Per accedere alle REFRESH istruzioni nell'interfaccia utente della cronologia query, segui questi passaggi:

1. Fai clic su sulla barra laterale sinistra per aprire l'interfaccia Cronologia Query.
2. Selezionare la casella di controllo REFRESH dal filtro a discesa della dichiarazione .
3. Fare clic sul nome dell'istruzione query per visualizzare i dettagli di riepilogo, ad esempio la durata della query e le metriche aggregate.
4. Fare clic su Visualizza profilo di query per aprire il profilo di query. Per informazioni dettagliate sull'esplorazione del profilo di query, vedere Profilo di query.
5. Opzionalmente, usare i collegamenti nella sezione Origine delle query per aprire la query o la pipeline correlata.

Vedi CREATE MATERIALIZED VIEW.

Visualizzare lo stato di aggiornamento nell'interfaccia utente DLT

Per impostazione predefinita, la pipeline DLT che gestisce una vista materializzata non è visibile nell'interfaccia utente DLT. Per visualizzare la pipeline nell'interfaccia utente DLT, è necessario accedere direttamente al collegamento alla pagina dei dettagli della pipeline. Per accedere al collegamento:

• Copiare e incollare il collegamento visualizzato nella riga Aggiornamento più recente della tabella restituita dall'istruzione DESCRIBE EXTENDED .
• Nella scheda derivazione per la vista materializzata fare clic su Pipeline e quindi sul collegamento alla pipeline.

Per i comandi asincroni REFRESH inviati usando l'editor SQL nell'interfaccia utente di Azure Databricks, è possibile visualizzare lo stato di aggiornamento seguendo il collegamento illustrato nel pannello Risultati.

Fermare un aggiornamento attivo

Per arrestare un aggiornamento attivo nell'interfaccia utente DLT, nella pagina dettagli della pipeline fare clic su Arresta per arrestare l'aggiornamento della pipeline. È anche possibile arrestare l'aggiornamento con l'interfaccia della riga di comando di Databricks o l'operazione POST /api/2.0/pipelines/{pipeline_id}/stop nell'API Pipeline.

Aggiornare la definizione di una vista materializzata

Per aggiornare la definizione di una vista materializzata, è prima necessario eliminare, e poi ricreare, la vista materializzata.

Eliminare definitivamente i record da una vista materializzata con vettori di eliminazione abilitati

Per eliminare fisicamente i record dall'archiviazione sottostante per una vista materializzata con vettori di eliminazione abilitati, ad esempio per la conformità al GDPR, è necessario eseguire ulteriori passaggi per garantire che un'operazione di VACUUM venga eseguita sui dati della vista materializzata.

Di seguito vengono descritti in modo più dettagliato questi passaggi:

1. Eseguire un'istruzione REORG sulla vista materializzata, specificando il parametro APPLY (PURGE). Ad esempio, REORG TABLE <materialized-view-name> APPLY (PURGE);. Vedi REORG TABLE.
2. Attendere il passaggio del periodo di conservazione dei dati della vista materializzata. Il periodo di conservazione dei dati predefinito è di sette giorni, ma può essere configurato con la proprietà della tabella delta.deletedFileRetentionDuration. Vedere Configurare la conservazione dei dati per le query di spostamento cronologico.
3. REFRESH la vista materializzata. Consultare Aggiornare una vista materializzata. Entro 24 ore dall'operazione di REFRESH, le attività di manutenzione DLT, inclusa l'operazione di VACUUM necessaria per garantire che i record vengano eliminati definitivamente, vengono eseguiti automaticamente. Vedere Attività di manutenzione eseguite da DLT.

Eliminare una vista materializzata

Per eliminare una vista materializzata, usare l'istruzione DROP VIEW. Per inviare un'istruzione DROP, utilizzare l'editor SQL nell'interfaccia utente di Azure Databricks, l’interfaccia utente della riga di comando di Databricks SQL o l'API Databricks SQL. L'esempio seguente elimina la vista materializzata mv1:

DROP MATERIALIZED VIEW mv1;

Descrivere una vista materializzata

Per recuperare le colonne e i tipi di dati per una vista materializzata, usare l'istruzione DESCRIBE . Per recuperare le colonne, i tipi di dati e i metadati, ad esempio proprietario, posizione, ora di creazione e stato di aggiornamento per una vista materializzata, usare DESCRIBE EXTENDED. Per inviare un'istruzione DESCRIBE, utilizzare l'editor SQL nell'interfaccia utente di Azure Databricks, l’interfaccia utente della riga di comando di Databricks SQL o l'API Databricks SQL.

Modificare il proprietario di una visualizzazione materializzata

È possibile modificare il proprietario di una visualizzazione materializzata se si è un amministratore del metastore e un amministratore dell'area di lavoro. Le viste materializzate creano e usano automaticamente pipeline DLT per elaborare le modifiche. Usare la procedura seguente per modificare un proprietario delle viste materializzate:

• Nella scheda derivazione per la vista materializzata, fare clic su Pipeline e quindi sul collegamento alla pipeline.
• Fare clic su Condividi. La finestra di dialogo Impostazioni autorizzazioni viene visualizzata.
• Fare clic su x a destra del nome del proprietario corrente per rimuoverlo.
• Iniziare a digitare per filtrare l'elenco degli utenti disponibili. Fare clic sull'utente che deve essere il nuovo proprietario della pipeline.
• Fare clic su Salva per salvare le modifiche e chiudere la finestra di dialogo.

Tutte le risorse della pipeline, comprese le viste materializzate definite all'interno della pipeline, sono di proprietà del nuovo proprietario della pipeline. Tutti gli aggiornamenti futuri vengono eseguiti usando l'identità del nuovo proprietario.

Controllare l'accesso alle viste materializzate

Le viste materializzate supportano controlli di accesso avanzati per consentire la condivisione dei dati evitando di esporre dati potenzialmente privati. Il proprietario di una vista materializzata o un utente con il privilegio MANAGE può concedere i privilegi SELECT ad altri utenti. Gli utenti con accesso SELECT alla vista materializzata non devono accedere SELECT alle tabelle a cui fa riferimento la vista materializzata. Questo controllo di accesso consente la condivisione dei dati durante il controllo dell'accesso ai dati sottostanti.

Concedere privilegi a una vista materializzata

Per concedere l'accesso a una vista materializzata, utilizzare l'istruzione GRANT:

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

Il privilege_type può essere:

• SELECT - L'utente può SELECT la vista materializzata.
• REFRESH - L'utente può REFRESH la vista materializzata. Gli aggiornamenti vengono eseguiti usando le autorizzazioni del proprietario.

Nell'esempio seguente viene creata una vista materializzata e vengono concessi privilegi di selezione e aggiornamento a un utente:

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

Revocare i privilegi a una vista materializzata

Per revocare l'accesso a una vista materializzata, usare l'istruzione REVOKE:

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

Quando i privilegi SELECT su una tabella di base vengono revocati dal proprietario della vista materializzata o da qualsiasi altro utente a cui sono stati concessi privilegi MANAGE o SELECT sulla vista materializzata, oppure quando la tabella di base viene eliminata, il proprietario della vista materializzata o l'utente a cui è stato concesso l'accesso è comunque in grado di eseguire query sulla vista materializzata. In ogni caso, si ha il seguente comportamento:

• Il proprietario della vista materializzata o altri utenti che hanno perso l'accesso a una vista materializzata non possono più REFRESH quella vista materializzata, e la vista materializzata diventerà obsoleta.
• Se l’operazione è automatizzata con una pianificazione, la successiva pianificazione REFRESH ha esito negativo o non viene eseguita.

Nel seguente esempio viene revocato il privilegio SELECT a mv1:

REVOKE SELECT ON mv1 FROM user1;

Abilitare il feed di dati delle modifiche

Il feed di dati delle modifiche è necessario nelle tabelle di base delle viste materializzate, ad eccezione di alcuni casi d'uso avanzati. Per abilitare il feed di dati delle modifiche in una tabella di base, impostare la proprietà delta.enableChangeDataFeed della tabella usando la seguente sintassi:

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

Visualizzare la cronologia di aggiornamento di una vista materializzata.

Per visualizzare lo stato delle operazioni di REFRESH in una vista materializzata, inclusi gli aggiornamenti correnti e precedenti, eseguire una query sul registro eventi DLT:

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

Sostituire <fully-qualified-table-name> con il nome completo della vista materializzata, inclusi il catalogo e lo schema.

Consulta Che cos'è il registro eventi DLT?.

Ottieni informazioni dettagliate sulle viste materializzate con Catalog Explorer

È possibile utilizzare Catalog Explorer per visualizzare i dettagli relativi a una vista materializzata.

1. Fare clic sull'icona Catalogo nella barra laterale.
2. Nel riquadro di esplorazione dei cataloghi a sinistra, aprite il catalogo e selezionate lo schema in cui si trova la vista materializzata.
3. Aprire l'elemento tabelle sotto lo schema selezionato e fare clic sulla vista materializzata.

Da qui è possibile usare le schede sotto il nome della vista materializzata per visualizzare e modificare le informazioni sulla vista materializzata, tra cui:

• Aggiornare lo stato e la cronologia
• Schema della tabella
• Dati di esempio (richiede un calcolo attivo)
• Autorizzazioni
• Dipendenze, incluse tabelle e pipeline da cui dipende questa vista materializzata
• Informazioni dettagliate sull'utilizzo
• Monitoraggi che hai creato per questa vista materializzata

Limitazioni

• Per i requisiti dell’ambiente di calcolo e dell'area di lavoro, si veda Requisiti.
• Le viste materializzate non supportano colonne Identity o chiavi sostitutive.
• Se in una vista materializzata viene utilizzata un'aggregazione di somma su una colonna NULL-abile e rimangono solo i valori NULL in quella colonna, il valore di aggregazione risultante delle viste materializzate è zero anziché NULL.
• Non è possibile leggere un feed di dati delle modifiche da una vista materializzata.
• Le interrogazioni sui viaggi nel tempo non sono supportate nelle viste materializzate.
• I file sottostanti che supportano le viste materializzate potrebbero includere dati provenienti da tabelle a monte ( comprendenti possibili informazioni di identificazione personale) che non compaiono nella definizione della vista materializzata. Questi dati vengono aggiunti automaticamente all'archivio sottostante per supportare l'aggiornamento incrementale delle viste materializzate. Poiché i file sottostanti di una vista materializzata potrebbero rischiare di esporre dati da tabelle upstream non incluse nello schema della vista materializzata, Databricks consiglia di non condividere l'archiviazione sottostante con consumer downstream non attendibili. Si supponga, ad esempio, che la definizione di una vista materializzata includa una clausola COUNT(DISTINCT field_a). Anche se la definizione di vista materializzata include solo la clausola COUNT DISTINCT di aggregazione , i file sottostanti conterranno un elenco dei valori effettivi di field_a.