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 d'un autre langage de programmation, tel que JavaScript. Ces fonctions acceptent des colonnes d'entrée et effectuent des actions, renvoyant 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 sont temporaires. Cela signifie que vous ne pouvez les utiliser que pour la requête ou la session de ligne de commande actuelle. Lorsque vous créez une fonction définie par l'utilisateur à employer avec des requêtes en SQL standard, vous n'avez pas besoin d'accéder à l'onglet UDF Editor (Éditeur de fonction définie par l'utilisateur) de l'UI Web. Cet onglet est destiné à être utilisé avec l'ancien SQL.

Syntaxe générale des fonctions définies par l'utilisateur

Dans BigQuery, les fonctions définies par l'utilisateur exploitent la syntaxe générale suivante :

CREATE  { TEMPORARY | TEMP }  FUNCTION
  function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  { [LANGUAGE language AS """body"""] | [AS (function_definition)] };

named_parameter:
  param_name param_type

Cette syntaxe comprend les composants suivants :

  • CREATE { TEMPORARY | TEMP } FUNCTION : crée une fonction. Cette fonction peut contenir plusieurs paramètres nommés (named_parameter) ou aucun. Vous devez inclure TEMPORARY ou TEMP lors de la création d'une fonction définie par l'utilisateur.
  • named_parameter : consiste en une paire param_name/param_type séparée par des virgules. La valeur de param_type est un type de données BigQuery. Dans 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 et BigQuery infère le type de résultat à partir du corps de la fonction SQL. Si la fonction est définie dans un langage externe, la clause RETURNS est obligatoire. Consultez la section Types de données compatibles avec les fonctions définies par l'utilisateur pour en savoir plus sur les valeurs autorisées pour data_type.
  • [LANGUAGE language AS """body"""] : spécifie le langage externe de la fonction et le code qui la définit.
  • AS (function_definition) : spécifie le code SQL qui définit la fonction. function_definition est une expression SQL.

Structure des fonctions externes définies par l'utilisateur

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

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  [LANGUAGE language]
  AS external_code

Exemples de fonctions externes 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 créer plusieurs fonctions définies par l'utilisateur avant une requête. 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(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

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

Langages compatibles avec les fonctions externes définies par l'utilisateur

Les fonctions externes définies par l'utilisateur acceptent le code rédigé en JavaScript. Pour spécifier ce langage, indiquez js dans le champ LANGUAGE. Exemple :

CREATE TEMP FUNCTION greeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  return "Hello, " + a + "!";
  """;
SELECT greeting(name) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS name;

+----------------+
| everyone       |
+----------------+
| Hello, Hannah! |
| Hello, Max!    |
| Hello, Jakob!  |
+----------------+

Consultez la section Encodage des types SQL en JavaScript 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 externes définies par l'utilisateur

BigQuery accepte les types de données suivants pour les fonctions externes définies par l'utilisateur :

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

Encodage des types SQL en JavaScript

Certains types SQL sont directement mappés à des types JavaScript, tandis que d'autres non.

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.

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 OBJECT dans lequel chaque champ STRUCT est un champ nommé
TIMESTAMP DATE avec un champ de microseconde contenant la fraction microsecond de l'horodatage
DATE DATE

Règles relatives aux guillemets

Vous devez placer le code externe 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!  |
+-----------------------+

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  { TEMPORARY | TEMP }  FUNCTION 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 modélisé peut correspondre à plus d'un type d'argument au moment de l'appel de la fonction. Si une signature de fonction inclut un paramètre modélisé, BigQuery autorise les appels de fonction à transmettre l'un des types d'argument à la fonction.

Les signatures de fonction SQL définie par l'utilisateur peuvent contenir la valeur param_type modélisée suivante :

  • ANY TYPE : la fonction accepte une entrée de n'importe quel type pour cet argument. Si plusieurs paramètres possèdent le type ANY TYPE, BigQuery n'applique aucune relation entre ces arguments au moment de la création de la fonction. Toutefois, si vous transmettez des arguments de fonction possédant des types incompatibles avec la définition de la fonction, une erreur est générée lors 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     |
+------------+-----------+

Inclure des bibliothèques externes

Vous pouvez étendre vos fonctions externes 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 AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
"""
OPTIONS (
  library="gs://my-bucket/path/to/lib1.js",
  library=["gs://my-bucket/path/to/lib2.js", "gs://my-bucket/path/to/lib3.js"]
);

SELECT myFunc(3.14, 'foo');

Dans l'exemple précédent, le code de lib1.js, lib2.js et lib3.js est accessible par le code présent dans la section [external_code] de la fonction définie par l'utilisateur. Notez que vous pouvez spécifier des fichiers de bibliothèque à l'aide d'une syntaxe à élément unique ou à tableau.

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

Vous pouvez utiliser l'outil de ligne de commande bq du SDK Google Cloud pour 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

  • La quantité de données générées par votre fonction JavaScript définie par l'utilisateur lors du traitement d'une seule ligne ne doit pas excéder 5 Mo.
  • Chaque utilisateur est limité à l'exécution simultanée d'environ six requêtes de fonction JavaScript définie par l'utilisateur dans un projet spécifique. Si vous recevez une erreur indiquant que vous avez dépassé la limite de requêtes simultanées, attendez quelques minutes avant de réessayer.
  • 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.
  • Une tâche de requête peut contenir jusqu'à 50 ressources de fonction JavaScript définie par l'utilisateur (blobs de code intégrés ou fichiers externes).
  • Chaque blob de code en ligne est limité à une taille maximale de 32 Ko.
  • La taille maximale de chaque ressource de code externe est de 1 Mo.

Limites

  • Dans les fonctions temporaires définies par l'utilisateur, le paramètre function_name ne peut pas comprendre de point.
  • 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.
  • Les opérations bit à bit en JavaScript ne traitent que les 32 bits les plus significatifs.
  • 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.
  • Vous ne pouvez pas référencer une table dans une fonction définie par l'utilisateur.
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.