Fonctions définies par l'utilisateur en ancien SQL

Ce document explique en détail comment utiliser les fonctions JavaScript définies par l'utilisateur dans une syntaxe de requête en ancien SQL. La syntaxe de requête privilégiée pour BigQuery est le langage SQL standard. Consultez la section Fonctions définies par l'utilisateur en SQL standard pour en savoir plus sur celles-ci.

L'ancien SQL BigQuery est compatible avec les fonctions définies par l'utilisateur écrites en JavaScript. Une fonction de ce type se comporte de la même manière que la fonction "Map" dans MapReduce : elle utilise une seule ligne d'entrée et produit zéro ou plusieurs lignes de sortie. En outre, la sortie peut présenter un schéma différent de celui de l'entrée.

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

Exemple de fonction définie par l'utilisateur

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Haut de page

Structure des fonctions définies par l'utilisateur

function name(row, emit) {
  emit(<output data>);
}

Les fonctions définies par l'utilisateur de BigQuery s'exécutent sur des lignes individuelles d'une table ou sur les résultats d'une sous-requête. Elles comprennent deux paramètres formels :

  • row : une ligne d'entrée.
  • emit : un hook utilisé par BigQuery pour collecter des données de sortie. La fonction emit utilise un seul paramètre : un objet JavaScript qui représente une seule ligne de données de sortie. La fonction emit peut être appelée plusieurs fois, par exemple dans une boucle pour générer plusieurs lignes de données.

L'exemple de code suivant illustre une fonction définie par l'utilisateur basique :

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

Enregistrer une fonction définie par l'utilisateur

Vous devez enregistrer un nom pour votre fonction afin de pouvoir l'appeler depuis BigQuery SQL. Le nom enregistré ne doit pas nécessairement correspondre au nom que vous avez utilisé pour votre fonction en JavaScript.

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

Colonnes d'entrée

Les noms des colonnes d'entrée doivent correspondre aux noms (ou alias, le cas échéant) des colonnes de la table d'entrée ou de la sous-requête.

En ce qui concerne les colonnes d'entrée qui sont des enregistrements, vous devez spécifier dans la liste des colonnes d'entrée les champs feuilles auxquels vous souhaitez accéder depuis l'enregistrement.

Par exemple, si vous possédez un enregistrement qui comprend le nom et l'âge d'une personne :

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

L'indicateur d'entrée pour le nom et l'âge s'afficherait de la manière suivante :

['person.name', 'person.age']

L'utilisation de ['person'] sans le nom ou l'âge engendrerait une erreur.

Le résultat correspond au schéma : vous obtenez un tableau d'objets JavaScript, chacun possédant une propriété "name" et une propriété "age". Exemple :

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

Schéma de sortie

Vous devez fournir à BigQuery le schéma ou la structure des enregistrements produits par votre fonction définie par l'utilisateur, au format JSON. Le schéma peut contenir tous les types de données BigQuery compatibles, y compris des enregistrements imbriqués. Les indicateurs de type compatibles sont les suivants :

  • boolean (booléen)
  • float (nombre à virgule flottante)
  • integer (entier)
  • record (enregistrement)
  • string (chaîne)
  • timestamp (horodatage)

L'exemple de code ci-dessous montre la syntaxe des enregistrements dans le schéma de sortie. Chaque champ de sortie nécessite un attribut name et type. Les champs imbriqués doivent également contenir un attribut fields.

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

Chaque champ peut contenir un attribut mode facultatif, qui est compatible avec les valeurs suivantes :

  • nullable : il s'agit de la valeur par défaut, qui peut être omise.
  • required : si spécifié, le champ donné doit être défini sur une valeur et ne peut pas être indéterminé.
  • repeat : si spécifié, le champ donné doit être un tableau.

Les lignes transmises à la fonction emit() doivent correspondre aux types de données du schéma de sortie. Les champs représentés dans le schéma de sortie qui sont omis dans la fonction "emit" seront affichés avec la valeur "null".

Définir ou référencer une fonction définie par l'utilisateur

Si vous préférez, vous pouvez définir la fonction définie par l'utilisateur de manière intégrée dans bigquery.defineFunction. Exemple :

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

Vous pouvez également définir la fonction définie par l'utilisateur séparément et transmettre une référence à la fonction dans bigquery.defineFunction. Exemple :

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Traitement des erreurs

Si une exception ou une erreur sont renvoyées pendant le traitement d'une fonction définie par l'utilisateur, la requête entière n'aboutit pas. Vous pouvez utiliser un bloc try-catch pour le traitement des erreurs. Exemple :

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

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

Vous pouvez utiliser l'interface utilisateur Web de BigQuery pour ajouter des fonctions définies par l'utilisateur avec lesquelles exécuter des requêtes.

Prérequis

Pour utiliser l'interface utilisateur Web de BigQuery, votre compte doit avoir accès à un projet compatible avec BigQuery dans la console Google Cloud Platform.

  1. Si vous ne l'avez encore jamais utilisée, accédez à la console, acceptez les conditions d'utilisation et créez un projet.

  2. Accédez à l'interface utilisateur Web de BigQuery.

Ajouter la fonction définie par l'utilisateur

  1. Cliquez sur le bouton COMPOSE QUERY (Saisir une requête).

  2. Cliquez sur l'onglet UDF Editor (Éditeur de fonction définie par l'utilisateur) pour ajouter la fonction.

  3. Copiez et collez le code suivant dans la zone de texte correspondante :

    // The UDF
    function urlDecode(row, emit) {
      emit({title: decodeHelper(row.title),
            requests: row.num_requests});
    }
    
    // Helper function for error handling
    function decodeHelper(s) {
      try {
        return decodeURI(s);
      } catch (ex) {
        return s;
      }
    }
    
    // UDF registration
    bigquery.defineFunction(
      'urlDecode',  // Name used to call the function from SQL
    
      ['title', 'num_requests'],  // Input column names
    
      // JSON representation of the output schema
      [{name: 'title', type: 'string'},
       {name: 'requests', type: 'integer'}],
    
      urlDecode  // The function reference
    );
    

Exécuter une requête dans l'interface utilisateur Web

  • Cliquez sur l'onglet Query Editor (Éditeur de requête).

  • Copiez et collez la requête suivante dans la zone de texte correspondante.

    #legacySQL
    SELECT requests, title
    FROM
      urlDecode(
        SELECT
          sum(requests) AS num_requests
        FROM
          [fh-bigquery:wikipedia.pagecounts_201504]
        WHERE language = 'fr'
        GROUP BY title
      )
    WHERE title LIKE '%ç%'
    ORDER BY requests DESC
    LIMIT 100
    

    La requête ci-dessus recherche les articles Wikipédia en français avec une cédille (ç) dans le titre les plus consultés en avril 2015.

  • Cliquez sur le bouton RUN QUERY (Exécuter la requête). Les résultats de la requête s'affichent sous les boutons.

Référencer un code depuis Cloud Storage

Dans l'exemple ci-dessus, vous avez ajouté la fonction définie par l'utilisateur directement dans l'interface utilisateur Web de BigQuery. Vous pouvez également stocker tout ou partie de votre code JavaScript dans Cloud Storage. Quel que soit l'emplacement du code des fonctions définies par l'utilisateur, celles-ci doivent toutes être enregistrées à l'aide d'un appel de bigquery.defineFunction dans votre code. L'appel de bigquery.definefunction peut être fourni dans l'interface utilisateur Web de BigQuery ou dans des ressources de code à distance. Les fichiers source à distance doivent avoir une extension ".js".

Par exemple, vous pouvez gérer des bibliothèques tierces, votre propre code de fonctions définies par l'utilisateur et des appels de fonctions d'enregistrement dans des fichiers distincts. Ces fichiers seraient chargés en tant que ressource externe distincte dans votre requête.

Référencer une fonction définie par l'utilisateur externe dans l'interface utilisateur Web de BigQuery

  1. Cliquez sur le bouton Show options (Afficher les options) sous la zone de texte.

  2. Cliquez sur le bouton Edit (Modifier) à côté de l'en-tête UDF Source URIs (URI source de la fonction définie par l'utilisateur).

  3. Pour chaque fichier source à distance, cliquez sur le bouton Add UDF Source URI (Ajouter un URI source de fonction définie par l'utilisateur) et renseignez l'URI Cloud Storage.

    Si vous souhaitez essayer l'exemple de décodage d'URL précédent à l'aide d'une fonction définie par l'utilisateur externe, collez bigquery-sandbox-udf/url_decode.js dans le champ "URI". Une fois ces étapes accomplies, assurez-vous de supprimer le contenu de la zone UDF Editor (Éditeur de fonction définie par l'utilisateur).

  4. Cliquez sur le bouton OK.

Vous pouvez ensuite basculer vers l'onglet Query Editor (Éditeur de requête) et suivre les mêmes étapes que dans l'exemple ci-dessus pour utiliser la fonction définie par l'utilisateur dans une requête.

En règle générale, lorsque vous utilisez des fonctions externes définies par l'utilisateur, vous pouvez également ajouter du code JavaScript supplémentaire dans la zone de texte UDF Editor (Éditeur de fonction définie par l'utilisateur), à condition que ces fonctions soient enregistrées dans un bloc defineFunction de l'interface utilisateur Web ou dans un fichier externe.

Vous pouvez également utiliser l'outil de ligne de commande bq pour référencer une fonction définie par l'utilisateur stockée dans Cloud Storage. Consulter la section Fonctions définies par l'utilisateur et outil de ligne de commande bq pour en savoir plus.

Haut de page

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 Cloud pour exécuter une requête contenant une ou plusieurs fonctions définies par l'utilisateur, en spécifiant l'indicateur --udf_resource. La valeur de l'indicateur peut être un URI Cloud Storage (gs://...) ou un chemin d'accès vers un fichier local. Vous pouvez répéter cet indicateur pour spécifier plusieurs fichiers de ressources de 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 --udf_resource=<file_path_or_URI> <sql_query>

Cet exemple exécute une requête reprenant une fonction définie par l'utilisateur stockée dans un fichier local, ainsi qu'une requête SQL également stockée dans un fichier local.

Créer la fonction définie par l'utilisateur

Vous pouvez stocker la fonction définie par l'utilisateur dans Cloud Storage ou en tant que fichier texte local. Par exemple, pour stocker la fonction urlDecode abordée dans la section Fonctions définies par l'utilisateur et interface utilisateur Web, créez un fichier nommé urldecode.js et collez le code JavaScript suivant dans le fichier avant de l'enregistrer.

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Créer la requête

Vous pouvez également stocker la requête dans un fichier pour éviter que votre ligne de commande ne devienne trop détaillée. Par exemple, vous pouvez créer un fichier local nommé query.sql, puis coller l'instruction BigQuery suivante dans ce fichier :

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

Une fois le fichier enregistré, vous pouvez le référencer sur la ligne de commande.

Exécuter la requête

Après avoir spécifié la fonction définie par l'utilisateur et la requête dans des fichiers distincts, vous pouvez les référencer dans la ligne de commande. Par exemple, la commande suivante exécute la requête que vous avez enregistrée en tant que fichier nommé query.sql et référence la fonction définie par l'utilisateur que vous avez créée :

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

Fonctions définies par l'utilisateur et API BigQuery

configuration.query

Les requêtes qui utilisent des fonctions définies par l'utilisateur doivent contenir des éléments userDefinedFunctionResources qui fournissent le code ou les emplacements de ressources à utiliser dans la requête. Le code fourni doit inclure des appels de fonction d'enregistrement pour toutes les fonctions définies par l'utilisateur référencées par la requête.

Ressources de code

La configuration de vos requêtes peut inclure des blobs de code JavaScript, ainsi que des références à des fichiers sources JavaScript stockés dans Cloud Storage.

Les blobs de code JavaScript intégrés sont renseignés dans la section inlineCode d'un élément userDefinedFunctionResource. Toutefois, le code qui sera réutilisé ou référencé dans plusieurs requêtes doit être conservé dans Cloud Storage et être référencé en tant que ressource externe.

Pour référencer un fichier source JavaScript à partir de Cloud Storage, définissez la section resourceURI de l'élément userDefinedFunctionResource sur l'URI gs:// du fichier.

La configuration de la requête peut contenir plusieurs éléments userDefinedFunctionResource. Chaque élément peut contenir soit une section inlineCode, soit une section resourceUri.

Exemple

L'exemple JSON suivant illustre une requête qui référence deux ressources de fonction définie par l'utilisateur : un blob de code intégré et un fichier lib.js à lire depuis Cloud Storage. Dans l'exemple ci-dessous, myFunc et l'invocation d'enregistrement pour myFunc sont fournis par lib.js.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

Haut de page

Bonnes pratiques

Développer votre fonction définie par l'utilisateur

Vous pouvez utiliser notre outil de test de fonction définie par l'utilisateur pour tester et déboguer votre fonction sans augmenter votre facture BigQuery.

Préfiltrer votre entrée

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

Dans l'exemple d'exécution d'une requête, une sous-requête est transmise en tant qu'entrée pour urlDecode, au lieu d'une table complète. La table [fh-bigquery:wikipedia.pagecounts_201504] contient environ 5,6 milliards de lignes. Pour exécuter la fonction définie par l'utilisateur sur l'intégralité de la table, le framework JavaScript devrait traiter plus de 21 fois plus de lignes qu'avec la sous-requête filtrée.

Éviter les états modifiables persistants

Veillez à ne pas stocker les états modifiables sur les appels de fonction définie par l'utilisateur ni y accéder. L'exemple de code suivant décrit ce scénario :

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit(rowCount: ++numRows);
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

L'exemple ci-dessus ne va pas se comporter comme prévu, car BigQuery partitionne votre requête sur plusieurs nœuds. Chaque nœud possède un environnement de traitement JavaScript autonome qui accumule des valeurs distinctes pour numRows.

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 définie par l'utilisateur qui accumulent trop d'états locaux peuvent échouer en raison de l'épuisement de la mémoire.

Développer les requêtes de sélection

Vous devez explicitement répertorier les colonnes sélectionnées dans une fonction définie par l'utilisateur. SELECT * FROM <UDF name>(...) n'est pas compatible.

Afin d'examiner la structure des données de la ligne d'entrée, vous pouvez utiliser JSON.stringify() pour émettre une colonne de sortie de type chaîne :

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

Haut de page

Limites

  • La quantité de données générées par votre fonction 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 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 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 définie par l'utilisateur (blobs de code intégrés ou fichiers externes).
  • La taille maximale de chaque blob de code intégré est de 32 Ko. Pour utiliser des ressources plus importantes, stockez votre code dans Cloud Storage et référencez-le en tant que ressource externe.
  • La taille maximale de chaque ressource de code externe est de 1 Mo.
  • La taille maximale cumulée de toutes les ressources de code externes est de 5 Mo.

Haut de page

Limites

  • 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.

Haut de page

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

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