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 fonction SQL temporaire définie par l'utilisateur nommée AddFourAndDivide et appelle cette fonction définie par l'utilisateur à partir d'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 contenant l'UDF.

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. Les fonctions JavaScript définies par l'utilisateur consomment généralement plus de ressources d'emplacements que les requêtes SQL standards, ce qui réduit les performances des tâches. Si la fonction peut être exprimée en SQL, il est souvent plus optimal d'exécuter le code en tant que tâche de requête SQL standard.

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 ou BIGNUMERIC 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, cette valeur est encodée sous forme de chaîne.
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
JSON

Les OBJECTS, ARRAY et VALUES JSON sont convertis en OBJECTS, ARRAY et VALUES JavaScript équivalents.

JavaScript n'est pas compatible avec les valeurs INT64. Seuls les nombres JSON compris dans la plage [-253, 253] sont convertis exactement. Sinon, la valeur numérique est arrondie, ce qui peut entraîner une perte de précision.

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 l'objet Promise, jusqu'à ce que cet 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 d'insertion

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 les 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 être filtrée avant d'être transmise à une fonction JavaScript définie par l'utilisateur, la requête sera susceptible d'être 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.

Autoriser les routines

Vous pouvez autoriser les fonctions définies par l'utilisateur (UDF) en tant que routines. Les routines autorisées vous permettent de partager des résultats de requête avec des utilisateurs ou des groupes spécifiques, sans leur donner accès aux tables sous-jacentes qui ont généré les résultats. Par exemple, une routine 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 section Routines 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 la console Google Cloud.

    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 Exécuter une requête interactive.

Créer des routines de masquage personnalisées

Vous pouvez créer des fonctions définies par l'utilisateur destinées à être associées à des routines de masquage personnalisées. Les routines de masquage personnalisées doivent répondre aux exigences suivantes :

  • La routine de masquage personnalisée doit être une fonction SQL définie par l'utilisateur.
  • Dans la fonction OPTIONS, l'option data_governance_type doit être définie sur DATA_MASKING.
  • Les routines de masquage personnalisées sont compatibles avec les fonctions suivantes :
  • Les routines de masquage personnalisées peuvent accepter soit aucune entrée, soit une entrée, correspondant aux types de données BigQuery, à l'exception de GEOGRAPHY et STRUCT. GEOGRAPHY et STRUCT ne sont pas compatibles avec les routines de masquage personnalisées.
  • Les paramètres modélisés des fonctions SQL définies par l'utilisateur ne sont pas acceptés.
  • Lorsqu'une entrée est fournie, les types de données d'entrée et de sortie doivent être identiques.
  • Vous devez indiquer un type de sortie.
  • Aucune autre fonction définie par l'utilisateur, sous-requête, table ou vue ne peut être référencée dans le corps de la définition.
  • Une fois que vous avez créé une routine de masquage, celle-ci ne peut pas être transformée en fonction standard. Cela signifie que si l'option data_governance_type est définie sur DATA_MASKING, vous ne pouvez pas modifier data_governance_type à l'aide d'instructions LDD ou d'appels d'API.

Par exemple, une routine de masquage qui remplace le numéro de sécurité sociale d'un utilisateur par XXX-XX-XXXX peut se présenter comme suit :

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

L'exemple suivant effectue le hachage avec le salage fourni par l'utilisateur, à l'aide de la fonction SHA256 :

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

L'exemple suivant masque une colonne DATETIME avec une valeur constante :

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

Nous vous recommandons d'utiliser le préfixe SAFE dans la mesure du possible pour éviter d'exposer des données brutes via des messages d'erreur.

Une fois que vous avez créé la routine de masquage personnalisée, celle-ci est disponible en tant que règle de masquage dans Créer des stratégies de données.

Fonctions issues de la communauté

Les fonctions définies par l'utilisateur issues de la communauté sont disponibles dans l'ensemble de données public bigquery-public-data.persistent_udfs et dans le dépôt GitHub Open Source bigquery-utils. Vous pouvez voir tous lesfonctions définies par l'utilisateur issues de la communauté dans la console Google Cloud en ajoutant aux favoris 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.