Funcións SQL definidas polo usuario

BigQuery admite funcións definidas polo usuario (UDF). Un UDF permítelle crear unha función por outra expresión SQL ou JavaScript. Estas funcións aceptan columnas de entrada e realizan accións e mostre o resultado destas accións como valor. Para obter información sobre as funcións definidas polo usuario en SQL herdada, consulte as funcións definidas polo usuario en SQL herdado.

UDF pode ser persistente ou temporal. Pode reutilizar a UDF persistentes en varias consultas e UDF temporais nunha única consulta.

Sintaxe do UDF

Para crear unha UDF persistente, use a seguinte sintaxe:

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

Para crear un UDF temporal, use a seguinte sintaxe:

Esta sintaxe consiste nos seguintes compoñentes:

  • Crear {Función | Ou substituír a función | Función se non existe}. Crea unha función ou actualiza-lo. Para substituír unha función existente co mesmo nome, use a palabra clave OR REPLACE. Use a CLAUSE IF NOT EXISTS Se desexa tratar a consulta completa e quere que non se realice ningunha acción no caso de que xa hai unha función co mesmo nome.

    /li>

  • project_name é o nome do proxecto no que creará a función. A configuración predeterminada é o proxecto que executa esta consulta DDL. Se o nome do proxecto contén caracteres especiais, como dous puntos, debe estar entre acentos graves ` (exemplo: `google.com:my_project`).

  • dataset_name é o nome do conxunto de datos nos que creará a función. O valor predeterminado é defaultDataset na solicitude.

  • namado_parameter. Consiste nun par de param_name e param_type separado por comas. O valor de param_type é un tipo de datos de bigquery. Para un SQL UDF, o valor de param_type tamén pode ser ANY TYPE.

  • Detasterism_specifier. Só se aplica ás funcións definidas polo usuario de JavaScript. Proporciona unha suxestión a BigQuery para decidir se o resultado da consulta pode ser caché. Pode ser un dos seguintes valores:

    • DETERMINISTIC: a función sempre mostra o mesmo resultado cando se aproban os mesmos argumentos. O resultado da consulta pode almacenarse na caché. Por exemplo, se a función add_one(i) mostra sempre i + 1, a función é determinista.

    • NOT DETERMINISTIC: a función non sempre mostra o mesmo resultado cando se aproben os mesmos argumentos e, polo tanto, non poden ser caché. Por exemplo, se add_random(i) mostra i + rand(), a función non é determinista e que o bigquery non usará resultados almacenados na caché.

      Se todas as funcións invocadas son deterministas, o bigquery intentará caché o resultado, a menos que os resultados non sexan capturados por outros motivos. Para obter máis información, consulte a sección sobre como usar consultas almacenadas con caché.

  • . Especifica o tipo de datos que mostra a función.

    • Se a función está definida en SQL, a cláusula RETURNS é opcional. Se a cláusula , BigQuery deduce o tipo de resultado da función do corpo da función SQL cando unha consulta chama a función.
    • se o A función defínese en JavaScript, a claveRETURNS é obrigatoria. Se desexa máis información sobre os valores permitidos para data_type, consulte os tipos de datos UDF soportados de JavaScript.
  • Como (sql_expression). Especifica a expresión SQL que define a función.

  • . Para unha UDF de JavaScript, especifique unha disposición das bibliotecas de JavaScript para incluír na definición da función.

  • como javascript_code. Especifica a definición dunha función de JavaScript. é un literal de cadea.

    • Se o código inclúe citas e barras invertidas, debes escapar representando como un cadea non procesada. Por exemplo, o códigoreturn "\n"; pode prepararse como unha das seguintes opcións:
      • Entrequilled string "return \"\\n\";". Os bancos e bares invertidos deben ser escalados.
      • Entre triples comentarios: """return "\\n";""". As barras invertidas deben ser gardadas, mentres que as comiñas non.
      • Raw string: r"""return "\n";""". Non se necesita fuga.
    • para borrar unha función definida polo usuario persistente, use a seguinte sintaxe:

    DROP FUNCTION dataset_name.]function_name

    O UDF temporal caduca axiña que a consulta, polo que as declaracións DROP FUNCTION son compatibles con UDF temporal en secuencias de comandos e procedementos

    SQL UDF

    Crear SQL UDF coa seguinte sintaxe :.

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

    SQL UDF parámetros con modelo

    un parámetro con plantilla con ANY TYPE pode coincidir con máis dun tipo de argumento cando a función chámase.

    • Se hai máis dun parámetro co tipo ANY TYPE, BigQuery non impón ningunha relación entre estes argumentos.
    • O tipo de datos que se mostra como resultado da función non pode ser ANY TYPE. Debe omitirse, o que significa que está determinado automaticamente dependendo de sql_expression ou debe ser un tipo explícito.
    • Se pasas as funcións argumentos tipos son incompatibles coa definición da función, producirase un erro durante a chamada.

    SQL UDF Exemplos

    No seguinte exemplo, amósase un UDF que usa un Función 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 |+-----+--------+

    No seguinte exemplo, amósase un SQL UDF que usa un parámetro cun modelo. A función resultante acepta argumentos de varios tipos.

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

    No seguinte exemplo, un SQL UDF que usa un parámetro con plantilla para mostrar o último elemento dun arranxo de calquera tipo.

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

    No seguinte exemplo, un SQL UDF que usa un subconsulto escalar para contar o número de usuarios cunha idade determinada nunha táboa de usuario .

    JavaScript UDF

    Crear un UDF JavaScript mediante a seguinte estrutura.

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

    Javascript UDF Tipos de datos soportados

    Algúns tipos de SQL corresponden directamente con tipos de JavaScript, pero outros non. BigQuary representa os tipos do seguinte xeito:

    Tipo de datos de bigquery Tipo de datos JavaScript Array Array
    bool Booleano
    bytes cadea codificada en base64
    float64 número
    numérico, bignumumérico (previsualización) Se un valor numérico pode ser representado con precisión como un valor de punto IEEE flotante 754 e non ten unha parte fraccionada , está codificado como un número. Estes valores están no rango. Se non, está codificado como unha cadea.
    cadea
    struct obxecto en que cada un Campo de estrutura ten nome
    timestamp data cun campo de microsegundos que contén a fracción de microsecond do Timestamp
    Data Data

    porque JavaScript non admite un tipo enteiro de 64 bits, INT64 non é compatible como un tipo de entrada para JavaScript UDF. En vez diso, use para representar valores como un número enteiro ou para representalos como unha cadea.

    BigQuery soporta INT64 como tipo de datos mostrado na UDFS JavaScript. Neste caso, o corpo da función de JavaScript pode mostrar unha cadea ou un número de javascript. BigQuery converte algún destes tipos en INT64.

    Se o valor de retorno de JavaScript UDF é un Promise, WATQUERY agarda ata o Promise está establecido. Se Promise está ambientado nun estado entregado, BigQuery mostra o seu resultado. Se Promise está configurado nun estado rexeitado, BigQuery mostra un erro.

    Regras de cita

    Debe incluír o código JavaScript en comiñas. Para fragmentos de código sinxelos dunha liña, pode usar unha cadea entre comillas estándar:

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

    Nos casos en que o fragmento contén comiñas ou consta de varias liñas, use bloques con comiñas triplas:

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

    Inclúe JavaScript bibliotecas

    pode estender o seu JavaScript UDF coa sección OPTIONS. Nesta sección, ten permiso para especificar bibliotecas de código externo para a 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');

    No exemplo anterior, o código en lib1.js e lib2.js está dispoñible para calquera código do do UDF.

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

    Pode pasar o resultado dun UDF como entrada para outro UDF. Por exemplo:

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

    No seguinte exemplo, os valores de todas as chamadas engádense foo en A cadea json dada.

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

    Se desexa máis información para asignar os tipos de datos de BigQuery a tipos de JavaScript, consulte os tipos de datos JavaScript UDF admitidos

    Recomendacións para javascript udfs

    Realizar un filtro antes da súa entrada

    Se a súa entrada pode ser facilmente filtrada antes de pasarla a un UDF de JavaScript, é probable que a túa consulta é máis rápida e rápida.

    Evita o estado mutable persistente

    Non almacéns ou accede a unha chamada de estado mutable ao JavaScript UDF.

    Use a memoria Efectivamente

    O ambiente de procesamento de JavaScript ten un límite de memoria dispoñible por consulta. Javascript UDF consultas que acumulan demasiado o estado local pode fallar debido ao esgotamento da memoria.

    Executar unha consulta con UDF

    Usa a consola de nube.

    Pode usar a consola de nube para executar consultas con un ou máis UDF.

    1. Prema no borrador de nova consulta.
    2. No panel de editor de Querue, introduza a instrución UDF. Por exemplo:

      CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)RETURNS FLOAT64 LANGUAGE js AS r""" return x*2;""";
    3. Debaixo da instrución UDF, introduza a consulta SQL. Por exemplo:

      SELECT timesTwo(numbers) AS doublesFROM UNNEST() AS numbers;
    4. Prema na consulta executar. Os resultados da consulta amósanse debaixo dos botóns.

  • Use a ferramenta de liña de comandos BQ

    . Pode usar a ferramenta de liña de da nube SDK para executar unha consulta que contén un ou máis UDF.

    Use a seguinte sintaxe para executar unha consulta cun UDF:

bq query <statement_with_udf_and_query>

UDF autorizado

Un UDF autorizado é un UDF que está autorizado para acceder a un determinado conxunto de datos. UDF pode consultar táboas no conxunto de datos, mesmo se O usuario que chama a UDF non ten acceso a estas táboas.

Os UDF Autorizados permítenlle compartir resultados de consultas con usuarios ou grupos específicos sen dar a estes usuarios ou acceder aos grupos ás táboas subxacentes. Por exemplo, unha UDF autorizada pode calcular unha agregación nos datos ou buscar un valor de táboa e usar ese valor nun cálculo.

Para autorizar un UDF, pode usar a consola de Google Cloud, o repouso API o comando -LINE ferramenta de bq:

consola

  1. Ir á páxina de BigQuery en Cloud Console.

    Ir a BigQuery

  2. No panel de navegación, na sección Recursos, expandir o seu proxecto e seleccionar un conxunto de datos.

  3. No panel de detalles, faga clic en Autorizar rutinas.

  4. Na páxina de rutinas autorizadas, na sección de rutina de autorización, seleccione o proxecto ID, o conxunto de datos ID e ID de rutina que desexa autorizar.

  5. Prema Engadir autorización.

API

  1. Chama ao método datasets.get para recuperar o conxunto de datos que desexa a UDF. O corpo de resposta contén unha representación do recurso Dataset.

  2. Engadir o seguinte obxecto JSON á matriz access no recurso Dataset:

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

    aquí:

    • dataset_name é o nome do conxunto de datos que contén o UDF.
    • project_id é o ID do proxecto que contén a UDF.
    • rutine_name é o nome do UDF.
  3. Chamar o método dataset.update coa representación Dataset modificada.

bq

  1. Use o comando bq show para obter a representación de JSON do conxunto de datos que desexa acceder á UDF. O resultado do comando é unha representación de JSON do recurso Dataset. Guard o resultado nun ficheiro local.

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

    Substituír Target_Dataset mediante o nome do conxunto de datos aos que terá a UDF.

  2. Editar o ficheiro para engadir o seguinte obxecto JSON á matriz access no recurso Dataset:

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

    aquí:

    • dataset_name é o nome do conxunto de datos que contén a UDF.
    • project_id é o ID do proxecto que contén a UDF.
    • rutine_name é o nome do UDF.
  3. Use o comandobq update

para actualizar o ficheiro.

bq update --source dataset.json TARGET_DATASET

Exemplo dun UDF

ABAIXO

A continuación, móstrase un exemplo de creación e uso dun UDF autorizado.

  1. Crear dous conxuntos de datos chamados private_dataset e public_dataset. Para obter máis información sobre a creación dun conxunto de datos, a consulta crea un conxunto de datos.

  2. Executar a seguinte declaración para crear unha táboa chamada private_table en private_dataset:

    CREATE OR REPLACE TABLE private_dataset.private_tableAS SELECT key FROM UNNEST() key;
  3. Executa a seguinte declaración para crear unha chamada UDF count_key en public_dataset. O UDF inclúe un 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. concede a función bigquery.dataViewer a un usuario no conxunto de datos public_dataset. Esta función inclúe o permiso bigquery.routines.get, que permite ao usuario chamar a función. Se desexa obter información para asignar controis de acceso a un conxunto de datos, os controis de consulta acceden a conxuntos de datos.

  5. Neste punto, o usuario ten permiso para chamar a función count_key, pero non pode acceder á táboa en private_dataset. Se o usuario trata de chamar á función, recibirá unha mensaxe de erro similar á seguinte:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.
  6. coa ferramenta de liña , introduza o como se mostra a continuación:

    O resultado gárdase nun ficheiro local chamado dataset.json.

  7. Editar dataset.json Para engadir o seguinte obxecto JSON á matrizaccess:

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

    substituír project_id polo ID do proxecto para public_dataset.

  8. Coa ferramenta de liña de comandos de bq, introduza o Como se mostra a continuación:

    bq update --source dataset.json private_dataset
  9. para verificar que o UDF ten acceso a private_dataset, o usuario pode executar a seguinte consulta:

Engadir descricións a UDF

Para engadir unha descrición a unha UDF, siga estes pasos:

Consola

  1. Ir á páxina de BigQuery na consola de nube.

    Ir a BigQuery

  2. no Panel Explorador, expandir o seu proxecto e conxunto de datos e, a continuación, seleccionar a función.

  3. No panel de detalles, faga clic na icona do lapis ao lado da descrición para editar o texto da descrición .

  4. No diálogo, introduza unha descrición na caixa ou edite a descrición existente. Faga clic en Actualizar para gardar o novo texto de descrición.

Como alternativa, pode usar unha consulta SQL estándar para actualizar a descrición usando o do campo OPTIONS. No cadro editor de Querry, introduza a definición da función e, a continuación, engada a seguinte liña:

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

substituíndo a descrición pola descrición que desexa engadir.

BQ

Pode editar a descrición dunha función da liña de comandos usando a sintaxe bq query da UDF e a ferramenta de liña de BQ Comandos. Especifica SQL estándar cunha marca --nouse_legacy_sql ou --use_legacy_sql=false e, a continuación, introduza a definición da súa función. Para configurar o parámetro no campoOPTIONS

, engada a seguinte liña á súa definición:

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

substitúe a descrición da descrición que desexa engadir.

Limitacións

As seguintes limitacións aplícanse a funcións temporais e persistentes definidas pola Usuario:

  • Obxectos DOM non son compatibles Window e Node, nin as funcións que lles requiren.
  • As funcións de JavaScript que están baseadas no código nativo non son compatibles.

  • A JavaScript UDF pode esgotar o tempo de espera e evitar que a súa consulta sexa completado. Os tempos de espera poden ser de só 5 minutos, pero poden variar dependendo de diferentes factores, incluído o tempo de CPU do usuario que consome a súa función eo tamaño das entradas e saídas na función JavaScript.
  • operacións de bits en JavaScript manexar só os 32 bits máis significativos
  • Os UDF están suxeitos a certos límites de frecuencia e límites de cotas. Para obter máis información, consulte os límites de UDF.

As seguintes limitacións aplícanse ás funcións persistentes definidas polo usuario:

  • Cada conxunto de datos só pode conter un UDF persistente co mesmo nome. Non obstante, pode crear un UDF cuxo nome é igual ao dunha táboa no mesmo conxunto de datos.
  • Cando se refire a un UDF persistente doutro UDF persistente ou unha vista lóxica, debe cualificar o nome co conxunto de datos. Por exemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

As seguintes limitacións aplícanse ás funcións temporais definidas polo usuario.

Deixa unha resposta

O teu enderezo electrónico non se publicará Os campos obrigatorios están marcados con *