Crea viste materializzate

Questo documento descrive come creare visualizzazioni materializzate in BigQuery. Prima di leggere il presente documento, acquisisci familiarità con consulta 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à in questo documento.

Autorizzazioni obbligatorie

Per creare viste materializzate, è necessario bigquery.tables.create Autorizzazione IAM.

Ciascuno dei seguenti ruoli IAM predefiniti include le 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 CREATE MATERIALIZED VIEW. L'esempio seguente crea una vista materializzata per il numero di clic per ciascun ID prodotto:

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. 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 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 esempio my_mv.
    • QUERY_EXPRESSION: l'espressione di query GoogleSQL che definisce la vista materializzata, ad esempio SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

  3. 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 risorsa google_bigquery_table.

Per autenticarti a BigQuery, configura le credenziali predefinite per l'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

Il seguente esempio crea 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 a un progetto Google Cloud, completa i passaggi nella le sezioni seguenti.

Prepara Cloud Shell

  1. Avvia Cloud Shell.

  2. Imposta il progetto Google Cloud predefinito in cui vuoi applicare le configurazioni Terraform.

    Devi eseguire questo comando una sola volta per progetto e puoi farlo in qualsiasi directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Le variabili di ambiente vengono sostituite se imposti valori espliciti nel file di configurazione di Terraform.

Prepara la directory

Ogni file di configurazione Terraform deve avere una directory dedicata (inoltre chiamato modulo principale).

  1. In Cloud Shell, crea una directory e un nuovo file al suo interno. Il nome file deve avere l'estensione .tf, ad esempio main.tf. In questo tutorial, il file è denominato main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Se stai seguendo un tutorial, puoi copiare il codice campione in ogni sezione o passaggio.

    Copia il codice campione nel nuovo oggetto main.tf.

    Se vuoi, copia il codice da GitHub. Questa opzione è consigliata quando lo snippet Terraform fa parte di una soluzione end-to-end.

  3. Esamina e modifica i parametri di esempio da applicare al tuo ambiente.
  4. Salva le modifiche.
  5. 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

  1. Rivedi la configurazione e verifica che le risorse che Terraform sta per creare o aggiornare corrispondano alle tue aspettative: 
    terraform plan

    Apporta le correzioni necessarie alla configurazione.

  2. Applica la configurazione Terraform eseguendo questo comando e inserendo yes alla richiesta: 
    terraform apply

    Attendi finché Terraform non visualizzi il messaggio "Applicazione completata!". per creare un nuovo messaggio email.

  3. 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 vista materializzata in, ad esempio mydataset. 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 esempio my_mv.
  • QUERY_EXPRESSION: l'espressione di query 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 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 di configurazione Java riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, 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());
    }
  }
}

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:

Schema delle viste materializzate nella console Google Cloud

A meno che non disattivi l'aggiornamento automatico, BigQuery avvia un aggiornamento completo asincrono per la vista materializzata. 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, livello di vista o 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 alla sua base tabelle. Per condividere una vista materializzata, puoi concedere le autorizzazioni alla o configurare una vista materializzata come vista autorizzata. Per ulteriori informazioni, consulta Visualizzazioni autorizzate.

Per controllare l'accesso alle visualizzazioni 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. Computing, filtri o l'unione in base a un valore aggregato non è supportata. Ad esempio, la creazione di una visualizzazione dalla seguente query 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;

Al momento sono supportate solo le seguenti funzioni di aggregazione:

  • ANY_VALUE (ma non più di STRUCT)
  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG (ma non più di 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 più di STRUCT)
  • MIN_BY (ma non più di STRUCT)
  • SUM

Funzionalità SQL non supportate

Le seguenti funzionalità SQL non sono supportate nelle viste materializzate:

Assistenza di LEFT OUTER JOIN e UNION ALL

Per richiedere assistenza o feedback per questa funzione, invia un'email a 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 memorizzazione sotto indizio con union all o join esterno sinistro.

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 di 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 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
);

Restrizioni del controllo degli accessi

  • 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. In questo modo, la query rispetterà tutti i vincoli di controllo dell'accesso. Questa limitazione si applica anche quando si eseguono query sulle tabelle con colonne con mascheramento dei dati.

Clausola WITH ed espressioni di tabella comuni (CTE)

Le viste materializzate supportano le clausole WITH e le espressioni di tabelle 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 visualizzazioni materializzate sulle tabelle BigLake, la tabella BigLake deve avere la memorizzazione nella cache dei metadati attivata sui dati di Cloud Storage e la visualizzazione 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.

Visualizzazioni materializzate nelle tabelle Apache Iceberg

Per richiedere feedback o assistenza per questa funzionalità, invia un'email all'indirizzo 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 su un'iceberg:

  1. Ottenere una tabella Iceberg utilizzando uno dei seguenti metodi metodo:

    Esempio

    CREATE EXTERNAL TABLE mydataset.myicebergtable
  WITH CONNECTION `myproject.us.myconnection`
  OPTIONS (
        format = 'ICEBERG',
        uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
  )

  2. 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
} ]

  3. 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 allineata alla partizione con la tabella di base. Tuttavia, la vista materializzata supporta solo la trasformazione delle partizioni in base al tempo, ad esempio YEAR, MONTH, DAY e HOUR.
  • La granularità della partizione della vista materializzata non può essere più fine 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 con PARTITION BY DATE_TRUNC(birth_date, MONTH) non funziona.
  • Qualsiasi modifica allo schema invalida la vista materializzata.
  • Evoluzioni delle partizioni è supportato. Tuttavia, la modifica delle colonne di partizione di una tabella di base senza ricreare la vista materializzata potrebbe comportare un'invalidazione completa che non può essere corretta con l'aggiornamento.
  • Nella tabella di base deve essere presente almeno uno snapshot.
  • Il tavolo Iceberg deve essere un BigLake come 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 contenere le seguenti specifiche. Senza queste specifiche, le query eseguono la scansione della tabella di base senza utilizzare il risultato materializzato.

  • Nei metadati della tabella:

    • current-snapshot-id
    • current-schema-id
    • snapshots
    • snapshot-log

  • Negli snapshot:

    • parent-snapshot-id (se disponibile)
    • schema-id
    • operation (nel campo summary)

  • 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 partizionare una vista materializzata sulla stessa colonna di partizionamento. Per le partizioni basate sul tempo, la granularità deve essere uguale (ora, giorno, mese o anno). Per le partizioni di intervalli interi, la specifica dell'intervallo di date deve corrispondere esattamente. Non puoi partizionare 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, valuta la possibilità di partizionare la vista materializzata nonché per ridurre la manutenzione del job di aggiornamento e il costo delle query.

Scadenza partizione

La scadenza della partizione non può essere impostata nelle viste materializzate. Una vista materializzata eredita implicitamente la data di scadenza della partizione dalla tabella di base. Le partizioni delle viste materializzate 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 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 è agrupata per transaction_hour e partizionata in base a questo attributo.

Tieni presente quanto segue:

  • La funzione di troncamento applicata alla colonna di partizionamento deve essere almeno granulare quanto la partizione 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 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 in 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:

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é esistono al massimo 20 viste materializzate per tabella, non devi creare una vista materializzata per ogni permutazione di una query. Crea invece viste materializzate per soddisfare 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, raggrupparle, anziché aggiungere filtri come user_id = 123 alla visualizzazione materializzata.

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 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 consigli riportati di seguito 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 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 alle altre tabelle invalidano completamente la cache di visualizzazione. Nella 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 su chiavi di clustering

Le viste materializzate con join funzionano meglio nei casi in cui i dati siano pesantemente aggregate 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 le seguenti definizioni di visualizzazioni materializzate.

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

Utilizza le viste materializzate con l'opzione max_staleness

L'opzione max_staleness delle viste materializzate ti consente di ottenere un rendimento costante delle query 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 delle query è accettabile. Questo comportamento può essere utile per le dashboard e i report per i quali i risultati delle query completamente aggiornati non sono essenziali.

In ritardo 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 l'ora in cui si è verificato l'ultimo aggiornamento.

Se l'ultimo aggiornamento si è verificato nell'intervallo di max_staleness: BigQuery restituisce i dati direttamente dalla vista materializzata senza leggere le tabelle di base. Ad esempio, questo vale se l'intervallomax_staleness è di 4 ore e l'ultimo aggiornamento è avvenuto 2 ore fa.

Se l'ultimo aggiornamento si è verificato al di fuori dell'intervallo max_staleness: BigQuery legge i dati dalla vista materializzata, li combina con modifiche alla tabella di base dall'ultimo aggiornamento, e restituisce il token o il risultato finale. Questo risultato combinato potrebbe essere ancora inattivo, fino all'intervallo max_staleness. Ad esempio, ciò si applica se l'intervallo di 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 OPTIONS all'istruzione DDL quando crei la vista materializzata:

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. 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 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 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);

Esegui una 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 in un intervallo max_staleness, BigQuery unisce i risultati dell'ultimo aggiornamento disponibile con le modifiche alla tabella di base per restituire risultati nell'intervallomax_staleness.

Flusso di dati e max_staleness risultati

Se trasmetti i dati nelle tabelle di base di una vista materializzata con max_staleness, la query della vista materializzata potrebbe escludere di flussi di dati trasmessi nelle rispettive tabelle prima dell'inizio dello stato di inattività intervallo di tempo. 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

La regolazione intelligente riscrive automaticamente le query per 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 si applica solo alle query che eseguono query direttamente sulla vista materializzata.

Gestire la frequenza di aggiornamento e l'obsolescenza

Devi 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 un runtime 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 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 utilizzando un simulacro. Negli scenari in cui l'obsolescenza dei dati è accettabile, ad esempio per l'elaborazione o la generazione di report dei dati batch, le viste materializzate non incrementali possono migliorare le prestazioni delle query e ridurre i costi. Utilizzando l'opzione max_staleness, puoi creare viste materializzate complesse e arbitrarie che vengono mantenute automaticamente e dispongono di garanzie di sfasamento integrate.

Utilizzare viste materializzate non incrementali

Puoi creare viste materializzate non incrementali utilizzando il metodo Opzione allow_non_incremental_definition. Questa opzione deve essere accompagnata dall'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 scoprire di più sulle possibili implicazioni sul rendimento, consulta Inattualità 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 cambiare 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:

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. 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 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.

  3. Fai clic su Esegui.

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

API

Chiama il metodo tables.insert con una risorsa materializedView definita all'interno della richiesta dell'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 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 per le viste materializzate non incrementali

Le seguenti limitazioni si applicano solo alle viste materializzate con Opzione allow_non_incremental_definition. Ad eccezione delle limitazioni alla sintassi delle query supportate, continuano a essere valide tutte le limitazioni delle visualizzazioni materializzate.

  • 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'opzione allow_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'opzione allow_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.

Passaggi successivi