Introduzione alle tabelle partizionate

Una tabella partizionata è divisa in segmenti, denominati partizioni, che semplificano la gestione e l'esecuzione di query sui dati. Dividendo una tabella grande in partizioni più piccole puoi migliorare le prestazioni delle query e controllare i costi riducendo il numero di byte letti da una query. Puoi partizionare una tabella specificando una colonna di partizionamento che BigQuery utilizza per segmentare la tabella.

Se una query utilizza un filtro idoneo sul valore della colonna di partizionamento, BigQuery può analizzare le partizioni che corrispondono al filtro e saltare quelle rimanenti. Questo processo è chiamato potatura.

Come funziona il partizionamento

In una tabella partizionata, i dati vengono archiviati in blocchi fisici, ognuno dei quali contiene una partizione di dati. Ogni tabella partizionata conserva vari metadati sulle proprietà di ordinamento in tutte le operazioni che la modificano. I metadati consentono a BigQuery di stimare con maggiore precisione il costo di una query prima che venga eseguita.

Quando utilizzare il partizionamento

Prendi in considerazione il partizionamento di una tabella nei seguenti scenari:

  • Vuoi migliorare le prestazioni delle query analizzando solo una parte di una tabella.
  • L'operazione sulla tabella supera una quota di tabella standard e puoi limitare l'ambito delle operazioni sulla tabella a valori di colonna di partizione specifici, consentendo quote di tabelle partizionate più elevate.
  • Vuoi determinare i costi delle query prima che vengano eseguite. BigQuery fornisce stime dei costi delle query prima che vengano eseguite su una tabella partizionata. Calcola una stima del costo della query eseguendo la potatura di una tabella partizionata, quindi esegui un dry run della query per stimare i costi della query.
  • Vuoi una delle seguenti funzionalità di gestione a livello di partizione:

Valuta la possibilità di eseguire il clustering di una tabella anziché di partizionarla nelle seguenti circostanze:

  • Hai bisogno di una granularità maggiore di quella consentita dal partizionamento.
  • Le tue query utilizzano solitamente filtri o aggregazioni su più colonne.
  • La cardinalità del numero di valori in una colonna o in un gruppo di colonne è elevata.
  • Non hai bisogno di stime dei costi rigorose prima dell'esecuzione delle query.
  • Il partizionamento genera una piccola quantità di dati per partizione (circa meno di 10 GB). La creazione di molte partizioni di piccole dimensioni aumenta i metadati della tabella e può influire sui tempi di accesso ai metadati durante l'esecuzione di query sulla tabella.
  • Il partizionamento genera un numero elevato di partizioni, superando i limiti per le tabelle partizionate.
  • Le operazioni DML modificano di frequente (ad esempio, ogni pochi minuti) la maggior parte delle partizioni della tabella.

In questi casi, il clustering delle tabelle consente di accelerare le query raggruppando i dati in colonne specifiche in base alle proprietà di ordinamento definite dall'utente.

Puoi anche combinare il clustering e il partizionamento delle tabelle per ottenere un ordinamento più granulare. Per saperne di più su questo approccio, consulta Combinazione di tabelle raggruppate in cluster e partizionate.

Tipi di partizionamento

Questa sezione descrive i diversi modi per partizionare una tabella.

Partizionamento per intervalli di numeri interi

Puoi partizionare una tabella in base a intervalli di valori in una colonna INTEGER specifica. Per creare una tabella partizionata per intervalli di numeri interi, devi fornire:

  • La colonna di partizionamento.
  • Il valore iniziale per il partizionamento per intervalli (incluso).
  • Il valore finale per il partizionamento per intervalli (esclusivo).
  • L'intervallo di ogni intervallo all'interno della partizione.

Ad esempio, supponiamo di creare una partizione per intervallo di numeri interi con la seguente specifica:

Argomento Valore
nome colonna customer_id
start 0
end 100
intervallo 10

La tabella è partizionata in base alla colonna customer_id in intervalli di 10. I valori da 0 a 9 vengono inseriti in una partizione, i valori da 10 a 19 nella partizione successiva e così via, fino a 99. I valori al di fuori di questo intervallo vengono inseriti in una partizione denominata __UNPARTITIONED__. Tutte le righe in cui customer_id è NULL vengono inserite in una partizione denominata __NULL__.

Per informazioni sulle tabelle partizionate per intervalli di numeri interi, consulta Crea una tabella partizionata per intervalli di numeri interi.

Partizionamento delle colonne per unità di tempo

Puoi partizionare una tabella in base a una colonna DATE, TIMESTAMP o DATETIME della tabella. Quando scrivi i dati nella tabella, BigQuery li inserisce automaticamente nella partizione corretta, in base ai valori della colonna.

Per le colonne TIMESTAMP e DATETIME, le partizioni possono avere granularità oraria, giornaliera, mensile o annuale. Per le colonne DATE, le partizioni possono avere granularità giornaliera, mensile o annuale. I limiti delle partizioni si basano sul fuso orario UTC.

Ad esempio, supponiamo di partizionare una tabella in base a una colonna DATETIME con partizionamento mensile. Se inserisci i seguenti valori nella tabella, le righe vengono scritte nelle seguenti partizioni:

Valore colonna Partizione (mensile)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

Inoltre, vengono create due partizioni speciali:

  • __NULL__: contiene righe con valori NULL nella colonna di partizionamento.
  • __UNPARTITIONED__: contiene righe in cui il valore della colonna di partizionamento è precedente al 1960-01-01 o successivo al 2159-12-31.

Per informazioni sulle tabelle partizionate per colonne di unità di tempo, consulta Crea una tabella partizionata per colonne di unità di tempo.

Partizionamento per data/ora di importazione

Quando crei una tabella partizionata per data di importazione, BigQuery assegna automaticamente le righe alle partizioni in base al momento in cui BigQuery importa i dati. Puoi scegliere una granularità oraria, giornaliera, mensile o annuale per le partizioni. I limiti delle partizioni si basano sul fuso orario UTC.

Se i dati potrebbero raggiungere il numero massimo di partizioni per tabella quando utilizzi una granularità temporale più precisa, utilizza una granularità più ampia. Ad esempio, puoi eseguire il partizionamento per mese anziché per giorno per ridurre il numero di partizioni. Puoi anche eseguire il clustering della colonna di partizionamento per migliorare ulteriormente le prestazioni.

Una tabella partizionata per data di importazione ha una pseudocolonna denominata _PARTITIONTIME. Il valore di questa colonna è l'ora di importazione di ogni riga, troncata al limite della partizione (ad esempio, su base oraria o giornaliera). Ad esempio, supponiamo di creare una tabella partizionata per data di importazione con partizionamento orario e di inviare i dati nei seguenti orari:

Data di importazione _PARTITIONTIME Partizione (oraria)
2021-05-07 17:22:00 2021-05-07 17:00:00 2021050717
2021-05-07 17:40:00 2021-05-07 17:00:00 2021050717
2021-05-07 18:31:00 2021-05-07 18:00:00 2021050718

Poiché la tabella in questo esempio utilizza il partizionamento orario, il valore di _PARTITIONTIME viene troncato a un limite di un'ora. BigQuery utilizza questo valore per determinare la partizione corretta per i dati.

Puoi anche scrivere dati in una partizione specifica. Ad esempio, potresti voler caricare dati storici o apportare modifiche in base ai fusi orari. Puoi utilizzare qualsiasi data valida compresa tra 0001-01-01 e 9999-12-31. Tuttavia, le istruzioni DML non possono fare riferimento a date precedenti al 1° gennaio 1970 o successive al 31 dicembre 2159. Per saperne di più, consulta Scrivi dati in una partizione specifica.

Anziché utilizzare _PARTITIONTIME, puoi anche utilizzare _PARTITIONDATE. La pseudo-colonna _PARTITIONDATE contiene la data UTC corrispondente al valore nella pseudo-colonna _PARTITIONTIME.

Seleziona il partizionamento giornaliero, orario, mensile o annuale

Quando partizioni una tabella in base alla colonna dell'unità di tempo o al tempo di importazione, scegli se le partizioni hanno una granularità giornaliera, oraria, mensile o annuale.

  • Il partizionamento giornaliero è il tipo di partizionamento predefinito. Il partizionamento giornaliero è una buona scelta quando i dati sono distribuiti su un ampio intervallo di date o se vengono aggiunti continuamente nel tempo.

  • Scegli il partizionamento orario se le tue tabelle contengono un volume elevato di dati che coprono un intervallo di date breve, in genere meno di sei mesi di valori di timestamp. Se scegli il partizionamento orario, assicurati che il numero di partizioni rientri nei limiti di partizione.

  • Scegli il partizionamento mensile o annuale se le tue tabelle hanno una quantità di dati relativamente ridotta per ogni giorno, ma coprono un intervallo di date ampio. Questa opzione è consigliata anche se il tuo workflow richiede l'aggiornamento o l'aggiunta frequente di righe che coprono un intervallo di date ampio (ad esempio, più di 500 date). In questi scenari, utilizza il partizionamento mensile o annuale insieme al clustering sulla colonna di partizionamento per ottenere le prestazioni migliori. Per ulteriori informazioni, consulta Combinazione di tabelle raggruppate e partizionate in questo documento.

Combinazione di tabelle raggruppate in cluster e partizionate

Puoi combinare il partizionamento delle tabelle con il clustering delle tabelle per ottenere un ordinamento granulare e ottimizzare ulteriormente le query.

Una tabella in cluster contiene colonne in cluster che ordinano i dati in base alle proprietà di ordinamento definite dall'utente. I dati all'interno di queste colonne raggruppate vengono ordinati in blocchi di archiviazione di dimensioni adattive in base alle dimensioni della tabella. Quando esegui una query che filtra in base alla colonna in cluster, BigQuery analizza solo i blocchi pertinenti in base alle colonne in cluster anziché l'intera tabella o partizione della tabella. In un approccio combinato che utilizza sia il partizionamento sia il clustering delle tabelle, i dati della tabella vengono prima segmentati in partizioni, quindi vengono raggruppati in cluster all'interno di ogni partizione in base alle colonne di clustering.

Quando crei una tabella con clustering e partizionamento, puoi ottenere un ordinamento più granulare, come mostrato nel seguente diagramma:

Confronto tra tabelle non raggruppate in cluster o partizionate e tabelle raggruppate in cluster e partizionate.

Partizionamento e sharding

Lo sharding delle tabelle è la pratica di archiviare i dati in più tabelle, utilizzando un prefisso di denominazione come [PREFIX]_YYYYMMDD.

Il partizionamento è consigliato rispetto allo sharding delle tabelle, perché le tabelle partizionate offrono prestazioni migliori. Con le tabelle con sharding, BigQuery deve gestire una copia dello schema e dei metadati per ogni tabella. BigQuery potrebbe anche dover verificare le autorizzazioni per ogni tabella sottoposta a query. Questa pratica aumenta anche l'overhead delle query e influisce sulle prestazioni delle query.

Se in precedenza hai creato tabelle con sharding per data, puoi convertirle in una tabella partizionata per data di importazione. Per saperne di più, consulta Converti le tabelle con sharding per data in tabelle partizionate per data di acquisizione.

Decoratori di partizioni

I decoratori di partizione consentono di fare riferimento a una partizione in una tabella. Ad esempio, puoi utilizzarli per scrivere dati in una partizione specifica.

Un decoratore di partizione ha la forma table_name$partition_id, dove il formato del segmento partition_id dipende dal tipo di partizionamento:

Tipo di partizionamento Formato Esempio
Ogni ora yyyymmddhh my_table$2021071205
Ogni giorno yyyymmdd my_table$20210712
Mensile yyyymm my_table$202107
Annuale yyyy my_table$2021
Intervallo di numeri interi range_start my_table$40

Sfoglia i dati in una partizione

Per sfogliare i dati in una partizione specificata, utilizza il comando bq head con un decoratore di partizione.

Ad esempio, il seguente comando elenca tutti i campi nelle prime 10 righe di my_dataset.my_table nella partizione 2018-02-24:

    bq head --max_rows=10 'my_dataset.my_table$20180224'

Esporta i dati della tabella

L'esportazione di tutti i dati da una tabella partizionata è uguale all'esportazione dei dati da una tabella non partizionata. Per saperne di più, consulta Esportazione dei dati delle tabelle.

Per esportare i dati da una singola partizione, utilizza il comando bq extract e aggiungi il decoratore della partizione al nome della tabella. Ad esempio, my_table$20160201. Puoi anche esportare i dati dalle partizioni __NULL__ e __UNPARTITIONED__ aggiungendo i nomi delle partizioni al nome della tabella. Ad esempio, my_table$__NULL__ o my_table$__UNPARTITIONED__.

Limitazioni

Le tabelle partizionate presentano le seguenti limitazioni:

  • Non puoi utilizzare SQL precedente per eseguire query su tabelle partizionate o per scrivere i risultati delle query su tabelle partizionate.

  • BigQuery non supporta il partizionamento in base a più colonne. Per partizionare una tabella è possibile utilizzare una sola colonna.

  • Non puoi convertire direttamente una tabella non partizionata esistente in una tabella partizionata. La strategia di partizionamento viene definita al momento della creazione della tabella. Utilizza invece l'istruzione CREATE TABLE per creare una nuova tabella partizionata eseguendo una query sui dati della tabella esistente.

  • Le tabelle partizionate per colonne in base all'unità di tempo sono soggette alle seguenti limitazioni:

    • La colonna di partizionamento deve essere una colonna scalare DATE, TIMESTAMP o DATETIME. La modalità della colonna può essere REQUIRED o NULLABLE, ma non REPEATED (basata su array).
    • La colonna di partizionamento deve essere un campo di primo livello. Non puoi utilizzare un campo foglia di un RECORD (STRUCT) come colonna di partizionamento.

    Per informazioni sulle tabelle partizionate per colonne di unità di tempo, consulta Crea una tabella partizionata per colonne di unità di tempo.

  • Le tabelle partizionate per intervallo di numeri interi sono soggette alle seguenti limitazioni:

    • La colonna di partizionamento deve essere una colonna INTEGER. Sebbene la modalità della colonna possa essere REQUIRED o NULLABLE, non può essere REPEATED (basata su array).
    • La colonna di partizionamento deve essere un campo di primo livello. Non puoi utilizzare un campo foglia di un RECORD (STRUCT) come colonna di partizionamento.

    Per informazioni sulle tabelle partizionate per intervalli di numeri interi, consulta Crea una tabella partizionata per intervalli di numeri interi.

Quote e limiti

Le tabelle partizionate hanno limiti definiti in BigQuery. Per ulteriori informazioni su tutte le quote e i limiti, consulta Quote e limiti.

Quote e limiti per i job relativi alle tabelle partizionate

Le quote e i limiti si applicano anche ai diversi tipi di job che puoi eseguire sulle tabelle partizionate, tra cui:

Errori relativi alla quota per il numero di modifiche delle partizioni per le tabelle partizionate per colonna

BigQuery restituisce questo errore quando la tabella partizionata per colonne raggiunge la quota del numero di modifiche alle partizioni consentite al giorno. Le modifiche alle partizioni includono il totale di tutti i job di caricamento, job di copia e job di query che aggiungono o sovrascrivono una partizione di destinazione.

Per visualizzare il valore del limite Numero di modifiche alle partizioni per tabella partizionata per colonna al giorno, consulta Tabelle partizionate.

Messaggio di errore

Quota exceeded: Your table exceeded quota for
Number of partition modifications to a column partitioned table

Risoluzione

Questa quota non può essere aumentata. Per risolvere questo errore di quota, segui questi passaggi:

  • Modifica il partizionamento della tabella in modo da avere più dati in ogni partizione, così da ridurre il numero totale di partizioni. Ad esempio, passa dal partizionamento per giorno al partizionamento per mese o modifica il modo in cui partizioni la tabella.
  • Utilizza il clustering anziché il partizionamento.
  • Se carichi spesso dati da più file di piccole dimensioni archiviati in Cloud Storage che utilizzano un job per file, combina più job di caricamento in un unico job. Puoi caricare da più URI Cloud Storage con un elenco separato da virgole (ad esempio gs://my_path/file_1,gs://my_path/file_2) o utilizzando i caratteri jolly (ad esempio gs://my_path/*).

    Per saperne di più, consulta Caricamento dei dati in batch.

  • Se utilizzi job di caricamento, selezione o copia per aggiungere singole righe di dati a una tabella, ad esempio, ti consigliamo di raggruppare più job in un unico job. BigQuery non offre prestazioni ottimali se utilizzato come database relazionale. Come best practice, evita di eseguire azioni di aggiunta frequenti su singole righe.
  • Per aggiungere dati a una frequenza elevata, valuta la possibilità di utilizzare l'API BigQuery Storage Write (gRPC). È una soluzione consigliata per l'importazione dati ad alte prestazioni. L'API BigQuery Storage Write (gRPC) offre funzionalità efficaci, tra cui la semantica di consegna "exactly-once". Per informazioni su limiti e quote, consulta API Storage Write e per visualizzare i costi di utilizzo di questa API, consulta Prezzi di importazione dati di BigQuery.
  • Per monitorare il numero di partizioni modificate in una tabella, utilizza la vista INFORMATION_SCHEMA.
  • Per informazioni sull'ottimizzazione dei job di caricamento delle tabelle per evitare di raggiungere i limiti di quota, consulta Ottimizza i job di caricamento.

Prezzi delle tabelle

Quando crei e utilizzi tabelle partizionate in BigQuery, gli addebiti si basano sulla quantità di dati archiviati nelle partizioni e sulle query eseguite sui dati:

Molte operazioni tabella partizionata sono senza costi, tra cui il caricamento dei dati nelle partizioni, la copia delle partizioni e l'esportazione dei dati dalle partizioni. Anche se sono senza costi, queste operazioni sono soggette alle quote e ai limiti di BigQuery. Per informazioni su tutte le operazioni senza costi, consulta Operazioni senza costi nella pagina dei prezzi.

Per le best practice per il controllo dei costi in BigQuery, consulta Controllo dei costi in BigQuery

Sicurezza delle tabelle

Il controllo degli accessi per le tabelle partizionate è lo stesso del controllo dell'accesso per le tabelle standard. Per saperne di più, consulta Introduzione ai controlli di accesso alle tabelle.

Passaggi successivi