Utilizzare le viste materializzate

Questo documento fornisce informazioni aggiuntive sulle viste materializzate e su come utilizzarle. Prima di leggere questo documento, acquisisci familiarità con Introduzione alle viste materializzate e Crea viste materializzate.

Eseguire query sulle viste materializzate

Puoi eseguire query direttamente sulle viste materializzate, come faresti con una tabella normale o 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 state modificate 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) nella tabella di base della vista materializzata e nella vista materializzata stessa. Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per eseguire query su una vista materializzata. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

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 usufruire di regolazione intelligente.

Per saperne di più sui ruoli IAM in BigQuery, consulta Introduzione a IAM.

Aggiornamenti incrementali

Gli aggiornamenti incrementali si verificano quando BigQuery combina i dati della vista memorizzati nella cache con i nuovi dati per fornire risultati di query coerenti, utilizzando comunque la vista materializzata. Per le viste materializzate a tabella singola, questa operazione è possibile se la tabella di base è rimasta invariata dall'ultimo aggiornamento o se sono stati aggiunti solo nuovi dati. Per le viste JOIN, solo le tabelle sul lato sinistro di JOIN possono avere dati aggiunti. Se una delle tabelle sul lato destro di un JOIN è stata modificata, la vista 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 state modificate, BigQuery non utilizza gli aggiornamenti incrementali e torna automaticamente alla query originale. Per saperne di più sui join e sulle viste materializzate, consulta Join. Di seguito sono riportati esempi di Google Cloud azioni della console, dello strumento a riga di comando bq e dell'API che possono causare un aggiornamento o un'eliminazione:

  • Istruzioni UPDATE, MERGE o DELETE di Data Manipulation Language (DML)
  • Troncamento
  • Scadenza partizione

Anche le seguenti operazioni sui metadati impediscono l'aggiornamento incrementale di una vista materializzata:

  • Modifica della scadenza della partizione
  • Aggiornamento o eliminazione di una colonna

Se una vista materializzata non può essere aggiornata in modo incrementale, le query non utilizzano i dati memorizzati nella cache fino a quando la vista non viene aggiornata automaticamente o manualmente. Per informazioni dettagliate sul motivo per cui un job non ha utilizzato i dati della vista materializzata, consulta Informazioni sul motivo per cui le viste 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 superiore all' intervallo di time travel della tabella.

Allineamento delle partizioni

Se una vista materializzata è partizionata, BigQuery garantisce che le relative partizioni siano allineate alle partizioni della colonna di partizionamento della tabella di base. Allineato 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 è partizionata, il comportamento descritto in Aggiornamenti incrementali si verifica per ogni singola partizione in modo indipendente. Ad esempio, se i dati vengono eliminati in una partizione della tabella di base, BigQuery può comunque utilizzare le altre partizioni della vista materializzata senza richiedere un aggiornamento completo dell'intera vista materializzata.

Le viste materializzate con inner join possono essere allineate solo a una delle relative tabelle di base. Se una delle tabelle di base non allineate viene modificata, la modifica influisce sull'intera vista.

Per le viste materializzate incrementali partizionate che utilizzano UNION ALL, tutte le tabelle di base devono avere le stesse impostazioni di data di scadenza della partizione (TTL) per mantenere l'allineamento delle partizioni.

Regolazione intelligente

BigQuery riscrive automaticamente le query per utilizzare le viste materializzate, quando possibile. La riscrittura automatica migliora le prestazioni delle query e riduce i costi senza modificare i risultati delle query. L'esecuzione di query non attiva automaticamente un aggiornamento materializzato. Affinché una query venga riscritta utilizzando la regolazione intelligente, la vista materializzata deve soddisfare le seguenti condizioni:

  • Appartenere allo stesso progetto di una delle relative tabelle di base o al progetto in cui viene eseguita la query.
  • Utilizzare lo stesso insieme di tabelle di base della query.
  • Includere tutte le colonne in lettura.
  • Includere tutte le righe in lettura.

La regolazione intelligente non è supportata per quanto segue:

Esempi di regolazione intelligente

Considera il seguente esempio di query di 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

Gli esempi seguenti mostrano le query e il motivo per cui queste query vengono o non vengono riscritte automaticamente utilizzando questa vista:

Query Riscrivi? 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 in lettura. La vista non include "SUM(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 in lettura. La vista 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 in lettura. "sold_datetime" non è un output (ma "CAST(sold_datetime AS DATE)" lo è).
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 vista deve includere tutte le righe in lettura. "promo_id" non è un output, quindi il filtro più restrittivo non può essere applicato alla vista.
SELECT SUM(net_profit) AS sum_profit
FROM dataset.store_sales
WHERE CAST(sold_datetime AS DATE) >= '2020-01-01'
 No La vista deve includere tutte le righe in lettura. Il filtro della vista per le date del 2021 e successive, ma la query legge le date dal 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ì

Verificare se una query è stata riscritta

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

Informazioni sul motivo per cui 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ù veloce per diversi giorni, ma poi iniziare a tornare alla query originale, con conseguente velocità di elaborazione più lenta. Per usufruire delle viste materializzate, attiva l'aggiornamento automatico o aggiorna manualmente regolarmente e monitora i job di aggiornamento delle vista materializzata per verificare che vadano a buon fine.

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

  • Query diretta della vista materializzata
  • Query indiretta in cui la regolazione intelligente potrebbe scegliere di utilizzare la vista materializzata

Le sezioni seguenti forniscono i passaggi per aiutarti a capire perché una vista materializzata è stata rifiutata.

Query diretta delle viste materializzate

Le query dirette delle 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 trova la vista materializzata di destinazione nel materialized_view_statistics campo per la query.
  2. Se chosen è presente nelle statistiche e il suo 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 prossimo aggiornamento automatico .

Query con regolazione intelligente

  1. Segui i passaggi descritti in Monitorare l'utilizzo delle viste materializzate e trova la vista materializzata di destinazione in materialized_view_statistics per la query.
  2. Esamina rejected_reason per trovare i passaggi successivi. Ad esempio, se il valore di rejected_reason è COST, la regolazione intelligente ha identificato origini dati più efficienti per costi e prestazioni.
  3. Se la vista materializzata non è presente, prova a eseguire una query diretta della vista materializzata e segui i passaggi descritti in Query diretta delle 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ù sulla regolazione intelligente e su come le query vengono riscritte utilizzando le viste materializzate, consulta Esempi di regolazione 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 la query viene eseguita, viene eseguita completamente, senza alcun vantaggio rispetto ai risultati precedenti, e paghi il costo di calcolo completo per la query. Le query pianificate sono ideali quando non hai bisogno dei dati più recenti e hai un'elevata tolleranza per la non aggiornamento dei dati.

Le viste materializzate sono più adatte quando devi eseguire query sui dati più recenti con latenza e costi ridotti al minimo riutilizzando il risultato calcolato in precedenza. Puoi utilizzare le viste materializzate come pseudo-indici, accelerando le query sulla tabella di base senza aggiornare i flussi di lavoro esistenti. L'--max_staleness opzione consente di definire la non aggiornamento accettabile per le viste materializzate, garantendo prestazioni elevate e costanti con costi controllati durante l'elaborazione di set di dati di grandi dimensioni e in continua evoluzione.

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

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

In generale, una query su una vista materializzata non è sempre efficiente come 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 sempre aggiornati, BigQuery deve eseguire query sulle nuove righe della tabella di base e unirle alla vista materializzata prima di applicare il predicato "LIMIT 10". Di conseguenza, la lentezza rimane, anche se la vista materializzata è completamente aggiornata.

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

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