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 est compatible avec 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 :

  • Le type de tâche à implémenter.
  • Le langage de programmation à utiliser.
  • Le type de persistance à implémenter pour la routine : temporaire ou persistante.
  • Le type de réutilisation requis pour la routine : dans une ou plusieurs requêtes.
  • Considérations sur les performances.
  • Accès aux 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éer des fonctions qui effectuent des tâches à usage général dans BigQuery.

UDF SQL ou JavaScript

UDAF SQL ou JavaScript

Créer 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éer une table à l'aide de paramètres.

Fonctions de table

Créer 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écuter plusieurs instructions dans une seule requête en tant que requête multi-instruction à l'aide de langage procédural. Vous pouvez utiliser une requête multi-instruction 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.
  • Implémenter une logique complexe à l'aide de constructions 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 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 UDF à utiliser avec des routines de masquage personnalisées, qui renvoient la valeur d'une colonne après l'application d'une UDF à 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 :

UDF basées sur un langage

  • Les UDF basées sur SQL sont compatibles avec les paramètres 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 du code écrit en JavaScript à partir d'une requête SQL.
    • Les UDF JavaScript 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.

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

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 :

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.

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 sont soumises à certaines contraintes. Seuls des encodages de type spécifiques sont autorisés, et il existe des exigences en matière de sérialisation et de 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 effectuer la même opération 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 UDF et les 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 aux ressources Cloud. Toutefois, les UDF Python ne sont pas compatibles avec les contrôles de service VPC ni les clés de chiffrement gérées par le client (CMEK).
  • Les UDAF présentent les mêmes limites que les UDF, plus quelques autres.
  • Les UDF et les UDAF sont soumises aux mêmes quotas et limites.

Les UDF et les UDAF 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.

Langages acceptés

SQL, JavaScript et Python

SQL et JavaScript

Persistance
  • Peut être temporaire ou persistante.
  • Vous pouvez utiliser des UDF persistantes dans plusieurs requêtes.
  • Vous ne pouvez utiliser des UDF temporaires que pour une seule requête.
  • Les UDF Python ne peuvent être que persistantes, et non temporaires.
  • Peut être temporaire ou persistante.
  • 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 UDAF persistantes peuvent être appelées en toute sécurité lorsqu'elles sont partagées entre les propriétaires.

Arguments et types de données

Les UDF acceptent les valeurs de paramètres conformes à GoogleSQL pour les types de données BigQuery data types. Certains types SQL sont directement mappés à des types JavaScript, mais d'autres ne le sont pas. Consultez les types compatibles avec JavaScript.

Pour une UDF SQL, les valeurs de paramètres 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 fournit une indication à BigQuery indiquant 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 à GoogleSQL pour BigQuery types de données.

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 les moyennes, les sommes et les décomptes.

Fonctions de table

Une fonction de table, également appelée fonction de valeur de table, 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 :

  • Transmettre plusieurs paramètres.
  • Appeler une fonction de table dans n'importe quel contexte où une table est valide.
  • Joindre la sortie 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 les sections 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 non compatibles avec les UDF BigQuery.

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

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

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

  1. Créez le point de terminaison HTTP dans Cloud Run Functions ou Cloud Run.
  2. Créez une fonction distante dans BigQuery à l'aide du type de connexion CLOUD_RESOURCE.
  3. 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 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 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 :

  • Utiliser des arguments d'entrée et renvoyer des valeurs en sortie.
  • Accéder à des données ou les modifier dans plusieurs ensembles de données par plusieurs utilisateurs.
  • 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 compatibles. Ces procédures sont soumises à des quotas et des limites.

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

Utiliser des routines avec des agents d'IA

Si vous créez des agents d'IA qui fonctionnent avec des données BigQuery, ils peuvent récupérer des 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 la section Récupérer le contexte des routines.