BigQuery permite crear Stored Procedures y/o funciones usando como lenguaje base a Javascript. En este post veremos las UDF o las funciones definidas por el usuario.
Una función definida por el usuario (UDF) nos permite crear una función mediante una expresión SQL o código JavaScript.
Una UDF puede ser:
- Persistente o
- Temporal (en el alcance de una única consulta).
Aquí podemos ver un ejemplo sacado de la página de documentación oficial de GCP:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64) RETURNS FLOAT64 LANGUAGE 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 product FROM numbers; |
Se trata de una UDF temporal (usando TEMPORARY o TEMP) que multiplica dos números tipo FLOAT64. Dentro del privote ``numbers`` habrá dos uniones con los datos a trabajar; invocaremos la función mediante una consulta SELECT y asignará el resultado como ``product``.
Tipos de datos permitidos
| Tipos de datos en BigQuery | Tipos de datos en Javascript |
| ARRAY | ARRAY |
| BOOL | BOOLEAN |
| BYTES | STRING base 64 |
| NUMERIC, BIGNUMERIC | Valor punto flotante |
| STRING | STRING |
| STRUCT | OBJECT |
| TIMESTAMP | DATE |
| DATE | DATE |
| JSON | OBJECTS, ARRAY |
Recordar que en Javascript no existen los tipos de manera explícita.
Ejemplo 1. Crearemos una función (persistente) BigQuery que use JS como lenguaje base para obtener la longitud de una cadena.
1 2 3 4 5 6 7 | CREATE OR REPLACE FUNCTION `myproject.mydataset.longitud`(input STRING) RETURNS STRING LANGUAGE js AS ''' if(input === '' || input ===null){ return "La cadena tiene una longitud de 0 caracteres."; }else{ return "La cadena tiene una longitud de: "+input.length+" caracteres."; } ''' |
Para hacer uso de ella la invocaremos de esta forma:
1 2 | DECLARE cadena STRING DEFAULT ''; SELECT `myproject.mydataset.longitud`(cadena) as resultado; |
El dato de entrada será la variable ``cadena`` cuyo valor será vacío. Lo que nos devolverá:
1 | La cadena tiene una longitud de: 0 caracteres. |
Está función obtiene la longitud de una cadena introducida por el usuario. No mucho por explicar.
Ejemplo 2. Crear una sencilla función para evaluar si una condición o valor booleano es "correcta" si es true, "incorrecta" si es false.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE FUNCTION `myproject.mydataset.checkAssert`(condition BOOL) RETURNS STRING LANGUAGE js AS ''' var result = "Evaluacion: "+condition+"."; if(condition){ result += " Es correcta."; }else{ result += " Es incorrecta."; } return result; ''' |
Invocamos la función:
1 2 3 4 | DECLARE condition BOOL; SET condition= 34 > 0; SELECT `myproject.mydataset.checkAssert`(condition) AS resultado ; |
Hacer uso de UDF con Javascript como lenguaje base puede ayudarnos a resolver tareas que quizás de una manera más nativa sería algo más complicado.
Continuaremos con este tema en próximas entregas.
Enlaces:
https://cloud.google.com/bigquery/docs/user-defined-functionshttps://stackoverflow.com/questions/62271743/bringing-in-a-function-from-javascript-to-big-query-standardsql
https://hoffa.medium.com/new-in-bigquery-persistent-udfs-c9ea4100fd83
https://briangrinstead.com/blog/js-functions-bigquery/
