DuckDB embedded OLAP SQL — blazing-fast columnar analytics directly on CSV, Parquet, and JSON files without any import step.

Overview

DuckDB is the “SQLite of analytics”. It executes SQL with columnar vectorized execution across all CPU cores, directly on CSV, Parquet, and JSON files — no server, no import step, just query. Results from a 100 M-row Parquet file arrive in seconds from a laptop. It is used by data engineers, ML pipelines, and BI tools everywhere that high-speed aggregation over flat files matters.

Unlike SQLite (row-oriented, optimized for OLTP), DuckDB is column-oriented and optimized for analytical queries: GROUP BY, aggregations, window functions, and joins over millions of rows. Both can coexist in the same program — use SQLite for transactional state, DuckDB for analytics.

Query a CSV File Directly

uses duckdb;

var db := OpenMemory();

// DuckDB reads CSV on disk; no IMPORT or COPY needed
var rows := Query(db,
  'SELECT region, SUM(sales) AS total ' +
  'FROM ''sales.csv'' ' +
  'GROUP BY region ORDER BY 2 DESC');

for var r in rows do
  WriteLn(r['region'], '  $', r['total']);

Close(db);

In-Memory Analytics with Window Functions

uses duckdb;

var db := OpenMemory();

// Create an in-memory table from a Parquet file
Execute(db,
  'CREATE TABLE events AS SELECT * FROM read_parquet(''events.parquet'')');

// Window function: running total per user
var rows := Query(db,
  'SELECT user_id, ts, amount, ' +
  'SUM(amount) OVER (PARTITION BY user_id ORDER BY ts) AS running_total ' +
  'FROM events ORDER BY user_id, ts');

for var r in rows do
  WriteLn(r['user_id'], '  ', r['running_total']);

Close(db);

Prepared Statements

uses duckdb;

var db := Open('analytics.db');

// Prepare once, execute many times with $1 $2 positional parameters
var stmt := Prepare(db, 'SELECT * FROM orders WHERE status = $1 AND year = $2');

BindText(stmt, 1, 'shipped');
BindInt(stmt, 2, 2024);
var rows := ExecuteStmt(stmt);

for var r in rows do
  WriteLn(r['order_id'], '  ', r['amount']);

ResetStmt(stmt);
FinalizeStmt(stmt);
Close(db);

Export to Parquet

uses duckdb;

var db := OpenMemory();

// Query across JSON + CSV and export result as Parquet in one call
ExportParquet(db,
  'SELECT a.user_id, a.name, b.total_spend ' +
  'FROM read_json_auto(''users.json'') a ' +
  'JOIN read_csv_auto(''spend.csv'') b ON a.user_id = b.user_id',
  '/out/enriched_users.parquet');

WriteLn('Exported.');
Close(db);

API Reference

Open / Close

Function	Description
Open(path)	Open or create a persistent DuckDB database file; returns a db handle
OpenMemory()	Open an in-memory DuckDB instance; data is lost on Close
Close(db)	Flush and release all resources for a db handle

Execute & Query

Function	Description
Execute(db, sql)	Run a DDL or DML statement with no result set (CREATE, INSERT, UPDATE, DELETE)
Query(db, sql)	Run a SELECT and return a List of row dictionaries (column name → value)
QueryScalar(db, sql)	Run a single-value SELECT and return the result as a Variant
QueryInt(db, sql)	Run a single-value SELECT and return an Integer
QueryFloat(db, sql)	Run a single-value SELECT and return a Float

Prepared Statements

Function	Description
Prepare(db, sql)	Compile a parameterized SQL string into a reusable statement handle
BindInt(stmt, idx, val)	Bind an Integer to positional parameter $idx (1-based)
BindFloat(stmt, idx, val)	Bind a Float to positional parameter $idx
BindText(stmt, idx, val)	Bind a String to positional parameter $idx
BindBool(stmt, idx, val)	Bind a Boolean to positional parameter $idx
BindNull(stmt, idx)	Bind SQL NULL to positional parameter $idx
ExecuteStmt(stmt)	Execute a prepared statement and return the result rows
ResetStmt(stmt)	Reset bindings so the statement can be reused with new parameters
FinalizeStmt(stmt)	Release a prepared statement handle

File Queries

Function	Description
QueryCSV(db, path, sql)	Register a CSV file as a virtual table and run sql against it
QueryParquet(db, path, sql)	Register a Parquet file as a virtual table and run sql
QueryJSON(db, path, sql)	Register a JSON/NDJSON file as a virtual table and run sql
ImportCSV(db, path, table)	Load a CSV file into a named in-memory table
ExportCSV(db, sql, path)	Execute sql and write the result set as a CSV file
ExportParquet(db, sql, path)	Execute sql and write the result set as a Parquet file

Transactions

Function	Description
BeginTransaction(db)	Begin an explicit transaction (auto-commit is on by default)
Commit(db)	Commit the current transaction
Rollback(db)	Rollback the current transaction and discard all changes

Schema Inspection

Function	Description
GetTables(db)	Return a List of table names in the current database
GetColumns(db, table)	Return a List of TColumnInfo (Name, Type, Nullable) for a table
TableExists(db, table)	True if the named table exists in the current database

Configuration

Function	Description
SetThreads(db, n)	Set the number of worker threads used for parallel query execution
SetMemoryLimit(db, bytes)	Cap the maximum memory DuckDB may allocate for a query (e.g. 2*1024*1024*1024 for 2 GB)