MaxCompute:UDF SQL (fonctions définies par l'utilisateur)

Concepts clés

Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

• Un environnement exécuté en mode script SQL. Pour plus de détails, consultez la rubrique SQL en mode script.

• La confirmation que les types de données des paramètres d'entrée prévus sont pris en charge par MaxCompute. Pour obtenir la liste complète, consultez la rubrique Édition des types de données MaxCompute V2.0.

• Les autorisations au niveau des fonctions requises sur votre compte Alibaba Cloud. Pour plus de détails, consultez la rubrique Autorisations MaxCompute.

Créer une UDF SQL permanente

Les UDF SQL permanentes sont stockées dans le système de métadonnées MaxCompute. Une fois créées, elles apparaissent dans la liste des fonctions et peuvent être appelées à tout moment.

Syntaxe

CREATE SQL FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

Paramètres

Exemples

Fonction simple — ajoute 1 à une entrée BIGINT :

CREATE SQL FUNCTION my_add(@a BIGINT) AS @a + 1;

Fonction multi-instructions utilisant BEGIN / END :

CREATE SQL FUNCTION my_sum(@a BIGINT, @b BIGINT, @c BIGINT) RETURNS @my_sum BIGINT
AS BEGIN
    @temp := @a + @b;
    @my_sum := @temp + @c;
END;

Créer une UDF SQL temporaire

Les UDF SQL temporaires ne sont pas stockées dans MaxCompute. Elles existent uniquement au sein du script SQL où elles sont définies et ne peuvent pas être appelées dans d'autres sessions ou scripts.

Syntaxe

FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

Les paramètres sont identiques à ceux de CREATE SQL FUNCTION. Omettez CREATE SQL pour créer une UDF temporaire.

Exemple

FUNCTION my_add(@a BIGINT) AS @a + 1;

Interroger une UDF SQL

Seules les UDF SQL permanentes peuvent être interrogées ; les UDF temporaires ne sont pas stockées dans MaxCompute.

Pour exécuter DESC FUNCTION depuis le client MaxCompute (odpscmd), mettez à niveau le client vers la version 0.34.0 ou ultérieure. Pour les instructions d'installation et de mise à niveau, consultez la rubrique Client MaxCompute (odpscmd) .

Syntaxe

DESC FUNCTION <function_name>;

Exemple

DESC FUNCTION my_add;

Résultat :

Name                  my_add
Owner                 ALIYUN$s***_****@**.aliyunid.com
Created Time          2021-05-08 11:26:02
SQL Definition Text   CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1

Appeler une UDF SQL

Appelez une UDF SQL de la même manière qu'une fonction intégrée.

• Les UDF SQL permanentes peuvent être appelées à tout moment.

• Les UDF SQL temporaires ne peuvent être appelées qu'au sein du script où elles sont définies.

Les types de données des arguments passés doivent correspondre aux types de données définis dans l'UDF.

Syntaxe

SELECT <function_name>(<column_name>[, ...]) FROM <table_name>;

Exemple

-- Create a table and insert sample data.
CREATE TABLE src (c BIGINT, d STRING);
INSERT INTO TABLE src VALUES (1, '100.1'), (2, '100.2'), (3, '100.3');

-- Call my_add on column c.
SELECT my_add(c) FROM src;

Résultat :

+------------+
| _c0        |
+------------+
| 2          |
| 3          |
| 4          |
+------------+

Supprimer une UDF SQL

Syntaxe

DROP FUNCTION <function_name>;

Exemple

DROP FUNCTION my_add;

Passer une fonction en tant que paramètre

Les UDF SQL prennent en charge les paramètres de type fonction. Lorsque vous déclarez un paramètre comme FUNCTION (<input_type>) RETURNS <output_type>, l'appelant peut passer n'importe quelle fonction compatible : une fonction intégrée, d'autres UDF (Java, Python ou SQL) ou une fonction anonyme.

Exemple

-- Define a SQL UDF.
FUNCTION add(@a BIGINT) AS @a + 1;

-- Define a higher-order UDF that accepts a function as an argument.
FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Call op, passing different functions as the second argument.
-- add is a SQL UDF; abs is a MaxCompute built-in function.
SELECT op(key, add), op(key, abs) FROM VALUES (1), (2) AS t(key);

Résultat :

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

_c0 est le résultat de add(key) (ajoute 1). _c1 est le résultat de abs(key) (valeur absolue). Pour plus de détails sur la fonction ABS, consultez la rubrique Fonctions mathématiques.

Pour connaître les précautions à prendre lors de l'utilisation des expressions Lambda dans MaxCompute, consultez la rubrique Fonctions Lambda .

Utiliser des fonctions anonymes

Lors de l'appel d'une UDF avec un paramètre de type fonction, passez une fonction anonyme inline plutôt qu'une fonction nommée. Le compilateur déduit les types de paramètres de la fonction anonyme à partir de la signature de l'UDF.

Exemple

FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- Pass an anonymous function as the second argument.
SELECT op(key, FUNCTION (@a) AS @a + 1) FROM VALUES (1), (2) AS t(key);

FUNCTION (@a) AS @a + 1 est la fonction anonyme. Son type de paramètre est déduit de la signature de op ; aucune déclaration de type explicite n'est nécessaire.

Exemple : simplifier la logique SQL répétitive

Scénario : convertir des chaînes de date au format yyyy-mm-dd vers yyyymmdd. Les dates d'entrée sont 2020-11-21, 2020-1-01, 2019-5-1 et 19-12-1.

Utilisation d'une UDF SQL (recommandé)

Définissez la logique de conversion une seule fois, puis appelez la fonction partout où cela est nécessaire :

CREATE SQL FUNCTION y_m_d2yyyymmdd(@y_m_d STRING) RETURNS @yyyymmdd STRING
AS BEGIN
    @yyyymmdd := CONCAT(
        LPAD(SPLIT_PART(@y_m_d, '-', 1), 4, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 2), 2, '0'),
        LPAD(SPLIT_PART(@y_m_d, '-', 3), 2, '0')
    );
END;

SELECT y_m_d2yyyymmdd(d) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Résultat :

+------------+
| _c0        |
+------------+
| 20201121   |
| 20200101   |
| 20190501   |
| 00191201   |
+------------+

Sans UDF SQL

Intégrez l'expression complète à chaque site d'appel ; cette approche est plus difficile à maintenir et sujette aux erreurs lorsque la logique doit être modifiée :

SELECT CONCAT(
    LPAD(SPLIT_PART(d, '-', 1), 4, '0'),
    LPAD(SPLIT_PART(d, '-', 2), 2, '0'),
    LPAD(SPLIT_PART(d, '-', 3), 2, '0')
) FROM VALUES ('2020-11-21'), ('2020-1-01'), ('2019-5-1'), ('19-12-1') AS t(d);

Limites

Étapes suivantes

• SQL en mode script

• Édition des types de données MaxCompute V2.0

• Fonctions Lambda

• Fonctions mathématiques

• LIST FUNCTIONS