OxideDB

-- Registrar tabla Delta Lake desde S3
REGISTER DELTA 's3://bucket/warehouse/sales' AS sales;
-- REGISTER DELTA TABLE sales (Version: 5, Files: 12, 2.3M rows)

-- Consultar normalmente con SQL estándar
SELECT fecha, SUM(monto) as total
FROM sales
WHERE fecha >= '2025-01-01'
GROUP BY fecha;

-- Ver historial de versiones (Time Travel)
DELTA HISTORY sales;
-- version | timestamp           | operation | user
-- 5       | 2025-11-25 21:54:14 | WRITE     | etl_user
-- 4       | 2025-11-24 15:30:00 | WRITE     | etl_user
-- ...

🚀 Ventaja Clave: Zero-Downtime Discovery

Con REFRESH TABLES, puedes agregar nuevas tablas a tu data warehouse (usando oxide-etl u otras herramientas) y sincronizarlas en OxideDB instantáneamente sin reiniciar el servidor. Perfecto para pipelines ETL en producción.

-- OxideDB v1.6.0: Lakehouse Analytics con Delta Lake + S3
-- DataFusion 50.3, Delta Lake 0.29, Arrow 56

-- 1. Registrar tabla Delta Lake desde S3
REGISTER DELTA 's3://bucket/warehouse/sales_delta' AS sales;
-- REGISTER DELTA TABLE sales (Version: 5, Files: 12, 2.3M rows)

-- 2. Sincronizar tablas desde AWS Glue Catalog
REFRESH TABLES;
-- REFRESH TABLES completed. Discovered 47 tables in 2.85s

-- 3. CTE con Window Functions sobre Delta Lake
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', fecha) as mes,
        categoria,
        SUM(monto) as ventas_mes,
        COUNT(DISTINCT cliente_id) as clientes,
        ROW_NUMBER() OVER (PARTITION BY categoria
                          ORDER BY SUM(monto) DESC) as ranking
    FROM sales  -- Delta Lake table con ACID transactions
    WHERE fecha >= '2025-01-01'
    GROUP BY DATE_TRUNC('month', fecha), categoria
)
SELECT mes, categoria, ventas_mes, clientes, ranking
FROM monthly_sales
WHERE ranking <= 10;

-- Ver historial de versiones (Time Travel)
DELTA HISTORY sales;
-- Performance: Sub-segundo sobre millones de filas ⚡
-- Features: Delta Lake ✅ Time Travel ✅ CTEs ✅ Window Functions ✅