Servidor disponible solo los jueves de 10:30 a 12:30 y los viernes de 10:00 a 13:00 (hora de Madrid); fuera de ese horario está apagado. Fuera del horario, usa la documentación local del material que te descargaste (el README.md de cada carpeta).
G214 · CUNEF
Inicio / tema4 / 04.04-airemadrid

Tema 4 · 4 · Ejercicio adicional A: calidad del aire de Madrid

Presentación del Tema 4: «Ejercicio A · Un dataset nuevo en el lago, de principio a fin» y «Ejercicio A · paso a paso». Duración: 60 a 120 minutos. Coste: céntimos (decenas de MB). Es un ejercicio opcional de ampliación.

Qué vas a hacer

Vas a bajar tres años (2022, 2023 y 2024) de datos horarios de calidad del aire del portal de datos abiertos del Ayuntamiento de Madrid, subirlos a tu Data Lake, describirlos con una tabla externa de 56 columnas y convertirlos a Parquet en formato largo (una fila por medición) para responder tres preguntas sobre el dióxido de nitrógeno (NO2). Las diapositivas dan el enunciado y una guía; este README da lo que faltaba: de dónde bajar los datos, cómo subirlos, qué fichero SQL te ayuda y qué debes ver.

Qué se crea en AWS (dentro del stack de 04.01): los CSV en s3://<tu-bucket>/raw/aire/anio=YYYY/ y, en la base demo_datalake_db, las tablas aire_raw (externa, sobre los CSV) y aire_curated (Parquet en formato largo, particionada por anio, con ficheros en athena-results/tables/).

Qué contiene esta carpeta

Fichero Para qué sirve Paso en que se usa
3-athena-aire.sql SQL de apoyo, la solución probada: comandos de descarga y subida (en comentarios), tabla raw de 56 columnas, CTAS de ancho a largo, las tres preguntas y la comparación de Data scanned Pasos 3 a 7
README.md Esta guía -

Intenta resolver el ejercicio primero por tu cuenta y usa 3-athena-aire.sql para comprobar tu SQL o si te atascas. Cómo usarlo: ábrelo con cat o nano en CloudShell (o desde tu PC), copia las sentencias en el editor de Athena sustituyendo BUCKET_AQUI por el valor de tu bucket (echo "$BUCKET") y ejecútalas de una en una, con el workgroup demo-athena-workgroup y la base demo_datalake_db. Los valores esperados van en comentarios encima de cada sentencia; si copias varias sentencias a la vez o añades comentarios propios, no dejes un comentario detrás del ; en la misma línea: la API de Athena lo toma por una segunda sentencia y da MALFORMED_QUERY (Only one sql statement is allowed).

Antes de empezar

Cómo llevar los ficheros a CloudShell

  1. En tu PC, comprime esta carpeta (04.04-airemadrid) en un zip y no la descomprimas.
  2. En CloudShell (región eu-north-1): Actions > Upload file > 04.04-airemadrid.zip.
  3. Descomprime y entra:
unzip -o 04.04-airemadrid.zip && cd 04.04-airemadrid && chmod -R u+rwX . && chmod +x *.sh 2>/dev/null; ls

Si ya subiste g214-materiales.zip (README principal), no necesitas este zip: entra con cd ~/g214-materiales/tema4/04.04-airemadrid y, si algún fichero da Permission denied, ejecuta allí chmod -R u+rwX . && chmod +x *.sh.

(Esta carpeta no tiene scripts .sh: el chmod no hace nada y el 2>/dev/null oculta su aviso. Los datos se descargan con curl en CloudShell y el SQL se pega en Athena.)

Paso a paso

Paso 1 · Averigua tu bucket

BUCKET=$(aws cloudformation describe-stacks --stack-name g214-datalake-demo --region eu-north-1 \
  --query "Stacks[0].Outputs[?OutputKey=='S3Bucket'].OutputValue" --output text)
echo "$BUCKET"

Paso 2 · Descargar y subir los datos

Datos: portal datos.madrid.es, conjunto «Calidad del aire. Datos horarios desde 2001» (https://datos.madrid.es/dataset/201200-0-calidad-aire-horario). Hay un zip por año. Enlaces directos de los tres zips (comprobados el 2026-10-03: responden 200; los números del enlace no siguen el orden de los años; también están en la cabecera de 3-athena-aire.sql y, en forma de plantilla,):

Si alguno falla, mira la lista de https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/downloads.

El zip trae tres copias de cada mes: una carpeta Anio24/ con 12 ficheros .csv, 12 .txt y 12 .xml. No existe ningún aire_2024.csv. Sube solo los .csv: si mezclas formatos en la misma carpeta, la tabla lee basura.

curl -L -o aire2024.zip "<enlace de 2024, de la lista de arriba>"
unzip -o aire2024.zip
head -3 Anio24/ene_mo24.csv
aws s3 cp Anio24/ "s3://$BUCKET/raw/aire/anio=2024/" --recursive \
  --exclude "*" --include "*.csv" --region eu-north-1
aws s3 ls "s3://$BUCKET/raw/aire/" --recursive --region eu-north-1

Repite con 2023 y 2022 (carpetas Anio23/ y Anio22/, destinos anio=2023/ y anio=2022/). Qué verás: 12 objetos por año (unos 21 MiB los tres años).

Formato del CSV: separador ;, cabecera, decimal con punto, y formato ancho: una fila por día, estación y magnitud, con 24 pares de columnas H01/V01 ... H24/V24 (valor y validez V/N). Son 56 columnas. Magnitud 8 = dióxido de nitrógeno (NO2); su valor límite horario es 200 µg/m3.

Paso 3 · Crear la tabla raw

Declara las 56 columnas como STRING, PARTITIONED BY (anio INT), OpenCSVSerde con separatorChar = ';' y skip.header.line.count = 1. El enunciado dice «clasificador CSV»: aquí el clasificador es ese SerDe con el separador correcto. No olvides MSCK REPAIR TABLE. La sentencia completa está en el apartado 1 de 3-athena-aire.sql (con BUCKET_AQUI sustituido por tu bucket).

Qué verás: SHOW PARTITIONS demo_datalake_db.aire_raw; lista anio=2022, anio=2023 y anio=2024; y las filas de aire_raw por año son 47.223 / 47.242 / 46.737.

Paso 4 · CTAS a Parquet, de ancho a largo

Como el fichero es ancho, hay que pasarlo a formato largo (una fila por medición) con CROSS JOIN UNNEST(...) WITH ORDINALITY sobre dos arrays (valores y validez) y filtrar valido = 'V'; los números se convierten con TRY_CAST(REPLACE(valor, ',', '.') AS DOUBLE). La CTAS «larga» de la presentación (estacion, magnitud, valor, anio) solo sirve si el fichero ya es largo; con los datos reales falla con COLUMN_NOT_FOUND: Column 'valor' cannot be resolved. La versión que funciona está en el apartado 2 de 3-athena-aire.sql.

Qué verás: las filas de aire_curated por año son 1.107.890 / 1.110.525 / 1.102.638 (2022 / 2023 / 2024).

Paso 5 · Las tres preguntas

Las responde el apartado 3 de 3-athena-aire.sql. Qué debes ver: media de NO2 en 2022 de 28,21; las cinco estaciones con peor media de NO2 en 2024 son 56 (30,73), 17 (28,90), 27 (28,03), 40 (28,00) y 8 (27,74); y las horas por encima de 200 µg/m3 son 1 en 2022 y ninguna en 2023 ni 2024.

Paso 6 · Data scanned: raw frente a curated

Ejecuta la misma consulta sobre aire_raw y sobre aire_curated (apartado 4 del SQL) y anota Data scanned: unos 22 MB sobre la raw (22.052.831 B) frente a unos 3 MB sobre la curated (2,99 a 3,00 millones de bytes según la ejecución). Las dos consultas no calculan lo mismo: se comparan por lo que leen. Cómo leer ese valor: 04.02.

Errores frecuentes

Qué ves Causa Solución
La tabla devuelve filas con basura Subiste también los .txt o .xml a raw/aire/ Borra lo que no sea .csv de raw/aire/ y sube solo los .csv
curl descarga un fichero pequeño o falla El enlace del año ha cambiado Mira https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/downloads
COLUMN_NOT_FOUND: Column 'valor' cannot be resolved Usaste la CTAS «larga» de la presentación sobre el CSV ancho Usa CROSS JOIN UNNEST ... WITH ORDINALITY (paso 4)
aire_raw devuelve 0 filas Faltan particiones por registrar MSCK REPAIR TABLE demo_datalake_db.aire_raw; y SHOW PARTITIONS
MALFORMED_QUERY / Only one sql statement is allowed Hay un comentario -- ... detrás de un ; en la misma línea Pon el comentario en la línea de encima o bórralo; ejecuta de una en una
Las rutas s3://BUCKET_AQUI/... fallan No sustituiste BUCKET_AQUI Pon el valor de echo "$BUCKET"

Limpieza

Solo lo de esta actividad, en este orden (el stack NO se borra aquí: se borra al final del Tema 4 desde 04.01, y debe ser lo último). Si vas a borrar el stack, todo esto se va con él y puedes saltarte esta sección.

  1. En Athena: DROP TABLE IF EXISTS demo_datalake_db.aire_curated; y DROP TABLE IF EXISTS demo_datalake_db.aire_raw; (solo borran la descripción: los ficheros siguen en S3).
  2. En CloudShell: aws s3 rm "s3://$BUCKET/raw/aire/" --recursive --region eu-north-1. Los ficheros de aire_curated están en s3://$BUCKET/athena-results/tables/<id>/ (para encontrar el id: SHOW CREATE TABLE demo_datalake_db.aire_curated;); se van con el stack.
  3. Borra lo descargado en CloudShell: rm -rf aire20*.zip Anio2*.

Cómo sabes que has terminado

Anterior: 04.03 NYC Taxi · Siguiente: 04.05 Euribor e IPC