Lavorare con le CTE ricorrenti

In GoogleSQL per BigQuery, una clausola WITH contiene una o più tabelle comuni espressioni (CTE) con tabelle temporanee a cui puoi fare riferimento in una query un'espressione di base. 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 un CTE successivo. R La 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 ricorrenti vengono eseguite una volta. Per questi motivi, le CTE ricorsive vengono per eseguire query su dati gerarchici e su grafici.

Ad esempio, prendi in considerazione un grafo in cui ogni riga rappresenta un nodo che può collegarsi ad altri nodi. Per trovare la chiusura transitiva di tutti i nodi raggiungibili da una di un particolare nodo di inizio senza conoscere il numero massimo di hop, una CTE ricorsiva nella query (WITH RECURSIVE). La query ricorsiva inizierà con il caso base del nodo iniziale e ogni passaggio calcolerebbe il nuovo nodi che possono essere raggiunti da tutti i nodi visualizzati fino alla fase passaggio. La query termina quando non è possibile trovare nuovi nodi.

Tuttavia, le CTE ricorsive possono essere computazionalmente costose, quindi prima di utilizzarle, consulta questa guida e la sezione WITH clause della documentazione di riferimento di GoogleSQL.

Creare una CTE ricorsiva

Per creare un'espressione CTE ricorsiva 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 di base, un operatore di unione e un termine ricorsivo. Il termine di base esegue la prima iterazione dell'operazione di unione ricorsiva. La il termine ricorsivo esegue le iterazioni rimanenti e deve includere un autoriferimento alla ricorsione CTE. 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 Union: UNION ALL
• Termine ricorrente: 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 connettività in un grafo diretto aciclico (DAG). La seguente query trova tutti i nodi che possono essere raggiunto 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 viene eseguita continuativamente senza soddisfare una condizione di risoluzione. Per terminare le ricorsioni infinite, viene applicato un limite di iterazioni per ogni CTE ricorsivo. Per BigQuery, il limite di iterazioni è 500. Quando una CTE ricorrente 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 richiede e richiede molto più tempo.

Le query che raggiungono il limite di iterazione di solito non dispongono di una condizione di terminazione corretta, creando così un ciclo infinito o utilizzando CTE ricorsivi in scenari inappropriati.

Se riscontri un errore relativo al limite di iterazione della ricorsione, esamina i suggerimenti in questa sezione.

Verificare la ricorsione infinita

Per evitare una 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 la CTE ricorsiva in un TEMP TABLE con un ciclo 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 da eseguire il debug.
• base_expression: il termine di base della CTE ricorsiva.
• recursive_expression: il termine ricorsivo dell'espressione CTE ricorsiva.
• termination_condition_expression: l'espressione di risoluzione dei CTE ricorsivo.

Ad esempio, considera 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 testa quindi 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 la funzione 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, come in questo esempio, è probabile che il campione abbia una ricorsione infinita.

Verificare l'utilizzo appropriato della CTE ricorsiva

Verifica di utilizzare l'espressione CTE ricorsiva in uno scenario appropriato. Le CTE ricorsive possono essere costose da calcolare perché sono progettate per eseguire query dati gerarchici e dati dei grafici. Se non esegui query su questi due tipi di dati, valuta le alternative, ad esempio l'utilizzo della istruzione LOOP con un CTE non ricorsivo.

Suddividere una CTE ricorsiva in più CTE ricorsive

Se ritieni che la tua CTE ricorsiva debba superare il limite massimo consentito e iterazioni, potresti essere in grado di scomporre la CTE ricorsiva 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 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 sono sostituite da 10 iterazioni per velocizzare la visualizzazione dei risultati. La query genera 30 righe, ma ogni CTE ricorsiva viene eseguita solo 10 volte. L'output assomiglia a seguenti:

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

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

Utilizza un ciclo anziché un'espressione CTE ricorsiva

Per evitare i limiti di iterazione, ti consigliamo di utilizzare un ciclo anziché una CTE ricorsiva. Puoi creare un ciclo con una delle numerose istruzioni di loop, come LOOP, REPEAT o WHILE. Per ulteriori informazioni, vedi Loop.

Modificare il limite di ricorsione

Se ritieni che i seguenti fattori si applichino al tuo caso, contatta l'Assistenza clienti per elevare il limite di ricorsione:

• Hai un motivo valido per far sì che la CTE ricorrente esegua più di 500 di Google Cloud.
• Va bene un'esecuzione molto più lunga.

Tieni presente che l'aumento del limite di ricorsione comporta dei rischi potenziali:

• La CTE potrebbe non riuscire e restituire un messaggio di errore diverso, ad esempio se la memoria è stata superata o timeout.
• Se il tuo progetto utilizza il modello di prezzo on demand, il CTE potrebbe comunque non riuscire a completare l'operazione a causa di un errore del livello di fatturazione finché non passi al modello di prezzo basato sulla capacità.
• Una CTE ricorsiva con un gran numero di iterazioni consuma molte Google Cloud. Ciò potrebbe influire su altre query in esecuzione all'interno della stessa prenotazione, poiché competono per le risorse condivise.

Prezzi

Se utilizzi la fatturazione on demand, BigQuery addebita un costo in base al numero di byte che vengono elaborati durante l'esecuzione di una query con una CTE ricorsiva.

Per ulteriori informazioni, vedi Calcolo delle dimensioni delle query:

Quote

Per informazioni su quote e limiti CTE ricorrenti, consulta Quote e limiti.