Algumas ou todas as informações nesta página podem não se aplicar à Trusted Cloud by S3NS. Consulte o artigo Diferenças em relação ao Google Cloud para mais detalhes.

Esta página foi traduzida pela API Cloud Translation.

Trabalhe com CTEs recursivas

No GoogleSQL para BigQuery, uma cláusula WITH contém uma ou mais expressões de tabela comuns (CTEs) às quais pode fazer referência numa expressão de consulta. As CTEs podem ser não recursivas, recursivas ou ambas. A palavra-chave RECURSIVE permite a recursão na cláusula WITH (WITH RECURSIVE).

Uma CTE recursiva pode referenciar-se a si própria, a uma CTE anterior ou a uma CTE subsequente. Uma CTE não recursiva só pode referenciar CTEs anteriores e não pode referenciar-se a si própria. As CTEs recursivas são executadas continuamente até não serem encontrados novos resultados, enquanto as CTEs não recursivas são executadas uma vez. Por estes motivos, as CTEs recursivas são usadas frequentemente para consultar dados hierárquicos e dados de grafos.

Por exemplo, considere um gráfico em que cada linha representa um nó que pode ser associado a outros nós. Para encontrar o fecho transitivo de todos os nós acessíveis a partir de um nó inicial específico sem saber o número máximo de saltos, precisa de um CTE recursivo na consulta (WITH RECURSIVE). A consulta recursiva começaria com o caso base do nó inicial, e cada passo calcularia os novos nós não vistos que podem ser alcançados a partir de todos os nós vistos até ao passo anterior. A consulta termina quando não é possível encontrar novos nós.

No entanto, as CTEs recursivas podem ser computacionalmente dispendiosas. Por isso, antes de as usar, reveja este guia e a secção da cláusula WITH da documentação de referência do GoogleSQL.

Crie uma CTE recursiva

Para criar um CTE recursivo no GoogleSQL, use a cláusula WITH RECURSIVE, conforme mostrado no exemplo seguinte:

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

O exemplo anterior produz os seguintes resultados:

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 1         |
 | 2         |
 | 2         |
 | 3         |
 | 3         |
 *-----------*/

Uma CTE recursiva inclui um termo base, um operador de união e um termo recursivo. O termo base executa a primeira iteração da operação de união recursiva. O termo recursivo executa as iterações restantes e tem de incluir uma autorreferência ao CTE recursivo. Apenas o termo recursivo pode incluir uma autorreferência.

No exemplo anterior, a CTE recursiva contém os seguintes componentes:

Nome da CTE recursiva: CTE_1
Termo base: SELECT 1 AS iteration
Operador de união: UNION ALL
Termo recursivo: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

Para saber mais sobre a sintaxe, as regras e os exemplos de CTEs recursivos, consulte a cláusula WITH na documentação de referência do GoogleSQL.

Explore a acessibilidade num gráfico acíclico dirigido (DAG)

Pode usar uma consulta recursiva para explorar a acessibilidade num gráfico acíclico dirigido (DAG). A consulta seguinte encontra todos os nós que podem ser alcançados a partir do nó 5 num gráfico denominado 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;

O exemplo anterior produz os seguintes resultados:

/*------*
 | node |
 +------+
 | 5    |
 | 6    |
 | 7    |
 | 8    |
 *------*/

Resolva problemas de erros de limite de iteração

As CTEs recursivas podem resultar numa recursão infinita, que ocorre quando o termo recursivo é executado continuamente sem cumprir uma condição de terminação. Para terminar as recursões infinitas, é aplicada um limite de iterações para cada CTE recursivo. Para o BigQuery, o limite de iterações é de 500 iterações. Quando uma CTE recursiva atinge o número máximo de iterações, a execução da CTE é anulada com um erro.

Este limite existe porque a computação de um CTE recursivo pode ser dispendiosa e a execução de um CTE com um grande número de iterações consome muitos recursos do sistema e demora muito mais tempo a terminar.

As consultas que atingem o limite de iteração geralmente não têm uma condição de terminação adequada, criando assim um ciclo infinito, ou usam CTEs recursivos em cenários inadequados.

Se receber um erro de limite de iteração de recursão, reveja as sugestões nesta secção.

Verifique se existe recursão infinita

Para evitar a recursão infinita, certifique-se de que o termo recursivo consegue produzir um resultado vazio após a execução de um determinado número de iterações.

Uma forma de verificar a recursão infinita é converter a CTE recursiva num TEMP TABLE com um ciclo REPEAT para as primeiras 100 iterações, da seguinte forma:

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;

Substitua os seguintes valores:

recursive_cte_name: o CTE recursivo a depurar.
base_expression: o termo base da CTE recursiva.
recursive_expression: o termo recursivo da CTE recursiva.
termination_condition_expression: A expressão de terminação da CTE recursiva.

Por exemplo, considere a seguinte CTE recursiva denominada TestCTE:

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

Este exemplo usa os seguintes valores:

recursive_cte_name: TestCTE
base_expression: SELECT 1
recursive_expression: n + 3
termination_condition_expression: MOD(n, 6) != 0

Por conseguinte, o seguinte código testaria o TestCTE para recursão 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;

O exemplo anterior produz os seguintes resultados que incluem o ID da iteração e o número de linhas que foram produzidas durante essa iteração:

/*-----------+----------*
 | 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        |
 *-----------+----------*/

Estes são os resultados reais produzidos durante a iteração 2:

/*----------+-----------*
 | n        | iteration |
 +----------+-----------+
 | 7        | 2         |
 *----------+-----------*/

Se o número de linhas for sempre superior a zero, o que é verdade neste exemplo, é provável que o exemplo tenha uma recursão infinita.

Valide a utilização adequada da CTE recursiva

Verifique se está a usar a CTE recursiva num cenário adequado. As CTEs recursivas podem ser dispendiosas de calcular porque foram concebidas para consultar dados hierárquicos e dados de gráficos. Se não estiver a consultar estes dois tipos de dados, considere alternativas, como usar a declaração LOOP com um CTE não recursivo.

Dividir uma CTE recursiva em várias CTEs recursivas

Se considerar que o seu CTE recursivo precisa de mais iterações do que o máximo permitido, pode dividir o CTE recursivo em vários CTEs recursivos.

Pode dividir uma CTE recursiva com uma estrutura de consulta semelhante à seguinte:

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
[...]

Substitua os seguintes valores:

base_expression: a expressão do termo base para o CTE atual.
recursive_expression: a expressão do termo recursivo para o CTE atual.

Por exemplo, o código seguinte divide uma CTE em três CTEs distintas:

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

No exemplo anterior, 500 iterações são substituídas por 10 iterações para que seja mais rápido ver os resultados da consulta. A consulta produz 30 linhas, mas cada CTE recursivo itera apenas 10 vezes. O resultado tem o seguinte aspeto:

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

Pode testar a consulta anterior em iterações muito maiores.

Use um ciclo em vez de uma CTE recursiva

Para evitar limites de iteração, considere usar um ciclo em vez de uma CTE recursiva. Pode criar um ciclo com uma de várias declarações de ciclo, como LOOP, REPEAT ou WHILE. Para mais informações, consulte o artigo Loops.

Altere o limite recursivo

Se considerar que os seguintes fatores se aplicam, contacte o apoio ao cliente para aumentar o limite recursivo:

Tem um motivo válido para que o seu CTE recursivo seja executado mais de 500 iterações.
Não se importa que a execução seja muito mais longa.

Tenha em atenção que aumentar o limite recursivo tem potenciais riscos:

A CTE pode falhar com uma mensagem de erro diferente, como memória excedida ou tempo limite.
Se o seu projeto estiver a usar o modelo de preços a pedido, o CTE pode continuar a falhar com um erro de nível de faturação até mudar para o modelo de preços baseado na capacidade.
Uma CTE recursiva com um grande número de iterações consome muitos recursos. Isto pode afetar outras consultas que estejam a ser executadas na mesma reserva, uma vez que competem por recursos partilhados.

Preços

Se usar a faturação a pedido, o BigQuery cobra com base no número de bytes processados durante a execução de uma consulta com um CTE recursivo.

Para mais informações, consulte a secção Cálculo do tamanho da consulta.

Quotas

Para ver informações sobre as quotas e os limites de CTEs recursivas, consulte o artigo Quotas e limites.