Puntos clave: Las Funciones Definidas por el Usuario (UDFs) en Snowflake Permite a los usuarios Crea funciones SQL y JavaScript personalizadas para un procesamiento de datos mejorado. El tutorial trata sobre la creación de funciones escalares y de tabla para diversas aplicaciones como el análisis de datos meteorológicos.
- Las UDF pueden devolver valores simples (escalares) o múltiples (de tabla).
- JavaScript está compatible con cálculos complejos dentro de UDFs.
En este tutorial, te mostramos cómo Crea funciones definidas por el usuario (UDF) en Snowflake.
En Copo de Nieve, te puede Crea:
- Funciones en lenguajes SQL y JavaScript
- Funciones que devuelven un único valor (escalar)
- Funciones que devuelven múltiples valores (tabla)
Crea datos
Si te quieres seguir los tutoriales a continuación, utiliza las instrucciones de este tutorial sobre funciones estadísticas para cargar algunos datos en Snowflake. Los datos están 41 días de datos meteorológicos horarios de Pafos, Chipre.
Función SQL UDF de Snowflake
Comienza con un ejemplo de SQL.
El código siguiente toma las condiciones meteorológicas de entrada, descritas en la columna principal de la tabla, y las convierte en un entero. Esto resuelve un problema común del aprendizaje automático: convertir datos categóricos en enteros.
Fíjate que la función tiene parámetros (dt varchar(20)) y un valor de retorno (int). El resto está simplemente una sentencia SQL select.
El código siguiente utiliza las sentencias iff() y regex() para Ve si la palabra lluvia, nube, etc., se está en la columna principal. Funciona sumando los números del 1 al 9. Como solo una de estas sentencias if será verdadera, entonces la suma será uno de los valores del 1 al 9, dando así las condiciones meteorológicas.
create or replace function weathercategorical (dt varchar(20) )
returns int
as $$select (iff(main regexp '.*Clear.*',1,0) +
iff(main regexp '.*Clouds.*',2,0) +
iff(main regexp '.*Rain.*',3,0) +
iff(main regexp '.*Thunderstorm.*', 4,0) +
iff(main regexp '.*Mist.*', 5, 0) +
iff(main regexp '.*Fog.*', 6, 0) +
iff(main regexp '.*Squall.*',7,0) +
iff(main regexp '.*Tornado.*', 8, 0) +
iff(main regexp '.*Haze.*', 9, 0))
from weather as w where w.dt = dt$$
La fecha y hora están en formato de tiempo de época. La instrucción SQL a continuación llama a la función weathercategórica para la fecha 1 de enero de 2000, devolviendo el valor escalar 1, que significa tiempo despejado.
sselect weathercategorical (946684800) from weather where dt = 946684800
Función de tabla copo de nieve
Aquí mostramos cómo devolver más de un valor, que Snowflake llama tabla.
Crea estas dos tablas:
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) ,
ordernumber varchar(100) PRIMARY KEY,
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount float,
quantity int,
productnumber varchar(50)
);
Luego copia y pega esos datos.
Escalar frente a función de tabla
Ahora Crea una función para buscar el nombre del cliente y el correo electrónico que se obtiene como un registro de la tabla de pedidos. Los pedidos no contienen información del cliente, así que es como hacer una unión. Pero como es una función, es mucho menos extensa y más cómoda que crear una unión siempre te necesitar información del cliente con el pedido.
create or replace function getcustomer (customernumber number )
returns table (customername varchar, email varchar)
as 'select customername, email from customers
where customers.customernumber = customernumber';
Dado el número de cliente de la tabla de pedidos, esta declaración recibe:
- El nombre del cliente
- Número de pedido
- Correo electrónico
select getcustomer (948 ), ordernumber from orders where customernumber = 948;
JavaScript UDFs
te puede utiliza JavaScript en una función definida por el usuario. Solo pon lenguaje JavaScript.
Calculemos n factorial (¡n!) ya que Copo de Nieve no tiene esa función matemática. ¡n factorial n! están n * (n-1) * (n-2) * .. ** (n - (n + 1))). Por ejemplo: ¡3! =3*2*1=6.
Observa abajo que utilizamos variante como tipo de dato, ya que JavaScript no tiene tipos enteros.
CREATE OR REPLACE FUNCTION factorial(n variant)
RETURNS variant
LANGUAGE JAVASCRIPT
AS '
var f=n;
for (i=n-1; i>0; i--) {
f=f*i
}
return f';
Lo ejecuto y calcula el valor 6.
select factorial(3)
Ten en cuenta que 33 están el mayor número que puede manejar esa función. ¡33! = 8683317618811886495518194401280000000
Recursos adicionales
Para más tutoriales como este, explora estos recursos:
Guía de copos de nieve
- Snowflake 101: Introducción a la Nube de Datos Snowflake
- Cómo importar datos de Amazon S3 a Snowflake
- Cómo consultar datos JSON en Snowflake
- Cargando archivos CSV de S3 a Snowflake
- Snowflake: Uso de Analítica y Funciones Estadísticas
- Funciones de la ventana copo de nieve: Partición por y orden por
- Función de retraso de copo de nieve y medias móviles
- Funciones agregadas SQL de Snowflake y uniones de tablas
- Funciones definidas por el usuario (UDFs) en Snowflake
- Creación y uso de arroyos de copos de nieve
- Uso de procedimientos almacenados en Snowflake
- Copo de nieve vs Redshift: ¿Cuál es la diferencia y cómo elegir?
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.