Tema 3 · 9 · Ejercicios adicionales A y B: población del INE e índices con datos de aire

Presentación del Tema 3: «Ejercicio A» (datos del INE: claves ajenas y JOIN) y «Ejercicio B» (índices y EXPLAIN), de «Ejercicios adicionales». Duración: 20 a 30 minutos cada parte (la carga de genera_aire.sql tarda unos segundos; con Adminer, ~2 s medidos). Coste: ninguno propio (usas la RDS y Adminer que ya tienes); con los datos reales de Madrid, además, descargas ~20 MB a tu PC.

¿Dudas con un script? Todos los scripts de esta carpeta traen su propia ayuda: python3 limpia-ine.py --ayuda · python3 madrid-a-largo.py --ayuda (también vale --help o -h) explica los pasos y las opciones. Este README.md es el paso a paso de la actividad: tenlo a mano y consúltalo antes de preguntar.

Qué vas a hacer

Parte A (INE): cargar 8.132 municipios con su población y las 52 provincias en la base territorio, añadir la clave ajena que relaciona las dos tablas y hacer consultas con JOIN, GROUP BY y HAVING; después, comprobar que la integridad referencial impide borrar o insertar de forma incoherente. Parte B (aire): cargar 632.448 mediciones sintéticas de calidad del aire en la base aire, medir una consulta sin índice y con índice (EXPLAIN) y ver cuánto mejora.

Parte A: provincias.sql + municipios_ine.csv ──(Adminer)──> base territorio (provincias, municipios) + clave ajena
Parte B: genera_aire.sql ──(Adminer)──> base aire, tabla mediciones (632.448 filas) ──> CREATE INDEX + EXPLAIN
(opcional) limpia-ine.py / madrid-a-largo.py: generan los CSV desde datos reales (en tu PC)

Qué se crea en AWS: nada. Los ficheros de esta carpeta no crean nada en AWS: generan datos en tu PC o los cargan en la RDS que ya tienes.

Qué contiene esta carpeta

Antes de empezar

• [ ] RDS MariaDB Available (03.01) y Adminer conectado (03.02).
• [ ] Los ficheros están en tu PC; ábrelos con un editor de texto, no con Excel (Excel pierde los ceros de 01001).
• [ ] Para los pasos opcionales (scripts .py, LOAD DATA): Python 3 en tu PC y, para LOAD DATA, el cliente mariadb o mysql (instálalo en tu PC, o ejecútalo desde una máquina EC2 de tu cuenta) y una RDS pública con el Security Group BaseDeDatos abierto a tu IP en el 3306.
• Las dos partes son independientes entre sí; esta actividad no necesita las demás.

Cómo llevar los ficheros a donde toque

No hace falta CloudShell. Para cargar datos se llevan desde tu PC a Adminer (copiar y pegar el SQL en SQL command; el CSV con el enlace Import de la tabla). Los scripts .py opcionales se ejecutan en tu PC con Python 3, abriendo una terminal dentro de esta carpeta (03.09-ineaire).

Ficheros grandes: el límite de subida de Adminer y LOAD DATA LOCAL INFILE

Adminer sube los ficheros por HTTP y PHP limita su tamaño. Los dos despliegues de 03.02 dejan Adminer con upload_max_filesize = 100M y post_max_size = 100M, así que un Adminer creado con ellos admite ficheros de hasta 100 MB. Con un Adminer desplegado antes de esa corrección, o con cualquier otro que traiga el PHP por defecto, el límite es 2 MB (Unable to upload a file. Maximum allowed file size is 2MB.) y, por encima de 8 MB, The POST data is too large (HTTP 413).

Qué cabe siempre: provincias.sql, genera_aire.sql y municipios_ine.csv (220 KB). Qué no cabe con el límite de 2 MB: el CSV original del INE (27 MB) y el CSV de calidad del aire de Madrid en formato largo (~30 MB). El Adminer de Docker (adminer:latest, ver 03.02) admite 128 MB.

Si tu Adminer admite menos de lo que pesa el fichero, usa el cliente de línea de comandos desde tu PC (no pasa por PHP):

mariadb -h <endpoint-de-tu-rds> -u admin -p --ssl --local-infile=1 <base> \
  -e "LOAD DATA LOCAL INFILE 'fichero.csv' INTO TABLE tabla CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"

Requisitos: el cliente mariadb o mysql instalado en tu PC (o, mejor, en una máquina EC2 de tu cuenta con acceso a la RDS), que la RDS sea pública y que el Security Group BaseDeDatos deje pasar tu IP al puerto 3306 (My IP; desde CloudShell no valdría, porque su IP de salida es otra). La RDS MariaDB 10.11 trae local_infile=ON. CHARACTER SET utf8mb4 evita que las tildes y la ñ salgan rotas (València) cuando el CSV es UTF-8 y el cliente usa otra codificación; con los datos numéricos de calidad del aire no hace falta, pero no estorba. La opción --local-infile=1 es del lado del cliente: sin ella da The used command is not allowed with this MariaDB version. Si el CSV trae cabecera, añade IGNORE 1 LINES; si sus columnas no están en el orden de la tabla, indícalas entre paréntesis detrás del TERMINATED BY (por ejemplo (estacion, magnitud, fecha, hora, valor)). Tiempo medido: 1.076.939 filas en ~10 s contra un MariaDB local y alrededor de 1 minuto contra una RDS.

Paso a paso

Parte A. Ejercicio adicional A: INE

Paso 1. Crear la base territorio y las dos tablas SIN clave ajena (SQL y en el bloque Ejercicio adicional A de ejercicios.txt). El orden importa: con la clave ajena puesta antes de cargar los datos, la importación falla con Error 1452 Cannot add or update a child row.

Qué verás: la base territorio con las tablas provincias (cod_prov, nombre, ccaa) y municipios (cod_mun, nombre, cod_prov, poblacion), vacías.

Paso 2. Cargar las 52 provincias. Pega provincias.sql en SQL command y Execute (empieza con USE territorio;). Qué verás: sin errores; SELECT COUNT(*) FROM provincias; da 52.

Paso 3. Importar municipios_ine.csv. Base territorio > tabla municipios > select data > enlace Import > el fichero. Son 8.132 municipios (año 2025; columnas cod_mun, nombre, cod_prov, poblacion, sin cabecera). Qué verás: SELECT COUNT(*) FROM municipios; debe dar 8132.

Paso 4. Añadir la clave ajena.

ALTER TABLE municipios ADD FOREIGN KEY (cod_prov) REFERENCES provincias(cod_prov);

Si falla con 1452, busca los huérfanos con SELECT DISTINCT cod_prov FROM municipios WHERE cod_prov NOT IN (SELECT cod_prov FROM provincias);. No sirve rellenar provincias con INSERT ... SELECT DISTINCT (el CSV de población no trae nombre ni comunidad y las dos columnas son NOT NULL): por eso se entrega provincias.sql.

Paso 5. Consultas del ejercicio (están en ejercicios.txt con su resultado esperado). Qué verás: los municipios más poblados son Madrid (3.506.730), Barcelona (1.731.649), València (840.792; cod_mun 46250, el CSV la escribe con el nombre en valenciano), Zaragoza (693.091) y Sevilla (689.423); por comunidad, Andalucía 8.666.412, Cataluña 8.146.265 y Comunidad de Madrid 7.137.031; la consulta 3 (más de 50 municipios de menos de 1.000 habitantes) devuelve 27 provincias (Burgos 346, Salamanca 333, Guadalajara 254, Zaragoza 236...).

Paso 6. Integridad referencial. Las dos sentencias del final del bloque DEBEN dar error: DELETE FROM provincias WHERE cod_prov = '28'; da Error 1451 (hay municipios que dependen de ella) y un municipio de una provincia que no existe da Error 1452.

Paso 7 (opcional). Regenerar municipios_ine.csv desde el INE. El CSV original del INE (tabla 29005, https://www.ine.es/jaxiT3/files/t/es/csv_bdsc/29005.csv, 27 MB, columnas Municipios;Sexo;Periodo;Total) trae todos los años y los tres sexos y no cabe en un Adminer de 2 MB. limpia-ine.py lo descarga y lo convierte (solo Sexo = Total, el año más reciente con datos, código y nombre separados, sin el separador de miles). Desde una terminal en tu PC, dentro de esta carpeta:

python3 limpia-ine.py              # descarga el CSV y genera municipios_ine.csv
python3 limpia-ine.py ine.csv      # usa un CSV ya descargado
python3 limpia-ine.py --anio 2023  # otro año; con --sql genera además municipios_ine.sql

Qué verás: N municipios (anio ...) escritos en municipios_ine.csv. Esto sobrescribe municipios_ine.csv de la carpeta. Un fallo típico: abrir el CSV del INE en Excel y volver a guardarlo (Excel pierde los ceros de 01001). El script no pasa por Excel.

Parte B. Ejercicio adicional B: índices

Paso 8. Cargar los datos. Pega genera_aire.sql en SQL command y Execute. Crea la base aire, la tabla mediciones y la rellena con 632.448 filas sintéticas de 2024 (24 estaciones × 3 magnitudes × 366 días × 24 horas; la 28079008 es una de ellas y la magnitud 8 es NO₂). Todos los alumnos obtienen los mismos números. Qué verás: tarda unos segundos (con Adminer, ~2 s medidos) y al final muestra 632448 filas, del 2024-01-01 al 2024-12-31.

Paso 9. Consulta de referencia. Ejecútala dos veces y anota el tiempo de la segunda (está en ejercicios.txt). Qué verás: 366 filas (2024-01-01: 2,50; 2024-01-02: 5,01; 2024-01-03: 5,88...).

Paso 10. EXPLAIN sin índice. Pon EXPLAIN delante de la misma consulta. Qué verás: type = ALL y rows de unos 631.000 (con ANALYZE, r_rows = 632448).

Paso 11. Crear el índice (igualdad, igualdad, rango) y repetir:

CREATE INDEX idx_est_mag_fecha ON mediciones (estacion, magnitud, fecha);

Qué verás: repitiendo el EXPLAIN, type = range, rows de unos 17.700 y, con ANALYZE, r_rows = 8784. El tiempo baja de unos 0,2 s a unos 0,02 s. Tamaño medido: datos ~25,5 MB e índices ~14,5 MB con este índice (la consulta de information_schema.tables del fichero lo muestra).

Paso 12. Reto: compara con otro índice. Con el índice de la diapositiva original, (estacion, fecha), la mejora es menor (r_rows = 26352). Con un rango de un mes en vez de un año el factor es mucho mayor (r_rows = 744). Las sentencias DROP INDEX / CREATE INDEX están comentadas al final del bloque.

Paso 13 (opcional). Con datos reales de Madrid. El portal https://datos.madrid.es/dataset/201200-0-calidad-aire-horario publica un CSV de unos 20 MB en formato ancho (una fila por estación, magnitud y día, con columnas H01/V01... H24/V24). Enlace directo (comprobado el 2026-10-03; el nombre del enlace es estable, el contenido se actualiza; las URL antiguas de ficheros anuales dan 404):

curl -L -o madrid.csv "https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/resource/201200-1-calidad-aire-horario-csv/download/201200-1-calidad-aire-horario-csv.csv"

Si algún día ese enlace falla, entra en la página del dataset y copia el enlace del recurso «CSV» (pestaña de descargas). El fichero cubre un periodo reciente (el 2026-10-03, de 2025-01 a 2026-08): por eso se usa --anio 2025, el único año completo. madrid-a-largo.py lo pasa al formato de mediciones:

python3 madrid-a-largo.py madrid.csv --anio 2025 > mediciones_2025.csv
# 1.076.939 filas escritas (30 MB: no cabe en un Adminer de 2 MB, usa LOAD DATA, ver «Ficheros grandes»)

Para cargarlo con LOAD DATA hay que nombrar las columnas: la tabla mediciones tiene una primera columna id automática que el CSV no trae, y sin la lista el comando carga unas pocas filas y el resto se pierde con avisos de datos truncados (Skipped alto):

mariadb -h <endpoint-de-tu-rds> -u admin -p --ssl --local-infile=1 aire   -e "TRUNCATE TABLE mediciones; LOAD DATA LOCAL INFILE 'mediciones_2025.csv' INTO TABLE mediciones FIELDS TERMINATED BY ',' (estacion, magnitud, fecha, hora, valor)"

Debe terminar con Records: 1076939 Deleted: 0 Skipped: 0 y SELECT COUNT(*) FROM mediciones; dar 1.076.939.

Antes de cargar los datos reales vacía la tabla (TRUNCATE TABLE mediciones;) o usa una base nueva: si ya cargaste genera_aire.sql, el COUNT(*) sumaría las 632.448 filas sintéticas a las 1.076.939 reales. Con esos datos hay que usar el año del fichero en la consulta (BETWEEN '2025-01-01' AND '2025-12-31'); medido con 2025: sin índice r_rows = 1076939, con (estacion, magnitud, fecha) r_rows = 8609.

Errores frecuentes

Limpieza

Nada que borrar en AWS. Si quieres borrar las bases: DROP DATABASE territorio; y DROP DATABASE aire;; los ficheros que generaste en tu PC (madrid.csv, mediciones_2025.csv, ine_29005.csv, y municipios_ine.sql si usaste --sql) se pueden borrar a mano. Las bases desaparecen igualmente al borrar la RDS.

Esta es la última actividad que usa Adminer y la RDS: cuando la termines, borra Adminer (limpieza de 03.02) y después la RDS y el Security Group BaseDeDatos (limpieza de 03.01). Antes de borrar la RDS, asegúrate de que tienes en tu PC lo que tengas que entregar.

Cómo sabes que has terminado

• [ ] Parte A: territorio.municipios tiene 8132 filas, provincias 52, y la clave ajena impide borrar una provincia con municipios (error 1451) y insertar un municipio de una provincia inexistente (error 1452).
• [ ] Parte A: tus consultas dan Madrid 3.506.730 como municipio más poblado y 27 provincias en la consulta 3.
• [ ] Parte B: aire.mediciones tiene 632.448 filas, el EXPLAIN sin índice muestra type = ALL y con idx_est_mag_fecha muestra type = range.
• [ ] Sabes explicar por qué el orden de las columnas del índice es igualdad, igualdad, rango.

Anterior: 03.08 · Visor web de DynamoDB · Siguiente: 03.10 · Ejercicio adicional C (estaciones en DynamoDB)