DuckDB on AWS

DuckDB is an in-process analytical database. It can read and write data in Amazon S3 directly, which makes it an affordable way to run transforms that do not justify the scale of a distributed cluster.

DuckDB is not an AWS service, so the generic hook, operator and duckdb connection type live in the DuckDB provider. What this provider adds is the AWS auth: the hook creates the DuckDB secret that grants access to S3, so there is no credential wiring needed in the Dag.

You run DuckDBExecuteQueryOperator and point it at a duckdb_aws connection when the SQL should reach S3.

Prerequisite Tasks

To use these operators, you must do a few things:

DuckDB support is an optional extra

pip install 'apache-airflow-providers-amazon[duckdb]'

Note

S3 access needs DuckDB’s httpfs and aws extensions. This provider installs them if they are missing by default. If your workers should not download anything at runtime, set autoinstall_extensions=False and make the extensions available yourself, either in an extension_directory or baked into your image.

Warning

Because this provider installs those two extensions by default, it needs the environment to have a usable HOME. DuckDB installs extensions under the user’s home directory and fails with IO Error: Can't find the home directory when there is none, which is the default state of an AWS Lambda function. Configuring a writable home directory is the deployment administrator’s job; see the DuckDB connection docs for the details and for the alternative that needs no home directory at all. If you run tasks on the Lambda executor, set HOME in the function’s image or environment variables.

Run a DuckDB query against Amazon S3

Two connections are involved: a DuckDB on AWS connection that says which DuckDB database to open and with what settings, and an AWS connection that gives the region and endpoint. The AWS connection’s credentials are used only when credential_strategy is config; by default DuckDB takes credentials from the worker’s environment, see How credentials reach DuckDB. The DuckDB on AWS connection can be empty, but the operator needs it to exist.

Given that connection, the hook loads the httpfs and aws extensions and creates the S3 secret, so the task supplies only SQL:

tests/system/amazon/aws/example_duckdb.py[source]

summarize_sales = DuckDBExecuteQueryOperator(
    task_id="summarize_sales",
    conn_id=DUCKDB_AWS_CONN_ID,
    sql=f"""
        COPY (
            SELECT category, SUM(price * quantity) AS revenue
            FROM read_csv('s3://{s3_bucket}/sales/*.csv')
            GROUP BY category
            ORDER BY revenue DESC
        ) TO 's3://{s3_bucket}/summary/revenue.parquet' (FORMAT PARQUET)
    """,
)

Pass the id explicitly. With no conn_id the operator uses duckdb_default and the vendor-neutral hook, which has no S3 access.

The DuckDB connection carries the database and the engine settings; leave its Database path empty for an in-memory database, which suits a task that reads from S3 and writes back to it. Everything AwsDuckDBHook accepts can be set in the connection extra, and hook_params on the operator overrides it per task. Set memory_limit and threads explicitly: DuckDB otherwise sizes itself from the resources it detects on the host.

hook_params = {"memory_limit": "2GB", "threads": 4}

Note

aws_conn_id resolves on whether the key is present, not on its value, because None is a valid choice for it rather than the absence of one. Set it to null in the extra (or None in hook_params) to use no Airflow AWS connection at all. The secret then gets a region and endpoint only from the DuckDB connection’s own keys, such as region_name and s3_endpoint_url, and config takes credentials from boto3’s default chain, such as environment variables or an instance role. credential_chain takes credentials from the environment either way.

Using the hook directly

For work that is not a single statement (e.g. chaining several queries against one database, or returning results to Python) you may use the hook directly. Opening one connection for several statements also avoids paying connection setup and extension loading more than once per task:

@task
def compare_regions():
    hook = AwsDuckDBHook(aws_conn_id="aws_default")
    with hook.get_conn() as conn:
        conn.execute("CREATE TABLE sales AS SELECT * FROM read_parquet('s3://my-bucket/sales.parquet')")
        return conn.execute("SELECT region, SUM(revenue) FROM sales GROUP BY region").fetchall()

How credentials reach DuckDB

DuckDB does not use boto3. The hook therefore issues a CREATE SECRET statement that tells DuckDB how to authenticate, and the credential_strategy parameter selects how:

credential_chain (default)

Issues CREATE SECRET (TYPE s3, PROVIDER credential_chain), which resolves credentials through the AWS SDK inside DuckDB’s aws extension. Nothing secret is written into SQL text, and the secret is created with REFRESH 'auto': when S3 rejects expired credentials, DuckDB resolves the chain again and retries, so a long query does not fail partway through when a set of temporary credentials expires.

Before DuckDB 1.5.6, only opening a file to read refreshes the credentials. Listing files with a glob, reading further into a file that is already open, and writing with COPY ... TO can still fail once the credentials expire.

From DuckDB 1.4 onward the chain is resolved when the secret is created rather than when S3 is first read. If nothing can be resolved, the hook logs a warning and opens the connection anyway, so a query that does not use S3 still runs; one that does will fail on its own. A config secret that cannot be created raises instead, since explicit credentials failing is a misconfiguration.

config

Resolves credentials through AwsBaseHook and writes them into the secret explicitly. This also works with a container-assigned role, because boto3 resolves that on the Airflow side, but the credentials are frozen when the connection is opened rather than refreshed. Needed when the Airflow connection carries static keys that the AWS SDK running inside DuckDB cannot see. The credentials become part of the SQL statement, so prefer credential_chain where it works.

none

Creates no secret. Use for a DuckDB database that never touches S3.

DuckDBExecuteQueryOperator(
    task_id="query",
    conn_id="duckdb_aws_default",
    sql="SELECT COUNT(*) FROM read_parquet('s3://my-bucket/data.parquet')",
    hook_params={"aws_conn_id": "aws_static_keys", "credential_strategy": "config"},
)

If credential_strategy is not set and the AWS connection has credentials (keys, a role_arn or a profile_name), the hook logs a warning, because credential_chain does not use them. Set it to config to use them, or to credential_chain to silence the warning.

Non-default S3 endpoints

Set s3_endpoint_url, or endpoint_url in the AWS connection extra, to reach an S3-compatible service or an S3 interface VPC endpoint. The hook translates it into the secret’s ENDPOINT, URL_STYLE and USE_SSL clauses, selecting path-style addressing because virtual-host-style bucket names generally do not resolve against such endpoints.

Note

The S3 secret is the only channel through which Airflow configuration reaches DuckDB. A DuckDB session with no secret ignores the Airflow connection entirely — including its region and endpoint falling back to whatever DuckDB’s own settings and environment provide.

DuckDB’s httpfs extension reads credentials from the standard AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY / AWS_SESSION_TOKEN environment variables, so it can authenticate where those are set. What its HTTP client does not implement is the ECS container credential provider, so a container-assigned task role (often how a managed Airflow worker receives credentials) is invisible to it and S3 access fails with an opaque HTTP 403. Both credential_chain and config handle that case; the difference is whether DuckDB or Airflow does the resolving, and therefore whether the credentials refresh.

Reference

Was this entry helpful?