Fonctions SQL définies par l’utilisateur

BigQuery prend en charge les fonctions définies par l’utilisateur (UDF). Un UDF vous permet de créer une fonction par une autre expression SQL ou JavaScript. Ces fonctions acceptent les colonnes d’entrée et effectuent des actions, puis montrent le résultat de ces actions comme valeur. Pour plus d’informations sur les fonctions définies par l’utilisateur dans SQL héritée, consultez des fonctions définies par l’utilisateur dans Hérité SQL.

UDF peut être persistant ou temporaire. Vous pouvez réutiliser les UDF persistants dans plusieurs consultations et UDF temporaires dans une seule requête.

Syntaxe de l’UDF

Pour créer un UDF persistant, utilisez la syntaxe suivante:

CREATE FUNCTION dataset_name.]function_name (]) { sql_function_definition | javascript_function_definition }

Pour créer un UDF temporaire, utilisez la syntaxe suivante:

Cette syntaxe est composée des composants suivants:

  • créer {FONCTION | Ou remplacer la fonction | Fonction si elle n’existe pas}. Créez une fonction ou mettez-le à jour. Pour remplacer une fonction existante avec le même nom, utilisez le mot-clé OR REPLACE. Utilisez la clause IF NOT EXISTS Si vous souhaitez traiter la requête telle que complétée et que vous ne voulez qu’aucune mesure soit effectuée en cas de fonctionnalité avec le même nom.

    /li>

  • Nom de projet est le nom du projet dans lequel vous allez créer la fonction. La configuration par défaut est le projet qui exécute cette requête DDL. Si le nom du projet contient des caractères spéciaux, tels que deux points, il doit s’agir d’accents graves ` (Exemple: ).

  • DataSet_Name est le nom du jeu de données dans lequel vous allez créer la fonction. La valeur par défaut est defaultDataset dans la demande.

  • nommé_paramètre. Il consiste en une paire de param_name et param_type séparé par des virgules. La valeur de param_type est un type de données de bigquery. Pour un UDF SQL, la valeur de param_type peut aussi être .

  • Déterisme_specififier. Il ne s’applique qu’aux fonctions définies par l’utilisateur JavaScript. Il propose une suggestion à BigQuery de décider si le résultat de la requête peut être mis en cache. Il peut être l’une des valeurs suivantes:
    • DETERMINISTIC: la fonction montre toujours le même résultat lorsque les mêmes arguments sont passés. Le résultat de la requête peut être stocké dans le cache. Par exemple, si la fonction add_one(i) montre toujours i + 1, la fonction est déterministe.

    • NOT DETERMINISTIC: la fonction ne montre pas toujours le même résultat lorsque les mêmes arguments sont passés et, par conséquent, ne peuvent pas être mis en cache. Par exemple, si add_random(i) montre i + rand(), la fonction n’est pas déterministe et la bigquerie n’utilisera pas de résultats stockés dans le cache.

      Si toutes les fonctions invoquées sont déterministes, BigQuery tentera de mettre en cache le résultat, à moins que les résultats ne puissent être attrapés par d’autres raisons. Pour plus d’informations, reportez-vous à la section Comment utiliser les requêtes enregistrées par cache.

  • . Spécifie le type de données indiquant la fonction.

    • Si la fonction est définie dans SQL, la clause RETURNS est facultative. Si la clause , la bigquery déduit le type de résultat de la fonction du corps de la fonction SQL lorsqu’une requête appelle la fonction.
    • Si le La fonction est définie dans JavaScript, la clause RETURNS est obligatoire. Si vous souhaitez plus d’informations sur les valeurs autorisées pour data_type, voir les types de données JavaScript pris en charge JavaScript pris en charge.
  • omme (sql_expression). Spécifie l’expression SQL qui définit la fonction.

  • . Pour un UDF JavaScript, spécifiez un agencement de bibliothèques JavaScript pour inclure dans la définition de la fonction.

  • comme javascript_code. Spécifie la définition d’une fonction JavaScript. est un littéral à chaîne.

    • Si le code inclut des guillemets et des barres inversées, vous devez vous échapper comme un chaîne non transformée. Par exemple, le code return "\n"; peut être préparé comme l’une des options suivantes:
      • String Entriguillé "return \"\\n\";". Les banques et les barres inversées doivent être escaladées.
      • String entre Triple Comments: """return "\\n";""". Les barres inversées doivent être sauvegardées, tandis que les citations no.
      • Striche RAW: r"""return "\n";""". Aucune évasion n’est nécessaire.

Pour effacer une fonction définie par l’utilisateur persistant, utilisez la syntaxe suivante:

DROP FUNCTION dataset_name.]function_name

L’UDF temporaire expire dès que la requête, donc les déclarations ne sont compatibles que par des UDF temporaires de Commandes et procédures.

SQL UDF

Créez SQL UDF avec la syntaxe suivante:

CREATE FUNCTION dataset_name.]function_name (]) AS (sql_expression)named_parameter: param_name param_type

PARAMÈTRES SQL UDF avec modèle

un paramètre avec modèle avec ANY TYPE peut correspondre à plus d’un type d’argument lorsque la fonction est appelé.

  • S’il y a plus d’un paramètre avec le type , BigQuery n’impose aucune relation entre ces arguments.
  • Le type de données affiché à la suite de la fonction ne peut pas être ANY TYPE. Il doit être omis, ce qui signifie qu’il est déterminé automatiquement en fonction de sql_expression ou il doit s’agir d’un type explicite.
  • Si vous transmettez les fonctions Types d’arguments qui sont incompatibles avec la définition de la fonction, une erreur se produira pendant l’appel.

Exemples SQL UDF

Dans l’exemple suivant, un UDF est affiché qui utilise un Fonction SQL.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);WITH numbers AS (SELECT 1 as val UNION ALL SELECT 3 as val UNION ALL SELECT 4 as val UNION ALL SELECT 5 as val)SELECT val, addFourAndDivide(val, 2) AS resultFROM numbers;+-----+--------+| val | result |+-----+--------+| 1 | 2.5 || 3 | 3.5 || 4 | 4 || 5 | 4.5 |+-----+--------+

Dans l’exemple suivant, un UDF SQL qui utilise un paramètre avec un modèle est affiché. La fonction résultante accepte des arguments de différents types.

Dans l’exemple suivant, un UDF SQL qui utilise un paramètre avec modèle pour afficher le dernier élément d’un arrangement de n’importe quel type.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS ( arr);SELECT names AS first_name, lastArrayElement(names) AS last_nameFROM ( SELECT AS names UNION ALL SELECT );+------------+-----------+| first_name | last_name |+------------+-----------+| Fred | Rogers || Marie | Curie |+------------+-----------+

Dans l’exemple suivant, un UDF SQL qui utilise un sous-constat scalaire pour compter le nombre d’utilisateurs ayant un âge déterminé dans une table utilisateur .

JavaScript UDF

Créer un UDF JavaScript par la structure suivante.

CREATE FUNCTION dataset_name.]function_name (]) RETURNS data_type LANGUAGE js AS javascript_code

JavaScript Types de données UDF prises en charge

Certains types SQL correspondent directement aux types JavaScript, mais d’autres ne le font pas. BigQuery représente les types comme suit:

Type de données de BigQuery ype de données JavaScript
tableau graphique
bool Boolean
octets codé sur base64
float64 numéro
numérique, bignumérique (aperçu) si une valeur numérique peut être représenté avec précision comme une valeur de point IEEE flottante 754 et ne dispose pas d’une partie fractionnée. , il est codé comme un nombre. Ces valeurs sont dans la gamme. Sinon, il est codé comme une chaîne.
string
struct objet dans lequel chaque Champ de structure a le nom
horsetamp date avec un champ microsecond contenant la fraction de microsecond de la horodatage
date date

Parce que JavaScript ne prend pas en charge un type d’entier 64 bits, INT64 Il n’est pas compatible comme un type d’entrée pour JavaScript UDF. Au lieu de cela, utilisez FLOAT64 afin de représenter des valeurs telles qu’un entier ou pour les représenter comme une chaîne.

Supports de BigQuery INT64 comme type de données affiché dans les UDF JavaScript. Dans ce cas, le corps de fonction JavaScript peut afficher une chaîne ou un numéro JavaScript. BigQuery puis convertit l’un de ces types dans INT64

si la valeur de retour de JavaScript UDF est un Promise, BigQuery attend jusqu’à ce que le Promise est établi. Si Promise est défini dans un état livré, BigQuery montre son résultat. Si Promise est défini dans un état rejeté, BigQuery affiche une erreur d’erreur.

Règles de citation

Vous devez mettre le code JavaScript dans des guillemets. Pour des fragments de code simples d’une ligne, vous pouvez utiliser une chaîne entre COMILLAS standard:

CREATE TEMP FUNCTION plusOne(x FLOAT64)RETURNS FLOAT64LANGUAGE jsAS "return x+1;";SELECT val, plusOne(val) AS resultFROM UNNEST() AS val;+-----------+-----------+| val | result |+-----------+-----------+| 1 | 2 || 2 | 3 || 3 | 4 || 4 | 5 || 5 | 6 |+-----------+-----------+

Dans les cas où le fragment contient des citations ou consiste en plusieurs lignes, utilisez Blocs avec triple citations:

CREATE TEMP FUNCTION customGreeting(a STRING)RETURNS STRINGLANGUAGE js AS r""" var d = new Date(); if (d.getHours() 

comprend des bibliothèques JavaScript

Vous pouvez étendre votre UDF JavaScript avec la section OPTIONS. Dans cette section, il est autorisé à spécifier des bibliothèques de code externes pour l’UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING) RETURNS STRING LANGUAGE js OPTIONS ( library= ) ASr""" // Assumes 'doInterestingStuff' is defined in one of the library files. return doInterestingStuff(a, b);""";SELECT myFunc(3.14, 'foo');

Dans l’exemple précédent, le code dans lib1.js et lib2.js est disponible pour n’importe quel code dans la section de l’UDF.

Exemples de JavaScript UDF

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)RETURNS FLOAT64LANGUAGE js AS r""" return x*y;""";WITH numbers AS (SELECT 1 AS x, 5 as y UNION ALL SELECT 2 AS x, 10 as y UNION ALL SELECT 3 as x, 15 as y)SELECT x, y, multiplyInputs(x, y) as productFROM numbers;+-----+-----+--------------+| x | y | product |+-----+-----+--------------+| 1 | 5 | 5 || 2 | 10 | 20 || 3 | 15 | 45 |+-----+-----+--------------+

Vous pouvez passer le résultat d’un UDF comme entrée pour un autre UDF. Par exemple:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)RETURNS FLOAT64LANGUAGE js AS r""" return x*y;""";CREATE TEMP FUNCTION divideByTwo(x FLOAT64)RETURNS FLOAT64LANGUAGE js AS r""" return x/2;""";WITH numbers AS (SELECT 1 AS x, 5 as y UNION ALL SELECT 2 AS x, 10 as y UNION ALL SELECT 3 as x, 15 as y)SELECT x, y, multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_productFROM numbers;+-----+-----+--------------+| x | y | half_product |+-----+-----+--------------+| 1 | 5 | 1.25 || 2 | 10 | 5 || 3 | 15 | 11.25 |+-----+-----+--------------+

Dans l’exemple suivant, les valeurs de tous les appels sont ajoutées foo La chaîne JSON donnée.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING) RETURNS FLOAT64 LANGUAGE js AS r"""function SumFoo(obj) { var sum = 0; for (var field in obj) { if (obj.hasOwnProperty(field) && obj != null) { if (typeof obj == "object") { sum += SumFoo(obj); } else if (field == "foo") { sum += obj; } } } return sum;}var row = JSON.parse(json_row);return SumFoo(row);""";WITH Input AS ( SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL SELECT NULL, 4 AS foo UNION ALL SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo)SELECT TO_JSON_STRING(t) AS json_row, SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sumFROM Input AS t;+---------------------------------------------------------------------+---------+| json_row | foo_sum |+---------------------------------------------------------------------+---------+| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10} | 14.14 || {"s":null,"foo":4} | 4 || {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59 |+---------------------------------------------------------------------+---------+

Si vous souhaitez plus d’informations pour attribuer les types de données de bigquery aux types JavaScript, voir JavaScript Types de données UDF admis

Recommandations pour JavaScript UDFS

Effectuer un filtre avant votre entrée

Si votre entrée peut être facilement filtrée avant de le transmettre à un UDF JavaScript, il est probable que que votre requête est plus rapide et plus rapide.

évite l’état mutable persistant

pas des entrepôts ou accédez à des appels d’état mutable vers le JavaScript UDF.

Utilisez la mémoire Effectivement

L’environnement de traitement JavaScript a une limite de mémoire disponible par consultation. Les requêtes JavaScript UDF accumulant également l’état local peuvent échouer en raison de l’épuisement de la mémoire.

Exécuter une requête avec un UDF

utilise la console de cloud.

Vous pouvez utiliser la console Cloud pour exécuter des requêtes avec un ou plusieurs UDF.

  1. Cliquez sur le brouillon de la nouvelle requête.
  2. Dans le panneau Editeur de Querue, entrez la déclaration UDF. Par exemple:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)RETURNS FLOAT64 LANGUAGE js AS r""" return x*2;""";
  3. sous l’instruction UDF, entrez la requête SQL. Par exemple:

    SELECT timesTwo(numbers) AS doublesFROM UNNEST() AS numbers;
  4. Cliquez sur la requête exécutée. Les résultats de la requête sont affichés sous les boutons.

Utilisez l’outil BQ

ligne de commande. Vous pouvez utiliser l’outil Line de bq de la SDK Cloud pour exécuter une requête contenant une ou plusieurs UDF.

Utilisez la syntaxe suivante pour exécuter une requête avec un UDF:

bq query <statement_with_udf_and_query>

UDF autorisé

Un UDF autorisé est un UDF autorisé à accéder à un ensemble de données particulier. UDF peut consulter des tables dans le jeu de données, même si L’utilisateur appelant l’UDF n’a pas accès à ces tables.

Les UDF autorisés vous permettent de partager des résultats de requêtes avec des utilisateurs ou des groupes spécifiques sans donner à ces utilisateurs ou groupes accès aux tables sous-jacentes. Par exemple, une UDF autorisée peut calculer une agrégation dans les données ou rechercher une valeur de table et utiliser cette valeur dans un calcul.

Pour autoriser un UDF, vous pouvez utiliser Google Cloud Console, repose API la commande. Totiligne -line de bq

console

    aller à la bigquery Page de la console de nuage.

    aller à bigquery

  1. dans le volet de navigation, dans la section Ressources, développez votre projet et sélectionnez un ensemble de données.

  2. dans le volet Détails, cliquez sur Autoriser les routines.

  3. sur la page de routine autorisée, dans la section Autoriser la routine, sélectionnez l’ID de projet, le jeu de données ID et l’ID de routine que vous souhaitez autoriser.

  4. Cliquez sur Ajouter une autorisation.

API

  1. Appelez la méthode datasets.get pour récupérer le jeu de données que vous souhaitez que l’UDF. Le corps de réponse contient une représentation de la ressource Dataset

  2. Ajoutez l’objet JSON suivant à la disposition access dans la ressource Dataset:

    { "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" }}

    ici:

    • DataSet_Name est le nom du jeu de données contenant l’UDF.
    • project_id est l’ID de projet contenant le UDF.
    • routine_name est le nom de l’UDF.
  3. appelez la méthode dataset.update avec la représentation Dataset modifié.

bq

  1. Utilisez la commande bq show pour obtenir la représentation JSON de l’ensemble des données que vous souhaitez accéder à l’UDF. Le résultat de la commande est une représentation JSON de la ressource Dataset .guard le résultat dans un fichier local.

    bq show --format=prettyjson TARGET_DATASET > dataset.json

    Remplacez cible_dataset par le nom du jeu de données sur lequel l’UDF aura.

  2. Modifier le fichier pour ajouter l’objet JSON suivant au tableau access dans la ressource Dataset:

    { "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" }}

    ici:

    • DataSet_Name est le nom du jeu de données contenant le UDF.
    • project_id est l’ID de projet contenant l’UDF.
    • Routine_Name est le nom de l’UDF.
  3. tilisez la commande bq update pour mettre à jour le fichier.

    bq update --source dataset.json TARGET_DATASET

Exemple d’UDF autorisé

ci-dessous, un exemple de création et d’utilisation d’un UDF autorisé est affiché.

  1. créer deux ensembles de données appelés private_dataset et public_dataset. Pour plus d’informations sur la création d’un ensemble de données, la requête crée un ensemble de données.

  2. exécutez la déclaration suivante pour créer une table appelée dans private_dataset:

    CREATE OR REPLACE TABLE private_dataset.private_tableAS SELECT key FROM UNNEST() key;
  3. exécute la relève suivante pour créer un appel UDF count_key dans public_dataset. L’UDF inclut une déclaration dans .

    CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING)RETURNS INT64AS((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));
  4. subve la fonction bigquery.dataViewer à un utilisateur dans l’ensemble de données public_dataset. Cette fonction inclut la permission bigquery.routines.get, qui permet à l’utilisateur d’appeler la fonction. Si vous souhaitez obtenir des informations pour attribuer des contrôles d’accès à un jeu de données, des contrôles de consultation ont accès à des ensembles de données.

  5. À ce stade, l’utilisateur a la permission d’appeler la fonction count_key, mais ne peut pas accéder à la table dans private_dataset. Si l’utilisateur tente d’appeler la fonction, vous recevrez un message d’erreur similaire à celui suivant:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.
  6. avec l’outil Line , entrez la Comme indiqué ci-dessous:

    Le résultat est enregistré dans un fichier local appelé dataset.json.

  7. modifierdataset.json

Pour ajouter l’objet JSON suivant à la matriceaccess:

{ "routine": { "datasetId": "public_dataset", "projectId": "PROJECT_ID", "routineId": "count_key" }}

Remplacez le projet_ID par l’ID du projet pour public_dataset.

  • avec l’outil de ligne de commande de bq, entrez dans le comme indiqué ci-dessous:

    bq update --source dataset.json private_dataset
  • Pour vérifier que l’UDF a accès à private_dataset, l’utilisateur peut exécuter la requête suivante:

  • ajoutez des descriptions à UDF

    Pour ajouter une description à un UDF, procédez comme suit:

    Console

    1. aller à la page de bigquery dans la console de nuage.

      aller à bigquery

    2. dans le Panneau d’explorateur, développez votre projet et votre jeu de données, puis sélectionnez la fonction.

    3. Dans le panneau Détails, cliquez sur l’icône de crayon à côté de la description pour éditer le texte de la description. .

    4. Dans la boîte de dialogue, entrez une description dans la zone ou modifiez la description existante. Cliquez sur Mettre à jour pour enregistrer le nouveau texte de description.

    comme une alternative, vous pouvez utiliser une requête SQL standard afin de mettre à jour la description à l’aide de l’ID du champ OPTIONS. Dans la zone Editeur de Querry, entrez la définition de la fonction, puis ajoutez la ligne suivante:

    OPTIONS (description="DESCRIPTION") AS """

    remplacer la description de la description que vous souhaitez ajouter.

    BQ

    Vous pouvez modifier la description d’une fonction de la ligne de commande à l’aide de la syntaxe bq query de l’UDF et de l’outil Line de BQ commandes. Spécifie standard SQL avec une marque --nouse_legacy_sql ou --use_legacy_sql=false puis entrez la définition de votre fonction. Pour définir le paramètre dans le champ OPTIONS, ajoutez la ligne suivante à votre définition:

    OPTIONS (description="DESCRIPTION") AS """

    remplace la description par la description que vous souhaitez ajouter.

    limitations

    Les limitations suivantes s’appliquent aux fonctions temporaires et persistantes définies par le Utilisateur:

    • Les objets DOM ne sont pas pris en charge Window et Node, ni les fonctions qui les nécessitent.
    • Les fonctions JavaScript basées sur le code natif ne sont pas prises en charge.

    • Un UDF JavaScript peut épuiser le temps d’attente et empêcher votre requête complété. Les temps d’attente ne peuvent avoir que 5 minutes seulement, mais peuvent varier en fonction de différents facteurs, y compris le temps processeur de l’utilisateur qui consomme votre fonction et la taille des entrées et des sorties dans la fonction JavaScript.
    • Opérations de niveau bit dans la poignée JavaScript uniquement Les 32 bits les plus significatifs
    • Les UDF sont soumis à certaines limites de fréquence et de limites de quota. Pour plus d’informations, voir Limites UDF.

    Les limitations suivantes s’appliquent aux fonctions persistantes définies par l’utilisateur:

    • chaque jeu de données seulement il peut contenir un UDF persistant avec le même nom. Cependant, vous pouvez créer un UDF dont le nom est égal à celui d’une table dans le même ensemble de données.
    • Lorsque vous vous référez à un UDF persistant d’un autre UDF persistant ou une vue logique, vous devez qualifier le nom du nom. avec le jeu de données. Par exemple: CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

    Les limitations suivantes s’appliquent aux fonctions temporaires définies par l’utilisateur.

    Laisser un commentaire

    Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *