Puntos clave: Snowflake proporciona análisis básicos a través de funciones estadísticas, centrándose en métricas agregadas como la covarianza y los promedios. Este tutorial ilustra el utiliza de estas funciones con datos meteorológicos, utilizando consultas SQL y funciones de ventana para un análisis eficaz.
- Snowflake ofrece funciones estadísticas esenciales pero carece de capacidades avanzadas de aprendizaje automático.
- Ejemplos clave incluyen cálculos de covarianza y estadísticas básicas como promedios y rangos.
Snowflake no hace aprendizaje automático. Solo tiene regresión lineal simple y funciones estadísticas básicas. (Si te quieres hacer aprendizaje automático con Snowflake, te necesitas poner los datos en Spark u otro producto de terceros.)
Sin embargo, te puede hacer análisis en Snowflake, armado con algunos conocimientos de matemáticas, funciones agregadas y funciones de ventanas. La analítica básica están todo lo que te necesario en la mayoría de las situaciones, y están el primer paso hacia un análisis más elaborado.
En este tutorial, te mostramos cómo utilizar funciones estadísticas de Snowflake con algunos ejemplos. Vamos a demostrar estas funciones:
Primero, vamos a preparar nuestro trabajo.
Datos de muestra
te necesito algunos datos para trabajar con este ejemplo. Descarga 1.000 registros meteorológicos por hora desde aquí.
(Compramos 20 años de datos meteorológicos para Pafos, Chipre, a OpenWeather. Esto es solo un pequeño subconjunto de 1.000 registros, convertidos a formato CSV. Hemos trabajado con estos datos en otros tutoriales, incluyendo Loading CSV Files from S3 to Snowflake.)
Sube estos datos a Amazon S3 así:
Covarianza
Desviación estándar media, máxima, mínima
Correlación
Rango
aws s3 cp paphosWeather.csv s3://gluebmcwalkerrowe/paphosWeather.csv
Crea tabla en Snowflake
Desafortunadamente, Snowflake no lee el registro de cabecera ni Crea la tabla por ti. (Presumiblemente están porque preferirían que te Define los tipos de datos de columna, la precisión numérica y el tamaño tú mismos.)
create table weather(
dt integer,
temp decimal(6,2),
temp_min decimal(6,2),
temp_max decimal(6,2),
pressure int,
humidity int,
speed decimal(6,2),
deg int,
main varchar(50),
description varchar(50))
Copiar archivo de datos al área de etapa Copo de Nieve.
Luego Crea un área de escenario Copo de Nieve así.
(No importa cuáles son tus credenciales de Snowflake: pon tus credenciales de Amazon AWS. De hecho, no están dónde consultar tus credenciales de Snowflake porque son iguales a tus credenciales de Amazon IAM, incluso si te son ejecutándolo en una instancia de Snowflake, es decir, te no instala Snowflake localmente en tus servidores EC2.)
create or replace stage paphosweather
url='s3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxx')
Ahora copia los datos en la tabla te creada arriba.
copy into weather
from 's3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxxx')
file_format = (type = csv field_delimiter = ',' skip_header = 1);
La covarianza c de las variables x e y están > 0 cuando un aumento en x resulta en un aumento de y. Es una medida de cómo son relacionan dos variables. Mostremos cómo hacer esto en Snowflake.
Cada una de las consultas a continuación envuelve una consulta interna en una consulta externa. Esto es necesario porque:
- La consulta interna realiza algunos cálculos creando un conjunto más pequeño que contiene el resultado de esos cálculos.
- La consulta externa se ejecuta a continuación. Utiliza las columnas que le pasan desde la consulta interna para Haz que el proceso de consulta sea un proceso de dos pasos. En otras palabras, en el Paso 1 Crea un conjunto A. En el Paso 2, producimos otro conjunto, llamándolo B, o un escalar, que significa un solo número.
Los datos que tenemos están datos meteorológicos horarios de un lugar: Pafos, Chipre. Los meteorólogos dicen que la caída de la presión barométrica provoca un aumento de la velocidad del aire. Así que medimos eso calculando la covarianza entre la disminución de la presión atmosférica y el aumento de la velocidad del viento.
(Recuerda que ejecutamos el tutorial de abajo usando 117.000 registros meteorológicos horarios de OpenWeather para una sola ciudad, Pafos, Chipre, durante 20 años. te puedes descargar 1.000 registros en 41 días desde aquí. O comprar datos de tu ubicación para investigar las condiciones históricas allí.)
Calculamos:
- El cambio en la presión del aire usando la función lag() de ventanas para observar la presión en dos momentos del tiempo. Una función ventana ejecuta un cálculo sobre filas adyacentes en los resultados de la consulta. Por eso, utilizamos lag (presión,8) para calcular el cambio en la presión del aire durante las 8 horas anteriores, es decir, 8 filas detrás de la fila actual. Cada registro en la base de datos está de 1 hora.
- Luego calculamos el cambio en la velocidad del viento durante las mismas 8 horas usando también lag().
Utiliza una restadura simple para calcular el cambio. Ordenamos los datos por la columna de fecha dt porque son trabajamos con el tiempo, así que las fechas deben estar en orden.
select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt) as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc
Ahora envolvemos la función interna dentro de la función exterior. Nótese que:
- Como la función interna dio nombre a los cálculos cambio de presión y cambio de viento , simplemente escribimos covar_pop(cambio de presión, cambio de viento).
- Añadimos la columna principal, que está nublado, lluvioso, tormenta, etc., para mostrar la covarianza según cada condición meteorológica.
select main, covar_pop(pressurechange, windchange) as covariance from
(select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt)as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc)
group by main
Aquí Ve que una disminución de la presión barométrica resulta en un aumento de la velocidad del viento, como esperaríamos. Ese efecto es más fuerte en tormentas eléctricas.
| PRINCIPAL | COVARIANZA |
|---|---|
| Nubes' | 2.682560523 |
| Claro' | 2.578284619 |
| Tormenta eléctrica | 3.578449916 |
| Lluvia | 3.328280913 |
| Dust' | 1.697657949 |
| Squall' | 2.921487603 |
| Mist' | 2.513627372 |
| Niebla' | 2.9256 |
| 'Niebla' | 3.565528153 |
| Tornado' | -1.425 |
La media, máxima, mínima y desviación estándar son funciones agregadas. Eso significa que esperan un grupo por cláusula. Estas son las estadísticas más básicas. Te te dan una idea de cómo la media y la variación media en algunas métricas a lo largo del tiempo. Ten en cuenta que:
- to_timestamp() convierte la hora de la época (que están segundos desde el 01/01/1970) en aa-mm-dd hh:mm:ss.
- Nosotros utiliza date_part() para sacar el mes y el año.
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
Aquí son los resultados:
| PROMEDIO | ETS | MAXTEMP | MINTEMP | AÑO | MES |
|---|---|---|---|---|---|
| 53.4 | 5.57 | 64.08 | 37.42 | 2017 | 1 |
| 54.4 | 6.37 | 72.84 | 40.44 | 2017 | 2 |
| 59.17 | 5.1 | 70.84 | 45.91 | 2017 | 3 |
| 63.79 | 5.36 | 76.78 | 51.67 | 2017 | 4 |
| 79.28 | 0.1 | 79.39 | 79.21 | 2017 | 8 |
| 78.35 | 4.9 | 88.5 | 67.21 | 2017 | 9 |
| 71.86 | 4.86 | 82.2 | 62.13 | 2017 | 10 |
| 64.08 | 5.29 | 78.31 | 50.61 | 2017 | 11 |
| 59.55 | 5.69 | 69.87 | 45.84 | 2017 | 12 |
| 56.5 | 4.99 | 66.42 | 44.31 | 2018 | 1 |
| 58.81 | 4.47 | 68.04 | 47.88 | 2018 | 2 |
| 61.63 | 5.27 | 77.61 | 50.56 | 2018 | 3 |
| 65.91 | 6.07 | 78.75 | 51.94 | 2018 | 4 |
| 75.88 | 1.43 | 77.54 | 75.04 | 2018 | 8 |
| 79.21 | 5.01 | 88.81 | 66.67 | 2018 | 9 |
| 73.28 | 5.97 | 85.15 | 58.28 | 2018 | 10 |
Podemos Obtén una comparación año por año ordenando primero los resultados por mes y luego por año:
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(month,to_timestamp(dt)) ,date_part(year, to_timestamp(dt))
Obviamente, los meses se correlacionaban con la temperatura, ya que hace más calor en verano y más frío en invierno. ¿Qué tan fuerte están esa correlación? te pensaría que estaría cerca del 100%. Aquí, en el clima templado de Chipre, está cerca del 70%.
Esto da lugar a una estadística escalar ya que devuelve un valor, corr (mes, promedio) en lugar de filas.
select corr(month, average) from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
Resulta en este valor escalar. Así que son correlacionados positivamente, pero menos de lo que imaginamos.
CORR(MONTH, AVERAGE)
0.6127760999
Ahora calculemos los meses medios más calurosos desde 2017. Podríamos hacer eso con una función SQL normal, pero si utilizamos la función rank() windows podemos numerar cada fila del conjunto resultante. Ese número están el rango.
Esto también es una función de dos pasos, porque necesitamos convertir los registros meteorológicos horarios a mensuales para no tener demasiadas líneas:
- Calcula la temperatura media de cada mes.
- Selecciona las métricas que queremos que se ordenen. Esto se está dando el promedio de columna en la instrucción (orden por promedio desc [descendente]).
select year, month, average, rank() over (order by average desc) as hottest from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
Aquí son los resultados (en Fahrenheit).
| AÑO | MES | PROMEDIO | MÁS CALIENTE |
|---|---|---|---|
| 2020 | 9 | 81.73 | 1 |
| 2019 | 8 | 79.36 | 2 |
| 2017 | 8 | 79.28 | 3 |
| 2018 | 9 | 79.21 | 4 |
| 2017 | 9 | 78.35 | 5 |
| 2019 | 9 | 77.74 | 6 |
| 2020 | 8 | 76.48 | 7 |
| 2018 | 8 | 75.88 | 8 |
| 2019 | 10 | 73.79 | 9 |
| 2018 | 10 | 73.28 | 10 |
| 2017 | 10 | 71.86 | 11 |
| 2019 | 11 | 68.13 | 12 |
| 2018 | 11 | 66.25 | 13 |
| 2018 | 4 | 65.91 | 14 |
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.