Fonctions définies par l'utilisateur en SQL standard

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 disponible que pour les UDF temporaires dans les scripts 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_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

Cet exemple génère la sortie suivante :

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 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 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

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;

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

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

Une fonction définie par l'utilisateur autorisée est une fonction définie par l'utilisateur autorisée à accéder à un ensemble de données particulier. Elle permet d'interroger des tables dans l'ensemble de données, même si l'utilisateur qui appelle la fonction définie par l'utilisateur n'a pas accès à ces tables.

Les fonctions définies par l'utilisateur 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 définie par l'utilisateur 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 autoriser une fonction définie par l'utilisateur, vous pouvez utiliser Google Cloud Console, l'API REST ou l'outil de ligne de commande bq :

Console

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

    Accéder à BigQuery

  2. Dans la section Ressources du panneau de navigation, développez votre projet et sélectionnez un ensemble de données.

  3. Dans le panneau des détails, cliquez sur Autoriser les routines.

  4. Sur la page Routines autorisées, dans la section Autoriser la routine, sélectionnez l'ID du projet, l'ID de l'ensemble de données et l'ID de routine pour la fonction définie par l'utilisateur que vous souhaitez autoriser.

  5. Cliquez sur Ajouter une autorisation.

API

  1. Appelez la méthode datasets.get pour récupérer l'ensemble de données auquel la fonction définie par l'utilisateur doit accéder. Le corps de la réponse contient une représentation de la ressource Dataset.

  2. Ajoutez l'objet JSON suivant au tableau access de la ressource Dataset :

    {
     "routine": {
       "datasetId": "DATASET_NAME",
       "projectId": "PROJECT_ID",
       "routineId": "ROUTINE_NAME"
     }
    }
    

    Où :

    • DATASET_NAME correspond au nom de l'ensemble de données contenant la fonction définie par l'utilisateur.
    • PROJECT_ID est l'ID du projet contenant la fonction définie par l'utilisateur.
    • ROUTINE_NAME est le nom de la fonction définie par l'utilisateur.
  3. Appelez la méthode dataset.update avec la représentation Dataset modifiée.

bq

  1. Utilisez la commande bq show pour obtenir la représentation JSON de l'ensemble de données auquel la fonction définie par l'utilisateur doit accéder. Le résultat de la commande est une représentation JSON de la ressource Dataset. Enregistrez le résultat dans un fichier local.

    bq show --format=prettyjson TARGET_DATASET > dataset.json
    

    Remplacez TARGET_DATASET par le nom de l'ensemble de données auquel la fonction définie par l'utilisateur aura accès.

  2. Modifiez le fichier pour ajouter l'objet JSON suivant au tableau access de la ressource Dataset :

    {
     "routine": {
       "datasetId": "DATASET_NAME",
       "projectId": "PROJECT_ID",
       "routineId": "ROUTINE_NAME"
     }
    }
    

    Où :

    • DATASET_NAME correspond au nom de l'ensemble de données contenant la fonction définie par l'utilisateur.
    • PROJECT_ID est l'ID du projet contenant la fonction définie par l'utilisateur.
    • ROUTINE_NAME est le nom de la fonction définie par l'utilisateur.
  3. Exécutez la commande bq update pour mettre à jour l'ensemble de données.

    bq update --source dataset.json TARGET_DATASET
    

Exemple de fonctions définies par l'utilisateur autorisées

Voici un exemple de bout en bout de la création et de l'utilisation d'une fonction définie par l'utilisateur autorisée.

  1. Créez deux ensembles de données nommés private_dataset et public_dataset. Pour en savoir plus sur la création d'un ensemble de données, consultez la page Créer un ensemble de données.

  2. Exécutez l'instruction suivante pour créer une table nommée private_table dans private_dataset :

    CREATE OR REPLACE TABLE private_dataset.private_table
    AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;
    
  3. Exécutez l'instruction suivante pour créer une fonction définie par l'utilisateur nommée count_key dans public_dataset. La fonction définie par l'utilisateur inclut une instruction SELECT sur private_table.

    CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING)
    RETURNS INT64
    AS
    ((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));
    
  4. Attribuez le rôle bigquery.dataViewer à un utilisateur sur l'ensemble de données public_dataset. Ce rôle inclut l'autorisation bigquery.routines.get, qui permet à l'utilisateur d'appeler la fonction. Pour en savoir plus sur l'attribution de contrôles d'accès à un ensemble de données, consultez la page Contrôler l'accès aux ensembles de données.

  5. À ce stade, l'utilisateur est autorisé à appeler la fonction count_key, mais ne peut pas accéder à la table dans private_dataset. Si l'utilisateur tente d'appeler la fonction, il reçoit un message d'erreur semblable à celui-ci :

    Access Denied: Table myproject:private_dataset.private_table: User does
    not have permission to query table myproject:private_dataset.private_table.
    
  6. À l'aide de l'outil de ligne de commande bq, exécutez la commande show comme suit :

    bq show --format=prettyjson private_dataset > dataset.json
    

    Le résultat est enregistré dans un fichier local nommé dataset.json.

  7. Modifiez dataset.json pour ajouter l'objet JSON suivant au tableau access :

    {
     "routine": {
       "datasetId": "public_dataset",
       "projectId": "PROJECT_ID",
       "routineId": "count_key"
     }
    }
    

    Remplacez PROJECT_ID par l'ID du projet public_dataset.

  8. À l'aide de l'outil de ligne de commande bq, exécutez la commande update comme suit :

    bq update --source dataset.json private_dataset
    
  9. Pour vérifier que la fonction définie par l'utilisateur a accès à private_dataset, l'utilisateur peut exécuter la requête suivante :

    SELECT public_dataset.count_key('key1');
    

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 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 l'icône en forme de crayon à côté de l'onglet Description 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 Mettre à jour pour enregistrer la nouvelle description.

Vous pouvez également utiliser une requête SQL standard pour mettre à jour la description à l'aide du paramètre description du champ OPTIONS. Dans la zone Éditeur de requête, saisissez la définition de votre fonction, puis ajoutez la ligne suivante :

OPTIONS (description="DESCRIPTION") AS """

Remplacez DESCRIPTION par la description que vous souhaitez ajouter.

bq

En utilisant la syntaxe bq query des UDF et de l'outil de ligne de commande bq, vous pouvez modifier la description d'une fonction à partir de la ligne de commande. Spécifiez le langage SQL standard avec une option --nouse_legacy_sql ou -- use_legacy_sql=false, puis saisissez la définition de votre fonction. Ajoutez la ligne suivante à votre définition pour définir le paramètre description dans le champ OPTIONS :

OPTIONS (description="DESCRIPTION") AS """

Remplacez DESCRIPTION par la description que vous souhaitez ajouter.

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.