Introduzione alle tabelle partizionate

Una tabella partizionata è divisa in segmenti, denominati partizioni, che semplificano la gestione e l'interrogazione dei 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 le tabelle specificando una colonna di partizione utilizzata 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 le partizioni rimanenti. Questo processo è chiamato potatura.

In una tabella partizionata, i dati vengono archiviati in blocchi fisici, ciascuno dei quali contiene una partizione di dati. Ogni tabella partizionata gestisce vari metadati relativi alle 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

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

  • Vuoi migliorare le prestazioni delle query scansionando solo una parte di una tabella.
  • L'operazione della tabella supera una quota di tabella standard e puoi limitare l'ambito delle operazioni della tabella a valori specifici delle colonne di partizione, 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 delle query potando una tabella partizionata, quindi esegui un dry run della query per stimare i costi delle query.
  • Vuoi una delle seguenti funzionalità di gestione a livello di partizione:

Valuta la possibilità di raggruppare una tabella anziché suddividerla nelle seguenti circostanze:

  • Hai bisogno di una granularità maggiore di quella consentita dal partizionamento.
  • Le query in genere utilizzano filtri o aggregazioni su più colonne.
  • La cardinalità del numero di valori in una colonna o in un gruppo di colonne è elevata.
  • Non sono necessarie stime dei costi rigorose prima dell'esecuzione della query.
  • La partizione genera una piccola quantità di dati per partizione (circa meno di 10 GB). La creazione di molte piccole partizioni aumenta i metadati della tabella e può influire sui tempi di accesso ai metadati quando esegui query sulla tabella.
  • La partizione genera un numero elevato di partizioni, che supera 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 a proprietà di ordinamento definite dall'utente.

Puoi anche combinare il clustering e il partizionamento delle tabelle per ottenere un'ordinamento più granulare. Per ulteriori informazioni su questo approccio, consulta Combinazione di tabelle 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 con intervallo di numeri interi, specifica:

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

Ad esempio, supponiamo di creare una partizione dell'intervallo di 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 vanno in una partizione, i valori da 10 a 19 vanno nella partizione successiva e così via fino a 99. I valori che non rientrano in questo intervallo vengono inseriti in una partizione chiamata __UNPARTITIONED__. Tutte le righe in cui customer_id è NULL vengono inserite 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 per unità di tempo

Puoi partizionare una tabella in base a una colonna DATE, TIMESTAMP o DATETIME al suo interno. 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 sull'ora UTC.

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

Valore della 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 partizione.
  • __UNPARTITIONED__: contiene le righe in cui il valore della colonna di suddivisione è precedente al 1° gennaio 1960 o successivo al 31 dicembre 2159.

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

Partizionamento per data 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 la granularità oraria, giornaliera, mensile o annuale per le partizioni. I confini delle partizioni si basano sull'ora UTC.

Se i dati potrebbero raggiungere il numero massimo di partizioni per tabella quando utilizzi una granularità temporale più fine, utilizza una granularità più approssimativa. Ad esempio, puoi eseguire la partizione per mese anziché per giorno per ridurre il numero di partizioni. Puoi anche clusterizzare la colonna della partizione per migliorare ulteriormente il rendimento.

Una tabella partizionata per data di importazione ha una pseudocolonna denominata _PARTITIONTIME. Il valore di questa colonna è la data e l'ora di importazione di ogni riga, troncata al confine della partizione (ad es. ogni ora o ogni giorno). Ad esempio, supponiamo di creare una tabella partizionata per data di importazione con partizione oraria e di inviare i dati alle seguenti ore:

Data di importazione _PARTITIONTIME Partizione (ora)
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
07-05-2021 18:31:00 07-05-2021 18:00:00 2021050718

Poiché la tabella in questo esempio utilizza la partizione oraria, il valore di _PARTITIONTIME viene troncato a un confine di 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 i dati storici o apportare aggiustamenti in base ai fusi orari. Puoi utilizzare qualsiasi data valida tra 01-01-0001 e 31-12-9999. Tuttavia, le istruzioni DML non possono fare riferimento a date precedenti al 01-01-1970 o successive al 31-12-2159. Per ulteriori informazioni, consulta Scrivere dati in una partizione specifica.

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

Seleziona la suddivisione giornaliera, oraria, mensile o annuale

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

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

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

  • Scegli la partizione mensile o annuale se le tabelle contengono una quantità relativamente ridotta di dati per ogni giorno, ma coprono un intervallo di date ampio. Questa opzione è consigliata anche se il tuo flusso di lavoro richiede l'aggiornamento o l'aggiunzione frequente di righe che coprono un ampio intervallo di date (ad esempio più di 500 date). In questi scenari, utilizza il partizionamento mensile o annuale insieme al clustering nella colonna di partizionamento per ottenere il rendimento migliore. Per ulteriori informazioni, consulta Combinazione di tabelle clusterizzate e partizionate in questo documento.

Combinazione di tabelle in cluster e partizionate

Puoi combinare il partizionamento delle tabelle con il clustering delle tabelle per ottenere un'ordinamento granulare per un'ulteriore 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 le cui dimensioni si adattano 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 tabella o alla partizione della tabella. In un approccio combinato che utilizza sia il partitioning sia il clustering delle tabelle, devi prima segmentare i dati della tabella in partizioni, quindi raggruppare i dati all'interno di ogni partizione in base alle colonne di clustering.

Quando crei una tabella clusterizzata e partizionata, puoi ottenere un'ordinamento più granulare, come mostrato nel seguente diagramma:

Confronto di tabelle non in cluster o partizionate con tabelle in cluster e partizionate.

Partizionamento e suddivisione in parti

Lo sharding delle tabelle consiste nell'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 hanno un rendimento migliore. Con le tabelle suddivise in parti, BigQuery deve mantenere 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 il sovraccarico delle query e influisce sulle relative prestazioni.

Se in precedenza hai creato tabelle con suddivisione in base alla data, puoi convertirle in una tabella partizionata per data di importazione. Per ulteriori informazioni, consulta Convertire le tabelle con partizioni per data in tabelle partizionate in base al momento dell'importazione.

Decorator per partizioni

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

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

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

Sfogliare i dati in una partizione

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

Esportare i dati delle tabelle

L'esportazione di tutti i dati da una tabella partizionata è la stessa procedura utilizzata per esportare i dati da una tabella non partizionata. Per ulteriori informazioni, consulta Esportare i dati delle tabelle.

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

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

BigQuery non supporta il partizionamento per più colonne. Per partizionare una tabella può essere utilizzata una sola colonna.

Le tabelle partizionate per colonne di unità di tempo sono soggette alle seguenti limitazioni:

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

Per informazioni sulle tabelle partizionate per colonne di 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 partizione 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 partizione deve essere un campo di primo livello. Non puoi utilizzare un campo foglia da un 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.

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

Per ulteriori informazioni su tutte le quote e i limiti, consulta Quote e limiti.

Prezzi delle tabelle

Quando crei e utilizzi tabelle partizionate in BigQuery, i tuoi addebiti dipendono dalla quantità di dati archiviati nelle partizioni e dalle query eseguite sui dati:

Molte operazioni sulle tabelle partizionate sono gratuite, tra cui il caricamento dei dati nelle partizioni, la copia delle partizioni e l'esportazione dei dati dalle partizioni. Sebbene gratuite, queste operazioni sono soggette alle quote e ai 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, consulta Controllo dei costi in BigQuery

Sicurezza delle tabelle

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