How to Use DuckDB for Fast Data Analysis and SQL Queries

10 minutes ago

7 min read

Modern data analysis is dependent on efficient tools that can deal with large datasets, fetch sophisticated SQL query results, and use programming languages including Python. In most cases, traditional database systems have many restrictions regarding additional infrastructure, server configuration, and data relocation operations until the point when analysts are able to start working with their data.

DuckDB is known as an open-source in-process OLAP database management system that gives the opportunity to create almost instant queries and conveniently analyze large datasets. It can be used by programmers, data scientists, analyst that need to work with structured datasets using SQL while avoiding process with creating an additional separate database server.

DuckDB has high effectiveness in working with CSV, Parquet, and JSON files allowing performing switching aggregation, joins of several datasets, and conducting analytical tasks locally. It has a columnar execution engine, vectorized query processing and parallel execution function that makes it possible to deal with analyzing millions or even billions of records of information depending on hardware and data processing complexity.

In the spatial fields, DuckDB can also be integrated with spatial extensions, enabling geographic dataset processing, place-based requests, etc.

What is DuckDB?

DuckDB is a database designed mainly for OLAP purposes. Unlike the usual DBMS, DuckDB is implemented in the application or the development framework.

It does not require the user to set up an independent database server to carry out SQL-based analysis.

DuckDB has many features, some of which include:

In-process execution: DuckDB has been implemented within applications such as Python, R, command-line applications, etc.
Columnar, vectorized execution: Data is processed in batches instead of as individual rows in many analytical tasks.
Parallel query execution: Several CPU threads are utilized for analytical workloads.
Direct file querying: Data is read directly from the supported file formats such as CSV, Parquet, and JSON without the necessity for the import process.
ACID compliance: DuckDB transactions are considered to be transactional DBMS operations.
SQL compliance: All functions available in SQL, including filtering, joins, aggregation, window functions, and CTEs, are provided.
Extensibility: The functionality can be extended using extensions.

Key Aspects of DuckDB for Data Analytics

DuckDB has various database functionalities that allow it to excel in analytical workloads.

Columnar Storage and Vectorized Query Execution

In DuckDB, a column-oriented execution model is utilized to enable efficient analytical query processing. Unlike a row-by-row approach, it utilizes vectorized execution, which means computations are performed on batches of data, referred to as vectors.

This enables improved CPU usage as:

The overhead of function calls is minimized.
There is enhanced CPU cache utilization.
Efficient operations on batches of data take place.
Vectorized instructions enhance the processing of multiple values at once.
Redundant processing of data while querying is avoided.

Columnar processing is especially advantageous given that most queries only select a few columns from tables with hundreds of columns.

Parallel Query Processing

DuckDB allows queries to be executed concurrently by making use of many CPU threads. Data analytics operations like aggregating, scanning, and joining can exploit concurrent execution.

Users can choose the number of threads used:

SET threads = 8;

To check the current configuration:

SELECT current_setting('threads');

The optimal thread count depends on CPU resources, memory availability, storage performance, and query characteristics.

How to Install DuckDB

DuckDB supports multiple installation methods, including Python packages, command-line tools, and other programming language integrations.

Install DuckDB Using Python

If Python is already installed, use pip to install DuckDB.

Open Command Prompt, Terminal, or a Python environment and execute:

pip install duckdb

To verify the installation:

import duckdbprint(duckdb.__version__)

If the package installs successfully, DuckDB is ready to use.

Install DuckDB Using Conda

Users working with Anaconda or Miniconda can install DuckDB using:

conda install python-duckdb -c conda-forge

Install the DuckDB Command-Line Interface

DuckDB also provides a standalone command-line interface (CLI), which allows users to execute SQL queries without writing Python code.

After installing the CLI, launch it using:

duckdb

To create or open a database file:

duckdb analytics. duckdb

This opens the database named analytics.duckdb or creates it if it does not already exist.

Refer to the official DuckDB installation documentation for platform-specific installation instructions.

How to Connect to a DuckDB Database

DuckDB supports both in-memory and persistent database connections.

Create an In-Memory Database

An in-memory database stores its database state in memory rather than a persistent database file.

import duckdbcon = duckdb.connect(database=":memory:")result = con.execute("SELECT 100 AS total")print(result.fetchall())

Output:

[(100,)]

This approach is useful for temporary analysis, testing SQL queries, and processing datasets that do not require persistent database storage.

Create a Persistent Database

For projects that require saving tables and database changes, create a persistent DuckDB database.

import duckdbcon = duckdb.connect("geospatial_analysis.duckdb")con.execute("""    CREATE TABLE locations (        location_id INTEGER,        city VARCHAR,        population BIGINT    )""")

The database is saved in the specified file and can be reopened later.

con.close()

To reconnect:

con = duckdb.connect("geospatial_analysis.duckdb")

Persistent databases are useful for repeatable analytical workflows and applications that need to retain structured data between sessions.

How to Import and Query Data in DuckDB

DuckDB provides multiple methods for reading structured datasets. Users can either query source files directly or load their contents into database tables.

Read CSV Files

CSV files are widely used for storing tabular information, including survey results, environmental measurements, and business records.

Consider a file named satellite_data.csv containing the following fields:

image_id
location
acquisition_date
cloud_cover
resolution

To query the file directly:

SELECT *
FROM 'satellite_data.csv';

To filter satellite imagery with less than 10% cloud cover:

SELECT
    image_id,
    location,
    acquisition_date,
    cloud_cover
FROM 'satellite_data.csv'
WHERE cloud_cover < 10;

To load the CSV file into a persistent database table:

CREATE TABLE satellite_images AS
SELECT *
FROM read_csv_auto('satellite_data.csv');

DuckDB automatically detects common CSV data types through its CSV reader.

Query Parquet Files

Apache Parquet is a columnar storage format designed for efficient data storage and analytical processing.

DuckDB can query Parquet files directly:

SELECT *
FROM 'satellite_data.parquet';

To retrieve only selected columns:

SELECT
    image_id,
    acquisition_date,
    cloud_cover
FROM 'satellite_data.parquet'
WHERE cloud_cover < 15;

This is particularly useful when working with large datasets because DuckDB can apply projection and filter pushdown to avoid reading unnecessary columns and data wherever supported.

For multiple Parquet files:

SELECT *
FROM read_parquet('data/*.parquet');

This allows users to analyze multiple files using a single SQL query.

Read JSON Files

DuckDB also supports querying JSON datasets.

SELECT *
FROM 'satellite_metadata.json';

For more explicit JSON processing, users can work with JSON functions and extract values from nested structures.

Example:

SELECT
    json_extract_string(
        metadata,
        '$.sensor'
    ) AS sensor_name
FROM imagery_records;

Here, metadata represents a column containing JSON data, and the JSON path extracts the sensor name.

How to Perform SQL Queries in DuckDB

DuckDB supports standard SQL operations and advanced analytical queries.

Filtering Data Using WHERE

The WHERE clause retrieves records that satisfy specific conditions.

SELECT *
FROM satellite_images
WHERE cloud_cover < 20
AND resolution <= 1;

This query identifies satellite images with less than 20% cloud cover and a spatial resolution of 1 meter or better, assuming resolution is stored in meters.

Aggregating Data Using GROUP BY

Aggregate functions help summarize large datasets.

SELECT
    location,
    COUNT(*) AS total_images,
    AVG(cloud_cover) AS average_cloud_cover,
    MIN(resolution) AS best_resolution
FROM satellite_images
GROUP BY location;

This query calculates the number of images, average cloud cover, and minimum resolution for each location.

Common aggregate functions include:

COUNT() – Counts records.
SUM() – Calculates total values.
AVG() – Calculates averages.
MIN() – Returns the minimum value.
MAX() – Returns the maximum value.
MEDIAN() – Calculates the median value.

Joining Multiple Tables

SQL joins allow users to combine related datasets using common fields.

Consider two tables:

satellite_images: Contains image metadata.
locations: Contains geographic location information.

Example:

SELECT
    s.image_id,
    s.cloud_cover,
    l.city,
    l.country
FROM satellite_images s
INNER JOIN locations l
ON s.location_id = l.location_id;

This query combines satellite imagery records with location details.

DuckDB supports different join operations, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

Using Window Functions

Window functions perform calculations across related rows without collapsing the result into grouped records.

For example, calculate the average cloud cover for each location while retaining individual image records:

SELECT
    image_id,
    location,
    cloud_cover,
    AVG(cloud_cover) OVER (
        PARTITION BY location
    ) AS location_avg_cloud_cover
FROM satellite_images;

Window functions are useful for ranking, trend analysis, running totals, and comparative data analysis.

Using Common Table Expressions (CTEs)

Common Table Expressions make complex SQL queries easier to organize and maintain.

WITH filtered_images AS (
    SELECT *
    FROM satellite_images
    WHERE cloud_cover < 20
)
SELECT
    location,
    COUNT(*) AS image_count,
    AVG(cloud_cover) AS avg_cloud_cover
FROM filtered_images
GROUP BY location
ORDER BY image_count DESC;

The CTE creates a temporary named result called filtered_images, which is referenced by the main query.

Using DuckDB for Geospatial Data Analysis

DuckDB can also support geospatial workflows through its Spatial extension.

The Spatial extension adds spatial data types, functions, and operations that allow users to work with geographic information using SQL.

It is useful for GIS professionals working with location-based datasets, spatial measurements, geographic features, and spatial analysis.

Install and Load the Spatial Extension

Execute the following SQL commands:

INSTALL spatial;
LOAD spatial;

The INSTALL command downloads the extension when required, while the LOAD command makes its functionality available in the current session.

Calculate Geographic Distances

Spatial functions can be used to calculate distances between geographic points.

For example:

SELECT
    ST_Distance(
        ST_Point(-104.9903, 39.7392),
        ST_Point(-97.7431, 30.2672)
    ) AS distance;

This example calculates the planar distance between two points represented by longitude and latitude coordinates. The result is in the coordinate system's units, which are degrees for these geographic coordinates, not meters.

For accurate ground-distance measurements, use an appropriate projected coordinate system or a geodesic distance calculation.

Perform Spatial Intersection Analysis

Spatial intersection operations identify whether two geometries overlap.

SELECT
    ST_Intersects(
        ST_GeomFromText(
            'POLYGON((-105 39, -104 39, -104 40, -105 40, -105 39))'
        ),
        ST_GeomFromText(
            'POINT(-104.5 39.5)'
        )
    ) AS intersects;

This query checks whether a point falls within or intersects a polygon.

Analyze Geographic Datasets

DuckDB's Spatial extension can be used for operations such as:

Spatial intersection and containment.
Geometry transformation.
Spatial measurements.
Geometry construction and manipulation.
Processing supported spatial file formats.
Combining geographic attributes with tabular datasets.

DuckDB is an efficient analytical database, allowing SQL queries to be processed without an extra database server. Its columnar approach, vectorized execution, parallel querying capabilities, and built-in support for analytical formats allow the analysis of big structured datasets.

Combining DuckDB with Python, Pandas, Polars, and Apache Arrow, developers can create versatile data processing systems that allow them to perform SQL operations and utilize available data science tools.

The Spatial extension of DuckDB provides even more possibilities for users from GIS and remote sensing fields, who can now do geographic data processing and spatial analysis using SQL workflows.

Whether it is the analysis of satellite image metadata or working with Parquet datasets, DuckDB stands as a great option for modern data processing.

To learn more about DuckDB and its geospatial capabilities, click here.

For more information or any questions regarding the LizardTech suite of products, please don't hesitate to contact us at:

Email: info@geowgs84.com

USA (HQ): (720) 702–4849

LizardTech.com

(A GeoWGS84 Corp Company)

https://www.lizardtech.com

https://www.geowgs84.com

Visit www.lizardtech.com - The Creators of the MrSID Format