Esegui query sulle tabelle partizionate

Questo documento descrive alcune considerazioni specifiche per eseguire query sulle tabelle partizionate in BigQuery.

Per informazioni generali sull'esecuzione di query in BigQuery, consulta Eseguire query interattive e in batch.

Panoramica

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 eliminazione delle partizioni.

L'eliminazione delle partizioni è il meccanismo utilizzato da BigQuery per eliminare le partizioni non necessarie dall'analisi di input. Le partizioni eliminate non sono incluse nel calcolo dei byte analizzati dalla query. In generale, l'eliminazione delle partizioni contribuisce a ridurre il costo delle query.

I comportamenti di potatura variano in base ai diversi tipi di partizione, pertanto potresti notare una differenza nei byte elaborati quando esegui query su tabelle partizionate in modo diverso, ma altrimenti identiche. Per stimare quanti byte verrà elaborata da una query, esegui una prova secca.

Eseguire query su una tabella partizionata per colonne di unità di tempo

Per eliminare le partizioni quando esegui una query su una tabella partizionata per colonne di unità di tempo, includere un filtro nella colonna di partizionamento.

Nell'esempio seguente, supponiamo che dataset.table sia partizionato in base alla colonna transaction_date. La query di esempio elimina le date precedenti al giorno 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Eseguire query su una tabella partizionata per data di importazione

Le tabelle partizionate per data di importazione contengono una pseudocolonna denominata _PARTITIONTIME, che è la colonna di partizione. Il valore della colonna è l'ora di importazione UTC per ogni riga, troncata al confine della partizione (ad esempio oraria o giornaliera), come valore TIMESTAMP.

Ad esempio, se aggiungi i dati il 15 aprile 2021 alle 08:15:00 UTC, la colonna _PARTITIONTIME per queste righe contiene i seguenti valori:

  • Tabella partizionata per ora: TIMESTAMP("2021-04-15 08:00:00")
  • Tabella partizionata giornaliera: TIMESTAMP("2021-04-15")
  • Tabella partizionata mensile: TIMESTAMP("2021-04-01")
  • Tabella partizionata annuale: TIMESTAMP("2021-01-01")

Se la granularità della partizione è giornaliera, la tabella contiene anche una pseudocolonna chiamata _PARTITIONDATE. Il valore è uguale a _PARTITIONTIME troncato a un valore DATE.

Entrambi questi nomi di pseudocolonne sono riservati. Non puoi creare una colonna con entrambi i nomi in nessuna delle tue tabelle.

Per potare le partizioni, applica un filtro a una di queste colonne. Ad esempio, la seguente query esegue la scansione solo delle partizioni tra le date 1° gennaio 2016 e 2 gennaio 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Per selezionare la pseudocolonna _PARTITIONTIME, devi utilizzare un alias. Ad esempio, la seguente query seleziona _PARTITIONTIME assegnando l'alias pt alla pseudocolonna:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Per le tabelle partizionate giornaliere, puoi selezionare la pseudocolonna _PARTITIONDATE nello stesso modo:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Le pseudocolonne _PARTITIONTIME e _PARTITIONDATE non vengono restituite da un statement SELECT *. Devi selezionarle esplicitamente:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Gestire i fusi orari nelle tabelle partizionate per data di importazione

Il valore di _PARTITIONTIME si basa sulla data UTC in cui viene compilato il campo. Se vuoi eseguire query sui dati in base a un fuso orario diverso da UTC, scegli una delle seguenti opzioni:

  • Modifica le query SQL in base alle differenze di fuso orario.
  • Utilizza i decoratori delle partizioni per caricare i dati in partizioni specifiche in base al momento dell'importazione, in base a un fuso orario diverso dall'UTC.

Migliore rendimento con le pseudocolonne

Per migliorare le prestazioni delle query, utilizza la pseudocolonna _PARTITIONTIME da sola sul lato sinistro di un confronto.

Ad esempio, le seguenti due query sono equivalenti. A seconda delle dimensioni della tabella, la seconda query potrebbe avere un rendimento migliore perché posiziona _PARTITIONTIME da sola sul lato sinistro dell'operatore >. Entrambe le query elaborano la stessa quantità di dati.

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

Per limitare le partizioni sottoposte a scansione in una query, utilizza un'espressione costante nel filtro. La seguente query limita le partizioni da eliminare in base alla prima condizione di filtro nella clausola WHERE. Tuttavia, la seconda condizione del filtro non limita le partizioni sottoposte a scansione, perché utilizza i valori di tabella, che sono dinamici.

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

Per limitare le partizioni sottoposte a scansione, non includere altre colonne in un filtro _PARTITIONTIME. Ad esempio, la seguente query non limita le partizioni sottoposte a scansione, perché field1 è una colonna della tabella.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Se esegui spesso query su un determinato intervallo di date, ti consigliamo di creare una vista che applichi un filtro alla pseudocolonna _PARTITIONTIME. Ad esempio, la seguente dichiarazione crea una vista che include solo i dati dei sette giorni più recenti di una tabella denominata dataset.partitioned_table:

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

Per informazioni sulla creazione di viste, consulta Creare viste.

Esegui una query su una tabella partizionata con intervallo di numeri interi

Per eliminare le partizioni quando esegui una query su una tabella partizionata con intervallo di numeri interi, includere un filtro nella colonna di partizionamento di numeri interi.

Nell'esempio seguente, supponiamo che dataset.table sia una tabella partizionata con intervallo di interi con una specifica di partizione customer_id:0:100:10. La query di esempio esegue la scansione delle tre partizioni che iniziano con 30, 40 e 50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

L'eliminazione delle partizioni non è supportata per le funzioni su una colonna con partizione in un intervallo di interi. Ad esempio, la seguente query esegue la scansione dell'intera tabella.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Esegui query sui dati nello spazio di archiviazione ottimizzato per la scrittura

La partizione __UNPARTITIONED__ memorizza temporaneamente i dati in streaming in una tabella partizionata mentre si trova nello spazio di archiviazione ottimizzato per la scrittura. I dati in streaming direttamente in una partizione specifica di una tabella partizionata non utilizzano la partizione __UNPARTITIONED__. I dati vengono invece trasmessi in streaming direttamente alla partizione.

I dati nello spazio di archiviazione ottimizzato per la scrittura hanno valori NULL nelle colonne _PARTITIONTIME e _PARTITIONDATE.

Per eseguire query sui dati nella partizione __UNPARTITIONED__, utilizza la pseudocolonna _PARTITIONTIME con il valore NULL. Ad esempio:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Per ulteriori informazioni, consulta Streaming in tabelle partizionate.

Best practice per il pruning delle partizioni

Utilizzare un'espressione di filtro costante

Per limitare le partizioni sottoposte a scansione in una query, utilizza un'espressione costante nel filtro. Se utilizzi espressioni dinamiche nel filtro della query, BigQuery deve analizzare tutte le partizioni.

Ad esempio, la seguente query elimina le partizioni perché il filtro contiene un'espressione costante:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Tuttavia, la seguente query non elimina le partizioni perché il filtro WHERE t1.ts = (SELECT timestamp from table where key = 2) non è un'espressione costante, ma dipende dai valori dinamici dei campi timestamp e key:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

Isola la colonna di partizione nel filtro

Isola la colonna di partizione quando esprimi un filtro. I filtri che richiedono dati da più campi per il calcolo non elimineranno le partizioni. Ad esempio, una query con un confronto delle date che utilizza la colonna di partizione e un secondo campo o query contenenti alcune concatenazioni di campi non eliminerà le partizioni.

Ad esempio, il seguente filtro non elimina le partizioni perché richiede un calcolo basato sul campo di partizionamento ts e su un secondo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Richiedere un filtro di partizione nelle query

Quando crei una tabella partizionata, puoi richiedere l'utilizzo di filtri predicati attivando l'opzione Richiedi filtro di partizionamento. Quando questa opzione viene applicata, i tentativi di eseguire query sulla tabella partizionata senza specificare una clausola WHERE generano il seguente errore:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Affinché il filtro sia considerato idoneo per l'eliminazione della partizione, deve essere presente almeno un predicato che fa riferimento solo a una colonna della partizione. Ad esempio, per una tabella partizionata in base alla colonna partition_id con una colonna aggiuntiva f nello schema, entrambe le seguenti clausole WHERE soddisfano il requisito:

WHERE partition_id = "20221231"
WHERE partition_id = "20221231" AND f = "20221130"

Tuttavia, WHERE (partition_id = "20221231" OR f = "20221130") non è sufficiente.

Per le tabelle partizionate per data di importazione, utilizza la pseudocolonna _PARTITIONTIME o _PARTITIONDATE.

Per ulteriori informazioni sull'aggiunta dell'opzione Richiedi filtro partizione quando crei una tabella partizionata, consulta Creare tabelle partizionate. Puoi anche aggiornare questa impostazione in una tabella esistente.

Passaggi successivi