Presentación de funciones definidas por el usuario de SQL

Una función definida por el usuario (UDF) es un medio para que un usuario extienda las capacidades nativas de Apache Spark™. SQL en Databricks ha admitido UDF externas escritas en los lenguajes de programación Scala, Java, Python y R desde la versión 1.3.0. Si bien las UDF externas son muy potentes, también conllevan algunas advertencias:

• Seguridad. Una UDF escrita en un lenguaje externo puede ejecutar código peligroso o incluso malicioso. Esto requiere un control estricto sobre quién puede crear UDF.
• Rendimiento. Las UDF son cajas negras para el Optimizador Catalyst. Dado que Catalyst no conoce el funcionamiento interno de una UDF, no puede hacer nada para mejorar el rendimiento de la UDF en el contexto de una consulta SQL.
• Usabilidad de SQL. Para un usuario de SQL, puede ser engorroso escribir UDF en un lenguaje anfitrión y luego registrarlas en Spark. Además, hay un conjunto de extensiones que muchos usuarios querrán hacer a SQL que son bastante simples, donde desarrollar una UDF externa es excesivo.

Para hacer frente a las limitaciones anteriores, estamos encantados de presentar una nueva forma de UDF: UDF de SQL. Disponible en DBR 9.1 LTS, la UDF de SQL se define completamente con el poder expresivo de SQL y también es completamente transparente para el Compilador de SQL.

---

Consulte el ebook Por qué el Data Lakehouse es su próximo Data Warehouse para descubrir el funcionamiento interno de la Plataforma Databricks Lakehouse.

Beneficios de usar UDF de SQL

Las UDF de SQL son extensiones simples pero potentes de SQL en Databricks. Como funciones, proporcionan una capa de abstracción para simplificar la construcción de consultas, haciendo que las consultas SQL sean más legibles y modulares. A diferencia de las UDF que se escriben en un lenguaje que no es SQL, las UDF de SQL son más ligeras para que los usuarios de SQL las creen. Los cuerpos de las funciones SQL son transparentes para el optimizador de consultas, lo que las hace más eficientes que las UDF externas. Las UDF de SQL se pueden crear como funciones temporales o permanentes, reutilizarse en múltiples consultas, sesiones y usuarios, y controlarse mediante el Lenguaje de Control de Acceso (ACL).

UDF de SQL como constantes

Empecemos con la función más simple imaginable: una constante. Todos sabemos que no debemos usar literales en nuestro código porque perjudica la legibilidad y, quién sabe, tal vez la constante no siga siendo constante después de todo. Así que queremos poder cambiarla en un solo lugar:

Si está familiarizado con las UDF externas, puede ver que hay algunas diferencias que destacan:

1. Una UDF de SQL debe definir su lista de parámetros, incluso si está vacía. Una constante no toma parámetros.
2. La función también declara el tipo de datos que devolverá. En este caso, es STRING.
3. La implementación de la función es parte de la definición de la función.
4. Especifica LANGUAGE SQL para indicar que es una UDF de SQL. Pero en realidad, eso no es necesario. La cláusula RETURN es suficiente para indicar que hemos decidido hacer esto opcional.

Más allá de estas diferencias, hay muchas otras cosas que son iguales que las UDF externas:

• Puede reemplazar una función. Más sobre eso más adelante.
• Puede agregar un comentario que describa la función, como se muestra arriba.
• Incluso puede crear una función temporal que puede usar solo dentro de la sesión actual.

Usemos la función:

No sorprende que esto funcione. Pero, ¿qué está sucediendo internamente?

¡Esto es genial! El compilador de SQL reemplazó la invocación de la función con la constante misma.
Eso significa que, al menos, esta UDF de SQL no tiene ningún costo de rendimiento.

Ahora, veamos otro patrón de uso común.

UDF de SQL que encapsulan expresiones

Imagine que no le gusta la nomenclatura de algunas funciones integradas. Quizás esté migrando muchas consultas de otro producto, que tiene diferentes nombres y comportamientos de funciones. O quizás simplemente no soporta copiar y pegar algunas expresiones largas una y otra vez en sus consultas SQL. Entonces, quiere solucionar eso.

Con las UDF de SQL, podemos simplemente crear una nueva función con el nombre que nos guste:

Echemos un vistazo a la nueva sintaxis utilizada aquí:

• Esta función toma un argumento, y el parámetro se define por un nombre, un tipo y un comentario opcional.
• La cláusula CONTAINS SQL es opcional, pero nos dice que la función no lee ni modifica ningún dato en una tabla. Es la configuración predeterminada, por lo que normalmente no la especificaría.
• DETERMINISTIC también es opcional y nos dice que la función siempre devolverá el mismo conjunto de resultados dados los mismos argumentos. La cláusula es solo para documentación en este momento. Pero en algún momento en el futuro, puede usarse para bloquear funciones no deterministas en ciertos contextos.
• En la cláusula RETURN, el parámetro se ha referenciado por nombre. En escenarios más complejos a continuación, verá que el parámetro se puede desambiguar con el nombre de la función. Naturalmente, puede usar expresiones arbitrariamente complejas como cuerpo de la función.

No solo funciona...

...sino que funciona bien:

Podemos ver que el plan físico muestra una aplicación directa de las funciones lpad, hex, least y greatest. Este es el mismo plan que se obtiene al invocar la serie de funciones directamente.

También puede componer funciones SQL a partir de funciones SQL:

UDF de SQL que leen de tablas

Otro uso común de las UDF de SQL es codificar búsquedas. Una búsqueda simple puede ser decodificar códigos de color RGB en nombres de color en inglés:

Está bien, pero hay muchas más que dos colores en este mundo. Y queremos esta traducción en ambos sentidos, por lo que realmente deberían estar en una tabla de búsqueda:

Aquí se aplican varios conceptos nuevos:

• Puede REEMPLAZAR una UDF de SQL. Para poder hacerlo, la nueva función debe coincidir con la firma de la función anterior. La firma de una función se define como el número de sus parámetros y sus tipos.
• Esta función busca información en una tabla, por lo que puede documentarlo opcionalmente usando READS SQL DATA. Si no indica nada, el Compilador de SQL derivará el valor correcto, pero no debe mentir e indicar CONTAINS SQL.
• SQL SECURITY DEFINER es otra cláusula opcional, que establece que la consulta que accede a la tabla de colores utilizará la autorización del propietario de la función. Por lo tanto, la función podría ser ejecutada por el público sin comprometer la seguridad de la tabla.
• Así como la función opera bajo la autorización de su propietario, siempre se analizará utilizando la base de datos actual en el momento de la creación.
• `rgb` es el nombre de la columna en números. Al calificar el parámetro como `from_rgb`.`rgb`, aclara que se refiere a la referencia del parámetro y no a la columna.

¿Cómo se ve el plan físico ahora? Es fácil ver que usar una UDF externa, que a su vez realiza una consulta que resultaría en un join de bucle anidado, es una forma terrible de quemar recursos valiosos.

En este caso, Catalyst eligió un broadcast hash join en lugar de un nested loop join. Puede hacerlo porque entiende el contenido de la SQL UDF.

Hasta ahora, todos los ejemplos discutidos usaron funciones con valores escalares, aquellas que devuelven un solo valor. Ese resultado puede ser de cualquier tipo, incluso combinaciones complejas de structs, arrays y maps. También hay otro tipo de UDF a discutir: la UDF con valores de tabla.

SQL Table UDF

¡Imagina si las vistas aceptaran argumentos! Podrías encapsular predicados complejos incluso si dependen de valores proporcionados por el usuario. Una SQL Table UDF es exactamente eso: una vista con otro nombre, excepto que tiene parámetros.

Asumamos que el mapeo de colores anterior no es único. Como mínimo, podemos afirmar que los nombres de los colores difieren entre idiomas.

Por lo tanto, la función `from_rgb` necesita ser modificada para devolver un array de nombres o una relación.

Como puedes ver, la única diferencia en comparación con una función escalar es una cláusula RETURNS más compleja. A diferencia de las vistas, las SQL UDFs exigen una declaración de la firma de la relación devuelta:

• TABLE especifica que la función devuelve una relación.
• La cláusula TABLE debe incluir un nombre para cada columna de retorno y el tipo de datos de la columna.
• Opcionalmente, puedes especificar un comentario para cualquier columna de retorno.

Las funciones de tabla definidas por el usuario son nuevas en DBR. Echemos un vistazo a cómo invocarlas.

En su forma más simple, una función de tabla se invoca de la misma manera y en los mismos lugares donde se hace referencia a una vista. La única diferencia son las llaves obligatorias, que incluyen los argumentos de la función. Esta función se invoca con argumentos literales, pero los argumentos pueden ser cualquier expresión, incluso subconsultas escalares.

Sin embargo, lo más potente es el uso de SQL table UDF en un join, típicamente un correlated cross join:

Aquí los argumentos se refieren (correlacionan) a una relación anterior (lateral) en la cláusula FROM. La nueva palabra clave LATERAL le da a Catalyst permiso para resolver estas columnas. También ten en cuenta que puedes referirte al resultado de la función de tabla nombrando las columnas como se definieron en la firma del resultado y opcionalmente calificadas por el nombre de la función.

Administración

Naturalmente, las SQL UDFs son totalmente compatibles con las sentencias GRANT, REVOKE, SHOW, DESCRIBE y DROP existentes.

La sentencia que vale la pena destacar con más detalle es DESCRIBE.

El describe básico devuelve lo que podrías esperar, pero el DESCRIBE extendido añade significativamente más detalles:

Perspectivas

Lo que hemos descrito representa la funcionalidad inicial para SQL UDF. Las extensiones futuras que estamos considerando incluyen soporte para:

• SQL PATH, para que puedas crear una biblioteca de funciones en una base de datos y suscribirte a ellas desde otra, tal como lo harías en tu sistema de archivos.
• Sobrecarga de UDFs.
• UDFs con valores predeterminados para los parámetros.

Las SQL UDFs son un gran paso adelante en la usabilidad de SQL y pueden usarse de muchas maneras diferentes, como se describe en este blog. Te animamos a pensar en formas aún más creativas de aprovechar las SQL UDFs, ya sea en Databricks SQL o usando Photon para trabajos de Data Engineering. Prueba el notebook aquí y consulta la documentación para obtener más información.

(Esta entrada del blog ha sido traducida utilizando herramientas basadas en inteligencia artificial) Publicación original