JavaScript UDFs

Cette rubrique couvre les exigences générales et les détails d’utilisation des UDF (fonctions définies par l’utilisateur) JavaScript, ainsi que les limitations spécifiques aux UDFs.

Dans ce chapitre :

Exemple d’introduction

L’exemple suivant montre comment trier un tableau avec un UDF JavaScript. Cet exemple profite du fait que JavaScript dispose d’une méthode de tri intégrée pour les tableaux.

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();
$$
;

-- Call the UDF with a small array.
SELECT ARRAY_SORT(PARSE_JSON('[2,4,5,3,1]'));

La sortie serait similaire à ce qui suit :

[1, 2, 3, 4, 5]

Types de données JavaScript

Les UDFs SQL et JavaScript fournissent des types de données similaires, mais différents, en fonction de leur prise en charge native des types de données. Les objets dans Snowflake et JavaScript sont transférés en utilisant les mappages suivants.

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

Arguments JavaScript et valeurs retournées

Les arguments peuvent être référencés directement par leur nom dans JavaScript. Notez qu’un identificateur sans guillemet doit être référencé avec le nom de la variable en majuscule. Comme les arguments et les UDF sont référencés depuis JavaScript, ils doivent être des identificateurs JavaScript légaux. Plus précisément, les noms d’UDF et d’arguments doivent commencer par une lettre ou $, tandis que les caractères suivants peuvent être alphanumériques, $, ou _. De plus, les noms ne peuvent pas être des mots réservés JavaScript.

Les trois exemples suivants illustrent des UDFs qui utilisent des arguments référencés par leur nom :

-- Valid UDF.  'N' must be capitalized.
CREATE OR REPLACE FUNCTION add5(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return N + 5;';

select add5(0.0);

-- Valid UDF. Lowercase argument is double-quoted.
CREATE OR REPLACE FUNCTION add5_quoted("n" double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_quoted(0.0);

-- Invalid UDF. Error returned at runtime because JavaScript identifier 'n' cannot be resolved.
CREATE OR REPLACE FUNCTION add5_lowercase(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_lowercase(0.0);

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 valeurJavaScript 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');

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;
    }
    ';

Appelez la fonction :

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

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;
      }
      ';
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            |
+------+--------------------------------------+-----------------------------------+

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;
      }
      $$;
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                                                            |
|                    |               | }                                                                         |
+--------------------+---------------+---------------------------------------------------------------------------+

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;
$$
;
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

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.

Erreurs JavaScript

Toutes les erreurs rencontrées lors de l’exécution de JavaScript apparaissent à l’utilisateur comme des erreurs SQL. Cela inclut les erreurs d’analyse, les erreurs d’exécution et les erreurs non détectées lancées dans l’UDF. Si l’erreur contient une trace d’appels, celle-ci sera imprimée avec le message d’erreur. Il est acceptable de lancer une erreur sans la capturer afin de terminer la requête et de produire une erreur SQL.

Lors du débogage, vous pouvez trouver utile d’imprimer les valeurs des arguments avec le message d’erreur pour qu’elles apparaissent dans le texte du message d’erreur SQL. Pour des UDFs déterministes, cela fournit les données nécessaires pour reproduire les erreurs dans un moteur JavaScript local. Un modèle commun est de placer un corps d’UDF JavaScript entier dans un bloc try-catch, d’ajouter des valeurs d’argument au message de l’erreur capturée, et de lancer une erreur avec le message étendu. Vous devriez envisager de supprimer de tels mécanismes avant de déployer des UDFs dans un environnement de production ; l’enregistrement de valeurs dans des messages d’erreur peut révéler involontairement des données sensibles.

La fonction peut générer et intercepter des exceptions prédéfinies ou des exceptions personnalisées. Un exemple simple de levée d’une exception personnalisée est disponible ici.

Limites liées aux UDF JavaScript

Pour assurer la stabilité dans l’environnement Snowflake, Snowflake impose les limites suivantes aux UDFs JavaScript. Ces limitations ne sont pas appelées lors de la création d’UDF, mais plutôt lors de l’exécution au moment de l’appel de l’UDF.

Mémoire

Les UDFs JavaScript échoueront s’ils consomment trop de mémoire. La limite spécifique est sujette à changement. L’utilisation d’une trop grande quantité de mémoire entraînera le retour d’une erreur.

Durée

Les UDFs JavaScript qui prennent trop de temps à se terminer seront arrêtés et une erreur sera retournée à l’utilisateur. De plus, les UDFs JavaScript qui commencent des boucles sans fin entraîneront des erreurs.

Profondeur de pile

Une profondeur de pile excessive due à la récursion entraînera une erreur.

État global

Snowflake préserve généralement l’état global JavaScript entre les itérations d’un UDF. Cependant, vous ne devez pas vous fier aux modifications précédentes de l’état global disponibles entre les appels de fonctions. De plus, vous ne devez pas supposer que toutes les lignes s’exécuteront dans le même environnement JavaScript.

Dans la pratique, l’état global est pertinent dans les cas suivants :

  • Logique d’initialisation complexe/coûteuse. Par défaut, le code UDF fourni est évalué pour chaque ligne traitée. Si ce code contient une logique complexe, cela pourrait être inefficace.

  • Les fonctions qui contiennent du code qui n’est pas idempotent. Un modèle typique serait celui-ci :

    Date.prototype._originalToString = Date.prototype.toString;
    Date.prototype.toString = function() {
      /* ... SOME CUSTOM CODE ... */
      this._originalToString()
      }
    

    La première fois que ce code est exécuté, il change l’état de toString et _originalToString. Ces changements sont conservés dans l’état global, et la deuxième fois que ce code est exécuté, les valeurs sont à nouveau modifiées de manière à créer une récursion. La deuxième fois que toString est appelé, le code se répète à l’infini (jusqu’à ce qu’il n’y ait plus d’espace de pile).

Pour ces situations, un modèle recommandé est de garantir que le code pertinent n’est évalué qu’une seule fois, en utilisant la sémantique des variables globales de JavaScript. Par exemple :

var setup = function() {
/* SETUP LOGIC */
};

if (typeof(setup_done) === "undefined") {
  setup();
  setup_done = true;  // setting global variable to true
}

Notez que ce mécanisme n’est sûr que pour la mise en cache des effets de l’évaluation du code. Il n’est pas garanti qu’après une initialisation, le contexte global sera préservé pour toutes les lignes, et aucune logique métier ne doit en dépendre.

Bibliothèques

Les UDFs JavaScript acceptent l’accès à la bibliothèque JavaScript standard. Notez que ceci exclut de nombreux objets et méthodes généralement fournis par les navigateurs. Il n’existe aucun mécanisme pour importer, inclure ou appeler des bibliothèques supplémentaires. Tout le code requis doit être incorporé dans l’UDF.

De plus, la fonction eval() JavaScript intégrée est désactivée.

Taille et profondeur de la variante retournée

Les objets de variantes renvoyés sont soumis à des limitations de taille et de profondeur d’imbrication :

Taille

Actuellement limité à plusieurs mégaoctets, mais sujet à changement.

Profondeur

Actuellement limité à une profondeur d’imbrication de 1 000, mais sujet à changement.

Si un objet est trop grand ou trop profond, une erreur est retournée lorsque l’UDF est appelé.

Sécurité des UDF JavaScript

Les UDFs JavaScript sont conçus pour être sûrs et sécuritaires en fournissant plusieurs couches d’interrogation et d’isolation des données :

  • Les serveurs de l’entrepôt virtuel qui exécute un UDF JavaScript ne sont accessibles qu’à partir de votre compte (c’est-à-dire que les entrepôts virtuels ne partagent pas les ressources avec d’autres comptes Snowflake).

  • Les données des tables sont cryptées dans l’entrepôt virtuel pour empêcher tout accès non autorisé.

  • Les UDFs JavaScript de Snowflake sont exécutés dans un moteur restreint, empêchant les appels système du contexte JavaScript (par exemple, pas d’accès au réseau et au disque) et limitant les ressources système disponibles pour le moteur, en particulier la mémoire.

Par conséquent, les UDFs JavaScript ne peuvent accéder qu’aux données nécessaires à l’exécution de la fonction définie et ne peuvent pas affecter l’état du système sous-jacent, si ce n’est en consommant une quantité raisonnable de mémoire et de temps processeur.

Exemples

Cette section contient des exemples supplémentaires.

Cet exemple montre qu’un UDF JavaScript peut s’appeler lui-même (c’est-à-dire qu’il peut utiliser la récursivité) :

Créez un UDF récursif :

CREATE OR REPLACE FUNCTION RECURSION_TEST (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
  return (STR.length <= 1 ? STR : STR.substring(0,1) + '_' + RECURSION_TEST(STR.substring(1)));
  $$
  ;

Appelez l’UDF récursif :

SELECT RECURSION_TEST('ABC');
+-----------------------+
| RECURSION_TEST('ABC') |
|-----------------------|
| A_B_C                 |
+-----------------------+

Cet exemple montre un UDF JavaScript qui lève une exception personnalisée :

Créez la fonction :

CREATE FUNCTION validate_ID(ID FLOAT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    try {
        if (ID < 0) {
            throw "ID cannot be negative!";
        } else {
            return "ID validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

Créez une table avec des valeurs valides et non valides :

CREATE TABLE employees (ID INTEGER);
INSERT INTO employees (ID) VALUES 
    (1),
    (-1);

Appelez la fonction :

SELECT ID, validate_ID(ID) FROM employees ORDER BY ID;
+----+-------------------------------+
| ID | VALIDATE_ID(ID)               |
|----+-------------------------------|
| -1 | Error: ID cannot be negative! |
|  1 | ID validated.                 |
+----+-------------------------------+

Dépannage

Message d’erreur : Variable is not defined

Cause

Dans certains cas, ce message d’erreur peut être causé par une esperluette (&) à l’intérieur d’une commande CREATE FUNCTION parce que l’esperluette est le caractère de substitution de la variable. Par exemple :

create function mask_bits(...)
    ...
    as
    $$
    var masked = (x & y);
    ...
    $$;

L’erreur se produit au moment de la création de la fonction et non au moment de l’appel de la fonction.

Solution

Si vous n’avez pas l’intention d’utiliser de substitution de variable, vous pouvez explicitement la désactiver. Par exemple, dans SnowSQL, vous pouvez désactiver la substitution de variable en exécutant la commande suivante :

!set variable_substitution=false;

Pour plus d’informations sur la substitution de variables, voir Utilisation de variables.