D
Adquisición y arquitectura de datos M6 intermedio 42 min

Capitulo 06

SQL y DuckDB para análisis de datos

De la consulta relacional al pipeline analítico que trabaja directamente sobre archivos grandes

Fundamentos de SQL, análisis exploratorio, conexión de DuckDB con Python, consultas eficientes, procesamiento out-of-core y comparación razonada con Pandas. Incluye una guía práctica para analizar datos meteorológicos.

La pregunta viaja hacia los datos

En Ciencia de Datos no siempre conviene cargar una tabla completa en Pandas. SQL permite expresar qué necesitas y DuckDB puede ejecutar esa pregunta directamente sobre CSV o Parquet, filtrar temprano y entregar a Python un resultado pequeño, trazable y listo para analizar.

Trae solo las columnas y filas que vas a usar; el resto debe quedarse cerca del almacenamiento.

— Principio de consulta analítica

6.1 Mapa de aprendizaje

Al finalizar este capítulo podrás:

  • Explicar qué problema resuelve SQL en un pipeline de datos y diferenciar DDL, DML, DQL, TCL y DCL.
  • Escribir consultas con SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING y funciones de agregación.
  • Conectar DuckDB con Python usando una base en memoria o persistente.
  • Consultar CSV y Parquet directamente, sin cargar todo el archivo en memoria.
  • Leer un plan de ejecución y reconocer decisiones que afectan el rendimiento.
  • Elegir entre DuckDB y Pandas con criterios de volumen, memoria, expresividad y destino del resultado.
  • Completar la pre-entrega de análisis meteorológico con consultas reproducibles y resultados documentados.

💡 Piensa en el tamaño, la forma y la calidad del dato.

6.2 SQL: el lenguaje para preguntar al dato

SQL (Structured Query Language) es un lenguaje declarativo para interactuar con datos estructurados. Es declarativo porque describes qué resultado quieres y el motor decide cómo obtenerlo mediante un plan de ejecución.

En un flujo de Ciencia de Datos, SQL es más que una herramienta de bases de datos: funciona como una primera etapa de EDA y de preprocesamiento masivo. Filtrar y agregar cerca del almacenamiento evita transferir información innecesaria a Python.

Clasificación de sentencias

ConceptoDescripcion
DDL Define o modifica estructuras. CREATE, ALTER, DROP
DML Inserta, actualiza o elimina registros. INSERT, UPDATE, DELETE
DQL Consulta y recupera datos. SELECT
TCL Controla transacciones e integridad. COMMIT, ROLLBACK
DCL Administra permisos y seguridad. GRANT, REVOKE

En este capítulo el foco está en DQL y en la lógica de consulta. DDL, DML, TCL y DCL siguen siendo importantes porque una consulta profesional debe convivir con un esquema confiable, permisos correctos y operaciones que puedan confirmarse o revertirse.

SQL declarativo

M6

Lenguaje en el que expresas el resultado deseado sin describir cada paso físico de lectura, filtrado o agrupación. El motor optimiza la ejecución.

Ej: SELECT region, AVG(ventas) FROM ventas GROUP BY region; describe la métrica, no cómo recorrer cada fila.

#sql #fundamentos

SELECT y FROM: qué necesito y de dónde sale

Toda consulta empieza por dos preguntas:

  1. ¿Qué columnas necesito?
  2. ¿De qué tabla o archivo provienen?
SELECT nombre_producto, precio
FROM inventario_ventas;

SELECT define la proyección de columnas y FROM define la fuente. Aunque SELECT * es útil para una inspección rápida, no debe convertirse en la consulta final: puede leer columnas pesadas, consumir memoria y romperse cuando cambia el esquema.

-- Exploración rápida, aceptable en una tabla pequeña
SELECT * FROM productos LIMIT 5;

-- Consulta explícita, preferible para un pipeline
SELECT producto_id, categoria, precio
FROM productos
WHERE activo = true;

WHERE y operadores

WHERE filtra filas antes de agruparlas. La reducción temprana del volumen es una decisión de rendimiento y también una decisión de calidad: permite excluir registros que no pertenecen a la pregunta.

SELECT track_name, artist_name, popularity
FROM tracks
WHERE genre = 'Rock'
  AND popularity > 80
  AND release_date >= '2023-01-01';

Operadores frecuentes:

CategoríaOperadoresUso
Comparación=, !=, <>, >, <, >=, <=Comparar valores y rangos.
LógicaAND, OR, NOTCombinar condiciones.
PertenenciaIN (...)Comparar contra una lista.
AusenciaIS NULL, IS NOT NULLDetectar datos faltantes.
PatrónLIKEBuscar coincidencias de texto.
NULL no es cero

NULL representa un valor desconocido o ausente. No se compara con = NULL; se consulta con IS NULL. Además, una condición como precio > 100 no incluye filas cuyo precio sea NULL.

ORDER BY, LIMIT y agregaciones

ORDER BY organiza el resultado y LIMIT reduce la cantidad de filas devueltas. Son herramientas especialmente útiles para explorar extremos y muestras.

-- Los cinco productos más caros
SELECT nombre, precio
FROM productos
ORDER BY precio DESC
LIMIT 5;

Las funciones de agregación convierten muchas filas en una métrica:

  • COUNT(*): cuenta filas.
  • COUNT(columna): cuenta valores no nulos.
  • SUM(columna): suma valores numéricos.
  • AVG(columna): calcula un promedio.
  • MIN(columna) y MAX(columna): localizan extremos.
SELECT
  COUNT(*) AS total_transacciones,
  AVG(monto) AS monto_promedio,
  MAX(monto) AS transaccion_mas_alta
FROM transacciones
WHERE etiqueta_fraude = 1;

El alias con AS no es decoración. Hace que el resultado sea más legible cuando pasa a un DataFrame, a un dashboard o a un informe.

GROUP BY y HAVING

GROUP BY crea grupos con valores iguales para que las métricas se calculen por segmento.

SELECT
  categoria,
  COUNT(*) AS cantidad_productos,
  AVG(precio) AS precio_medio
FROM catalogo
GROUP BY categoria;

La diferencia esencial es el momento en que filtra cada cláusula:

  • WHERE filtra filas antes de la agrupación.
  • HAVING filtra grupos después de calcular la agregación.
SELECT tienda, SUM(ventas) AS ventas_totales
FROM ventas
WHERE fecha >= '2024-01-01'
GROUP BY tienda
HAVING SUM(ventas) > 1000
ORDER BY ventas_totales DESC;

El orden lógico habitual de una consulta es:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

El orden de escritura empieza normalmente con SELECT, pero el motor razona las etapas en un orden lógico parecido al anterior.

6.3 SQL como primera herramienta de EDA

Imagina una plataforma de comercio electrónico con miles de millones de transacciones. Descargar toda la tabla para explorarla en Python puede saturar la RAM, demorar la red y hacer difícil repetir el análisis. La alternativa es llevar la pregunta hacia los datos:

Fuente grande → SQL filtra y agrega → resultado pequeño → Pandas/visualización/ML

Preguntas exploratorias que SQL responde rápido

PreguntaConsulta o técnica
¿Cuántos registros hay?COUNT(*)
¿Qué valores faltan?COUNT(*) - COUNT(columna)
¿Qué categorías existen?SELECT DISTINCT categoria
¿Cuál es el rango temporal?MIN(fecha), MAX(fecha)
¿Qué segmentos concentran el volumen?GROUP BY + COUNT o SUM
¿Cuáles son los extremos?ORDER BY + LIMIT
SELECT
  COUNT(*) AS filas,
  COUNT(*) - COUNT(temperatura) AS nulos_temperatura,
  MIN(fecha) AS primera_fecha,
  MAX(fecha) AS ultima_fecha,
  AVG(temperatura) AS temperatura_media
FROM 'data/clima.csv';

La consulta anterior convierte una inspección vaga en un diagnóstico medible: tamaño, faltantes, cobertura temporal y tendencia central.

EDA con SQL

M6

Exploración de datos realizada cerca de la fuente mediante conteos, filtros, agrupaciones, rangos y controles de calidad antes de cargar el resultado en una herramienta de análisis.

Ej: Contar nulos y agrupar temperaturas por estación antes de construir un DataFrame para visualizar.

#eda #sql #calidad-de-datos

6.4 DuckDB: un motor analítico embebido

DuckDB es un motor SQL orientado a análisis (OLAP). Se ejecuta dentro del proceso de Python, no necesita un servidor separado y está optimizado para operaciones sobre columnas, agregaciones y archivos analíticos.

Por qué usar DuckDB

  • Puede consultar CSV y Parquet directamente.
  • Usa procesamiento vectorizado y paralelismo de CPU.
  • Puede trabajar out-of-core cuando el conjunto no entra completamente en RAM.
  • Se integra con Pandas sin obligar a elegir una sola herramienta.
  • Permite prototipos reproducibles con una base en memoria o persistencia en un archivo.

Instalación y conexiones

pip install duckdb pandas pyarrow
import duckdb

# La base vive mientras corre el proceso
con_memoria = duckdb.connect(database=':memory:')

# Las tablas se conservan en el archivo
con_archivo = duckdb.connect(database='clima.duckdb')

con_memoria.close()
con_archivo.close()

La conexión en memoria es adecuada para pruebas y ETLs temporales. La conexión persistente permite reutilizar tablas entre ejecuciones, pero el archivo debe tratarse como un artefacto generado y no como la fuente original del repositorio.

Ejecutar una consulta y volver a Pandas

import duckdb

con = duckdb.connect(database=':memory:')
con.execute('CREATE TABLE lecturas (id INTEGER, valor DOUBLE)')
con.execute("INSERT INTO lecturas VALUES (1, 25.5), (2, 28.1)")

df = con.sql('''
    SELECT id, valor
    FROM lecturas
    WHERE valor > 26
''').df()

print(df)
con.close()

con.execute() es útil para sentencias que modifican estructuras o datos. con.sql() devuelve un objeto de relación que puede convertirse en DataFrame con .df().

DuckDB y Pandas no compiten siempre

Una práctica moderna es usar DuckDB para leer, filtrar y agregar grandes volúmenes, y Pandas para el análisis estadístico, la visualización y la preparación final de vectores para scikit-learn.

Consultas parametrizadas

No construyas SQL concatenando valores recibidos desde una entrada externa. Usa parámetros para reducir errores e inyección de SQL.

import duckdb

con = duckdb.connect()
minimo = 26

resultado = con.execute(
    'SELECT * FROM lecturas WHERE valor > ?',
    [minimo],
).fetchdf()

6.5 Consultar CSV y Parquet directamente

DuckDB puede tratar archivos como tablas virtuales. No hace falta importar primero el archivo a una base interna.

-- CSV
SELECT region, AVG(ventas) AS ventas_promedio
FROM 'data/ventas.csv'
WHERE ventas IS NOT NULL
GROUP BY region;

-- Parquet
SELECT fecha, estacion, temperatura
FROM 'data/clima.parquet'
WHERE temperatura BETWEEN -10 AND 45
LIMIT 100;

También puedes usar funciones explícitas cuando quieras comunicar mejor la intención:

SELECT * FROM read_csv('data/clima.csv', header = true);
SELECT * FROM read_parquet('data/clima.parquet');

CSV frente a Parquet

ConceptoDescripcion
CSV Universal, visible y cómodo para intercambiar datos. No conserva tipos fuertes y debe leer líneas para encontrar columnas.
Parquet Comprime y lee solo las columnas necesarias. Preferible para pipelines analíticos y datasets grandes.

Si el archivo tiene cien columnas y la consulta usa solamente dos, Parquet puede evitar leer las otras noventa y ocho. En CSV, el motor debe interpretar las líneas del archivo para localizar la información.

6.6 Consultas eficientes y planes de ejecución

Cada query consume CPU, memoria, disco y, en algunos escenarios, red. El rendimiento no depende solo del tamaño del archivo: también depende de cuántas columnas lees, cuándo filtras, si ordenas sin necesidad y qué operaciones obligan a mover datos.

Las reglas de oro

  1. Selecciona columnas explícitas; evita SELECT * en el pipeline final.
  2. Filtra lo antes posible con WHERE.
  3. Usa LIMIT para exploraciones y muestras.
  4. Agrega en SQL antes de transportar el resultado a Python.
  5. Ordena solo cuando la pregunta lo necesita.
  6. Evita funciones sobre columnas filtradas cuando impidan aprovechar el almacenamiento o un índice.
  7. Mide antes de afirmar que una consulta es rápida.
-- Menos eficiente: aplica YEAR a cada fila
SELECT *
FROM ventas
WHERE YEAR(fecha) = 2024;

-- Más explícita: filtra un rango de fechas
SELECT fecha, total
FROM ventas
WHERE fecha >= '2024-01-01'
  AND fecha < '2025-01-01';

EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra el plan que el motor pretende ejecutar. EXPLAIN ANALYZE además ejecuta la consulta y permite observar tiempos reales.

EXPLAIN
SELECT nombre, salario
FROM empleados
WHERE departamento = 'Ventas';

EXPLAIN ANALYZE
SELECT departamento, AVG(salario)
FROM empleados
GROUP BY departamento;

Conceptos que pueden aparecer:

  • Sequential Scan: recorre secuencialmente la fuente.
  • Filter: aplica una condición como WHERE.
  • Projection: conserva las columnas solicitadas.
  • Aggregate: calcula SUM, AVG, COUNT u otra métrica.

No confundas EXPLAIN con la ejecución. El primero describe el plan; el segundo permite estudiar el comportamiento efectivo.

Índices y trade-offs

Un índice mantiene una estructura auxiliar para localizar valores de una columna. Puede acelerar lecturas frecuentes por fecha, ID o categoría, pero agrega espacio y hace más costosas las escrituras.

No existe una regla de “indexar todo”. La decisión depende de la proporción entre lecturas y escrituras, la selectividad y el patrón real de consulta. En análisis intensivo de lectura, los índices pueden ayudar; en archivos Parquet, la selección de columnas y el particionado suelen ser más relevantes.

6.7 Procesamiento de archivos grandes

Pandas normalmente construye un DataFrame en memoria. Si el archivo pesa 10 GB y la máquina tiene 8 GB disponibles, el proceso puede fallar antes de comenzar el análisis.

DuckDB cambia el flujo mediante:

  • Lectura directa: consulta el CSV o Parquet sin importar primero todo.
  • Procesamiento vectorizado: opera sobre bloques de valores.
  • Paralelismo: usa varios núcleos cuando la consulta lo permite.
  • Out-of-core: procesa datos que superan la RAM usando el almacenamiento de manera eficiente.
import duckdb

con = duckdb.connect()

resumen = con.sql('''
    SELECT estacion, AVG(temperatura) AS media_temperatura
    FROM 'data/clima.parquet'
    WHERE fecha >= '2020-01-01'
    GROUP BY estacion
    ORDER BY estacion
''').df()

print(resumen)

El error conceptual más común es cargar primero con Pandas y después pasar el DataFrame a DuckDB. Eso ya consumió la RAM y anula el beneficio de consultar directamente el archivo.

Out-of-core

M6

Capacidad de procesar datos que superan la memoria disponible, leyendo y liberando bloques de información de forma controlada.

Ej: Consultar un Parquet de 10 GB y devolver solo la media mensual por estación en lugar de materializar las 10 GB en Pandas.

#duckdb #big-data #rendimiento

6.8 DuckDB frente a Pandas

La decisión no debe reducirse a “qué herramienta es mejor”. Debe responder qué etapa del pipeline estás resolviendo.

ConceptoDescripcion
DuckDB Filtrar, agregar y unir grandes volúmenes localmente. Aprovecha SQL, paralelismo y lectura directa de archivos.
Pandas Transformar, inspeccionar y preparar resultados manejables. Excelente ecosistema para análisis estadístico y visualización.
Polars Procesar localmente con expresiones y buen uso de CPU. Alternativa cuando quieres una API tipo DataFrame con ejecución lazy.
EscenarioElección razonablePor qué
Dataset de 100 filas y transformación puntual de stringsPandasEl coste de iniciar un motor SQL no aporta demasiado.
Parquet de 10 GB y dos columnas necesariasDuckDBLee solo lo que la consulta necesita.
JOIN complejo entre tablas grandesDuckDBEl motor está optimizado para operaciones relacionales.
Visualización final con SeabornPandasEl ecosistema gráfico trabaja naturalmente con DataFrames.
Dataset que excede la RAMDuckDBPuede procesar por bloques y devolver un resumen reducido.

La sinergia habitual es:

DuckDB: leer + filtrar + unir + agregar
                ↓
Pandas: analizar estadísticamente + visualizar + preparar ML

6.9 Caso práctico: análisis meteorológico

El caso de la pre-entrega utiliza un dataset temporal de clima en CSV o Parquet. La pregunta guía es:

¿Cómo cambian las temperaturas por estación y mes, qué valores faltan y qué extremos históricos debemos investigar?

Contrato mínimo del dataset

Antes de consultar, define el significado de las columnas:

ColumnaTipo esperadoSignificado
fechaFechaDía u hora de observación.
estacionTextoEstación meteorológica.
temperaturaNuméricoTemperatura observada.
humedadNuméricoHumedad relativa, si existe.
precipitacionNuméricoLluvia registrada, si existe.

Script reproducible

from pathlib import Path
import duckdb

DATA = Path('data/clima.csv')
OUTPUT = Path('outputs')
OUTPUT.mkdir(exist_ok=True)

con = duckdb.connect(database=':memory:')

calidad = con.execute('''
    SELECT
        COUNT(*) AS total_filas,
        COUNT(*) - COUNT(temperatura) AS nulos_temperatura,
        COUNT(DISTINCT estacion) AS estaciones,
        MIN(fecha) AS fecha_inicio,
        MAX(fecha) AS fecha_fin
    FROM read_csv(?, header = true, auto_detect = true)
''', [str(DATA)]).fetchdf()

mensual = con.execute('''
    SELECT
        estacion,
        DATE_TRUNC('month', CAST(fecha AS DATE)) AS mes,
        AVG(temperatura) AS temperatura_media,
        MIN(temperatura) AS temperatura_minima,
        MAX(temperatura) AS temperatura_maxima,
        COUNT(*) AS observaciones
    FROM read_csv(?, header = true, auto_detect = true)
    WHERE temperatura IS NOT NULL
    GROUP BY estacion, mes
    ORDER BY estacion, mes
''', [str(DATA)]).fetchdf()

extremos = con.execute('''
    SELECT estacion, fecha, temperatura
    FROM read_csv(?, header = true, auto_detect = true)
    WHERE temperatura IS NOT NULL
    ORDER BY temperatura DESC
    LIMIT 10
''', [str(DATA)]).fetchdf()

calidad.to_csv(OUTPUT / 'calidad.csv', index=False)
mensual.to_parquet(OUTPUT / 'resumen_mensual.parquet', index=False)
extremos.to_csv(OUTPUT / 'extremos.csv', index=False)
con.close()

El script produce tres salidas separadas por responsabilidad: diagnóstico de calidad, resumen mensual y extremos. Eso facilita validar cada resultado y evita una tabla final ambigua.

Práctica guiada: analiza el clima sin mirar la solución intermedio
  1. Consigue un CSV o Parquet público de NOAA u otra fuente meteorológica y documenta su URL y fecha de descarga.
  2. Adapta los nombres de columnas sin cambiar el contrato de análisis: fecha, estación y temperatura.
  3. Ejecuta un diagnóstico de filas, estaciones, rango temporal y nulos.
  4. Calcula promedios mensuales, máximos históricos por estación y cantidad de observaciones.
  5. Compara cuánto tarda una consulta directa de DuckDB contra cargar todo el archivo en Pandas.
  6. Registra tamaño del archivo, tiempo de ejecución, versión de Python, DuckDB y hallazgos.

Tres consultas mínimas de la pre-entrega

-- 1. Promedios y cantidad de observaciones
SELECT estacion, AVG(temperatura) AS promedio, COUNT(*) AS observaciones
FROM 'data/clima.parquet'
WHERE temperatura IS NOT NULL
GROUP BY estacion;

-- 2. Máximo histórico por estación
SELECT estacion, MAX(temperatura) AS maximo_historico
FROM 'data/clima.parquet'
GROUP BY estacion;

-- 3. Filtrado complejo
SELECT fecha, estacion, temperatura, humedad
FROM 'data/clima.parquet'
WHERE temperatura BETWEEN 30 AND 45
  AND humedad < 40
  AND fecha >= '2020-01-01'
ORDER BY temperatura DESC
LIMIT 50;

6.10 Errores frecuentes y mejores prácticas

Traer todas las columnas
✗ SELECT * FROM archivo_grande
✓ Seleccionar únicamente las columnas necesarias para la pregunta.
Cargar Pandas antes de DuckDB
✗ df = pd.read_csv(...) y luego duckdb.sql sobre df
✓ Consultar el CSV o Parquet directamente con DuckDB.
Filtrar una agregación con WHERE
✗ WHERE SUM(ventas) > 1000
✓ GROUP BY tienda HAVING SUM(ventas) > 1000.
Ignorar NULL
✗ Tratar NULL como si fuera cero
✓ Medir faltantes con COUNT, decidir una estrategia y documentarla.
Ordenar siempre
✗ ORDER BY en cada consulta exploratoria
✓ Ordenar solo cuando la pregunta necesita ranking o LIMIT.
Interpolar valores en SQL
✗ f"SELECT * FROM t WHERE id={id}"
✓ Usar parámetros de DuckDB para evitar errores e inyección.
Subir artefactos binarios
✗ Commitear clima.duckdb o datasets gigantes
✓ Versionar código, instrucciones y muestras pequeñas; generar salidas reproducibles.

6.11 Preguntas de autoevaluación

💡 Observa si el filtro ocurre antes o después de agrupar.

💡 Relaciona columnas no utilizadas con disco, memoria y red.

💡 No significa que el archivo desaparezca de la RAM.

Q: ¿Cuándo elegirías DuckDB en lugar de Pandas?

Cuando necesito consultar o agregar archivos grandes, leer solo algunas columnas, ejecutar joins SQL o trabajar con un volumen que no cabe cómodamente en memoria. Después puedo entregar el resultado reducido a Pandas.

🪤 Responder que DuckDB siempre reemplaza a Pandas.

Q: ¿Cómo demostrarías que una consulta es reproducible?

Versionaría el script, declararía dependencias, documentaría la fuente y fecha de corte, fijaría parámetros relevantes, registraría tiempos y produciría salidas a partir del mismo comando.

🪤 Mostrar únicamente una captura de pantalla del resultado.

6.12 Checklist de cierre

  • El script funciona desde un entorno limpio.
  • duckdb aparece en las dependencias.
  • El archivo se consulta directamente como CSV o Parquet.
  • Hay al menos tres consultas analíticas distintas.
  • Se midieron nulos, volumen y rango temporal.
  • Se documentaron tiempos, resultados y limitaciones.
  • No se subieron secretos, bases binarias ni datasets innecesariamente pesados.
  • El README explica instalación, ejecución, estructura y hallazgos.

6.13 Recursos para seguir estudiando

Libro: Designing Data-Intensive Applications, de Martin Kleppmann: almacenamiento y procesamiento de datos.
Video: Buscar una demostración de DuckDB con Parquet y EXPLAIN para observar el plan en ejecución.

Tu espacio de estudio

Notas privadas

Se guardan solo en este navegador y no se publican.

Sin cambios