Utilizzare le viste materializzate

Questo documento fornisce informazioni aggiuntive sulle visualizzazioni materializzate e su come utilizzarle. Prima di leggere questo documento, consulta la sezione Introduzione alle viste materializzate e Creare viste materializzate.

Esegui query sulle viste materializzate

Puoi eseguire query direttamente sulle viste materializzate, nello stesso modo in cui esegui query su una tabella normale o su una vista standard. Le query sulle viste materializzate sono sempre coerenti con le query sulle tabelle di base della vista, anche se queste tabelle sono cambiate dall'ultimo aggiornamento della vista materializzata. L'esecuzione di query non attiva automaticamente un aggiornamento materializzato.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per eseguire query su una vista materializzata, chiedi all'amministratore di concederti il ruolo IAM Visualizzatore dati BigQuery (roles/bigquery.dataViewer) per la tabella di base della vista materializzata e per la vista materializzata stessa. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per eseguire query su una vista materializzata. Per visualizzare le autorizzazioni esatte richieste, espandi la sezione Autorizzazioni richieste:

Autorizzazioni obbligatorie

Per eseguire query su una vista materializzata sono necessarie le seguenti autorizzazioni:

  • bigquery.tables.get
  • bigquery.tables.getData

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Queste autorizzazioni sono necessarie per le query per poter usufruire del tuning intelligente.

Per ulteriori informazioni sui ruoli IAM in BigQuery, consulta Introduzione a IAM.

Aggiornamenti incrementali

BigQuery combina i dati della vista memorizzata nella cache con i nuovi dati per fornire risultati di query coerenti continuando a utilizzare la vista materializzata. Per le visualizzazioni materializzate a una sola tabella, questo è possibile se la tabella di base non è stata modificata dall'ultimo aggiornamento o se sono stati aggiunti solo nuovi dati. Per le visualizzazioni JOIN, solo le tabelle sul lato sinistro del JOIN possono avere dati aggiunti. Se una delle tavole sul lato destro di un JOIN è stata modificata, la visualizzazione non può essere aggiornata in modo incrementale.

Se la tabella di base ha subito aggiornamenti o eliminazioni dall'ultimo aggiornamento o se le tabelle di base della vista materializzata sul lato destro di JOIN sono cambiate, BigQuery torna automaticamente alla query originale. Per maggiori informazioni su join e viste materializzate, consulta la sezione Join. Di seguito sono riportati esempi di azioni della console Google Cloud, dello strumento a riga di comando bq e dell'API che possono causare un aggiornamento o un'eliminazione:

  • Istruzioni UPDATE, MERGE o DELETE del linguaggio di manipolazione dei dati (DML)
  • Troncamento
  • Scadenza partizione

Le seguenti operazioni sui metadati impediscono inoltre l'aggiornamento incrementale di una vista materializzata:

  • Modificare la scadenza della partizione
  • Aggiornamento o eliminazione di una colonna

Se una vista materializzata non può essere aggiornata in modo incrementale, i relativi dati memorizzati nella cache non vengono utilizzati dalle query finché la vista non viene aggiornata automaticamente o manualmente. Per dettagli sul motivo per cui un job non ha utilizzato i dati delle vista materializzata, consulta Informazioni sul motivo per cui le visualizzazioni materializzate sono state rifiutate. Inoltre, le viste materializzate non possono essere aggiornate in modo incrementale se la tabella di base ha accumulato modifiche non elaborate per un periodo di tempo maggiore dell'intervallo di viaggio nel tempo della tabella.

Allineamento delle partizioni

Se una vista materializzata è partizionata, BigQuery assicura che le sue partizioni siano allineate alle partizioni della colonna di partizione della tabella di base. Allineata significa che i dati di una determinata partizione della tabella di base contribuiscono alla stessa partizione della vista materializzata. Ad esempio, una riga della partizione 20220101 della tabella di base contribuirebbe solo alla partizione 20220101 della vista materializzata.

Quando una vista materializzata viene suddivisa, il comportamento descritto in Aggiornamenti incrementali si verifica per ogni singola partizione. Ad esempio, se i dati vengono eliminati in una partizione della tabella di base, BigQuery può comunque utilizzare le altre partizioni della vista materializzata.

Le viste con aggregazione con join interni possono essere allineate solo a una delle relative tavole di base. Se una delle tabelle di base non allineate cambia, la modifica interessa l'intera visualizzazione.

Ottimizzazione intelligente

BigQuery riscriverà automaticamente le query per utilizzare le viste materializzate, se possibile. La riscrittura automatica migliora le prestazioni e il costo delle query e non modifica i risultati delle query. L'esecuzione di query non attiva automaticamente un aggiornamento materializzato. Affinché una query venga riscritta, la vista materializzata deve soddisfare le seguenti condizioni:

  • Appartenere allo stesso set di dati di una delle relative tabelle di base.
  • Utilizza lo stesso insieme di tabelle di base della query.
  • Includi tutte le colonne lette.
  • Includi tutte le righe lette.

La regolazione intelligente non è supportata per quanto segue:

Esempi di ottimizzazione intelligente

Considera l'esempio seguente di query sulla vista materializzata:

SELECT
  store_id,
  CAST(sold_datetime AS DATE) AS sold_date
  SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
  CAST(sold_datetime AS DATE) >= '2021-01-01' AND
  promo_id IS NOT NULL
GROUP BY 1, 2

I seguenti esempi mostrano le query e il motivo per cui queste vengono o meno riscritte automaticamente utilizzando questa visualizzazione:

Query Riscrivere? Motivo
SELECT
SUM(net_paid) AS sum_paid,
SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL 		No La vista deve includere tutte le colonne lette. La visualizzazione non include 'SOMMA(net_paid)".
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL 		Sì
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL AND
customer_id = 12345 		No La vista deve includere tutte le colonne lette. La visualizzazione non include "customer".
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
sold_datetime= '2021-01-01' AND
promo_id IS NOT NULL
 No La vista deve includere tutte le colonne lette. "sold_datetime" non è un output (ma lo è "CAST(sold_datetime AS DATE)").
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id IS NOT NULL AND
store_id = 12345		 Sì
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2021-01-01' AND
promo_id = 12345 		No La visualizzazione deve includere tutte le righe lette. "promo_id" non è un output, pertanto il filtro più restrittivo non può essere applicato alla visualizzazione.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE CAST(sold_datetime AS DATE) >= '2020-01-01'
 No La visualizzazione deve includere tutte le righe lette. Il filtro della visualizzazione per le date nel 2021 e successive, ma la query legge le date del 2020.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE
CAST(sold_datetime AS DATE) >= '2022-01-01' AND
promo_id IS NOT NULL 		Sì

Capire se una query è stata riscritta

Per capire se una query è stata riscritta tramite la regolazione intelligente per utilizzare una vista materializzata, controlla il piano di query. Se la query è stata riscritta, il piano di query contiene un passaggio READ my_materialized_view, dove my_materialized_view è il nome della vista materializzata utilizzata. Per comprendere il motivo per cui una query non ha utilizzato una vista materializzata, consulta Informazioni sul motivo per cui le viste materializzate sono state rifiutate.

Scopri perché le viste materializzate sono state rifiutate

Se hai disattivato l'aggiornamento automatico per la vista materializzata e la tabella contiene modifiche non elaborate, la query potrebbe essere più rapida per diversi giorni, ma poi inizierà a ripristinare la query originale, con una conseguente riduzione della velocità di elaborazione. Per usufruire delle viste materializzate, attiva l'aggiornamento automatico o esegui regolarmente l'aggiornamento manuale e monitora i job di aggiornamento delle vista materializzata per verificare che vengano eseguiti correttamente.

I passaggi per capire perché una vista materializzata è stata rifiutata dipendono dal tipo di query utilizzata:

  • Query diretta della vista materializzata
  • Query indiretta in cui l'ottimizzazione intelligente potrebbe scegliere di utilizzare la vista materializzata

Le sezioni seguenti descrivono la procedura per aiutarti a capire perché una vista tabella intermedia è stata rifiutata.

Query diretta delle viste materializzate

Le query dirette sulle viste materializzate potrebbero non utilizzare i dati memorizzati nella cache in determinate circostanze. I seguenti passaggi possono aiutarti a capire perché i dati della vista materializzata non sono stati utilizzati:

  1. Segui i passaggi descritti in Monitorare l'utilizzo delle viste materializzate e individua la vista materializzata di destinazione nel campo materialized_view_statistics per la query.
  2. Se chosen è presente nelle statistiche e il relativo valore è TRUE, la query utilizza la vista materializzata.
  3. Esamina il campo rejected_reason per trovare i passaggi successivi. Nella maggior parte dei casi, puoi aggiornare manualmente la vista materializzata o attendere il successivo aggiornamento automatico.

Query con ottimizzazione intelligente

  1. Segui i passaggi descritti in Monitorare l'utilizzo delle viste materializzate e individua la vista materializzata di destinazione in materialized_view_statistics per la query.
  2. Consulta la sezione rejected_reason per scoprire i passaggi successivi. Ad esempio, se il valore rejected_reason è COST, la regolazione intelligente ha identificato origini dati più efficienti per costo e rendimento.
  3. Se la vista materializzata non è presente, prova a eseguire una query diretta sulla vista materializzata e segui i passaggi descritti in Query diretta sulle viste materializzate.
  4. Se la query diretta non utilizza la vista materializzata, la forma della vista materializzata non corrisponde alla query. Per saperne di più sull'ottimizzazione intelligente e su come le query vengono riscritte utilizzando le viste materializzate, consulta Esempi di ottimizzazione intelligente.

Domande frequenti

Quando devo utilizzare le query pianificate rispetto alle viste materializzate?

Le query pianificate sono un modo pratico per eseguire periodicamente calcoli arbitrariamente complessi. Ogni volta che viene eseguita, la query viene eseguita completamente, senza alcun vantaggio dai risultati precedenti, e paghi l'intero costo computazionale della query. Le query pianificate sono ideali quando non hai bisogno dei dati più aggiornati e hai una tolleranza elevata per l'obsolescenza dei dati.

Le viste materializzate sono ideali quando devi eseguire query sui dati più recenti con latenza e costi minimi riutilizzando il risultato calcolato in precedenza. Puoi utilizzare le viste materializzate come pseudo-indici, accelerando le query alla tabella di base senza aggiornare i flussi di lavoro esistenti. L'--max_staleness opzione ti consente di definire un'obsolescenza accettabile per le viste materializzate, offrendo prestazioni costantemente elevate con costi controllati durante l'elaborazione di set di dati di grandi dimensioni e in continua evoluzione.

Come linea guida generale, se possibile e se non esegui calcoli arbitrariamente complessi, utilizza le viste materializzate.

Alcune query sulle viste materializzate sono più lente rispetto alle stesse query sulle tabelle materializzate manualmente. Perché?

In generale, una query su una vista materializzata non è sempre altrettanto efficace di una query sulla tabella materializzata equivalente. Il motivo è che le viste materializzate restituiscono sempre risultati aggiornati e devono tenere conto delle modifiche apportate alle tabelle di base dall'ultimo aggiornamento della vista.

Considera questo scenario:

CREATE MATERIALIZED VIEW my_dataset.my_mv AS
SELECT date, customer_id, region, SUM(net_paid) as total_paid
FROM my_dataset.sales
GROUP BY 1, 2, 3;

CREATE TABLE my_dataset.my_materialized_table AS
SELECT date, customer_id, region, SUM(net_paid) as total_paid
FROM my_dataset.sales
GROUP BY 1, 2, 3;

Ad esempio, questa query: 

  SELECT * FROM my_dataset.my_mv LIMIT 10
 in genere viene eseguita molto più lentamente di questa query: 
  SELECT * FROM my_dataset.my_materialized_table LIMIT 10
 Per fornire risultati aggiornati in modo coerente, BigQuery deve eseguire query sulle nuove righe nella tabella di base e unire queste righe alla vista materializzata prima di applicare il predicato "LIMIT 10". Di conseguenza, la lentezza persiste, anche se la vista materializzata è completamente aggiornata.

D'altra parte, le aggregazioni sulle viste materializzate sono in genere veloci quanto le query sulla tabella materializzata. Ad esempio: 

  SELECT SUM(total_paid) FROM my_dataset.my_mv WHERE date > '2020-12-01'
 Deve essere veloce come questo: 
  SELECT SUM(total_paid) FROM my_dataset.my_materialized_table WHERE date > '2020-12-01'