Cette page a été traduite par l'API Cloud Translation.

Présentation des routines

Ce document explique comment choisir une routine, qui est un type de ressource que vous utilisez pour créer des fonctions ou des procédures stockées dans BigQuery.

Routines compatibles

BigQuery accepte les routines suivantes:

Choisir une routine

Cette section décrit les facteurs à prendre en compte lors du choix d'une routine et compare les routines par tâche.

Facteurs à prendre en compte

Pour choisir une routine, tenez compte des facteurs suivants, qui sont décrits dans les sections de chaque type de routine:

Type de tâche à implémenter.
Langage de programmation à utiliser.
Type de persistance à implémenter pour la routine: temporaire ou persistante.
Type de réutilisation requis pour la routine: pour une ou plusieurs requêtes.
Considérations sur les performances
Accéder à des services externes
Partager la routine avec les utilisateurs

Comparer les routines par tâche

Le tableau suivant indique le type de tâches que vous pouvez effectuer pour chaque type de routine:

Tâche	Type de ressource de routine
Créez des fonctions qui effectuent des tâches générales dans BigQuery.	Fonctions définies par l'utilisateur SQL ou JavaScript UDAF SQL ou JavaScript
Créez des fonctions qui effectuent des tâches générales dans BigQuery et qui communiquent avec des systèmes externes à l'aide d'une connexion de ressource Cloud. Google Cloud	UDF Python
Créez des fonctions qui agrégeront les données.	UDAF
Créez une table à l'aide de paramètres.	Fonctions de table
Créez des fonctions qui utilisent des langages, des bibliothèques ou des services non compatibles avec BigQuery. Ces fonctions s'intègrent directement aux fonctions Cloud Run et à Cloud Run.	Fonctions à distance
Exécutez plusieurs instructions en une seule requête en tant que requête à plusieurs instructions à l'aide d'un langage procédural. Vous pouvez utiliser une requête multi-instructions pour effectuer les opérations suivantes: exécuter plusieurs requêtes à la suite, avec un état partagé ; Automatiser les tâches de gestion telles que la création ou la suppression de tables. mettre en œuvre une logique complexe à l'aide de structures de programmation telles que `IF` et `WHILE`. Créez et appelez des procédures stockées pour Apache Spark dans BigQuery.	Procédures stockées

Fonctions définies par l'utilisateur

Une fonction définie par l'utilisateur vous permet de créer une fonction à l'aide d'une expression SQL, d'un code JavaScript ou d'un code Python. Les fonctions définies par l'utilisateur acceptent des colonnes d'entrée, effectuent des actions sur l'entrée et renvoient le résultat de ces actions sous forme de valeur.

Vous pouvez définir une UDF comme persistante ou temporaire. Vous pouvez réutiliser des UDF persistantes dans plusieurs requêtes, tandis que les UDF temporaires n'existent que dans le champ d'application d'une seule requête.

Vous pouvez créer des fonctions définies par l'utilisateur destinées à être associées à des routines de masquage personnalisées, qui renvoient la valeur d'une colonne après avoir appliqué une fonction définie par l'utilisateur à la colonne. 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.

Pour en savoir plus sur les fonctions définies par l'utilisateur, consultez les ressources suivantes:

Fonctions définies par l'utilisateur basées sur le langage

Les fonctions définies par l'utilisateur basées sur SQL acceptent les paramètres de fonction définie par l'utilisateur modélisés, qui peuvent correspondre à plusieurs types d'arguments lorsque la fonction définie par l'utilisateur est appelée. Les UDF SQL peuvent également renvoyer la valeur d'une sous-requête scalaire.
Les fonctions définies par l'utilisateur basées sur JavaScript vous permettent 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.
Les UDF basées sur Python sont créées et exécutées sur des ressources gérées par BigQuery. Ces fonctions définies par l'utilisateur vous permettent d'implémenter une fonction en Python et de l'utiliser dans une requête SQL.
- Vous pouvez accéder à un service Google Cloud ou à un service externe à partir d'une fonction définie par l'utilisateur Python à l'aide du compte de service Connexion aux ressources cloud.
- Vous pouvez également installer des bibliothèques tierces à partir de l'index de packages Python (PyPI).

Fonctions définies par l'utilisateur issues de la communauté

En plus des fonctions définies par l'utilisateur que vous créez, des 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.

Fonctions d'agrégation définies par l'utilisateur (UDAF)

Une fonction UDAF vous permet de créer une fonction d'agrégation à l'aide d'une expression contenant du code SQL ou JavaScript. Une fonction UDAF accepte des colonnes d'entrée, effectue un calcul sur un groupe de lignes à la fois, puis renvoie le résultat de ce calcul sous forme de valeur unique.

Les fonctions UDAF ne peuvent pas muter des données, communiquer avec des systèmes externes ni envoyer de journaux à Google Cloud Observability ou à des applications similaires.

Pour en savoir plus, consultez les ressources suivantes :

Fonctions UDAF SQL

Les fonctions UDAF SQL agrègent normalement les paramètres de fonction sur toutes les lignes d'un groupe. Toutefois, vous pouvez spécifier un paramètre de fonction en tant que non agrégé à l'aide du mot clé NOT AGGREGATE. Un paramètre de fonction non agrégé est un paramètre de fonction scalaire avec une valeur constante pour toutes les lignes d'un groupe. Les fonctions UDAF SQL peuvent contenir à la fois des paramètres agrégés et non agrégés.

Fonctions UDAF JavaScript

Les UDAF JavaScript peuvent inclure des bibliothèques JavaScript. Le corps de la fonction JavaScript peut inclure du code JavaScript personnalisé, tel que des variables globales JavaScript et des fonctions personnalisées.

Étant donné que les fonctions basées sur JavaScript utilisent généralement plus de ressources, il peut être utile de consulter ces conseils de performances.

Les UDAF JavaScript présentent certaines contraintes. Seuls les encodages de type spécifiques sont autorisés, et des exigences sont imposées pour la sérialisation et la désérialisation.

Comparer les UDF et les UDAF

Le choix d'une fonction définie par l'utilisateur plutôt que d'une fonction définie par l'utilisateur avec affectation dépend de la tâche spécifique que vous essayez d'effectuer.

Pour effectuer un calcul ou une transformation sur des valeurs de données individuelles, utilisez une fonction définie par l'utilisateur.
Pour effectuer la même opération sur des groupes de valeurs de données, utilisez une fonction UDAF.

Par exemple, si vous souhaitez calculer la moyenne d'une colonne de nombres, utilisez une fonction UDAF. Si vous souhaitez convertir une colonne de chaînes en majuscules, utilisez une fonction définie par l'utilisateur.

Les fonctions définies par l'utilisateur et les fonctions UDAF présentent les similitudes suivantes:

Les fonctions UDF et UDAF ne peuvent pas muter des données, communiquer avec des systèmes externes ni envoyer de journaux à Google Cloud Observability ou à des applications similaires. L'exception concerne les fonctions définies par l'utilisateur Python, qui peuvent accéder à des services externes à l'aide d'une connexion de ressources Cloud. Toutefois, les fonctions définies par l'utilisateur Python ne sont pas compatibles avec les VPC Service Controls ni avec les clés de chiffrement gérées par le client (CMEK).
Les fonctions UDAF présentent les mêmes limites que les fonctions définies par l'utilisateur, ainsi que quelques autres.
Les fonctions définies par l'utilisateur et les fonctions UDAF sont soumises aux mêmes quotas et limites.

Les fonctions définies par l'utilisateur et les fonctions d'agrégation définies par l'utilisateur présentent les différences suivantes:

Attribut	Fonctions définies par l'utilisateur	UDAF
Définition	Les fonctions définies par l'utilisateur (UDF) acceptent des colonnes d'entrée, effectuent des actions sur l'entrée et renvoient le résultat de ces actions sous forme de valeur.	Les fonctions d'agrégation définies par l'utilisateur (UDAF) acceptent des colonnes d'entrée, effectuent un calcul sur un groupe de lignes à la fois, puis renvoient le résultat de ce calcul sous forme de valeur unique.
Langages acceptés	SQL, JavaScript et Python	SQL et JavaScript
Persistance	Elles peuvent être temporaires ou persistantes. Vous pouvez utiliser des UDF persistantes dans plusieurs requêtes. Vous ne pouvez utiliser des UDF temporaires que pour une seule requête. Les fonctions définies par l'utilisateur Python ne peuvent être que persistantes, et non temporaires.	Elles peuvent être temporaires ou persistantes. Vous pouvez utiliser des UDAF persistantes dans plusieurs requêtes. Vous ne pouvez utiliser des fonctions UDAF temporaires que pour une seule requête, un seul script, une seule session ou une seule procédure. Les fonctions UDAF persistantes peuvent être appelées en toute sécurité lorsqu'elles sont partagées entre propriétaires.
Arguments et types de données	Les fonctions définies par l'utilisateur acceptent les valeurs de paramètre conformes à GoogleSQL pour les types de données BigQuery. Certains types SQL sont directement mappés à des types JavaScript, tandis que d'autres non. Consultez les types compatibles avec JavaScript. Pour une fonction SQL définie par l'utilisateur, les valeurs de paramètre peuvent être `ANY TYPE`, ce qui peut correspondre à plusieurs types d'arguments lorsque la fonction est appelée. Seules les fonctions définies par l'utilisateur JavaScript disposent d'un spécificateur de déterminisme qui indique à BigQuery si le résultat de la requête peut être mis en cache.	Les UDAF SQL et JavaScript acceptent les valeurs de paramètres conformes aux types de données GoogleSQL pour BigQuery. Les paramètres de fonction peuvent être agrégés ou non.
Utilisation	Les fonctions définies par l'utilisateur sont couramment utilisées pour le nettoyage, la transformation et la validation des données.	Les fonctions UDAF sont couramment utilisées pour calculer des statistiques récapitulatives, telles que les moyennes, les sommes et les décomptes.

Fonctions de table

Une fonction de table, également appelée fonction de valeur de table (table-valued function, TVF), est une fonction définie par l'utilisateur qui renvoie une table. Vous pouvez utiliser une fonction de table partout où vous pouvez utiliser une table. Les fonctions de table se comportent de la même manière que les vues, mais une fonction de table peut accepter des paramètres.

Vous pouvez effectuer les opérations suivantes avec les fonctions de table:

Transmettez plusieurs paramètres.
Appelez une fonction de table dans tous les contextes dans lesquels une table est valide.
Joignez le résultat d'une fonction de table à une autre table.
Utilisez une fonction de table dans une sous-requête.

Pour en savoir plus sur les fonctions de table, consultez les pages Fonctions de table, Limites et Quotas et limites.

Fonctions à distance

Les fonctions distantes vous permettent d'implémenter votre fonction dans d'autres langages que SQL et JavaScript, ou d'utiliser des bibliothèques ou des services non compatibles avec les fonctions UDF (définies par l'utilisateur) de BigQuery.

Une fonction distante BigQuery intègre votre fonction Google SQL aux fonctions Cloud Run et à Cloud Run à l'aide de n'importe quel langage compatible, puis appelle ces fonctions à partir de requêtes Google SQL.

Voici quelques exemples de ce que vous pouvez faire avec les fonctions distantes:

Pour créer une fonction distante, procédez comme suit:

Créez le point de terminaison HTTP dans Cloud Run Functions ou Cloud Run.
Créez une fonction distante dans BigQuery à l'aide du type de connexion CLOUD_RESOURCE.
Utilisez la fonction distante dans une requête comme n'importe quelle autre fonction définie par l'utilisateur pour BigQuery.

Pour en savoir plus sur les fonctions distantes, consultez les sections Fonctions distantes, Limites et Quotas et limites.

Procédures stockées

Une procédure stockée SQL est un ensemble d'instructions pouvant être appelées à partir d'autres requêtes ou d'autres procédures stockées. Vous nommez et stockez une procédure dans un ensemble de données BigQuery.

Les procédures stockées sont compatibles avec les instructions de langage procédural, qui vous permettent, par exemple, de définir des variables et de mettre en œuvre un flux de contrôle. Pour en savoir plus sur les instructions de langages procéduraux, consultez la documentation de référence sur les langages procéduraux.

Une procédure stockée peut effectuer les opérations suivantes:

Elles utilisent des arguments d'entrée et renvoient des valeurs en sortie.
Accéder aux données de plusieurs ensembles de données ou les modifier par plusieurs utilisateurs
Contiennent une requête multi-instruction.

Certaines procédures stockées sont intégrées à BigQuery et n'ont pas besoin d'être créées. Ces procédures sont appelées "procédures système". Pour en savoir plus à ce sujet, consultez la documentation de référence sur les procédures système.

Les procédures stockées pour Spark dans BigQuery sont également compatibles. Ces procédures sont soumises à des quotas et limites.

Pour en savoir plus sur les procédures stockées, consultez la section Procédures stockées SQL.