Présentation des UDFs JavaScript¶

Exemple¶

Le code de l’exemple suivant crée une UDF appelée array_sort avec un code de gestionnaire qui trie le tableau reçu. Les types d’argument et de retour JavaScript sont convertis de et vers SQL par Snowflake selon les mappages décrits dans Mappages de type de données SQL-JavaScript.

Notez que le code JavaScript doit faire référence aux noms de paramètres d’entrée en tant que majuscules, même si les noms ne sont pas en majuscules dans le code SQL.

-- Create the UDF.
CREATE OR REPLACE FUNCTION array_sort(a array)
  RETURNS array
  LANGUAGE JAVASCRIPT
AS
$$
  return A.sort();
$$
;

Entiers et doubles¶

JavaScript n’a pas de type entier ; tous les nombres sont représentés en double. Les UDFs JavaScript n’acceptent pas ou ne retournent pas de valeurs entières, sauf par le biais de la conversion de type (c’est-à-dire que vous pouvez transmettre un entier dans un UDF JavaScript qui accepte un double).

SQL et JavaScript sur Snowflake acceptent des valeurs doubles. Ces valeurs sont transférées telles quelles.

Chaînes¶

SQL et JavaScript sur Snowflake acceptent des valeurs de chaînes. Ces valeurs sont transférées telles quelles.

Valeurs binaires¶

Toutes les valeurs binaires sont converties en objets JavaScript Uint8Array. Ces tableaux de types peuvent être accessibles de la même manière que des tableaux JavaScript normaux, mais ils sont plus efficaces et acceptent des méthodes supplémentaires.

Si un UDF JavaScript retourne un objet Uint8Array, il est converti en une valeur binaire SQL Snowflake.

Dates¶

Tous les types d’horodatage et de date sont convertis en objets Date() JavaScript. Le type de date JavaScript est équivalent à TIMESTAMP_LTZ(3) dans le langage SQL Snowflake.

Considérez les notes suivantes pour les UDFs JavaScript qui acceptent une date ou une heure :

• Toute précision au-delà de la milliseconde est perdue.

• Un Date JavaScript généré à partir de SQL TIMESTAMP_NTZ n’agit plus comme une « horloge murale » ; il est influencé par l’heure d’été. Ceci est similaire au comportement qui survient lors de la conversion de TIMESTAMP_NTZ en TIMESTAMP_LTZ.

• Un Date JavaScript généré à partir de SQL TIMESTAMP_TZ perd des informations de fuseau horaire, mais représente le même moment dans le temps que l’entrée (similaire à la conversion de TIMESTAMP_TZ en TIMESTAMP_LTZ).

• DATE SQL est converti en Date JavaScript représentant minuit du jour actuel dans le fuseau horaire local.

De plus, considérez les notes suivantes pour les UDFs JavaScript qui retournent les types DATE et TIMESTAMP :

• Les objets Date JavaScript sont convertis dans le type de données du résultat de l’UDF, selon la même sémantique de conversion que les conversions de TIMESTAMP_LTZ(3) vers le type de données de retour.

• Les objets JavaScript Date imbriqués dans des objets VARIANT sont toujours de type TIMESTAMP_LTZ(3).

Variante, objets et tableaux¶

Les UDFs JavaScript permettent une manipulation simple et intuitive des variantes et des données JSON. Les objets de variantes transmis dans un UDF sont transformés en types et valeurs JavaScript natifs. Toutes les valeurs précédemment listées sont traduites dans leurs types JavaScript correspondants. Les objets et tableaux de variantes sont convertis en objets et tableaux JavaScript. De même, toutes les valeurs renvoyées par l’UDF sont transformées en valeurs de variantes appropriées. Notez que les objets et les tableaux retournés par l’UDF sont soumis à des limitations de taille et de profondeur.

-- flatten all arrays and values of objects into a single array
-- order of objects may be lost
CREATE OR REPLACE FUNCTION flatten_complete(v variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
  AS '
  // Define a function flatten(), which always returns an array.
  function flatten(input) {
    var returnArray = [];
    if (Array.isArray(input)) {
      var arrayLength = input.length;
      for (var i = 0; i < arrayLength; i++) {
        returnArray.push.apply(returnArray, flatten(input[i]));
      }
    } else if (typeof input === "object") {
      for (var key in input) {
        if (input.hasOwnProperty(key)) {
          returnArray.push.apply(returnArray, flatten(input[key]));
        }
      }
    } else {
      returnArray.push(input);
    }
    return returnArray;
  }

  // Now call the function flatten() that we defined earlier.
  return flatten(V);
  ';

select value from table(flatten(flatten_complete(parse_json(
'[
  {"key1" : [1, 2], "key2" : ["string1", "string2"]},
  {"key3" : [{"inner key 1" : 10, "inner key 2" : 11}, 12]}
  ]'))));

-----------+
   VALUE   |
-----------+
 1         |
 2         |
 "string1" |
 "string2" |
 10        |
 11        |
 12        |
-----------+

Valeurs NULL et non définies¶

Lorsque vous utilisez des UDFs JavaScript, vous devez porter une attention particulière aux lignes et aux variables qui peuvent contenir des valeurs NULL. Plus précisément, Snowflake contient deux valeurs NULL distinctes (SQL NULL et variante JSON null), tandis que JavaScript contient la valeur undefined en plus de la valeur null.

Les arguments SQL NULL vers un UDF JavaScript se traduiront par la valeur undefined JavaScript. De même, les valeurs undefined JavaScript renvoyées se traduisent par des valeurs SQL NULL. Ceci est vrai pour tous les types de données, y compris la variante. Pour les types qui ne sont pas des variantes, une valeur JavaScript null renvoyée donnera également une valeur SQL NULL.

Les arguments et les valeurs renvoyées du type de variante font la distinction entre les valeurs undefined et les valeurs null de JavaScript. SQL NULL continue de se traduire par JavaScript undefined (et JavaScript undefined vers SQL NULL) ; la variante JSON null se traduit par JavaScript null (et JavaScript null par variante JSON null). Une valeur undefined incorporée dans un objet JavaScript (comme valeur) ou un tableau entraînera l’omission de l’élément.

Créez une table avec une chaîne et une valeur NULL :

create or replace table strings (s string);
insert into strings values (null), ('non-null string');

Copy

Créez une fonction qui convertit une chaîne en NULL et un NULL en chaîne :

CREATE OR REPLACE FUNCTION string_reverse_nulls(s string)
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS '
    if (S === undefined) {
        return "string was null";
    } else
    {
        return undefined;
    }
    ';

Copy

Appelez la fonction :

select string_reverse_nulls(s) 
    from strings
    order by 1;
+-------------------------+
| STRING_REVERSE_NULLS(S) |
|-------------------------|
| string was null         |
| NULL                    |
+-------------------------+

Copy

Créez une fonction qui montre la différence entre transmettre un NULL SQL et une variante null JSON :

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS VARCHAR
      LANGUAGE JAVASCRIPT
      AS '
      if (V === undefined) {
        return "input was SQL null";
      } else if (V === null) {
        return "input was variant null";
      } else {
        return V;
      }
      ';

Copy

select null, 
       variant_nulls(cast(null as variant)),
       variant_nulls(PARSE_JSON('null'))
       ;
+------+--------------------------------------+-----------------------------------+
| NULL | VARIANT_NULLS(CAST(NULL AS VARIANT)) | VARIANT_NULLS(PARSE_JSON('NULL')) |
|------+--------------------------------------+-----------------------------------|
| NULL | input was SQL null                   | input was variant null            |
+------+--------------------------------------+-----------------------------------+

Copy

Créez une fonction qui montre la différence entre le renvoi de undefined, null et une variante contenant undefined et null (notez que la valeur undefined est supprimée de la variante renvoyée) :

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS variant
      LANGUAGE JAVASCRIPT
      AS $$
      if (V == 'return undefined') {
        return undefined;
      } else if (V == 'return null') {
        return null;
      } else if (V == 3) {
        return {
            key1 : undefined,
            key2 : null
            };
      } else {
        return V;
      }
      $$;

Copy

select variant_nulls('return undefined'::VARIANT) AS "RETURNED UNDEFINED",
       variant_nulls('return null'::VARIANT) AS "RETURNED NULL",
       variant_nulls(3) AS "RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED";
+--------------------+---------------+---------------------------------------------------------------------------+
| RETURNED UNDEFINED | RETURNED NULL | RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED |
|--------------------+---------------+---------------------------------------------------------------------------|
| NULL               | null          | {                                                                         |
|                    |               |   "key2": null                                                            |
|                    |               | }                                                                         |
+--------------------+---------------+---------------------------------------------------------------------------+

Copy

Conversion de type dans JavaScript¶

JavaScript convertira implicitement les valeurs entre de nombreux types différents. Lorsqu’une valeur est retournée, elle est d’abord convertie dansa le type de retour demandé avant d’être convertie en une valeur SQL. Par exemple, si un nombre est retourné, mais que l’UDF est déclaré comme retournant une chaîne, ce nombre sera converti en chaîne dans JavaScript. Gardez à l’esprit que les erreurs de programmation JavaScript, comme le retour du type incorrect, peuvent être masquées par ce comportement. De plus, si une erreur est générée lors de la conversion du type de la valeur, une erreur se produira.

Plage de nombres JavaScript¶

La plage pour les nombres dont la précision est intacte est de

-(2^53 -1)

dans

(2^53 -1)

La plage de valeurs valides dans les types de données Snowflake NUMBER(p, s) et DOUBLE est plus grande. La récupération d’une valeur de Snowflake et son stockage dans une variable numérique JavaScript peut entraîner une perte de précision. Par exemple :

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

Copy

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Copy

Les deux premières colonnes doivent correspondre, et la troisième doit contenir 0.0.

Le problème s’applique aux fonctions JavaScript définies par l’utilisateur (UDFs) et aux procédures stockées.

Si vous rencontrez le problème dans les procédures stockées avec getColumnValue(), vous pouvez l’éviter en récupérant une valeur sous forme de chaîne, par exemple avec :

getColumnValueAsString()

Vous pouvez ensuite renvoyer la chaîne à partir de la procédure stockée et la convertir en un type de données numérique dans SQL.