Fonctions de table définies par l’utilisateur (UDTFs JavaScript)¶
Semblable aux UDTFs SQL, un UDTF JavaScript renvoie un ensemble de lignes, et est accessible dans la clause FROM d’une requête.
Dans ce chapitre :
Syntaxe¶
Un UDTF JavaScript est défini en utilisant la même syntaxe qu’un UDTF SQL standard, mais avec l’ajout du mot clé LANGUAGE. De plus, au lieu d’un bloc SQL dans la définition de la fonction, un code JavaScript est transmis :
CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_columns> )
LANGUAGE JAVASCRIPT
AS '<javascript_code>';
La section suivante fournit des détails pour écrire le code_javascript
qui comprend le corps principal (ou « définition ») de l’UDTF.
Pour une description plus détaillée de la syntaxe générale pour tous les UDFs, y compris les UDTFs JavaScript, voir CREATE FUNCTION.
Notes sur l’utilisation¶
Lorsqu’on utilise un UDTF JavaScript avec une clause
PARTITION BY
, la clausePARTITION BY
ne peut contenir qu’une référence de colonne, 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 col2 * 2));
Écrire des UDTFs JavaScript¶
Exigences en matière de code¶
Le code JavaScript doit répondre aux exigences suivantes pour que l’UDTF soit valide :
Le code doit définir un seul objet JavaScript littéral.
L’objet défini doit inclure une fonction de rappel appelée
processRow
. Pour plus d’informations, reportez-vous à la section suivante.
Important
Si le code JavaScript ne répond pas à ces exigences, l’UDTF sera quand même créé ; cependant, il échouera lorsqu’il sera appelé dans une requête.
Fonctions de rappel d’objets¶
Grâce au code JavaScript, Snowflake interagit avec l’UDTF en appelant diverses fonctions de rappel pendant l’exécution de la requête. Le squelette suivant décrit tous les rappels disponibles et leur signature attendue :
{ processRow: function (row, rowWriter, context) {/*...*/}, finalize: function (rowWriter, context) {/*...*/}, initialize: function (argumentInfo, context) {/*...*/}, }
Notez que seul le rappel processRow
est requis ; les autres sont facultatifs.
processRow
¶
Cette fonction de rappel est appelée une fois pour chaque ligne de la relation d’entrée. Les arguments de l’UDTF sont transmis dans l’objet row
. Pour chacun des arguments définis dans le DDL CREATE FUNCTION utilisé pour créer l’UDTF, il y a une propriété sur l’objet row
avec le même nom en majuscules. La valeur de cette propriété est la valeur de l’argument de la ligne courante convertie en une valeur JavaScript.
L’argument rowWriter
est utilisé par le code fourni par l’utilisateur pour produire des lignes de sortie. Une seule fonction, writeRow
, est définie sur l’objet rowWriter
. La fonction writeRow
prend un argument, l’objet ligne, qui est une ligne simple dans la table de sortie représentée comme un objet JavaScript. Pour chaque colonne définie dans la clause RETURNS du DDL CREATE FUNCTION, une propriété correspondante peut être définie sur l’objet de ligne. La valeur de cette propriété sur l’objet de ligne deviendra la valeur de la colonne correspondante dans la relation de sortie. Toute colonne de sortie sans propriété correspondante sur l’objet de ligne aura la valeur NULL dans la table des résultats.
finalize
¶
Cette fonction de rappel est appelée une fois pour chaque partition, après que toutes les lignes ont été transmises dans processRow
.
Elle peut être utilisée pour sortir n’importe quel état qui peut avoir été agrégé dans processRow
en utilisant le même générateur de lignes que celui qui est transmis dans processRow
.
initialize
¶
Cette fonction de rappel est appelée une fois pour chaque partition avant tout appel de processRow
; des lignes ne peuvent pas être produites par cette méthode.
initialize
peut être utilisé pour configurer n’importe quel état nécessaire pendant le calcul du résultat. En outre, initialize
transmet des métadonnées sur les arguments à la fonction définie par l’utilisateur dans l’objet argumentInfo
. argumentInfo
a une propriété pour chaque colonne d’entrée définie dans DDL avec des métadonnées sur cette colonne. Chaque entrée est un objet avec les valeurs suivantes :
type
: chaîne. Le type de cette colonne.isConst
: booléen. Si « true », la valeur de cette colonne est constante (c’est-à-dire qu’elle est la même pour chaque ligne).constValue
: siisConst
(comme défini ci-dessus) est « true », cette entrée contient la valeur constante de la colonne ; sinon, ce champ estundefined
.
Notes générales sur l’utilisation des fonctions de rappel¶
Les trois fonctions de rappel prennent un objet
context
; celui-ci est réservé pour une utilisation future et est actuellement vide.Prudence
La modification de l’objet
context
peut entraîner un comportement indéfini.D’autres fonctions et propriétés peuvent être définies, si nécessaire, sur l’objet à utiliser dans l’UDTF.
Les arguments des fonctions de rappel sont positionnels et peuvent être nommés librement ; cependant, pour les besoins de ce chapitre, les noms ci-dessus sont utilisés pour le reste de la discussion et des exemples.
Partitions¶
Les UDTFs JavaScript peuvent être appelés à l’aide d’une partition. Par exemple :
SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER (PARTITION BY <expr> ORDER BY <expr>));
Une partition se compose de toutes les lignes pour lesquelles les expressions PARTITION BY
sont évaluées à la même valeur. Les lignes sont transmises dans processRow
dans l’ordre défini par l’expression ORDER BY.
Pour cette discussion, une instance d’un UDTF JavaScript est définie comme une instance de l’objet JavaScript utilisée pour représenter la fonction dans Snowflake.
Pour améliorer les performances lors de l’appel d’un UDTF JavaScript, plusieurs instances UDTF peuvent s’exécuter en parallèle. Pour que cela fonctionne, les lignes de l’entrée doivent être réparties entre les différentes instances de la fonction. Le partitionnement est spécifié lors de l’appel d’un UDTF JavaScript :
Toutes les lignes d’une partition sont transmises à la même instance d’UDTF via la fonction
processRow
.initialize
etfinalize
sont chacun appelés une fois par partition.
Notez qu’il n’y a pas de relation « une à une » entre les partitions et les instances d’UDTF. Bien que chaque partition soit traitée que par une seule instance d’UDTF, l’inverse n’est pas vrai – une seule instance d’UDTF peut traiter plusieurs partitions. Il est donc important d’utiliser initialize
et finalize
pour configurer et démonter spécifiquement les partitions, par exemple pour éviter de « transférer » les valeurs accumulées lors du traitement d’une partition vers le traitement d’une autre partition.
Colonnes de résultats¶
Cette section décrit les colonnes retournées par un UDTF JavaScript :
Toutes les colonnes définies dans la clause RETURNS du DDL CREATE FUNCTION sont retournées dans la relation de sortie.
Toutes les colonnes transmises dans l’UDTF sont également retournées.
Il existe une distinction entre les lignes produites dans le rappel processRow
et les lignes produites par finalize
:
Lorsqu’une ligne est produite dans
processRow
, Snowflake peut la corréler à une entrée, à savoir celle transmise dans la fonction en tant qu’argumentrow
. Notez que si unprocessRow
donné produit plus d’une ligne, les attributs d’entrée sont dupliqués dans chaque ligne de sortie.Pour les lignes produites dans
processRow
, toutes les colonnes d’entrée sont dupliquées dans la relation de sortie.Dans le rappel
finalize
, Snowflake est incapable de le corréler à une seule ligne, car il n’existe pas de « ligne actuelle » à corréler.Pour les lignes produites dans le rappel
finalize
, seules les colonnes utilisées dans la clause PARTITION BY sont dupliquées (car elles sont les mêmes pour toutes les lignes de la partition courante) ; tous les autres attributs sont NULL. Si aucune clause PARTITION BY n’est spécifiée, tous les attributs d’entrée sont NULL.
Appeler des UDTFs JavaScript dans les requêtes¶
Pas de partitionnement¶
Cet exemple simple montre comment appeler un UDTF. Cet exemple transmet des valeurs littérales. L’UDTF renvoie simplement les paramètres dans l’ordre inverse de leur ordre de transmission. Cet exemple n’utilise pas le partitionnement.
SELECT * FROM TABLE(js_udtf(10.0::FLOAT, 20.0::FLOAT)); +----+----+ | Y | X | |----+----| | 20 | 10 | +----+----+
Cet exemple appelle un UDTF et lui transmet les valeurs d’une autre table. Dans cet exemple, l’UDTF nommé js_udtf
est appelé une fois pour chaque ligne de la table nommée tab1
. Chaque fois que la fonction est appelée, les valeurs des colonnes c1
et c2
de la ligne en cours lui sont transmises. Comme ci-dessus, l’UDTF est appelé sans clause PARTITION BY
.
SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2)) ;
Lorsqu’aucun partitionnement n’est utilisé, le moteur d’exécution de Snowflake partitionne l’entrée elle-même en fonction de différents facteurs, tels que la taille de l’entrepôt virtuel traitant la fonction et la cardinalité de la relation d’entrée. Dans ce mode, le code utilisateur ne peut faire aucune supposition quant aux partitions. Ceci est particulièrement utile lorsque la fonction n’a que besoin d’examiner les lignes en isolation pour produire sa sortie, et aucun état n’est agrégé d’une ligne à l’autre.
Partitionnement explicite¶
Les UDTFs JavaScript peuvent aussi être appelés à l’aide d’une partition. Par exemple :
SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER (PARTITION BY tab1.c3 ORDER BY tab1.c1));
Partitionnement explicite avec une clause OVER
vide¶
SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2) OVER ());
Une clause OVER
vide signifie que chaque ligne appartient à la même partition (c’est-à-dire que toute la relation d’entrée est une partition).
Note
Vous devez faire preuve de prudence lorsque vous appelez un UDTF JavaScript avec une clause OVER
vide, car cela limite Snowflake à créer une instance de la fonction et, par conséquent, Snowflake est incapable de paralléliser le calcul.
Exemples d’UDTFs JavaScript¶
Cette section contient plusieurs exemples d’UDTFs JavaScript.
Exemples Hello World
de base¶
L’UDTF JavaScript suivant ne prend aucun paramètre en compte et retourne toujours les mêmes valeurs. Il est fourni principalement à titre d’illustration :
CREATE OR REPLACE FUNCTION HelloWorld0() RETURNS TABLE (OUTPUT_COL VARCHAR) LANGUAGE JAVASCRIPT AS '{ processRow: function f(row, rowWriter, context){ rowWriter.writeRow({OUTPUT_COL: "Hello"}); rowWriter.writeRow({OUTPUT_COL: "World"}); } }'; SELECT output_col FROM TABLE(HelloWorld0());Sortie :
+------------+ | OUTPUT_COL | +============+ | Hello | +------------+ | World | +------------+
L’UDTF JavaScript suivant sert également à des fins d’illustration, mais utilise un paramètre d’entrée. Notez que JavaScript est sensible à la casse, mais SQL force les identificateurs à être écrits en majuscules. Par conséquent, lorsque le code JavaScript fait référence à un nom de paramètre SQL, le code JavaScript doit utiliser des majuscules.
Notez également que les paramètres de fonction sont accessibles par le paramètre nommé row
dans la fonction get_params()
:
CREATE OR REPLACE FUNCTION HelloHuman(First_Name VARCHAR, Last_Name VARCHAR) RETURNS TABLE (V VARCHAR) LANGUAGE JAVASCRIPT AS '{ processRow: function get_params(row, rowWriter, context){ rowWriter.writeRow({V: "Hello"}); rowWriter.writeRow({V: row.FIRST_NAME}); // Note the capitalization and the use of "row."! rowWriter.writeRow({V: row.LAST_NAME}); // Note the capitalization and the use of "row."! } }'; SELECT V AS Greeting FROM TABLE(HelloHuman('James', 'Kirk'));Sortie :
+------------+ | GREETING | +============+ | Hello | +------------+ | James | +------------+ | Kirk | +------------+
Exemples de base illustrant les fonctions de rappel¶
L’UDTF JavaScript suivant illustre toutes les fonctions de rappel d’API et les différentes colonnes de sortie. Il retourne simplement toutes les lignes telles quelles et fournit un décompte du nombre de caractères vus dans chaque partition. Il illustre également comment partager l’état d’une partition en utilisant une référence THIS
. Notez que l’exemple utilise un rappel initialize
pour rétablir le compteur à zéro ; cela est nécessaire, car une instance de fonction donnée peut être utilisée pour traiter plusieurs partitions :
-- set up for the sample CREATE TABLE parts (p FLOAT, s STRING); INSERT INTO parts VALUES (1, 'michael'), (1, 'kelly'), (1, 'brian'); INSERT INTO parts VALUES (2, 'clara'), (2, 'maggie'), (2, 'reagan'); -- creation of the UDTF CREATE OR REPLACE FUNCTION "CHAR_SUM"(INS STRING) RETURNS TABLE (NUM FLOAT) LANGUAGE JAVASCRIPT AS '{ processRow: function (row, rowWriter, context) { this.ccount = this.ccount + 1; this.csum = this.csum + row.INS.length; rowWriter.writeRow({NUM: row.INS.length}); }, finalize: function (rowWriter, context) { rowWriter.writeRow({NUM: this.csum}); }, initialize: function(argumentInfo, context) { this.ccount = 0; this.csum = 0; }}';
La requête suivante illustre l’appel de l’UDTF CHAR_SUM
sur la table parts
sans partitionnement :
SELECT * FROM parts, TABLE(char_sum(s));Sortie :
+--------+---------+-----+ | P | S | NUM | +--------+---------+-----+ | 1 | michael | 7 | | 1 | kelly | 5 | | 1 | brian | 5 | | 2 | clara | 5 | | 2 | maggie | 6 | | 2 | reagan | 6 | | [NULL] | [NULL] | 34 | +--------+---------+-----+Lorsqu’aucun partitionnement n’est spécifié, Snowflake définit automatiquement les partitions. Dans cet exemple, en raison du petit nombre de lignes, une seule partition est créée (c’est-à-dire qu’un seul appel de
finalize
est exécuté). Notez que la dernière ligne contient des valeurs NULL dans les colonnes d’entrée.
Même requête, mais avec un partitionnement explicite :
SELECT * FROM parts, TABLE(char_sum(s) OVER (PARTITION BY p));Sortie :
+--------+---------+-----+ | P | S | NUM | +--------+---------+-----+ | 1 | michael | 7 | | 1 | kelly | 5 | | 1 | brian | 5 | | 1 | [NULL] | 17 | | 2 | clara | 5 | | 2 | maggie | 6 | | 2 | reagan | 6 | | 2 | [NULL] | 17 | +--------+---------+-----+Cet exemple de partitions sur la colonne
p
donne deux partitions. Pour chaque partition, une seule ligne est retournée dans le rappelfinalize
, ce qui donne un total de deux lignes, distinguées par la valeur NULL dans la colonnes
. Puisquep
est la colonne PARTITION BY, les lignes créées dansfinalize
ont la valeurp
qui définit la partition courante.
Exemples étendus utilisant les valeurs des tables et d’autres UDTFs comme entrées¶
Cet UDTF de base convertit une « plage » d’adresses IP en une liste complète d’adresses IP. L’entrée se compose des 3 premiers segments de l’adresse IP (par exemple '192.168.1'
), puis du début et de la fin de la plage utilisée pour générer le dernier segment (par exemple 42
et 45
) :
CREATE OR REPLACE FUNCTION range_to_values(PREFIX VARCHAR, RANGE_START FLOAT, RANGE_END FLOAT) RETURNS TABLE (IP_ADDRESS VARCHAR) LANGUAGE JAVASCRIPT AS $$ { processRow: function f(row, rowWriter, context) { var suffix = row.RANGE_START; while (suffix <= row.RANGE_END) { rowWriter.writeRow( {IP_ADDRESS: row.PREFIX + "." + suffix} ); suffix = suffix + 1; } } } $$; SELECT * FROM TABLE(range_to_values('192.168.1', 42::FLOAT, 45::FLOAT));Sortie :
+--------------+ | IP_ADDRESS | +==============+ | 192.168.1.42 | +--------------+ | 192.168.1.43 | +--------------+ | 192.168.1.44 | +--------------+ | 192.168.1.45 | +--------------+
En vous basant sur l’exemple précédent, vous pouvez calculer des adresses IP individuelles pour plus d’une plage. L’instruction suivante crée une table de plages qui peut être utilisée pour s’étendre à des adresses IP individuelles. La requête entre ensuite les lignes de la table dans l’UDTF range_to_values()
pour retourner les adresses IP individuelles :
CREATE TABLE ip_address_ranges(prefix VARCHAR, range_start INTEGER, range_end INTEGER); INSERT INTO ip_address_ranges (prefix, range_start, range_end) VALUES ('192.168.1', 42, 44), ('192.168.2', 10, 12), ('192.168.2', 40, 40) ; SELECT rtv.ip_address FROM ip_address_ranges AS r, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv;Sortie :
+--------------+ | IP_ADDRESS | +==============+ | 192.168.1.42 | +--------------+ | 192.168.1.43 | +--------------+ | 192.168.1.44 | +--------------+ | 192.168.2.10 | +--------------+ | 192.168.2.11 | +--------------+ | 192.168.2.12 | +--------------+ | 192.168.2.40 | +--------------+Attention
Dans cet exemple, la syntaxe utilisée dans la clause FROM est identique à la syntaxe d’une jointure interne (c’est-à-dire
FROM t1, t2
) ; cependant, l’opération effectuée n’est pas une jointure interne vraie. Le comportement réel est la fonctionrange_to_values()
qui est appelée avec les valeurs de chaque ligne de la tableip_address changes
. En d’autres termes, ce serait l’équivalent de l’écriture :for input_row in ip_address_ranges: output_row = range_to_values(input_row.prefix, input_row.range_start, input_row.range_end)
Le concept de transmettre des valeurs à un UDTF peut être étendu à plusieurs UDTFs. L’exemple suivant crée un UDTF nommé fake_ipv4_to_ipv6()
qui « convertit » une adresse IPV4 en adresses IPV6. La requête appelle alors la fonction dans le cadre d’une instruction plus complexe impliquant un autre UDTF :
-- Example UDTF that "converts" an IPV4 address to a range of IPV6 addresses. -- (for illustration purposes only and is not intended for actual use) CREATE OR REPLACE FUNCTION fake_ipv4_to_ipv6(ipv4 VARCHAR) RETURNS TABLE (IPV6 VARCHAR) LANGUAGE JAVASCRIPT AS $$ { processRow: function f(row, rowWriter, context) { rowWriter.writeRow( {IPV6: row.IPV4 + "." + "000.000.000.000"} ); rowWriter.writeRow( {IPV6: row.IPV4 + "." + "..."} ); rowWriter.writeRow( {IPV6: row.IPV4 + "." + "FFF.FFF.FFF.FFF"} ); } } $$; SELECT ipv6 FROM TABLE(fake_ipv4_to_ipv6('192.168.3.100'));Sortie :
+-------------------------------+ | IPV6 | +===============================+ | 192.168.3.100.000.000.000.000 | +-------------------------------+ | 192.168.3.100.... | +-------------------------------+ | 192.168.3.100.FFF.FFF.FFF.FFF | +-------------------------------+
La requête suivante utilise les UDTFs fake_ipv4_to_ipv6
et range_to_values()
créés précédemment, avec l’entrée de la table ip_address changes
. En d’autres termes, elle commence par un ensemble de plages d’adresses IP, les convertit en adresses IPV4 individuelles, puis prend chaque adresse IPV4 et la « convertit » en une plage d’adresses IPV6 :
SELECT rtv6.ipv6 FROM ip_address_ranges AS r, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv, TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6 WHERE r.prefix = '192.168.2' -- limits the output for this example ;Sortie :
+------------------------------+ | IPV6 | +==============================+ | 192.168.2.10.000.000.000.000 | +------------------------------+ | 192.168.2.10.... | +------------------------------+ | 192.168.2.10.FFF.FFF.FFF.FFF | +------------------------------+ | 192.168.2.11.000.000.000.000 | +------------------------------+ | 192.168.2.11.... | +------------------------------+ | 192.168.2.11.FFF.FFF.FFF.FFF | +------------------------------+ | 192.168.2.12.000.000.000.000 | +------------------------------+ | 192.168.2.12.... | +------------------------------+ | 192.168.2.12.FFF.FFF.FFF.FFF | +------------------------------+ | 192.168.2.40.000.000.000.000 | +------------------------------+ | 192.168.2.40.... | +------------------------------+ | 192.168.2.40.FFF.FFF.FFF.FFF | +------------------------------+Notez que dans cet exemple, nous avons utilisé deux fois la syntaxe de jointure, mais aucune des opérations n’était une vraie jointure ; les deux étaient des appels à un UDTF utilisant la sortie d’une table ou un autre UDTF comme entrée.
Une jointure intérieure véritable est insensible à l’ordre. Par exemple, les instructions suivantes sont identiques :
table1 INNER JOIN table2 ON ...
table2 INNER JOIN table1 ON ...
La saisie de valeurs dans un UDTF n’est pas une véritable jointure, et les opérations ne sont pas insensibles à l’ordre. Par exemple, la requête suivante est identique à l’exemple précédent, sauf qu’elle inverse l’ordre des UDTFs dans la clause FROM :
SELECT rtv6.ipv6 FROM ip_address_ranges AS r, TABLE(fake_ipv4_to_ipv6(rtv.ip_address)) AS rtv6, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv WHERE r.prefix = '192.168.2' -- limits the output for this example ;La requête échoue avec le message d’erreur suivant :
SQL compilation error: error line 3 at position 35 invalid identifier 'RTV.IP_ADDRESS'
L’identificateur
rtv.ip_address
n’est pas valide, car il n’était pas défini avant son utilisation. Dans une vraie jointure, cela n’arriverait pas, mais lors du traitement des UDTFs en utilisant la syntaxe de jointure, cette erreur pourrait se produire.
Ensuite, essayez une instruction qui mélange l’entrée à un UDTF avec une jointure vraie ; cependant, rappelez-vous que l’entrée d’un UDTF et l’exécution d’une jointure interne utilisent la même syntaxe, ce qui pourrait porter à confusion :
-- First, create a small table of IP address owners. -- This table uses only IPv4 addresses for simplicity. DROP TABLE ip_address_owners; CREATE TABLE ip_address_owners (ip_address VARCHAR, owner_name VARCHAR); INSERT INTO ip_address_owners (ip_address, owner_name) VALUES ('192.168.2.10', 'Barbara Hart'), ('192.168.2.11', 'David Saugus'), ('192.168.2.12', 'Diego King'), ('192.168.2.40', 'Victoria Valencia') ; -- Now join the IP address owner table to the IPv4 addresses. SELECT rtv.ip_address, ipo.owner_name FROM ip_address_ranges AS r, TABLE(range_to_values(r.prefix, r.range_start::FLOAT, r.range_end::FLOAT)) AS rtv, ip_address_owners AS ipo WHERE ipo.ip_address = rtv.ip_address AND r.prefix = '192.168.2' -- limits the output for this example ;Sortie :
+--------------+-------------------+ | IP_ADDRESS | OWNER_NAME | +==============+===================+ | 192.168.2.10 | Barbara Hart | +--------------+-------------------+ | 192.168.2.11 | David Saugus | +--------------+-------------------+ | 192.168.2.12 | Diego King | +--------------+-------------------+ | 192.168.2.40 | Victoria Valencia | +--------------+-------------------+Attention
L’exemple précédent fonctionne comme décrit ; cependant, vous devez faire attention lorsque vous combinez des UDTFs avec des jointures vraies, car cela pourrait entraîner un comportement non déterministe et/ou inattendu.
De plus, notez que ce comportement pourrait changer à l’avenir.