# 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](../04.01-creardatalake/README.md)): 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

- Necesitas el Data Lake de [04.01 Crear el Data Lake](../04.01-creardatalake/README.md) (stack `g214-datalake-demo` creado, región `eu-north-1`). No necesitas haber hecho 04.02 ni 04.03, aunque «Cómo leer Data scanned» está explicado en [04.02](../04.02-titanic/README.md#cómo-leer-data-scanned-en-athena).
- Workgroup `demo-athena-workgroup` y base `demo_datalake_db` seleccionados en Athena; «Reuse query results» sin marcar.

## 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:

```bash
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

```bash
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,):

- 2024: `https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/resource/201200-0-calidad-aire-horario-zip/download/201200-0-calidad-aire-horario-zip.zip`
- 2023: `https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/resource/201200-3-calidad-aire-horario-zip/download/201200-3-calidad-aire-horario-zip.zip`
- 2022: `https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/resource/201200-23-calidad-aire-horario-zip/download/201200-23-calidad-aire-horario-zip.zip`

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.

```bash
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](../04.02-titanic/README.md#cómo-leer-data-scanned-en-athena).

## 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](../04.01-creardatalake/README.md), 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

- [ ] `SHOW PARTITIONS` lista `anio=2022`, `anio=2023` y `anio=2024`, y las filas de `aire_raw` y `aire_curated` coinciden con el paso 3 y el paso 4.
- [ ] Tu media de NO2 en 2022 es 28,21 y tu ranking de estaciones de 2024 empieza por la 56.
- [ ] Sabes explicar por qué un CSV ancho se pasa a formato largo antes de pasarlo a Parquet.
- [ ] Has comparado Data scanned de la raw (unos 22 MB) y de la curated (unos 3 MB).

Anterior: [04.03 NYC Taxi](../04.03-nyctaxi/README.md) · Siguiente: [04.05 Euribor e IPC](../04.05-euriboripc/README.md)