UDFs JavaScript tabulaires (UDTFs)¶

processRow()¶

Cette fonction de rappel est appelée une fois pour chaque ligne de la relation d’entrée. Les arguments de l”processRow() sont transmis dans l’objet row. Pour chacun des arguments définis dans l’instruction 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. (La valeur est convertie en une valeur JavaScript).

L’argument rowWriter est utilisé par le code fourni par l’utilisateur pour produire des lignes de sortie. L’objet rowWriter définit une seule fonction, writeRow(). 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 de la commande 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 finalize() est appelée une fois, après que toutes les lignes ont été transmises dans processRow(). (Si les données sont regroupées en partitions, alors finalize() est appelé une fois pour chaque partition, après que toutes les lignes de cette partition ont été transmises à processRow()).

Cette fonction de rappel 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 rowWriter 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().

Utilisez initialize() pour configurer n’importe quel état nécessaire pendant le calcul du résultat.

Le paramètre argumentInfo de la fonction initialize() contient des métadonnées sur les arguments de la fonction définie par l’utilisateur. Par exemple, si l’UDF est définie comme :

CREATE FUNCTION f(argument_1 INTEGER, argument_2 VARCHAR) ...

alors argumentInfo contient des informations sur argument_1 et argument_2.

argumentInfo possède une propriété pour chacun de ces arguments. Chaque propriété est un objet avec les valeurs suivantes :

• type : chaîne. Le type de cet argument.

• isConst : booléen. Si « true », la valeur de cet argument est constante (c’est-à-dire qu’elle est la même pour chaque ligne).

• constValue : si isConst (comme défini ci-dessus) est « true », cette entrée contient la valeur constante de l’argument ; sinon, ce champ est undefined.

La fonction initialize() ne peut pas produire de lignes de sortie.

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.

Colonnes de résultats¶

Lorsqu’une table est jointe à une fonction de table, comme dans les exemples de partitionnement ci-dessus, le jeu de résultats peut contenir les éléments suivants, selon ce qui est sélectionné :

• Les colonnes définies dans la clause RETURNS de la commande CREATE FUNCTION.

• Les colonnes de la table, y compris les colonnes utilisées pour partitionner les données et les autres colonnes, qu’elles soient ou non utilisées comme paramètres d’entrée de l’UDTF.

Notez que les lignes produites dans le rappel processRow et les lignes produites par finalize diffèrent des manières suivantes :

• Lorsqu’une ligne est produite dans processRow, Snowflake peut la corréler à une ligne en entrée, à savoir celle transmise dans la fonction en tant qu’argument row. Notez que si un appel processRow donné produit plus d’une ligne, les attributs d’entrée sont corrélés dans chaque ligne de sortie.

  Pour les lignes produites en processRow, toutes les colonnes d’entrée peuvent être jointes à 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 disponibles (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 ces attributs sont NULL.

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).

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 rappel finalize(), ce qui donne un total de deux lignes, distinguées par la valeur NULL dans la colonne s. Puisque p est la colonne PARTITION BY, les lignes créées dans finalize() ont la valeur p 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 |
+--------------+

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 :

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 |
+--------------+-------------------+