Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

Présentation des routines

La création de routines dans BigQuery vous permet d'encapsuler une logique complexe à l'aide de fonctions et de procédures stockées réutilisables. Choisir la bonne routine pour votre charge de travail vous permet d'agréger efficacement les données, d'automatiser les tâches de gestion et d'intégrer des services externes directement à partir de vos requêtes.

Routines compatibles

BigQuery est compatible avec les routines suivantes :

Comment 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 : sur une ou plusieurs requêtes.
Considérations sur les performances.
Accéder à des services externes
Partage de 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 d'usage général dans BigQuery.	UDF SQL ou JavaScript 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 Google Cloud externes à l'aide d'une connexion aux ressources Cloud.	UDF Python
Créer des fonctions qui agrègent des 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 à Cloud Run Functions et à Cloud Run.	Fonctions distantes
Exécutez plusieurs instructions dans une seule requête en tant que requête multi-instruction à l'aide du langage procédural. Avec une requête multi-instruction, vous pouvez : 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éer et appeler des procédures stockées pour Apache Spark dans BigQuery	Procédures stockées

Fonctions définies par l'utilisateur

Une UDF 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 UDF persistante 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 lui avoir appliqué une UDF par l'utilisateur. 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 :

UDF basées sur un langage

Les UDF basées sur SQL sont compatibles avec les paramètres d'UDF modélisés, qui peuvent correspondre à plusieurs types d'arguments lorsque l'UDF est appelée. Les fonctions SQL définies par l'utilisateur peuvent également renvoyer la valeur d'une sous-requête scalaire.
Les UDF basées sur JavaScript vous permettent d'appeler du 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 job 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 UDF Python à l'aide du compte de service de connexion aux ressources Cloud.
- Vous pouvez également installer des bibliothèques tierces à partir de l'index de packages Python (PyPI).

UDF issues de la communauté

En plus des UDF que vous créez, les UDF issues des contributions 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 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é avec le 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 JavaScript d'agrégation définies par l'utilisateur

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 sont soumises à certaines contraintes. Seuls certains encodages de types 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;utilisateur.
Pour effectuer la même opération sur des groupes de valeurs de données, utilisez une fonction d'agrégation définie par l'utilisateur (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;utilisateur.

Les fonctions définies par l'utilisateur (UDF) et les fonctions d'agrégation définies par l'utilisateur (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. Les UDF Python, qui peuvent accéder à des services externes à l'aide d'une connexion aux ressources Cloud, constituent une exception. Toutefois, les UDF Python ne sont pas compatibles avec 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 d'agrégation définies par l'utilisateur 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	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	Peut être temporaire ou persistant. 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.	Peut être temporaire ou persistant. Vous pouvez utiliser des UDAF persistantes dans plusieurs requêtes. Vous ne pouvez utiliser des 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è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 pour 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 UDF 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 agrégés.
Utilisation	Les fonctions définies par l'utilisateur 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 nombres.

Fonctions de table

Une fonction de table, également appelée fonction de valeur de table, 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.
Joindre le résultat d'une fonction de table à une autre table.
Utiliser 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 distantes

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 fonctions définies par l'utilisateur BigQuery.

Une fonction distante BigQuery intègre votre fonction Google SQL à Cloud Run Functions 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 tâches que vous pouvez effectuer avec les fonctions distantes :

La création d'une fonction distante nécessite 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#39;utilisateur pour BigQuery.

Pour en savoir plus sur les fonctions distantes, consultez les pages 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 de plusieurs ensembles de données de plusieurs utilisateurs ou les modifier.
Contenir 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 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.

Utiliser des routines avec des agents IA

Si vous créez des agents IA qui fonctionnent avec des données BigQuery, ils peuvent récupérer les métadonnées de routine à partir de Knowledge Catalog, y compris les arguments, le type renvoyé et le code source de la routine, pour comprendre ce qu'elle fait et comment l'appeler. Pour en savoir plus, consultez Récupérer le contexte des routines.