Esegui query su tabelle partizionate
Questo documento descrive alcune considerazioni specifiche per l'esecuzione di query su 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 ignorare le partizioni rimanenti. Questo processo è chiamato eliminazione delle partizioni.
L'eliminazione delle partizioni è il meccanismo utilizzato da BigQuery per eliminare le partizioni non necessarie dalla scansione di input. Le partizioni eliminate non sono incluse nel calcolo dei byte analizzati dalla query. In generale, l'eliminazione delle partizioni aiuta a ridurre i costi delle query.
I comportamenti di eliminazione variano a seconda dei tipi di partizionamento, quindi potresti notare una differenza nei byte elaborati quando esegui query su tabelle partizionate in modo diverso, ma che per il resto sono identiche. Per stimare il numero di byte elaborati da una query, esegui una prova.
Esegui una query su una tabella partizionata per le colonne delle unità di tempo
Per eliminare le partizioni quando esegui una query su una tabella partizionata per le colonne di unità di tempo, includi un filtro nella colonna di partizionamento.
Nell'esempio seguente, supponiamo che dataset.table
sia partizionato nella 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 una query su una tabella partizionata per data di importazione
Le tabelle partizionate per data di importazione contengono una pseudo-colonna denominata _PARTITIONTIME
, ovvero la colonna di partizionamento. Il valore della colonna corrisponde all'ora di importazione UTC di ogni riga, troncata in base al limite della partizione (ad esempio oraria o giornaliera), sotto forma di valore TIMESTAMP
.
Ad esempio, se aggiungi dati il 15 aprile 2021 alle 08:15:00 UTC, la colonna _PARTITIONTIME
di queste righe contiene i seguenti valori:
- Tabella partizionata oraria:
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 pseudo-colonna denominata _PARTITIONDATE
. Il valore è uguale a _PARTITIONTIME
troncato a un
valore DATE
.
Entrambi i nomi di pseudo-colonna sono riservati. Non puoi creare una colonna con questi nomi in nessuna delle tabelle.
Per eliminare le partizioni, applica un filtro in base a una di queste colonne. Ad esempio, la seguente query analizza solo le partizioni comprese 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 pseudo-colonna _PARTITIONTIME
, devi utilizzare un alias. Ad esempio, la seguente query seleziona _PARTITIONTIME
assegnando l'alias pt
alla pseudo colonna:
SELECT _PARTITIONTIME AS pt, column FROM dataset.table
Per le tabelle partizionate giornaliere, puoi selezionare la pseudo-colonna _PARTITIONDATE
nello stesso modo:
SELECT _PARTITIONDATE AS pd, column FROM dataset.table
Le pseudo-colonne _PARTITIONTIME
e _PARTITIONDATE
non vengono restituite da un'istruzione SELECT *
. Devi selezionarli 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:
- Regola per tenere conto delle differenze di fuso orario nelle query SQL.
- Utilizza i decoratori delle partizioni per caricare i dati in partizioni specifiche per la fase di importazione, in base a un fuso orario diverso rispetto a UTC.
Rendimento migliore con le pseudo-colonne
Per migliorare le prestazioni delle query, utilizza la pseudo-colonna _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 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 analizzate in una query, utilizza un'espressione costante nel filtro. La query seguente limita le partizioni eliminate in base alla prima condizione di filtro nella clausola WHERE
. Tuttavia, la seconda condizione del filtro non limita le partizioni analizzate, perché utilizza valori di tabella 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 nella tabella.
-- Scans all partitions of table2. No pruning. SELECT field1 FROM dataset.table2 WHERE _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');
Se esegui spesso query in un determinato intervallo di tempo, valuta la possibilità di creare una vista che filtri la pseudo-colonna _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 delle viste, consulta la sezione Creazione delle viste.
Esegui una query su una tabella partizionata con intervallo di numeri interi
Per eliminare le partizioni quando esegui query su una tabella partizionata con intervallo intero, includi un filtro nella colonna di partizionamento dei 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 in una colonna partizionata con intervallo di numeri interi. Ad esempio, la query seguente scansiona l'intera tabella.
SELECT * FROM dataset.table WHERE customer_id + 1 BETWEEN 30 AND 50
Utilizza SQL precedente per eseguire query su tabelle partizionate con intervallo di numeri interi
Non puoi utilizzare SQL precedente per eseguire query su un'intera tabella partizionata con intervallo di numeri interi. La query restituisce invece un errore come il seguente:
Querying tables partitioned on a field is not supported in Legacy SQL
Tuttavia, l'SQL precedente supporta l'uso di decorator delle tabelle per gestire una partizione specifica in una tabella partizionata con intervallo di numeri interi. La chiave per indirizzare una partizione dell'intervallo è l'inizio dell'intervallo.
L'esempio seguente esegue una query sulla partizione dell'intervallo che inizia con 30:
SELECT * FROM dataset.table$30
Esegui query sui dati nello spazio di archiviazione ottimizzato per la scrittura
La partizione __UNPARTITIONED__
archivia temporaneamente i dati trasmessi in flussi a una tabella partizionata mentre si trova nello spazio di archiviazione ottimizzato per la scrittura.
I dati trasmessi direttamente su una partizione specifica di una tabella partizionata non utilizzano la partizione __UNPARTITIONED__
. ma vengono trasmessi 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 pseudo-colonna _PARTITIONTIME
con il valore NULL
. Ad esempio:
SELECT column FROM dataset.table WHERE _PARTITIONTIME IS NULL
Per maggiori informazioni, consulta Creazione di flussi di dati in tabelle partizionate.
Best practice per l'eliminazione delle partizioni
Utilizza un'espressione di filtro costante
Per limitare le partizioni analizzate in una query, utilizza un'espressione costante nel filtro. Se utilizzi espressioni dinamiche nel filtro delle 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; 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)
Isolare la colonna di partizione nel filtro
Isolare la colonna di partizione quando esprimi un filtro. I filtri che richiedono dati di più campi per il calcolo non eliminano le partizioni. Ad esempio, una query con un confronto delle date che utilizza la colonna di partizionamento e un secondo campo oppure le query contenenti alcune concatenazioni di campi non eliminano le partizioni.
Ad esempio, il seguente filtro non elimina le partizioni perché richiede un calcolo basato sul campo ts
di partizionamento e su un secondo campo ts2
:
WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2
Richiedere un filtro di partizionamento nelle query
Quando crei una tabella partizionata, puoi richiedere l'utilizzo di filtri
predicati abilitando l'opzione Richiedi filtro di partizionamento. Quando viene applicata questa opzione, i tentativi di eseguire una 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
.
Deve esistere almeno un predicato che fa riferimento solo a una colonna di partizione affinché il filtro venga considerato idoneo per l'eliminazione della partizione. Ad esempio, per una tabella partizionata nella colonna partition_id
con una colonna aggiuntiva f
nel proprio 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 pseudo-colonna _PARTITIONTIME
o _PARTITIONDATE
.
Per saperne di più sull'aggiunta dell'opzione Richiedi filtro di partizionamento quando crei una tabella partizionata, consulta Creazione di tabelle partizionate. Puoi anche aggiornare questa impostazione in una tabella esistente.
Passaggi successivi
- Per una panoramica delle tabelle partizionate, consulta Introduzione alle tabelle partizionate.
- Per scoprire di più sulla creazione di tabelle partizionate, consulta Creazione di tabelle partizionate.