Google Cloud Platform: funciones definidas por el usuario en BigQuery

SQL tiene muchas funciones integradas para realizar cálculos en los datos. Pero a veces, es posible que sus sistemas necesiten manejar datos, como strings o valores de fecha, de forma única. Las funciones definidas por el usuario son una manera eficiente de tener estos cálculos personalizados al alcance de su mano al analizar datos.

En este artículo, hablaremos sobre cómo crear, almacenar y compartir funciones definidas por el usuario en BigQuery. Una función definida por el usuario o UDF le permite crear una función reutilizable que usted defina, ya sea con otra expresión SQL o con JavaScript. Estas funciones aceptan entradas, realizan acciones y luego devuelven un valor como resultado. Una vez que haya creado una UDF, puede hacer referencia a ella en las consultas o al crear vistas lógicas, tal como lo haría con las funciones integradas.

 Veamos un ejemplo de caso de uso. Imagine una aplicación que captura datos de varias fuentes y requiere que los campos de texto se limpien antes de que puedan usarse con fines analíticos. Podemos escribir un UDF, lo llamaremos cleanse_string para abordar este problema de limpieza de datos. La expresión SQL que define la UDF hace tres cosas. Recorta los espacios en blanco, convierte el texto a minúsculas y elimina los símbolos. Después de crear y definir la UDF, en la siguiente parte de la declaración de consulta, podemos probar tres strings de texto diferentes a través de nuestra nueva función. Si ejecutamos toda la consulta. Veremos los resultados del antes y el después de cada string de texto. Una vez que estemos satisfechos con el rendimiento de la función, podemos agregarla a las declaraciones de consulta que analizan los datos de nuestra aplicación. 

#Standard_SQL

CREATE TEMP FUNCTION cleanse_string (text STRING)
RETURNS STRING
AS (REGEXP_REPLACE (LOWER (TRIM (text)), [a-zA-Z0-9]+', '')); WITH strings AS

(SELECT 'Hello, World!!!' AS text
UNION ALL
SELECT 'I am $Special$ STRING' AS text
UNION ALL
SELECT 'ABC, XYZ AS text)

SELECT text
,cleanse_string(text) AS clean_text
FROM strings;

Este ejemplo incluye lo que llamamos un UDF temporal , lo que significa que se crea y se usa dentro de una sola instrucción de consulta. Sin embargo, BigQuery también admite UDF persistentes , que se definen y almacenan dentro de un proyecto y un conjunto de datos específicos, pero que se pueden reutilizar en múltiples consultas y proyectos. 

Vayamos a la consola y veamos cómo crear, usar y compartir una UDF persistente. 

Comience en el Editor de consultas. Aquí definirá su UDF usando SQL, siguiendo una sintaxis específica. La FUNCIÓN CREAR O REEMPLAZAR indica que le gustaría crear una UDF persistente. A esto le sigue el ID de su proyecto y el nombre del conjunto de datos donde desea que viva la UDF. El siguiente es el nombre de la función en sí. En este caso, cleanse_string. A continuación, entre paréntesis, se incluye una lista separada por comas de todos los parámetros de entrada con sus tipos de datos que requiere su UDF. Para cleanse_string, solo tiene una entrada, que es una string de texto. En la línea siguiente, la cláusula de devolución especifica el tipo de datos que devolverá la función. Ahora es el momento de proporcionar una expresión SQL que defina cómo procesar la entrada. Esta expresión utiliza las funciones SQL TRIM para recortar los espacios en blanco y LOWER para convertir el texto a minúsculas. Luego usa una expresión regular para eliminar cualquier símbolo. mano izquierda

Ahora está listo para hacer clic en Ejecutar para crear la UDF persistente. Verá un relleno debajo de su conjunto de datos en el navegador de la izquierda. Seleccionar la UDF le permite verla, editarla o eliminarla. En el futuro, puede usar esta UDF como lo haría con cualquier otra función SQL, directamente dentro de sus declaraciones de consulta. Deberá hacer referencia a él con su nombre completo, incluido el proyecto y el conjunto de datos en el que reside. De hecho, cualquier usuario con el permiso adecuado para el conjunto de datos que contiene el UDF, como el rol de visor de datos de BigQuery, también puede hacer referencia a él en sus consultas. Al compartir conjuntos de datos, tiene el potencial de crear una biblioteca de UDF en toda la organización, lo que a su vez puede garantizar que la lógica comercial se aplique de manera uniforme y eficiente en toda su organización.