o-nikolas commented on code in PR #73301: URL: https://github.com/apache/airflow/pull/73301#discussion_r4088644083
########## providers/amazon/docs/operators/duckdb.rst: ########## @@ -0,0 +1,164 @@ + .. Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + .. http://www.apache.org/licenses/LICENSE-2.0 + + .. Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +============= +DuckDB on AWS +============= + +`DuckDB <https://duckdb.org/>`__ 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 +:doc:`DuckDB provider <apache-airflow-providers-duckdb:index>`. What this provider adds is the AWS +auth. Using an Airflow AWS connection to create the DuckDB secret that grants access to S3. +This means there is no credential wiring needed in the Dag. + +You run :class:`~airflow.providers.duckdb.operators.duckdb.DuckDBExecuteQueryOperator` and point it +at a ``duckdb_aws`` connection when the SQL should reach S3. + +Prerequisite Tasks +------------------ + +.. include:: ../_partials/prerequisite_tasks.rst + +DuckDB support is an optional extra + +.. code-block:: bash + + 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. + +.. _howto/connection:duckdb_aws: + +Run a DuckDB query against Amazon S3 +==================================== + +Create a connection of type **DuckDB on AWS** (``duckdb_aws``) and give its id to +:class:`~airflow.providers.duckdb.operators.duckdb.DuckDBExecuteQueryOperator`. The connection type +selects :class:`~airflow.providers.amazon.aws.hooks.duckdb.AwsDuckDBHook`, which loads the ``httpfs`` +and ``aws`` extensions and creates an S3 secret from the AWS connection, so the task supplies only SQL: + +.. exampleinclude:: /../../amazon/tests/system/amazon/aws/example_duckdb.py + :language: python + :dedent: 4 + :start-after: [START howto_operator_aws_duckdb] + :end-before: [END howto_operator_aws_duckdb] + +The connection type is what selects the hook, and ``duckdb_aws_default`` is the id used when none is given. + +The 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 +:class:`~airflow.providers.amazon.aws.hooks.duckdb.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. + +.. code-block:: python + + hook_params = {"memory_limit": "2GB", "threads": 4} + +``aws_conn_id`` is the exception, and is only settable through ``hook_params``: ``None`` is meaningful +there (it means fall back to the ambient AWS environment), so it is not read from the ``extra``. + +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: + +.. code-block:: python + + @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() Review Comment: I'm not entirely sure I follow here. We're using the `AwsDuckDBHook` instead of the `DbApiHook` because we're reading a parquet from s3, so we need AWS auth here. If you're asking why we don't use the `.run()` hook interface then the only real reasons is that these are split into two commands so the same conn is used so that auth doesn't happen twice (which is 1) a waste and 2) you get broken results because with DuckDB you'll get a second new in-memory db which won't have results from the first run). You could put both statements in one run, but I think that's more of a foot gun to show to people than as it is now. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
