Esegui query su tabelle partizionate
Questo documento descrive alcune considerazioni specifiche per l'esecuzione di query tabelle partizionate in BigQuery.
Per informazioni generali sull'esecuzione di query in BigQuery, consulta Esecuzione di 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 della partizione.
L'eliminazione delle partizioni è il meccanismo utilizzato da BigQuery per eliminare le partizioni non necessarie dalla scansione di input. Le partizioni eliminate non vengono inclusi nel calcolo dei byte analizzati dalla query. In generale, il partizionamento l'eliminazione consente di ridurre il costo delle query.
I comportamenti di eliminazione variano a seconda dei diversi tipi di partizionamento, quindi notare una differenza di byte elaborati quando si eseguono query su tabelle partizionate ma che per il resto sono identiche. Per stimare quanti byte l'elaborazione della query, esegui una prova.
Eseguire query su una tabella partizionata per colonne di unità di tempo
Per eliminare le partizioni quando esegui una query tabella partizionata per colonne di unità di tempo, includere un filtro nella colonna di partizionamento.
Nell'esempio seguente, supponiamo che dataset.table
sia partizionato
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'
Query su una tabella partizionata per data di importazione
Tabelle partizionate in fase di importazione
contengono una pseudocolonna denominata _PARTITIONTIME
, che rappresenta il partizionamento
colonna. Il valore della colonna corrisponde all'ora di importazione UTC per ogni riga,
troncato al limite di partizione (ad esempio orario o giornaliero), ad esempio TIMESTAMP
valore.
Ad esempio, se aggiungi dati il 15 aprile 2021 alle 08:15:00 UTC,
_PARTITIONTIME
per 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 pseudocolonna
denominato _PARTITIONDATE
. Il valore è uguale a _PARTITIONTIME
troncato a un
Valore DATE
.
Entrambi i nomi delle pseudocolonne sono riservati. Non puoi creare una colonna con in una qualsiasi delle tue tabelle.
Per eliminare le partizioni, filtra in base a una di queste colonne. Ad esempio, la seguente query scansiona 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
a
la pseudocolonna:
SELECT _PARTITIONTIME AS pt, column FROM dataset.table
Per le tabelle partizionate giornaliere, puoi selezionare la pseudocolonna _PARTITIONDATE
allo stesso modo:
SELECT _PARTITIONDATE AS pd, column FROM dataset.table
Le pseudocolonne _PARTITIONTIME
e _PARTITIONDATE
non vengono restituite da un
Informativa 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 il campo è
compilate. 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.
- Usare i decoratori della partizione per caricare i dati in partizioni specifiche in fase di importazione, in base a fuso orario rispetto a UTC.
Miglior rendimento con le pseudocolonne
Per migliorare le prestazioni delle query, utilizza la pseudocolonna _PARTITIONTIME
da sola
a sinistra di un confronto.
Ad esempio, le seguenti due query sono equivalenti. A seconda della tabella
dimensioni, la seconda query potrebbe avere un rendimento migliore, perché inserisce _PARTITIONTIME
da solo sul lato sinistro dell'operatore >
. Entrambe le query elaborano lo stesso
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 tuo filtro. I seguenti limiti di query sulle partizioni eliminate in base
la prima condizione di filtro nella clausola WHERE
. Tuttavia, il secondo 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 per un determinato intervallo di volte, valuta la possibilità di creare una vista che
filtri nella pseudocolonna _PARTITIONTIME
. Ad esempio,
crea una vista che include solo gli ultimi sette giorni di dati
da 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.
Query su una tabella partizionata con intervallo di numeri interi
Per eliminare le partizioni quando esegui una query tabella partizionata con intervalli interi, includi un filtro nella colonna di partizionamento dei numeri interi.
Nell'esempio seguente, supponiamo che dataset.table
sia un intervallo di numeri interi
tabella partizionata 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 | +-------------+-------+
L'eliminazione delle partizioni non è supportata per le funzioni su un intervallo di numeri interi una colonna partizionata. Ad esempio, la seguente query analizza l'intera tabella.
SELECT * FROM dataset.table WHERE customer_id + 1 BETWEEN 30 AND 50
Utilizza il codice SQL precedente per eseguire query sulle tabelle partizionate con intervalli interi
Non puoi utilizzare SQL precedente per eseguire query su un intero intervallo di numeri interi partizionato . La query restituisce invece un errore come il seguente:
Querying tables partitioned on a field is not supported in Legacy SQL
Tuttavia, il codice SQL precedente supporta l'utilizzo di decoratori di tabelle per risolvere problemi specifici in una tabella partizionata con intervalli di numeri interi. La chiave per gestire un intervallo la partizione è l'inizio dell'intervallo.
L'esempio seguente esegue query sulla partizione di intervallo che inizia con 30:
SELECT * FROM dataset.table$30
Query sui dati nello spazio di archiviazione ottimizzato per la scrittura
La partizione __UNPARTITIONED__
contiene temporaneamente i dati trasmessi in flusso a una
tabella partizionata, mentre si trova
archiviazione ottimizzata per la scrittura.
Dati trasmessi in flusso direttamente su una partizione specifica di una tabella partizionata
non utilizza la partizione __UNPARTITIONED__
. I dati vengono invece trasmessi in flusso
direttamente nella partizione.
I dati nello spazio di archiviazione ottimizzato per la scrittura hanno valori NULL
in _PARTITIONTIME
e
_PARTITIONDATE
colonne.
Per eseguire query sui dati nella partizione __UNPARTITIONED__
, utilizza _PARTITIONTIME
pseudocolonna con il valore NULL
. Ad esempio:
SELECT column FROM dataset.table WHERE _PARTITIONTIME IS NULL
Per ulteriori informazioni, vedi Flusso 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 tuo filtro. Se utilizzi espressioni dinamiche nel filtro di query, BigQuery deve analizzare tutte le partizioni.
Ad esempio, la seguente query elimina le partizioni perché il filtro contiene una 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 di timestamp
e
key
campi:
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 si esprime un filtro. Filtri che richiedono dati da più campi per il calcolo non eliminerà le partizioni. Ad esempio, una query con un confronto delle date utilizzando la colonna di partizionamento e un secondo campo oppure le 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 ts
di partizionamento e un secondo campo
ts2
:
WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2
Richiedono un filtro di partizione nelle query
Quando crei una tabella partizionata, puoi richiedere l'utilizzo di un predicato
abilitando l'opzione Richiedi filtro di partizionamento. Quando questa opzione viene
applicata, tenta di eseguire una query sulla tabella partizionata senza specificare un WHERE
genera 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 per
il filtro sia considerato idoneo per l'eliminazione della partizione. Ad esempio, per
una tabella partizionata nella colonna partition_id
con una colonna aggiuntiva f
in
allo 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 in fase di importazione, utilizza _PARTITIONTIME
o
_PARTITIONDATE
pseudocolonna.
Per saperne di più sull'aggiunta dell'opzione Richiedi filtro di partizionamento quando crei una tabella partizionata, Creazione di tabelle partizionate. Puoi anche aggiornare questa impostazione su 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.