MaxCompute:FAQ about built-in functions

Comment convertir une date telle que 2010/1/3 au format 2010-01-03 ?

Pour une entrée comportant des zéros non significatifs (par exemple, 2010/01/03), combinez les fonctions TO_DATE et TO_CHAR :

SELECT TO_CHAR(TO_DATE('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd');
-- Returns: 2010-01-03

Pour une entrée sans zéros non significatifs (par exemple, 2010/1/3), les fonctions intégrées ne peuvent pas l'analyser directement. Créez une fonction définie par l'utilisateur (UDF) pour gérer les parties de date de longueur variable. Cette rubrique décrit les types, les scénarios, le processus de développement et les notes d'utilisation des UDF prises en charge dans MaxCompute.

Pour plus d'informations, consultez les rubriques TO_DATE et TO_CHAR.

Comment convertir un horodatage UNIX en valeur DATETIME ?

Utilisez la fonction FROM_UNIXTIME :

SELECT FROM_UNIXTIME(1609459200);
-- Returns: 2021-01-01 08:00:00

Pour plus d'informations, consultez la rubrique FROM_UNIXTIME.

Comment obtenir l'heure système actuelle ?

Utilisez la fonction GETDATE :

SELECT GETDATE();
-- Returns the current date and time, e.g., 2026-02-15 10:30:00

Pour plus d'informations, consultez la rubrique GETDATE.

Pourquoi l'erreur « cannot be resolved » s'affiche-t-elle avec YEAR, QUARTER, MONTH ou DAY ?

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function or view 'year' cannot be resolved

Les fonctions YEAR, QUARTER, MONTH et DAY sont des fonctions d'extension introduites dans MaxCompute V2.0. Elles nécessitent l'édition de type de données V2.0.

Ajoutez le paramètre suivant avant votre instruction SQL :

SET odps.sql.type.system.odps2 = true;
SELECT YEAR(GETDATE());

Pourquoi TO_DATE échoue-t-il avec une erreur « missing minute part » ?

FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn't have minute part : yyyy-MM-dd HH:mm:ss

Dans MaxCompute, mm et MM représentent tous deux le mois. Utilisez mi pour les minutes :

-- Incorrect
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss');

-- Correct
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss');

Cette syntaxe diffère de celle de nombreuses autres plateformes SQL où mm représente les minutes. Dans MaxCompute, utilisez toujours mi pour la composante minute.

Pourquoi ROUND(4.515, 2) renvoie-t-il 4.51 au lieu de 4.52 ?

Les valeurs DOUBLE sont des nombres à virgule flottante sur 8 octets dont la précision est limitée. La valeur 4.515 est stockée en interne sous la forme approximative 4.514999999.... Par conséquent, l'arrondi à deux décimales produit 4.51 :

SELECT ROUND(4.515, 2), ROUND(125.315, 2);
-- Returns: 4.51, 125.32

Pour un arrondi exact, utilisez le type DECIMAL :

SELECT ROUND(4.515BD, 2);
-- Returns: 4.52

Le type DECIMAL stocke les valeurs avec une précision exacte, évitant ainsi les problèmes de représentation en virgule flottante.

Comment générer une séquence d'incrémentation automatique ?

Utilisez la fonction de fenêtre ROW_NUMBER :

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
FROM my_table;

Pour plus d'informations, consultez la rubrique ROW_NUMBER.

Comment concaténer les valeurs d'une colonne ?

Utilisez la fonction WM_CONCAT :

SELECT WM_CONCAT(',', name) AS all_names
FROM my_table;
-- Returns: Alice,Bob,Charlie

Pour plus d'informations, consultez la rubrique WM_CONCAT.

MaxCompute prend-il en charge MD5 ?

Oui. La fonction MD5 calcule le hachage d'une chaîne :

SELECT MD5('hello');
-- Returns: 5d41402abc4b2a76b9719d911017c592

Pour plus d'informations, consultez la rubrique MD5.

Comment ajouter des zéros à gauche d'une chaîne ?

Utilisez la fonction LPAD :

SELECT LPAD('42', 6, '0');
-- Returns: 000042

Pour plus d'informations, consultez la rubrique LPAD.

MaxCompute prend-il en charge SUBSTRING_INDEX ?

Oui. La fonction SUBSTRING_INDEX fonctionne de la même manière que dans MySQL :

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);
-- Returns: www.example

Pour plus d'informations, consultez la rubrique SUBSTRING_INDEX.

REGEXP_COUNT prend-il en charge les requêtes imbriquées dans le paramètre de modèle ?

Non. Le paramètre pattern de la fonction REGEXP_COUNT ne prend pas en charge les instructions de requête imbriquées.

Pour plus d'informations, consultez la rubrique REGEXP_COUNT.

MaxCompute prend-il en charge le formatage numérique TO_CHAR d'Oracle ?

MaxCompute ne prend pas en charge la syntaxe Oracle TO_CHAR(Data, FM9999.00). Utilisez plutôt la fonction FORMAT_NUMBER :

SELECT FORMAT_NUMBER(12332.123456, '#,###,###,###.###');
-- Returns: 12,332.123

Pour plus d'informations, consultez la rubrique FORMAT_NUMBER.

Comment agréger des champs JSON correspondant à une condition ?

Filtrez les enregistrements à l'aide de conditions SQL telles que LIKE, puis utilisez les fonctions ARRAY ou MAP pour construire un type complexe à partir des résultats. Convertissez le résultat en chaîne JSON avec la fonction TO_JSON :

SELECT TO_JSON(ARRAY(col1, col2))
FROM my_table
WHERE col1 LIKE '%keyword%';

Comment extraire les clés JSON sous forme de colonnes distinctes ?

Utilisez la fonction GET_JSON_OBJECT :

SELECT
  GET_JSON_OBJECT(json_col, '$.name') AS name,
  GET_JSON_OBJECT(json_col, '$.age') AS age
FROM my_table;

Pour plus d'informations, consultez la rubrique GET_JSON_OBJECT.

Comment convertir une chaîne JSON en tableau ?

Utilisez la fonction FROM_JSON en spécifiant le type cible sous forme de chaîne :

SELECT FROM_JSON(json_col, 'array<bigint>');
-- Converts a JSON array string like "[1, 2, 3]" to a MaxCompute ARRAY<BIGINT>

Pour plus d'informations, consultez la rubrique FROM_JSON.

MaxCompute ne prend pas en charge IFNULL. Quelle fonction utiliser à la place ?

MaxCompute ne dispose pas de la fonction IFNULL. Si vous tentez de l'utiliser, le message d'erreur suivant s'affiche :

Semantic analysis exception - Invalid function : line 1:41 'ifnull'

Utilisez l'une des alternatives suivantes :

Dans la plupart des cas, la fonction NVL constitue le remplacement le plus simple :

-- MySQL
SELECT IFNULL(col, 0) FROM my_table;

-- MaxCompute equivalent
SELECT NVL(col, 0) FROM my_table;

Pour plus d'informations, consultez les rubriques NVL, COALESCE, Expression CASE WHEN et correspondances entre les fonctions intégrées de MaxCompute, Hive, MySQL et Oracle.

Comment diviser une ligne en plusieurs lignes ?

Utilisez la fonction TRANS_COLS. La sortie comprend une colonne d'index suivie des colonnes de données transposées. Le nombre d'alias doit donc être égal à 1 + le nombre de colonnes de données :

SELECT TRANS_COLS(2, col1, col2) AS (idx, key, value)
FROM my_table;

Pour plus d'informations, consultez la rubrique TRANS_COLS.

Pourquoi l'erreur « Expression not in GROUP BY key » s'affiche-t-elle lors de l'utilisation de COALESCE ?

FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"

Cette erreur se produit lorsqu'une expression non agrégée à l'intérieur de la fonction COALESCE fait référence à une colonne qui ne figure pas dans la clause GROUP BY.

Dans l'exemple suivant, la requête échoue car get_json_object(extended_x, '$.table') à l'intérieur de la fonction decode n'est ni agrégée ni incluse dans la clause GROUP BY :

SELECT
  md5(concat(aid, bid)) AS id,
  aid,
  bid,
  sum(amountdue) AS amountdue,
  coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) AS tableparty
FROM e_orders
WHERE pt = '20170425'
GROUP BY aid, bid;

L'expression decode(get_json_object(extended_x, '$.table'), null, 0, 1) opère sur des lignes individuelles mais apparaît en dehors de toute fonction d'agrégation. Vous devez soit l'encapsuler dans une fonction d'agrégation, soit l'inclure dans la clause GROUP BY.

Pourquoi des erreurs de conversion de type implicite surviennent-elles après l'activation de MaxCompute V2.0 ?

Lorsque l'édition de type de données MaxCompute V2.0 est activée (odps.sql.type.system.odps2=true), les conversions de type implicites suivantes sont désactivées :

Ces conversions pouvant entraîner une perte de précision, V2.0 les bloque par défaut. Pour résoudre ce problème :

• Conversion explicite (recommandée) : Utilisez la fonction CAST pour convertir explicitement les types. Pour plus d'informations, consultez la rubrique CAST.

    SELECT CAST(string_col AS BIGINT) FROM my_table;

• Désactivation des types de données V2.0 : Définissez odps.sql.type.system.odps2=false pour réactiver les conversions implicites. Notez que ce paramètre peut également désactiver les fonctions d'extension V2.0 telles que YEAR, QUARTER, MONTH et DAY, selon la configuration au niveau du projet.