Funcțiile SQL definite de utilizator

Bigquery acceptă funcții definite de utilizator (UDF). Un UDF vă permite să creați o funcție cu o altă expresie SQL sau JavaScript. Aceste funcții acceptă coloane de intrare și efectuează acțiuni și apoi arată rezultatul acestor acțiuni ca valoare. Pentru informații despre funcțiile definite de utilizator în SQL moștenit, consultați funcțiile definite de utilizator în SQL moștenit

UDF poate fi persistent sau temporar. Puteți reutiliza UDF-urile persistente în mai multe consultări și UDF-uri temporare într-o singură interogare.

Sintaxă a UDF

Pentru a crea un UDF persistent, utilizați următoarea sintaxă:

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

Pentru a crea un UDF temporar, utilizați următoarea sintaxă:

Această sintaxă constă din următoarele componente:

  • creare {funcție | Sau înlocuiți funcția | Funcția dacă nu există}. Creați o funcție sau actualizați-o. Pentru a înlocui o funcție existentă cu același nume, utilizați cuvântul cheie OR REPLACE. Utilizați clauza IF NOT EXISTS dacă doriți să tratați interogarea așa cum ați terminat și doriți ca nicio acțiune să se facă în cazul în care există deja o funcție cu același nume.

  • Project_name este numele proiectului în care veți crea funcția. Configurația implicită este proiectul care execută această interogare DDL. Dacă numele proiectului conține caractere speciale, cum ar fi două puncte, trebuie să fie între accente grave ` (exemplu: `google.com:my_project`).

  • Dataset_name este numele setului de date în care veți crea funcția. Valoarea implicită este defaultDataset în cerere.

  • Named_parameter. Se compune dintr-o pereche de param_name și param_type separate prin virgule. Valoarea param_type este un tip de date bigquery. Pentru un SQL UDF, valoarea param_type poate fi, de asemenea, ANY TYPE

  • Deterzism_specificator. Se aplică numai funcțiilor definite de utilizatorul JavaScript. Oferă o sugestie la Bigquery pentru a decide dacă rezultatul interogării poate fi cache. Poate fi una dintre următoarele valori:

    • DETERMINISTIC

Afișează întotdeauna i + 1, funcția este deterministă.

  • <
  • iv id = „1339126223”

  • : Funcția nu arată întotdeauna același rezultat atunci când aceleași argumente sunt transmise și, prin urmare, nu pot fi memorate în cache. De exemplu, dacă add_random(i) arată i + rand(), funcția nu este deterministă și bigquery nu va folosi rezultatele stocate în memoria cache.

    <

    Dacă toate funcțiile invocate sunt deterministe, bigquery va încerca să cache rezultatul, cu excepția cazului în care rezultatele nu pot fi prinse din alte motive. Pentru mai multe informații, consultați secțiunea privind utilizarea interogărilor stocate cache.

  • . Specifică tipul de date care arată funcția.

    • Dacă funcția este definită în SQL, clauza este opțională. Dacă clauza , bigquery deduce tipul de rezultat al funcției din corpul funcției SQL atunci când o interogare numește funcția.
    • Daca Funcția este definită în JavaScript, clauza iv ID = „49E0F3709”
  • este obligatorie. Dacă doriți mai multe informații despre valorile permise pentru data_type, consultați tipurile de date JavaScript UDF acceptate.

  • Ca (sql_expression). Specifică expresia SQL care definește funcția.

  • . Pentru un UDF JavaScript, specificați un aranjament al bibliotecilor JavaScript pentru a include în definiția funcției.

  • ca javascript_code. Specifică definiția unei funcții JavaScript. este un string literal.

    • Dacă codul include citate și bare inversate, trebuie să scăpați reprezentat ca un String neprelucrat. De exemplu, codul return "\n"; poate fi preparat ca una dintre următoarele opțiuni:
      • String întreprind "return \"\\n\";". Băncile și barele inversate trebuie să fie escaled.
      • șir între comentarii triple: """return "\\n";""". Barele inversate trebuie salvate, în timp ce citatele nr.
      • șir brut: r"""return "\n";""". Pentru a șterge o funcție definită de utilizatorul persistent, utilizați următoarea sintaxă:

        DROP FUNCTION dataset_name.]function_name

        UDF temporar expiră imediat ce interogarea, astfel încât declarațiile DROP FUNCTION sunt numai compatibile cu UDF-urile temporare în secvențe Comenzi și proceduri.

        sql udf

        creare sql udf cu următoarea sintaxă:

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

        sql udf parametrii cu șablon

        un parametru cu șablon cu ANY TYPE se poate potrivi mai mult de un tip de argument atunci când funcția se numește.

        • Dacă există mai mult de un parametru cu tipul ANY TYPE, bigquery nu impune nicio relație între aceste argumente.
        • Tipul de date afișate ca rezultat al funcției nu poate fi ANY TYPE. Aceasta trebuie să fie omisă, ceea ce înseamnă că este determinată automat în funcție de sql_expression sau trebuie să fie un tip explicit.
        • Dacă treceți tipurile de funcții care ar argumente sunt incompatibile cu definiția funcției, va apărea o eroare în timpul apelului.

        SQL UDF Exemple

        În exemplul următor, este afișat un UDF care utilizează a Funcția 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 |+-----+--------+

        În exemplul Udf SQL care utilizează un parametru cu un șablon este afișat. Funcția rezultată acceptă argumente de diferite tipuri.

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

        În următorul exemplu, un SQL UDF care utilizează un parametru cu șablon pentru a arăta ultimul element al unui aranjament de orice fel.

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

        În următorul exemplu, un SQL UDF care utilizează o subconst scalar pentru a număra numărul de utilizatori cu o vârstă determinată într-o masă de utilizator .

        JavaScript UDF

        Creați un UDF JavaScript prin următoarea structură.

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

        JavaScript UDF Tipuri de date acceptate

        Unele tipuri SQL corespund direct cu tipurile JavaScript, dar altele nu. Bigquery reprezintă tipurile după cum urmează:

        Tipul de date bigquery Tipul de date JavaScript
        ARRAY ARRAY
        Boolean
        Bytes String codificat pe Base64
        Float64 Numar
        Numeric, bignumeric (previzualizare) Dacă o valoare numerică poate fi reprezentată cu precizie ca o valoare de punct de ieee plutitoare 754 și nu are o parte fracționată , este codificat ca un număr. Aceste valori sunt în intervalul. În caz contrar, este codificat ca șir.
        str.
        obiect în care fiecare Structura câmpului are numele
        Timestamp Data cu un câmp microsecund care conține fracțiunea de microsecond al TIMSTAMP

        deoarece JavaScript nu acceptă un tip întreg de 64 de biți, INT64 Nu este compatibil ca un tip de intrare pentru JavaScript UDF. În schimb, utilizați FLOAT64 pentru a reprezenta valori precum un număr întreg sau pentru a le reprezenta ca și șir.

        Bigquery suportă INT64 ca tip de date afișat în UDF-urile JavaScript. În acest caz, organismul funcției JavaScript poate afișa un șir sau un număr JavaScript. Bigquery apoi convertește oricare dintre aceste tipuri în INT64.

        Dacă valoarea de returnare a JavaScript UDF este un Promise, Bigquery așteaptă până se stabilește Promise. Dacă Promise este setat într-o stare livrată, Bigquery arată rezultatul acestuia. Dacă Promise este setat într-o stare respinsă, bigquery afișează o eroare.

        Reguli de citare

        Trebuie să închideți codul JavaScript în citate. Pentru fragmente de cod simplu dintr-o linie, puteți utiliza un șir între Comilale 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 |+-----------+-----------+

        în cazul în care fragmentul conține citate sau constă din mai multe linii, utilizați Blocuri cu citate triple:

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

        include biblioteci JavaScript

        Puteți extinde UDF-ul dvs. JavaScript cu secțiunea OPTIONS. În această secțiune, este permisă specificarea bibliotecilor de cod extern pentru 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');

        în exemplul anterior, codul din lib1.js și lib2.js este disponibil pentru orice cod în secțiunea al UDF.

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

        Puteți trece rezultatul unui UDF ca intrare pentru un alt UDF. De exemplu:

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

        În următorul exemplu, valorile tuturor apelurilor sunt adăugate foo în Stringul JSON a dat.

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

        Dacă doriți mai multe informații pentru a atribui tipurile de date de bigquery la tipurile JavaScript, consultați tipurile de date JavaScript UDF admise

        Recomandări pentru JavaScript UDFS

        Efectuați un filtru înainte de intrarea dvs.

        Dacă intrarea dvs. poate fi filtrată cu ușurință înainte de ao trece la un UDF JavaScript, este probabil că interogarea dvs. este mai rapidă și mai rapidă.

        Evită starea mutabilă persistentă

        nu depozite sau accesați o solicitare de stat mutabilă către JavaScript UDF.

        Utilizați memoria Eficient

        Mediul de prelucrare JavaScript are o limită de memorie disponibilă prin consultare. JavaScript UDF interogări care acumulează starea prea locală poate eșua din cauza epuizării memoriei.

        Executați o interogare cu un UDF

        utilizează consola cloud.

        Puteți utiliza consola cloud pentru a rula interogări cu unul sau mai multe UDF.

        1. Faceți clic pe proiectul de interogare nouă.
        2. În panoul Editor Querue, introduceți instrucțiunea UDF. De exemplu:

          CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)RETURNS FLOAT64 LANGUAGE js AS r""" return x*2;""";
        3. sub instrucțiunea UDF, introduceți interogarea SQL. De exemplu:

          SELECT timesTwo(numbers) AS doublesFROM UNNEST() AS numbers;
        4. Faceți clic pe Query Run. Rezultatele interogării sunt afișate sub butoane.

      • Utilizați instrumentul de comandă BQ

        . Puteți utiliza instrumentul de linie de al norului SDK pentru a rula o interogare care conține unul sau mai multe UDF.

        Utilizați următoarea sintaxă pentru a rula o interogare cu un UDF:

        bq query <statement_with_udf_and_query>

        UDF autorizat

        Un UDF autorizat este un UDF autorizat să acceseze un anumit set de date. UDF poate consulta tabelele din setul de date, chiar dacă Utilizatorul care apelează UDF nu are acces la aceste tabele.

        UDF-urile autorizate vă permit să împărtășiți rezultatele interogărilor cu anumiți utilizatori sau grupuri, fără a oferi acești utilizatori sau grupuri acces la tabelele subiacente. De exemplu, un UDF autorizat poate calcula o agregare în date sau poate căuta o valoare a tabelului și poate utiliza această valoare într-un calcul.

        Pentru a autoriza un UDF, puteți utiliza Google Cloud Console, restul API comanda – Instrumentul de bq:

      • Du-te la pagina de bigquery în consola cloud.

        Du-te la bigquery

      • În panoul de navigare, în secțiunea Resurse, extindeți-vă proiectul și selectați un set de date.

      • În panoul Detalii, faceți clic pe Authorizeaza rutine.

      • pe pagina de rutină autorizată, în secțiunea de rutină Autorizează, selectați ID-ul proiectului, setul de date ID-ul și ID-ul de rutină pe care doriți să-l autorizați.

      • Apăsați pe Adăugare autorizare.

      • api

        1. Apelați metoda datasets.get pentru a recupera setul de date dorit UDF. Organismul de răspuns conține o reprezentare a resursei Dataset.

        2. Adăugați următorul obiect JSON la matria în resursa Dataset:

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

        aici:

        • Dataset_name este numele setului de date care conține UDF.
        • proiect_id este ID-ul proiectului care conține UDF.
        • rutine_name este numele UDF.

        sunați la metoda dataset.update cu reprezentare Dataset modificat.

        bq

        1. Utilizați comanda bq show pentru a obține reprezentarea JSON a setului de date pe care doriți să le accesați UDF. Rezultatul comenzii este o reprezentare JSON a resursei Dataset .guard rezultatul într-un fișier local.

          bq show --format=prettyjson TARGET_DATASET > dataset.json
  • înlocuiți-l pe numele de date setat la care va avea UDF.

  • Editați fișierul pentru a adăuga următorul obiect JSON la matria în resursa Dataset:

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

    • Dataset_name este numele setului de date care conține UDF.
    • proiect_id este ID-ul proiectului care conține UDF.
    • rutine_name este numele UDF.
    • Utilizați comanda pentru a actualiza fișierul.

      bq update --source dataset.json TARGET_DATASET

    Exemplu de UDF autorizat

    Mai jos, este arătat un exemplu de creare și utilizare a unui UDF autorizat.

    1. Creați două seturi de date numite private_dataset și public_dataset. Pentru mai multe informații despre crearea unui set de date, interogarea creează un set de date.

    2. Executați următoarea instrucțiune pentru a crea o masă numită private_table în

  • execută următoarea instrucțiune pentru a crea un apel UDF count_key în . UDF include o declarație în 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));
  • Granturi funcția bigquery.dataViewer la un utilizator din setul de date public_dataset. Această funcție include permisiunea bigquery.routines.get, care permite utilizatorului să apeleze funcția. Dacă doriți să obțineți informații pentru a atribui controale de acces la un set de date, consultarea controlează accesul la seturi de date.

  • În acest moment, utilizatorul are permisiunea de a apela funcția , dar nu poate accesa tabelul din private_dataset. Dacă utilizatorul încearcă să apeleze funcția, veți primi un mesaj de eroare similar cu următorul text:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.
  • cu instrumentul de linie , introduceți comanda , după cum se arată mai jos:

    Rezultatul este salvat într-un fișier local numit dataset.json

  • .

  • editare dataset.json

  • :

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

    înlocuire proiect_id de către ID-ul proiectului Pentru public_dataset.

  • cu instrumentul de linie de comandă a bq, introduceți După cum se arată mai jos:

    bq update --source dataset.json private_dataset
  • Pentru a verifica dacă UDF are acces la private_dataset, utilizatorul poate rula următoarea interogare:

  • Pentru a adăuga o descriere într-un UDF, urmați acești pași:

    Consola

    1. Du-te la pagina de bigquery în consola cloud.

      Du-te la bigquery

    2. în Panoul Explorer, extindeți-vă proiectul și setul de date, apoi selectați funcția.

    3. În panoul Detalii, faceți clic pe pictograma creionului de lângă descriere pentru editarea textului descrierii .

    4. În dialog, introduceți o descriere în casetă sau editați descrierea existentă. Faceți clic pe Actualizare pentru a salva noul text de descriere.

    ca o alternativă, puteți utiliza o interogare standard SQL pentru a actualiza descrierea folosind al câmpului OPTIONS. În caseta Editor Querry, introduceți definiția funcției, apoi adăugați următoarea linie:

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

    Înlocuirea descrierii de către descrierea pe care doriți să o adăugați.

    BQ

    Puteți edita descrierea unei funcții de la linia de comandă utilizând sintaxa bq query a UDF și a instrumentului de linie BQ comenzi. Specifică SQL standard cu un marcaj --nouse_legacy_sql sau --use_legacy_sql=false

    și apoi introduceți definiția funcției dvs. Pentru a seta parametrul în câmpul OPTIONS, adăugați următoarea linie la definiția dvs.:

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

    înlocuiește descrierea de către descrierea pe care doriți să o adăugați.

    limitări

    următoarele limitări se aplică funcțiilor temporare și persistente definite de Utilizator:

    • Obiectele DOM nu sunt acceptate Window și Node, nici funcțiile care le necesită.
    • Funcțiile JavaScript care se bazează pe codul nativ nu sunt acceptate efectuat. Timpurile de așteptare pot fi de numai 5 minute, dar pot varia în funcție de diferiți factori, inclusiv timpul CPU al utilizatorului care vă consumă funcția și dimensiunea intrărilor și ieșirilor din funcția JavaScript.

    • Operații la nivel de biți în JavaScript Mandal Doar cele 32 de biți semnificativi
    • UDFS sunt supuse anumitor limite de frecvență și limite de cotă. Pentru mai multe informații, consultați Limitele UDF.

    Următoarele limitări se aplică funcțiilor persistente definite de utilizator:

    • fiecare set de date numai că poate conține o Persistentă UDF cu același nume. Cu toate acestea, puteți crea un UDF al cărui nume este egal cu cel al unui tabel din același set de date.
    • Când consultați un UDF persistent dintr-un alt UDF persistent sau o vizualizare logică, trebuie să calificați numele cu setul de date. De exemplu:
      CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

    Următoarele limitări se aplică funcțiilor temporare definite de utilizator.

    Lasă un răspuns

    Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate cu *