MaxCompute:FAQ sur les opérations DQL

Comment résoudre l'erreur « Repeated key in GROUP BY » lors de l'exécution d'une instruction SQL MaxCompute ?

• Problème

  Lorsque vous exécutez une instruction SQL MaxCompute, l'erreur suivante s'affiche :

  FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY.

• Cause

  Une constante est utilisée après SELECT DISTINCT, ce qui n'est pas autorisé.

• Solution

  Décomposez l'instruction SQL en deux niveaux. Le niveau interne gère la logique DISTINCT sans constantes, tandis que le niveau externe ajoute les données constantes.

Comment résoudre l'erreur « Expression not in GROUP BY key » lors de l'exécution d'une instruction SQL MaxCompute ?

• Problème

  Lorsque vous exécutez une instruction SQL MaxCompute, l'erreur suivante s'affiche :

  FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

• Cause

  Une colonne non incluse dans la clause GROUP BY est référencée directement. Pour plus d'informations, consultez Clause GROUP BY (col_list).

• Solution

  Assurez-vous que les colonnes figurant dans la liste SELECT sont soit des colonnes de la clause GROUP BY, soit des colonnes traitées par des fonctions d'agrégation, telles que SUM ou COUNT.

J'ai exécuté une opération GROUP BY sur la table A pour générer la table B. La table B contient moins de lignes que la table A, mais son stockage physique est 10 fois plus volumineux. Pourquoi cela se produit-il ?

MaxCompute utilise la compression columnaire pour le stockage. Si les valeurs consécutives d'une même colonne sont similaires, le taux de compression est élevé. Lorsque odps.sql.groupby.skewindata=true est activé, les données sont dispersées, ce qui entraîne un taux de compression plus faible. Pour améliorer la compression, effectuez un tri local lors de l'écriture des données avec une instruction SQL.

L'exécution d'une requête GROUP BY sur 10 milliards d'enregistrements affecte-t-elle les performances ? Existe-t-il une limite de volume de données pour GROUP BY ?

Non. GROUP BY n'impose aucune limite de volume de données.

Comment les données renvoyées par une requête MaxCompute sont-elles triées ?

Les données sont lues à partir des tables MaxCompute dans un ordre indéfini. Sans clause de tri, les résultats de la requête ne sont pas ordonnés.

Pour trier les données, ajoutez une clause order by xx limit n à votre instruction SQL.

Pour un tri complet, définissez la valeur n de limit sur nombre total d'enregistrements + 1.

MaxCompute prend-il en charge la syntaxe ORDER BY FIELD NULLS LAST ?

MaxCompute prend en charge cette syntaxe. Pour plus d'informations, consultez Différences par rapport aux autres syntaxes SQL.

Comment résoudre l'erreur « ORDER BY must be used with a LIMIT clause » lors de l'exécution d'une instruction SQL MaxCompute ?

• Problème

  Lorsque vous exécutez une instruction SQL MaxCompute, l'erreur suivante s'affiche :

  FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

• Cause

  ORDER BY effectue un tri global sur un seul nœud d'exécution ; une clause LIMIT est donc requise par défaut pour éviter un traitement excessif des données sur ce nœud.

• Solution

  Si votre scénario nécessite ORDER BY sans clause LIMIT, désactivez cette exigence de l'une des manières suivantes :

  • Au niveau du projet : Exécutez la commande setproject odps.sql.validate.orderby.limit=false; pour désactiver l'exigence selon laquelle order by doit être utilisé avec une clause limit.

  • Au niveau de la session : Exécutez la commande set odps.sql.validate.orderby.limit=false; pour désactiver l'exigence selon laquelle order by doit être utilisé avec une clause limit. Cette commande doit être soumise conjointement avec l'instruction SQL.

    Remarque

    La désactivation de l'exigence order by-limit implique le tri d'un grand ensemble de données sur un seul nœud d'exécution, ce qui dégrade les performances et augmente la consommation de ressources.

Pour plus d'informations sur ORDER BY, consultez Clause ORDER BY (ORDER_condition).

Lorsque j'exécute une instruction SQL MaxCompute avec une sous-requête NOT IN, celle-ci renvoie des dizaines de milliers d'enregistrements. Toutefois, si la sous-requête après IN ou NOT IN renvoie des partitions, le nombre maximal de partitions renvoyées est de 1 000. Comment implémenter cette requête si je dois absolument utiliser NOT IN ?

select * from a where a.ds not in (select ds from b);
Change the statement to the following:
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;              

Comment fusionner deux tables sans association entre elles ?

Pour une fusion verticale, utilisez union all. Pour une fusion horizontale, utilisez la fonction row_number pour ajouter une colonne ID aux deux tables, joignez-les sur l'ID, puis sélectionnez les champs requis. Pour plus d'informations, consultez Union ou ROW_NUMBER.

Comment résoudre l'erreur « ValidateJsonSize » lors d'une opération UNION ALL ?

• Symptômes

  Lorsque vous exécutez une instruction SQL contenant 200 opérations UNION ALL, telle que select count(1) as co from client_table union all ..., l'erreur suivante se produit :

  FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
      "ErrCode": "RPC_FAILED_REPLY",
      "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack      

• Causes

  • Cause 1 : Le plan d'exécution dépasse la limite de 1 024 Ko de l'architecture sous-jacente. La longueur du plan d'exécution n'est pas directement proportionnelle à la longueur de l'instruction SQL et ne peut pas être estimée à l'avance.

  • Cause 2 : Le nombre de partitions est trop élevé.

  • Cause 3 : Il y a trop de petits fichiers.

• Solutions

  • Solution pour la cause 1 : Décomposez la longue instruction SQL pour éviter de dépasser la limite de longueur.

  • Solution pour la cause 2 : Ajustez le nombre de partitions. Pour plus d'informations, consultez Partition.

  • Solution pour la cause 3 : Fusionnez les petits fichiers.

Comment résoudre l'erreur « Both left and right aliases encountered in JOIN » lors d'une opération JOIN ?

• Problème

  Lorsque vous exécutez une instruction SQL MaxCompute, l'erreur suivante s'affiche :

  FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

• Causes

  • Cause 1 : Une jointure non équivalente est spécifiée dans la clause ON, telle que table1.c1>table2.c3.

  • Cause 2 : Un côté de la condition JOIN fait référence aux colonnes des deux tables, par exemple table1.col1 = concat(table1.col2,table2.col3).

• Solutions

  • Solution pour la cause 1 : Modifiez l'instruction SQL. La condition de jointure doit être une jointure équivalente.

    Remarque

    Si vous devez absolument utiliser une jointure non équivalente, vous pouvez ajouter un indicateur mapjoin. Pour plus d'informations, consultez ODPS-0130071.

  • Solution pour la cause 2 : Si l'une des tables est petite, vous pouvez utiliser la méthode MAPJOIN.

Comment résoudre l'erreur « Maximum 16 join inputs allowed » lors d'une opération JOIN ?

• Symptômes

  Lorsque vous exécutez une instruction SQL MaxCompute, l'erreur suivante s'affiche :

  FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

• Cause

  Dans SQL MaxCompute, une opération MAPJOIN prend en charge un maximum de six petites tables, et une seule opération JOIN prend en charge un maximum de 16 tables.

• Solution

  Joignez d'abord certaines des petites tables dans une table temporaire. Cela réduit le nombre de tables d'entrée.

Lors d'une opération JOIN, le nombre d'enregistrements dans le résultat est supérieur à celui de la table d'origine. Comment corriger ce problème ?

• Symptômes

  Après avoir exécuté l'instruction SQL MaxCompute suivante, le nombre d'enregistrements dans le résultat de la requête est supérieur au nombre d'enregistrements dans table1.

  select count(*) from table1 a left outer join table2 b on a.ID = b.ID;

• Cause

  Une jointure externe gauche (left outer join) renvoie tous les enregistrements de table1, même s'il n'existe aucun enregistrement correspondant dans table2. Si table2 contient des ID dupliqués, le nombre d'enregistrements renvoyés augmente. Par exemple :

  Supposons que table1 contienne les données suivantes.

  id	values
  1	a
  1	b
  2	c

  Supposons que table2 contienne les données suivantes.

  id	values
  1	A
  1	B
  3	D

  La commande select count(*) from table1 a left outer join table2 b on a.ID = b.ID; renvoie le résultat suivant.

  id1	values1	id2	values2
  1	b	1	B
  1	b	1	A
  1	a	1	B
  1	a	1	A
  2	c	NULL	NULL

  • Des enregistrements avec id=1 existent dans les deux tables. Un produit cartésien est effectué et quatre enregistrements sont renvoyés.

  • L'enregistrement avec id=2 existe uniquement dans table1. Un seul enregistrement est renvoyé.

  • Des enregistrements avec id=3 existent uniquement dans table2. Aucun enregistrement n'est renvoyé car il n'y a pas d'enregistrements correspondants dans table1.

• Solution

  Vérifiez si table2 contient des ID dupliqués :

  select id, count(*) as cnt from table2 group by id having cnt>1 limit 10;

  Pour éviter le produit cartésien, réécrivez l'instruction SQL comme suit :

  select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;

Pourquoi une analyse complète de la table est-elle interdite dans une opération JOIN même si une condition de partition est spécifiée ?

• Problème

  Lorsque le même code est exécuté dans deux projets, il réussit dans l'un mais échoue dans l'autre.

  select t.stat_date 
  from fddev.tmp_001 t  
  left outer join (select '20180830' as ds from fddev.dual ) t1 
  on t.ds = 20180830
  group by t.stat_date; 

  L'exécution ayant échoué signale l'erreur suivante :

  Table(fddev,tmp_001) is full scan with all partitions,please specify partitions predicates.

• Cause

  Lorsque vous effectuez une opération SELECT, la condition de partition doit figurer dans la clause WHERE. L'utilisation de la clause ON à cette fin n'est pas standard.

  L'exécution a réussi dans un projet car elle était configurée avec la commande set odps.sql.outerjoin.supports.filters=false. Cette commande convertit la condition de la clause ON en condition de filtre. Ce comportement est compatible avec la syntaxe Hive, mais ne respecte pas la norme SQL.

• Solution

  Placez la condition de filtre de partition dans la clause WHERE.

Pour une opération JOIN, l'élimination des partitions (partition pruning) est-elle effective si la condition se trouve dans la clause ON ou dans la clause WHERE ?

• Si la condition d'élimination des partitions se trouve dans la clause WHERE, l'élimination des partitions est effective.

• Si la condition se trouve dans la clause ON, l'élimination des partitions est effective sur la table de détails, mais pas sur la table principale. Cela entraîne une analyse complète de la table principale.

Pour plus d'informations sur l'élimination des partitions, consultez Évaluer la validité de l'élimination des partitions.

Comment utiliser MAPJOIN pour mettre en cache plusieurs petites tables ?

MAPJOIN accélère les requêtes en mettant en cache les petites tables en mémoire. Spécifiez les alias de table dans l'indicateur MAPJOIN.

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+
                

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count(*) as cnt,category from iris group by category
) b
on a.category = b.category
cross join 
(
  select count(*) as cnt from iris
) c;              

Peut-on intervertir les grandes et les petites tables dans un MAPJOIN ?

Oui. Le système distingue les grandes et les petites tables en fonction de la taille de stockage et charge la petite table en mémoire pour accélérer l'opération JOIN.

Après avoir défini une condition de filtre pour une instruction SQL MaxCompute, une erreur indique que les données d'entrée dépassent 100 Go. Comment résoudre ce problème ?

Filtrez d'abord les données par le champ de partition, puis par les autres champs non partitionnés. Le volume de données d'entrée est calculé après le filtrage au niveau des partitions.

La clause WHERE pour les recherches floues dans SQL MaxCompute prend-elle en charge les expressions régulières ?

Oui. Par exemple, select * from user_info where address rlike '[0-9]{9}'; permet de trouver les enregistrements contenant un nombre à neuf chiffres.

Si je souhaite synchroniser uniquement 100 enregistrements, comment puis-je utiliser LIMIT dans la condition de filtre WHERE ?

LIMIT n'est pas pris en charge dans une condition de filtre. Utilisez une instruction SQL pour sélectionner d'abord 100 enregistrements, puis effectuez l'opération de synchronisation.

Comment améliorer l'efficacité des requêtes ? Puis-je ajuster les paramètres de partitionnement ?

Partitionnez une table selon ses champs de partition pour permettre l'ajout, la mise à jour ou la lecture de données dans des partitions spécifiques sans effectuer d'analyse complète de la table. Pour plus d'informations, consultez Opérations sur les tables.

SQL MaxCompute prend-il en charge l'instruction WITH AS ?

Oui. MaxCompute prend en charge les expressions de table communes (CTE) SQL standard pour améliorer la lisibilité et l'efficacité d'exécution. Pour plus d'informations, consultez COMMON TABLE EXPRESSION (CTE).

Comment diviser une ligne de données en plusieurs lignes ?

Utilisez LATERAL VIEW avec des fonctions génératrices de tables telles que Split et Explode pour diviser une ligne en plusieurs lignes, puis agrégez les données résultantes.

Dans le fichier odps_config.ini du client, j'ai défini use_instance_tunnel=false et instance_tunnel_max_record=10. Pourquoi une instruction SELECT affiche-t-elle toujours de nombreux enregistrements ?

Remplacez use_instance_tunnel=false par use_instance_tunnel=true pour que le paramètre instance_tunnel_max_record prenne effet.

Comment utiliser une expression régulière pour déterminer si un champ contient des caractères chinois ?

select 'field' rlike '[\\x{4e00}-\\x{9fa5}]+';