# Tema 4 · 6 · Ejercicio adicional C: optimizar el lago y defender el presupuesto

Presentación del Tema 4: «Ejercicio C · Optimizar el lago y defender el presupuesto» y «Ejercicio C · paso a paso». Duración: 60 a 120 minutos. Coste: céntimos (decenas de MB, salvo la tabla de texto, que escribe unos 400 MB). Es un ejercicio opcional de ampliación.

## Qué vas a hacer

Con los datos de NYC Taxi que ya tienes cargados, vas a medir cuánto lee Athena en cinco consultas habituales (la «línea base»), aplicar cuatro optimizaciones (menos columnas, filtro de partición, Parquet frente a texto, compactar), calcular cuánto ahorra cada una y traducirlo a dinero para defender un presupuesto. Terminas con dos medidas de higiene: una regla de ciclo de vida en S3 y un presupuesto en AWS Budgets.

**Qué se crea en AWS**: en la base `demo_datalake_db`, las tablas `nyc_taxi_csv` (texto, unos 400 MB) y `viajes_compacto` (Parquet compactado, unos 36 MB), con sus ficheros en `athena-results/tables/`; opcionalmente una regla de ciclo de vida en el bucket y un presupuesto de toda la cuenta en AWS Budgets.

## Qué contiene esta carpeta

| Fichero | Para qué sirve | Paso en que se usa |
|---|---|---|
| `5-athena-optimizar.sql` | SQL de apoyo, la solución probada: línea base, las cuatro optimizaciones, orden de impacto y costes, higiene (ciclo de vida y presupuesto, en comentarios) y limpieza | Pasos 2 a 5 |
| `README.md` | Esta guía | - |

**Intenta resolverlo primero por tu cuenta** y usa el SQL para comprobar tus cifras. Cómo usarlo: ábrelo con `cat` o `nano` en CloudShell (o desde tu PC), copia las sentencias en el editor de Athena y ejecútalas de una en una, con el workgroup `demo-athena-workgroup` y la base `demo_datalake_db`. Este fichero no lleva comentarios detrás del `;`: los valores esperados van en la línea de ENCIMA. Si añades tus propios comentarios, no los pongas detrás del `;` en la misma línea (la API de Athena da `MALFORMED_QUERY`, `Only one sql statement is allowed`).

## Antes de empezar

- Necesitas el Data Lake de [04.01](../04.01-creardatalake/README.md) **y los datos de NYC Taxi de [04.03 NYC Taxi](../04.03-nyctaxi/README.md)** (tablas `nyc_taxi_yellow_raw` y compañía, con los 5 meses de 2025 por defecto). La cabecera de `5-athena-optimizar.sql` dice «Requiere NYC Taxi cargado (./gestionar-nyc-taxi.sh)»: ese script está en la carpeta 04.03, no en esta; si no lo ejecutaste, hazlo allí antes de seguir.
- Las cifras de abajo son de los 5 meses de 2025 por defecto (2025-05 a 2025-09: 20.638.874 viajes en la raw). Con otros meses salen cifras distintas, pero las **relaciones** (3,1 veces más, 4,9 veces menos, 11,5 veces...) se mantienen.
- Cómo leer Data scanned: [04.02](../04.02-titanic/README.md#cómo-leer-data-scanned-en-athena).

## Cómo llevar los ficheros a CloudShell

1. En tu PC, comprime **esta carpeta** (`04.06-optimizarpresupuesto`) en un zip y no la descomprimas.
2. En CloudShell (región `eu-north-1`): Actions > Upload file > `04.06-optimizarpresupuesto.zip`.
3. Descomprime y entra:

```bash
unzip -o 04.06-optimizarpresupuesto.zip && cd 04.06-optimizarpresupuesto && 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.06-optimizarpresupuesto` 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. El SQL se pega en Athena y los comandos de higiene se ejecutan en CloudShell.)

## 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 · Línea base

**Mide con agregados (`SUM`), no con `LIMIT`**: con `LIMIT 100` Athena lee en paralelo y corta cuando quiere, las cifras no son estables y el «filtro de partición» puede salir leyendo más que la consulta sin filtro. Tampoco sirve `COUNT(*)`: sobre Parquet lee solo metadatos (0 B). Coste = max(bytes, 10 MB) x 5 USD por TB.

Ejecuta las cinco consultas habituales del apartado 1 del SQL y anota el Data scanned de cada una. Qué verás: `SELECT * LIMIT 100` 2.567.885 B (la primera vez; `LIMIT` no da cifras estables); `COUNT(*)` 0 B; `AVG(tip_amount)` con `payment_type = 1` 20.803.771 B; `GROUP BY pulocationid` 16.383.473 B; `SUM(total_amount)` por mes 35.482.600 B.

### Paso 3 · Las cuatro optimizaciones

Mide antes de tocar nada: sin la línea base no hay ahorro que demostrar. Una optimización cada vez (apartados 2 a 5 del SQL). Qué verás:

| Optimización | Antes | Después |
|---|---|---|
| 1 · solo las columnas necesarias | 4 columnas: 108.875.095 B | 1 columna: 35.482.600 B (3,1 veces más con 4 columnas) |
| 2 · filtro de partición (1 mes de 5) | 35.482.600 B | 7.274.878 B (4,9 veces menos) |
| 3 · Parquet frente a texto (`nyc_taxi_csv`, `TEXTFILE` comprimido en gzip) | texto: unos 407 MB (406.947.430 y 407.013.111 B en dos medidas) | Parquet: 35,5 MB (11,5 veces menos) |
| 4 · compactar (`viajes_compacto`) | 35,5 MB | unos 36,5 MB (36.377.849 y 36.561.656 B en dos medidas): sin ahorro apreciable (entre un 2 y un 3 % más) porque los ficheros mensuales ya son grandes (de 60 a 75 MB) |

### Paso 4 · Orden de impacto y coste

Ahorro de cada medida, de mayor a menor: Parquet frente a texto 91 %; filtro de partición 79 %; una columna frente a cuatro 67 %; compactar entre -2 y -3 %. Las cifras exactas varían algo de una ejecución a otra: valen las relaciones.

Coste a 100 consultas al día durante 30 días (3.000 consultas al mes; 5 USD/TB, mínimo 10 MB): texto 0,0020 USD por consulta (6,10 USD al mes); Parquet sin filtro 0,00018 USD (0,53 USD al mes); con filtro de partición, que lee 7,3 MB y factura el mínimo de 10 MB, 0,00005 USD (0,15 USD al mes). Estos costes usan el TB decimal (10^12 bytes); con el TB binario de la presentación (1.099.511.627.776 bytes) salen un 9 % menos (por ejemplo, 5,55 USD al mes en el texto).

Alternativa a copiar los valores de Recent queries: el historial de Data scanned por CLI.

```bash
for id in $(aws athena list-query-executions --work-group demo-athena-workgroup --region eu-north-1 \
            --no-paginate --max-results 20 --query 'QueryExecutionIds' --output text); do
  aws athena get-query-execution --query-execution-id "$id" --region eu-north-1 \
    --query 'QueryExecution.[QueryExecutionId,Statistics.DataScannedInBytes,Statistics.TotalExecutionTimeInMillis]' --output text
done
```

Qué verás: una línea por consulta con su identificador, los bytes escaneados y los milisegundos.

### Paso 5 · Higiene (opcional)

**Higiene 1 · caducar resultados de Athena, con cuidado.** Los ficheros de tus tablas CTAS (`titanic_parquet`, `nyc_taxi_yellow_curated`, `nyc_taxi_csv`, `viajes_compacto`, `aire_curated`...) están dentro de `athena-results/tables/<id>/`. **Una regla de ciclo de vida de 7 días sobre todo `athena-results/` los borraría y esas tablas devolverían 0 filas sin dar ningún error.** Aplica la regla solo al prefijo `athena-results/Unsaved/` (resultados de las consultas lanzadas desde la consola). **Son dos reglas, no una:** «Expire current versions» (7 días) y «Delete expired object delete markers» **no se pueden combinar en la misma regla** (por API da `MalformedXML`; en la consola las dos casillas se excluyen). Por consola: S3 > tu bucket > Management > Create lifecycle rule, con ese prefijo; en la primera regla marca «Expire current versions» a 7 días y, porque el bucket tiene versionado, «Permanently delete noncurrent versions» a 1 día; crea una segunda regla, con el mismo prefijo, que solo tenga «Delete expired object delete markers». Los resultados de consultas lanzadas desde scripts o CLI están en `athena-results/<id>.csv` (más su `.metadata`), no en `Unsaved/`: esa regla no los cubre; bórralos a mano cuando quieras con `aws s3 rm "s3://$BUCKET/athena-results/" --recursive --exclude "tables/*"` (el filtro deja intactas las tablas: probado, borra los resultados sueltos y las tablas CTAS siguen dando filas). Por CLI, guarda esto como `ciclo-vida.json` (cada regla lleva su `Filter`; la primera lleva `NoncurrentVersionExpiration` y la segunda solo `ExpiredObjectDeleteMarker`):

```json
{
  "Rules": [
    { "ID": "caducar-resultados-athena", "Status": "Enabled",
      "Filter": { "Prefix": "athena-results/Unsaved/" },
      "Expiration": { "Days": 7 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 1 } },
    { "ID": "limpiar-marcadores-athena", "Status": "Enabled",
      "Filter": { "Prefix": "athena-results/Unsaved/" },
      "Expiration": { "ExpiredObjectDeleteMarker": true } }
  ]
}
```

```bash
aws s3api put-bucket-lifecycle-configuration --bucket "$BUCKET" \
  --lifecycle-configuration file://ciclo-vida.json --region eu-north-1
aws s3api get-bucket-lifecycle-configuration --bucket "$BUCKET" --region eu-north-1
```

La primera orden **sustituye todas las reglas de ciclo de vida del bucket**: el bucket del stack no trae ninguna, así que aquí no pierdes nada. (La primera regla se probó por API; la segunda sigue la documentación de `put-bucket-lifecycle-configuration`: una regla con `ExpiredObjectDeleteMarker` no puede llevar `Days`.)

**Higiene 2 · presupuesto.** Billing and Cost Management > Budgets > Create budget > Cost budget (mensual), por ejemplo 10 USD, con alerta al 80 % por correo. Es un presupuesto de **toda la cuenta** (la alerta manda un correo real): bórralo al terminar si no lo quieres; si la consola no te deja, por CLI: `aws budgets delete-budget --account-id "$(aws sts get-caller-identity --query Account --output text)" --budget-name <nombre-del-presupuesto>` (la región del servicio Budgets es global; el nombre lo ves con `aws budgets describe-budgets --account-id <id>`).

## Errores frecuentes

| Qué ves | Causa | Solución |
|---|---|---|
| `TABLE_NOT_FOUND` con `nyc_taxi_yellow_raw` | No cargaste NYC Taxi | Haz [04.03](../04.03-nyctaxi/README.md) |
| Las cifras no se parecen a las de arriba | Cargaste otros meses o mides con `LIMIT`/`COUNT(*)` | Mide con `SUM`; compara relaciones, no valores absolutos |
| El «filtro de partición» lee más que la consulta sin filtro | Mediste con `LIMIT` | Usa agregados (`SUM`) |
| `MalformedXML` al aplicar la regla de ciclo de vida | Se combinó «Expire current versions» con «Delete expired object delete markers» en la misma regla, o falta el `Filter` | Dos reglas, cada una con su `Filter` (paso 5, Higiene 1) |
| Una tabla CTAS (`titanic_parquet`, `aire_curated`...) devuelve 0 filas sin error | Una regla de ciclo de vida borró `athena-results/tables/` | Aplica la regla solo a `athena-results/Unsaved/` y recrea la tabla |
| `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 |

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

1. En Athena: `DROP TABLE nyc_taxi_csv;` y `DROP TABLE viajes_compacto;`. Para encontrar el `<id>` de sus ficheros: `SHOW CREATE TABLE demo_datalake_db.nyc_taxi_csv;`.
2. Borra sus ficheros de `athena-results/tables/<id>/` (la CTAS de texto escribe unos 400 MB con 5 meses; `DROP TABLE` no borra los ficheros), o borra el stack completo desde 04.01.
3. Si creaste un presupuesto en AWS Budgets solo para este ejercicio y no lo quieres, bórralo (es de toda la cuenta).
4. Borra `ciclo-vida.json` de CloudShell si lo creaste: `rm -f ciclo-vida.json`.

## Cómo sabes que has terminado

- [ ] Tienes tu línea base de las cinco consultas con su Data scanned.
- [ ] Has medido las cuatro optimizaciones y tus relaciones se parecen a 3,1 / 4,9 / 11,5 / sin ahorro.
- [ ] Sabes ordenar las medidas por ahorro (Parquet 91 %, partición 79 %, columnas 67 %, compactar -2 a -3 %) y calcular el coste mensual (6,10 / 0,53 / 0,15 USD).
- [ ] Sabes por qué no se debe aplicar una regla de ciclo de vida a todo `athena-results/`.
- [ ] (Si es el último ejercicio) Vuelve a [04.01](../04.01-creardatalake/README.md) para la limpieza final y borra el stack.

Anterior: [04.05 Euribor e IPC](../04.05-euriboripc/README.md) · Siguiente: [04.07 Panel Streamlit y seguridad](../04.07-PanelStreamlitSeguridad.md)