Fonctions définies par l'utilisateur en SQL standard

BigQuery est compatible avec les fonctions définies par l'utilisateur. Une fonction définie par l'utilisateur vous permet de créer une fonction à l'aide d'une autre expression SQL ou du langage JavaScript. Ces fonctions acceptent des colonnes d'entrée et effectuent des actions, puis renvoient le résultat de ces dernières sous la forme d'une valeur. Consultez la section Fonctions définies par l'utilisateur en ancien SQL pour en savoir plus sur celles-ci.

Les fonctions définies par l'utilisateur peuvent être persistantes ou temporaires. Vous pouvez réutiliser une fonction persistante définie par l'utilisateur dans plusieurs requêtes, alors qu'une fonction temporaire définie par l'utilisateur ne peut être utilisée que dans une seule requête.

Syntaxe des fonctions définies par l'utilisateur

Pour créer une fonction persistante définie par l'utilisateur, utilisez la syntaxe suivante :

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [`project_name`.]dataset_name.function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

Pour créer une fonction temporaire définie par l'utilisateur, utilisez la syntaxe suivante :

CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

named_parameter:
  param_name param_type

sql_function_definition:
  AS (sql_expression)

javascript_function_definition:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Cette syntaxe comprend les composants suivants :

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS } : crée ou met à jour une fonction. Pour remplacer une fonction existante portant le même nom, utilisez le mot clé OR REPLACE. Pour traiter la requête comme réussie et n'effectuer aucune action si une fonction du même nom existe déjà, utilisez la clause IF NOT EXISTS.
  • named_parameter : consiste en une paire param_name/param_type séparée par une virgule. La valeur de param_type est un type de données BigQuery. Pour une fonction SQL définie par l'utilisateur, la valeur de param_type peut également être ANY TYPE.
  • [RETURNS data_type] : spécifie le type de données renvoyé par la fonction.
    • Si la fonction est définie en SQL, la clause RETURNS est facultative. Si la clause RETURNS est omise, BigQuery déduit le type renvoyé par votre fonction du corps de la fonction SQL lors des appels par une requête.
    • Si la fonction est définie en JavaScript, la clause RETURNS est obligatoire. Pour plus d'informations sur les valeurs autorisées pour data_type, reportez-vous à la section Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur.
  • AS (sql_expression) : spécifie l'expression SQL qui définit la fonction.
  • [OPTIONS (library = library_array)] : pour une fonction définie par l'utilisateur écrite en JavaScript, spécifie un tableau de bibliothèques JavaScript à inclure dans la définition de la fonction.
  • AS javascript_code : spécifie la définition d'une fonction JavaScript. javascript_code correspond à une valeur littérale de chaîne.

Pour supprimer une fonction persistante définie par l'utilisateur, utilisez la syntaxe suivante :

DROP FUNCTION [IF EXISTS] [`project_name`.]dataset_name.function_name

Les fonctions temporaires définies par l'utilisateur expirent dès que la requête se termine et n'acceptent plus les instructions DROP FUNCTION, sauf dans les scripts et les procédures.

Structure des fonctions SQL définies par l'utilisateur

Vous pouvez créer des fonctions SQL définies par l'utilisateur à l'aide de la structure suivante :

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Paramètres modélisés des fonctions SQL définies par l'utilisateur

Un paramètre basé sur un modèle avec param_type = ANY TYPE peut correspondre à plusieurs types d'argument lorsque la fonction est appelée.

  • Si plusieurs paramètres ont le type ANY TYPE, BigQuery n'applique aucune relation entre ces arguments.
  • Le type renvoyé par la fonction ne peut pas être ANY TYPE. Elle doit être d'un type explicite ou bien omise, ce qui signifie qu'elle doit être déterminée automatiquement en fonction de sql_expression.
  • La transmission des arguments de fonction possédant des types incompatibles avec la définition de la fonction génère une erreur au moment de l'appel.

Exemples de fonctions SQL définies par l'utilisateur

L'exemple suivant montre une fonction définie par l'utilisateur qui emploie une fonction SQL :

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

L'exemple suivant décrit une fonction SQL définie par l'utilisateur qui exploite un paramètre modélisé. La fonction obtenue accepte des arguments de divers types.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);
SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

L'exemple suivant montre une fonction SQL définie par l'utilisateur qui emploie un paramètre modélisé pour renvoyer le dernier élément d'un tableau de n'importe quel type.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Structure des fonctions JavaScript définies par l'utilisateur

Vous pouvez créer des fonctions JavaScript définies par l'utilisateur à l'aide de la structure suivante :

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Exemples de fonctions JavaScript définies par l'utilisateur

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

Vous pouvez transmettre le résultat d'une fonction définie par l'utilisateur en tant qu'entrée d'une autre fonction définie par l'utilisateur. Exemple :

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x/2;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

L'exemple suivant additionne les valeurs de tous les champs nommés "foo" dans la chaîne JSON fournie.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Consultez la section Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur pour découvrir la façon dont les types de données BigQuery sont mappés aux types JavaScript.

Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur

Certains types SQL sont directement mappés à des types JavaScript, tandis que d'autres non. BigQuery représente les types de la manière suivante :

Type de données BigQuery Type de données JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING avec un encodage en base64
FLOAT64 NUMBER
NUMERIC Si une valeur NUMERIC peut être exactement représentée comme une valeur à virgule flottante au format IEEE 754 et ne comporte aucune fraction, elle est encodée au format NUMBER. Ces valeurs sont comprises dans la plage [-253, 253]. Dans le cas contraire, la valeur est encodée au format STRING.
STRING STRING
STRUCT Type OBJECT dans lequel chaque champ STRUCT est un champ nommé
TIMESTAMP Type DATE avec un champ de microseconde contenant la fraction microsecond de l'horodatage
DATE DATE

Comme JavaScript n'est pas compatible avec les types entiers de 64 bits, INT64 n'est pas accepté en tant que type d'entrée pour les fonctions JavaScript définies par l'utilisateur. Utilisez plutôt FLOAT64 pour représenter les valeurs entières sous forme de nombre, ou STRING pour les représenter sous forme de chaîne.

BigQuery accepte toutefois INT64 comme type renvoyé dans les fonctions JavaScript définies par l'utilisateur. Dans ce cas, le corps de la fonction JavaScript peut renvoyer un type NUMBER ou STRING JavaScript. BigQuery convertit ensuite l'un de ces types au format INT64.

Si la valeur renvoyée par la fonction JavaScript définie par l'utilisateur est Promise, BigQuery attend que l'objet Promise soit établi. Si l'état de Promise est "abouti", BigQuery renvoie son résultat. Si létat de Promise est "refusé", BigQuery renvoie une erreur.

Règles relatives aux guillemets

Vous devez placer le code JavaScript entre guillemets. Pour les extraits de code simples ne comprenant qu'une ligne, vous pouvez utiliser une chaîne standard entre guillemets :

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

Dans le cas où l'extrait contient des guillemets ou se compose de plusieurs lignes, utilisez des blocs entre guillemets triples :

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;
SELECT customGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;
+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

Inclure des bibliothèques JavaScript

Vous pouvez étendre vos fonctions JavaScript définies par l'utilisateur à l'aide de la section OPTIONS. Cette section vous permet de spécifier des bibliothèques de code externes pour la fonction définie par l'utilisateur.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
  )
  AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

Dans l'exemple précédent, le code dans lib1.js et lib2.js est disponible pour tout code de la section [external_code] de la fonction définie par l'utilisateur.

Fonctions définies par l'utilisateur et interface utilisateur Web

Vous pouvez utiliser l'UI Web de BigQuery pour exécuter des requêtes à l'aide d'une ou de plusieurs fonctions définies par l'utilisateur.

Exécuter une requête avec une fonction définie par l'utilisateur

  1. Cliquez sur le bouton SAISIR UNE REQUÊTE.
  2. Cliquez sur l'onglet Query Editor (Éditeur de requête).
  3. Cliquez sur le bouton Afficher les options.
  4. Décochez la case Utiliser l'ancien SQL.
  5. Saisissez l'instruction de fonction définie par l'utilisateur dans la zone de texte de l'éditeur de requête. Exemple :

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. Sous la déclaration de fonction définie par l'utilisateur, saisissez votre requête. Exemple :

    SELECT timesTwo(numbers) AS doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. Cliquez sur le bouton RUN QUERY (Exécuter la requête). Les résultats de la requête s'affichent sous les boutons.

Fonctions définies par l'utilisateur et outil de ligne de commande bq

L'outil de ligne de commande bq du SDK Google Cloud permet d'exécuter une requête contenant une ou plusieurs fonctions définies par l'utilisateur.

Utilisez la syntaxe suivante pour exécuter une requête avec une fonction définie par l'utilisateur :

bq query <statement_with_udf_and_query>

Bonnes pratiques relatives aux fonctions JavaScript définies par l'utilisateur

Préfiltrer votre entrée

Si votre entrée peut facilement être filtrée avant d'être transmise à une fonction JavaScript définie par l'utilisateur, votre requête sera probablement plus rapide et plus économique.

Éviter les états modifiables persistants

Veillez à ne pas stocker les états modifiables sur les appels de fonction JavaScript définie par l'utilisateur ni à y accéder.

Utiliser efficacement la mémoire

L'environnement de traitement JavaScript dispose d'une quantité de mémoire disponible limitée par requête. Les requêtes de fonction JavaScript définies par l'utilisateur qui accumulent trop d'états locaux peuvent échouer en raison de l'épuisement de la mémoire.

Limites

  • Quantité de données générées par votre fichier UDF JavaScript lors du traitement d'une seule ligne : environ 5 Mo ou moins.
  • Limite de débit simultané pour les requêtes en ancien SQL contenant des fonctions définies par l'utilisateur : 6 requêtes simultanées.
  • La limite de débit simultané pour les requêtes en ancien SQL contenant des fonctions définies par l'utilisateur inclut les requêtes interactives et les requêtes par lot. Les requêtes interactives contenant des fonctions définies par l'utilisateur sont également comptabilisées dans la limite de débit simultané pour les requêtes interactives. Cette limite ne s'applique pas aux requêtes en SQL standard.

  • Nombre maximal de ressources de fonctions JavaScript définies par l'utilisateur (blobs de code intégrés ou fichiers externes, par exemple) dans une tâche de requête : 50
  • Taille maximale de chaque blob de code intégré : 32 Ko
  • Taille maximale de chaque ressource de code externe : 1 Mo

Les limites suivantes s'appliquent aux fonctions persistantes définies par l'utilisateur.
  • Longueur maximale d'un nom de fonction : 256 caractères
  • Nombre maximal d'arguments : 256
  • Longueur maximale d'un nom d'argument : 128 caractères
  • Profondeur maximale d'une chaîne de référence de fonction définie par l'utilisateur : 16
  • Profondeur maximale d'un argument ou d'une sortie de type STRUCT : 15
  • Nombre maximal de champs dans un argument ou une sortie de type STRUCT par fonction définie par l'utilisateur : 1 024
  • Nombre maximal de fonctions définies par l'utilisateur uniques, plus références de table par requête : 1 000 Après extension complète, chaque fonction définie par l'utilisateur peut référencer jusqu'à 1 000 tables et fonctions définies par l'utilisateur uniques combinées.
  • Nombre maximal de bibliothèques JavaScript dans l'instruction CREATE FUNCTION : 50
  • Longueur maximale des chemins d'accès aux bibliothèques JavaScript incluses : 5 000 caractères
  • Fréquence maximale de mise à jour par fonction définie par l'utilisateur : 5 par intervalle de 10 secondes Après sa création, vous pouvez mettre à jour chaque fonction jusqu'à 5 fois par intervalle de 10 secondes.
  • Taille maximale de chaque blob de code intégré : 32 Ko
  • Taille maximale de chaque ressource de code JavaScript : 1 Mo

Limites

Les limites suivantes s'appliquent aux fonctions temporaires et persistantes définies par l'utilisateur :

  • Les objets DOM Window, Document et Node, ainsi que les fonctions qui les utilisent, ne sont pas compatibles.
  • Les fonctions JavaScript basées sur du code natif ne sont pas compatibles.
  • Une fonction JavaScript définie par l'utilisateur peut arriver à expiration et empêcher votre requête de se terminer. Les délais avant expiration peuvent ne durer que cinq minutes, mais ils varient en fonction de plusieurs facteurs, y compris le temps CPU utilisateur que consomme votre fonction, et la taille de vos entrées et sorties par rapport à la fonction JS.
  • En raison de leur nature non déterministe, les requêtes appelant des fonctions définies par l'utilisateur ne peuvent pas utiliser des résultats mis en cache.
  • Les opérations bit à bit en JavaScript ne traitent que les 32 bits les plus significatifs.
  • Vous ne pouvez pas référencer une table dans une fonction définie par l'utilisateur.

Les limites suivantes s'appliquent aux fonctions persistantes définies par l'utilisateur :

  • Chaque ensemble de données ne peut contenir qu'une seule fonction persistante définie par l'utilisateur portant le même nom. Cependant, vous pouvez créer une fonction définie par l'utilisateur dont le nom est identique au nom d'une table dans le même ensemble de données.
  • Lorsque vous référencez une fonction persistante définie par l'utilisateur depuis une autre fonction persistante définie par l'utilisateur, vous devez qualifier le nom à l'aide de l'ensemble de données. Par exemple :
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • Lorsque vous référencez une fonction persistante définie par l'utilisateur depuis une vue logique, vous devez qualifier le nom à l'aide du projet et de l'ensemble de données. Par exemple :
    CREATE VIEW mydataset.sample_view AS SELECT `my-project`.mydataset.referencedFunction();

Les limites suivantes s'appliquent aux fonctions temporaires définies par l'utilisateur.

  • Lorsque vous créez une fonction temporaire définie par l'utilisateur, function_name ne peut pas contenir de caractères "point".
  • Les vues logiques et les fonctions persistantes définies par l'utilisateur ne peuvent référencer des fonctions temporaires définies par l'utilisateur.