Esegui query su tabelle partizionate

Quando esegui una query su una tabella partizionata in BigQuery, il filtro sulla colonna di partizionamento consente a BigQuery di eliminare le partizioni, riducendo i byte analizzati e i costi delle query. Questo documento descrive come eseguire query su tabelle partizionate per colonna in base all'unità di tempo, per data di importazione e per intervallo di numeri interi e fornisce le best practice per la riduzione delle partizioni.

Per informazioni generali sull'esecuzione di query, consulta Esegui una query.

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 quelle rimanenti. Questo processo è detto 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 eliminazione variano a seconda dei diversi tipi di partizionamento, pertanto potresti notare una differenza nei byte elaborati quando esegui query su tabelle partizionate in modo diverso, ma per il resto identiche. Per stimare il numero di byte che verranno elaborati da una query, esegui un dry run.

Esegui query su una tabella partizionata per colonne in base a unità di tempo

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

Nell'esempio seguente, supponiamo che dataset.table sia partizionata 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'

Esegui query su una tabella partizionata per data di importazione

Le tabelle partizionate per data di acquisizione contengono una pseudo-colonna denominata _PARTITIONTIME, che è la colonna di partizionamento. 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 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 giornalmente: TIMESTAMP("2021-04-15")
  • Tabella partizionata mensilmente: TIMESTAMP("2021-04-01")
  • Tabella partizionata per anno: TIMESTAMP("2021-01-01")

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

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

Per eliminare le partizioni, filtra in base a una di queste colonne. Ad esempio, la seguente query analizza solo le 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 giornalmente, 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'istruzione SELECT *. Devi selezionarli esplicitamente:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Gestisci i fusi orari nelle tabelle partizionate per data di acquisizione

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

  • Adatta le query SQL alle differenze di fuso orario.
  • Utilizza i decoratori di partizione per caricare i dati in partizioni di importazione specifiche, in base a un fuso orario diverso da UTC.

Prestazioni migliori 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 solo 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 scansionate in una query, utilizza un'espressione costante nel filtro. La seguente query limita le partizioni eliminate in base alla prima condizione di filtro nella clausola WHERE. Tuttavia, la seconda condizione di filtro non limita le partizioni analizzate, perché utilizza i valori della 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 scansionate, non includere altre colonne in un filtro _PARTITIONTIME. Ad esempio, la seguente query non limita le partizioni analizzate, 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 intervallo di tempo specifico, valuta la possibilità di creare una vista che filtri in base alla pseudocolonna _PARTITIONTIME. Ad esempio, la seguente istruzione crea una vista che include solo i dati degli ultimi sette giorni 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 Creazione di viste.

Esegui query su una tabella partizionata per intervallo di numeri interi

Per eliminare le partizioni quando esegui una query su una tabella partizionata per intervalli di numeri interi, includi un filtro nella colonna di partizionamento dei numeri interi.

Nell'esempio seguente, supponiamo che dataset.table sia una tabella partizionata per intervallo di numeri interi con una specifica di partizionamento di customer_id:0:100:10. La query di esempio analizza le 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 |
+-------------+-------+

La riduzione delle partizioni non è supportata per le funzioni su una colonna partizionata per intervallo di numeri interi. Ad esempio, la seguente query analizza l'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__ contiene temporaneamente i dati che vengono trasmessi in streaming a una tabella partizionata mentre si trovano nello spazio di archiviazione ottimizzato per la scrittura. I dati trasmessi in streaming direttamente a 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 saperne di più, consulta Flusso di dati in tabelle partizionate.

Best practice per la potatura delle partizioni

Questa sezione descrive le best practice per la scrittura di query che utilizzano la riduzione delle partizioni per ottimizzare le prestazioni delle query e ridurre i costi.

Utilizza un'espressione di filtro costante

Per limitare le partizioni scansionate in una query, filtra la colonna di partizionamento utilizzando un'espressione costante anziché un'espressione dinamica.

La seguente query esegue la potatura delle partizioni:

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

In confronto, la seguente query non esegue la potatura delle partizioni, perché il predicato, WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2), non è un'espressione costante. Questa query confronta la colonna di partizionamento con un valore dinamico, il che impedisce l'eliminazione delle partizioni.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

Inoltre, una query con i seguenti predicati non esegue la potatura delle partizioni perché richiede un calcolo basato su una seconda colonna della tabella non costante ts2 o duration:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Isola la colonna di partizionamento o utilizza le funzioni supportate

Per eliminare le partizioni, le condizioni di filtro devono essere strutturate in modo che BigQuery possa determinare quali partizioni analizzare senza leggere i dati della tabella. Per farlo, isola la colonna di partizionamento su un lato di un operatore di confronto o inserisci la colonna solo in una funzione integrata supportata. Puoi utilizzare il dry run per verificare se la riduzione delle partizioni è supportata per la tua query specifica.

Le seguenti funzioni integrate nella colonna di partizionamento supportano la potatura delle partizioni, se i relativi argomenti aggiuntivi sono costanti:

Altre funzioni e operazioni matematiche complesse richiedono una scansione completa della tabella.

Esempi

Le seguenti query mostrano esempi di predicati che supportano la potatura delle partizioni.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

La seguente query salta tutte le partizioni perché il predicato non corrisponde a nessuna riga.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

La seguente query seleziona il primo giorno di ogni mese nella tabella e supporta la potatura delle partizioni. 

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

Le query con i seguenti predicati non eseguono la potatura delle partizioni perché manipolano la colonna di partizionamento con funzioni non supportate:

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

Analogamente, una query con il seguente predicato non esegue la potatura delle partizioni perché manipola la colonna di partizionamento con un'operazione aritmetica: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Per attivare la riduzione delle partizioni, devi riscrivere l'espressione isolando la colonna di partizionamento ts dalle funzioni o dalle operazioni aritmetiche non supportate. Per gli intervalli di tempo, utilizza >= e < per acquisire l'intervallo esatto. Per l'aritmetica, sposta l'operazione sull'altro lato del confronto.

La seguente query consente la potatura delle partizioni isolando la colonna di partizionamento ts per un intervallo di tempo:

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

La seguente query consente la riduzione delle partizioni isolando la colonna di partizionamento dall'operazione aritmetica:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Filtra in base a più colonne

Un predicato nella colonna di partizionamento di una query non limita gli altri elementi su cui puoi applicare un filtro. Puoi includere predicati in altre colonne nella stessa clausola WHERE e la riduzione delle partizioni verrà comunque eseguita, a condizione che la condizione che valuta la colonna di partizionamento segua le best practice. Tieni presente che AND è importante nell'esempio seguente. Se AND viene modificato in OR, la potatura delle partizioni non funzionerà, poiché anche se una partizione non corrisponde al predicato nella colonna di partizionamento, non può comunque essere potata. I dati in queste partizioni con meter_id = 1234 sono comunque idonei per la query.

Tieni presente che i predicati non devono essere scritti in un ordine specifico. Nella query di esempio seguente, supponendo che il partizionamento sia eseguito sulla colonna ts, la riduzione delle partizioni avviene comunque indipendentemente dalla posizione del predicato.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Richiedi un filtro di partizionamento nelle query

Quando crei una tabella partizionata, puoi richiedere l'utilizzo di filtri di predicato attivando l'opzione Richiedi filtro di partizionamento. Quando questa opzione è 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.

Questo requisito si applica anche alle query su viste e viste materializzate che fanno riferimento alla tabella partizionata.

Affinché il filtro sia considerato idoneo per l'eliminazione delle partizioni, deve essere presente almeno un predicato che faccia riferimento solo a una colonna di partizionamento. Per una tabella partizionata in base alla colonna partition_id con una colonna aggiuntiva f nel suo schema, entrambe le seguenti clausole WHERE soddisfano il requisito:

WHERE partition_id = "20221231"

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

Tuttavia, quanto segue non è sufficiente e genererà un errore:

WHERE partition_id = "20221231" OR f = "20221130"

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

Per saperne di più sull'aggiunta dell'opzione Richiedi filtro di partizione quando crei una tabella partizionata, consulta Crea tabelle partizionate. Puoi anche aggiornare questa impostazione su una tabella esistente.

Passaggi successivi