Crea viste materializzate

Questo documento descrive come creare viste materializzate in BigQuery. Prima di leggere questo documento, consulta la sezione Introduzione alle viste materializzate.

Prima di iniziare

Concedi i ruoli IAM (Identity and Access Management) che concedono agli utenti le autorizzazioni necessarie per eseguire ogni attività nel documento.

Autorizzazioni obbligatorie

Per creare viste materializzate, devi disporre dell'autorizzazione IAM bigquery.tables.create.

Ciascuno dei seguenti ruoli IAM predefiniti include le autorizzazioni necessarie per creare una vista materializzata:

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

Per saperne di più 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. L'esempio seguente crea una vista materializzata per il numero di clic per ogni ID prodotto:

Nella console Google Cloud, vai alla pagina BigQuery.

Vai a BigQuery
Nell'Editor 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 progetto in cui vuoi creare la vista materializzata, ad esempio myproject.
- DATASET: il nome del set di dati BigQuery in cui vuoi creare la vista materializzata, ad esempio mydataset. 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 esempio my_mv.
- QUERY_EXPRESSION: l'espressione di query di GoogleSQL che definisce la vista materializzata, ad esempio SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.
Fai clic su Esegui.

Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.

Esempio

L'esempio seguente 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.

Nell'esempio seguente viene creata una vista denominata my_materialized_view:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "my_materialized_view"
  deletion_protection = false # set to "true" in production

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

Per applicare la configurazione Terraform in un progetto Google Cloud, completa i passaggi nelle sezioni seguenti.

prepara Cloud Shell

Avvia Cloud Shell.
Imposta il progetto Google Cloud predefinito a 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
```
Se imposti valori espliciti nel file di configurazione Terraform, le variabili di ambiente vengono sostituite.

Prepara la directory

Ogni file di configurazione Terraform deve avere una propria directory (detta anche modulo principale).

In Cloud Shell, crea una directory e un nuovo file al suo interno. Il nome del file deve avere l'estensione .tf, ad esempio main.tf. In questo tutorial, il file è indicato come main.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.

Se vuoi, copia il codice da GitHub. Questa opzione è consigliata se 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 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 Terraform eseguendo il comando seguente e inserendo yes al prompt:
```
terraform apply
```
Attendi finché Terraform non visualizza 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 passa una Tablerisorsa 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 progetto in cui vuoi creare la vista materializzata, ad esempio myproject.
DATASET: il nome del set di dati BigQuery in cui vuoi creare la vista materializzata, ad esempio mydataset. 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 esempio my_mv.
QUERY_EXPRESSION: l'espressione di query di GoogleSQL che definisce la vista materializzata, ad esempio SELECT 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 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 disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

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 una vista materializzata:

Schema della vista materializzata nella console Google Cloud

A meno che non disattivi l'aggiornamento automatico, BigQuery avvia un aggiornamento completo asincrono della vista materializzata. La query termina rapidamente, ma l'esecuzione dell'aggiornamento iniziale potrebbe continuare.

Controllo dell'accesso

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 sue 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 scoprire di più, 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 aggregati

Gli aggregati nella query della vista materializzata devono essere output. Il computing, l'applicazione di filtri o l'unione in base a un valore aggregato non sono supportati. Ad esempio, la creazione di una vista dalla query seguente non è supportata perché produce un valore calcolato 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;

Attualmente sono supportate solo le seguenti funzioni di aggregazione:

ANY_VALUE (ma non superiore a STRUCT)
APPROX_COUNT_DISTINCT
ARRAY_AGG (ma non superiore a ARRAY o STRUCT)
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
MAX_BY (ma non superiore a STRUCT)
MIN_BY (ma non superiore a STRUCT)
SUM

Funzionalità SQL non supportate

Le seguenti caratteristiche SQL non sono supportate nelle viste materializzate:

UNION ALL (Assistenza nell'anteprima )
LEFT OUTER JOIN (assistenza nell'anteprima )
RIGHT/FULL OUTER JOIN.
Self-join, anche noto come utilizzo di JOIN nella stessa tabella più volte.
Funzioni finestra.
ARRAY sottoquery.
Funzioni non deterministiche come RAND(), CURRENT_DATE(), SESSION_USER() o CURRENT_TIME().
Funzioni definite dall'utente.
TABLESAMPLE.
FOR SYSTEM_TIME AS OF.

Assistenza `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 viste materializzate con le istruzioni LEFT OUTER JOIN e UNION ALL condividono le limitazioni di altre viste materializzate incrementali. Inoltre, l'ottimizzazione 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, 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 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 restituisce il messaggio Access Denied.
Se un utente esegue una query su una vista materializzata, ma non ha accesso completo a tutte le righe nelle tabelle di base delle viste materializzate, BigQuery esegue la query sulle tabelle di base anziché leggere i dati della vista materializzata. Ciò garantisce che la query rispetti tutti i vincoli di controllo dell'accesso. Questo limite si applica anche all'esecuzione di query su tabelle con colonne mascherate dai dati.

Clausola `WITH` ed espressioni di tabella comuni (CTE)

Le viste materializzate supportano clausole WITH ed espressioni di tabella comuni. Le viste materializzate con clausole WITH devono comunque seguire lo schema 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 che 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, nella tabella BigLake deve essere attivata la memorizzazione nella cache dei metadati sui dati di Cloud Storage e la vista materializzata deve avere un valore di opzione max_staleness maggiore rispetto alla tabella di base. Le viste materializzate sulle tabelle BigLake supportano lo stesso insieme di query delle altre viste materializzate.

Esempio

Creazione di una semplice visualizzazione 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 i dettagli sui limiti delle viste materializzate sulle tabelle BigLake, consulta Viste materializzate sulle tabelle BigLake.

Viste materializzate partizionate

Le viste materializzate nelle 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 spesso accedono a un sottoinsieme delle partizioni. Inoltre, il partizionamento di una vista materializzata può migliorare il comportamento della vista quando i dati nella tabella o nelle tabelle di base vengono modificati o eliminati. Per maggiori informazioni, consulta 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 (ora, giornaliera, mensile o annuale). Per le partizioni di intervalli interi, la specifica dell'intervallo deve corrispondere esattamente. Non puoi partizionare una vista materializzata in una tabella di base non partizionata.

Se la tabella di base è partizionata in base alla data e ora di importazione, una vista materializzata può essere raggruppata in base alla colonna _PARTITIONDATE della tabella di base e anche in base a quest'ultima. Se non specifichi in modo esplicito il partizionamento quando crei la vista materializzata, la vista materializzata non sarà 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 quelli delle query.

Scadenza partizione

La scadenza della partizione non può essere impostata per le viste materializzate. Una vista materializzata eredita implicitamente la 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 nella colonna transaction_time con partizioni giornaliere. La vista materializzata è partizionata sulla stessa colonna e raggruppata 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 in base alla data e all'ora di importazione con partizioni giornaliere. 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 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 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 per transaction_hour e partizionata in base a quest'ultima.

Tieni presente quanto segue:

La funzione di troncamento applicata alla colonna di partizionamento deve essere più granulare rispetto al 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 o YEAR.
Nella specifica della partizione della vista materializzata, la granularità deve corrispondere 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 relative colonne di output, in base alle limitazioni delle tabelle in cluster di BigQuery. Le colonne di output aggregate 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 i filtri su queste colonne.

Fare riferimento a 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 alla vista materializzata.
Se la visualizzazione logica cambia, la vista materializzata non è più valida e deve essere completamente aggiornata.
L'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 rifletti i pattern di query con le tabelle di base. Poiché esiste un massimo di 20 viste materializzate per tabella, non devi creare una vista materializzata per ogni permutazione di una query. Crea viste materializzate per gestire 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, creare un cluster in base a queste, anziché aggiungere filtri come user_id = 123 nella vista materializzata.

Per fare un altro esempio, gli utenti spesso utilizzano i filtri della data, per data specifica, ad esempio WHERE order_date = CURRENT_DATE(), o per intervallo di date, come WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. Aggiungi un filtro dell'intervallo di date nella vista materializzata che copra gli intervalli di date previsti nella query:

CREATE MATERIALIZED VIEW ...
  ...
  WHERE date > '2019-01-01'
  GROUP BY date

Unioni

I suggerimenti seguenti si applicano alle viste materializzate con i JOIN.

Posiziona la tabella che cambia più frequentemente

Assicurati che la tabella più grande o che cambia più di frequente sia la prima tabella a sinistra a cui viene fatto riferimento nella query di visualizzazione. Le viste materializzate con join supportano le query incrementali e si aggiornano quando viene aggiunta la prima tabella o la più a sinistra della query, ma le modifiche ad altre tabelle rendono la cache della visualizzazione completa. Negli schemi a stella o a fiocco di neve, in genere, la prima tabella o la tabella più a sinistra deve essere la tabella dei fatti.

Evita di unire le chiavi di clustering

Le viste materializzate con join funzionano meglio nei casi in cui i dati siano molto aggregati o la query di join originale è costosa. Per le query selettive, BigQuery è spesso in grado di eseguire l'unione in modo efficiente e non è necessaria alcuna vista materializzata. Ad esempio, prendiamo in considerazione le seguenti definizioni dellevista 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 in cluster su ss_store_sk e spesso esegui query come queste:

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, sperimenta con un insieme rappresentativo di query, con e senza la vista materializzata.

Utilizza le viste materializzate con l'opzione `max_staleness`

L'opzione della vista materializzata max_staleness consente di ottenere prestazioni costantemente elevate con costi controllati durante l'elaborazione di set di dati di grandi dimensioni e che cambiano spesso. Con il parametro max_staleness, puoi regolare l'aggiornamento dei risultati 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.

Inattività dei dati

Quando esegui query sulle viste materializzate con max_staleness, BigQuery restituisce dati coerenti con il risultato di una query relativa a una vista materializzata eseguita nell'intervallo max_staleness.

La query viene eseguita in base alle seguenti condizioni:

Se l'ultimo aggiornamento rientra nell'intervallo 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 delle tabelle di base per restituire i risultati entro l'intervallo di inattività.

Crea con l'opzione `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.

Vai a 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
FROM my_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 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 metodo tables.insert con una risorsa materializedView definita come parte della tua 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 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 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 precedenti al 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 di base per restituire i risultati entro l'intervallo max_staleness.

Flussi di dati e `max_staleness` risultati

Se trasmetti i dati in flussi nelle tabelle di base di una vista materializzata con l'opzione max_staleness, la query della vista materializzata potrebbe escludere i record trasmessi nelle sue 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 point-in-time di queste tabelle.

Sintonizzazione intelligente e opzione `max_staleness`

L'ottimizzazione intelligente riscrive automaticamente le query in modo da utilizzare le viste materializzate ove 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 interessa solo le query che eseguono query direttamente sulla vista materializzata.

Gestisci il mancato aggiornamento e la frequenza di aggiornamento

Devi impostare max_staleness in base ai tuoi requisiti. Per evitare di leggere i dati delle tabelle di base, configura l'intervallo di aggiornamento in modo che avvenga all'interno dell'intervallo di obsolescenza. Puoi prendere in considerazione il tempo di esecuzione medio dell'aggiornamento più un margine di 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 assicura che l'aggiornamento avvenga entro il limite massimo di quattro ore per il mancato aggiornamento 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;

Visualizzazioni 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. In scenari in cui è accettabile l'inattività dei dati, ad esempio per l'elaborazione o la generazione di report in batch dei dati, 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 inattività integrate.

Utilizza 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 un criterio di aggiornamento. Senza un criterio di aggiornamento, devi aggiornare manualmente la vista materializzata.

La vista materializzata rappresenta sempre lo stato delle tabelle di base nell'intervallo max_staleness. Se l'ultimo aggiornamento è troppo inattivo e non rappresenta le tabelle di base all'interno dell'intervallo max_staleness, la query legge le tabelle di base. Per scoprire di più sulle possibili implicazioni per le prestazioni, consulta Stato di inattività dei dati.

Crea con `allow_non_incremental_definition`

Per creare una vista materializzata con l'opzione allow_non_incremental_definition, segui questi passaggi. 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.

Vai a 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 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 ulteriori 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 tua 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 precedenti al parametro max_staleness, questa query restituisce i dati dell'ultimo aggiornamento. Per maggiori dettagli sul livello di obsolescenza e aggiornamento dei dati, consulta Inattività 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 della sintassi delle query supportate, vengono comunque applicate tutte le limitazioni delle viste materializzate.

L'ottimizzazione intelligente non viene applicata alle viste materializzate che includono l'opzione allow_non_incremental_definition. L'unico modo per trarre vantaggio dalle viste materializzate con l'opzione allow_non_incremental_definition è eseguire query dirette.
Le viste materializzate senza l'opzione allow_non_incremental_definition possono aggiornare in modo incrementale un sottoinsieme di dati. Le viste materializzate con l'opzione allow_non_incremental_definition devono essere aggiornate nella loro interezza.
Le viste materializzate con l'opzione max_staleness convalida la presenza di vincoli di sicurezza a livello di colonna durante l'esecuzione della query. Scopri di più in merito nel controllo dell'accesso a livello di colonna

Crea viste materializzate

Prima di iniziare

Autorizzazioni obbligatorie

Crea viste materializzate

SQL

Terraform

prepara Cloud Shell

Prepara la directory

Applica le modifiche

API

Java

Controllo dell'accesso

Supporto delle query sulle viste materializzate

Limitazioni delle query

Requisiti aggregati

Funzionalità SQL non supportate

Assistenza LEFT OUTER JOIN e UNION ALL

Esempi

Limitazioni del controllo dell'accesso

Clausola WITH ed espressioni di tabella comuni (CTE)

Esempi

Viste materializzate sulle tabelle BigLake

Esempio

Viste materializzate partizionate

Scadenza partizione

Esempio 1

Esempio 2

Esempio 3

Viste materializzate del cluster

Fare riferimento a viste logiche

Considerazioni per la creazione di viste materializzate

Quali viste materializzate creare

Unioni

Posiziona la tabella che cambia più frequentemente

Evita di unire le chiavi di clustering

Utilizza le viste materializzate con l'opzione max_staleness

Inattività dei dati

Crea con l'opzione max_staleness

SQL

API

Applica l'opzione max_staleness

Query con max_staleness

Flussi di dati e max_staleness risultati

Sintonizzazione intelligente e opzione max_staleness

Gestisci il mancato aggiornamento e la frequenza di aggiornamento

Visualizzazioni materializzate non incrementali

Utilizza viste materializzate non incrementali

Crea con allow_non_incremental_definition

SQL

API

Query con allow_non_incremental_definition

Limitazioni specifiche per le viste materializzate non incrementali.

Passaggi successivi

Assistenza `LEFT OUTER JOIN` e `UNION ALL`

Clausola `WITH` ed espressioni di tabella comuni (CTE)

Utilizza le viste materializzate con l'opzione `max_staleness`

Crea con l'opzione `max_staleness`

Applica l'opzione `max_staleness`

Query con `max_staleness`

Flussi di dati e `max_staleness` risultati

Sintonizzazione intelligente e opzione `max_staleness`

Crea con `allow_non_incremental_definition`

Query con `allow_non_incremental_definition`