View a markdown version of this page

Using DuckDB with Amazon MWAA - Amazon Managed Workflows for Apache Airflow
VersionPrerequisitesHow DuckDB connects in Amazon MWAAUsing DuckDB with Apache Airflow v2.11 through v3.2Customize DuckDB in your environmentTroubleshooting DuckDB tasksNext stepsRelated resources

Using DuckDB with Amazon MWAA

Amazon Managed Workflows for Apache Airflow (Amazon MWAA) environments that use Apache Airflow v3.3.1 and later include native DuckDB support. With DuckDB, the tasks in your directed acyclic graphs (DAGs) can run SQL without installing packages, creating connections, or managing credentials. DuckDB is an in-process SQL engine that runs inside an Apache Airflow task on your environment's workers, so you don't need a separate database server or cluster. Use DuckDB for checks, summaries, and transformations on data that your pipelines produce, such as CSV, JSON, and Parquet files in Amazon S3.

This topic describes how DuckDB works in an Amazon MWAA environment. The following code examples show how to write DAGs that run DuckDB queries in memory, and that read and write data in Amazon S3. Each code example includes its own prerequisites, permissions, and cleanup steps.

Topics

Version

Native DuckDB support is available in Amazon MWAA environments that use Apache Airflow v3.3.1 and later. Earlier Apache Airflow versions don't include the DuckDB provider. To use DuckDB with an earlier version, see Using DuckDB with Apache Airflow v2.11 through v3.2. You can use the code examples on this page with Apache Airflow v3.3.1 in Python 3.12.

If your Apache Airflow v3.3.1 environment was created before DuckDB support was available, update your environment to apply the latest Amazon MWAA image. Don't change any environment settings. The update can take some time to complete.

Prerequisites

To use the code examples for DuckDB, you need an Amazon MWAA environment using Apache Airflow v3.3.1 or later. Each example lists any additional prerequisites, such as Amazon S3 permissions.

How DuckDB connects in Amazon MWAA

Amazon MWAA environments that use Apache Airflow v3.3.1 or later include DuckDB by default, so you don't need to add packages to your requirements.txt file. The current Apache Airflow v3.3.1 image includes DuckDB version 2.0.0, version 0.2.0 of the Apache Airflow DuckDB provider, and version 9.38.0 of the Apache Airflow Amazon provider (apache-airflow-providers-amazon). The Amazon provider adds the duckdb_aws connection type that the duckdb_default connection uses. This connection type requires version 9.38.0 or later of the Amazon provider. To check the DuckDB version that your tasks use, run SELECT version(); in a DuckDB task.

When a DuckDB task doesn't name a connection, the DuckDB query operator, DuckDBExecuteQueryOperator, uses the duckdb_default connection. Amazon MWAA creates this connection for you when you create or update your environment. With this connection, DuckDB does the following:

  • Reads and writes Amazon S3 data as your environment's execution role, the same IAM role that your other tasks use. You don't need to add access keys to Apache Airflow or to your SQL.

  • Uses the Amazon S3 support that's included in the Amazon MWAA image, so your environment doesn't need internet access to download it. This applies only to the default DuckDB version. If you install a different DuckDB version, DuckDB downloads its Amazon S3 support the first time a task uses it, even if your tasks use the duckdb_default connection. For more information, see Customize DuckDB in your environment.

  • Runs in memory, so each task starts with an empty database. To share data between tasks, write it to Amazon S3.

The duckdb_default connection appears in the Apache Airflow UI under Admin, Connections. You can edit or delete it.

Amazon MWAA doesn't overwrite an existing duckdb_default connection. If you delete the connection, Amazon MWAA creates it again the next time you update your environment. For more information about connections, see Managing connections to Apache Airflow.

Using DuckDB with Apache Airflow v2.11 through v3.2

Amazon MWAA environments that use Apache Airflow v3.2 or earlier don't include DuckDB, and Amazon MWAA doesn't create the duckdb_default connection for them. You can add DuckDB to environments that use Apache Airflow v2.11 through v3.2. The Apache Airflow DuckDB provider requires Apache Airflow v2.11 or later, so you can't use it with Apache Airflow v2.10 or earlier. To use DuckDB in an environment that uses Apache Airflow v2.11 through v3.2, do the following:

  1. Add apache-airflow-providers-duckdb and duckdb to your requirements.txt file. Choose versions that support your Apache Airflow version. For more information about requirements.txt, see Installing Python dependencies.

  2. To read and write Amazon S3 data, set up Amazon S3 access in your SQL before your queries. The following statements load the DuckDB extensions for Amazon S3 and use your environment's execution role for credentials.

    INSTALL httpfs;
LOAD httpfs;
INSTALL aws;
LOAD aws;
CREATE SECRET (TYPE s3, PROVIDER credential_chain);

DuckDB downloads these extensions from extensions.duckdb.org the first time a task uses them. If your environment doesn't have internet access, provide the extensions yourself, as described in Customize DuckDB in your environment.

Customize DuckDB in your environment

You can change how DuckDB works in your environment in the following ways:

  • Use a different AWS identity for some tasks – Create a connection with the duckdb_aws connection type, and pass its ID to the operator as conn_id. In the connection's extra field, set aws_conn_id to an AWS connection, such as one that assumes a role in another account.

  • Change the default connection – Edit duckdb_default in the Apache Airflow UI. For example, set DuckDB options such as memory_limit or threads in the connection's extra field.

  • Use a different DuckDB or provider version – Add the version that you want to your requirements.txt file. If you change the version of the Amazon provider, apache-airflow-providers-amazon, use version 9.38.0 or later. Earlier versions don't include the duckdb_aws connection type that the duckdb_default connection uses. If your environment doesn't have internet access, also provide the matching DuckDB extensions. DuckDB looks for extensions in a folder for each DuckDB version and platform, ~/.duckdb/extensions/version/platform/. For example, for DuckDB 1.5.6, use a startup script to copy httpfs.duckdb_extension and aws.duckdb_extension from Amazon S3 to ~/.duckdb/extensions/v1.5.6/linux_amd64/. For more information about requirements.txt, see Installing Python dependencies.

Note

The Amazon MWAA image includes DuckDB extensions that match the default DuckDB version. If you install a different version, DuckDB downloads matching extensions from extensions.duckdb.org the first time a task uses them. This download might add time to the first task run.

Troubleshooting DuckDB tasks

If a DuckDB task fails, check the task's log in the Apache Airflow UI for missing permissions, incorrect paths, missing extensions, or memory limits. The following entries describe how to resolve each of these common causes, so that your DAGs can complete successfully.

Resolve HTTP 403 or access denied errors from Amazon S3

Verify that your environment's execution role has the permissions in Permissions for the Amazon S3 code example for the bucket and prefix in your SQL. If the bucket uses AWS KMS keys for encryption, also verify the AWS KMS permissions.

If the role has the required permissions, verify that the task uses a connection with the duckdb_aws connection type. A connection with the duckdb connection type doesn't create a secret for Amazon S3 access, so Amazon S3 denies the request. Check the connection that you pass as conn_id, or the duckdb_default connection if the task doesn't specify one.

The duckdb_aws connection type requires version 9.38.0 or later of the Amazon provider, apache-airflow-providers-amazon. If your requirements.txt file installs an earlier version, remove that requirement or change it to version 9.38.0 or later.

Fix wildcard paths that find no files

Verify that the execution role has s3:ListBucket on the bucket. DuckDB lists the objects in your bucket to expand paths such as orders/*/*.csv.gz. Also verify that the path in your SQL matches where you store your files. If an earlier task writes the files, verify that the earlier task succeeded.

Resolve DuckDB extension installation or loading failures

Check whether you installed a DuckDB version other than the default one, DuckDB 2.0.0, in your requirements.txt file. If your environment doesn't have internet access, DuckDB can't download extensions for that version. Remove the version from your requirements.txt file to use the default version and the extensions in the Amazon MWAA image. Alternatively, provide the extensions yourself as described in Customize DuckDB in your environment.

Reduce memory use for DuckDB tasks

Reduce the data that each query reads, because DuckDB runs on the worker and shares its memory with other tasks. For example, filter on a partition column such as order_date. You can also set memory_limit in the connection's extra field, or use a larger Configuring the Amazon MWAA environment class. For queries over large datasets, consider using Athena instead.

Next steps

  • Run DuckDB tasks when another DAG updates your data, without changing that DAG. To do this, add an outlets asset to the task that writes the data. Then use that asset as the schedule for your DuckDB DAG. For more information, see Asset-aware scheduling in the Apache Airflow documentation.

  • Query JSON and Parquet files in Amazon S3 by using the DuckDB read_json and read_parquet functions in your DAGs.

  • Share results with other teams and BI tools by querying the Parquet files that DuckDB writes to Amazon S3 with Athena.

Related resources