Tema 4 · 2 · Ejercicio 1: Titanic (CSV, Crawler, nulos, Parquet y medir)

Presentación del Tema 4: «Ejercicio 1: Titanic» (de «Ejercicio 1: Titanic» a «CSV frente a Parquet · cómo medirlo en Athena»). Duración: 60 a 90 minutos. Coste: céntimos de Athena más entre 7 y 15 céntimos por cada ejecución de un Crawler de Glue (en esta actividad ejecutarás dos).

¿Dudas con un script? Todos los scripts de esta carpeta traen su propia ayuda: ./gestionar-titanic.sh --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.

Etiquetas de lo que creas. Este ejercicio no crea recursos propios: trabaja dentro de los de 04.01-creardatalake, que ya llevan las etiquetas curso = G214, universidad = CUNEF y actividad = 04.01-creardatalake. Más detalle (cómo etiquetar con la CLI, cómo buscar y qué hacer en una emergencia): README principal, sección «Etiquetas».

Qué vas a hacer

Vas a subir el CSV del Titanic (891 pasajeros) a tu Data Lake, catalogarlo con un Crawler de Glue y ver cómo falla si no le dices cómo se leen las comillas; lo arreglarás con un clasificador, resolverás el problema de los nulos, convertirás el CSV a Parquet con Athena y medirás cuántos bytes lee cada formato.

Qué se crea en AWS (dentro del stack de 04.01): el objeto raw/titanic/titanic.csv en tu bucket; un Crawler (crawler-titanic) y un clasificador (csv-titanic) de Glue; y en la base demo_datalake_db las tablas titanic (la del Crawler), titanic_csv (el CSV, todo texto) y titanic_parquet (copia en Parquet particionada por survived, con ficheros en athena-results/tables/).

Qué contiene esta carpeta

Antes de empezar

• Necesitas el Data Lake de 04.01 Crear el Data Lake: el stack g214-datalake-demo debe estar en CREATE_COMPLETE, en la región eu-north-1. El script gestionar-titanic.sh no lee ningún fichero de esa carpeta: consulta el bucket directamente al stack con la AWS CLI, y si el stack no existe o no está en CREATE_COMPLETE se detiene con un mensaje que dice qué hacer. Antes de empezar comprueba también que aws es la versión 2 y que tus credenciales son válidas.
• La región de la consola es Europa (Estocolmo) eu-north-1.

Cómo llevar los ficheros a CloudShell

1. En tu PC, comprime esta carpeta (04.02-titanic, entera) en un zip y no la descomprimas.
2. En CloudShell (región eu-north-1): Actions > Upload file > 04.02-titanic.zip.
3. Descomprime, entra y da permisos:

unzip -o 04.02-titanic.zip && cd 04.02-titanic && chmod -R u+rwX . && chmod +x *.sh

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

Ejecuta el script desde dentro de la carpeta: busca titanic.csv y 1-athena-titanic.sql en la carpeta actual y, si no están, junto al propio script. Si al ejecutarlo ves bad interpreter o errores con \r, el zip ha dejado finales de línea de Windows: sed -i 's/\r$//' *.sh y repite.

Paso a paso

La presentación tiene el guion. Aquí van los pasos y qué debes ver.

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"

Qué verás: g214-datalake-demo-datalakebucket-<letras y números>. (CloudShell pierde las variables si se cierra la sesión: repite este comando si vuelves más tarde.)

Paso 2 · Subir el CSV a S3

Por consola: S3 > tu bucket > crea la carpeta raw y, dentro, la carpeta titanic > Upload > titanic.csv. O desde CloudShell, dentro de esta carpeta:

aws s3 cp titanic.csv "s3://$BUCKET/raw/titanic/titanic.csv" --region eu-north-1
aws s3 ls "s3://$BUCKET/raw/titanic/" --region eu-north-1

Qué verás: upload: ./titanic.csv to s3://<tu-bucket>/raw/titanic/titanic.csv y un listado con 60302 titanic.csv. No dejes otros ficheros en esa carpeta: todo lo que haya dentro se lee como parte de la tabla.

Paso 3 · Crawler sin clasificador: ver el fallo

1. Glue > Data Catalog > Databases: debe aparecer demo_datalake_db.
2. Glue > Crawlers > Create crawler (crea uno nuevo: en la consola de Glue no existe «duplicar» un Crawler ni un rol). Data source: S3, ruta s3://<tu-bucket>/raw/titanic/ (la carpeta, no el fichero). IAM role: g214-datalake-demo-GlueCrawlerRole (lo ha creado el stack y aparece en los Outputs como GlueCrawlerRoleArn; no hace falta crear otro rol). Database: demo_datalake_db. Schedule: On demand. Create crawler y Run (tarda unos 40 segundos). No uses ni ejecutes el Crawler g214-datalake-demo-crawler que ya trae el stack: rastrea toda la carpeta raw/ y, si lo ejecutas después de los ejercicios, sobrescribe las tablas que hayas arreglado a mano (la corrección de tipos del paso 5 se pierde y, si ya habías subido esos datos, aparecen tablas nuevas como aire, euribor, ipc y nyc_taxi).
3. Cuando termine, en Glue > Tables aparece la tabla titanic.
4. En Athena (comprueba que la región es Estocolmo): arriba a la derecha elige el workgroup demo-athena-workgroup y, en el panel izquierdo, la base demo_datalake_db. No marques «Reuse query results». Ejecuta:

SELECT sex, COUNT(*) AS supervivientes
FROM demo_datalake_db.titanic
WHERE survived = 1
GROUP BY sex;

SELECT passengerid, survived, pclass, name, sex FROM "demo_datalake_db"."titanic" LIMIT 10;

Qué verás (y no debería ser así): la columna sex no contiene male/female sino trozos de nombre ( Mrs. John Bradley (Florence Briggs Thayer)", Miss. Laina"...), y salen cientos de grupos en vez de dos (328 con survived = 1; 803 valores distintos de sex en toda la tabla). Motivo: en titanic.csv el nombre lleva una coma dentro de comillas ("Braund, Mr. Owen Harris") y, sin un clasificador que respete las comillas, cada coma cuenta como separador de columna. Sin clasificador, el Crawler sí detecta bien la cabecera, los 12 nombres y los tipos (survived bigint, age double...): lo único que falla es el reparto por comas. El fichero está bien; lo que está mal es cómo se ha descrito.

Si Athena te pide una ruta para los resultados (No output location provided), estás en el workgroup primary: cámbialo a demo-athena-workgroup, que ya guarda los resultados en s3://<tu-bucket>/athena-results/ y no hay que configurar nada.

Equivalente por línea de comandos (probado en la revisión; útil si la consola de Glue te da problemas), con $BUCKET del paso 1:

aws glue create-crawler --name crawler-titanic --region eu-north-1 \
  --role g214-datalake-demo-GlueCrawlerRole --database-name demo_datalake_db \
  --targets "S3Targets=[{Path=s3://$BUCKET/raw/titanic/}]"
aws glue start-crawler --name crawler-titanic --region eu-north-1
aws glue get-crawler --name crawler-titanic --region eu-north-1 --query "Crawler.[State,LastCrawl.Status]" --output text

Repite el último comando hasta ver READY SUCCEEDED (unos 40 segundos).

Paso 4 · Clasificador CSV y Crawler nuevo

En la presentación, primero se explica por qué el CSV se lee mal, después se dan los pasos del clasificador (pasos 3 y 4 de abajo) y por último se repite la consulta.

1. Glue > Tables: selecciona titanic y bórrala (Delete). Los datos de S3 siguen ahí.
2. Glue > Crawlers: actualiza la vista, selecciona tu crawler, Actions > Delete crawler.
3. Glue > Data Catalog > Classifiers > Add classifier: nombre libre (por ejemplo csv-titanic), tipo CSV, delimitador de columna coma (,), símbolo de comillas comilla doble ("), y el resto por defecto.
4. Crea otro Crawler como en el paso 3 (rol g214-datalake-demo-GlueCrawlerRole) y, en la parte de clasificadores personalizados, añade csv-titanic. Run. (El clasificador se crea antes, en Classifiers, no dentro del asistente del Crawler.)
5. En Athena, repite la consulta:

SELECT passengerid, survived, pclass, name, sex FROM "demo_datalake_db"."titanic" LIMIT 10;

Qué verás: 10 filas con el nombre completo en name (por ejemplo Braund, Mr. Owen Harris) y male/female en sex. La tabla titanic pasa a usar OpenCSVSerde (quoteChar "), con los mismos tipos que antes.

Equivalente por línea de comandos (probado en la revisión): borra la tabla y el Crawler del paso 3 (aws glue delete-table --database-name demo_datalake_db --name titanic --region eu-north-1 y aws glue delete-crawler --name crawler-titanic --region eu-north-1) y crea el clasificador y el Crawler nuevo:

aws glue create-classifier --region eu-north-1 \
  --csv-classifier 'Name=csv-titanic,Delimiter=",",QuoteSymbol="\""'
aws glue create-crawler --name crawler-titanic --region eu-north-1 \
  --role g214-datalake-demo-GlueCrawlerRole --database-name demo_datalake_db \
  --targets "S3Targets=[{Path=s3://$BUCKET/raw/titanic/}]" --classifiers csv-titanic
aws glue start-crawler --name crawler-titanic --region eu-north-1

Paso 5 · Nulos

SELECT * FROM "demo_datalake_db"."titanic" LIMIT 10;

En titanic.csv hay 177 pasajeros sin edad (celda vacía) y el Crawler ha declarado age como número decimal. Athena falla con BAD_DATA: Error parsing column 'age' with value '' ... NumberFormatException: empty String (en otras versiones el mensaje empieza por HIVE_BAD_DATA). Arréglalo así: Glue > Data Catalog > Tables > titanic > Edit schema > cambia el tipo de age (y fare, como pide la presentación) a string > Save. Repite la consulta: ya debe funcionar (con age como texto, AVG(age) deja de funcionar: es el precio de este arreglo rápido; para calcular con ella, AVG(CAST(NULLIF(age, '') AS DOUBLE)), el mismo patrón que usa la CTAS del script gestionar-titanic.sh).

Tipos de Athena que vas a ver (apartado de tipos de datos de Athena): string es texto (admite celdas vacías y cualquier carácter: por eso arregla el error), bigint es un entero grande (survived, pclass, passenger_count), double un decimal (age, fare, total_amount), int un entero corto (year, month de las particiones) y timestamp una fecha con hora (pickup_ts). Con CAST(x AS tipo) conviertes un valor; TRY_CAST devuelve NULL en vez de fallar; NULLIF(x, '') convierte la celda vacía en NULL. En un CSV el tipo no está en el fichero: lo declara el catálogo (el Crawler lo adivina, y se equivoca con las celdas vacías); en Parquet viaja dentro del fichero.

Si vuelves a ejecutar un Crawler (el tuyo o el del stack), este cambio se deshace: el Crawler vuelve a escribir age y fare como double y SELECT * ... LIMIT 10 vuelve a dar BAD_DATA. Arréglalo otra vez, o mejor, no relances el Crawler.

Alternativa por línea de comandos (orientativa; la consola es la vía de referencia):

aws glue get-table --database-name demo_datalake_db --name titanic --region eu-north-1 --query Table > tabla.json
jq '{Name, StorageDescriptor, PartitionKeys, TableType, Parameters} | with_entries(select(.value != null))
    | (.StorageDescriptor.Columns[] | select(.Name == "age" or .Name == "fare") | .Type) = "string"' tabla.json > tabla-nueva.json
aws glue update-table --database-name demo_datalake_db --region eu-north-1 --table-input file://tabla-nueva.json

Después lanza las consultas de la presentación y apunta el valor Data scanned de cada una (ver el recuadro «Cómo leer Data scanned» más abajo):

SELECT pclass, sex, AVG(survived) FROM titanic GROUP BY pclass, sex;
SELECT sex, AVG(survived) FROM titanic GROUP BY sex;

Estas consultas funcionan sin CAST: en la tabla titanic del Crawler survived es bigint (con y sin clasificador). Debes ver female 0.742 y male 0.189 (por clase y sexo, los valores del paso 7), con Data scanned de 60.302 bytes en las cuatro. El error de tipos (FUNCTION_NOT_FOUND ... Unexpected parameters (varchar) for function avg) solo aparece con titanic_csv, la tabla del script del paso 6, donde todo es STRING: ahí sí hace falta AVG(CAST(survived AS DOUBLE)) (paso 8).

Paso 6 · Convertir a Parquet con el script

./gestionar-titanic.sh crear

(Sin crear, ./gestionar-titanic.sh abre un menú numerado: 1) Crear, 2) Ver estado, 3) Eliminar lo de este ejercicio, 0) Salir. Las órdenes directas son crear, estado, eliminar y ayuda; también valen --create, --status y --delete.)

No pide datos por teclado. Hazlo desde esta carpeta. Tarda unos 20 segundos. Qué hace: sube titanic.csv a s3://<tu-bucket>/raw/titanic/, crea demo_datalake_db.titanic_csv (tabla externa sobre el CSV, todo STRING) y demo_datalake_db.titanic_parquet (copia en Parquet con los tipos convertidos y particionada por survived), y lanza 3 consultas de ejemplo. El SQL completo está en 1-athena-titanic.sql; léelo con la presentación. Si falla algo (stack inexistente, sin credenciales, una sentencia de Athena...) imprime qué ha pasado y qué hacer, sin trazas; si AWS limita las peticiones, reintenta solo.

Qué verás (cada línea empieza con un emoji en pantalla): Descubriendo bucket del stack g214-datalake-demo..., Bucket: <tu-bucket>, Subiendo dataset Titanic a S3 (raw)..., una línea upload: ..., Preparando SQL de Titanic con el bucket real..., y después 7 pares de líneas Athena: <inicio de la sentencia>... y OK (<identificador>). Termina con Listo. Tabla Titanic creada y dataset subido. (Mensajes comprobados en AWS real.)

Importante: el script NO imprime los resultados de las 3 consultas finales; solo comprueba que terminan bien. Para verlos, abre Athena > Recent queries, o repite las consultas a mano (paso 7).

Si repites el script: es seguro. Borra y recrea las dos tablas y vuelve a subir el CSV (queda otra versión del fichero en el bucket, sin efecto en los datos). Los ficheros Parquet del CTAS se escriben cada vez en una carpeta nueva y los anteriores quedan sin usar (céntimos).

Dónde están los ficheros Parquet: no en raw/, sino bajo s3://<tu-bucket>/athena-results/tables/<identificador>/survived=0/ y .../survived=1/. Para ver la ruta exacta ejecuta en Athena SHOW CREATE TABLE demo_datalake_db.titanic_parquet;.

Ver qué hay cargado: ./gestionar-titanic.sh estado muestra el estado del stack, los objetos de raw/titanic/ con su tamaño, si existen el workgroup y la base, qué tablas hay (titanic, titanic_csv, titanic_parquet), el estado del Crawler crawler-titanic y del clasificador csv-titanic, y te dice si falta cargar el ejercicio. No cambia nada.

Paso 7 · Consultas sobre Parquet

Selecciona la base demo_datalake_db en Athena y ejecuta:

SELECT sex, AVG(survived) FROM titanic_parquet GROUP BY sex;
SELECT pclass, sex, AVG(survived) FROM titanic_parquet GROUP BY pclass, sex;

Qué verás para comprobar que todo va bien (cálculo sobre titanic.csv):

(Sin redondear, Athena muestra por ejemplo 0.7420382165605095.) Conclusión: sobrevivieron proporcionalmente más las mujeres y las clases altas.

Paso 8 · CSV frente a Parquet: medir

Mide con las consultas siguientes y rellena tu tabla (la misma que trae la presentación, con sus columnas Consulta, CSV, Parquet y Relación). La primera consulta sobre la tabla CSV necesita CAST porque en titanic_csv todas las columnas son texto:

-- 1) misma consulta, dos formatos
SELECT sex, AVG(CAST(survived AS DOUBLE)) FROM demo_datalake_db.titanic_csv GROUP BY sex;
SELECT sex, AVG(survived) FROM demo_datalake_db.titanic_parquet GROUP BY sex;

-- 2) proyección de columnas
SELECT * FROM demo_datalake_db.titanic_parquet;
SELECT sex FROM demo_datalake_db.titanic_parquet;
SELECT * FROM demo_datalake_db.titanic_csv;
SELECT sex FROM demo_datalake_db.titanic_csv;

-- 3) filtro de partición
SELECT AVG(age) FROM demo_datalake_db.titanic_parquet;
SELECT AVG(age) FROM demo_datalake_db.titanic_parquet WHERE survived = 1;

Qué verás (valores medidos en la revisión; los tuyos pueden variar algo): la tabla CSV lee siempre el fichero entero (60.302 bytes), pidas una columna o doce; la tabla Parquet lee solo las columnas que pides.

Quédate con la relación, no con el valor absoluto. Con un dataset tan pequeño todas las consultas se facturan igual (mínimo de 10 MB, 0,00005 USD); el ahorro se ve en bytes y se convierte en dinero cuando el lago llega a terabytes. Para medir el filtro de partición no uses COUNT(*): sobre Parquet lee solo metadatos y marca 0 B con y sin filtro; por eso el ejemplo usa AVG(age).

Cómo leer Data scanned en Athena

Lo vas a usar en todas las actividades siguientes.

• Tras ejecutar una consulta, en la parte de resultados aparecen Run time y Data scanned (bytes leídos de S3). Anótalos con su unidad (KB o MB). La pestaña Recent queries los lista por consulta.
• Data scanned no es el tamaño del resultado ni el número de filas: es lo que Athena leyó, y es lo que se factura (5 USD por TB, mínimo 10 MB por consulta).
• Compara siempre la misma consulta en dos tablas, o dos versiones de una consulta, cambiando una sola cosa cada vez.
• No marques «Reuse query results»: una consulta repetida saldría con 0 bytes.
• SELECT COUNT(*) sobre Parquet lee casi solo metadatos (marca 0 B); para ver el efecto de columnas o particiones usa una columna real (SUM, AVG).
• La consola muestra el valor con su unidad (bytes, KB, MB). Lo que se factura es el mayor entre ese valor y 10 MB: con el Titanic todo cae en el mínimo; el ahorro real se ve con NYC Taxi (MB, 04.03) y se convierte en dinero a escala de terabytes.
• LIMIT no reduce lo escaneado de forma fiable (Athena lee en paralelo antes de cortar): con NYC y 5 meses, SELECT * ... LIMIT 10 leyó de unos 3 a unos 35 MB según la ejecución, mientras que sin LIMIT son siempre unos 350 MB. Para medir, usa agregados (SUM, AVG).

Errores frecuentes

Limpieza

Solo lo de esta actividad (el stack NO se borra aquí: se borra al final del Tema 4 desde 04.01, y debe ser lo último):

1. Con el script, desde esta carpeta:

./gestionar-titanic.sh eliminar

(o la opción 3 del menú). Lista lo que ha creado el ejercicio y te pide confirmar con s (--si para no preguntar). Borra: el Crawler crawler-titanic y cualquier otro Crawler cuyo destino sea raw/titanic/, el clasificador csv-titanic, las tablas titanic, titanic_csv y titanic_parquet (con los ficheros Parquet de titanic_parquet en athena-results/tables/) y los datos de raw/titanic/ (todas las versiones del fichero). Al final verifica y lista lo que quede. No toca el stack ni nada de otros ejercicios. Si no hay nada, lo dice y termina bien: se puede repetir. Si lo ejecutas a mitad del ejercicio, tendrás que repetir los pasos 2 a 4. 2. Si creaste tu Crawler o tu clasificador con otros nombres o por la consola, bórralos a mano: Glue > Crawlers y Glue > Data Catalog > Classifiers, o por CLI con tus nombres (aws glue delete-crawler --name <tu-crawler> --region eu-north-1 y aws glue delete-classifier --name <tu-clasificador> --region eu-north-1). Si el asistente de la consola creó un rol nuevo, bórralo también en IAM > Roles (AWSGlueServiceRole-...). 3. Los comandos del paso 5 dejaron tabla.json y tabla-nueva.json en CloudShell (ocupan bytes): rm -f tabla.json tabla-nueva.json. 4. Si no vas a usar eliminar y solo quieres vaciar a mano: en Athena, DROP TABLE de titanic, titanic_csv y titanic_parquet, y borra los ficheros de raw/titanic/ y los de athena-results/tables/ de titanic_parquet. Si vas a borrar el stack, no hace falta nada de esto: todo se va con él.

Cómo sabes que has terminado

• [ ] aws glue get-tables --database-name demo_datalake_db --region eu-north-1 --query "TableList[].Name" lista titanic, titanic_csv y titanic_parquet (y ./gestionar-titanic.sh estado dice que el script ya ha hecho su trabajo).
• [ ] Puedes explicar por qué la tabla del primer Crawler tenía las columnas desplazadas.
• [ ] Has anotado Data scanned del CSV y del Parquet y sabes decir cuántas veces menos lee el Parquet (unas 184 veces con una columna).
• [ ] Tus tasas de supervivencia coinciden: mujeres 0.742, hombres 0.189.

Anterior: 04.01 Crear el Data Lake · Siguiente: 04.03 NYC Taxi