Skip to content

SQL on Kaggle with DuckDB

This use case shows how to use DuckDB inside an AerolVM sandbox to perform high-performance SQL analytics on Kaggle datasets. DuckDB is an in-process SQL OLAP database management system that is perfect for analyzing large CSV or Parquet files.

The DuckDB Advantage

Section titled “The DuckDB Advantage”
  • No Installation: Run complex SQL queries without setting up a full database server.
  • Direct CSV/Parquet Querying: DuckDB can query files directly from disk (or even HTTP) with high efficiency.
  • Sandboxed Analytics: Each analyst or project gets their own isolated compute environment.

Copy-paste TypeScript script

Section titled “Copy-paste TypeScript script”

Code URL: https://github.com/aerol-ai/aerolvm-examples/tree/main/ml-data-engineering/duckdb-dataset-explorer

This script downloads a dataset and starts a simple HTTP proxy that allows you to send SQL queries to the sandbox.

import { MicroVM } from "@aerol-ai/aerolvm-sdk";


const apiUrl = process.env.SB_API_URL ?? "http://127.0.0.1:21212";
const patToken = process.env.SB_PAT_TOKEN;
const kaggleUsername = process.env.KAGGLE_USERNAME;
const kaggleKey = process.env.KAGGLE_KEY || process.env.KAGGLE_API_TOKEN;
const kaggleDataset = process.env.KAGGLE_DATASET ?? "dgomonov/new-york-city-airbnb-open-data";






const sqlProxyScript = `
import os
import json
import duckdb
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
from kaggle.api.kaggle_api_extended import KaggleApi


# 1. Download Dataset
api = KaggleApi()
api.authenticate()
api.dataset_download_files(os.environ.get('KAGGLE_DATASET'), path='./data', unzip=True)


# 2. Init DuckDB
db = duckdb.connect(':memory:')
csv_file = [f for f in os.listdir('./data') if f.endswith('.csv')][0]
db.execute(f"CREATE VIEW data AS SELECT * FROM read_csv_auto('./data/{csv_file}')")


# 3. Simple SQL HTTP Proxy
class Handler(BaseHTTPRequestHandler):
    def do_POST(self):
        content_length = int(self.headers['Content-Length'])
        query = self.rfile.read(content_length).decode('utf-8')
        try:
            result = db.execute(query).df().to_json(orient='records')
            self.send_response(200)
            self.send_header('Content-Type', 'application/json')
            self.end_headers()
            self.wfile.write(result.encode())
        except Exception as e:
            self.send_response(500)
            self.end_headers()
            self.wfile.write(str(e).encode())


print("DuckDB Proxy listening on port 8080...")
ThreadingHTTPServer(('0.0.0.0', 8080), Handler).serve_forever()
`;


async function main() {
  if (!patToken || !kaggleUsername || !kaggleKey) {
    throw new Error("Set SB_PAT_TOKEN, KAGGLE_USERNAME, and KAGGLE_KEY (or KAGGLE_API_TOKEN).");
  }
  console.log("Initializing AerolVM client...");
  const client = new MicroVM({ apiUrl, patToken });
  console.log("Creating DuckDB sandbox (0.5 CPU, 2GB RAM)...");
  const sandbox = await client.create({
    image: "python:3.11-bookworm",
    cpu: 0.5,
    memoryMB: 2048,
    env: { KAGGLE_USERNAME: kaggleUsername, KAGGLE_KEY: kaggleKey, KAGGLE_DATASET: kaggleDataset }
  });
  console.log(`Sandbox created successfully! ID: ${sandbox.id}`);


  console.log("Installing DuckDB and Kaggle dependencies...");
  const installRes = await sandbox.exec("pip install duckdb kaggle pandas");
  console.log(`Dependencies installed (exit code: ${installRes.exitCode}, duration: ${installRes.durationMS}ms)`);


  console.log("Uploading DuckDB SQL Proxy script...");
  await sandbox.uploadFile("/workspace/proxy.py", sqlProxyScript);
  console.log("Script uploaded to /workspace/proxy.py");


  console.log("Starting DuckDB SQL Proxy session...");
  const session = await sandbox.createSession({
    name: "duckdb-proxy",
    command: "python3 /workspace/proxy.py",
  });
  console.log(`Session started! ID: ${session.id}`);


  console.log("Exposing port 8080...");
  const exposure = await sandbox.exposePort(8080);
  console.log(`Port exposed!`);


  console.log(`\n✅ DuckDB SQL Proxy ready at: ${exposure.url}`);
  console.log(`Try running: curl -X POST -d "SELECT * FROM data LIMIT 5" ${exposure.url}`);
}


main().catch(console.error);
Section titled “Related docs”