DuckDB overview
Overview
DuckDB is an analytical relational database management system designed for fast data processing and analytical workloads. Unlike traditional client-server database systems, DuckDB is an embedded database engine that runs inside the host process and does not require a separate database server. DuckDB is optimized for analytical queries and can process data from local files and external data sources.
For example, an Airflow workflow can use DuckDB to:
-
read data from external storage;
-
run SQL queries directly on Parquet-formatted data files and others;
-
transform and aggregate datasets;
-
write analytical results;
-
check whether required data is available;
-
combine data from local files and external sources.
The DuckDB service in ADO provides a managed DuckDB runtime for executing analytical SQL queries. The service is delivered as a command-line runtime and can be used by Airflow DAGs and other ADO components. It is not a long-running daemon and does not provide a network endpoint or a web interface.
The DuckDB service provides:
-
a pre-installed DuckDB CLI on selected ADO hosts;
-
centralized configuration managed through ADCM;
-
integration with Airflow through the DuckDB provider;
-
a common DuckDB runtime and configuration.
Architecture
DuckDB uses an in-process architecture. When a query is submitted to the DuckDB CLI, the DuckDB engine parses, binds, plans, optimizes, and executes the query within the same process.
A query processing goes through the following stages:
-
Parser — converts an SQL query into an internal representation. At this stage, DuckDB does not resolve tables, columns, or data types.
-
Binder — resolves tables and columns using the catalog, resolves data types, and extracts aggregate and window functions.
-
Logical planner — creates the logical query tree from the bound query.
-
Optimizer — transforms the logical query tree into an optimized query plan. Among other optimizations, DuckDB can simplify expressions, push filters down the query tree, reorder joins, and eliminate common subexpressions.
-
Column binding resolver — resolves column references to positions in the data structures used during query execution.
-
Physical plan generator — converts the optimized logical plan into a tree of physical operators.
-
Execution engine — executes the physical operators and produces the query result. DuckDB uses vectorized execution and processes data in chunks.
DuckDB can work with both persistent .duckdb database files and data accessed directly from external sources. Its extension mechanism provides support for additional data formats, storage systems, and integrations.
Components
The DuckDB service includes the DuckDB CLI component which provides the DuckDB command-line runtime used to execute SQL queries.
ADCM installs the DuckDB CLI on the selected hosts by the /usr/bin/duckdb path. The executable is an ADO wrapper that applies the service-level configuration before starting the DuckDB CLI.
The DuckDB service uses the following configuration files:
-
/etc/duckdb/conf/duckdb-env.sh — defines the DuckDB environment and paths to the DuckDB installation, extensions, and external runtimes.
-
/etc/duckdb/conf/duckdb-conf.sql — contains the global DuckDB SQL configuration.
Workflow
DuckDB queries in ADO are executed locally on a host where the DuckDB CLI is installed.
The general workflow is as follows:
-
A user or an app submits a DuckDB SQL query.
-
The DuckDB CLI wrapper starts the DuckDB runtime.
-
The wrapper applies the environment variables and global SQL configuration.
-
DuckDB parses and binds the query.
-
DuckDB creates a logical query plan and applies query optimizations.
-
The optimized logical plan is converted into a physical execution plan.
-
DuckDB executes the physical operators using its vectorized execution engine.
-
The query result is returned to the caller.
For Airflow DAGs, the DuckDB provider uses the same /usr/bin/duckdb executable. The provider executes SQL through the DuckDB CLI and exposes the execution through the standard Airflow interface.
The provider includes:
-
DuckDBHook— for executing SQL through the DuckDB runtime; -
DuckDBOperator— for executing inline SQL or SQL files in DAG tasks; -
DuckDBSqlSensor— for waiting until a DuckDB query returns a valid result.
The Airflow DuckDB connection can define a database path, an alternative DuckDB executable, and additional CLI parameters. Operator-level settings can override connection-level values where applicable.
Integration with ADO services
The primary integration is with Airflow. Airflow workers can use the DuckDB service to execute analytical SQL as part of DAG workflows. This makes it possible to include DuckDB operations in data processing pipelines without invoking the CLI manually through a BashOperator.
DuckDB can also access data stored outside its local database files through supported extensions. Depending on the installed extensions and their configuration, DuckDB workloads can work with external object storage and data platforms, including S3-compatible storage, Arenadata Hyperwave (ADH), HDFS, Ozone, and Hive Metastore.
Configuration
The DuckDB service is configured through ADCM. The configuration includes the DuckDB environment, global SQL settings, resource limits, storage and I/O options, extension settings, SQL compatibility options, and execution and optimizer settings.
For the complete list of available parameters, see DuckDB configuration parameters.
DuckDB provider for Airflow
After the DuckDB component is installed on a host, the DuckDB CLI is available at:
$ /usr/bin/duckdbThe CLI can be used directly on the host or by ADO components that integrate with DuckDB.
In Airflow, use the DuckDB provider to execute SQL in DAGs. The provider uses the DuckDB CLI as the execution backend and applies the same ADO-managed DuckDB environment and global configuration.
For more information, see DuckDB provider.