Crea viste materializzate
Questo documento descrive come creare viste materializzate in in BigQuery. Prima di leggere il presente documento, acquisisci familiarità con consulta Introduzione alle viste materializzate.
Prima di iniziare
Concede ruoli IAM (Identity and Access Management) che concedono agli utenti le autorizzazioni necessarie per eseguire ciascuna attività in questo documento.
Autorizzazioni obbligatorie
Per creare viste materializzate, devi disporre dell'autorizzazione IAM bigquery.tables.create
.
Ciascuno dei seguenti ruoli IAM predefiniti include autorizzazioni necessarie per creare una vista materializzata:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Per ulteriori informazioni Identity and Access Management (IAM) per BigQuery), consulta Controllo dell'accesso con IAM.
Crea viste materializzate
Per creare una vista materializzata, seleziona una delle seguenti opzioni:
SQL
Utilizza la
Dichiarazione CREATE MATERIALIZED VIEW
.
L'esempio seguente crea una vista materializzata per il numero di clic
per ciascun 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 un Amazon Simple Storage Service (Amazon S3) Tabella BigLake (anteprima), rendi che il set di dati sia in un regione supportata.MATERIALIZED_VIEW_NAME
: il nome del vista materializzata che vuoi creare, ad esempiomy_mv
.QUERY_EXPRESSION
: il team SQL di Google un'espressione di query che definisce la vista materializzata, ad esempioSELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
.
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.
Esempio
L'esempio seguente crea una vista materializzata per il numero di clic per ciascun 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
google_bigquery_table
risorsa.
Per eseguire l'autenticazione in BigQuery, configura il valore predefinito dell'applicazione Credenziali. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
L'esempio seguente crea una vista denominata my_materialized_view
:
Per applicare la configurazione Terraform a un progetto Google Cloud, completa i passaggi nella le 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 in Terraform di configurazione del deployment.
Prepara la directory
Ogni file di configurazione Terraform deve avere una directory (inoltre chiamato modulo principale).
-
In Cloud Shell, crea una directory e un nuovo
all'interno di quella directory. Il nome file deve avere l'estensione
.tf
, ad esempiomain.tf
. In questo tutorial, il file è 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 nuovo oggetto
main.tf
.Facoltativamente, copia il codice da GitHub. Opzione consigliata quando lo snippet Terraform fa parte di una soluzione end-to-end.
- Esamina e modifica i parametri di esempio da applicare al tuo ambiente.
- Salva le modifiche.
-
Inizializza Terraform. Devi eseguire questa operazione una sola volta per directory.
terraform init
Facoltativamente, per utilizzare la versione più recente del provider Google, includi
-upgrade
:terraform init -upgrade
Applica le modifiche
-
Rivedi la configurazione e verifica che le risorse che Terraform creerà o
che l'aggiornamento soddisfi le tue aspettative:
terraform plan
Apporta le correzioni necessarie alla configurazione.
-
Applica la configurazione Terraform eseguendo questo comando e inserendo
yes
alla richiesta:terraform apply
Attendi che Terraform mostri il messaggio "Applicazione completata".
- Apri il progetto Google Cloud 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 passiamo
Table
risorsa
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 vista materializzata in, ad esempiomydataset
. Se stai creando una vista materializzata su un Amazon Simple Storage Service (Amazon S3) Tabella BigLake (anteprima), rendi che il set di dati sia in un regione supportata.MATERIALIZED_VIEW_NAME
: il nome della vista materializzata che vuoi creare, ad esempiomy_mv
.QUERY_EXPRESSION
: il team SQL di Google un'espressione di query che definisce la vista materializzata, ad esempioSELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
.
Esempio
L'esempio seguente crea una vista materializzata per il numero di clic per ciascun 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 per la configurazione di Java nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.
Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
Dopo essere stata creata, la vista materializzata appare nel riquadro Explorer di BigQuery nella console Google Cloud. L'esempio seguente mostra uno schema di vista materializzata:
A meno che non disattivi l'aggiornamento automatico, BigQuery avvia un aggiornamento completo asincrono vista. La query termina rapidamente, ma è possibile che l'aggiornamento iniziale continui vengono eseguiti tutti i test delle unità.
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 maggiori informazioni informazioni, consulta la sezione Visualizzazioni autorizzate.
Per controllare l'accesso alle viste in BigQuery, consulta Visualizzazioni autorizzate.
Supporto per le 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 per le query
Le viste materializzate hanno le seguenti limitazioni.
Requisiti aggregati
Gli aggregati nella query della vista materializzata devono essere output. Il calcolo, i filtri o le unioni in base a un valore aggregato non sono supportati. Ad esempio, la creazione
una vista dalla seguente query non è supportata perché produce un valore
calcolati da un valore 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 più diSTRUCT
)APPROX_COUNT_DISTINCT
ARRAY_AGG
(ma non più diARRAY
oSTRUCT
)AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
MAX_BY
(ma non più diSTRUCT
)MIN_BY
(ma non più diSTRUCT
)SUM
Funzionalità SQL non supportate
Le seguenti funzionalità SQL non sono supportate nelle viste materializzate:
UNION ALL
. (Assistenza nell'anteprima di )LEFT OUTER JOIN
(assistenza nell'anteprima di )RIGHT/FULL OUTER JOIN
.- I self-join, noti anche come utilizzare
JOIN
nella 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
.
Assistenza di 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 istruzioni LEFT OUTER JOIN
e UNION ALL
condividono le limitazioni di altre visualizzazioni materializzate incrementali. Inoltre, l'ottimizzazione intelligente non è supportata per le visualizzazioni con dati materiali con union all o join esterno sinistro.
Esempi
L'esempio seguente crea una vista materializzata incrementale aggregata con un LEFT JOIN
. Questa vista viene aggiornata in modo incrementale quando i dati vengono aggiunti a sinistra
tabella.
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
UNION ALL
. Questa vista viene aggiornata in modo incrementale quando i dati vengono aggiunti a uno o
entrambe le tabelle. Per ulteriori informazioni sugli aggiornamenti incrementali, consulta
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 dell'accesso
- Se la query di un utente per una vista materializzata include colonne della tabella di base
a cui non possono accedere a causa della sicurezza a livello di colonna, la query ha esito negativo
con il messaggio
Access Denied
. - Se un utente esegue query su una vista materializzata ma non ha accesso completo a tutte le righe delle viste materializzate tabelle di base, BigQuery esegue la query anziché leggere i dati delle vista materializzata. Ciò garantisce che la query rispetti tutti i vincoli di controllo dell'accesso. Questa limitazione si applica anche nei casi in cui eseguire query su tabelle con colonne mascherate dai dati.
Clausola WITH
ed espressioni di tabella comuni (CTE)
Le viste materializzate supportano le clausole WITH
ed espressioni di tabella comuni.
Le viste materializzate con clausole WITH
devono comunque seguire lo schema e
limitazioni delle viste materializzate senza clausole WITH
.
Esempi
L'esempio seguente mostra una vista materializzata utilizzando 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 utilizzando una clausola WITH
che è
non supportato 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 su BigLake
tabelle,
La tabella BigLake deve avere la memorizzazione dei metadati nella cache
attivate su
I dati Cloud Storage e la vista materializzata devono avere un
Valore dell'opzione max_staleness
maggiore di quello della tabella di base.
Le viste materializzate sulle tabelle BigLake supportano lo stesso insieme di
query rispetto ad 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 maggiori dettagli sulle limitazioni delle viste materializzate rispetto a Per le tabelle BigLake, vedi le visualizzazioni materializzate su Tavoli BigLake.
Viste materializzate sulle tabelle Apache Iceberg
Per richiedere assistenza o feedback per questa funzione, invia un'email a bq-mv-help@google.com.
Puoi fare riferimento alle tabelle Iceberg di grandi dimensioni in anziché migrare i dati in uno spazio di archiviazione gestito da BigQuery.
Creare una vista materializzata su una tabella Iceberg
Per creare una vista materializzata di un Iceberg: questi passaggi:
Ottenere una tabella Iceberg utilizzando uno dei seguenti metodi metodo:
- Crea una tabella Iceberg con il file di metadati JSON.
- Crea una tabella Iceberg utilizzando BigLake Metastore.
- Scoprilo nei set di dati federati di AWS Glue.
Esempio
CREATE EXTERNAL TABLE mydataset.myicebergtable WITH CONNECTION `myproject.us.myconnection` OPTIONS ( format = 'ICEBERG', uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"] )
Fai riferimento alla tabella Iceberg con quanto segue specifiche della partizione:
"partition-specs" : [ { "spec-id" : 0, "fields" : [ { "name" : "birth_month", "transform" : "month", "source-id" : 3, "field-id" : 1000 } ]
Crea una vista materializzata allineata alle partizioni:
CREATE MATERIALIZED VIEW mydataset.myicebergmv PARTITION BY DATE_TRUNC(birth_month, MONTH) AS SELECT * FROM mydataset.myicebergtable;
Limitazioni
Oltre alle limitazioni delle tabelle Iceberg standard, viste materializzate Le tabelle Iceberg hanno le seguenti limitazioni:
- Puoi creare una vista materializzata che sia allineata alla partizione
tabella. Tuttavia, la vista materializzata supporta solo la trasformazione della partizione basata sul tempo.
ad esempio
YEAR
,MONTH
,DAY
eHOUR
. - La granularità della partizione della vista materializzata non può essere inferiore alla
granularità della partizione della tabella di base. Ad esempio, se suddividi
tabella di base annuale utilizzando la colonna
birth_date
, creando una vista materializzata conPARTITION BY DATE_TRUNC(birth_date, MONTH)
non funziona. - Qualsiasi modifica allo schema invalida la vista materializzata.
- Le evoluzioni delle partizioni sono supportate. Tuttavia, la modifica delle colonne di partizionamento di una tabella di base senza ricreare la vista materializzata, potrebbe causare un'invalidazione completa che non possono essere corrette tramite aggiornamento.
- Nella tabella di base deve essere presente almeno uno snapshot.
- La tabella Iceberg deve essere una tabella BigLake, ad esempio una tabella esterna autorizzata.
- Se Controlli di servizio VPC è attivato, gli account di servizio della tabella esterna autorizzata devono essere aggiunti alle regole di ingresso, altrimenti Controlli di servizio VPC blocca l'aggiornamento automatico in background della vista materializzata.
Il file metadata.json
della tabella Iceberg deve avere
le seguenti specifiche. Senza queste specifiche, le query eseguono la scansione della tabella di base senza utilizzare il risultato materializzato.
-
current-snapshot-id
current-schema-id
snapshots
snapshot-log
Negli snapshot:
parent-snapshot-id
(se disponibile)schema-id
operation
(nel camposummary
)
Partizionamento (per la vista materializzata partizionata)
Viste materializzate partizionate
Le viste materializzate nelle tabelle partizionate possono essere partizionate. Il partizionamento di un è simile al partizionamento di una tabella normale, in quanto fornisce e offre un vantaggio quando le query spesso accedono a un sottoinsieme delle partizioni. Inoltre, il partizionamento di una vista materializzata può migliorare il comportamento della vista quando i dati la tabella o le tabelle di base vengono modificate o eliminate. Per ulteriori informazioni, vedi Allineamento di partizione.
Se la tabella di base è partizionata, puoi eseguire il partizionamento di una vista materializzata la stessa colonna di partizionamento. Per le partizioni basate sul tempo, la granularità deve corrispondenza (orale, giornaliera, mensile o annuale). Per le partizioni di intervalli interi, la specifica dell'intervallo di date deve corrispondere esattamente. Non puoi eseguire il partizionamento di una vista materializzata su una tabella di base non partizionata.
Se la tabella di base è partizionata per data di importazione, una vista materializzata può
il raggruppamento in base alla colonna _PARTITIONDATE
della tabella di base e la partizione in base a questa.
Se non specifichi esplicitamente la partizione quando crei la vista materializzata, la vista materializzata non è partizionata.
Se la tabella di base è partizionata, ti consigliamo di partizionare anche la vista materializzata per ridurre i costi di manutenzione del job di aggiornamento e di query.
Scadenza partizione
La scadenza della partizione non può essere impostata sulle viste materializzate. Una vista materializzata eredita implicitamente la data di scadenza della partizione dalla tabella di base. Le partizioni delle viste materializzate sono allineate con quelle 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 raggruppati 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 ora di importazione con
partizioni di Compute Engine. La vista materializzata seleziona la data e l'ora di importazione come colonna denominata
date
. La vista materializzata è raggruppata in base alla colonna date
e partizionata per
la 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
colonna denominata transaction_hour
, utilizzando TIMESTAMP_TRUNC
per troncare il valore all'ora più vicina. La vista materializzata è
raggruppate per transaction_hour
e anche partizionate per questo.
Tieni presente quanto segue:
La funzione di troncamento applicata alla colonna di partizionamento deve essere granulare almeno quanto il partizionamento della tabella di base. Ad esempio, se la tabella di base utilizza partizioni giornaliere, la funzione di troncamento non può utilizzare Livello di granularità:
MONTH
oYEAR
.Nella specifica della partizione della vista materializzata, la granularità deve corrispondono alla 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 rispettive colonne di output, in base alle Tabella in cluster BigQuery limitazioni. Le colonne di output aggregate non possono essere utilizzate come colonne di clustering. Aggiunta del clustering in corso... colonne alle viste materializzate può migliorare le prestazioni delle query che includere filtri in queste colonne.
Visualizzazioni logiche di riferimento
Per richiedere assistenza o feedback per questa funzione, invia un'email a bq-mv-help@google.com.
Le query sulle viste materializzate possono fare riferimento alle viste logiche, ma sono soggette alle seguenti limitazioni:
- Si applicano limitazioni delle viste materializzate.
- Se la visualizzazione logica cambia, la visualizzazione materializzata diventa non valida e deve essere aggiornata completamente.
- La ottimizzazione intelligente non è supportata.
Considerazioni per la creazione di viste materializzate
Quali viste materializzate creare
Quando crei una vista materializzata, assicurati che la definizione della vista materializzata riflette i pattern di query rispetto alle tabelle di base. Poiché esiste un massimo di 20 viste materializzate per tabella, non devi creare una vista materializzata ogni permutazione di una query. Crea invece viste materializzate per pubblicare un un insieme più ampio di query.
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, cluster
da questi elementi, invece di aggiungere filtri come user_id = 123
nel file
vista.
Per fare un altro esempio, gli utenti spesso usano filtri della data per data specifica,
ad esempio WHERE order_date = CURRENT_DATE()
o un intervallo di date, ad esempio WHERE order_date
BETWEEN '2019-10-01' AND '2019-10-31'
. Aggiungi un filtro dell'intervallo di date in
la 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 applicano alle viste materializzate con JOIN.
Metti al primo posto la tabella che cambia di frequente
Assicurati che la tabella più grande o che cambia più di frequente sia la prima/la più a sinistra a cui viene fatto riferimento nella query di visualizzazione. Viste materializzate con supporto dei join incrementali e si aggiornano quando si trova la prima tabella o la tabella più a sinistra nella query , ma le modifiche ad altre tabelle invalidano completamente la cache di visualizzazione. Nel schemi a stella o a fiocco di neve, la prima tabella o la tabella più a sinistra in genere deve essere delle informazioni.
Evita l'unione sulle chiavi di clustering
Le viste materializzate con join funzionano meglio nei casi in cui i dati siano pesantemente o la query di join originale è costosa. Per le query selettive, BigQuery è spesso in grado di eseguire il join in modo efficiente e non è necessaria alcuna vista materializzata. Ad esempio, considera quanto segue: definizioni della vista materializzata.
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
ed esegui 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 quanto la query originale. Per migliori, sperimentare con un insieme rappresentativo di query, con e senza vista materializzata.
Utilizzare le viste materializzate con l'opzione max_staleness
L'opzione di vista materializzata max_staleness
ti aiuta a ottenere risultati costantemente elevati
prestazioni con costi controllati durante l'elaborazione di grandi quantità e modifiche frequenti
e set di dati. Con il parametro max_staleness
, puoi regolare l'aggiornamento del
per ottimizzare le prestazioni
delle query. Questo comportamento può essere utile per le dashboard
e i report per i quali l'aggiornamento dei dati non è essenziale.
In ritardo dei dati
Quando esegui query sulle viste materializzate con max_staleness
, BigQuery
restituisce dati coerenti con il risultato di una query di vista materializzata che è stata
nell'intervallo di max_staleness
.
La query viene eseguita in base alle seguenti condizioni:
Se l'ultimo aggiornamento rientra nell'intervallo di
max_staleness
: BigQuery restituisce i dati direttamente dalla vista materializzata senza leggere le tabelle di base.Se l'ultimo aggiornamento non rientra nell'intervallo
max_staleness
, la query legge i dati dalle tabelle di base per restituire i risultati in caso di mancato aggiornamento intervallo di tempo.
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 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 vista materializzata.
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
API
Chiama il tables.insert
con una risorsa materializedView
definita come parte dell'API
richiesta. La risorsa materializedView
contiene un campo query
. Per
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 set di dati che funge da tabella di base per la vista materializzata.
product_id
è una colonna della tabella di base.clicks
è una colonna della tabella di base.sum_clicks
è una colonna nella vista materializzata che stai creando.
Applica opzione max_staleness
Puoi applicare questo parametro alle visualizzazioni 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
eseguire query su qualsiasi altra vista materializzata, vista logica o tabella.
Ad esempio:
SELECT * FROM project-id.my_dataset.my_mv_table
Questa query restituisce i dati dall'ultimo aggiornamento se questi non sono più vecchi della
Parametro max_staleness
. Se la vista materializzata non è stata aggiornata
nell'intervallo max_staleness
, BigQuery unisce i risultati
ultimo aggiornamento disponibile con le modifiche alla tabella di base per restituire i risultati entro
l'intervallo di max_staleness
.
Streaming di dati e risultati max_staleness
Se carichi i dati nelle tabelle di base di una vista materializzata con l'opzione max_staleness
, la query della vista materializzata potrebbe escludere i record caricati nelle tabelle prima dell'inizio dell'intervallo di inattività. Di conseguenza, una vista materializzata che include i dati di più tabelle e l'opzione max_staleness
potrebbe non rappresentare uno snapshot istantataneo di queste tabelle.
Ottimizzazione intelligente e opzione max_staleness
L'ottimizzazione intelligente riscrive automaticamente le query per utilizzare le viste materializzate ogni volta che
indipendentemente dall'opzione max_staleness
, anche se la query non
fare riferimento a una vista materializzata. L'opzione max_staleness
in una vista materializzata
non influisce sui risultati della query riscritta. Opzione max_staleness
riguarda solo le query che eseguono query direttamente sulla vista materializzata.
Gestire la frequenza di aggiornamento e l'obsolescenza
Dovresti impostare max_staleness
in base ai tuoi requisiti. Per evitare di leggere
dalle tabelle di base, configura l'intervallo di aggiornamento in modo che
all'interno dell'intervallo di inattività. Puoi tenere conto dell'aggiornamento medio
più un margine di crescita.
Ad esempio, se è necessaria un'ora per aggiornare la vista materializzata 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 all'interno massimo di quattro ore in caso di mancato aggiornamento.
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, tra cui le clausole OUTER
JOIN
, UNION
e HAVING
e le funzioni di analisi. Per determinare
se nella query è stata utilizzata una vista materializzata, controlla le stime dei costi
con una prova.
In scenari in cui
l'inattività dei dati sia accettabile, ad esempio per l'elaborazione dei dati in batch
nei report, le viste materializzate non incrementali possono migliorare le prestazioni delle query
e ridurre i costi. Utilizzando l'opzione max_staleness
, puoi creare in modo arbitrario,
viste materializzate complesse, gestite automaticamente e dotate di
garanzie di obsolescenza.
Utilizza viste materializzate non incrementali
Puoi creare viste materializzate non incrementali utilizzando l'opzioneallow_non_incremental_definition
. Questa opzione deve essere accompagnata dalla dicitura
l'opzione max_staleness
. Per garantire un aggiornamento periodico delle informazioni
, devi anche configurare un aggiornamento
.
Senza un criterio di aggiornamento, devi aggiornare manualmente la vista materializzata.
La vista materializzata rappresenta sempre lo stato delle tabelle di base all'interno del
Intervallo di max_staleness
. Se l'ultimo aggiornamento è troppo in ritardo e non rappresenta le tabelle di base nell'intervallo max_staleness
, la query legge le tabelle di base. Per ulteriori informazioni sulle possibili implicazioni in termini di rendimento, consulta Informazioni
di inattività.
Crea con allow_non_incremental_definition
Per creare una vista materializzata con allow_non_incremental_definition
segui questa procedura. Dopo aver creato la vista materializzata, non puoi
modifica l'opzione allow_non_incremental_definition
. Ad esempio, non puoi
modifica il valore true
in false
o rimuovi il
allow_non_incremental_definition
dalla vista materializzata.
SQL
Aggiungi una clausola OPTIONS
all'istruzione DDL quando crei l'oggetto
vista materializzata:
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'editor 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 risorsa vista 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.
API
Chiama il tables.insert
con una risorsa materializedView
definita come parte dell'API
richiesta. La risorsa materializedView
contiene un campo query
. Per
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 in il 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 tabella, vista logica o vista materializzata.
Ad esempio:
SELECT * FROM my_project.my_dataset.my_mv_table
Se i dati non sono precedenti al parametro max_staleness
, questa query
restituisce i dati dell'ultimo aggiornamento. Per i dettagli relativi ai problemi di mancato aggiornamento
l'aggiornamento dei dati, controlla l'inattività dei dati.
Limitazioni specifiche alle viste materializzate non incrementali
Le seguenti limitazioni si applicano solo alle viste materializzate con
Opzione allow_non_incremental_definition
. Ad eccezione delle limitazioni
sintassi delle query supportata, tutte le viste materializzate
rimangono valide.
- L'ottimizzazione intelligente non viene applicata alle viste materializzate che includono
Opzione
allow_non_incremental_definition
. L'unico modo per usufruire delle viste materializzate con l'opzioneallow_non_incremental_definition
è eseguire query direttamente su di esse. - Viste materializzate senza l'opzione
allow_non_incremental_definition
possono aggiornare in modo incrementale un sottoinsieme dei loro dati. Le viste materializzate con l'opzioneallow_non_incremental_definition
devono essere aggiornate interamente. - Le viste materializzate con l'opzione max_staleness convalidano la presenza di vincoli di sicurezza a livello di colonna durante l'esecuzione delle query. Visualizza altri dettagli su questo argomento nel controllo dell'accesso a livello di colonna.