Gestione delle tabelle partizionate

Questo documento descrive come gestire le tabelle partizionate in BigQuery.

Nota: le informazioni riportate nella sezione Gestione delle tabelle si applicano anche alle tabelle partizionate.

Recuperare i metadati della partizione

Puoi ottenere informazioni sulle tabelle partizionate nei seguenti modi:

• Utilizza la visualizzazione INFORMATION_SCHEMA.PARTITIONS (Anteprima).
• Utilizza la metatabella __PARTITIONS_SUMMARY__ (solo SQL precedente).

Ottenere i metadati della partizione utilizzando le visualizzazioni INFORMATION_SCHEMA

Quando esegui una query sulla vista 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, consulta INFORMATION_SCHEMA.PARTITIONS.

Ottenere i metadati della partizione utilizzando le metatabelle

In SQL precedente, puoi ottenere i metadati relativi alle partizioni della tabella eseguendo una query sulla metatabella __PARTITIONS_SUMMARY__. Le metatabelle sono tabelle di sola lettura che contengono metadati.

Esegui una query sulla metatabella __PARTITIONS_SUMMARY__ come segue:

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

Nota: GoogleSQL non supporta il separatore del decoratore della partizione ($), pertanto non puoi eseguire query su __PARTITIONS_SUMMARY__ in GoogleSQL.

La metatabella __PARTITIONS_SUMMARY__ contiene le seguenti colonne:

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

Per eseguire un job di query che utilizza la tabella meta__PARTITIONS_SUMMARY__, devi disporre almeno delle autorizzazioni bigquery.jobs.create ebigquery.tables.getData.

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

Imposta la scadenza della partizione

Quando crei una tabella partizionata per data di importazione o 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 ogni partizione in base al momento della partizione.

L'ora di scadenza di una partizione viene calcolata dal confine della partizione in UTC. Ad esempio, con il partizionamento giornaliero, il confine 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 al suo interno.

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

Nota: le tabelle con partizione in base ad intervalli di interi non supportano le date di scadenza della partizione.

Se imposti una scadenza della tabella, questo valore ha la precedenza sulla scadenza della tabella. 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 relative partizioni vengono eliminate dopo 5 giorni.

In qualsiasi momento dopo la creazione di una tabella, puoi aggiornarne la scadenza della partizione. La nuova impostazione si applica a tutte le partizioni della tabella, indipendentemente da quando sono state create. Le partizioni esistenti scadono immediatamente se sono più vecchie della nuova data e ora di scadenza. Analogamente, se i dati vengono copiati o inseriti in una tabella partizionata in base a una colonna di 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 della partizione vengono conservati in conformità con le norme relative al viaggio nel tempo e al fail-safe e possono essere soggetti a costi, 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 di una 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 su 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 ulteriori informazioni su come eseguire 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) per le partizioni della tabella. Non è previsto un valore minimo. L'ora di scadenza viene valutata come la 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, in base alla 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 seguente comando per aggiornare la data e l'ora di scadenza delle partizioni in mydataset.mytable su 5 giorni (432000 secondi). mydataset si trova nel tuo progetto predefinito.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Inserisci il seguente comando per aggiornare la data e l'ora di scadenza delle partizioni in mydataset.mytable su 5 giorni (432000 secondi). mydataset si trova in myotherproject, non nel 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 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 del predicato (una clausola WHERE) che filtra in base alla colonna di partizione. Questa impostazione può migliorare le prestazioni e ridurre i costi, poiché BigQuery può utilizzare il filtro per eliminare le partizioni che non corrispondono al predicato.

Per informazioni sull'aggiunta dell'opzione Richiedi filtro partizione quando crei una tabella partizionata, consulta Creare 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 ulteriori informazioni, consulta Esecuzione di query sulle tabelle partizionate.

Aggiorna il requisito del filtro di partizione

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 la istruzione ALTER TABLE SET OPTIONS per aggiornare il requisito del filtro di partizionamento. Nell'esempio riportato di seguito, il requisito viene aggiornato in 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

bq

Per aggiornare una tabella partizionata in modo che richieda i filtri di partizione utilizzando lo strumento a riga di comando bq, invia il comando bq update e specifica 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, invia:

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 tabella, è preferibile il metodo tables.patch.

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 autenticarti in BigQuery, configura le 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.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 per copiare una tabella standard. Per ulteriori informazioni, consulta la sezione 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 la vecchia tabella avranno partizioni identiche.

• Copia di una tabella non partizionata in una tabella partizionata esistente

  Questa operazione è supportata solo per il partizionamento in base al momento dell'importazione. BigQuery copia i dati di origine nella partizione che rappresenta la data corrente. Questa operazione non è supportata per le tabelle con partizione per colonne di unità di tempo o per intervallo di interi.

• Copiare una tabella partizionata in un'altra tabella partizionata

  Le specifiche della partizione per le tabelle di origine e di destinazione devono essere uguali.

• Copiare 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 una combinazione di tabelle partizionate e non partizionate.

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

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

Copiare le singole partizioni

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

Nota: le autorizzazioni richieste sono le stesse per la copia di una 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 della partizione ($date) come $20160201.

È possibile utilizzare flag facoltativi per controllare l'allocazione delle scritture 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 richiede conferma.
• -n o --no_clobber restituisce il seguente messaggio di errore se la tabella o la suddivisione esistente nel set di dati di destinazione: Table '<var>project_id:dataset.table</var> or <var>table$date</var>' already exists, skipping. Se non è specificato -n, il comportamento predefinito è chiederti di scegliere se sostituire la tabella o la suddivisione 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 sede. 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 posizione 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 della partizione di destinazione.

Esempi:

Nota: il separatore del decoratore della partizione ($) è una variabile speciale nella shell di Unix. Potresti dover eseguire l'escape del decoratore quando utilizzi lo strumento della riga di comando. I seguenti esempi escono dal decoratore della partizione: mydataset.table\$20160519, 'mydataset.table$20160519'.

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

Copiare 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 accodare 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 richiesta.

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

Copia di 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 accodare i dati della partizione alla tabella di destinazione. Poiché non è specificato alcun decoratore della partizione nella tabella di destinazione, la chiave della 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 della 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 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 richiedere conferma. Se non viene utilizzato alcun decoratore della partizione, tutti i dati della tabella di destinazione vengono sovrascritti. mydataset si trova nel tuo progetto predefinito. mydataset2 si trova in myotherproject, non nel 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 progetto predefinito. Se nella tabella di destinazione sono presenti dati, il comportamento predefinito è chiederti di eseguire l'overwriting.

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

Nota: il comando bq cp con un decoratore della partizione funziona su partizioni basate su colonne in cui la partizione di origine e quella di destinazione sono identiche. Il comando bq cp funziona anche con le partizioni basate sul momento dell'importazione se la partizione rappresenta la stessa unità di tempo o un'unità di tempo più approssimativa che contiene la partizione di origine. Ad esempio, se $20180130 è il decoratore della partizione di origine, i decoratori della partizione di destinazione validi includono $20180130, $201801 e $2018. Per copiare una partizione basata su colonne in un decoratore di partizione completamente diverso o in una partizione in unità di tempo con una granularità più fine, utilizza un'istruzione INSERT SELECT.

Per copiare più partizioni, specificale 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 delle tabelle) 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 solo una partizione alla volta.

Nota: le autorizzazioni richieste sono le stesse per la eliminazione di una tabella.

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 DELETE che soddisfa i requisiti copre tutte le righe di una partizione, BigQuery rimuove l'intera partizione. Questa rimozione viene eseguita senza scansionare i byte o utilizzare gli slot. Il seguente esempio di stmtDELETE 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

bq

Utilizza il comando bq rm con il flag --table (o la scorciatoia -t) e specifica il decoratore della 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 contenente la tabella.
• table è il nome della tabella.
• partition è il decoratore della partizione che stai eliminando.

I decoratori delle partizioni hanno il seguente formato, a seconda del tipo di suddivisione:

• Partizione oraria: yyyymmddhh. Esempio: $2016030100.
• Partizione giornaliera: yyyymmdd. Esempio: $20160301.
• Partizione mensile: yyyymm. Esempio: $201603.
• Partizione annuale: yyyy. Esempio: $2016.
• Partizione per intervallo di numeri interi: inizio dell'intervallo della 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).

Nota: il separatore del decoratore della partizione ($) è una variabile speciale nella shell di Unix. Potresti dover eseguire l'escape del decoratore quando utilizzi lo strumento della riga di comando. I seguenti esempi escono dal decoratore della partizione: mydataset.table\$20160519, 'mydataset.table$20160519'.

Esempi:

Elimina la partizione relativa al 1° marzo 2016 in una tabella partizionata giornaliera denominata mydataset.mytable nel progetto predefinito:

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

Elimina la partizione di marzo 2016 in una tabella partizionata mensile:

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

Elimina l'intervallo di numeri interi a partire da 20 in una tabella con intervallo di numeri interi suddivisa denominata mydataset.mytable:

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

API

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

Sicurezza delle tabelle partizionate

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