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 lorsque vous choisissez 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 : 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 les types de tâches que vous pouvez effectuer pour chaque type de routine :

Tâche	Type de ressource "Routine"
Créez des fonctions qui effectuent des tâches à usage général dans BigQuery.	Fonction définie par l'utilisateur SQL ou JavaScript Fonction UDAF SQL ou JavaScript
Créez des fonctions qui effectuent des tâches à usage général dans BigQuery et qui communiquent avec des systèmes externes Google Cloud à l'aide d'une connexion de ressource Cloud.	UDF Python
Créez des fonctions qui agrègent 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 du langage procédural. Vous pouvez utiliser une requête à plusieurs 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 boucles 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 lUDF vous permet de créer une fonction à l'aide d'une expression SQL, d'un code JavaScript ou d'un code Python. Les 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.

Vous pouvez définir une UDF comme persistante ou temporaire. Vous pouvez réutiliser une fonction persistante définie par l'utilisateur dans plusieurs requêtes, alors qu'une fonction temporaire définie par l'utilisateur ne peut être utilisée que dans 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 l'application d'une UDF 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 UDF, consultez les ressources suivantes :

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

Les UDF basées sur SQL acceptent les paramètres d'UDF modélisés, qui peuvent correspondre à plusieurs types d'arguments lorsque l'UDF est appelée. Les UDF SQL peuvent également renvoyer la valeur d'une sous-requête scalaire.
Les UDF 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 standard, 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 UDF 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 UDF 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, celles 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 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 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 UDAF SQL peuvent contenir 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 sur les performances.

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

Comparer les UDF et les UDAF

Le choix d'une UDF plutôt que d'une UDAF 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 UDF.
Pour faire de même sur des groupes de valeurs de données, utilisez une UDAF.

Par exemple, si vous souhaitez calculer la moyenne d'une colonne de nombres, utilisez une UDAF. Si vous souhaitez convertir une colonne de chaînes en majuscules, utilisez une UDF.

Les fonctions UDF et UDAF présentent les similitudes suivantes :

Les UDF et les 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. Les UDF Python font exception, car elles peuvent accéder à des services externes à l'aide d'une connexion à une ressource Cloud. Toutefois, les UDF Python ne sont pas compatibles avec les VPC Service Controls ni 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.

Voici les différences entre les UDF et les UDAF :

Attribut	UDF	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.
Langues acceptées	SQL, JavaScript et Python	SQL et JavaScript
Persistance	Peuvent être temporaires ou persistants. Vous pouvez utiliser des UDF persistantes dans plusieurs requêtes. Vous ne pouvez utiliser les UDF temporaires que pour une seule requête. Les UDF Python ne peuvent être que persistantes, et non temporaires.	Peuvent être temporaires ou persistants. Vous pouvez utiliser des UDAF persistantes dans plusieurs requêtes. Vous ne pouvez utiliser les 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 UDF acceptent les valeurs de paramètres conformes aux types de données GoogleSQL pour BigQuery. Certains types SQL sont directement mappés à des types JavaScript, tandis que d'autres non. Consultez la section Types compatibles avec JavaScript. Pour une UDF SQL, les valeurs de paramètre peuvent être `ANY TYPE`, ce qui peut correspondre à plusieurs types d'argument lorsque la fonction est appelée. Seules les fonctions JavaScript définies par l'utilisateur 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 agrégés.
Utilisation	Les UDF sont couramment utilisées pour le nettoyage, la transformation et la validation des données.	Les UDAF sont couramment utilisées pour calculer des statistiques récapitulatives, telles que des moyennes, des sommes et des décomptes.

Fonctions de table

Une fonction de table, également appelée fonction de valeur de table (table-valued function, TVF), est une UDF 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 Fonctions de table, Limites et Quotas et limites.

Fonctions à distance

Les fonctions distantes vous permettent d'implémenter votre fonction dans des langages autres que SQL et JavaScript, ou d'utiliser des bibliothèques ou des services qui ne sont pas compatibles avec les UDF BigQuery.

Une fonction distante BigQuery intègre votre fonction GoogleSQL à Cloud Run Functions et Cloud Run en utilisant n'importe quel langage compatible, puis appelle ces fonctions à partir de requêtes GoogleSQL.

Voici quelques exemples de tâches que vous pouvez effectuer avec des fonctions à distance :

Pour créer une fonction distante, vous devez suivre les étapes suivantes :

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 UDF pour BigQuery.

Pour en savoir plus sur les fonctions distantes, consultez 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 langages procéduraux, 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 ou les modifier sur plusieurs ensembles de données par plusieurs utilisateurs.
contenir une requête à plusieurs instructions ;

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 acceptées. Ces procédures sont soumises à des quotas et limites.

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