How to Query Data with DuckDB: A Step-by-Step Tutorial

Introduction:

DuckDB is a fast, in-process SQL OLAP database management system ideal for data analytics inside your applications. It supports full SQL querying capabilities and loads data efficiently from various sources. This tutorial will guide you through querying data with DuckDB, starting from setup to running your first SQL queries.

Prerequisites

A system with Python installed (if using DuckDB via Python).
Basic SQL knowledge.
Installed DuckDB or access to DuckDB in your programming environment.

Step 1: Installing DuckDB

If you haven’t installed DuckDB yet, here’s how you can do it:

Python: Run pip install duckdb in your terminal or command prompt.
CLI: Download the DuckDB CLI from the DuckDB official site and run it directly.
Database drivers: DuckDB supports multiple integrations including C++, R, and Node.js.

Step 2: Setting Up a Simple DuckDB Database

For this tutorial, we will use DuckDB in Python, but querying principles are similar across environments.

import duckdb

# Connect to an in-memory DuckDB database
conn = duckdb.connect(database=':memory:')

This creates a temporary DuckDB database that lives in-memory and disappears when the program ends.

Step 3: Creating a Sample Table and Inserting Data

Let’s create a simple table named employees and insert some sample data.

conn.execute('''
CREATE TABLE employees (
  id INTEGER,
  name VARCHAR,
  department VARCHAR,
  salary DOUBLE
);
''')

conn.execute('''
INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 85000),
  (2, 'Bob', 'Marketing', 60000),
  (3, 'Carol', 'Engineering', 95000),
  (4, 'Dave', 'HR', 55000);
''')

Step 4: Querying Data in DuckDB

Now for the core part: querying data with SQL via DuckDB.

Select all records:

result = conn.execute('SELECT * FROM employees').fetchall()
print(result)

Filter by department:

engineering_emps = conn.execute(
    "SELECT name, salary FROM employees WHERE department = 'Engineering'").fetchall()
print(engineering_emps)

Calculate average salary:

avg_salary = conn.execute('SELECT AVG(salary) FROM employees').fetchone()[0]
print(f'Average Salary: {avg_salary}')

Step 5: Querying External Data

DuckDB can directly query CSV, Parquet, or other file formats without importing. For example, querying a CSV:

result = conn.execute(
  "SELECT * FROM read_csv_auto('path/to/file.csv') WHERE some_column > 1000"
).fetchall()
print(result)

Troubleshooting Tips

Ensure DuckDB is installed for your environment (pip show duckdb checks Python package).
Check file paths carefully when querying external files.
DuckDB supports ANSI SQL, so use standard SQL syntax for best compatibility.
Use conn.execute(...).fetchdf() in Python to fetch results as Pandas DataFrame for easier data analysis.

Summary Checklist

Install DuckDB in your preferred environment.
Create or connect to a DuckDB database.
Create tables and insert sample data or query external data sources.
Use standard SQL to query your data.
Convert query results to formats convenient for your use case (e.g., lists, DataFrames).

For more insights on installing DuckDB, see our related guide: How to Install DuckDB.

Querying with DuckDB can seamlessly integrate into data-heavy applications, providing fast and efficient analytics directly where you code. Start exploring DuckDB today for your data processing needs.