Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.

Fonctions définies par l'utilisateur

Une fonction définie par l'utilisateur (UDF, user-defined function) permet de créer une fonction à l'aide d'une expression SQL ou d'un code JavaScript. Une UDF accepte des colonnes d'entrée, effectue des actions sur l'entrée et renvoie le résultat de ces actions sous forme de valeur.

Vous pouvez définir une UDF comme persistante ou temporaire. Vous pouvez réutiliser une UDF persistante dans plusieurs requêtes, tandis que les UDF temporaires n'existent que dans le champ d'application d'une seule requête.

Pour créer une UDF, utilisez l'instruction CREATE FUNCTION. Pour supprimer une fonction persistante définie par l'utilisateur, utilisez l'instruction DROP FUNCTION. Les UDF temporaires expirent dès que la requête se termine. L'instruction DROP FUNCTION n'est compatible qu'avec les UDF temporaires dans les requêtes multi-instructions et les procédures.

Consultez la section Fonctions définies par l'utilisateur en ancien SQL pour en savoir plus sur celles-ci.

Fonctions SQL définies par l'utilisateur

L'exemple suivant crée une UDF SQL temporaire nommée AddFourAndDivide et l'appelle depuis une instruction SELECT :

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

Cet exemple génère la sortie suivante :

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

L'exemple suivant crée la même fonction qu'une UDF persistante :

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

Comme cette UDF est persistante, vous devez spécifier un ensemble de données pour la fonction (mydataset dans cet exemple). Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez appeler la fonction à partir d'une requête :

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

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

Un paramètre de type égal à 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 de type entre ces arguments.
  • Le type renvoyé par la fonction ne peut pas être ANY TYPE. Elle doit être omise, ce qui signifie qu'elle doit être déterminée automatiquement en fonction de sql_expression, ou d'un type explicite.
  • 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.

L'exemple suivant décrit une UDF SQL qui exploite un paramètre modélisé.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

Cet exemple génère la sortie suivante :

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

L'exemple suivant utilise 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
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

Cet exemple génère la sortie suivante :

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Sous-requêtes scalaires

Une UDF SQL peut renvoyer la valeur d'une sous-requête scalaire. Une sous-requête scalaire doit sélectionner une seule colonne.

L'exemple suivant montre une UDF SQL qui emploie une sous-requête scalaire pour compter le nombre d'utilisateurs ayant un âge donné dans une table d'utilisateurs.

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

Cet exemple génère la sortie suivante :

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Projet par défaut dans les expressions SQL

Dans le corps d'une UDF SQL, les références aux entités BigQuery, telles que les tables ou les vues, doivent inclure l'ID du projet, sauf si l'entité réside dans le projet qui exécute la fonction CREATE FUNCTION.

Prenons l'exemple de l'instruction suivante :

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

Si vous exécutez cette instruction à partir de project1 et que mydataset.mytable existe dans project1, l'instruction réussit. Toutefois, si vous exécutez cette instruction à partir d'un autre projet, l'instruction échoue. Pour corriger l'erreur, incluez l'ID du projet dans la référence de la table :

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

Vous pouvez également référencer une entité dans un projet ou un ensemble de données différent de celui dans lequel vous créez la fonction :

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

Fonctions JavaScript définies par l'utilisateur

Une UDF JavaScript vous permet d'appeler le code écrit en JavaScript à partir d'une requête SQL.

L'exemple suivant décrit une UDF JavaScript. Le code JavaScript est placé dans une chaîne brute.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  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;

Cet exemple génère la sortie suivante :

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

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 r"""
  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;

L'exemple produit la sortie suivante :

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

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, BIGNUMERIC 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;

Cet exemple génère la sortie suivante :

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

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 r"""
  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;

Cet exemple génère la sortie suivante :

+-----------------------+
| 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 l'UDF.

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 r"""
  // 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.

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. Par exemple, évitez le modèle suivant :

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

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 UDF JavaScript qui accumulent trop d'état local peuvent échouer en raison de l'épuisement de la mémoire.

Fonctions définies par l'utilisateur autorisées

Les fonctions autorisées vous permettent de partager les résultats d'une requête avec certains utilisateurs ou groupes sans leur donner accès aux tables sous-jacentes. Par exemple, une fonction autorisée peut calculer une agrégation sur des données ou rechercher une valeur de table et utiliser cette valeur dans un calcul.

Pour en savoir plus, consultez la page Créer des fonctions autorisées.

Ajouter des descriptions aux fonctions définies par l'utilisateur

Pour ajouter une description à une UDF, procédez comme suit :

Console

  1. Accédez à la page BigQuery de Google Cloud Console.

    Accéder à BigQuery

  2. Dans le panneau Explorateur, développez votre projet et votre ensemble de données, puis sélectionnez la fonction.

  3. Dans le volet Détails, cliquez sur Modifier les détails de la routine pour modifier la description.

  4. Dans la boîte de dialogue, saisissez une description dans la zone de texte ou modifiez la description existante. Cliquez sur Enregistrer pour enregistrer la nouvelle description.

SQL

Pour mettre à jour la description d'une fonction, recréez votre fonction à l'aide de l'instruction LDD CREATE FUNCTION et définissez le champ description dans la liste OPTIONS :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE OR REPLACE FUNCTION mydataset.my_function(...)
    AS (
      ...
    ) OPTIONS (
      description = 'DESCRIPTION'
    );
    

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez la page Exécuter des requêtes interactives.

Fonctions issues de la communauté

Les fonctions définies par l'utilisateur sont disponibles dans l'ensemble de données public bigquery-public-data.persistent_udfs et dans le dépôt GitHub bigquery-utils Open Source. Vous pouvez voir tous lesFonctions définies par l'utilisateur dans la console Google Cloud en épinglant le projet bigquery-public-data dans le volet Explorateur, puis en développant l'ensemble de données imbriqué persistent_udfs dans ce projet.

Si vous souhaitez contribuer aux fonctions définies par l'utilisateur de ce dépôt, consultez la section Contribuer aux fonctions définies par l'utilisateur pour obtenir des instructions.

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 peuvent échouer, par exemple, si elles effectuent des appels système restreints.
  • 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 JavaScript.
  • Les opérations bit à bit en JavaScript ne traitent que les 32 bits les plus significatifs
  • Les fonctions définies par l'utilisateur sont soumises à certaines limites de débit et de quota. Pour en savoir plus, consultez la section Limites relatives aux fonctions définies 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 UDF persistante depuis une autre UDF persistante ou une vue logique, vous devez qualifier le nom à l'aide de l'ensemble de données. Par exemple :
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

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

  • Lorsque vous créez une UDF temporaire, function_name ne peut pas contenir de caractères "point".
  • Les vues et les UDF persistantes ne peuvent pas référencer des UDF temporaires.