fonctionnalités, format de commande et exemples d'expressions de table communes - MaxCompute

Une COMMON TABLE EXPRESSION (CTE) est un jeu de résultats nommé temporairement qui simplifie le code SQL. MaxCompute prend en charge les CTE SQL standard pour améliorer la lisibilité et l'efficacité d'exécution des instructions SQL. Cette rubrique décrit les fonctionnalités, la syntaxe et les exemples de CTE.

Vue d'ensemble

Une CTE est un jeu de résultats temporaire défini dans le périmètre d'exécution d'une seule instruction DML. À l'instar d'une table dérivée, une CTE n'est pas stockée en tant qu'objet et persiste uniquement pendant la durée de la requête. L'utilisation de CTE lors du développement améliore la lisibilité du code SQL et simplifie la maintenance des requêtes complexes.
Une CTE est une expression de clause au niveau de l'instruction qui commence par WITH, suivie du nom de l'expression. MaxCompute prend en charge deux formes de CTE :
- NON RECURSIVE CTE : Une CTE qui ne fait pas référence à elle-même et n'effectue pas d'itération. Utilisez cette forme pour simplifier les requêtes qui réutilisent la même logique de sous-requête.
- RECURSIVE CTE : Une CTE capable de faire référence à elle-même de manière itérative, permettant ainsi des capacités de requête récursive en SQL. Généralement utilisée pour parcourir des données hiérarchiques telles que des organigrammes.
CTE matérialisée : Lors de la définition d'une CTE, utilisez l'indicateur MATERIALIZE HINT dans l'instruction SELECT pour mettre en cache le résultat de la CTE dans une table temporaire. Les références ultérieures lisent les données depuis le cache, évitant ainsi les problèmes de limite de mémoire dans les scénarios de CTE profondément imbriquées et améliorant les performances.

NON RECURSIVE CTE

Syntaxe

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

Paramètres

Paramètre

Obligatoire

Description

cte_name

Oui

Nom de la CTE. Il doit être unique au sein de la clause WITH. Toute référence ultérieure à cte_name dans l'instruction lit les données depuis cette CTE.

col_name

Non

Noms des colonnes de sortie pour la CTE. S'ils sont omis, les noms de colonne sont hérités de la liste SELECT dans cte_query.

cte_query

Oui

Instruction SELECT dont le jeu de résultats définit la CTE.

Exemple

La requête suivante utilise UNION ALL pour combiner deux opérations JOIN. Les deux jointures partagent la même sous-requête de gauche, qui doit être dupliquée en l'absence de 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;

La réécriture avec une CTE supprime la duplication. La sous-requête a est définie une seule fois et réutilisée par les deux jointures :

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;

RECURSIVE CTE

Syntaxe

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

Paramètres

Paramètre

Obligatoire

Description

RECURSIVE

Oui

La clause de CTE récursive doit commencer par WITH RECURSIVE.

cte_name

Oui

Nom de la CTE. Il doit être unique au sein de la clause WITH actuelle.

col_name

Non

Noms des colonnes de sortie. S'ils sont omis, les noms de colonne sont déduits de initial_part.

initial_part

Oui

Instruction SELECT qui produit le jeu de données initial (itération 0). Elle ne peut pas faire référence à cte_name.

recursive_part

Oui

Instruction SELECT qui fait référence à cte_name pour calculer l'itération suivante à partir de la précédente.

UNION ALL

Oui

Connecte initial_part et recursive_part. UNION (sans ALL) n'est pas pris en charge.

Limitations

Les CTE récursives ne peuvent pas apparaître dans les sous-requêtes IN, EXISTS ou scalaires.
Le nombre maximal d'itérations par défaut est de 10. Augmentez cette limite en définissant odps.sql.rcte.max.iterate.num (valeur maximale : 100).
Les résultats intermédiaires ne sont pas enregistrés entre les itérations. Si la tâche échoue, l'exécution redémarre depuis le début. Pour les calculs récursifs de longue durée, limitez le nombre d'itérations ou stockez les résultats intermédiaires dans une table temporaire.
Les CTE récursives ne sont pas prises en charge en mode Query Acceleration (MaxQA/MCQA).
- En mode MCQA, si interactive_auto_rerun=true est défini, la tâche revient au mode normal. Sinon, la tâche échoue.
- En mode MaxQA, le basculement automatique n'est pas pris en charge. Le job échoue directement et doit être soumis manuellement à un groupe de quotas de traitement par lots pour une nouvelle tentative.

Exemples

Exemple 1 : Définir une CTE récursive nommée 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;

Remarque

Dans recursive_part, définissez une condition de terminaison pour éviter une boucle infinie. Dans cet exemple, WHERE a + 1 <= 5 sert de critère de terminaison. Si la condition WHERE n'est pas satisfaite, le jeu de données généré lors de l'itération actuelle est vide et l'itération s'arrête.
Si les noms des colonnes de sortie ne sont pas explicitement spécifiés, le système prend en charge l'inférence automatique. Par exemple, dans la méthode 2, les noms des colonnes de sortie de initial_part sont utilisés comme noms des colonnes de sortie de la CTE récursive.

Résultat :

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

Exemple 2 : CTE récursive dans une sous-requête (erreur de compilation)

Les CTE récursives ne sont pas autorisées à l'intérieur des sous-requêtes IN, EXISTS ou scalaires. La requête suivante échoue lors de la compilation :

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);

Erreur :

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]

Exemple 3 : Parcourir une hiérarchie organisationnelle

Créez une table employees et insérez des données :

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');

Définissez une CTE récursive nommée company_hierarchy avec trois colonnes de sortie : le nom de l'employé, le nom de son responsable et son niveau dans la hiérarchie :

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;

L'exécution se déroule comme suit :

Itération 0 (initial_part) : Sélectionne les employés où boss_name IS NULL, en attribuant level = 0. Résultat : ('zhang_3', NULL, 0).
Itération 1 (recursive_part) : Jointure de employees avec la table de travail (itération 0). La condition e.boss_name = h.name trouve les employés gérés par zhang_3. Résultat : li_4 et wang_5 au level = 1.
Itération 2 : Trouve les employés gérés par li_4 ou wang_5. Résultat : zhao_6 et qian_7 au level = 2.
Itération 3 : Aucun employé n'a zhao_6 ou qian_7 comme responsables. La table de travail est vide et l'itération s'arrête.

Résultat :

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

Exemple 4 : Les données cycliques provoquent une boucle infinie

Insérez un enregistrement dans la table employees de l'exemple 3 où un employé est son propre responsable.
```
INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');
```
Cet enregistrement indique que le responsable de qian_7 est qian_7 lui-même. L'exécution de la CTE récursive définie précédemment entraîne une boucle infinie. Le système limite le nombre maximal d'itérations. La requête finit par échouer.

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

CTE matérialisée

Pour les CTE non récursives, MaxCompute développe toutes les CTE inline lors de la génération d'un plan d'exécution. Par exemple :

WITH v1 AS (SELECT SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

Résultats :

+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Cela équivaut à exécuter SIN(1.0) deux fois :

SELECT a FROM (SELECT SIN(1.0) AS a)
UNION ALL
SELECT a FROM (SELECT SIN(1.0) AS a);

Dans des scénarios complexes avec des CTE profondément imbriquées, si toutes les CTE sont développées jusqu'aux nœuds feuilles les plus basiques, le résultat est un arbre syntaxique très volumineux. Cela peut provoquer des échecs dus à un nombre excessif de nœuds d'arbre syntaxique lors de la génération du plan d'exécution et entraîner des problèmes de limite de mémoire. Par exemple :

WITH
  v1 AS (SELECT 1L AS a, 2L AS b, 3L AS c),
  v2 AS (SELECT * FROM v1 UNION ALL SELECT * FROM v1 UNION ALL SELECT * FROM v1),
  v3 AS (SELECT * FROM v2 UNION ALL SELECT * FROM v2 UNION ALL SELECT * FROM v2),
  v4 AS (SELECT * FROM v3 UNION ALL SELECT * FROM v3 UNION ALL SELECT * FROM v3),
  v5 AS (SELECT * FROM v4 UNION ALL SELECT * FROM v4 UNION ALL SELECT * FROM v4),
  v6 AS (SELECT * FROM v5 UNION ALL SELECT * FROM v5 UNION ALL SELECT * FROM v5),
  v7 AS (SELECT * FROM v6 UNION ALL SELECT * FROM v6 UNION ALL SELECT * FROM v6),
  v8 AS (SELECT * FROM v7 UNION ALL SELECT * FROM v7 UNION ALL SELECT * FROM v7),
  v9 AS (SELECT * FROM v8 UNION ALL SELECT * FROM v8 UNION ALL SELECT * FROM v8)
SELECT * FROM v9;

Pour résoudre ce problème, MaxCompute fournit la fonctionnalité de CTE matérialisée, qui met en cache les résultats de calcul des CTE pour référence par le code SQL en dehors de la clause WITH sans développement complet. Ce mécanisme évite efficacement les problèmes de limite de mémoire causés par le développement des CTE imbriquées et améliore les performances des instructions CTE.

Exemple d'utilisation

Ajoutez l'indicateur /*+ MATERIALIZE */ à l'instruction SELECT de niveau supérieur d'une CTE non récursive pour mettre en cache son résultat dans une table temporaire. Les références ultérieures lisent les données depuis le cache au lieu de réexécuter la requête :

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

Lorsque l'indicateur est effectif, l'onglet Job Details dans LogView affiche plusieurs Fuxi Jobs, confirmant que le résultat intermédiaire a été stocké.

Limitations

L'indicateur MATERIALIZE doit être appliqué à l'instruction SELECT de niveau supérieur d'une CTE non récursive. Les CTE récursives n'ont pas besoin de l'indicateur MATERIALIZE.
- Exemple incorrect : Dans la CTE suivante, l'instruction de niveau supérieur est UNION plutôt que SELECT, donc l'indicateur MATERIALIZE ne prend pas effet.
```
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;
```
- Exemple correct : Réécrivez l'exemple incorrect ci-dessus en l'encapsulant dans une sous-requête.
```
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;
```
Si la CTE utilise une fonction non déterministe telle que RAND ou une UDF Java/Python non déterministe, la matérialisation de la CTE met en cache une seule évaluation de la fonction. Les références ultérieures renvoient la valeur mise en cache, ce qui modifie le comportement sémantique des requêtes qui attendent des valeurs aléatoires indépendantes à chaque appel.
Les CTE matérialisées ne sont pas prises en charge dans MCQA(Query Acceleration 1.0) -deprecated. Si la tâche s'exécute en mode MCQA avec interactive_auto_rerun=true, elle revient au mode normal. Sinon, la tâche échoue.