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.
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,HAVINGy 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
| Concepto | Descripcion |
|---|---|
| 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
M6Lenguaje 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.
SELECT y FROM: qué necesito y de dónde sale
Toda consulta empieza por dos preguntas:
- ¿Qué columnas necesito?
- ¿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ía | Operadores | Uso |
|---|---|---|
| Comparación | =, !=, <>, >, <, >=, <= | Comparar valores y rangos. |
| Lógica | AND, OR, NOT | Combinar condiciones. |
| Pertenencia | IN (...) | Comparar contra una lista. |
| Ausencia | IS NULL, IS NOT NULL | Detectar datos faltantes. |
| Patrón | LIKE | Buscar coincidencias de texto. |
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)yMAX(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:
WHEREfiltra filas antes de la agrupación.HAVINGfiltra 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
| Pregunta | Consulta 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
M6Exploració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.
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().
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
| Concepto | Descripcion |
|---|---|
| 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
- Selecciona columnas explícitas; evita
SELECT *en el pipeline final. - Filtra lo antes posible con
WHERE. - Usa
LIMITpara exploraciones y muestras. - Agrega en SQL antes de transportar el resultado a Python.
- Ordena solo cuando la pregunta lo necesita.
- Evita funciones sobre columnas filtradas cuando impidan aprovechar el almacenamiento o un índice.
- 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,COUNTu 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
M6Capacidad 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.
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.
| Concepto | Descripcion |
|---|---|
| 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. |
| Escenario | Elección razonable | Por qué |
|---|---|---|
| Dataset de 100 filas y transformación puntual de strings | Pandas | El coste de iniciar un motor SQL no aporta demasiado. |
| Parquet de 10 GB y dos columnas necesarias | DuckDB | Lee solo lo que la consulta necesita. |
| JOIN complejo entre tablas grandes | DuckDB | El motor está optimizado para operaciones relacionales. |
| Visualización final con Seaborn | Pandas | El ecosistema gráfico trabaja naturalmente con DataFrames. |
| Dataset que excede la RAM | DuckDB | Puede 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:
| Columna | Tipo esperado | Significado |
|---|---|---|
fecha | Fecha | Día u hora de observación. |
estacion | Texto | Estación meteorológica. |
temperatura | Numérico | Temperatura observada. |
humedad | Numérico | Humedad relativa, si existe. |
precipitacion | Numérico | Lluvia 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.
- Consigue un CSV o Parquet público de NOAA u otra fuente meteorológica y documenta su URL y fecha de descarga.
- Adapta los nombres de columnas sin cambiar el contrato de análisis: fecha, estación y temperatura.
- Ejecuta un diagnóstico de filas, estaciones, rango temporal y nulos.
- Calcula promedios mensuales, máximos históricos por estación y cantidad de observaciones.
- Compara cuánto tarda una consulta directa de DuckDB contra cargar todo el archivo en Pandas.
- 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
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.
-
duckdbaparece 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
Tu espacio de estudio
Notas privadas
Se guardan solo en este navegador y no se publican.