Crea viste materializzate
Questo documento descrive come creare viste materializzate in BigQuery. Prima di leggere questo documento, familiarizza con l'introduzione alle viste materializzate.
Prima di iniziare
Concedi ruoli IAM (Identity and Access Management) che forniscono agli utenti le autorizzazioni necessarie per eseguire ogni attività descritta in questo documento.
Autorizzazioni obbligatorie
Per creare viste materializzate, devi disporre dell'autorizzazione IAM bigquery.tables.create
.
Ognuno dei seguenti ruoli IAM predefiniti include le autorizzazioni necessarie per creare una vista materializzata:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Per ulteriori informazioni su BigQuery Identity and Access Management (IAM), consulta Controllo dell'accesso con IAM.
Crea viste materializzate
Per creare una vista materializzata, seleziona una delle seguenti opzioni:
SQL
Utilizza l'istruzione
CREATE MATERIALIZED VIEW
.
Il seguente esempio crea una vista materializzata per il numero di clic
per ogni ID prodotto:
Nella console Google Cloud , vai alla pagina BigQuery.
Nell'editor di query, inserisci la seguente istruzione:
CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS ( QUERY_EXPRESSION );
Sostituisci quanto segue:
PROJECT_ID
: il nome del tuo progetto in cui vuoi creare la vista materializzata, ad esempiomyproject
.DATASET
: il nome del set di dati BigQuery in cui vuoi creare la vista materializzata, ad esempiomydataset
. Se stai creando una vista materializzata su una tabella BigLake di Amazon Simple Storage Service (Amazon S3) (anteprima), assicurati che il set di dati si trovi in una regione supportata.MATERIALIZED_VIEW_NAME
: il nome della vista materializzata che vuoi creare, ad esempiomy_mv
.QUERY_EXPRESSION
: l'espressione di query GoogleSQL che definisce la vista materializzata, ad esempioSELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
.
Fai clic su
Esegui.
Per maggiori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
Esempio
Il seguente esempio crea una vista materializzata per il numero di clic per ogni ID prodotto:
CREATE MATERIALIZED VIEW myproject.mydataset.my_mv_table AS ( SELECT product_id, SUM(clicks) AS sum_clicks FROM myproject.mydataset.my_base_table GROUP BY product_id );
Terraform
Utilizza la risorsa
google_bigquery_table
.
Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configurare l'autenticazione per le librerie client.
Il seguente esempio crea una vista denominata my_materialized_view
:
Per applicare la configurazione di Terraform in un progetto Google Cloud , completa i passaggi nelle sezioni seguenti.
Prepara Cloud Shell
- Avvia Cloud Shell.
-
Imposta il progetto Google Cloud predefinito in cui vuoi applicare le configurazioni Terraform.
Devi eseguire questo comando una sola volta per progetto e puoi eseguirlo in qualsiasi directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Le variabili di ambiente vengono sostituite se imposti valori espliciti nel file di configurazione Terraform.
Prepara la directory
Ogni file di configurazione di Terraform deve avere la propria directory (chiamata anche modulo radice).
-
In Cloud Shell, crea una directory e un nuovo file al suo interno. Il nome file deve avere l'estensione
.tf
, ad esempiomain.tf
. In questo tutorial, il file viene denominatomain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
Se stai seguendo un tutorial, puoi copiare il codice campione in ogni sezione o passaggio.
Copia il codice campione nel file
main.tf
appena creato.(Facoltativo) Copia il codice da GitHub. Questa operazione è consigliata quando lo snippet Terraform fa parte di una soluzione end-to-end.
- Rivedi e modifica i parametri di esempio da applicare al tuo ambiente.
- Salva le modifiche.
-
Inizializza Terraform. Devi effettuare questa operazione una sola volta per directory.
terraform init
(Facoltativo) Per utilizzare l'ultima versione del provider Google, includi l'opzione
-upgrade
:terraform init -upgrade
Applica le modifiche
-
Rivedi la configurazione e verifica che le risorse che Terraform creerà o
aggiornerà corrispondano alle tue aspettative:
terraform plan
Apporta le correzioni necessarie alla configurazione.
-
Applica la configurazione di Terraform eseguendo il comando seguente e inserendo
yes
al prompt:terraform apply
Attendi che Terraform visualizzi il messaggio "Apply complete!" (Applicazione completata).
- Apri il tuo Google Cloud progetto per visualizzare i risultati. Nella console Google Cloud , vai alle risorse nell'interfaccia utente per assicurarti che Terraform le abbia create o aggiornate.
API
Chiama il metodo tables.insert
e trasmetti una risorsa Table
con un campo materializedView
definito:
{ "kind": "bigquery#table", "tableReference": { "projectId": "PROJECT_ID", "datasetId": "DATASET", "tableId": "MATERIALIZED_VIEW_NAME" }, "materializedView": { "query": "QUERY_EXPRESSION" } }
Sostituisci quanto segue:
PROJECT_ID
: il nome del tuo progetto in cui vuoi creare la vista materializzata, ad esempiomyproject
.DATASET
: il nome del set di dati BigQuery in cui vuoi creare la vista materializzata, ad esempiomydataset
. Se stai creando una vista materializzata su una tabella BigLake di Amazon Simple Storage Service (Amazon S3) (anteprima), assicurati che il set di dati si trovi in una regione supportata.MATERIALIZED_VIEW_NAME
: il nome della vista materializzata che vuoi creare, ad esempiomy_mv
.QUERY_EXPRESSION
: l'espressione di query GoogleSQL che definisce la vista materializzata, ad esempioSELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
.
Esempio
Il seguente esempio crea una vista materializzata per il numero di clic per ogni ID prodotto:
{ "kind": "bigquery#table", "tableReference": { "projectId": "myproject", "datasetId": "mydataset", "tableId": "my_mv" }, "materializedView": { "query": "select product_id,sum(clicks) as sum_clicks from myproject.mydataset.my_source_table group by 1" } }
Java
Prima di provare questo esempio, segui le istruzioni di configurazione di Java nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.
Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configurare l'autenticazione per le librerie client.
Una volta creata correttamente, la vista materializzata viene visualizzata nel riquadro Explorer di BigQuery nella console Google Cloud . L'esempio seguente mostra uno schema di vista materializzata:
A meno che tu non disattivi l'aggiornamento automatico, BigQuery avvia un aggiornamento completo asincrono per la vista materializzata. La query termina rapidamente, ma l'aggiornamento iniziale potrebbe continuare a essere eseguito.
Controllo degli accessi
Puoi concedere l'accesso a una vista materializzata a livello di set di dati, a livello di vista o a livello di colonna. Puoi anche impostare l'accesso a un livello superiore nella gerarchia delle risorse IAM.
L'esecuzione di query su una vista materializzata richiede l'accesso alla vista e alle relative tabelle di base. Per condividere una vista materializzata, puoi concedere le autorizzazioni alle tabelle di base o configurare una vista materializzata come vista autorizzata. Per ulteriori informazioni, consulta Visualizzazioni autorizzate.
Per controllare l'accesso alle viste in BigQuery, consulta Visualizzazioni autorizzate.
Supporto delle query sulle viste materializzate
Le viste materializzate utilizzano una sintassi SQL limitata. Le query devono utilizzare il seguente pattern:
[ WITH cte [, …]] SELECT [{ ALL | DISTINCT }] expression [ [ AS ] alias ] [, ...] FROM from_item [, ...] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] from_item: { table_name [ as_alias ] | { join_operation | ( join_operation ) } | field_path | unnest_operator | cte_name [ as_alias ] } as_alias: [ AS ] alias
Limitazioni delle query
Le viste materializzate presentano le seguenti limitazioni.
Requisiti di aggregazione
Gli aggregati nella query della vista materializzata devono essere output. Il calcolo, il filtraggio
o l'unione in base a un valore aggregato non sono supportati. Ad esempio, la creazione
di una visualizzazione dalla seguente query non è supportata perché produce un valore
calcolato da un aggregato, COUNT(*) / 10 as cnt
.
SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt FROM mydataset.mytable GROUP BY ts_hour;
Al momento sono supportate solo le seguenti funzioni di aggregazione:
ANY_VALUE
(ma non superiore aSTRUCT
)APPROX_COUNT_DISTINCT
ARRAY_AGG
(ma non superiore aARRAY
oSTRUCT
)AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
MAX_BY
(ma non superiore aSTRUCT
)MIN_BY
(ma non superiore aSTRUCT
)SUM
Funzionalità SQL non supportate
Le seguenti funzionalità SQL non sono supportate nelle viste materializzate:
UNION ALL
. (Assistenza nell') anteprimaLEFT OUTER JOIN
(assistenza nell'anteprima di )RIGHT/FULL OUTER JOIN
.- Self-join, noto anche come utilizzo di un
JOIN
sulla stessa tabella più di una volta. - Funzioni finestra.
ARRAY
sottoquery.- Funzioni non deterministiche come
RAND()
,CURRENT_DATE()
,SESSION_USER()
oCURRENT_TIME()
. - Funzioni definite dall'utente.
TABLESAMPLE
.FOR SYSTEM_TIME AS OF
.
LEFT OUTER JOIN
e UNION ALL
Per richiedere feedback o assistenza per questa funzionalità, invia un'email all'indirizzo bq-mv-help @google.com.
Le viste materializzate incrementali supportano LEFT OUTER JOIN
e UNION ALL
.
Le visualizzazioni materializzate con le istruzioni LEFT OUTER JOIN
e UNION ALL
condividono le limitazioni delle altre visualizzazioni materializzate incrementali. Inoltre, la sintonizzazione
intelligente non è supportata per
le viste materializzate con UNION ALL o left outer join.
Esempi
L'esempio seguente crea una vista materializzata incrementale aggregata con
un LEFT JOIN
. Questa visualizzazione viene aggiornata in modo incrementale quando i dati vengono aggiunti alla tabella a sinistra.
CREATE MATERIALIZED VIEW dataset.mv AS ( SELECT s_store_sk, s_country, s_zip, SUM(ss_net_paid) AS sum_sales, FROM dataset.store_sales LEFT JOIN dataset.store ON ss_store_sk = s_store_sk GROUP BY 1, 2, 3 );
L'esempio seguente crea una vista materializzata incrementale aggregata con
un UNION ALL
. Questa visualizzazione viene aggiornata in modo incrementale quando i dati vengono aggiunti a una o a entrambe le tabelle. Per ulteriori informazioni sugli aggiornamenti incrementali, vedi
Aggiornamenti incrementali.
CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour) AS ( SELECT SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales FROM (SELECT ts, sales from dataset.table1 UNION ALL SELECT ts, sales from dataset.table2) GROUP BY 1 );
Limitazioni del controllo degli accessi
- Se la query di un utente su una vista materializzata include colonne della tabella di base
a cui non può accedere a causa della sicurezza a livello di colonna, la query non va a buon fine
e viene visualizzato il messaggio
Access Denied
. - Se un utente esegue una query su una vista materializzata, ma non ha accesso completo a tutte le righe delle tabelle di base della vista materializzata, BigQuery esegue la query sulle tabelle di base anziché leggere i dati della vista materializzata. Ciò garantisce che la query rispetti tutti i vincolicontrollo dell'accessoo. Questa limitazione si applica anche quando si eseguono query sulle tabelle con colonne con dati mascherati.
Clausola WITH
ed espressioni della tabella comune (CTE)
Le viste materializzate supportano le clausole WITH
e le espressioni di tabella comuni.
Le viste materializzate con clausole WITH
devono comunque seguire il pattern e
le limitazioni delle viste materializzate senza clausole WITH
.
Esempi
L'esempio seguente mostra una vista materializzata che utilizza una clausola WITH
:
WITH tmp AS ( SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, * FROM mydataset.mytable ) SELECT ts_hour, COUNT(*) AS cnt FROM tmp GROUP BY ts_hour;
L'esempio seguente mostra una vista materializzata che utilizza una clausola WITH
non supportata perché contiene due clausole GROUP BY
:
WITH tmp AS ( SELECT city, COUNT(*) AS population FROM mydataset.mytable GROUP BY city ) SELECT population, COUNT(*) AS cnt GROUP BY population;
Viste materializzate sulle tabelle BigLake
Per creare viste materializzate sulle tabelle BigLake, la tabella BigLake deve avere la memorizzazione nella cache dei metadati abilitata sui dati di Cloud Storage e la vista materializzata deve avere un valore dell'opzione max_staleness
maggiore della tabella di base.
Le viste materializzate sulle tabelle BigLake supportano lo stesso insieme di query delle altre viste materializzate.
Esempio
Creazione di una semplice vista aggregata utilizzando una tabella di base BigLake:
CREATE MATERIALIZED VIEW sample_dataset.sample_mv OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND) AS SELECT COUNT(*) cnt FROM dataset.biglake_base_table;
Per informazioni dettagliate sulle limitazioni delle viste materializzate sulle tabelle BigLake, consulta Viste materializzate sulle tabelle BigLake.
Viste materializzate su tabelle esterne di sola lettura Apache Iceberg
Puoi fare riferimento a tabelle Iceberg di grandi dimensioni nelle viste materializzate anziché eseguire la migrazione di questi dati nello spazio di archiviazione gestito da BigQuery.
Crea una vista materializzata su una tabella Iceberg
L'esempio seguente crea una vista materializzata allineata alla partizione su una tabella Iceberg di base partizionata:
CREATE MATERIALIZED VIEW mydataset.myicebergmv PARTITION BY DATE_TRUNC(birth_month, MONTH) AS SELECT * FROM mydataset.myicebergtable;
La tabella Iceberg di base sottostante myicebergtable
deve
avere una specifica di partizione
come la seguente:
"partition-specs" : [ {
"spec-id" : 0,
"fields" : [ {
"name" : "birth_month",
"transform" : "month",
"source-id" : 3,
"field-id" : 1000
} ]
} ]
Limitazioni
Oltre alle limitazioni delle tabelle Iceberg standard, le viste materializzate sulle tabelle Iceberg presentano le seguenti limitazioni:
- Puoi creare una vista materializzata allineata alla partizione con la tabella di base. Tuttavia, la vista materializzata supporta solo la trasformazione delle partizioni basata sul tempo, ad esempio
YEAR
,MONTH
,DAY
eHOUR
. - La granularità della partizione della vista materializzata non può essere più precisa della
granularità della partizione della tabella di base. Ad esempio, se partizioni la
tabella di base annualmente utilizzando la colonna
birth_date
, la creazione di una vista materializzata conPARTITION BY DATE_TRUNC(birth_date, MONTH)
non è supportata. - Se le tabelle Iceberg di base hanno modifiche in più di 4000 partizioni, lavista materializzataa viene invalidata completamente all'aggiornamento, anche se è partizionata.
- Sono supportate le evoluzioni delle partizioni. Tuttavia, la modifica delle colonne di partizionamento di una tabella di base senza ricreare la vista materializzata potrebbe comportare un'invalidazione completa che non può essere corretta aggiornando la vista materializzata.
- La tabella di base deve contenere almeno uno snapshot.
- La tabella Iceberg deve essere una tabella BigLake, ad esempio una tabella esterna autorizzata.
- La query sulla vista materializzata potrebbe non riuscire se il file
metadata.json
della tua tabella Iceberg è danneggiato. - Se sono attivi i Controlli di servizio VPC, gli account di servizio della tabella esterna autorizzata devono essere aggiunti alle regole di ingress, altrimenti i Controlli di servizio VPC bloccano l'aggiornamento automatico in background per la vista materializzata.
Il file metadata.json
della tabella Iceberg deve avere
le seguenti specifiche. Senza queste specifiche, le query analizzano la tabella di base e non utilizzano il risultato materializzato.
-
current-snapshot-id
current-schema-id
snapshots
snapshot-log
In snapshot:
parent-snapshot-id
(se disponibile)schema-id
operation
(nel camposummary
)
Partizionamento (per la vista materializzata partizionata)
Viste materializzate partizionate
Le viste materializzate sulle tabelle partizionate possono essere partizionate. Il partizionamento di una vista materializzata è simile al partizionamento di una tabella normale, in quanto offre vantaggi quando le query accedono spesso a un sottoinsieme delle partizioni. Inoltre, il partizionamento di una vista materializzata può migliorare il comportamento della vista quando i dati della tabella o delle tabelle di base vengono modificati o eliminati. Per ulteriori informazioni, vedi Allineamento delle partizioni.
Se la tabella di base è partizionata, puoi partizionare una vista materializzata sulla stessa colonna di partizionamento. Per le partizioni basate sul tempo, la granularità deve corrispondere (oraria, giornaliera, mensile o annuale). Per le partizioni di intervalli interi, la specifica dell'intervallo deve corrispondere esattamente. Non puoi partizionare una vista materializzata su una tabella di base non partizionata.
Se la tabella di base è partizionata in base alla data di importazione, una vista materializzata può
raggruppare in base alla colonna _PARTITIONDATE
della tabella di base e anche partizionarla.
Se non specifichi esplicitamente il partizionamento quando crei la vista materializzata, quest'ultima non viene partizionata.
Se la tabella di base è partizionata, valuta la possibilità di partizionare anche la vista materializzata per ridurre i costi di manutenzione del job di aggiornamento e della query.
Scadenza partizione
La scadenza della partizione non può essere impostata sulle viste materializzate. Una vista materializzata eredita implicitamente il tempo di scadenza della partizione dalla tabella di base. Le partizioni della vista materializzata sono allineate alle partizioni della tabella di base, quindi scadono in modo sincrono.
Esempio 1
In questo esempio, la tabella di base è partizionata in base alla colonna transaction_time
con partizioni giornaliere. La vista materializzata è partizionata nella stessa colonna
e raggruppata in cluster nella colonna employee_id
.
CREATE TABLE my_project.my_dataset.my_base_table( employee_id INT64, transaction_time TIMESTAMP) PARTITION BY DATE(transaction_time) OPTIONS (partition_expiration_days = 2); CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table PARTITION BY DATE(transaction_time) CLUSTER BY employee_id AS ( SELECT employee_id, transaction_time, COUNT(employee_id) AS cnt FROM my_dataset.my_base_table GROUP BY employee_id, transaction_time );
Esempio 2
In questo esempio, la tabella di base è partizionata per data di importazione con partizioni giornaliere. La vista materializzata seleziona l'ora di importazione come colonna denominata
date
. La vista materializzata è raggruppata in base alla colonna date
e partizionata in base alla stessa colonna.
CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table PARTITION BY date CLUSTER BY employee_id AS ( SELECT employee_id, _PARTITIONDATE AS date, COUNT(1) AS count FROM my_dataset.my_base_table GROUP BY employee_id, date );
Esempio 3
In questo esempio, la tabella di base è partizionata in base a una colonna TIMESTAMP
denominata
transaction_time
, con partizioni giornaliere. La vista materializzata definisce una
colonna denominata transaction_hour
, utilizzando la funzione
TIMESTAMP_TRUNC
per troncare il valore all'ora più vicina. La vista materializzata è
raggruppata in base a transaction_hour
e anche partizionata in base a questo valore.
Tieni presente quanto segue:
La funzione di troncamento applicata alla colonna di partizionamento deve essere almeno granulare quanto il partizionamento della tabella di base. Ad esempio, se la tabella di base utilizza partizioni giornaliere, la funzione di troncamento non può utilizzare la granularità
MONTH
oYEAR
.Nella specifica della partizione della vista materializzata, la granularità deve corrispondere a quella della tabella di base.
CREATE TABLE my_project.my_dataset.my_base_table ( employee_id INT64, transaction_time TIMESTAMP) PARTITION BY DATE(transaction_time); CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table PARTITION BY DATE(transaction_hour) AS ( SELECT employee_id, TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour, COUNT(employee_id) AS cnt FROM my_dataset.my_base_table GROUP BY employee_id, transaction_hour );
Viste materializzate del cluster
Puoi raggruppare le viste materializzate in base alle colonne di output, nel rispetto dei limiti delle tabelle clusterizzate di BigQuery. Le colonne di output aggregato non possono essere utilizzate come colonne di clustering. L'aggiunta di colonne di clustering alle viste materializzate può migliorare le prestazioni delle query che includono filtri su queste colonne.
Fare riferimento alle viste logiche
Per richiedere feedback o assistenza per questa funzionalità, invia un'email all'indirizzo bq-mv-help@google.com.
Le query delle viste materializzate possono fare riferimento a viste logiche, ma sono soggette alle seguenti limitazioni:
- Si applicano limitazioni alle viste materializzate.
- Se la visualizzazione logica cambia, la vista materializzata diventa non valida e deve essere aggiornata completamente.
- Smart Tuning non è supportato.
Considerazioni per la creazione di viste materializzate
Quali viste materializzate creare
Quando crei una vista materializzata, assicurati che la definizione della vista materializzata rifletta i pattern di query sulle tabelle di base. Le viste materializzate sono più efficaci quando servono un ampio insieme di query anziché un solo pattern di query specifico.
Ad esempio, considera una query su una tabella in cui gli utenti spesso filtrano in base alle colonne
user_id
o department
. Puoi raggruppare in base a queste colonne e, facoltativamente, raggrupparle, anziché aggiungere filtri come user_id = 123
alla vista materializzata.
Come altro esempio, puoi utilizzare filtri di date deterministici, in base a una data specifica, ad esempio WHERE order_date = '2019-10-01'
, o a un intervallo di date, ad esempio WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'
. Aggiungi un filtro dell'intervallo di date
nella vista materializzata che copre gli intervalli di date previsti nella query:
CREATE MATERIALIZED VIEW ... ... WHERE date > '2019-01-01' GROUP BY date
Unioni
I seguenti consigli si riferiscono alle viste materializzate con JOIN.
Inserisci per prima la tabella che cambia più spesso
Assicurati che la tabella più grande o che cambia più spesso sia la prima/più a sinistra a cui viene fatto riferimento nella query della visualizzazione. Le viste materializzate con join supportano query incrementali e aggiornamenti quando viene aggiunto un elemento alla prima tabella o alla tabella più a sinistra della query, ma le modifiche alle altre tabelle invalidano completamente la cache della vista. Negli schemi a stella o a fiocco di neve, la prima tabella o quella più a sinistra dovrebbe in genere essere la tabella dei fatti.
Evitare i join sulle chiavi di clustering
Le viste materializzate con join funzionano meglio nei casi in cui i dati sono fortemente aggregati o la query di join originale è costosa. Per le query selettive, BigQuery è spesso già in grado di eseguire il join in modo efficiente e non è necessaria alcuna vista materializzata. Ad esempio, considera le seguenti definizioni divista materializzatae.
CREATE MATERIALIZED VIEW dataset.mv CLUSTER BY s_market_id AS ( SELECT s_market_id, s_country, SUM(ss_net_paid) AS sum_sales, COUNT(*) AS cnt_sales FROM dataset.store_sales INNER JOIN dataset.store ON ss_store_sk = s_store_sk GROUP BY s_market_id, s_country );
Supponiamo che store_sales
sia raggruppato in cluster su ss_store_sk
e che tu esegua spesso query
come la seguente:
SELECT SUM(ss_net_paid) FROM dataset.store_sales INNER JOIN dataset.store ON ss_store_sk = s_store_sk WHERE s_country = 'Germany';
La vista materializzata potrebbe non essere efficiente come la query originale. Per ottenere risultati ottimali, prova un insieme rappresentativo di query, con e senza la vista materializzata.
Utilizzare le viste materializzate con l'opzione max_staleness
L'opzione di vista materializzata max_staleness
ti aiuta a ottenere prestazioni delle query costantemente elevate con costi controllati durante l'elaborazione di set di dati di grandi dimensioni e in continua evoluzione. Con il parametro max_staleness
, puoi ridurre i costi e la latenza delle query impostando un intervallo di tempo in cui l'obsolescenza dei dati dei risultati della query è accettabile. Questo comportamento può essere utile per dashboard e report per i quali i risultati delle query completamente aggiornati non sono essenziali.
Inattività dei dati
Quando esegui una query su una vista materializzata con l'opzione max_staleness
impostata,
BigQuery restituisce il risultato in base al valore max_staleness
e all'ora in cui è stato eseguito l'ultimo aggiornamento.
Se l'ultimo aggiornamento è avvenuto entro l'intervallo max_staleness
, BigQuery restituisce i dati direttamente dalla vista materializzata senza leggere le tabelle di base. Ad esempio, questo vale se l'intervallo di max_staleness
è di 4 ore e l'ultimo aggiornamento è avvenuto 2 ore fa.
Se l'ultimo aggiornamento è avvenuto al di fuori dell'intervallo max_staleness
, BigQuery legge i dati dalla vista materializzata, li combina con le modifiche apportate alla tabella base dall'ultimo aggiornamento e restituisce il risultato combinato. Questo risultato combinato potrebbe comunque essere obsoleto, fino all'intervallo max_staleness
. Ad esempio, questo vale se l'intervallo max_staleness
è di 4 ore
e l'ultimo aggiornamento è avvenuto 7 ore fa.
Opzione Crea con max_staleness
Seleziona una delle seguenti opzioni:
SQL
Per creare una vista materializzata con l'opzione max_staleness
, aggiungi una clausola OPTIONS
all'istruzione DDL quando crei la vista materializzata:
Nella console Google Cloud , vai alla pagina BigQuery.
Nell'editor di query, inserisci la seguente istruzione:
CREATE MATERIALIZED VIEW
project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60, max_staleness = INTERVAL "4:0:0" HOUR TO SECOND) AS SELECT employee_id, DATE(transaction_time), COUNT(1) AS count FROMmy_dataset.my_base_table
GROUP BY 1, 2;Sostituisci quanto segue:
- project-id è l'ID progetto.
- my_dataset è l'ID di un set di dati nel tuo progetto.
- my_mv_table è l'ID della vista materializzata che stai creando.
- my_base_table è l'ID di una tabella nel tuo set di dati che funge da tabella di base per la tua vista materializzata.
Fai clic su
Esegui.
Per maggiori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
API
Chiama il metodo tables.insert
con una risorsa materializedView
definita come parte della richiesta API. La risorsa materializedView
contiene un campo query
. Ad esempio:
{ "kind": "bigquery#table", "tableReference": { "projectId": "project-id", "datasetId": "my_dataset", "tableId": "my_mv_table" }, "materializedView": { "query": "select product_id,sum(clicks) as sum_clicks from project-id.my_dataset.my_base_table group by 1" } "maxStaleness": "4:0:0" }
Sostituisci quanto segue:
- project-id è l'ID progetto.
- my_dataset è l'ID di un set di dati nel tuo progetto.
- my_mv_table è l'ID della vista materializzata che stai creando.
- my_base_table è l'ID di una tabella nel tuo set di dati che funge da tabella di base per la tua vista materializzata.
product_id
è una colonna della tabella di base.clicks
è una colonna della tabella di base.sum_clicks
è una colonna della vista materializzata che stai creando.
Applica l'opzione max_staleness
Puoi applicare questo parametro alle viste materializzate esistenti utilizzando l'istruzione ALTER
MATERIALIZED VIEW
. Ad esempio:
ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);
Query con max_staleness
Puoi eseguire query sulle viste materializzate con l'opzione max_staleness
come faresti
con qualsiasi altra vista materializzata, vista logica o tabella.
Ad esempio:
SELECT * FROM project-id.my_dataset.my_mv_table
Questa query restituisce i dati dell'ultimo aggiornamento se non sono più vecchi del parametro max_staleness
. Se la vista materializzata non è stata aggiornata
entro l'intervallo max_staleness
, BigQuery unisce i risultati dell'ultimo
aggiornamento disponibile con le modifiche alla tabella base per restituire i risultati entro
l'intervallo max_staleness
.
Streaming di dati e risultati di max_staleness
Se trasmetti dati nelle tabelle di base di una vista materializzata con l'opzione
max_staleness
, la query della vista materializzata potrebbe escludere
i record trasmessi nelle relative tabelle prima dell'inizio dell'intervallo
di non aggiornamento. Di conseguenza, una vista materializzata che include dati di più tabelle e l'opzione max_staleness
potrebbe non rappresentare uno snapshot temporale di queste tabelle.
Ottimizzazione intelligente e opzione max_staleness
L'ottimizzazione intelligente riscrive automaticamente le query per utilizzare le viste materializzate ogni volta che è possibile, indipendentemente dall'opzione max_staleness
, anche se la query non fa riferimento a una vista materializzata. L'opzione max_staleness
in una vista materializzata
non influisce sui risultati della query riscritta. L'opzione max_staleness
influisce solo sulle query che interrogano direttamente la vista materializzata.
Gestire l'obsolescenza e la frequenza di aggiornamento
Devi impostare max_staleness
in base ai tuoi requisiti. Per evitare di leggere
i dati dalle tabelle di base, configura l'intervallo di aggiornamento in modo che l'aggiornamento avvenga
entro l'intervallo di obsolescenza. Puoi tenere conto del runtime medio
dell'aggiornamento più un margine per la crescita.
Ad esempio, se è necessaria un'ora per aggiornare la vista materializzata e vuoi un buffer di un'ora per la crescita, devi impostare l'intervallo di aggiornamento su due ore. Questa configurazione garantisce che l'aggiornamento avvenga entro il limite massimo di quattro ore per l'obsolescenza del report.
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness = INTERVAL "4:0:0" HOUR TO SECOND) AS SELECT employee_id, DATE(transaction_time), COUNT(1) AS cnt FROM my_dataset.my_base_table GROUP BY 1, 2;
Viste materializzate non incrementali
Le viste materializzate non incrementali supportano la maggior parte delle query SQL, incluse le clausole OUTER
JOIN
, UNION
e HAVING
, nonché le funzioni di analisi. Per determinare
se una vista materializzata è stata utilizzata nella query, controlla le stime dei costi
utilizzando un test dry run.
Negli scenari in cui
l'obsolescenza dei dati è accettabile, ad esempio per l'elaborazione batch dei dati o
la generazione di report, le viste materializzate non incrementali possono migliorare le prestazioni delle query e
ridurre i costi. Utilizzando l'opzione max_staleness
, puoi creare viste materializzate arbitrarie e complesse che vengono gestite automaticamente e hanno garanzie di obsolescenza integrate.
Utilizzare viste materializzate non incrementali
Puoi creare viste materializzate non incrementali utilizzando l'opzione
allow_non_incremental_definition
. Questa opzione deve essere accompagnata
dall'opzione max_staleness
. Per garantire un aggiornamento periodico della vista materializzata, devi anche configurare una policy di aggiornamento.
Senza una policy di aggiornamento, devi aggiornare manualmente la vista materializzata.
La vista materializzata rappresenta sempre lo stato delle tabelle di base all'interno dell'intervallo
max_staleness
. Se l'ultimo aggiornamento è troppo obsoleto e non rappresenta
le tabelle di base nell'intervallo max_staleness
, la query legge le
tabelle di base. Per scoprire di più sulle possibili implicazioni sul rendimento, consulta Stalezza
dei dati.
Crea con allow_non_incremental_definition
Per creare una vista materializzata con l'opzione allow_non_incremental_definition
, procedi nel seguente modo. Dopo aver creato la vista materializzata, non puoi
modificare l'opzione allow_non_incremental_definition
. Ad esempio, non puoi
modificare il valore true
in false
o rimuovere l'opzione
allow_non_incremental_definition
dalla vista materializzata.
SQL
Aggiungi una clausola OPTIONS
all'istruzione DDL quando crei la
vista materializzata:
Nella console Google Cloud , vai alla pagina BigQuery.
Nell'editor di query, inserisci la seguente istruzione:
CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table OPTIONS ( enable_refresh = true, refresh_interval_minutes = 60, max_staleness = INTERVAL "4" HOUR, allow_non_incremental_definition = true) AS
SELECT
s_store_sk, SUM(ss_net_paid) AS sum_sales, APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median FROM my_project.my_dataset.store LEFT OUTER JOIN my_project.my_dataset.store_sales ON ss_store_sk = s_store_sk GROUP BY s_store_sk HAVING median < 40 OR median is NULL ;Sostituisci quanto segue:
- my_project è l'ID progetto.
- my_dataset è l'ID di un set di dati nel tuo progetto.
- my_mv_table è l'ID della vista materializzata che stai creando.
- my_dataset.store e my_dataset.store_sales sono gli ID delle tabelle nel set di dati che fungono da tabelle di base per la vista materializzata.
Fai clic su
Esegui.
Per maggiori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
API
Chiama il metodo tables.insert
con una risorsa materializedView
definita come parte della richiesta API. La risorsa materializedView
contiene un campo query
. Ad esempio:
{ "kind": "bigquery#table", "tableReference": { "projectId": "my_project", "datasetId": "my_dataset", "tableId": "my_mv_table" }, "materializedView": { "query": "`SELECT` s_store_sk, SUM(ss_net_paid) AS sum_sales, APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median FROM my_project.my_dataset.store LEFT OUTER JOIN my_project.my_dataset.store_sales ON ss_store_sk = s_store_sk GROUP BY s_store_sk HAVING median < 40 OR median is NULL`", "allowNonIncrementalDefinition": true } "maxStaleness": "4:0:0" }
Sostituisci quanto segue:
- my_project è l'ID progetto.
- my_dataset è l'ID di un set di dati nel tuo progetto.
- my_mv_table è l'ID della vista materializzata che stai creando.
- my_dataset.store e my_dataset.store_sales sono gli ID delle tabelle nel set di dati che fungono da tabelle di base per la vista materializzata.
Query con allow_non_incremental_definition
Puoi eseguire query sulle viste materializzate non incrementali come faresti con qualsiasi altra vista materializzata, vista logica o tabella.
Ad esempio:
SELECT * FROM my_project.my_dataset.my_mv_table
Se i dati non sono più vecchi del parametro max_staleness
, questa query
restituisce i dati dell'ultimo aggiornamento. Per maggiori dettagli sull'obsolescenza e
sull'aggiornamento dei dati, vedi Obsolescenza dei dati.
Limitazioni specifiche per le viste materializzate non incrementali
Le seguenti limitazioni si applicano solo alle viste materializzate con l'opzione allow_non_incremental_definition
. Ad eccezione delle limitazioni alla sintassi delle query supportata, tutte le limitazioni delle viste materializzate continuano a essere applicate.
- L'ottimizzazione intelligente non viene applicata alle viste materializzate che includono l'opzione
allow_non_incremental_definition
. L'unico modo per usufruire delle viste materializzate con l'opzioneallow_non_incremental_definition
è eseguire query direttamente. - Le viste materializzate senza l'opzione
allow_non_incremental_definition
possono aggiornare in modo incrementale un sottoinsieme dei dati. Le viste materializzate con l'opzioneallow_non_incremental_definition
devono essere aggiornate completamente. - Le viste materializzate con l'opzione max_staleness convalidano la presenza dei vincoli di sicurezza a livello di colonna durante l'esecuzione della query. Scopri di più sul controllo dell'accesso a livello di colonna.