Introduzione alle tabelle partizionate

Una tabella partizionata è divisa in segmenti, denominati partizioni, che lo rendono gestire ed eseguire query sui tuoi dati. Dividendo una tabella grande in più puoi migliorare le prestazioni delle query e controllare i costi riducendo di byte letti da una query. Puoi partizionare le tabelle specificando una 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. Questa procedura è chiamata eliminazione.

In una tabella partizionata, i dati sono archiviati in blocchi fisici, ognuno dei quali contiene una partizione di dati. Ogni tabella partizionata conserva vari metadati le proprietà di ordinamento in tutte le operazioni che lo modificano. I metadati consentono a BigQuery 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 analizzando solo una parte di un .
  • Le operazioni tabella superano una quota standard e puoi definire l'ambito delle operazioni tabella a valori specifici della colonna di partizione, consentendo quote di tabelle partizionate più elevate.
  • Vuoi determinare i costi delle query prima dell'esecuzione. 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 query dry run per stimare i costi delle query.
  • Vuoi utilizzare una delle seguenti funzionalità di gestione a livello di partizione:

Valuta la possibilità di eseguire il clustering di una tabella il partizionamento di una tabella nelle seguenti circostanze:

  • Hai bisogno di più granularità rispetto a quanto consentito dal partizionamento.
  • In genere, le query utilizzano filtri o aggregazione su più colonne.
  • La cardinalità del numero di valori in una colonna o in un gruppo di colonne è grandi.
  • Non è necessario avere stime dei costi rigorose prima dell'esecuzione della query.
  • Il partizionamento si traduce in una piccola quantità di dati per partizione (circa meno di 10 GB). La creazione di molte partizioni piccole aumenta metadati della tabella e può influire sui tempi di accesso ai metadati quando si esegue una query .
  • Il partizionamento produce un numero elevato di partizioni, superando limiti per le tabelle partizionate.
  • Le tue operazioni DML cambiano spesso (ad esempio a intervalli di pochi minuti) delle partizioni di memoria nella tabella.

In questi casi, il clustering delle tabelle consente di accelerare le query mediante il clustering 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 una e l'ordinamento dei dati. Per saperne di più su questo approccio, consulta Combinazione di tabelle in cluster e di partizionamento.

Tipi di partizionamento

Questa sezione descrive i diversi modi per partizionare una tabella.

Partizionamento per intervalli di numeri interi

Puoi eseguire il partizionamento di una tabella in base a intervalli di valori in un intervallo di valori INTEGER specifico colonna. Per creare una tabella partizionata con intervallo di numeri interi, fornisci:

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

Supponiamo, ad esempio, di creare una partizione di intervallo di numeri interi con quanto segue specifiche:

Argomento Valore
nome colonna customer_id
start 0
end 100
intervallo 10

La tabella è partizionata nella colonna customer_id in intervalli di intervallo 10. I valori da 0 a 9 vanno in una partizione, i valori da 10 a 19 vanno in quella successiva ecc., fino a 99. I valori al di fuori di questo intervallo vengono inseriti in una partizione denominato __UNPARTITIONED__. Tutte le righe in cui customer_id è NULL entrano in un denominata __NULL__.

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

Partizionamento delle colonne in base all'unità di tempo

Puoi eseguire il partizionamento di una tabella in base a una colonna DATE, TIMESTAMP o DATETIME nel . Quando scrivi dati nella tabella, BigQuery automaticamente inserisce i dati nella partizione corretta, in base ai valori nella colonna.

Per TIMESTAMP e DATETIME colonne, le partizioni possono avere su base oraria, granularità giornaliera, mensile o annuale. Per DATE colonne, le partizioni possono possono avere una granularità giornaliera, mensile o annuale. I limiti delle partizioni si basano su Ora UTC.

Ad esempio, supponi di eseguire il partizionamento di una tabella su una colonna DATETIME con partizionalmente mensile. Se inserisci i seguenti valori nella tabella, 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 NULL valori nella colonna di partizionamento.
  • __UNPARTITIONED__: contiene le righe in cui il valore del partizionamento è precedente a 1960-01-01 o successivo a 2159-12-31.

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

Partizionamento della data di importazione

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

Se i tuoi dati possono raggiungere il numero massimo di partizioni per tabella quando utilizzi un una granularità temporale più fine, usa invece una granularità più bassa. Ad esempio, può eseguire la partizione per mese anziché per giorno per ridurre il numero di partizioni. Puoi anche eseguire il 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 è il tempo di importazione per ogni riga, troncato al limite di partizione, ad esempio orario o giornaliero. Ad esempio, supponiamo che tu una tabella partizionata per data di importazione con partizionamento orario e invia ai seguenti orari:

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

Poiché la tabella in questo esempio utilizza il partizionamento orario, il valore di _PARTITIONTIME è troncato al 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 per caricare dati storici o adattarsi ai fusi orari. Puoi utilizzare qualsiasi data valida tra 0001-01-01 e 9999-12-31. Tuttavia, Istruzioni DML non può fare riferimento a date precedenti al 1° gennaio 1970 o successive al 31/12/2159. Per ulteriori informazioni le informazioni, vedi Scrivi i dati in una partizione specifica.

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

Seleziona il partizionamento giornaliero, orario, mensile o annuale

Quando esegui il partizionamento di una tabella in base alla colonna dell'unità di tempo o alla data di importazione, scegli con 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 tue tabelle hanno un volume elevato di dati che copre un intervallo di date breve, in genere meno di sei mesi i valori di timestamp. Se scegli il partizionamento orario, assicurati che la partizione il conteggio rimane all'interno limiti di partizione.

  • Scegli il partizionamento mensile o annuale se le tabelle hanno una durata relativamente quantità ridotte di dati giornalieri, ma coprono un intervallo di date ampio. Questo è consigliata anche se il flusso di lavoro deve essere aggiornato o aggiungendo 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 sulla colonna di partizionamento per ottenere le migliori prestazioni. Per ulteriori informazioni le informazioni, vedi Combinazione di tabelle in cluster e di partizionamento 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 a proprietà di ordinamento definite dall'utente. I dati all'interno di queste colonne in cluster vengono ordinati in blocchi di archiviazione con 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 la partizione della tabella. In un approccio combinato utilizzando entrambe le tabelle il partizionamento e il clustering, per prima cosa segmenti i dati delle tabelle in partizioni, e raggruppa i dati all'interno di ogni partizione in base alle colonne di clustering.

Quando crei una tabella in cluster e partizionata, puoi ottenere un ordinamento 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.

Si consiglia di partizionare rispetto allo sharding delle tabelle, in quanto le tabelle partizionate o migliorare il rendimento. Per le tabelle con sharding, BigQuery deve mantenere dello schema e dei metadati per ogni tabella. BigQuery potrebbe devi verificare anche le autorizzazioni per ogni tabella su cui è stata eseguita la query. Questa prassi aggiunge anche per l'overhead delle query e influisce sulle loro prestazioni.

Se in precedenza hai creato tabelle con frazioni di date, puoi convertirle in un una tabella partizionata per data di importazione. Per ulteriori informazioni, vedi Converti le tabelle con frazioni di data in tabelle partizionate in base alla data di importazione.

Decoratori partizioni

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

Un decorator della partizione ha il formato 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 intero range_start my_table$40

Sfogliare i dati in una partizione

Per sfogliare i dati in una partizione specificata, utilizza 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_tablee$20180224'

Esporta i dati della tabella

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

Per esportare i dati da una singola partizione, usa il comando bq extract e aggiungi il decorator della partizione il nome della tabella. Ad esempio, my_table$20160201. Puoi anche esportare i dati __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 per scrivere i risultati delle query alle tabelle partizionate.

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

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

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

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

Le tabelle partizionate con intervalli interi sono soggette alle seguenti limitazioni:

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

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

Quote e limiti

Le tabelle partizionate hanno dei limiti definiti in BigQuery.

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

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

Prezzi tabella

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

Molte operazioni tabella partizionata sono gratuite, incluso il caricamento di dati partizioni, copiare partizioni ed esportare dati dalle partizioni. Sebbene gratuito, queste operazioni sono soggette alle Quote e limiti. Per informazioni su tutte le operazioni gratuite, consulta la sezione Operazioni gratuite nella pagina dei prezzi.

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

Sicurezza dei tavoli

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

Passaggi successivi