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 présente 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.

Fonction définie par l'utilisateur SQL ou JavaScript

Fonction d'agrégation définie par l'utilisateur 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 de ressource cloud.

Fonction définie par l'utilisateur Python

Créer des fonctions qui agrègent des données.

Fonctions d'agrégation définies par l'utilisateur

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 aux Cloud Run functions et à Cloud Run.

Fonctions à distance

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 instructions à 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 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 fonction définie par l'utilisateur comme persistante ou temporaire. Vous pouvez réutiliser des fonctions définies par l'utilisateur persistantes dans plusieurs requêtes, tandis que les fonctions définies par l'utilisateur 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 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 fonctions définies par l'utilisateur, consultez les ressources suivantes :

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

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

Une fonction d'agrégation définie par l'utilisateur vous permet de créer une fonction d'agrégation à l'aide d'une expression contenant du code SQL ou JavaScript. Une fonction d'agrégation définie par l'utilisateur 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 d'agrégation définies par l'utilisateur 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 d'agrégation définies par l'utilisateur SQL

Les fonctions d'agrégation définies par l'utilisateur 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 d'agrégation définies par l'utilisateur SQL peuvent contenir des paramètres agrégés et non agrégés.

Fonctions d'agrégation définies par l'utilisateur JavaScript

Les fonctions d'agrégation définies par l'utilisateur 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 fonctions d'agrégation définies par l'utilisateur 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 fonctions définies par l'utilisateur et les fonctions d'agrégation définies par l'utilisateur

Le choix d'une UDF plutôt que d'une fonction d'agrégation définie par l'utilisateur 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 fonction d'agrégation définie par l'utilisateur.

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

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

  • Les fonctions définies par l'utilisateur et les fonctions d'agrégation définies par l'utilisateur 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 fonctions définies par l'utilisateur Python constituent une exception, car elles peuvent accéder à des services externes à l'aide d'une connexion de ressource cloud. Toutefois, les fonctions définies par l'utilisateur 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 fonctions d'agrégation définies par l'utilisateur présentent les mêmes limites que les fonctions définies par l'utilisateur, plus 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

Fonctions définies par l'utilisateur

Fonctions d'agrégation définies par l'utilisateur

Définition

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.

Les fonctions d'agrégation définies par l'utilisateur 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 fonctions définies par l'utilisateur persistantes dans plusieurs requêtes.
  • Vous ne pouvez utiliser des fonctions définies par l'utilisateur temporaires que pour une seule requête.
  • Les fonctions définies par l'utilisateur Python ne peuvent être que persistantes, et non temporaires.
  • Peut être temporaire ou persistante.
  • Vous pouvez utiliser des fonctions d'agrégation définies par l'utilisateur persistantes dans plusieurs requêtes.
  • Vous ne pouvez utiliser des fonctions d'agrégation définies par l'utilisateur temporaires que pour une seule requête, un seul script, une seule session ou une seule procédure.
  • Les fonctions d'agrégation définies par l'utilisateur 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 fonctions définies par l'utilisateur acceptent les valeurs de paramètres conformes à GoogleSQL pour BigQuery types de données. Certains types SQL sont directement mappés aux types JavaScript, mais d'autres ne le sont pas. Consultez les types compatibles avec JavaScript.

Pour une fonction définie par l'utilisateur 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 fonctions définies par l'utilisateur 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 fonctions d'agrégation définies par l'utilisateur SQL et JavaScript acceptent les valeurs de paramètres conformes à GoogleSQL pour les types de données 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 fonctions d'agrégation définies par l'utilisateur 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 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 Fonctions de table, Limites, et Quotas et limites.

Fonctions à distance

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

Une fonction à distance BigQuery intègre votre fonction GoogleSQL aux fonctions Cloud Run 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 à distance :

La création d'une fonction à distance nécessite les étapes suivantes :

  1. Créez le point de terminaison HTTP dans Cloud Run Functions ou Cloud Run.
  2. Créez une fonction à distance dans BigQuery à l'aide du type de connexion CLOUD_RESOURCE.
  3. Utilisez la fonction à distance dans une requête comme n'importe quelle autre UDF pour BigQuery.

Pour en savoir plus sur les fonctions à distance, consultez Fonctions à distance, 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 :

  • Accepter 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 Procédures stockées SQL.