DuckDB guía

DuckDB viene preinstalado en JupyterHub y en los kernels de Python del entorno.

import duckdb

# Verificar versión
print(duckdb.__version__)

Para instalación local:

pip install duckdb
# o
brew install duckdb  # macOS
apt install duckdb   # Linux

import duckdb

# En memoria (temporal)
conn = duckdb.connect(':memory:')

# Con archivo de base de datos local
conn = duckdb.connect('analisis.duckdb')

# Verificar conexión
result = conn.execute("SELECT 42 AS valor").fetchall()
print(result)  # [(42,)]

import duckdb

conn = duckdb.connect(':memory:')

# Tabla desde Parquet
df = conn.execute("""
    SELECT * FROM read_parquet('datos.parquet')
    LIMIT 100
""").df()

# Configurar credenciales S3
conn.execute("""
    CREATE SECRET (TYPE S3,
        KEY_ID '<TU_ACCESS_KEY>',
        SECRET '<TU_SECRET_KEY>',
        ENDPOINT '<tu-url>',
        USE_SSL true
    )
""")

# Leer tabla desde Ceph
datos = conn.execute("""
    SELECT * FROM read_parquet('s3://data-analytics/ventas/2026/03/*.parquet')
    WHERE fecha >= '2026-03-01'
    LIMIT 1000
""").df()

# Un único CSV
df = conn.execute("""
    SELECT * FROM read_csv('archivo.csv')
""").df()

# Varios CSVs con patrón glob
df = conn.execute("""
    SELECT * FROM read_csv('datos_*.csv')
""").df()

# CSV con opciones
df = conn.execute("""
    SELECT * FROM read_csv('datos.csv',
        delim=';',
        header=true,
        nullstr='NA'
    )
""").df()

# Crear tabla
conn.execute("""
    CREATE TABLE ventas AS
    SELECT * FROM read_parquet('ventas.parquet')
""")

# Insertar datos
conn.execute("""
    INSERT INTO ventas VALUES (1, '2026-03-14', 100.50, 'ES')
""")

# Actualizar
conn.execute("""
    UPDATE ventas
    SET monto = monto * 1.10
    WHERE pais = 'ES'
""")

# Consulta con agregaciones
resultado = conn.execute("""
    SELECT
        pais,
        DATE_TRUNC('month', fecha) AS mes,
        COUNT(*) AS num_transacciones,
        SUM(monto) AS total,
        AVG(monto) AS promedio
    FROM ventas
    WHERE fecha >= '2026-01-01'
    GROUP BY pais, mes
    ORDER BY total DESC
""").df()

resultado = conn.execute("""
    SELECT
        v.pais,
        v.monto,
        ROW_NUMBER() OVER (PARTITION BY v.pais ORDER BY v.monto DESC) AS ranking,
        SUM(v.monto) OVER (PARTITION BY v.pais) AS total_pais
    FROM ventas v
    WHERE v.fecha >= '2026-01-01'
    ORDER BY v.pais, ranking
""").df()

resultado = conn.execute("""
    WITH mes_actual AS (
        SELECT pais, SUM(monto) AS total_marzo
        FROM ventas
        WHERE DATE_TRUNC('month', fecha) = '2026-03-01'
        GROUP BY pais
    ),
    mes_anterior AS (
        SELECT pais, SUM(monto) AS total_febrero
        FROM ventas
        WHERE DATE_TRUNC('month', fecha) = '2026-02-01'
        GROUP BY pais
    )
    SELECT
        ma.pais,
        ma.total_marzo,
        mp.total_febrero,
        ROUND((ma.total_marzo - mp.total_febrero) / mp.total_febrero * 100, 2) AS crecimiento_pct
    FROM mes_actual ma
    LEFT JOIN mes_anterior mp ON ma.pais = mp.pais
    ORDER BY crecimiento_pct DESC
""").df()

# Concatenar
SELECT CONCAT(pais, '-', TO_CHAR(monto, '99,999')) AS codigo

# Substring
SELECT SUBSTRING(email FROM 1 FOR 10)

# Upper/Lower
SELECT UPPER(nombre) AS nombre_mayus, LOWER(email) AS email_min

# Replace
SELECT REPLACE(descripcion, 'viejo', 'nuevo')

# Split
SELECT UNNEST(STRING_SPLIT(tags, ',')) AS etiqueta

resultado = conn.execute("""
    SELECT
        pais,
        COUNT(*) AS total,
        LIST(monto) AS montos_lista,
        ARRAY_AGG(DISTINCT categoria) AS categorias,
        HISTOGRAM(monto) AS distribucion_montos
    FROM ventas
    GROUP BY pais
""").df()

import duckdb
import pandas as pd

# De Pandas a DuckDB
df_pandas = pd.read_csv('datos.csv')
tabla = duckdb.from_df(df_pandas)

# Consultar desde DataFrame
resultado = conn.execute("""
    SELECT * FROM df_pandas WHERE edad > 30
""").df()

# De DuckDB a Pandas
resultado_df = conn.execute("""
    SELECT * FROM ventas LIMIT 1000
""").df()

import duckdb
import matplotlib.pyplot as plt

# Query y convertir a Pandas para plotear
df = conn.execute("""
    SELECT
        DATE_TRUNC('day', fecha) AS dia,
        SUM(monto) AS total_diario
    FROM ventas
    WHERE fecha >= '2026-03-01'
    GROUP BY dia
    ORDER BY dia
""").df()

df.plot(x='dia', y='total_diario', kind='line', figsize=(12, 6))
plt.title('Tendencia de ventas diarias')
plt.show()

# DuckDB accede directamente a Ceph sin descargar archivos locales
conn = duckdb.connect(':memory:')

# Registrar secret S3
conn.execute("""
    CREATE SECRET s3_ceph (
        TYPE S3,
        KEY_ID 'TU_ACCESS_KEY',
        SECRET 'TU_SECRET_KEY',
        ENDPOINT '<tu-url>',
        USE_SSL true
    )
""")

# Leer y procesar directamente desde S3
datos_año = conn.execute("""
    SELECT
        DATE_TRUNC('month', fecha) AS mes,
        COUNT(*) AS transacciones,
        SUM(monto) AS total
    FROM read_parquet('s3://data-analytics/ventas/2026/**/*.parquet')
    GROUP BY mes
    ORDER BY mes DESC
""").df()

DuckDB detecta particiones Parquet automáticamente:

# Si los archivos están en s3://bucket/tabla/año=2026/mes=03/
conn.execute("""
    SELECT
        año, mes, COUNT(*) as registros
    FROM read_parquet('s3://data-analytics/tabla/**/*.parquet')
    GROUP BY año, mes
    ORDER BY año DESC, mes DESC
""")

conn.execute("""
    COPY (
        SELECT * FROM ventas
        WHERE fecha >= '2026-03-01'
    ) TO 'resultados.parquet' (FORMAT PARQUET)
""")

conn.execute("""
    COPY (
        SELECT pais, mes, total
        FROM ventas_resumen
        ORDER BY mes DESC
    ) TO 'reporte.csv' (FORMAT CSV, HEADER true, DELIMITER ',')
""")

conn.execute("""
    CREATE SECRET s3_ceph (
        TYPE S3,
        KEY_ID 'TU_ACCESS_KEY',
        SECRET 'TU_SECRET_KEY',
        ENDPOINT '<tu-url>',
        USE_SSL true
    )
""")

conn.execute("""
    COPY (
        SELECT * FROM resultados_finales
    ) TO 's3://data-analytics/exports/mi_analisis.parquet'
""")

# Ver plan de ejecución
conn.execute("EXPLAIN SELECT ... FROM ...").show()

# Ver estadísticas de tabla
conn.execute("SELECT COUNT(*), COUNT(DISTINCT campo) FROM tabla").show()

# Profiling de query
result = conn.execute("SELECT * FROM tabla LIMIT 10").profile()