Esecuzione di query sulle tabelle partizionate

Questo documento descrive alcune considerazioni specifiche per l'esecuzione di query sulle tabelle partizionate in BigQuery.

Per informazioni generali sull'esecuzione di query in BigQuery, consulta Esecuzione di query interattive e 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 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 una query elaborerà, esegui un'esecuzione di prova.

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

Per eliminare le partizioni quando esegui 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 nella colonna transaction_date. La query di esempio elimina le date precedenti al 2016-01-01.

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

Esecuzione di 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 partizionamento. Il valore della colonna è la data e ora di importazione in formato UTC per ogni riga, troncata al limite 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 per giorno: TIMESTAMP("2021-04-15")
  • Tabella partizionata per mese: 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 questi nomi di 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 il 1° gennaio 2016 e il 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 per giorno, 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 selezionarle in modo esplicito:

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 in formato 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:

  • Regola le differenze di fuso orario nelle query SQL.
  • Utilizza i decoratori di partizioni per caricare i dati in partizioni specifiche per data di importazione, 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 due query seguenti sono equivalenti. A seconda delle dimensioni della tabella, la seconda query potrebbe avere prestazioni migliori, perché inserisce _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 analizzate 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 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 analizzate, 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 la pseudocolonna _PARTITIONTIME. Ad esempio, la seguente istruzione crea una vista che include solo gli ultimi sette giorni di dati 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.

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

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

Nell'esempio seguente, supponiamo che dataset.table sia una tabella partizionata con intervallo di numeri interi con una specifica di partizionamento 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 |
+-------------+-------+

L'eliminazione delle partizioni non è supportata per le funzioni su una colonna partizionata con 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

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

La partizione __UNPARTITIONED__ contiene temporaneamente i dati di cui viene eseguito lo streaming in una tabella partizionata mentre si trova nello spazio di archiviazione ottimizzato per la scrittura. I dati di cui viene eseguito lo streaming direttamente in una partizione specifica di una tabella partizionata non utilizzano la partizione __UNPARTITIONED__. Al contrario, i dati vengono 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 l'eliminazione delle partizioni

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

Utilizzare un'espressione di filtro costante

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

La seguente query elimina le partizioni:

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

Al contrario, la seguente query non elimina le 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, 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 elimina le 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

Isolare la colonna di partizionamento o utilizzare le funzioni supportate

Per eliminare le partizioni, le condizioni di filtro devono essere strutturate in modo che BigQuery possa determinare le partizioni da analizzare senza leggere i dati della tabella. Per farlo, isola la colonna di partizionamento su un lato di un operatore di confronto o racchiudi la colonna solo in una funzione integrata supportata. Puoi utilizzare l'esecuzione di prova per verificare se l'eliminazione delle partizioni è supportata nella tua query specifica.

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

Altre funzioni e operazioni matematiche complesse richiedono un'analisi completa della tabella.

Esempi

Le seguenti query mostrano esempi di predicati che supportano l'eliminazione 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 l'eliminazione 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 eliminano le 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

Allo stesso modo, una query con il seguente predicato non elimina le partizioni perché manipola la colonna di partizionamento con un'operazione aritmetica: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Per abilitare l'eliminazione 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 l'eliminazione 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 l'eliminazione delle partizioni isolando la colonna di partizionamento dall'operazione aritmetica:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Filtrare in base a più colonne

Un predicato nella colonna di partizionamento in una query non limita gli altri filtri che puoi applicare. Puoi includere predicati su altre colonne nella stessa clausola WHERE e l'eliminazione delle partizioni continuerà a verificarsi 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 sostituito da OR, l'eliminazione delle partizioni non funzionerà, perché anche se una partizione non corrisponde al predicato nella colonna di partizionamento, non può comunque essere eliminata. 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 seguente query di esempio, supponendo il partizionamento nella colonna ts, l'eliminazione delle partizioni si verifica indipendentemente dal posizionamento del predicato.

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

Richiedere 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 partizione. 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.

Perché 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 nella 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, quanto segue non è sufficiente e genererà un errore:

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

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

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

Passaggi successivi