MaxCompute:Réécriture des instructions SQL incompatibles

Contexte

MaxCompute V2.0 adopte pleinement l'écosystème open source. Il prend en charge davantage de fonctionnalités linguistiques et offre de meilleures performances d'exécution. Toutefois, MaxCompute V2.0 effectue des vérifications syntaxiques plus strictes. Les requêtes dont la syntaxe est imprécise et qui s'exécutaient avec succès dans les versions précédentes du compilateur peuvent générer des erreurs dans MaxCompute V2.0.

group.by.with.star

bad.escape

description : Ce problème est lié à des séquences d'échappement incorrectes.

Selon les règles de MaxCompute, vous devez utiliser une barre oblique inverse suivie d'un nombre octal à trois chiffres pour représenter les caractères ASCII de 0 à 127 dans un littéral de chaîne. Par exemple, vous pouvez utiliser « \001 » et « \002 » pour représenter 0 et 1. Toutefois, les versions antérieures traitaient également \01 et \0001 comme \001.

column.repeated.in.creation

description : Dans MaxCompute V2.0, une erreur se produit si vous utilisez des noms de colonne en double lors de la création d'une table.

string.join.double

window.ref.prev.window.alias

description : Ce problème survient lorsqu'une fonction de fenêtrage référence l'alias d'une autre fonction de fenêtrage dans la même liste SELECT.

select.invalid.token.after.star

description : Dans une liste SELECT, vous pouvez utiliser un astérisque (*) pour sélectionner toutes les colonnes d'une table. Toutefois, vous ne pouvez pas ajouter un alias après l'astérisque (). Cette syntaxe n'est pas autorisée même si l'astérisque () ne se développe qu'en une seule colonne. Le compilateur MaxCompute V2.0 signale une erreur pour cette syntaxe.

agg.having.ref.prev.agg.alias

description : Ce problème survient lorsque la liste SELECT référence l'alias d'une fonction d'agrégation précédente et qu'une clause HAVING est présente.

order.by.no.limit

description : Par défaut, MaxCompute vous impose d'ajouter une clause limit après une clause order by pour limiter le nombre de résultats renvoyés. Cette exigence existe car order by effectue un tri complet, ce qui entraîne de faibles performances d'exécution si aucune clause limit n'est utilisée.

Ajoutez une clause limit à la sous-requête order by v.uv desc.

create view table_view as select id from table_view order by id;

Pour accéder à cette vue :

select * from table_view;

MaxCompute V1.0 ne signale pas d'erreur. MaxCompute V2.0 signale le message d'erreur suivant :

FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause

generated.column.name.multi.window

description : Ce problème est lié à l'utilisation d'aliases générés automatiquement.

Les versions antérieures de MaxCompute génèrent automatiquement un alias pour chaque expression dans une instruction SELECT. Cet alias est affiché dans la console. Toutefois, les règles de génération de cet alias ne sont pas garanties et peuvent changer. Par conséquent, vous ne devez pas utiliser d'alias générés automatiquement.

MaxCompute V2.0 émet un avertissement lorsque des alias générés automatiquement sont utilisés. Cette pratique ne peut pas être interrompue pour le moment car elle est largement utilisée.

Dans certains cas, les règles de génération des alias changent entre les versions de MaxCompute. Comme certaines tâches en ligne dépendent de ces alias, ces requêtes peuvent échouer lors d'une mise à niveau ou d'un retour arrière de version de MaxCompute. Si vous rencontrez ce problème, vous devez modifier vos requêtes pour spécifier explicitement les alias des colonnes que vous souhaitez utiliser.

non.boolean.filter

Problème lié à l'utilisation d'une condition de filtre non booléenne.

MaxCompute n'autorise pas les conversions implicites entre les types booléens et les autres types de données. Toutefois, les versions antérieures de MaxCompute vous permettaient d'utiliser BIGINT comme condition de filtre dans certains cas. MaxCompute V2.0 n'autorise plus ce comportement. Si vos scripts contiennent de telles conditions de filtre, vous devez les modifier. Voici un exemple :

Syntaxe incorrecte :

select id, count(*) from table_name group by id having id;

Message d'erreur :

FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression

La méthode correcte est la suivante :

select id, count(*) from table_name group by id having id <> 0;

post.select.ambiguous

Problème lié au référencement de colonnes aux noms conflictuels dans les instructions order by, cluster by, distribute by ou sort by.

Dans les versions antérieures de MaxCompute, le système sélectionne par défaut la dernière colonne de la liste SELECT comme objet de l'opération. MaxCompute V2.0 signale une erreur. Vous devez modifier vos instructions. Voici un exemple :

Syntaxe incorrecte :

select a, b as a from t order by a limit 10;

Message d'erreur :

FAILED: ODPS-0130071:[1,34] Semantic analysis exception - a is ambiguous, can be both t.a or null.a

Voici la modification correcte :

select a as c, b as a from t order by a limit 10;

Cette modification couvre également les cas où les noms entrent en conflit mais où la sémantique est identique. Bien que cela ne crée pas d'ambiguïté, un avertissement est signalé pour vous encourager à effectuer des corrections, car cette syntaxe peut facilement entraîner des erreurs.

duplicated.partition.column

Problème lié à la spécification de partitions portant le même nom dans une requête.

Les versions antérieures de MaxCompute ne signalaient pas d'erreur lorsque vous spécifiiez une clé de partition portant le même nom. Au lieu de cela, la valeur de la dernière clé écrasait la valeur de la première. Ce comportement pouvait facilement prêter à confusion. MaxCompute V2.0 signale une erreur dans ce cas. Les exemples suivants illustrent le problème :

Syntaxe incorrecte 1 :

insert overwrite table partition (ds = '1', ds = '2'）select ... ;

Au moment de l'exécution, ds = ‘1’ est ignoré.

La méthode correcte est la suivante :

insert overwrite table partition (ds = '2'）select ... ;

Syntaxe incorrecte 2 :

create table t (a bigint, ds string) partitioned by (ds string);

Procédure correcte :

create table t (a bigint) partitioned by (ds string);

order.by.col.ambiguous

Problème lié à un alias dupliqué dans la liste select, référencé ultérieurement par une clause order by.

Syntaxe incorrecte :

select id, id
from table_test 
order by id;

Procédez comme suit pour corriger le problème :

select id, id id2
from table_name 
order by id;

Supprimez l'alias dupliqué avant de le référencer dans la clause order by.

in.subquery.without.result

Problème signalant que colx n'existe pas dans la table source lorsque colx in subquery ne renvoie aucun résultat.

Syntaxe incorrecte :

select * from table_name
where not_exist_col in (select id from table_name limit 0);

Message d'erreur :

FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolved

ctas.if.not.exists

Problème lié à une syntaxe incorrecte pour la table de destination.

Si la table de destination existe déjà, les versions antérieures de MaxCompute n'effectuent pas de vérification syntaxique. MaxCompute V2.0 effectue des vérifications syntaxiques normales. Cette modification peut générer de nombreux messages d'erreur, comme illustré ci-dessous :

Syntaxe incorrecte :

create table if not exists table_name
as
select * from not_exist_table;

Message d'erreur :

FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolved

worker.restart.instance.timeout

Dans les versions antérieures de MaxCompute, chaque enregistrement produit par une fonction définie par l'utilisateur (UDF) déclenche une écriture dans le système de fichiers distribué et envoie un signal de maintien de connexion (heartbeat) à Fuxi. Si l'UDF ne produit aucun résultat pendant 10 minutes, le message d'erreur suivant s'affiche :

FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.

Le framework d'exécution de MaxCompute V2.0 prend en charge la vectorisation. Ce mécanisme traite plusieurs lignes d'une colonne simultanément afin d'améliorer l'efficacité d'exécution. Toutefois, la vectorisation peut entraîner l'expiration du délai d'attente d'instructions qui s'exécutaient sans erreur auparavant. Ce problème survient si l'intervalle entre la production de deux enregistrements était inférieur à 10 minutes. Étant donné que plusieurs lignes sont traitées en une seule fois, les signaux de maintien de connexion peuvent ne pas être envoyés à Fuxi dans les temps.

Si vous rencontrez cette erreur, vérifiez d'abord si votre UDF présente des problèmes de performances, par exemple si le traitement de chaque enregistrement prend plusieurs secondes. Si vous ne pouvez pas optimiser les performances de l'UDF, essayez de résoudre le problème en définissant manuellement la taille du lot de lignes. La valeur par défaut est 1024.

set odps.sql.executionengine.batch.rowcount=16;

divide.nan.or.overflow

Problème lié à l'absence de pliage de constantes pour la division dans les versions antérieures de MaxCompute.

Par exemple, pour l'instruction suivante, le plan d'exécution physique dans les versions antérieures de MaxCompute est le suivant :

explain
select if(false, 0/0, 1.0)
from table_name;
in task M1_Stg1:
    Data source: meta_dev.table_name
    TS: alias: table_name
      SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
        FS: output: None

Comme vous pouvez le constater, les fonctions IF et Divide sont conservées. Au moment de l'exécution, l'expression Divide du deuxième paramètre n'est pas évaluée car le premier paramètre de IF est faux. Par conséquent, aucune exception de division par zéro ne se produit.

Cependant, MaxCompute V2.0 prend en charge le pliage de constantes pour la division et signale une erreur, comme illustré dans l'exemple suivant :

Syntaxe incorrecte :

select IF(FALSE, 0/0, 1.0)
from table_name;

Message d'erreur :

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result NaN, two params are 0.000000 and 0.000000

Outre l'erreur précédente, vous pouvez également rencontrer une erreur de dépassement de capacité. Par exemple :

Syntaxe incorrecte :

select if(false, 1/0, 1.0)
from table_name;

Message d'erreur :

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result overflow, two params are 1.000000 and 0.000000

Procédez comme suit pour corriger le problème :

Supprimez l'utilisation de /0 et remplacez-la par une constante valide.

Le pliage de constantes CASE WHEN présente un problème similaire. Par exemple, dans CASE WHEN TRUE THEN 0 ELSE 0/0, MaxCompute V2.0 évalue toutes les sous-expressions lors du pliage de constantes. Cela provoque une erreur de division par zéro.

CASE WHEN peut impliquer des scénarios d'optimisation plus complexes. Par exemple :

select case when key = 0 then 0 else 1/key end
from (
select 0 as key from src
union all
select key from src) r;

L'optimiseur pousse l'opération de division vers le bas dans la sous-requête. La transformation est similaire à la suivante :

M (
select case when 0 = 0 then 0 else 1/0 end c1 from src
UNION ALL
select case when key = 0 then 0 else 1/key end c1 from src) r;

Message d'erreur :

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.ArithmeticException: DIVIDE func result overflow, two params are 1.000000 and 0.000000

Dans ce cas, le pliage de constantes pour la première clause de UNION ALL signale une erreur. Pour résoudre ce problème, déplacez la clause CASE WHEN de l'instruction SQL dans la sous-requête, supprimez la clause CASE WHEN inutile et éliminez l'utilisation de /0 :

select c1 end
from (
select 0 c1 end from src
union all
select case when key = 0 then 0 else 1/key end) r;

small.table.exceeds.mem.limit

Les versions antérieures de MaxCompute prennent en charge l'optimisation Multi-way Join. Plusieurs jointures utilisant la même clé de jointure sont fusionnées en une seule tâche Fuxi. Un exemple est J4_1_2_3_Stg1 dans la requête suivante :

explain
select t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

Plan d'exécution physique dans les versions antérieures de MaxCompute :

In Job job0:
root Tasks: M1_Stg1, M2_Stg1, M3_Stg1
J4_1_2_3_Stg1 depends on: M1_Stg1, M2_Stg1, M3_Stg1

In Task M1_Stg1:
    Data source: meta_dev.t1

In Task M2_Stg1:
    Data source: meta_dev.t2

In Task M3_Stg1:
    Data source: meta_dev.t3

In Task J4_1_2_3_Stg1:
    JOIN: t1 INNER JOIN unknown INNER JOIN unknown
        SEL: t1._col0, t1._col1, t1._col2
            FS: output: None

Si vous ajoutez une indication MapJoin, le plan d'exécution physique dans les versions antérieures de MaxCompute ne change pas. Cela indique que les versions antérieures de MaxCompute privilégient l'optimisation Multi-way Join et peuvent ignorer les indications MapJoin spécifiées par l'utilisateur.

explain
select /* +mapjoin(t1) */ t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

Le plan d'exécution physique dans les versions antérieures de MaxCompute est identique au plan précédent.

L'optimiseur de MaxCompute V2.0 privilégie les indications MapJoin spécifiées par l'utilisateur. Pour l'exemple précédent, si t1 est volumineuse, vous pouvez rencontrer une erreur similaire à la suivante :

FAILED: ODPS-0010000:System internal error - SQL Runtime Internal Error: Hash Join Cursor HashJoin_REL… small table exceeds, memory limit(MB) 640, fixed memory used …, variable memory used …

Dans ce cas, si le comportement MapJoin ne correspond pas à vos attentes, vous pouvez supprimer l'indication MapJoin.

sigkill.oom

De manière similaire au problème small.table.exceeds.mem.limit, si vous spécifiez une indication MapJoin et que la petite table indiquée est volumineuse, la requête peut aboutir dans les versions antérieures de MaxCompute car elle est optimisée en tant que Multi-way Join. Dans MaxCompute V2.0, vous pouvez définir odps.sql.mapjoin.memory.max pour éviter les erreurs dues au dépassement de la limite de mémoire par la petite table. Cependant, chaque worker MaxCompute dispose d'une limite de mémoire fixe. Si la petite table est trop volumineuse, le worker MaxCompute est arrêté en raison d'une erreur de mémoire insuffisante (OOM). L'erreur est similaire à la suivante :

Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(outof memory).

Dans ce cas, vous pouvez supprimer l'indication MapJoin et utiliser un Multi-way Join.

wm_concat.first.argument.const

La description de WM_CONCAT dans Fonctions d'agrégation a toujours exigé que le premier paramètre de WM_CONCAT soit une constante. Les versions antérieures de MaxCompute n'étaient pas strictes sur cette vérification. Par exemple, si la table source ne contenait aucune donnée, aucune erreur n'était signalée même si le premier paramètre de WM_CONCAT était une ColumnReference.

Function declaration:
string wm_concat(string separator, string str)
Parameter description:
separator: A constant of the String type. This is the separator. Other data types or non-constants will cause an exception.

MaxCompute V2.0 vérifie la validité des paramètres lors de la phase de planification. Si le premier paramètre de WM_CONCAT n'est pas une constante, une erreur est immédiatement signalée. Un exemple est présenté ci-dessous :

Syntaxe incorrecte :

select wm_concat(value, ',') FROM src group by value;

Message d'erreur :

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.validator.AggregateCallValidator$AggregateCallValidationException: Invalid argument type - The first argument of WM_CONCAT must be constant string.

pt.implicit.convertion.failed

srcpt est une table partitionnée comportant deux partitions :

create table srcpt(key STRING, value STRING) partitioned by (pt STRING);
alter table srcpt add partition (pt='pt1');
alter table srcpt add partition (pt='pt2');

Pour l'instruction SQL précédente, la colonne pt de type String et les constantes de type INT sont toutes deux converties en type DOUBLE pour la comparaison. Même si le projet est configuré avec odps.sql.udf.strict.mode=true, les versions antérieures de MaxCompute ne signalent pas d'erreur. Toutes les valeurs pt sont filtrées. MaxCompute V2.0 signale une erreur. Un exemple est présenté ci-dessous :

Syntaxe incorrecte :

select key from srcpt where pt in (1, 2);

Message d'erreur :

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.NumberFormatException: ODPS-0123091:Illegal type cast - In function cast, value 'pt1' cannot be casted from String to Double.

Évitez de comparer une colonne de clé de partition STRING avec des constantes de type INT. Vous devez convertir les constantes de type INT en type STRING.

having.use.select.alias

La spécification SQL définit que les clauses GROUP BY et HAVING sont traitées avant la clause SELECT. Par conséquent, la clause HAVING ne peut pas utiliser un alias de colonne généré par la clause SELECT.

dynamic.pt.to.static

Description : Dans MaxCompute V2.0, les partitions dynamiques sont parfois converties en partitions statiques par l'optimiseur.

insert overwrite table srcpt partition(pt) select id, 'pt1' from table_name;

est converti en

insert overwrite table srcpt partition(pt='pt1') select id from table_name;

Si vous spécifiez une valeur de partition non valide, telle qu'une utilisation incorrecte de '${bizdate}', MaxCompute V2.0 signale une erreur lors de la phase de vérification syntaxique. Pour plus d'informations, consultez Partitions.

Syntaxe incorrecte :

insert overwrite table srcpt partition(pt) select id, '${bizdate}' from table_name limit 0;

Message d'erreur :

FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 2 in data source, requires 3 columns (includes dynamic partitions if any)

Dans les versions antérieures de MaxCompute, l'instruction SQL ne produit aucune donnée en raison de la clause LIMIT 0 et aucune partition dynamique n'est créée. Par conséquent, aucune erreur n'est signalée.

lot.not.in.subquery

Description : Ce problème est lié à la gestion des valeurs NULL dans une sous-requête IN.

Dans les opérations IN SQL standard, si la liste de valeurs contient NULL, la valeur de retour n'est pas false. La valeur de retour ne peut être que NULL ou true. Par exemple, 1 in (null, 1, 2, 3) renvoie true, 1 in (null, 2, 3) renvoie NULL et null in (null, 1, 2, 3) renvoie NULL. De même, pour une opération NOT IN, si la liste contient NULL, la valeur de retour est uniquement false ou NULL, et jamais true.

MaxCompute V2.0 gère ce problème conformément au comportement SQL standard. Si vous recevez cet avertissement, vérifiez vos requêtes pour déterminer si la sous-requête dans l'opération IN peut renvoyer des valeurs nulles. Vérifiez si le comportement correspond à vos attentes lorsque des valeurs nulles se produisent. Si ce n'est pas le cas, effectuez les modifications nécessaires.