MaxCompute:Expressão de tabela comum (CTE)

Sintaxe

WITH
  <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query>
  )
  [, <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query2>
  )
  , ...]

Parâmetros

Exemplo

A consulta a seguir usa um UNION ALL para combinar duas operações JOIN. Ambos os joins compartilham a mesma subconsulta à esquerda, que precisa ser duplicada sem uma CTE:

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0) b
    ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0) b
    ON a.key = b.key AND b.key IS NOT NULL
) d;

A reescrita com uma CTE elimina a duplicação. A subconsulta a é definida uma única vez e reutilizada por ambos os joins:

WITH
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key = b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key = c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM d UNION ALL SELECT * FROM e;

Sintaxe

WITH RECURSIVE <cte_name> [(col_name [, col_name] ...)] AS (
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

Parâmetros

Limitações

• CTEs recursivas não podem aparecer em subconsultas IN, EXISTS ou escalares.

• O número máximo padrão de iterações é 10. Aumente o limite definindo odps.sql.rcte.max.iterate.num (valor máximo: 100).

• Os resultados intermediários não são salvos entre iterações. Se a tarefa falhar, a execução recomeça do início. Para computações recursivas de longa duração, limite o número de iterações ou armazene os resultados intermediários em uma tabela temporária.

• CTEs recursivas não são suportadas no modo Query Acceleration (MaxQA/MCQA).

  • No modo MCQA, se interactive_auto_rerun=true estiver definido, a tarefa retorna ao modo normal. Caso contrário, a tarefa falha.

  • No modo MaxQA, o retorno automático não é suportado. O job falha diretamente e deve ser reenviado manualmente para um grupo de cotas de processamento em lote para nova tentativa.

Exemplos

• Exemplo 1: definir uma CTE recursiva chamada cte_name.

  -- Method 1: Explicitly specify output column names
  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L                                   -- initial_part: iteration 0
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5    -- recursive_part: references previous iteration
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;
  
  -- Method 2: Infer column names from initial_part
  WITH RECURSIVE cte_name AS (
    SELECT 1L AS a, 1L AS b
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;

  Nota

  • No recursive_part, defina uma condição de terminação para evitar loop infinito. Neste exemplo, WHERE a + 1 <= 5 serve como critério de terminação. Se a condição WHERE não for atendida, o conjunto de dados gerado na iteração atual fica vazio e a iteração é interrompida.

  • Se os nomes das colunas de saída não forem especificados explicitamente, o sistema suporta inferência automática. Por exemplo, no Método 2, os nomes das colunas de saída de initial_part são usados como nomes das colunas de saída da CTE recursiva.

  Resultado:

  +------------+------------+
  | a          | b          |
  +------------+------------+
  | 1          | 1          |
  | 2          | 2          |
  | 3          | 3          |
  | 4          | 4          |
  | 5          | 5          |
  +------------+------------+

• Exemplo 2: CTE recursiva em uma subconsulta (erro de compilação)

  CTEs recursivas não são permitidas dentro de subconsultas IN, EXISTS ou escalares. A consulta a seguir falha na compilação:

  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
  SELECT x, x IN (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

  Erro:

  FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

• Exemplo 3: percorrer uma hierarquia organizacional

  Crie uma tabela employees e insira dados:

  CREATE TABLE employees(name STRING, boss_name STRING);
  INSERT INTO TABLE employees VALUES
    ('zhang_3', null),
    ('li_4',    'zhang_3'),
    ('wang_5',  'zhang_3'),
    ('zhao_6',  'li_4'),
    ('qian_7',  'wang_5');

  Defina uma CTE recursiva chamada company_hierarchy com três colunas de saída: o nome do funcionário, o nome do gerente e o nível na hierarquia:

  WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
      SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL
      UNION ALL
      SELECT e.name, e.boss_name, h.level + 1
        FROM employees e
        JOIN company_hierarchy h ON e.boss_name = h.name
  )
  SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

  A execução ocorre da seguinte forma:

  • Iteração 0 (initial_part): seleciona os funcionários onde boss_name IS NULL, atribuindo level = 0. Resultado: ('zhang_3', NULL, 0).

  • Iteração 1 (recursive_part): faz o join de employees com a tabela de trabalho (iteração 0). A condição e.boss_name = h.name encontra os funcionários gerenciados por zhang_3. Resultado: li_4 e wang_5 no level = 1.

  • Iteração 2: encontra os funcionários gerenciados por li_4 ou wang_5. Resultado: zhao_6 e qian_7 no level = 2.

  • Iteração 3: nenhum funcionário tem zhao_6 ou qian_7 como gerente. A tabela de trabalho fica vazia e a iteração é interrompida.

  Resultado:

  +---------+-----------+------------+
  | name    | boss_name | level      |
  +---------+-----------+------------+
  | zhang_3 | NULL      | 0          |
  | li_4    | zhang_3   | 1          |
  | wang_5  | zhang_3   | 1          |
  | zhao_6  | li_4      | 2          |
  | qian_7  | wang_5    | 2          |
  +---------+-----------+------------+

• Exemplo 4: Dados cíclicos causam loop infinito

  Insira um registro na tabela employees do Exemplo 3 em que um funcionário é seu próprio gerente.

  INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');

  Esse registro declara que o gerente de qian_7 é o próprio qian_7. A execução da CTE recursiva definida anteriormente resulta em um loop infinito. O sistema limita o número máximo de iterações. A consulta eventualmente falha.

  Erro:

  FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10

Exemplo de uso

Adicione a dica /*+ MATERIALIZE */ ao SELECT de nível superior de uma CTE não recursiva para armazenar seu resultado em cache em uma tabela temporária. As referências subsequentes leem do cache em vez de executar a consulta novamente:

WITH v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- Result:
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Quando a dica está em vigor, a aba Job Details no LogView mostra vários Fuxi Jobs, confirmando que o resultado intermediário foi armazenado.

Limitações

• A dica MATERIALIZE deve ser aplicada à instrução SELECT de nível superior de uma CTE não recursiva. CTEs recursivas não precisam da dica MATERIALIZE.

  • Exemplo incorreto: na CTE a seguir, a instrução de nível superior é UNION em vez de SELECT, portanto a dica MATERIALIZE não tem efeito.

    WITH v1 AS (
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a
      UNION ALL
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

  • Exemplo correto: reescreva o exemplo incorreto acima envolvendo-o em uma subconsulta.

    WITH
      v1 AS (SELECT /*+ MATERIALIZE */ * FROM
               (SELECT SIN(1.0) AS a
                UNION ALL
                SELECT SIN(1.0) AS a)
            )
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

• Se a CTE usar uma função não determinística como RAND ou uma UDF Java/Python não determinística, a materialização da CTE armazena em cache uma única avaliação da função. As referências subsequentes retornam o valor em cache, o que altera o comportamento semântico de consultas que esperam valores aleatórios independentes por chamada.

• CTEs materializadas não são suportadas no MCQA(Query Acceleration 1.0) -deprecated. Se a tarefa for executada no modo MCQA com interactive_auto_rerun=true, ela retorna ao modo normal. Caso contrário, a tarefa falha.

• Ao usar uma CTE MATERIALIZE, o sistema precisa primeiro calcular a CTE e armazenar os resultados intermediários em cache. Como resultado, uma consulta é dividida em vários estágios para execução. Se ocorrer um failover durante a execução, a consulta só pode ser recomputada do início. Para instruções SQL que demoram muito para executar, recomendamos que você crie tabelas temporárias explicitamente para armazenar os resultados intermediários. Isso evita a perda dos resultados de computação já concluídos caso ocorra um erro de execução.