In GoogleSQL per BigQuery, una clausola WITH
contiene una o più espressioni di tabella comuni (CTE) con tabelle temporanee a cui puoi fare riferimento in un'espressione di query. Le CTE possono essere non ricorsive, ricorrenti o entrambe. La parola chiave RECURSIVE
consente la ricorsione nella clausola WITH
(WITH RECURSIVE
).
Un CTE ricorsivo può fare riferimento a se stesso, a un CTE precedente o a una CTE successiva. Una CTE non ricorsiva può fare riferimento solo alle CTE precedenti e non può fare riferimento a se stessa. Le CTE ricorsive vengono eseguite in modo continuo fino a quando non vengono trovati nuovi risultati, mentre le CTE non ricorsive vengono eseguite una sola volta. Per questi motivi, le CTE ricorsive vengono comunemente utilizzate per eseguire query su dati gerarchici e dati di grafici.
Ad esempio, considera un grafico in cui ogni riga rappresenta un nodo che può essere collegato ad altri nodi. Per trovare la chiusura transitiva di tutti i nodi raggiungibili da un determinato nodo di avvio senza conoscere il numero massimo di hop, devi avere un CTE ricorsivo nella query (WITH RECURSIVE
). La query ricorsiva inizia con il caso base del nodo iniziale e ogni passaggio calcolerà i nuovi nodi non visti che possono essere raggiunti da tutti i nodi visti finora fino al passaggio precedente. La query si conclude quando non è possibile trovare nuovi nodi.
Tuttavia, le CTE ricorsive possono essere costose dal punto di vista del calcolo, quindi prima di utilizzarle
consulta questa guida e la sezione clausola WITH
della
documentazione di riferimento di GoogleSQL.
Crea un CTE ricorsivo
Per creare un CTE ricorsivo in GoogleSQL, utilizza la clausola WITH RECURSIVE
come mostrato nell'esempio seguente:
WITH RECURSIVE
CTE_1 AS (
(SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
UNION ALL
SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
)
SELECT iteration FROM CTE_1
ORDER BY 1 ASC
L'esempio precedente produce i seguenti risultati:
/*-----------*
| iteration |
+-----------+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
*-----------*/
Una CTE ricorsiva include un termine base, un operatore di unione e un termine ricorsivo. Il termine di base esegue la prima iterazione dell'operazione di unione ricorsiva. Il termine ricorsivo esegue le iterazioni rimanenti e deve includere un autoriferimento alla CTE ricorsiva. Solo il termine ricorsivo può includere un autoriferimento.
Nell'esempio precedente, la CTE ricorsiva contiene i seguenti componenti:
- Nome CTE ricorsivo:
CTE_1
- Termine di base:
SELECT 1 AS iteration
- Operatore di unione:
UNION ALL
- Termine ricorsivo:
SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
Per scoprire di più sulla sintassi, le regole e gli esempi CTE ricorsivi, consulta la frase WITH
nella documentazione di riferimento di GoogleSQL.
Esplora la raggiungibilità in un grafo diretto aciclico (DAG)
Puoi utilizzare una query ricorsiva per esplorare la raggiungibilità in un grafo diretto aciclico (DAG). La seguente query trova tutti i nodi che possono essere raggiunti dal nodo 5
in un grafico chiamato GraphData
:
WITH RECURSIVE
GraphData AS (
-- 1 5
-- / \ / \
-- 2 - 3 6 7
-- | \ /
-- 4 8
SELECT 1 AS from_node, 2 AS to_node UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 5, 7 UNION ALL
SELECT 6, 8 UNION ALL
SELECT 7, 8
),
R AS (
(SELECT 5 AS node)
UNION ALL
(
SELECT GraphData.to_node AS node
FROM R
INNER JOIN GraphData
ON (R.node = GraphData.from_node)
)
)
SELECT DISTINCT node FROM R ORDER BY node;
L'esempio precedente produce i seguenti risultati:
/*------*
| node |
+------+
| 5 |
| 6 |
| 7 |
| 8 |
*------*/
Risolvere gli errori relativi ai limiti di iterazione
Le CTE ricorsive possono comportare una ricorsione infinita, che si verifica quando il termine ricorsivo viene eseguito continuamente senza soddisfare una condizione di terminazione. Per terminare ricorrenti infinite, viene applicato un limite di iterazioni per ogni CTE ricorsiva. Per BigQuery, il limite di iterazione è di 500 iterazioni. Quando una CTE ricorsiva raggiunge il numero massimo di iterazioni, l'esecuzione di CTE viene interrotta con un errore.
Questo limite esiste perché il calcolo di una CTE ricorsiva può essere costoso e l'esecuzione di una CTE con un numero elevato di iterazioni consuma molte risorse di sistema e richiede molto più tempo per il completamento.
Nelle query che raggiungono il limite di iterazione solitamente manca una condizione di terminazione adeguata, quindi si crea un loop infinito o vengono utilizzate CTE ricorsive in scenari inappropriati.
Se si verifica un errore relativo al limite di iterazione della ricorsione, rivedi i suggerimenti in questa sezione.
Verifica la ricorsione infinita
Per impedire la ricorsione infinita, assicurati che il termine ricorsivo sia in grado di produrre un risultato vuoto dopo l'esecuzione di un determinato numero di iterazioni.
Un modo per verificare la ricorsione infinita è convertire il CTE ricorsivo in un
TEMP TABLE
con un loop REPEAT
per le prime 100
iterazioni, come segue:
DECLARE current_iteration INT64 DEFAULT 0; CREATE TEMP TABLE recursive_cte_name AS SELECT base_expression, current_iteration AS iteration; REPEAT SET current_iteration = current_iteration + 1; INSERT INTO recursive_cte_name SELECT recursive_expression, current_iteration FROM recursive_cte_name WHERE termination_condition_expression AND iteration = current_iteration - 1 AND current_iteration < 100; UNTIL NOT EXISTS(SELECT * FROM recursive_cte_name WHERE iteration = current_iteration) END REPEAT;
Sostituisci i seguenti valori:
recursive_cte_name
: la CTE ricorsiva di cui eseguire il debug.base_expression
: il termine di base della CTE ricorsiva.recursive_expression
: il termine ricorsivo della CTE ricorsiva.termination_condition_expression
: l'espressione di terminazione della CTE ricorsiva.
Ad esempio, considera la seguente CTE ricorsiva chiamata TestCTE
:
WITH RECURSIVE
TestCTE AS (
SELECT 1 AS n
UNION ALL
SELECT n + 3 FROM TestCTE WHERE MOD(n, 6) != 0
)
In questo esempio vengono utilizzati i seguenti valori:
recursive_cte_name
:TestCTE
base_expression
:SELECT 1
recursive_expression
:n + 3
termination_condition_expression
:MOD(n, 6) != 0
Il seguente codice testerebbe quindi TestCTE
per verificare la ricorsione infinita:
DECLARE current_iteration INT64 DEFAULT 0; CREATE TEMP TABLE TestCTE AS SELECT 1 AS n, current_iteration AS iteration; REPEAT SET current_iteration = current_iteration + 1; INSERT INTO TestCTE SELECT n + 3, current_iteration FROM TestCTE WHERE MOD(n, 6) != 0 AND iteration = current_iteration - 1 AND current_iteration < 10; UNTIL NOT EXISTS(SELECT * FROM TestCTE WHERE iteration = current_iteration) END REPEAT; -- Print the number of rows produced by each iteration SELECT iteration, COUNT(1) AS num_rows FROM TestCTE GROUP BY iteration ORDER BY iteration; -- Examine the actual result produced for a specific iteration SELECT * FROM TestCTE WHERE iteration = 2;
L'esempio precedente produce i seguenti risultati che includono l'ID iterazione e il numero di righe prodotte durante l'iterazione:
/*-----------+----------*
| iteration | num_rows |
+-----------+----------+
| 0 | 1 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
*-----------+----------*/
Questi sono i risultati effettivi prodotti durante l'iterazione 2
:
/*----------+-----------*
| n | iteration |
+----------+-----------+
| 7 | 2 |
*----------+-----------*/
Se il numero di righe è sempre maggiore di zero, che è vero in questo esempio, è probabile che il campione abbia una ricorsione infinita.
Verifica l'utilizzo appropriato della CTE ricorsiva
Verifica di utilizzare il CTE ricorsivo in uno scenario appropriato.
Le CTE ricorsive possono essere costose da calcolare perché sono progettate per eseguire query su dati gerarchici e dati di grafici. Se non esegui query su questi due tipi di dati, prendi in considerazione delle alternative, ad esempio utilizzare l'istruzione LOOP
con una CTE non ricorsiva.
Dividi un CTE ricorsivo in più CTE ricorsive
Se ritieni che la tua CTE ricorsiva abbia bisogno di più delle iterazioni massime consentite, potresti riuscire a suddividerla in più CTE ricorsive.
Puoi suddividere una CTE ricorsiva con una struttura di query simile alla seguente:
WITH RECURSIVE CTE_1 AS ( SELECT base_expression UNION ALL SELECT recursive_expression FROM CTE_1 WHERE iteration < 500 ), CTE_2 AS ( SELECT * FROM CTE_1 WHERE iteration = 500 UNION ALL SELECT recursive_expression FROM CTE_2 WHERE iteration < 500 * 2 ), CTE_3 AS ( SELECT * FROM CTE_2 WHERE iteration = 500 * 2 UNION ALL SELECT recursive_expression FROM CTE_3 WHERE iteration < 500 * 3 ), [, ...] SELECT * FROM CTE_1 UNION ALL SELECT * FROM CTE_2 WHERE iteration > 500 UNION ALL SELECT * FROM CTE_3 WHERE iteration > 500 * 2 [...]
Sostituisci i seguenti valori:
base_expression
: l'espressione del termine di base per l'attuale CTE.recursive_expression
: l'espressione del termine ricorsivo per l'attuale CTE.
Ad esempio, il seguente codice suddivide un CTE in tre CTE distinte:
WITH RECURSIVE
CTE_1 AS (
SELECT 1 AS iteration
UNION ALL
SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 10
),
CTE_2 AS (
SELECT * FROM CTE_1 WHERE iteration = 10
UNION ALL
SELECT iteration + 1 AS iteration FROM CTE_2 WHERE iteration < 10 * 2
),
CTE_3 AS (
SELECT * FROM CTE_2 WHERE iteration = 10 * 2
UNION ALL
SELECT iteration + 1 AS iteration FROM CTE_3 WHERE iteration < 10 * 3
)
SELECT iteration FROM CTE_1
UNION ALL
SELECT iteration FROM CTE_2 WHERE iteration > 10
UNION ALL
SELECT iteration FROM CTE_3 WHERE iteration > 20
ORDER BY 1 ASC
Nell'esempio precedente, 500 iterazioni vengono sostituite con 10 iterazioni in modo che sia più veloce visualizzare i risultati della query. La query produce 30 righe, ma ogni CTE ricorsiva viene ripetuta solo 10 volte. L'output ha il seguente aspetto:
/*-----------*
| iteration |
+-----------+
| 2 |
| ... |
| 30 |
*-----------*/
Potresti testare la query precedente su iterazioni molto più ampie.
Usa un loop invece di una CTE ricorsiva
Per evitare limiti di iterazione, valuta l'utilizzo di un loop anziché di una CTE ricorsiva.
Puoi creare un loop con una delle numerose istruzioni di loop,
come LOOP
, REPEAT
o WHILE
. Per ulteriori informazioni, consulta la sezione
Loop.
Modificare il limite ricorsivo
Se ritieni che si applichino i seguenti fattori, contatta l'assistenza clienti per aumentare il limite ricorsivo:
- Hai un motivo valido per cui la tua CTE ricorsiva esegue più di 500 iterazioni.
- Sei d'accordo con un'esecuzione molto più lunga.
Tieni presente che aumentare il limite ricorsivo presenta potenziali rischi:
- La CTE potrebbe non riuscire con un messaggio di errore diverso, ad esempio superamento o timeout di memoria.
- Se il tuo progetto utilizza il modello di prezzi on demand, la CTE potrebbe comunque non riuscire con un errore del livello di fatturazione finché non passi al modello di prezzi basato sulla capacità.
- Una CTE ricorsiva con un numero elevato di iterazioni consuma molte risorse. Ciò potrebbe influire su altre query in esecuzione nella stessa prenotazione, poiché competono per risorse condivise.
Prezzi
Se utilizzi la fatturazione on demand, BigQuery addebita i costi in base al numero di byte elaborati durante l'esecuzione di una query con un CTE ricorsivo.
Per ulteriori informazioni, consulta Calcolo delle dimensioni delle query.
Quote
Per informazioni sulle quote e sui limiti CTE ricorsivi, consulta Quote e limiti.