Funcions de SQL estàndard definides per l’usuari

BigQuery accepta característiques definides per l’usuari (UDF). Una UDF et permet crear una funció mitjançant una altra expressió SQL o JavaScript. Aquestes funcions accepten columnes d’entrada i realitzen accions, per després mostrar el resultat d’aquestes accions com un valor. Per obtenir informació sobre les funcions definides per l’usuari en SQL heretat, consulta Funcions definides per l’usuari en SQL heretat.

Les UDF poden ser persistents o temporals. Pots reutilitzar les UDF persistents en diverses consultes i les UDF temporals en una única consulta.

Sintaxi de les UDF

Per a crear una UDF persistent, fa servir la següent sintaxi:

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

Per a crear una UDF temporal, fa servir la següent sintaxi:

CREATE {TEMPORARY | TEMP} FUNCTION function_name (]) { sql_function_definition | javascript_function_definition }
named_parameter: param_name param_typesql_function_definition: AS (sql_expression)javascript_function_definition: LANGUAGE js AS javascript_codedeterminism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }

Aquesta sintaxi consta dels següents components:

  • CREATE {FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS}. Crea una funció o l’actualitza. Per reemplaçar una funció existent amb el mateix nom, fa servir la paraula clau OR REPLACE. Fes servir la clàusula IF NOT EXISTS si voleu tractar la consulta com completada i vols que no es realitzi cap acció en el cas que ja existeixi una funció amb el mateix nom.

  • project_name és el nom de el projecte en el qual crearàs la función.La configuració per omissió és el projecte que executa aquesta consulta d’DDL. Si el nom de el projecte conté caràcters especials, com dos punts, ha d’estar entre accents greus ` (exemple: `google.com:my_project`).

  • dataset_name és el nom del conjunt de dades en què crearàs la funció.El omissió és defaultDataset en la sol·licitud.

  • named_parameter. Consisteix en un parell de param_name i param_type separats per comes. El valor de param_type és un tipus de dades de BigQuery. Per a una UDF de SQL, el valor de param_type també pot ser ANY TYPE.

  • determinism_specifier. Només s’aplica a les funcions definides per l’usuari d’JavaScript.Proporciona un suggeriment a BigQuery per decidir si el resultat de la consulta es pot emmagatzemar en memòria cau. Pot ser un dels següents valors:

    • DETERMINISTIC: La funció sempre mostra el mateix resultat quan es passen els mateixos arguments. El resultat de la consulta pot emmagatzemar en memòria cau. Per exemple, si la funció add_one(i) sempre mostra i + 1, la funció és determinista.

    • NOT DETERMINISTIC: La funció no sempre mostra el mateix resultat quan es passa els mateixos arguments i, per tant, no es pot emmagatzemar en memòria cau. Per exemple, si add_random(i) mostra i + rand(), la funció no és determinista i BigQuery no usarà resultats emmagatzemats en memòria cau.

      Si totes les funcions invocades són DETERMINIS-, BigQuery intentarà emmagatzemar en memòria cau el resultat, llevat que els resultats no es puguin emmagatzemar en memòria cau per altres motius. Per a més informació, consulta la secció sobre com utilitzar resultats de consultes emmagatzemats en memòria cau.

  • . Especifica el tipus de dades que mostra la funció.

    • Si la funció està definida en SQL, la clàusula RETURNS és opcional. Si s’omet la clàusula RETURNS, BigQuery dedueix el tipus de resultat de la funció a partir de el cos de la funció SQL quan una consulta crida a la funció.
    • si la funció està definida en JavaScript, la clàusula RETURNS és obligatòria. Per obtenir més informació sobre els valors permesos per data_type, consulta Tipus de dades de UDF de JavaScript admesos.
  • AS (sql_expression). Especifica l’expressió SQL que defineix la funció.

  • . Per a una UDF de JavaScript, especifica un arranjament de biblioteques de JavaScript per incloure en la definició de funció.

  • AS javascript_code. Especifica la definició d’una funció de JavaScript. javascript_code és un literal de string.

    • Si el codi inclou cometes i barres invertides, s’ha de escaparo representar com una string sense processar. Per exemple, el codi return "\n"; es puederepresentar com una de les següents opcions:
      • String entre cometes "return \"\\n\";". S’han escapar lascomillas i les barres invertides.
      • String entre cometes triples: """return "\\n";""". Les barres invertides debenescaparse, mentre que les cometes no.
      • String sense processar: r"""return "\n";""". No es necessita escapament.

Per esborrar una funció definida per l’usuari persistent, fa servir la següent sintaxi:

DROP FUNCTION dataset_name.]function_name

les UDF temporals vencen tot just finalitza la consulta, de manera que les declaracions DROP FUNCTION només són compatibles amb UDF temporals en seqüències d’ordres i procediments.

UDF de SQL

Crea UDF de SQL amb la següent sintaxi:

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

paràmetres de UDF de SQL amb plantilla

Un paràmetre amb plantilla amb param_typeANY TYPE pot coincidir amb més d’un tipus d’argument quan es crida a la funció.

  • Si hi ha més d’un paràmetre amb el tipus ANY TYPE, BigQuery no imposa cap relació entre aquests arguments .
  • El tipus de dades que es mostra com a resultat de la funció no pot ser ANY TYPE. Ha ometre, el que significa que es determina de manera automàtica en funció de sql_expression, o ha de ser un tipus explícit.
  • Si passes els arguments de funcions de tipus que són incompatibles amb la definició de la funció, es produirà un error durant la trucada.

Exemples d’UDF de SQL

En el següent exemple, es mostra una UDF que empra una funció de 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 |+-----+--------+

En el següent exemple, es mostra una UDF de SQL que fa servir un paràmetre amb plantilla. La funció resultant accepta arguments de diversos tipus.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS ( (x + 4) / y);SELECT addFourAndDivideAny(3, 4) AS integer_output, addFourAndDivideAny(1.59, 3.14) AS floating_point_output;+----------------+-----------------------+| integer_output | floating_point_output |+----------------+-----------------------+| 1.75 | 1.7802547770700636 |+----------------+-----------------------+

En el següent exemple, es mostra una UDF de SQL que fa servir un paràmetre amb plantilla per mostrar l’últim element d’un arranjament de qualsevol tipus.

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 |+------------+-----------+

En el següent exemple, es mostra una UDF de SQL que utilitza una subconsulta escalar per explicar la quantitat d’usuaris amb una edat determinada en una taula d’usuari.

CREATE TEMP TABLE usersAS SELECT 1 id, 10 ageUNION ALL SELECT 2, 30UNION ALL SELECT 3, 10;CREATE TEMP FUNCTION countUserByAge(userAge INT64)AS ((SELECT COUNT(1) FROM users WHERE age = userAge));SELECT countUserByAge(10) AS count_user_age_10, countUserByAge(20) AS count_user_age_20, countUserByAge(30) AS count_user_age_30;+-------------------+-------------------+-------------------+| count_user_age_10 | count_user_age_20 | count_user_age_30 |+-------------------+-------------------+-------------------+| 2 | 0 | 1 |+-------------------+-------------------+-------------------+

UDF de JavaScript

Crea una UDF de JavaScript mitjançant la següent estructura.

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

tipus de dades de UDF de JavaScript admesos

Alguns tipus de SQL es corresponen de forma directa amb tipus de JavaScript, però altres no. BigQuery representa els tipus de la següent manera:

Tipus de dades de BigQuery Tipus de dades de JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificada en base64
FLOAT64 NUMBER
NUMERIC, BIGNUMERIC (Vista prèvia) Si un valor NUMERIC es pot representar de forma exacta com un valor de punt flotant IEEE 754 i no té una part fraccionària, es codifica com un Nombre. Aquests valors estan en el rang. En cas contrari, es codifica com una String.
STRING STRING
struct OBJECT en el qual cada camp de struct té nom
TIMESTAMP DATE amb un camp de microsegons que conté la fracció de microsecond de la marca de temps
DATE DATE

Com que JavaScript no admet un tipus de nombre enter de 64 bits, INT64 no és compatible com a tipus d’entrada per UDF de JavaScript. En el seu lloc, usa FLOAT64 per tal de representar valors com un nombre sencer, o STRING per representar-los com una string.

BigQuery admet INT64 com un tipus de dades que es mostra en les UDF de JavaScript. En aquest cas, el cos de la funció de JavaScript pot mostrar una String o un número de JavaScript. BigQuery després converteix qualsevol d’aquests tipus en INT64.

Si el valor de retorn de l’UDF de JavaScript és una Promise, BigQuery espera fins que s’estableixi la Promise. Si Promise s’estableix en un estat entregat, BigQuery mostra el seu resultat. Si Promise s’estableix en un estat rebutjat, BigQuery mostra un error.

Regles de citació

Has tancar el codi de JavaScript entre cometes . Per fragments de codi simples d’una línia, pots utilitzar una string entre cometes estàndard:

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 |+-----------+-----------+

En els casos en què el fragment conté cometes o consta de diverses línies , usa blocs amb cometes triples:

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

Inclou biblioteques de JavaScript

Pots estendre teus UDF de JavaScript amb la secció OPTIONS .En aquesta secció, es permet especificar biblioteques de codi externes per a la 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');

En l’exemple anterior, el codi en lib1.js i lib2.js està disponible per a qualsevol codi a la secció de la UDF.

Exemples d’UDF de JavaScript

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 |+-----+-----+--------------+

Pots passar el resultat d’una UDF com a entrada per a una altra UDF. Per 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 |+-----+-----+--------------+

En el següent exemple, es sumen els valors de tots els camps anomenats foo a la string JSON donada.

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 |+---------------------------------------------------------------------+---------+

per obtenir més informació per assignar els tipus de dades de BigQuery als tipus de JavaScript, consulta tipus de dades de UDF de JavaScript admesos.

Recomanacions per a les UDF de JavaScript

Realitza un filtre previ al teu entrada

Si la teva entrada es pot filtrar amb facilitat abans de passar-la a una UDF de JavaScript , és probable que la teva consulta sigui més ràpida i econòmica.

Evita l’estat mutable persistent

No magatzems ni accedeixis a un estat mutable en trucades a la UDF de JavaScript.

Utilitza la memòria de manera eficaç

l’entorn de processament de JavaScript té un límit de memòria disponible per consulta.Las consultes de UDF de JavaScript que acumulen massa estat local poden fallar a causa de l’esgotament de la memòria.

Executa una consulta amb una UDF

Fes servir Cloud Console.

Podeu fer servir Cloud Console per executar consultes amb una o més UDF.

  1. Fes clic a Redactar consulta nova.
  2. al panell Editor de consultes, ingressa la declaració de UDF. Per exemple:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)RETURNS FLOAT64 LANGUAGE js AS r""" return x*2;""";
  3. A sota de la declaració de UDF, ingressa la consulta de SQL. Per exemple:

    SELECT timesTwo(numbers) AS doublesFROM UNNEST() AS numbers;
  4. Feu clic a Run query. Els resultats de la consulta es mostren sota dels botons.

Utilitza l’eina de línia d’ordres de bq

Podeu fer servir l’eina de línia de comandaments de bq de l’SDK de Cloud per executar una consulta que contingui una o més UDF.

Fes servir la següent sintaxi per executar una consulta amb una UDF:

bq query <statement_with_udf_and_query>

UDF autoritzades

Una UDF autoritzada és una UDF que està autoritzada per accedir a un conjunt de dades particular.La UDF pot consultar taules en el conjunt de dades, fins i tot si l’usuari que crida a la UDF no té accés a aquestes taules.

Les UDF autoritzades et permeten compartir resultats de consultes amb usuaris o grups específics sense donar-los a aquests usuaris o grups accés a les taules subjacents. Per exemple, una UDF autoritzada pot calcular una agregació en les dades o buscar un valor de taula i usar aquest valor en un càlcul.

Per a autoritzar una UDF, pots utilitzar Google Cloud Console, l’API de REST o l’eina de línia de comandaments de bq:

Console

  1. Ves a la pàgina de BigQuery en Cloud Console.

    Anar a BigQuery

  2. al tauler de navegació, a la secció Recursos, s’expandeix el teu projecte i selecciona un conjunt de dades.

  3. al tauler de detalls, feu clic a Autoritzar rutines.

  4. a la pàgina rutines autoritzades, a la secció Autoritzar rutina , selecciona la identificació de el projecte, la identificació de el conjunt de dades i la identificació de la rutina que vols autoritzar.

  5. Feu clic a Afegeix autorització.

API

  1. Crida a mètode datasets.get per recuperar el conjunt de dades a què vols que accedeixi la UDF . El cos de la resposta conté una representació de el recurs Dataset.

  2. Afegeix el següent objecte JSON a la matriu access en el recurs Dataset:

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

    Aquí:

    • DATASET_NAME és el nom del conjunt de dades que conté la UDF.
    • PROJECT_ID és la identificació de el projecte que conté la UDF.
    • ROUTINE_NAME és el nom de la UDF.
  3. Crida a mètode dataset.update amb la representació Dataset modificada.

bq

  1. Fes servir la comanda bq show per obtenir la representació JSON del conjunt de dades a què vols que accedeixi la UDF. El resultat de la comanda és una representació JSON de el recurs Dataset .Guarda el resultat en un fitxer local.

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

    reemplaça TARGET_DATASET pel nom del conjunt de dades a què tindrà accés la UDF.

  2. Edita l’arxiu per afegir el següent objecte JSON a la matriu access en el recurs Dataset:

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

    Aquí:

    • DATASET_NAME és el nom del conjunt de dades que conté la UDF.
    • PROJECT_ID és la identificació de el projecte que conté la UDF.
    • ROUTINE_NAME és el nom de la UDF.
  3. Utilitza la comanda bq update per actualitzar el fitxer.

    bq update --source dataset.json TARGET_DATASET

exemple d’una UDF autoritzada

A continuació, es mostra un exemple de creació i ús d’una UDF autoritzada.

  1. Crea dos conjunts de dades anomenats private_dataset i public_dataset. Per obtenir més informació sobre la creació d’un conjunt de dades, consulta Crea un conjunt de dades.

  2. Executa la següent declaració per crear una taula anomenada private_table en private_dataset:

    CREATE OR REPLACE TABLE private_dataset.private_tableAS SELECT key FROM UNNEST() key;
  3. Executa la següent declaració per crear una UDF crida count_key en public_dataset. La UDF inclou una declaració SELECT en private_table.

    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. Atorga la funció bigquery.dataViewer a un usuari en el conjunt de dades public_dataset. Aquesta funció inclou el permís bigquery.routines.get, que li permet a l’usuari trucar a la funció.Si vols obtenir informació per assignar controls d’accés a un conjunt de dades, consulta Controla l’accés als conjunts de dades.

  5. En aquest punt, l’usuari té permís per cridar a la funció count_key, però no pot accedir a la taula en private_dataset. Si l’usuari intenta cridar a la funció, rebrà un missatge d’error similar a el següent:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.
  6. Amb l’eina de línia de comandaments de bq, ingressa la comanda show com es mostra a continuació:

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

    El resultat es guarda en un arxiu local anomenat dataset.json.

  7. Edita dataset.json per afegir el següent objecte JSON a la matriu access:

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

    Reemplaça PROJECT_ID per la identificació de l’ projecte per public_dataset.

  8. Amb l’eina de línia de comandaments de bq, ingressa la comanda update com es mostra a continuació:

    bq update --source dataset.json private_dataset
  9. Per verificar que la UDF tingui accés a private_dataset, l’usuari pot executar la següent consulta:

Afegeix descripcions a les UDF

Per a afegir una descripció a una UDF, segueix aquests passos:

Console

  1. Ves a la pàgina de BigQuery en Cloud Console.

    Anar a BigQuery

  2. al panell Explorador, s’expandeix el teu projecte i conjunt de dades i, després, selecciona la funció.

  3. al tauler Detalls, feu clic a la icona de llapis al costat de Descripció per editar el text de la descripció.

  4. en el diàleg, ingressa una descripció en la casella o edita la descripció existent. Fes clic a Actualitza per desar el nou text de la descripció.

Com a alternativa, podeu utilitzar una consulta de SQL estàndard per tal d’actualitzar la descripció mitjançant el paràmetre description de camp OPTIONS. En el quadre Editor de consultes, ingressa la definició de la funció i, després, afegeix la següent línia:

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

Reemplaça DESCRIPTION per la descripció que vols afegir.

bq

Pots editar la descripció d’una funció des de la línia de comandes mitjançant la sintaxi bq query de les UDF i l’eina de línia d’ordres de bq. Especifica SQL estàndard amb una marca --nouse_legacy_sql o --use_legacy_sql=false i, després, ingressa la definició de la teva funció. Per establir el paràmetre description en el camp OPTIONS, afegeix la següent línia al teu definició:

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

Reemplaça DESCRIPTION per la descripció que vols afegir.

limitacions

Les següents limitacions s’apliquen a funcions temporals i persistents definides pel usuari:

  • No s’admeten els objectes DOM WindowDocument i Node, ni les funcions que els requereixen.
  • No s’admeten les funcions de JavaScript que es basen en el codi natiu.
  • Una UDF de JavaScript pot esgotar el temps d’espera i evitar que es completi la teva consulta. Els temps d’espera poden ser de tan sols 5 minuts, però poden variar segons diferents factors, inclosos el temps de CPU de l’usuari que consumeix la teva funció i la mida de les entrades i sortides en la funció de JavaScript.
  • Les operacions a nivell de bits en JavaScript manegen només els 32 bits més significatius
  • Les UDF estan subjectes a certs límits de freqüència i límits de quota. Per a més informació , consulta Límits de UDF .

Les següents limitacions s’apliquen a les funcions persistents definides per l’usuari :

  • Cada conjunt de dades només pot contenir una UDF persistent amb el mateix nom. No obstant això, pots crear una UDF el nom sigui igual a el d’una taula en el mateix conjunt de dades.
  • Quan facis referència a una UDF persistent des d’una altra UDF persistent o vista lògica , has de qualificar el nom amb el conjunt de dades. Per exemple : a CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

Les limitacions següents s’apliquen a les funcions temporals definides per l’usuari.

Deixa un comentari

L'adreça electrònica no es publicarà. Els camps necessaris estan marcats amb *