Gestione delle tabelle partizionate

Questo documento descrive come gestire le tabelle partizionate in BigQuery.

Recupero dei metadati della partizione

Puoi ottenere informazioni sulle tabelle partizionate nei seguenti modi:

Recupero dei metadati delle partizioni utilizzando le viste INFORMATION_SCHEMA

Quando esegui una query sulla visualizzazione INFORMATION_SCHEMA.PARTITIONS, i risultati della query contengono una riga per ogni partizione. Ad esempio, la seguente query elenca tutte le partizioni della tabella nel set di dati denominato mydataset:

SELECT table_name, partition_id, total_rows
FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE partition_id IS NOT NULL

Per ulteriori informazioni, vedi INFORMATION_SCHEMA.PARTITIONS.

Recupero dei metadati delle partizioni utilizzando le metatabelle

In SQL precedente, puoi ottenere i metadati sulle partizioni della tabella eseguendo query sulla metatabella __PARTITIONS_SUMMARY__. Le meta-tabelle sono tabelle di sola lettura che contengono metadati.

Esegui una query sulla meta-tabella __PARTITIONS_SUMMARY__ nel seguente modo:

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

La meta-tabella __PARTITIONS_SUMMARY__ ha le seguenti colonne:

Valore Descrizione
project_id Nome del progetto.
dataset_id Nome del set di dati.
table_id Nome della tabella partizionata per tempo.
partition_id Nome (data) della partizione.
creation_time L'ora in cui è stata creata la partizione, in millisecondi dal 1° gennaio 1970 UTC.
last_modified_time L'ora in cui la partizione è stata modificata l'ultima volta, in millisecondi dal 1° gennaio 1970 UTC.

Come minimo, per eseguire un job di query che utilizza la meta-tabella __PARTITIONS_SUMMARY__, devi disporre delle autorizzazioni bigquery.jobs.create e bigquery.tables.getData.

Per saperne di più sui ruoli IAM in BigQuery, consulta Controllo dell'accesso.

Impostare la scadenza della partizione

Quando crei una tabella partizionata per data di importazione o per colonna di unità di tempo, puoi specificare una scadenza per la partizione. Questa impostazione specifica per quanto tempo BigQuery conserva i dati in ogni partizione. L'impostazione si applica a tutte le partizioni della tabella, ma viene calcolata in modo indipendente per ciascuna partizione in base all'ora della partizione.

L'ora di scadenza di una partizione viene calcolata dal limite della partizione in UTC. Ad esempio, con il partizionamento giornaliero, il limite della partizione è a mezzanotte (00:00:00 UTC). Se la scadenza della partizione della tabella è di 6 ore, ogni partizione scade alle 06:00:00 UTC del giorno successivo. Quando una partizione scade, BigQuery elimina i dati contenuti.

Puoi anche specificare una scadenza della partizione predefinita a livello di set di dati. Se imposti la scadenza della partizione su una tabella, il valore sostituisce la scadenza della partizione predefinita. Se non specifichi alcuna scadenza della partizione (nella tabella o nel set di dati), le partizioni non scadono mai.

Se imposti una scadenza della tabella, questo valore ha la precedenza sulla scadenza della partizione. Ad esempio, se la scadenza della tabella è impostata su 5 giorni e la scadenza della partizione è impostata su 7 giorni, la tabella e tutte le partizioni al suo interno vengono eliminate dopo 5 giorni.

In qualsiasi momento dopo la creazione di una tabella, puoi aggiornare la scadenza della partizione della tabella. La nuova impostazione si applica a tutte le partizioni della tabella, indipendentemente da quando sono state create. Le partizioni esistenti scadono immediatamente se sono precedenti alla nuova ora di scadenza. Allo stesso modo, se i dati vengono copiati o inseriti in una tabella partizionata in base alla colonna dell'unità di tempo, tutte le partizioni precedenti alla scadenza della partizione configurata per la tabella scadono immediatamente.

Quando una partizione scade, BigQuery la elimina. I dati delle partizioni vengono conservati in conformità con le norme di time travel e fail-safe e possono essere addebitati a seconda del modello di fatturazione. Fino ad allora, la partizione viene conteggiata ai fini delle quote delle tabelle. Per eliminare immediatamente una partizione, puoi eliminarla manualmente.

Aggiorna la scadenza della partizione

Per aggiornare la scadenza della partizione di una tabella partizionata:

Console

Non puoi aggiornare la scadenza della partizione nella console Google Cloud .

SQL

Utilizza l'istruzione ALTER TABLE SET OPTIONS. L'esempio seguente aggiorna la scadenza a 5 giorni. Per rimuovere la scadenza della partizione per una tabella, imposta partition_expiration_days su NULL.

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

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

    ALTER TABLE mydataset.mytable
  SET OPTIONS (
    -- Sets partition expiration to 5 days
    partition_expiration_days = 5);

  3. Fai clic su Esegui.

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

bq

Esegui il comando bq update con il flag --time_partitioning_expiration. Se stai aggiornando una tabella partizionata in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: project_id:dataset.

bq update \
--time_partitioning_expiration integer_in_seconds \
--time_partitioning_type unit_time \
project_id:dataset.table

Dove:

  • integer è la durata predefinita (in secondi) delle partizioni della tabella. Non esiste un valore minimo. Il tempo di scadenza corrisponde alla data della partizione più il valore intero. Se specifichi 0, la scadenza della partizione viene rimossa e la partizione non scade mai. Le partizioni senza scadenza devono essere eliminate manualmente.
  • unit_time è DAY, HOUR, MONTH o YEAR, a seconda della granularità del partizionamento della tabella. Questo valore deve corrispondere alla granularità impostata durante la creazione della tabella.
  • project_id è l'ID progetto.
  • dataset è il nome del set di dati contenente la tabella che stai aggiornando.
  • table è il nome della tabella che stai aggiornando.

Esempi:

Inserisci il comando seguente per aggiornare il tempo di scadenza delle partizioni in mydataset.mytable a 5 giorni (432.000 secondi). mydataset si trova nel tuo progetto predefinito.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Inserisci il comando seguente per aggiornare il tempo di scadenza delle partizioni in mydataset.mytable a 5 giorni (432.000 secondi). mydataset si trova in myotherproject, non nel tuo progetto predefinito.

bq update \
--time_partitioning_expiration 432000 \
myotherproject:mydataset.mytable

API

Chiama il metodo tables.patch e utilizza la proprietà timePartitioning.expirationMs per aggiornare la scadenza della partizione in millisecondi. Poiché il metodo tables.update sostituisce l'intera risorsa della tabella, è preferibile il metodo tables.patch.

Impostare i requisiti del filtro di partizionamento

Quando crei una tabella partizionata, puoi richiedere che tutte le query sulla tabella includano un filtro predicato (una clausola WHERE) che filtra in base alla colonna di partizionamento. Questa impostazione può migliorare le prestazioni e ridurre i costi, perché BigQuery può utilizzare il filtro per eliminare le partizioni che non corrispondono al predicato. Questo requisito si applica anche alle query su viste e viste materializzate che fanno riferimento alla tabella partizionata.

Per informazioni sull'aggiunta dell'opzione Richiedi filtro partizione quando crei una tabella partizionata, vedi Creazione di tabelle partizionate.

Se una tabella partizionata ha l'impostazione Richiedi filtro di partizionamento, ogni query su quella tabella deve includere almeno un predicato che fa riferimento solo alla colonna di partizionamento. Le query senza questo predicato restituiscono il seguente errore:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Per maggiori informazioni, consulta la pagina Esecuzione di query sulle tabelle partizionate.

Aggiorna il requisito del filtro di partizionamento

Se non attivi l'opzione Richiedi filtro di partizionamento quando crei la tabella partizionata, puoi aggiornarla per aggiungere l'opzione.

Console

Non puoi utilizzare la console Google Cloud per richiedere filtri di partizione dopo la creazione di una tabella partizionata.

SQL

Utilizza l'istruzione ALTER TABLE SET OPTIONS per aggiornare il requisito del filtro di partizionamento. L'esempio seguente aggiorna il requisito a true:

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

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

    ALTER TABLE mydataset.mypartitionedtable
  SET OPTIONS (
    require_partition_filter = true);

  3. Fai clic su Esegui.

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

bq

Per aggiornare una tabella partizionata in modo che richieda filtri di partizionamento utilizzando lo strumento a riga di comando bq, inserisci il comando bq update e fornisci il flag --require_partition_filter.

Per aggiornare una tabella partizionata in un progetto diverso da quello predefinito, aggiungi l'ID progetto al set di dati nel seguente formato: project_id:dataset.

Ad esempio:

Per aggiornare mypartitionedtable in mydataset nel progetto predefinito, inserisci:

bq update --require_partition_filter mydataset.mytable

Per aggiornare mypartitionedtable in mydataset in myotherproject, inserisci:

bq update --require_partition_filter myotherproject:mydataset.mytable

API

Chiama il metodo tables.patch e imposta la proprietà requirePartitionFilter su true per richiedere i filtri di partizione. Poiché il metodo tables.update sostituisce l'intera risorsa della tabella, è preferibile il metodo tables.patch.

Java

Prima di provare questo esempio, segui le istruzioni di configurazione di Java nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configurare l'autenticazione per le librerie client. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;

// Sample to update require partition filter on a table.
public class UpdateTableRequirePartitionFilter {

  public static void runUpdateTableRequirePartitionFilter() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableRequirePartitionFilter(datasetName, tableName);
  }

  public static void updateTableRequirePartitionFilter(String datasetName, String tableName) {
    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();

      Table table = bigquery.getTable(datasetName, tableName);
      table.toBuilder().setRequirePartitionFilter(true).build().update();

      System.out.println("Table require partition filter updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Table require partition filter was not updated \n" + e.toString());
    }
  }
}

Copiare una tabella partizionata

La procedura per copiare una tabella partizionata è la stessa di quella per copiare una tabella standard. Per ulteriori informazioni, vedi Copiare una tabella.

Quando copi una tabella partizionata, tieni presente quanto segue:

  • Copia di una tabella partizionata in una nuova tabella di destinazione
    Tutte le informazioni sul partizionamento vengono copiate con la tabella. La nuova tabella e quella precedente avranno partizioni identiche.
  • Copia di una tabella non partizionata in una tabella partizionata esistente
    Questa operazione è supportata solo per il partizionamento in fase di importazione. BigQuery copia i dati di origine nella partizione che rappresenta la data corrente. Questa operazione non è supportata per le tabelle partizionate per colonne di unità di tempo o intervallo di numeri interi.
  • Copia di una tabella partizionata in un'altra tabella partizionata
    Le specifiche di partizionamento per le tabelle di origine e di destinazione devono corrispondere.
  • Copia di una tabella partizionata in una tabella non partizionata
    La tabella di destinazione rimane non partizionata.
  • Copia di più tabelle partizionate

    Se copi più tabelle di origine in una tabella partizionata nello stesso job, le tabelle di origine non possono contenere un mix di tabelle partizionate e non partizionate.

    Se tutte le tabelle di origine sono tabelle partizionate, le specifiche di partizionamento per tutte le tabelle di origine devono corrispondere alla specifica di partizionamento della tabella di destinazione.

  • Copia di una tabella partizionata con una specifica di clustering

    Se copi in una nuova tabella, tutte le informazioni di clustering vengono copiate con la tabella. La nuova tabella e quella precedente avranno un clustering identico.

    Se copi in una tabella esistente, le specifiche del cluster per le tabelle di origine e di destinazione devono corrispondere.

Quando copi in una tabella esistente, puoi specificare se aggiungere o sovrascrivere la tabella di destinazione.

Copiare singole partizioni

Puoi copiare i dati da una o più partizioni in un'altra tabella.

Console

La copia delle partizioni non è supportata dalla console Google Cloud .

bq

Per copiare una partizione, utilizza il comando bq cp (copia) dello strumento a riga di comando bq con un decoratore di partizione ($date) come $20160201.

I flag facoltativi possono essere utilizzati per controllare la disposizione di scrittura della partizione di destinazione:

  • -a o --append_table aggiunge i dati della partizione di origine a una tabella o partizione esistente nel set di dati di destinazione.
  • -f o --force sovrascrive una tabella o una partizione esistente nel set di dati di destinazione e non ti chiede la conferma.
  • -n o --no_clobber restituisce il seguente messaggio di errore se la tabella o la partizione esiste nel set di dati di destinazione: Table '<var>project_id:dataset.table</var> or <var>table$date</var>' already exists, skipping. Se -n non è specificato, il comportamento predefinito è quello di chiederti se sostituire la tabella o la partizione di destinazione.
  • --destination_kms_key è la chiave Cloud KMS gestita dal cliente utilizzata per criptare la tabella o la partizione di destinazione.

Il comando cp non supporta i flag --time_partitioning_field o --time_partitioning_type. Non puoi utilizzare un job di copia per convertire una tabella partizionata per data di importazione in una tabella partizionata.

--destination_kms_key non è dimostrato qui. Per ulteriori informazioni, consulta Protezione dei dati con le chiavi Cloud KMS.

Se il set di dati di origine o di destinazione si trova in un progetto diverso da quello predefinito, aggiungi l'ID progetto ai nomi dei set di dati nel seguente formato: project_id:dataset.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua posizione.

bq --location=location cp \
-a -f -n \
project_id:dataset.source_table$source_partition \
project_id:dataset.destination_table$destination_partition

Dove:

  • location è il nome della tua località. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.
  • project_id è l'ID progetto.
  • dataset è il nome del set di dati di origine o di destinazione.
  • source_table è la tabella che stai copiando.
  • source_partition è il decoratore della partizione di origine.
  • destination_table è il nome della tabella nel set di dati di destinazione.
  • destination_partition è il decoratore della partizione di destinazione.

Esempi:

Copia di una partizione in una nuova tabella

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable a una nuova tabella: mydataset.mytable2. mydataset si trova nel tuo progetto predefinito.

bq cp -a 'mydataset.mytable$20180130' mydataset.mytable2

Copia di una partizione in una tabella non partizionata

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable a una tabella non partizionata: mydataset2.mytable2. La scorciatoia -a viene utilizzata per aggiungere i dati della partizione alla tabella di destinazione non partizionata. Entrambi i set di dati si trovano nel tuo progetto predefinito.

bq cp -a 'mydataset.mytable$20180130' mydataset2.mytable2

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable a una tabella non partizionata: mydataset2.mytable2. La scorciatoia -f viene utilizzata per sovrascrivere la tabella di destinazione non partizionata senza prompt.

bq --location=US cp -f 'mydataset.mytable$20180130' mydataset2.mytable2

Copiare una partizione in un'altra tabella partizionata

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable a un'altra tabella partizionata: mydataset2.mytable2. La scorciatoia -a viene utilizzata per aggiungere i dati della partizione alla tabella di destinazione. Poiché non è specificato alcun decoratore di partizione nella tabella di destinazione, la chiave di partizione di origine viene conservata e i dati vengono copiati nella partizione del 30 gennaio 2018 nella tabella di destinazione. Puoi anche specificare un decoratore di partizione nella tabella di destinazione per copiare i dati in una partizione specifica. mydataset si trova nel tuo progetto predefinito. mydataset2 si trova in myotherproject, non nel tuo progetto predefinito.

bq --location=US cp \
-a \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable alla partizione del 30 gennaio 2018 di un'altra tabella partizionata: mydataset2.mytable2. La scorciatoia -f viene utilizzata per sovrascrivere la partizione del 30 gennaio 2018 nella tabella di destinazione senza prompt. Se non viene utilizzato alcun decoratore di partizione, tutti i dati nella tabella di destinazione vengono sovrascritti. mydataset si trova nel tuo progetto predefinito. mydataset2 si trova in myotherproject, non nel tuo progetto predefinito.

bq cp \
-f \
'mydataset.mytable$20180130' \
'myotherproject:mydataset2.mytable2$20180130'

Inserisci il seguente comando per copiare la partizione del 30 gennaio 2018 da mydataset.mytable a un'altra tabella partizionata: mydataset2.mytable2. mydataset si trova nel tuo progetto predefinito. mydataset2 si trova in myotherproject, non nel tuo progetto predefinito. Se nella tabella di destinazione sono presenti dati, il comportamento predefinito è quello di chiederti di sovrascriverli.

bq cp \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Per copiare più partizioni, specificarle come elenco separato da virgole:

bq cp \
'mydataset.mytable$20180130,mydataset.mytable$20180131' \
myotherproject:mydataset.mytable2

API

Chiama il metodo jobs.insert e configura un job copy. (Facoltativo) Specifica la tua regione nella proprietà location nella sezione jobReference della risorsa job.

Specifica le seguenti proprietà nella configurazione del job:

  • Inserisci il set di dati, la tabella e la partizione di origine nella proprietà sourceTables.
  • Inserisci il set di dati e la tabella di destinazione nella proprietà destinationTable.
  • Utilizza la proprietà writeDisposition per specificare se aggiungere o sovrascrivere la tabella o la partizione di destinazione.

Per copiare più partizioni, inserisci le partizioni di origine (inclusi i nomi del set di dati e della tabella) nella proprietà sourceTables.

Eliminare una partizione

Puoi eliminare una singola partizione da una tabella partizionata. Tuttavia, non puoi eliminare le partizioni speciali __NULL__ o __UNPARTITIONED__.

Puoi eliminare una sola partizione alla volta.

Puoi eliminare una partizione specificando il relativo decoratore, a meno che non si tratti di una delle due partizioni speciali.

Per eliminare una partizione in una tabella partizionata:

Console

L'eliminazione delle partizioni non è supportata dalla console. Google Cloud

SQL

Se un'istruzione qualificante DELETE copre tutte le righe di una partizione, BigQuery rimuove l'intera partizione. Questa rimozione viene eseguita senza analizzare byte o utilizzare slot. Il seguente esempio di un'istruzione DELETE copre l'intera partizione di un filtro sulla pseudocolonna _PARTITIONDATE:

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

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

    DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

  3. Fai clic su Esegui.

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

bq

Utilizza il comando bq rm con il flag --table (o la scorciatoia -t) e specifica il decoratore di partizione per eliminare una partizione specifica.

bq rm --table project_id:dataset.table$partition

Dove:

  • project_id è l'ID progetto. Se omesso, viene utilizzato il progetto predefinito.
  • dataset è il nome del set di dati che contiene la tabella.
  • table è il nome della tabella.
  • partition è il decoratore della partizione che stai eliminando.

I decoratori di partizione hanno il seguente formato, a seconda del tipo di partizionamento:

  • Partizione oraria: yyyymmddhh. Esempio: $2016030100.
  • Partizione giornaliera: yyyymmdd. Esempio: $20160301.
  • Partizione mensile: yyyymm. Esempio: $201603.
  • Partizione annuale: yyyy. Esempio: $2016.
  • Partizione per intervalli di numeri interi: inizio dell'intervallo di partizione. Esempio: $20.

Lo strumento a riga di comando bq ti chiede di confermare l'azione. Per saltare la conferma, utilizza il flag --force (o la scorciatoia -f).

Esempi:

Elimina la partizione per il 1° marzo 2016 in una tabella partizionata giornalmente denominata mydataset.mytable nel tuo progetto predefinito:

bq rm --table 'mydataset.mytable$20160301'

Elimina la partizione per marzo 2016 in una tabella partizionata mensilmente:

bq rm --table 'mydataset.mytable$201603'

Elimina l'intervallo di numeri interi che inizia da 20 in una tabella partizionata con intervalli di numeri interi denominata mydataset.mytable:

bq rm --table 'mydataset.mytable$20'

API

Chiama il metodo tables.delete e specifica il decoratore di tabella e partizione utilizzando il parametro tableId.

Sicurezza delle tabelle partizionate

Il controllo dell'accesso per le tabelle partizionate è lo stesso di quello per le tabelle standard. Per ulteriori informazioni, consulta Introduzione ai controlli dell'accesso alle tabelle.