Puntos clave: Escribir sentencias SQL en Amazon Redshift explica cómo utilizar el editor de consultas para el análisis de datos, centrándose en comandos SQL básicos como la agregación y la exportación de resultados. También analiza las funcionalidades de Amazon Redshift como almacén de datos en la nube.
- Utiliza el editor de consultas para ejecutar comandos SQL.
- Entiende las capacidades de Amazon Redshift para gestionar grandes conjuntos de datos.
En este tutorial, mostramos cómo escribir sentencias SQL para Amazon Redshift. Dado que este tema es amplio y complejo, Comienza por lo básico.
Este tutorial te mostrará cómo:
- Utiliza el editor de consultas
- Agrega filas usando grupo por
- Convertir fechas en año y mes
- Exporta los resultados a un archivo csv
¿Qué es Amazon Redshift?
Amazon Redshift está un almacén de datos en la nube de Amazon Web Services que está basado en SQL. Está diseñado para gestionar grandes conjuntos de datos y consultas complejas. Está ideal para almacenamiento de datos, inteligencia empresarial, análisis de big data, aprendizaje automático y flujos de trabajo de Extract-Load-Transform.
Los componentes de Redshift incluyen los siguientes:
- Los clústeres son nodos informáticos organizados alrededor de un nodo líder para gestionar conexiones y coordinar consultas.
- Los slices son divisiones de nodos que asignan recursos, memoria y espacio en disco para un procesamiento eficiente y masivamente paralelo.
- Las metodologías de distribución de datos asignan los datos a los nodos de forma equitativa, según un valor clave o entre cada nodo.
- El almacenamiento columnar de datos se está realizando en pilas verticales para Reduce la entrada/salida (E/S) y para un almacenamiento más eficiente.
- El motor de ejecución soporta consultas SQL y planifica la mejor manera de Obtén y utilizar datos.
- Spectrum es un servicio de Amazon Redshift que permite consultar datos en Amazon S3 sin necesidad de incorporarlos al entorno Redshift.
- La carga integrada de datos utiliza una variedad de herramientas de Amazon y de terceros.
- La copia de seguridad y la recuperación aportan resiliencia.
- La seguridad controla la identidad y el acceso, proporcionando registro para el reconocimiento de patrones y respuestas posteriores a la explotación.
- El mantenimiento y la supervisión están intrínsecos a este servicio totalmente gestionado, aliviando a tu equipo de las cargas administrativas continuas.
Editor de consultas Redshift
Para abrir el editor de consultas, haz clic en el editor desde la pantalla de clústeres. Redshift te pedirá entonces tus credenciales para conectarte a una base de datos. Una función útil están que está una opción para generar credenciales temporales, así que no te hace falta recordar tu contraseña. Es suficiente para tener acceso a la consola Amazon AWS.
Abajo tenemos un clúster que son reanudamos después de tenerlo en pausa (para Reduce los cargos de facturación de Amazon).
te escribir la sentencia SQL aquí. Solo están permitida una sentencia a la vez, ya que Redshift solo puede mostrar un conjunto de resultados a la vez. Para escribir más de una sentencia, haz clic en la más (+) para añadir una pestaña adicional.
Cuando tú ejecutas cada consulta, tarda unos segundos en enviar el trabajo y luego ejecutarlo. Así que no es instantáneo, como te podrías esperar con otros productos.
Los resultados son muestran al final, donde te pueden exportarlos como CSV, TXT o HTML. te también pueden graficar los resultados.
Obtén esquema de tabla
Para este tutorial, utilizamos una tabla de datos meteorológicos. (Ve más sobre cómo cargar datos a Amazon Redshift desde la temporada 3.) Esto están 20 años de datos meteorológicos para Pafos, Chipre. Tiene cuatro columnas:
- dt_iso
- Temp
- temp_min
- temp_max
dt_dso están de la marca de tiempo y están la clave primaria. Una ventaja de Redshift están te puede cargar la fecha en casi cualquier formato te quieras, y Redshift lo entiende. Luego Redshift proporciona la función to_char() para imprimir cualquier parte de la fecha te quieras, como la hora, el año, el minuto, etc.
Para mirar el esquema de la tabla, consulta la tabla pg_table_def.
Aquí está el esquema.
SELECT *
FROM pg_table_def
WHERE tablename = 'paphos'
AND schemaname = 'public';
Aquí está el esquema.
schemaname,tablename,column,type,encoding,distkey,sortkey,notnull
public,paphos,dt_iso,timestamp without time zone,none,t,1,t
public,paphos,temp,real,none,f,0,f
public,paphos,temp_min,real,none,f,0,f
public,paphos,temp_max,real,none,f,0,f
Instrucciones SQL agregadas
Esta consulta calcula la temperatura media mensual para los meses de verano de mayo a septiembre. Aviso:
- to_char() extrae cualquier parte de la fecha que te quiera, como el año anual o el número de mes MM.
- Nosotros utilizamos la instrucción in() para seleccionar los meses.
- La instrucción order usa un 1. Eso significa utilizar la primera columna devuelto por la consulta. Eso es una alternativa a escribir el nombre de la columna.
- Agrupamos por año y mes porque queremos calcular el promedio [promedio()] para el mes dentro del año
- Utilizamos la función round() para redondear dos decimales. De lo contrario, el corrimiento al rojo da demasiados decimales.
- Como en otras bases de datos, la sentencia as nos permite dar un alias a la columna resultante del cálculo. Sin él, la columna no tendría un nombre descriptivo. Aquí llamamos a la temperatura media aveTemp.
select round(avg(temp),2) as aveTemp,
to_char(dt_iso,'YYYY') as year,
to_char(dt_iso,'MM') as month
from paphos where
month in ('05','06','07','08','09')
group by year, month
order by 1 desc
Aquí son los resultados. Muestra los meses más calurosos de los 20 años de datos. He cortado la pantalla para Haz breve. Por ejemplo, en los 20 años, agosto de 2010 fue el mes más caluroso.
Agrupamos por año y luego por mes, según queremos que el mes dentro del año tenga en cuenta la observación meteorológica diaria.
| avetemp | Año | mes |
| 84.11 | 2010 | 8 |
| 83.12 | 2012 | 8 |
| 83.05 | 2012 | 7 |
| 82.9 | 2015 | 8 |
| 82.39 | 2017 | 7 |
| 82.04 | 2014 | 8 |
| 81.85 | 2007 | 7 |
| 81.73 | 2020 | 9 |
| 81.72 | 2013 | 8 |
| 81.72 | 2008 | 8 |
| 81.62 | 2000 | 7 |
| 81.61 | 2009 | 8 |
| 81.49 | 2017 | 8 |
Exportamos los datos a un formato csv usando el botón a la derecha de los resultados. Luego los importamos a una hoja de cálculo para poder Ve los resultados más fácilmente y darles colores y demás.
Aquí son los años más calurosos. Eso lo Obtén eliminando el mes de la agregación.
select round(avg(temp),2) as aveTemp,
to_char(dt_iso,'YYYY') as year
from paphos
group by year
order by 1 desc
Recursos adicionales
Para más tutoriales como este, explora estos recursos:
Guía de corrimiento al rojo de Amazon
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.