Introduzione alle tabelle partizionate

Una tabella partizionata è divisa in segmenti, chiamati partizioni, che semplificano la gestione e l'esecuzione di query sui dati. Dividendo una tabella di grandi dimensioni in partizioni più piccole, puoi migliorare le prestazioni delle query e controllare i costi riducendo il numero di byte letti da una query. Per eseguire il partizionamento delle tabelle, specifica una colonna di partizione da utilizzare 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 ignorare le partizioni rimanenti. Questa procedura è chiamata eliminazione.

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 relativi alle proprietà di ordinamento in tutte le operazioni che la modificano. I metadati consentono a BigQuery di stimare in modo più accurato il costo di una query prima che venga eseguita.

Quando utilizzare il partizionamento

Valuta la possibilità di partizionare una tabella nei seguenti scenari:

  • Vuoi migliorare le prestazioni delle query scansionando solo una parte di una tabella.
  • Le operazioni della tabella superano una quota di tabella standard e puoi limitare l'ambito delle operazioni della tabella a valori specifici delle colonne di partizione, consentendo di raggiungere quote per le tabelle partizionate più elevate.
  • Vuoi determinare i costi delle query prima di eseguirla. BigQuery fornisce stime dei costi delle query prima che la query venga eseguita su una tabella partizionata. Calcola una stima dei costi delle query eliminando una tabella partizionata, quindi eseguendo una prova della query per stimare i costi delle query.
  • Vuoi una qualsiasi delle seguenti funzionalità di gestione a livello di partizione:

Valuta la possibilità di clustering una tabella anziché partizionare una tabella nelle seguenti circostanze:

  • Hai bisogno di una maggiore granularità rispetto a quella consentita dal partizionamento.
  • Le query di solito utilizzano filtri o aggregazione su più colonne.
  • La cardinalità del numero di valori in una colonna o in un gruppo di colonne è elevata.
  • Non hai bisogno di rigorose stime dei costi prima dell'esecuzione della query.
  • Il partizionamento determina 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 delle tabelle partizionate.
  • Le operazioni DML modificano spesso (ad esempio, a intervalli di pochi minuti) la maggior parte delle partizioni nella tabella.

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

Puoi anche combinare clustering e partizionamento delle tabelle per ottenere un ordinamento più granulare. Per ulteriori informazioni su questo approccio, consulta Combinare le tabelle in cluster e di partizionamento.

Tipi di partizionamento

Questa sezione descrive i diversi modi per partizionare una tabella.

Partizionamento di intervalli interi

Puoi eseguire il partizionamento di una tabella in base agli intervalli di valori in una specifica colonna INTEGER. Per creare una tabella partizionata con intervallo di numeri interi, devi fornire:

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

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

Argomento Valore
nome colonna customer_id
avvio 0
end 100
intervallo 10

La tabella è partizionata nella colonna customer_id in intervalli dell'intervallo 10. I valori da 0 a 9 vanno in una partizione, i valori da 10 a 19 vanno 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 vanno in una partizione denominata __NULL__.

Per informazioni sulle tabelle partizionate con intervallo di numeri interi, consulta Creare una tabella partizionata con intervallo di numeri interi.

Partizionamento delle colonne in unità di tempo

Puoi eseguire il partizionamento di una tabella in base a una colonna DATE, TIMESTAMP o DATETIME nella tabella. Quando scrivi i dati nella tabella, BigQuery li inserisce automaticamente nella partizione corretta, in base ai valori nella 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 una granularità giornaliera, mensile o annuale. I limiti delle partizioni si basano sul fuso orario UTC.

Ad esempio, supponi di partizionare una tabella in una colonna DATETIME con il 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 le righe con valori NULL nella colonna di partizionamento.
  • __UNPARTITIONED__: contiene le righe in cui il valore della colonna di partizionamento è precedente a 1960-01-01 o successivo a 2159-12-31.

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

Partizionamento del tempo di importazione

Quando crei una tabella partizionata in base al tempo di importazione, BigQuery assegna automaticamente righe alle partizioni in base al momento in cui BigQuery importa i dati. Puoi scegliere la 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 limite di 4000 partizioni per tabella con una granularità temporale più precisa, utilizza invece una granularità più approssimativa. Ad esempio, puoi eseguire la partizione per mese anziché per giorno per ridurre il numero di partizioni. Puoi anche cluster la colonna di partizione per migliorare ulteriormente le prestazioni.

Una tabella partizionata per data di importazione ha una pseudocolonna denominata _PARTITIONTIME. Il valore di questa colonna corrisponde al tempo di importazione di ogni riga, troncato al limite della partizione (ad esempio, oraria o giornaliera). Ad esempio, supponi di creare una tabella partizionata per data di importazione con partizionamento orario e inviare i dati nei seguenti orari:

Data/ora di importazione _PARTITIONTIME Partizione (all'ora)
07/05/2021 17:22:00 07/05/2021 17:00:00 2021050717
07/05/2021 17:40:00 07/05/2021 17:00:00 2021050717
07/05/2021 18:31:00 07/05/2021 18:00:00 2021050718

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

Puoi anche scrivere dati in una partizione specifica. Ad esempio, potresti caricare i dati storici o adeguarti 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/12/2159. Per ulteriori informazioni, consulta Scrivere dati in una partizione specifica.

Anziché usare _PARTITIONTIME, puoi anche usare _PARTITIONDATE. La pseudocolonna _PARTITIONDATE contiene la data UTC corrispondente al valore nella pseudocolonna _PARTITIONTIME.

Seleziona il partizionamento giornaliero, orario, mensile o annuale

Quando parti una tabella in base alla colonna dell'unità di tempo o alla data e ora di importazione, puoi scegliere 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 in un ampio intervallo di date o se vengono aggiunti continuamente nel tempo.

  • Scegli il partizionamento orario se le tabelle hanno un volume elevato di dati relativi a un breve intervallo di date, in genere meno di sei mesi di valori di timestamp. Se scegli il partizionamento orario, assicurati che il conteggio delle partizioni rimanga entro i limiti di partizione.

  • Scegli il partizionamento mensile o annuale se le tabelle contengono una quantità di dati relativamente ridotta per ogni giorno, ma coprono un intervallo di date ampio. Questa opzione è consigliata anche se il flusso di lavoro richiede l'aggiornamento frequente o l'aggiunta 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 nella colonna di partizionamento per ottenere le migliori prestazioni. Per ulteriori informazioni, consulta Combinare le tabelle in cluster e di partizionamento in questo documento.

Combinare tabelle in cluster e partizionate

Puoi combinare il partizionamento delle tabelle con il clustering delle tabelle per ottenere un ordinamento più granulare ai fini dell'ottimizzazione delle 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 in cluster 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é all'intera partizione della tabella o della tabella. Con un approccio combinato che utilizza il partizionamento e il clustering delle tabelle, prima devi segmentare i dati delle tabelle in partizioni, quindi eseguire il clustering dei dati all'interno di ogni partizione in base alle colonne di clustering.

Quando crei una tabella in cluster e partizionata, puoi ottenere un ordinamento più granulare, come mostra il seguente diagramma:

Confronto tra tabelle non in cluster o partizionate con tabelle 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 anziché lo sharding delle tabelle, perché le tabelle partizionate hanno prestazioni migliori. Con le tabelle con sharding, BigQuery deve conservare 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 inoltre aumenta l'overhead delle query e influisce sulle prestazioni delle query.

Se in precedenza hai creato tabelle con suddivisione in base alle date, puoi convertirle in una tabella partizionata per data di importazione. Per maggiori informazioni, consulta Convertire le tabelle con suddivisione in base alle date in tabelle partizionate per data di importazione.

Decoratori di partizioni

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

Un decorator della partizione ha il formato table_name$partition_id, in cui 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
Ogni anno yyyy my_table$2021
Intervallo di numeri interi range_start my_table$40

Sfogliare i dati in una partizione

Per sfogliare i dati in una partizione specificata, utilizza il comando bq head con un decorator 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_tablee$20180224'

Esporta i dati delle tabelle

L'esportazione di tutti i dati da una tabella partizionata è la stessa procedura dell'esportazione dei dati da una tabella non partizionata. Per ulteriori informazioni, consulta la sezione Esportazione dei dati delle tabelle.

Per esportare i dati da una singola partizione, utilizza il comando bq extract e aggiungi il decorator 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

Non puoi utilizzare SQL precedente per eseguire query su tabelle partizionate o scrivere risultati di query nelle tabelle partizionate.

BigQuery non supporta il partizionamento in base a più colonne. È possibile utilizzare una sola colonna per partizionare una tabella.

Le tabelle partizionate per colonne di 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 può essere REPEATED (basata su array).
  • La colonna di partizionamento deve essere un campo di primo livello. Non puoi utilizzare un campo foglia di RECORD (STRUCT) come colonna di partizionamento.

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

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

  • La colonna di partizionamento deve essere una colonna INTEGER. Anche se la modalità della colonna può 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 RECORD (STRUCT) come colonna di partizionamento.

Per informazioni sulle tabelle partizionate con intervallo di numeri interi, consulta Creare una tabella partizionata con intervallo di numeri interi.

Quote e limiti

Le tabelle partizionate hanno limiti definiti in BigQuery.

Quote e limiti si applicano anche ai diversi tipi di job che puoi eseguire su tabelle partizionate, tra cui:

Per saperne di più su tutte le quote e i limiti, consulta Quote e limiti.

Prezzi della tabella

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

Molte operazioni tabella partizionata sono gratuite, tra cui il caricamento dei dati nelle partizioni, la copia delle partizioni e l'esportazione dei dati dalle partizioni. Sebbene siano gratuite, queste operazioni sono soggette a quote e limiti di BigQuery. Per informazioni su tutte le operazioni gratuite, consulta Operazioni gratuite nella pagina dei prezzi.

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

Sicurezza della tabella

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.

Passaggi successivi