Utilizzare CTE ricorsive

In GoogleSQL per BigQuery, una clausola WITH contiene una o più espressioni di tabella comuni (CTE) a cui puoi fare riferimento in un'espressione di query. Le CTE possono essere non ricorsive, ricorsive, o entrambe. La RECURSIVE parola chiave abilita la ricorsione nella clausola WITH (WITH RECURSIVE).

Una CTE ricorsiva può fare riferimento a se stessa, a una 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 continuamente finché non vengono trovati nuovi risultati, mentre le CTE non ricorsive vengono eseguite una sola volta. Per questi motivi, le CTE ricorsive sono comunemente utilizzate per eseguire query su dati gerarchici e dati di grafi.

Considera ad esempio un grafo 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 nodo iniziale specifico senza conoscere il numero massimo di hop, avresti bisogno di una CTE ricorsiva nella query (WITH RECURSIVE). La query ricorsiva inizia con il caso base del nodo iniziale e ogni passaggio calcola i nuovi nodi non visualizzati che possono essere raggiunti da tutti i nodi visualizzati finora fino al passaggio precedente. La query termina quando non è possibile trovare nuovi nodi.

Tuttavia, le CTE ricorsive possono essere costose dal punto di vista computazionale, quindi prima di utilizzare le, consulta questa guida e la sezione della clausola WITH della documentazione di riferimento di GoogleSQL.

Creare una CTE ricorsiva

Per creare una CTE ricorsiva in GoogleSQL, utilizza la WITH RECURSIVE clausola 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 base esegue la prima iterazione dell'operazione di unione ricorsiva. Il termine ricorsivo esegue le iterazioni rimanenti e deve includere un riferimento automatico alla CTE ricorsiva. Solo il termine ricorsivo può includere un riferimento automatico.

Nell'esempio precedente, la CTE ricorsiva contiene i seguenti componenti:

• Nome CTE ricorsiva: CTE_1
• Termine 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, sulle regole e sugli esempi delle CTE ricorsive, consulta la WITH clausola nella documentazione di riferimento di GoogleSQL.

Esplorare la raggiungibilità in un grafo diretto aciclico (DAG)

Puoi utilizzare una query ricorsiva per esplorare la raggiungibilità in un grafo diretto aciclico (DAG). La query seguente trova tutti i nodi raggiungibili dal nodo 5 in un grafo denominato 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 i problemi relativi agli errori di limite 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 le ricorsioni infinite, viene applicato un limite di iterazioni per ogni CTE ricorsiva. Per BigQuery, il limite di iterazione è di 500 iterazioni. Una volta che una CTE ricorsiva raggiunge il numero massimo di iterazioni, l'esecuzione della 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 essere completata.

Le query che raggiungono il limite di iterazione di solito non hanno una condizione di terminazione appropriata, creando così un loop infinito o utilizzando CTE ricorsive in scenari inappropriati.

Se riscontri un errore di limite di iterazione di ricorsione, consulta i suggerimenti in questa sezione.

Verificare la presenza di ricorsione infinita

Per evitare 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 presenza di ricorsione infinita è convertire la CTE ricorsiva in una 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 base della CTE ricorsiva.
• recursive_expression: il termine ricorsivo della CTE ricorsiva.
• termination_condition_expression: l'espressione di terminazione della CTE ricorsiva.

Considera ad esempio la seguente CTE ricorsiva denominata TestCTE:

WITH RECURSIVE
  TestCTE AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 3 FROM TestCTE WHERE MOD(n, 6) != 0
  )

Questo esempio utilizza 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 testerà quindi la TestCTE per 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, il che è vero in questo esempio, è probabile che l'esempio abbia una ricorsione infinita.

Verificare l'utilizzo appropriato della CTE ricorsiva

Verifica di utilizzare la CTE ricorsiva in uno scenario appropriato. Le CTE ricorsive possono essere costose da calcolare perché sono progettate per eseguire query su dati gerarchici e dati di grafi. Se non esegui query su questi due tipi di dati, valuta alternative, ad esempio l'utilizzo dell' LOOPistruzione con una CTE non ricorsiva.

Dividere una CTE ricorsiva in più CTE ricorsive

Se ritieni che la tua CTE ricorsiva abbia bisogno di più iterazioni del massimo consentito, potresti essere in grado di suddividerla in più CTE ricorsive.

Puoi dividere 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 base per la CTE corrente.
• recursive_expression: l'espressione del termine ricorsivo per la CTE corrente.

Ad esempio, il seguente codice suddivide una 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 esegue solo 10 iterazioni. L'output è simile al seguente:

/*-----------+
 | iteration |
 +-----------+
 | 2         |
 | ...       |
 | 30        |
 +-----------*/

Puoi testare la query precedente su iterazioni molto più grandi.

Utilizzare un loop anziché una CTE ricorsiva

Per evitare i limiti di iterazione, valuta la possibilità di utilizzare un loop anziché una CTE ricorsiva. Puoi creare un loop con una delle diverse istruzioni di loop, ad esempio LOOP, REPEAT o WHILE. Per ulteriori informazioni, consulta 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 deve eseguire più di 500 iterazioni.
• Non ti preoccupa un'esecuzione molto più lunga.

Tieni presente che l'aumento del limite ricorsivo comporta potenziali rischi:

• La CTE potrebbe non riuscire con un messaggio di errore diverso, ad esempio memoria insufficiente o timeout.
• 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 le 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 una CTE ricorsiva.

Per ulteriori informazioni, consulta Calcolo delle dimensioni delle query.

Quote

Per informazioni sulle quote e sui limiti delle CTE ricorsive, consulta Quote e limiti.