Gestire i suggerimenti per partizioni e cluster

Questo documento descrive il funzionamento del suggeritore di partizioni e cluster, come visualizzare i suggerimenti e gli approfondimenti e come applicarli.

Come funziona il sistema di raccomandazione

Il sistema di suggerimenti per il partizionamento e il clustering BigQuery genera suggerimenti per il partizionamento o il clustering per ottimizzare le tabelle BigQuery. Il sistema di suggerimenti analizza i workflow nelle tabelle BigQuery e offre suggerimenti per ottimizzare meglio i workflow e i costi delle query utilizzando il partizionamento o il clustering delle tabelle.

Per saperne di più sul servizio Recommender, consulta la panoramica di Recommender.

Il suggeritore di partizionamento e clustering utilizza i dati di esecuzione del carico di lavoro del progetto degli ultimi 30 giorni per analizzare ogni tabella BigQuery alla ricerca di configurazioni di partizionamento e clustering non ottimali. Il sistema di suggerimenti utilizza anche il machine learning per prevedere in che modo l'esecuzione del workload potrebbe essere ottimizzata con diverse configurazioni di partizionamento o clustering. Se il motore per suggerimenti rileva che il partizionamento o il clustering di una tabella comporta risparmi significativi, genera un suggerimento. Il sistema di suggerimenti per il partizionamento e il clustering genera i seguenti tipi di suggerimenti:

Ogni consiglio è composto da tre parti:

• Indicazioni per partizionare o raggruppare una tabella specifica
• La colonna specifica di una tabella da partizionare o raggruppare
• Risparmi mensili stimati per l'applicazione del consiglio

Per calcolare il potenziale risparmio di workload, il sistema di suggerimenti presuppone che i dati storici sul workload di esecuzione degli ultimi 30 giorni rappresentino il workload futuro.

L'API Recommender restituisce anche informazioni sul workload della tabella sotto forma di approfondimenti. Gli approfondimenti sono risultati che ti aiutano a comprendere il carico di lavoro del tuo progetto, fornendo maggiori informazioni su come un suggerimento per una partizione o un cluster potrebbe migliorare i costi del carico di lavoro.

Limitazioni

• Il suggeritore di partizionamento e clustering non supporta le tabelle BigQuery con SQL precedente. Quando genera un suggerimento, il motore per suggerimenti esclude qualsiasi query SQL precedente dalla sua analisi. Inoltre, l'applicazione dei suggerimenti per le partizioni alle tabelle BigQuery con SQL precedente interrompe tutti i flussi di lavoro SQL precedenti in quella tabella.

  Prima di applicare i suggerimenti per le partizioni, esegui la migrazione dei flussi di lavoro SQL precedente a GoogleSQL.

• BigQuery non supporta la modifica dello schema di partizionamento di una tabella sul posto. Puoi modificare il partizionamento di una tabella solo su una copia della tabella. Per ulteriori informazioni, consulta Applicare i suggerimenti per le partizioni.

Località

Il sistema di suggerimenti per il partizionamento e il clustering è disponibile nelle seguenti posizioni di elaborazione:

	Descrizione della regione	Nome regione	Dettagli
Asia Pacifico
	Delhi	asia-south2
	Hong Kong	asia-east2
	Giacarta	asia-southeast2
	Mumbai	asia-south1
	Osaka	asia-northeast2
	Seul	asia-northeast3
	Singapore	asia-southeast1
	Sydney	australia-southeast1
	Taiwan	asia-east1
	Tokyo	asia-northeast1
Europa
	Belgio	europe-west1	Bassi livelli di CO2
	Berlino	europe-west10	Bassi livelli di CO2
	Multiregione UE	eu
	Francoforte	europe-west3	Bassi livelli di CO2
	Londra	europe-west2	Bassi livelli di CO2
	Paesi Bassi	europe-west4	Bassi livelli di CO2
	Zurigo	europe-west6	Bassi livelli di CO2
Americhe
	Iowa	us-central1	Bassi livelli di CO2
	Las Vegas	us-west4
	Los Angeles	us-west2
	Montréal	northamerica-northeast1	Bassi livelli di CO2
	Virginia del Nord	us-east4
	Oregon	us-west1	Bassi livelli di CO2
	Salt Lake City	us-west3
	San Paolo	southamerica-east1	Bassi livelli di CO2
	Toronto	northamerica-northeast2	Bassi livelli di CO2
	Stati Uniti (multiregionale)	us

Prima di iniziare

• Assicurati che Gemini in BigQuery sia abilitato per il tuo progetto Google Cloud .
• Abilita l'API Recommender.

Autorizzazioni obbligatorie

Per ottenere le autorizzazioni necessarie per accedere ai suggerimenti su partizioni e cluster, chiedi all'amministratore di concederti il ruolo IAM BigQuery Partitioning Clustering Recommender Viewer (roles/recommender.bigqueryPartitionClusterViewer). Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per accedere ai suggerimenti per partizioni e cluster. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

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

Visualizza i suggerimenti

Questa sezione descrive come visualizzare i suggerimenti e gli approfondimenti su partizioni e cluster utilizzando la console Google Cloud , Google Cloud CLI o l'API Recommender.

Seleziona una delle seguenti opzioni:

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.table.PartitionClusterRecommender \
    --format=FORMAT_TYPE \

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.table.StatsInsight \
    --format=FORMAT_TYPE \

curl
    -H "Authorization: Bearer $GCLOUD_AUTH_TOKEN"
    -H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/my-project/locations/us/recommenders/google.bigquery.table.PartitionClusterRecommender/recommendations

curl
-H "Authorization: Bearer $GCLOUD_AUTH_TOKEN"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/my-project/locations/us/insightTypes/google.bigquery.table.StatsInsight/insights

Applica i suggerimenti per i cluster

Per applicare i suggerimenti per i cluster, esegui una delle seguenti operazioni:

• Applicare i cluster direttamente alla tabella originale
• Applicare i cluster a una tabella copiata
• Applicare i cluster in una vista materializzata

Applica i cluster direttamente alla tabella originale

Puoi applicare i suggerimenti per il clustering direttamente a una tabella BigQuery esistente. Questo metodo è più rapido dell'applicazione dei suggerimenti a una tabella copiata, ma non conserva una tabella di backup.

Segui questi passaggi per applicare una nuova specifica di clustering a tabelle non partizionate o partizionate.

1. Nello strumento bq, aggiorna la specifica di clustering della tabella in modo che corrisponda al nuovo clustering:

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.ORIGINAL_TABLE 

   Sostituisci quanto segue:

   • CLUSTER_COLUMN: la colonna su cui stai eseguendo il clustering, ad esempio mycolumn
   • DATASET: il nome del set di dati contenente la tabella, ad esempio mydataset
   • ORIGINAL_TABLE: il nome della tabella originale, ad esempio mytable

   Puoi anche chiamare il metodo API tables.update o tables.patch per modificare la specifica di clustering.

2. Per raggruppare tutte le righe in base alla nuova specifica di clustering, esegui la seguente istruzione UPDATE:

   UPDATE DATASET.ORIGINAL_TABLE SET CLUSTER_COLUMN=CLUSTER_COLUMN WHERE true

Applicare i cluster a una tabella copiata

Quando applichi i suggerimenti per il clustering a una tabella BigQuery, puoi prima copiare la tabella originale e poi applicare il suggerimento alla tabella copiata. Questo metodo garantisce che i dati originali vengano conservati se è necessario eseguire il rollback della modifica alla configurazione del clustering.

Puoi utilizzare questo metodo per applicare i suggerimenti di clustering sia alle tabelle non partizionate sia a quelle partizionate.

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

   Vai a BigQuery

2. Nell'editor di query, crea una tabella vuota con gli stessi metadati (incluse le specifiche di clustering) della tabella originale utilizzando l'operatore LIKE:

   CREATE TABLE DATASET.COPIED_TABLE
   LIKE DATASET.ORIGINAL_TABLE

   Sostituisci quanto segue:

   • DATASET: il nome del set di dati contenente la tabella, ad esempio mydataset
   • COPIED_TABLE: un nome per la tabella copiata, ad esempio copy_mytable
   • ORIGINAL_TABLE: il nome della tabella originale, ad esempio mytable

3. Nella console Google Cloud , apri l'editor di Cloud Shell.

   Attiva Cloud Shell

4. Nell'editor di Cloud Shell, aggiorna la specifica di clustering della tabella copiata in modo che corrisponda al clustering consigliato utilizzando il comando bq update:

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.COPIED_TABLE 

   Sostituisci CLUSTER_COLUMN con la colonna in base alla quale stai eseguendo il clustering, ad esempio mycolumn.

   Puoi anche chiamare il metodo API tables.update o tables.patch per modificare la specifica di clustering.

5. Nell'editor di query, recupera lo schema della tabella con la configurazione di partizionamento e clustering della tabella originale, se esiste un partizionamento o un clustering. Puoi recuperare lo schema visualizzando la visualizzazione INFORMATION_SCHEMA.TABLES della tabella originale:

   SELECT
     ddl
   FROM
     DATASET.INFORMATION_SCHEMA.TABLES
   WHERE
     table_name = 'DATASET.ORIGINAL_TABLE;'

   L'output è l'istruzione DDL (Data Definition Language) completa di ORIGINAL_TABLE, inclusa la clausola PARTITION BY. Per ulteriori informazioni sugli argomenti nell'output DDL, vedi l'istruzione CREATE TABLE.

   L'output DDL indica il tipo di partizionamento nella tabella originale:

   Tipo di partizionamento	Esempio di output
   Non partizionato	La clausola PARTITION BY è assente.
   Partizionato per colonna della tabella	PARTITION BY c0
   	PARTITION BY DATE(c0)
   	PARTITION BY DATETIME_TRUNC(c0, MONTH)
   Partizionata per tempo di importazione	PARTITION BY _PARTITIONDATE
   	PARTITION BY DATETIME_TRUNC(_PARTITIONTIME, MONTH)

6. Importa i dati nella tabella copiata. La procedura che utilizzi si basa sul tipo di partizione.

   • Se la tabella originale non è partizionata o è partizionata in base a una colonna della tabella, importa i dati dalla tabella originale alla tabella copiata:

     INSERT INTO DATASET.COPIED_TABLE
     SELECT * FROM DATASET.ORIGINAL_TABLE

   • Se la tabella originale è partizionata per data di importazione:

     1. Recupera l'elenco delle colonne per formare l'espressione di importazione dati utilizzando la visualizzazione INFORMATION_SCHEMA.COLUMNS:

        SELECT
        ARRAY_TO_STRING((
        SELECT
          ARRAY(
          SELECT
            column_name
          FROM
            DATASET.INFORMATION_SCHEMA.COLUMNS
          WHERE
            table_name = 'ORIGINAL_TABLE')), ", ")

        L'output è un elenco di nomi di colonne separati da virgole.

     2. Importa i dati dalla tabella originale a quella copiata:

        INSERT DATASET.COPIED_TABLE (COLUMN_NAMES, _PARTITIONTIME)
        SELECT *, _PARTITIONTIME FROM DATASET.ORIGINAL_TABLE

        Sostituisci COLUMN_NAMES con l'elenco di colonne che era l'output del passaggio precedente, separato da virgole, ad esempio col1, col2, col3.

   Ora hai una tabella copiata in cluster con gli stessi dati della tabella originale. Nei passaggi successivi, sostituisci la tabella originale con una tabella appena raggruppata.

7. Rinomina la tabella originale in una tabella di backup:

   ALTER TABLE DATASET.ORIGINAL_TABLE
   RENAME TO DATASET.BACKUP_TABLE

   Sostituisci BACKUP_TABLE con un nome per la tabella di backup, ad esempio backup_mytable.

8. Rinomina la tabella copiata con il nome della tabella originale:

   ALTER TABLE DATASET.COPIED_TABLE
   RENAME TO DATASET.ORIGINAL_TABLE

   La tabella originale è ora raggruppata in cluster in base al suggerimento di clustering.

• Accesso e autorizzazioni, ad esempio autorizzazioni IAM, accesso a livello di riga o accesso a livello di colonna.
• Elementi della tabella come cloni della tabella, snapshot della tabella o indici di ricerca.
• Lo stato di tutti i processi di tabella in corso, ad esempio le viste materializzate o i job eseguiti durante la copia della tabella.
• La possibilità di accedere ai dati storici delle tabelle utilizzando lo spostamento cronologico.
• Qualsiasi metadato associato alla tabella originale, ad esempio table_option_list o column_option_list. Per maggiori informazioni, consulta la sezione Istruzioni del linguaggio di definizione dei dati.

In caso di problemi, devi eseguire manualmente la migrazione degli artefatti interessati alla nuova tabella.

    DROP TABLE DATASET.BACKUP_TABLE
    

Applicare i cluster in una vista materializzata

Applica i suggerimenti di partizionamento

Per applicare i suggerimenti per le partizioni, devi applicarli a una copia della tabella originale. BigQuery non supporta la modifica dello schema di partizionamento di una tabella sul posto, ad esempio la modifica di una tabella non partizionata in una tabella partizionata, la modifica dello schema di partizionamento di una tabella o la creazione di una vista materializzata con uno schema di partizionamento diverso da quello della tabella di base. Puoi modificare il partizionamento di una tabella solo su una copia della tabella.

Applica i suggerimenti di partizionamento a una tabella copiata

Quando applichi i suggerimenti per le partizioni a una tabella BigQuery, devi prima copiare la tabella originale e poi applicare il suggerimento alla tabella copiata. Questo approccio garantisce la conservazione dei dati originali se devi eseguire il rollback di una partizione.

La seguente procedura utilizza un esempio di consiglio per partizionare una tabella in base all'unità di tempo di partizionamento DAY.

1. Crea una tabella copiata utilizzando i suggerimenti per le partizioni:

   CREATE TABLE DATASET.COPIED_TABLE
   PARTITION BY DATE_TRUNC(PARTITION_COLUMN, DAY)
   AS SELECT * FROM DATASET.ORIGINAL_TABLE

   Sostituisci quanto segue:

   • DATASET: il nome del set di dati contenente la tabella, ad esempio mydataset
   • COPIED_TABLE: un nome per la tabella copiata, ad esempio copy_mytable
   • PARTITION_COLUMN: la colonna in base alla quale stai partizionando, ad esempio mycolumn

   Per ulteriori informazioni sulla creazione di tabelle partizionate, consulta Creazione di tabelle partizionate.

2. Rinomina la tabella originale in una tabella di backup:

   ALTER TABLE DATASET.ORIGINAL_TABLE
   RENAME TO DATASET.BACKUP_TABLE

   Sostituisci BACKUP_TABLE con un nome per la tabella di backup, ad esempio backup_mytable.

3. Rinomina la tabella copiata con il nome della tabella originale:

   ALTER TABLE DATASET.COPIED_TABLE
   RENAME TO DATASET.ORIGINAL_TABLE

   La tabella originale è ora partizionata in base al suggerimento per la partizione.

• Accesso e autorizzazioni, ad esempio autorizzazioni IAM, accesso a livello di riga o accesso a livello di colonna.
• Elementi della tabella come cloni della tabella, snapshot della tabella o indici di ricerca.
• Lo stato di tutti i processi di tabella in corso, ad esempio le viste materializzate o i job eseguiti durante la copia della tabella.
• La possibilità di accedere ai dati storici delle tabelle utilizzando lo spostamento cronologico.
• Qualsiasi metadato associato alla tabella originale, ad esempio table_option_list o column_option_list. Per maggiori informazioni, consulta la sezione Istruzioni del linguaggio di definizione dei dati.
• Possibilità di utilizzare SQL precedente per scrivere i risultati delle query in tabelle partizionate. L'utilizzo di SQL precedente non è completamente supportato nelle tabelle partizionate. Una soluzione consiste nell'eseguire la migrazione dei flussi di lavoro SQL precedente in GoogleSQL prima di applicare un suggerimento di partizione.

In caso di problemi, devi eseguire manualmente la migrazione degli artefatti interessati alla nuova tabella.

    DROP TABLE DATASET.BACKUP_TABLE
    

Prezzi

Per informazioni dettagliate sui prezzi di questa funzionalità, consulta la panoramica dei prezzi di Gemini in BigQuery.

• Costi di elaborazione. Quando applichi un suggerimento, esegui una query Data Definition Language (DDL) o Data Manipulation Language (DML) nel tuo progetto BigQuery.
• Costi di archiviazione. Se utilizzi il metodo di copia di una tabella, utilizzi spazio di archiviazione aggiuntivo per la tabella copiata (o di backup).

Si applicano addebiti standard per l'elaborazione e l'archiviazione a seconda dell'account di fatturazione associato al progetto. Per ulteriori informazioni, vedi Prezzi di BigQuery.

Quote e limiti

Per informazioni su quote e limiti per questa funzionalità, vedi Quote per Gemini in BigQuery.

Risoluzione dei problemi

Problema:non vengono visualizzati suggerimenti per una tabella specifica.

I consigli per le partizioni potrebbero non essere visualizzati per le tabelle che soddisfano questi criteri:

• La tabella è inferiore a 100 GB.
• La tabella è già partizionata o raggruppata in cluster.

I consigli sui cluster potrebbero non essere visualizzati per le tabelle che soddisfano questi criteri:

• La tabella ha dimensioni inferiori a 10 GB.
• La tabella è già in cluster.

I suggerimenti di partizionamento e clustering potrebbero essere eliminati quando:

• La tabella ha un costo di scrittura elevato dovuto alle operazioni di data manipulation language (DML).
• La tabella non è stata letta negli ultimi 30 giorni.
• I risparmi mensili stimati sono troppo insignificanti (meno di un'ora di slot di risparmio).