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 GoogleSQL. Pour en savoir plus sur les fonctions définies par l'utilisateur en GoogleSQL, consultez la page Fonctions définies par l'utilisateur en GoogleSQL.
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.
Pour en savoir plus sur les fonctions définies par l'utilisateur en GoogleSQL, consultez la page Fonctions définies par l'utilisateur en GoogleSQL.
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
);
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: ligne de saisie.emit: hook utilisé par BigQuery pour collecter des données de sortie. La fonctionemitutilise un seul paramètre : un objet JavaScript qui représente une seule ligne de données de sortie. La fonctionemitpeut ê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 génère 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 facultatif mode, 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
);
Exécuter une requête avec une fonction définie par l'utilisateur
Vous pouvez utiliser les fonctions définies par l'utilisateur en ancien SQL avec l'outil de ligne de commande bq ou l'API BigQuery. La console Google Cloud n'est pas compatible avec les fonctions définies par l'utilisateur en ancien SQL.
Utiliser l'outil de ligne de commande bq
Pour exécuter une requête contenant une ou plusieurs fonctions définies par l'utilisateur, spécifiez l'option --udf_resource dans l'outil de ligne de commande bq de la Google Cloud CLI. La valeur de l'option peut être un URI Cloud Storage (gs://...) ou le chemin d'accès à un fichier local. Pour spécifier plusieurs fichiers de ressources de fonctions définies par l'utilisateur, répétez cette option.
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 définie par l'utilisateur ci-dessous, 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 et coller l'instruction BigQuery suivante dans le 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 sous le nom de fichier query.sql et référence la fonction utilisée par l'utilisateur que vous avez créée.
$ bq query --udf_resource=urldecode.js "$(cat query.sql)"
Utiliser l'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 du 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);"
}
}
}
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 la table entière, 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.
Le caractère SELECT * FROM <UDF name>(...) n'est pas accepté.
Pour examiner la structure des données de ligne d'entrée, vous pouvez utiliser JSON.stringify() pour émettre une colonne de sortie de chaîne :
bigquery.defineFunction(
'examineInputFormat',
['some', 'input', 'columns'],
[{name: 'input', type: 'string'}],
function(r, emit) {
emit({input: JSON.stringify(r)});
}
);
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.
Limites
- Les objets DOM
Window,DocumentetNode, 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.