Fast Local Data Analysis with Python and DuckDB#

Understanding DuckDB#

DuckDB is an embedded, in-process, analytical database management system (DBMS). Unlike client-server databases (like PostgreSQL, MySQL, or SQL Server) that run as separate processes and require network communication, DuckDB runs within the application’s process. This architecture eliminates network latency and serialization costs, significantly speeding up data access and query execution for local operations.

Key characteristics of DuckDB:

• Embedded: It runs directly within the application process, making it easy to deploy and use without external dependencies or server setup.
• Analytical (OLAP-focused): Designed for online analytical processing workloads, which involve complex queries over large volumes of data (aggregations, joins, scans). This contrasts with Online Transaction Processing (OLTP) databases optimized for frequent small transactions (inserts, updates, deletes).
• Columnar Storage: Stores data in columns rather than rows. This is highly efficient for analytical queries that often read specific columns across many rows, as it minimizes I/O and allows for better data compression and vectorized processing.
• Vectorized Execution: Processes data in batches (vectors) using efficient CPU instructions, leading to high throughput.
• SQL Interface: Provides a familiar SQL interface for querying and transforming data, leveraging decades of database optimization research.
• Handles Larger-Than-Memory Data: While it leverages memory extensively, DuckDB can spill data to disk when datasets exceed RAM, allowing analysis of datasets that wouldn’t fit entirely in memory using tools like Pandas.
• Directly Queries Data Files: DuckDB can query various data formats (Parquet, CSV, JSON, etc.) directly from disk without requiring explicit loading into its internal format beforehand.

The Synergy: Python and DuckDB#

Python is a cornerstone of data analysis, thanks to its extensive libraries like Pandas, NumPy, and Polars. While these libraries are powerful for in-memory operations, they can face limitations with very large datasets due to memory constraints and the efficiency of certain operations (e.g., complex joins, multi-column aggregations) compared to optimized database engines.

Combining Python with DuckDB leverages the strengths of both:

• Python’s Data Ecosystem: Use Python for scripting, data loading, pre-processing, visualization, and integrating with other libraries.
• DuckDB’s Query Engine: Offload data querying, filtering, sorting, joining, and aggregation tasks to DuckDB’s highly optimized SQL engine.

This combination allows analysts to perform complex data transformations on potentially large local datasets using familiar SQL syntax, benefiting from DuckDB’s performance advantages, while remaining within the flexible Python environment.

Getting Started: Installing and Connecting#

Setting up DuckDB with Python is straightforward.

1. Installation: Use pip to install the duckdb Python package.

   1
   pip install duckdb
   

2. Establishing a Connection: Connect to a database. DuckDB can work entirely in-memory or persist data to a file.

   1
   import duckdb
   2
   
   3
   # Connect to an in-memory database
   4
   # Data is lost when the connection is closed
   5
   con_in_memory = duckdb.connect(database=':memory:', read_only=False)
   6
   
   7
   # Connect to a persistent database file
   8
   # Creates the file if it doesn't exist
   9
   # Data is saved to 'my_local_data.duckdb'
   10
   con_persistent = duckdb.connect(database='my_local_data.duckdb', read_only=False)
   11
   
   12
   # Close connections when done
   13
   # con_in_memory.close()
   14
   # con_persistent.close()
   

   Connecting to :memory: is useful for temporary analysis or when querying files directly. Connecting to a .duckdb file allows for persistent storage and analysis of larger datasets over multiple sessions.

3. Running Queries: Execute SQL queries using the connection object.

   1
   # Using the in-memory connection
   2
   con_in_memory.execute("CREATE TABLE items (id INTEGER, name VARCHAR)").df()
   3
   con_in_memory.execute("INSERT INTO items VALUES (1, 'Apple'), (2, 'Banana')").df()
   4
   result_df = con_in_memory.execute("SELECT * FROM items").df()
   5
   print(result_df)
   6
   
   7
   # Using the persistent connection
   8
   con_persistent.execute("CREATE TABLE IF NOT EXISTS products (sku VARCHAR, description VARCHAR)").df()
   9
   con_persistent.execute("INSERT INTO products VALUES ('SKU001', 'Laptop')").df()
   10
   # Results can be fetched in various formats (list of tuples, Pandas DataFrame, Polars DataFrame)
   11
   result_list = con_persistent.execute("SELECT * FROM products").fetchall()
   12
   print(result_list)
   

   The .df() method is particularly useful as it returns the query results directly as a Pandas DataFrame. DuckDB also supports returning results as Polars DataFrames using .pl(), which can offer further performance benefits for certain workflows.

Core Techniques for Fast Local Data Analysis#

Several techniques highlight DuckDB’s power for local analysis:

1
import duckdb
2

3
con = duckdb.connect(database=':memory:', read_only=False)
4

5
# Query a local CSV file
6
# Note the single quotes around the file path in the SQL query
7
query_csv = """
8
SELECT
9
    column1,
10
    COUNT(*) as row_count
11
FROM 'path/to/your/large_file.csv'
12
WHERE column2 > 100
13
GROUP BY column1
14
ORDER BY row_count DESC;
15
"""
16
result_from_csv = con.execute(query_csv).df()
17
print(result_from_csv)
18

19
# Query a local Parquet file
20
query_parquet = """
21
SELECT
22
    product_id,
23
    SUM(quantity * price) as total_sales
24
FROM 'path/to/your/sales_data.parquet'
25
WHERE sale_date >= '2023-01-01'
26
GROUP BY product_id
27
ORDER BY total_sales DESC
28
LIMIT 10;
29
"""
30
result_from_parquet = con.execute(query_parquet).df()
31
print(result_from_parquet)

1
import duckdb
2
import pandas as pd
3
import numpy as np
4

5
# Create a sample large Pandas DataFrame
6
data = {
7
    'category': np.random.choice(['A', 'B', 'C', 'D'], size=1_000_000),
8
    'value': np.random.rand(1_000_000) * 100
9
}
10
large_df = pd.DataFrame(data)
11

12
con = duckdb.connect(database=':memory:', read_only=False)
13

14
# Query the Pandas DataFrame directly using SQL
15
query_on_df = """
16
SELECT
17
    category,
18
    AVG(value) as average_value,
19
    COUNT(*) as count
20
FROM large_df  -- Refer to the DataFrame variable name directly
21
WHERE value > 50
22
GROUP BY category
23
ORDER BY average_value DESC;
24
"""
25

26
result_from_df = con.execute(query_on_df).df()
27
print(result_from_df)
28

29
con.close()

1
# Example of a complex SQL query via DuckDB
2
# Assume you have two large Pandas DataFrames: 'sales_df' and 'customers_df'
3

4
query_complex_join_aggregate = """
5
SELECT
6
    c.region,
7
    AVG(s.amount) as average_purchase_amount,
8
    COUNT(DISTINCT s.customer_id) as unique_customers
9
FROM sales_df s
10
JOIN customers_df c ON s.customer_id = c.customer_id
11
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
12
GROUP BY c.region
13
HAVING COUNT(s.sale_id) > 1000 -- Only include regions with more than 1000 sales
14
ORDER BY average_purchase_amount DESC;
15
"""
16

17
# Assuming 'sales_df' and 'customers_df' are defined as Pandas DataFrames
18
# results = con.execute(query_complex_join_aggregate).df()
19
# print(results)

Real-World Example: Analyzing Website Logs Locally#

Consider a scenario involving the analysis of large website access log files stored locally in Parquet format. These files might contain billions of rows, exceeding the memory of a typical laptop. A common task is to find the most popular pages and the volume of traffic from different countries.

Traditionally, this would require loading data into a data warehouse or using distributed computing frameworks, adding complexity. With Python and DuckDB, this analysis can be performed directly on the local machine.

Task: Analyze website logs (website_logs.parquet) to find the top 10 most visited pages and count requests per country.

Log File Structure (simplified):

Python and DuckDB Solution:

1
import duckdb
2
import os
3

4
# Assume website_logs.parquet is in the current directory or specify full path
5
log_file_path = 'website_logs.parquet'
6

7
# Create a dummy large Parquet file for demonstration if it doesn't exist
8
# In a real scenario, this file would already exist.
9
if not os.path.exists(log_file_path):
10
    print(f"Creating dummy Parquet file: {log_file_path}")
11
    import pandas as pd
12
    import numpy as np
13
    from datetime import datetime, timedelta
14

15
    num_rows = 10_000_000 # Simulate a large file
16
    start_date = datetime(2023, 1, 1)
17

18
    data = {
19
        'timestamp': [start_date + timedelta(seconds=i) for i in range(num_rows)],
20
        'page_url': ['/page_' + str(np.random.randint(1, 1000)) for _ in range(num_rows)],
21
        'user_id': np.random.randint(1, 1_000_001, size=num_rows),
22
        'country_code': np.random.choice(['US', 'CA', 'GB', 'DE', 'JP', 'CN', 'BR', 'IN'], size=num_rows),
23
        'status_code': np.random.choice([200, 404, 500], p=[0.9, 0.08, 0.02], size=num_rows)
24
    }
25
    dummy_df = pd.DataFrame(data)
26
    dummy_df.to_parquet(log_file_path, index=False)
27
    print("Dummy file created.")
28

29
print(f"Analyzing log file: {log_file_path}")
30

31
# Connect to DuckDB (in-memory for this analysis)
32
con = duckdb.connect(database=':memory:', read_only=False)
33

34
# Query 1: Find the top 10 most visited pages
35
query_top_pages = f"""
36
SELECT
37
    page_url,
38
    COUNT(*) as visit_count
39
FROM '{log_file_path}'
40
WHERE status_code = 200 -- Consider only successful requests
41
GROUP BY page_url
42
ORDER BY visit_count DESC
43
LIMIT 10;
44
"""
45

46
print("\nExecuting query for top pages...")
47
top_pages_df = con.execute(query_top_pages).df()
48
print("\nTop 10 Most Visited Pages (Successful Requests):")
49
print(top_pages_df)
50

51
# Query 2: Count requests per country
52
query_requests_per_country = f"""
53
SELECT
54
    country_code,
55
    COUNT(*) as total_requests
56
FROM '{log_file_path}'
57
GROUP BY country_code
58
ORDER BY total_requests DESC;
59
"""
60

61
print("\nExecuting query for requests per country...")
62
requests_per_country_df = con.execute(query_requests_per_country).df()
63
print("\nTotal Requests per Country:")
64
print(requests_per_country_df)
65

66
# Close the connection
67
con.close()
68
print("\nAnalysis complete. DuckDB connection closed.")

This example demonstrates how DuckDB can directly query a large Parquet file on disk, perform complex aggregations and filtering using SQL, and return the results as Pandas DataFrames for easy display or further processing within Python. This is achieved without loading the entire dataset into RAM, making it feasible on standard machines.

Performance Insights#

DuckDB’s performance stems from its design principles tailored for analytical workloads:

• Columnar Storage: Reading only necessary columns drastically reduces I/O and memory usage compared to row-based systems for wide tables.
• Vectorized Processing: DuckDB processes data in batches, leveraging modern CPU architectures efficiently.
• Optimized Query Engine: Years of database research contribute to sophisticated query planning and execution strategies.
• Reduced Overhead: Being embedded eliminates network overhead, serialization, and inter-process communication latency inherent in client-server architectures.
• Efficient File Readers: DuckDB has highly optimized readers for formats like Parquet, allowing it to push down operations (like filtering) directly to the file scan, avoiding reading unnecessary data.

While Pandas is excellent for many tasks, operations like large joins or group-bys on large datasets can sometimes be bottlenecks due to single-threaded execution (in older versions or specific operations) or memory limitations. DuckDB, specifically designed for these analytical patterns, often provides superior performance for such operations, particularly on larger datasets or complex queries. Polars also offers strong performance with columnar processing, and DuckDB can complement or integrate with Polars workflows effectively.

Key Takeaways#

• DuckDB is an embedded analytical database ideal for fast local data analysis within Python.
• It runs within the Python process, eliminating network latency and simplifying setup.
• DuckDB’s columnar storage and vectorized execution engine provide significant performance advantages for analytical queries (aggregations, joins, filters) on large datasets.
• Data can be queried using familiar SQL syntax.
• DuckDB can directly query data files (CSV, Parquet, JSON) on disk without full loading, enabling analysis of datasets larger than RAM.
• It can query existing Pandas or Polars DataFrames directly in memory without copying data.
• The Python API allows easy integration, fetching results as DataFrames or other Python structures.
• This combination is powerful for handling medium to large datasets locally, providing a robust alternative to in-memory-only solutions or requiring external database servers.
• It’s particularly effective for complex SQL transformations on datasets that push the limits of tools optimized primarily for in-memory processing.