Ottimizzazione di Google BigQuery con sottoquery nella clausola WHERE

Question

Sto tentando di impostare una query che seleziona un sottoinsieme di dati da un intervallo di partizioni giornaliere dei dati di sessione di Google Analytics e scrive i dati in una tabella di staging di Google BigQuery. La sfida per me è ridurre i costi di elaborazione quando si utilizza una sottoquery nella clausola WHERE.

I dati di Google Analytics dalla query devono essere aggiunti a una tabella di staging prima di essere elaborati e caricati nella tabella dei dati di destinazione (my-data-table). La query principale è fornita in due forme di seguito. Il primo è hardcoded. Il secondo riflette la forma preferita. Il limite superiore su _TABLE_SUFFIX è hardcoded per entrambi per semplificare la query. L'obiettivo è utilizzare MAX (data), dove la data ha la forma AAAAMMGG, da my-data-table come limite inferiore nelle partizioni ga_sessions_ * daily. La query è stata semplificata per la presentazione qui, ma si ritiene che contenga tutti gli elementi necessari.

La query aggregata (SELECT MAX (date) FROM my-project-12345.dataset.my-data-table) restituisce il valore "20201015" ed elabora 202 KB. A seconda che utilizzo esplicitamente il valore restituito (come '20201015') nella clausola WHERE della query principale o utilizzo la query SELECT MAX () nella clausola WHERE, vi è una differenza significativa nei dati elaborati tra le due query (2.3 GB per il valore esplicito rispetto a 138,1 GB per l'espressione SELECT MAX ()).

Esiste un'ottimizzazione, un piano o una direttiva che può essere applicata alla forma preferita della query principale che ridurrà i costi di elaborazione dei dati? Grazie per tutta l'assistenza che può essere fornita.

Query principale (versione hardcoded, elabora 2,3 GB)

SELECT
  GA.date, 
  GA.field1, 
  hits.field2, 
  hits.field3
FROM 
  `my-project-12345.dataset.ga_sessions_*` AS GA, UNNEST(GA.hits) AS hits
WHERE 
  hits.type IN ('PAGE', 'EVENT')
  AND hits.field0 = 'some value'
  AND _TABLE_SUFFIX > '20201015'
  AND _TABLE_SUFFIX < '20201025' 

Query principale (modulo preferito, elabora 138,1 GB senza ottimizzazione)

SELECT
  GA.date, 
  GA.field1, 
  hits.field2, 
  hits.field3
FROM 
  `my-project-12345.dataset.ga_sessions_*` AS GA, UNNEST(GA.hits) AS hits
WHERE 
  hits.type IN ('PAGE', 'EVENT')
  AND hits.field0 = 'some value'
  AND _TABLE_SUFFIX > (SELECT MAX(date) FROM `my-project-12345.dataset.my-data-table`)
  AND _TABLE_SUFFIX < '20201025' 

Answer 1

Puoi usare lo scripting per questo

Il "trucco" sta nel pre-calcolo

DECLARE start_date STRING;
SET start_date = (SELECT MAX(date) FROM `my-project-12345.dataset.my-data-table`);    

e assegnando alla variabile e quindi utilizzare questa variabile nella clausola where sulla query principale - in questo caso utilizzerà la versione conveniente

AND _TABLE_SUFFIX > start_date
AND _TABLE_SUFFIX < '20201025'