Appel d’une UDF¶
Vous pouvez appeler une fonction définie par l’utilisateur (UDF) ou une fonction de table définie par l’utilisateur (UDTF) de la même manière que vous appelez d’autres fonctions.
Appel d’une UDF¶
En général, vous appelez une UDF de la même manière que vous appelez d’autres fonctions.
Si une UDF a des arguments, vous pouvez spécifier ces arguments par leur nom ou leur position.
Par exemple, l’UDF suivante accepte trois arguments :
CREATE OR REPLACE FUNCTION udf_concatenate_strings(
first_arg VARCHAR,
second_arg VARCHAR,
third_arg VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
SELECT first_arg || second_arg || third_arg
$$;
Lorsque vous appelez une UDF, vous pouvez spécifier les arguments par leur nom :
SELECT udf_concatenate_strings(
first_arg => 'one',
second_arg => 'two',
third_arg => 'three');
+--------------------------+
| UDF_CONCATENATE_STRINGS( |
| FIRST_ARG => 'ONE', |
| SECOND_ARG => 'TWO', |
| THIRD_ARG => 'THREE') |
|--------------------------|
| onetwothree |
+--------------------------+
Si vous spécifiez les arguments par leur nom, il n’est pas nécessaire de les spécifier dans un ordre particulier :
SELECT udf_concatenate_strings(
third_arg => 'three',
first_arg => 'one',
second_arg => 'two');
+--------------------------+
| UDF_CONCATENATE_STRINGS( |
| THIRD_ARG => 'THREE', |
| FIRST_ARG => 'ONE', |
| SECOND_ARG => 'TWO') |
|--------------------------|
| onetwothree |
+--------------------------+
Vous pouvez également spécifier les arguments par position :
SELECT udf_concatenate_strings(
'one',
'two',
'three');
+--------------------------+
| UDF_CONCATENATE_STRINGS( |
| 'ONE', |
| 'TWO', |
| 'THREE') |
|--------------------------|
| onetwothree |
+--------------------------+
Remarques :
Vous devez spécifier tous les arguments soit par leur nom, soit par leur position. Vous ne pouvez pas spécifier certains arguments par leur nom et d’autres par leur position.
Lorsque vous spécifiez un argument par son nom, vous ne pouvez pas utiliser de guillemets doubles autour du nom de l’argument.
Si deux fonctions ou deux procédures ont le même nom, mais des types d’arguments différents, vous pouvez utiliser les noms des arguments pour spécifier la fonction ou la procédure à exécuter, si les noms des arguments sont différents. Reportez-vous à Surcharge de procédures et de fonctions.
Appeler un UDF qui a des arguments optionnels¶
Si l’UDF comporte des arguments facultatifs, vous pouvez omettre les arguments facultatifs dans l’appel. Chaque argument facultatif a une valeur par défaut qui est utilisée lorsque l’argument est omis.
Par exemple, l’UDF suivant a un argument obligatoire et deux arguments facultatifs. Chaque argument facultatif a une valeur par défaut.
CREATE OR REPLACE FUNCTION build_string_udf(
word VARCHAR,
prefix VARCHAR DEFAULT 'pre-',
suffix VARCHAR DEFAULT '-post'
)
RETURNS VARCHAR
AS
$$
SELECT prefix || word || suffix
$$
;
Vous pouvez omettre tout argument facultatif dans l’appel. Lorsque vous omettez un argument, la valeur par défaut de l’argument est utilisée.
SELECT build_string_udf('hello');
+---------------------------+
| BUILD_STRING_UDF('HELLO') |
|---------------------------|
| pre-hello-post |
+---------------------------+
SELECT build_string_udf('hello', 'before-');
+--------------------------------------+
| BUILD_STRING_UDF('HELLO', 'BEFORE-') |
|--------------------------------------|
| before-hello-post |
+--------------------------------------+
Si vous devez omettre un argument facultatif et spécifier un autre argument facultatif qui apparaît après l’argument omis dans la signature, utilisez des arguments nommés plutôt que des arguments positionnels.
Par exemple, supposons que vous souhaitiez omettre l’argument prefix et spécifier l’argument suffix. L’argument suffix apparaît après l’argument prefix dans la signature, vous devez donc spécifier les arguments par leur nom :
SELECT build_string_udf(word => 'hello', suffix => '-after');
+-------------------------------------------------------+
| BUILD_STRING_UDF(WORD => 'HELLO', SUFFIX => '-AFTER') |
|-------------------------------------------------------|
| pre-hello-after |
+-------------------------------------------------------+
Appel d’une UDTF¶
Appelez une UDTF comme vous le feriez pour n’importe quelle fonction de table. Lorsque vous appelez une UDTF dans la clause FROM d’une requête, spécifiez le nom et les arguments de l’UDTF à l’intérieur des parenthèses qui suivent le mot-clé TABLE, comme vous le feriez lors de l’appel d’une fonction de table intégrée.
En d’autres termes, utilisez une forme telle que la suivante pour le mot-clé TABLE lorsque vous appelez une UDTF :
SELECT ...
FROM TABLE ( udtf_name (udtf_arguments) )
Le code de l’exemple suivant appelle la fonction de table my_java_udtf, en spécifiant un littéral DATE dans l’argument '2021-01-16'::DATE.
SELECT ...
FROM TABLE(my_java_udtf('2021-01-16'::DATE));
L’argument d’une fonction de table peut être une expression et pas seulement un littéral. Par exemple, une fonction de table peut être appelée en utilisant une colonne d’une table. Vous trouverez quelques exemples ci-dessous, notamment dans la section Exemples.
Comme dans le cas avec l’appel d’UDFs, vous pouvez spécifier les arguments par nom ou par position.
Pour plus d’informations sur les fonctions de table en général, voir fonction de table.
Note
Vous ne pouvez pas appeler un UDF dans la clause DEFAULT d’une instruction CREATE TABLE.
Utilisation d’une table ou d’une UDTF comme entrée d’une UDTF¶
L’entrée d’une fonction de table peut provenir d’une table ou d’une autre UDTF, comme documenté dans Utilisation d’une table comme entrée d’une fonction de table.
L’exemple ci-dessous montre comment utiliser une table pour fournir des entrées à l’UDTF split_file_into_words :
create table file_names (file_name varchar);
insert into file_names (file_name) values ('sample.txt'),
('sample_2.txt');
select f.file_name, w.word
from file_names as f, table(split_file_into_words(f.file_name)) as w;
La sortie devrait ressembler à ce qui suit :
+-------------------+------------+
| FILE_NAME | WORD |
+-------------------+------------+
| sample_data.txt | some |
| sample_data.txt | words |
| sample_data_2.txt | additional |
| sample_data_2.txt | words |
+-------------------+------------+
La clause IMPORTS de l’UDTF doit spécifier le nom et le chemin d’accès de chaque fichier transmis à l’UDTF. Par exemple :
create function split_file_into_words(inputFileName string)
...
imports = ('@inline_jars/sample.txt', '@inline_jars/sample_2.txt')
...
Chaque fichier doit déjà avoir été copié vers une zone de préparation (dans ce cas, la zone de préparation nommée @inline_jars) avant que l’UDTF ne lise le fichier.
Pour un exemple d’utilisation d’une UDTF comme entrée d’une autre UDTF, voir Exemples étendus utilisant les valeurs des tables et d’autres UDTFs comme entrées dans la documentation UDTF JavaScript.
Fonctions et partitions des tables¶
Avant que les lignes ne soient transmises aux fonctions de table, les lignes peuvent être regroupées en partitions. Le partitionnement présente deux avantages principaux :
Il permet à Snowflake de diviser la charge de travail pour améliorer la parallélisation et donc les performances.
Le partitionnement permet à Snowflake de traiter toutes les lignes ayant une caractéristique commune comme un groupe. Vous pouvez renvoyer des résultats basés sur toutes les lignes d’un groupe, et pas seulement sur des lignes individuelles.
Par exemple, vous pouvez partitionner les données sur les cours des actions dans un groupe par action. Tous les prix des actions de chaque entreprise peuvent être analysés ensemble, tandis que les prix des actions de chaque entreprise peuvent être analysés indépendamment de toute autre entreprise.
Les données peuvent être partitionnées de manière explicite ou implicite.
Partitionnement explicite¶
Partitionnement explicite en plusieurs groupes
L’instruction suivante appelle les UDTF nommées my_udtf sur des partitions individuelles. Chaque partition contient toutes les lignes pour lesquelles l’expression PARTITION BY donne la même valeur (par exemple, la même entreprise ou le même symbole boursier).
SELECT *
FROM stocks_table AS st,
TABLE(my_udtf(st.symbol, st.transaction_date, st.price) OVER (PARTITION BY st.symbol))
Partitionnement explicite en un seul groupe
L’instruction suivante appelle les UDTF nommées my_udtf sur une seule partition. La clause PARTITION BY <constant> (dans ce cas PARTITION BY 1) place toutes les lignes dans la même partition.
SELECT *
FROM stocks_table AS st,
TABLE(my_udtf(st.symbol, st.transaction_date, st.price) OVER (PARTITION BY 1))
Pour un exemple plus complet et réaliste, voir Exemples d’appels d’UDTFs Java dans des requêtes, en particulier la sous-section intitulée Partition unique.
Tri des lignes pour les partitions
Pour traiter les lignes de chaque partition dans un ordre précis, incluez une clause ORDER BY. Cela indique à Snowflake de transmettre les lignes à la méthode de gestion par ligne dans l’ordre spécifié.
Par exemple, si vous souhaitez calculer la moyenne mobile du cours d’une action dans le temps, vous devez ordonner les cours de l’action par horodatage (ainsi que partitionner par symbole boursier). L’exemple suivant montre comment procéder :
SELECT *
FROM stocks_table AS st,
TABLE(my_udtf(st.symbol, st.transaction_date, st.price) OVER (PARTITION BY st.symbol ORDER BY st.transaction_date))
Une clause OVER peut contenir une clause ORDER BY même sans clause PARTITION BY.
N’oubliez pas qu’inclure une clause ORDER BY à l’intérieur d’une clause OVER n’équivaut pas à placer une clause ORDER BY au niveau le plus externe de la requête. Si vous souhaitez que l’ensemble des résultats de la requête soit ordonné, vous devez utiliser une clause ORDER BY distincte. Par exemple :
SELECT *
FROM stocks_table AS st,
TABLE(my_udtf(st.symbol, st.transaction_date, st.price) OVER (PARTITION BY st.symbol ORDER BY st.transaction_date))
ORDER BY st.symbol, st.transaction_date, st.transaction_time;
Notes sur l’utilisation du partitionnement explicite
Lorsqu’on utilise une UDTF avec une clause PARTITION BY , la clause PARTITION BY ne peut contenir qu’une référence de colonne ou un litéral, et non une expression générale. Par exemple, la commande suivante n’est pas autorisée :
SELECT * FROM udtf_table, TABLE(my_func(col1) OVER (PARTITION BY udtf_table.col2 * 2)); -- NO!
Partitionnement implicite¶
Si une fonction de table ne partitionne pas explicitement les lignes en utilisant une clause PARTITION BY, alors Snowflake partitionne généralement les lignes de manière implicite afin d’utiliser le traitement parallèle pour améliorer les performances.
Le nombre de partitions est généralement basé sur des facteurs tels que la taille de l’entrepôt qui traite la fonction et la cardinalité de la relation d’entrée. Les lignes sont généralement affectées à des partitions spécifiques sur la base de facteurs tels que l’emplacement physique des lignes (par exemple, par micropartition), de sorte que le regroupement de partition n’a aucune signification.