DuckDB on AWS Connection¶

The DuckDB on AWS connection type is used by AwsDuckDBHook to query data in Amazon S3 with DuckDB. See DuckDB on AWS for usage.

Two connections are involved:

A DuckDB on AWS (duckdb_aws) connection which configures DuckDB itself, i.e.: which DuckDB database and with what settings. Its id is what you pass to DuckDBExecuteQueryOperator as conn_id, and its type is also what selects AwsDuckDBHook.
An AWS connection gives the region and endpoint, and also the credentials (keys, a role_arn or a profile_name) when credential_strategy is config. The DuckDB connection points at it through aws_conn_id, which defaults to aws_default.

With the default credential_strategy of credential_chain, DuckDB takes credentials from the worker’s environment, such as environment variables or the worker’s role, rather than from the AWS connection. If credential_strategy is not set and the AWS connection has credentials, the hook logs a warning; set it to config to use them, or to credential_chain to silence the warning. See How credentials reach DuckDB.

The duckdb_aws connection can be empty and often will be with DuckDB’s ability to use in-memory databases. Create it, name it whatever you like, and leave every field blank to get an in-memory database. Pass its id to DuckDBExecuteQueryOperator as conn_id; the operator needs the connection to exist, and with no conn_id it uses duckdb_default and the plain DuckDB hook, which has no S3 access. AwsDuckDBHook used on its own defaults to duckdb_aws_default, and opens an in-memory database if that connection does not exist.

Configuring the Connection¶

Database path (optional)

Path to a DuckDB database file, for example /tmp/analytics.duckdb. Leave empty for an in-memory database.

Extra (optional)

A JSON object. Every parameter of AwsDuckDBHook can be set here, and hook_params on the operator overrides it per task. The AWS keys are:

aws_conn_id: the AWS connection the secret’s region and endpoint come from, and its credentials too when credential_strategy is config. Defaults to aws_default. Set it to null to use no Airflow AWS connection. The secret then gets a region and endpoint only from this connection’s own keys, such as region_name and s3_endpoint_url, and config takes credentials from boto3’s default chain. credential_chain takes credentials from the environment either way.
region_name: region for the S3 secret. Defaults to the region of the AWS connection.
credential_strategy: credential_chain (default), config or none. See How credentials reach DuckDB.
credential_chain: the providers DuckDB’s credential chain consults, for example "env;config;sts;instance".
s3_endpoint_url: S3 endpoint override, for an S3-compatible service or a VPC endpoint. Defaults to endpoint_url from the AWS connection extra.
secret_name: name of the DuckDB secret to create. Defaults to airflow_aws.

The DuckDB engine keys, such as memory_limit, threads and extensions, are the same as in the DuckDB connection. One default differs: autoinstall_extensions is true here, so missing extensions, including httpfs and aws, are downloaded from DuckDB’s extension repository. Installing an extension needs a writable HOME; see the warning in DuckDB on AWS.

Examples¶

An in-memory database with explicit resource limits. The region comes from aws_default and the credentials from the worker’s environment, even if aws_default has credentials of its own:

{
  "credential_strategy": "credential_chain",
  "memory_limit": "2GB",
  "threads": 4
}

A database file, with credentials from a specific AWS connection:

export AIRFLOW_CONN_DUCKDB_AWS_DEFAULT='{
    "conn_type": "duckdb_aws",
    "host": "/tmp/analytics.duckdb",
    "extra": {"aws_conn_id": "aws_analytics", "credential_strategy": "config", "region_name": "eu-west-1"}
}'