[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Automating Query tasks with Managed Service for Apache Airflow™

# Automating Yandex Query tasks with Yandex Managed Service for Apache Airflow™

{% note warning %}

This tutorial was tested on clusters with the Apache Airflow™ version below 3.0.

{% endnote %}

Managed Service for Apache Airflow™ is a popular tool for automating data operations. Yandex Query supports integration with Managed Service for Apache Airflow™ using the [`apache-airflow-providers-yandex`](https://pypi.org/project/apache-airflow-providers-yandex/) pip package.

To create an infrastructure for automation of Yandex Query tasks with Managed Service for Apache Airflow™, follow these steps:

1. [Get your cloud ready](#before-you-begin).
1. [Create a service account](#create-service-account).
1. [Create a cloud network and subnets](#create-network).
1. [Prepare a bucket in Yandex Object Storage](#bucket).
1. [Configure an egress NAT](#nat-routing).
1. [Create a Managed Service for Apache Airflow™ cluster](#create-airflow-cluster).
1. [Prepare the DAG file and run the graph](#dag).
1. [Check the result](#check-out).

If you no longer need the resources you created, [delete them](#clear-out).

## Get your cloud ready {#before-you-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).

### Required paid resources {#paid-resources}

* Object Storage bucket: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).
* NAT gateway: hourly use of the gateway and its outgoing traffic (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Query: amount of data read when running queries (see [Query pricing](../../query/pricing.md)).
* Managed Service for Apache Airflow™ cluster: computing resources of cluster components (see [Managed Service for Apache Airflow™ pricing](../../managed-airflow/pricing.md)).

## Create a service account {#create-service-account}

[Create](../../iam/operations/sa/create.md) a service account named `airflow-sa` with the `editor` [role](../../iam/roles-reference.md#editor) for the [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) where the Managed Service for Apache Airflow™ cluster will be created:

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a service account.
  1. Navigate to **Identity and Access Management**.
  1. Click **Create service account**.
  1. Enter a name for the service account: `airflow-sa`.
  1. Click ![image](../../_assets/console-icons/plus.svg) **Add role** and select the `editor` role.
  1. Click **Create**.

- Yandex Cloud CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  1. Create a service account named `airflow-sa`:

      ```bash
      yc iam service-account create airflow-sa
      ```

      Result:

      ```
      id: nfersamh4sjq********
      folder_id: b1gc1t4cb638********
      created_at: "2023-09-21T10:36:29.726397755Z"
      name: airflow-sa
      ```

      Save the ID of the `airflow-sa` service account (`id`) and the ID of the folder where you created it (`folder_id`).

  1. Assign the `editor` role for the folder to the service account by specifying the folder and service account IDs you previously saved:

      ```bash
      yc resource-manager folder add-access-binding <folder_ID> \
        --role editor \
        --subject serviceAccount:<service_account_ID>
      ```

- API {#api}

  To create a service account, use the [create](../../iam/api-ref/ServiceAccount/create.md) REST API method for the [ServiceAccount](../../iam/api-ref/ServiceAccount/index.md) resource or the [ServiceAccountService/Create](../../iam/api-ref/grpc/ServiceAccount/create.md) gRPC API call.

  To assign the `editor` role for a folder to a service account, use the [setAccessBindings](../../iam/api-ref/ServiceAccount/setAccessBindings.md) method for the [ServiceAccount](../../iam/api-ref/ServiceAccount/index.md) resource or the [ServiceAccountService/SetAccessBindings](../../iam/api-ref/grpc/ServiceAccount/setAccessBindings.md) gRPC API call.

{% endlist %}

## Create a cloud network and subnets {#create-network}

All resources you create in this tutorial will belong to the same [cloud network](../../vpc/concepts/network.md).

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select a folder.
  1. Navigate to **Virtual Private Cloud**.
  1. Click **Create network**.
  1. In the **Name** field, specify `yq-network`.
  1. In the **Advanced** field, select `Create subnets`.
  1. Click **Create network**.

- Yandex Cloud CLI {#cli}

  1. Create a network named `yq-network`:

      ```bash
      yc vpc network create yq-network
      ```

      Result:

      ```
      id: enptrcle5q3d********
      folder_id: b1g9hv2loamq********
      created_at: "2022-04-04T05:25:03Z"
      name: yq-network
      default_security_group_id: enpbsnnop4ak********
      ```

      For more information about the `yc vpc network create` command, see the [CLI reference](../../cli/cli-ref/vpc/cli-ref/network/create.md).

  1. Create subnets:

      * [Availability zone](../../overview/concepts/geo-scope.md): `ru-central1-a`

        ```bash
        yc vpc subnet create yq-network-ru-central1-a \
          --zone ru-central1-a \
          --network-name yq-network \
          --range 10.1.0.0/16
        ```

        Result:

        ```
        id: b0c3pte4o2kn********
        folder_id: b1g9hv2loamq********
        created_at: "2022-04-04T09:28:08Z"
        name: yq-network-ru-central1-a
        network_id: enptrcle5q3d********
        zone_id: ru-central1-a
        v4_cidr_blocks:
        - 10.1.0.0/16
        ```

      * Availability zone: `ru-central1-b`:

        ```bash
        yc vpc subnet create yq-network-ru-central1-b \
          --zone ru-central1-b \
          --network-name yq-network \
          --range 10.2.0.0/16
        ```

      * Availability zone: `ru-central1-d`:

        ```bash
        yc vpc subnet create yq-network-ru-central1-d \
          --zone ru-central1-d \
          --network-name yq-network \
          --range 10.3.0.0/16
        ```

      For more information about the `yc vpc subnet create` command, see the [CLI reference](../../cli/cli-ref/vpc/cli-ref/subnet/create.md).

- API {#api}

  1. To create a network, use the [create](../../vpc/api-ref/Network/create.md) REST API method for the [Network](../../vpc/api-ref/Network/index.md) resource or the [NetworkService/Create](../../vpc/api-ref/grpc/Network/create.md) gRPC API call.

  1. To create subnets, use the [create](../../vpc/api-ref/Subnet/create.md) REST API method for the [Subnet](../../vpc/api-ref/Subnet/index.md) resource or the [SubnetService/Create](../../vpc/api-ref/grpc/Subnet/create.md) gRPC API call.

{% endlist %}

## Prepare a bucket in Object Storage {#bucket}

### Create a bucket {#create-bucket}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select a folder.
  1. Navigate to **Object Storage**.
  1. Click **Create bucket**.
  1. In the ** Name** field, enter a [unique name](../../storage/concepts/bucket.md#naming) for the bucket, e.g., `airflow-bucket`.
  1. In the **Read objects** and **Read object list** fields, select `For all`.
  1. Click **Create bucket**.

- AWS CLI {#cli}

  If you do not have the AWS CLI yet, [install and configure it](../../storage/tools/aws-cli.md).

  1. Create a bucket with a [unique name](../../storage/concepts/bucket.md#naming):

      ```bash
      aws --endpoint-url https://storage.yandexcloud.net \
        s3 mb s3://<bucket_name>
      ```
      
      Result:
      
      ```
      make_bucket: s3://airflow-bucket
      ```

  1. Enable [public access](../../storage/concepts/acl.md#predefined-acls) to read objects and their list in the bucket you created:

      ```bash
      aws --endpoint-url https://storage.yandexcloud.net \
        s3api put-bucket-acl \
        --bucket <bucket_name> \
        --acl public-read
      ```

- API {#api}

  To create a bucket, use the [create](../../storage/api-ref/Bucket/create.md) REST API method for the [Bucket](../../storage/api-ref/Bucket/index.md) resource, the [BucketService/Create](../../storage/api-ref/grpc/Bucket/create.md) gRPC API call, or the [create](../../storage/s3/api-ref/bucket/create.md) S3 API method.

{% endlist %}

### Set up the bucket ACL {#configure-acl-bucket}

[Edit the ACL](../../storage/operations/buckets/edit-acl.md) of the new bucket to give the `READ` permission to the `airflow-sa` service account.

## Configure an egress NAT {#nat-routing}

### Create a NAT gateway {#create-nat}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select a folder.
  1. Navigate to **Virtual Private Cloud**.
  1. In the left-hand panel, select **Gateways**.
  1. Click **Create gateway**, and in the window that opens:
      1. In the **Name** field, enter `yq-nat`.
      1. In the **Type** field, select `Egress NAT`.
      1. Click **Save**.

- Yandex Cloud CLI {#cli}

  Create a NAT gateway in the default folder:

  ```bash
  yc vpc gateway create \
    --name yq-nat
  ```

  Result:

  ```text
  id: enpkq1sb7hed********
  folder_id: b1g681qpemb4********
  created_at: "2024-05-19T13:20:36Z"
  name: yq-nat
  shared_egress_gateway: {}
  ```

  For more information about the `yc vpc gateway create` command, see the [CLI reference](../../cli/cli-ref/vpc/cli-ref/gateway/create.md).

  Save the NAT gateway `id`. You will need it when creating a route table.

- API {#api}

  To create a NAT gateway, use the [create](../../vpc/api-ref/Gateway/create.md) REST API method for the [Gateway](../../vpc/api-ref/Gateway/index.md) resource or the [GatewayService/Create](../../vpc/api-ref/grpc/Gateway/create.md) gRPC API call.

{% endlist %}

### Create a route table {#create-route}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the left-hand panel, select **Routing tables**.
  1. Click **Create routing table** and specify the route table parameters:
      1. Enter the name: `yq-route-table`.
      1. Select the network: `yq-network`.
      1. Click **Add**.
          * In the **Next hop** field, select `Gateway`.
          * In the **Gateway** field, select the `yq-nat` NAT gateway. The destination prefix will apply automatically.
      1. Click **Add**.
  1. Click **Create routing table**.

- Yandex Cloud CLI {#cli}

  Create a route table with the `yq-nat` NAT gateway as the next hop and the `0.0.0.0/0` destination prefix:

  ```bash
  yc vpc route-table create \
    --name=yq-route-table \
    --network-name=yq-network \
    --route destination=0.0.0.0/0,gateway-id=<NAT_gateway_ID>
  ```

  Result:

  ```text
  id: enp4v8foko6s********
  folder_id: b1g681qpemb4********
  created_at: "2024-05-19T13:22:47Z"
  name: yq-route-table
  network_id: enppoggov6ub********
  static_routes:
    - destination_prefix: 0.0.0.0/0
      gateway_id: enpkq1sb7hed********
  ```

  For more information about the `yc vpc route-table create` command, see the [CLI reference](../../cli/cli-ref/vpc/cli-ref/route-table/create.md).


- API {#api}

  To create a route table, use the [create](../../vpc/api-ref/RouteTable/create.md) REST API method for the [RouteTable](../../vpc/api-ref/RouteTable/index.md) resource or the [RouteTableService/Create](../../vpc/api-ref/grpc/RouteTable/create.md) gRPC API call.

{% endlist %}

### Associate the route table with a subnet {#bind-route}

Associate the route table with a subnet to route the subnet traffic via the NAT gateway:

{% list tabs group=instructions %}

- Management console {#console}

  1. In the left-hand panel, select ![image](../../_assets/console-icons/nodes-right.svg) **Subnets**.
  1. In the `yq-network-ru-central1-a` row, click ![image](../../_assets/console-icons/ellipsis.svg).
  1. Click **Link routing table** and select `yq-route-table`.
  1. Click **Link**.

- Yandex Cloud CLI {#cli}

  Run this command:

  ```bash
  yc vpc subnet update yq-network-ru-central1-a \
    --route-table-name=yq-route-table
  ```

  Result:

  ```text
  id: e9b6n3jj3gh6********
  folder_id: b1g681qpemb4********
  created_at: "2024-05-19T13:24:58Z"
  name: yq-network-ru-central1-a
  network_id: enppoggov6ub********
  zone_id: ru-central1-a
  v4_cidr_blocks:
    - 10.1.0.0/16
  route_table_id: enp4v8foko6s********
  dhcp_options: {}
  ```

  For more information about the `yc vpc subnet update` command, see the [CLI reference](../../cli/cli-ref/vpc/cli-ref/subnet/update.md).

- API {#api}

  To associate a route table with a subnet, use the [update](../../vpc/api-ref/Subnet/update.md) REST API method for the [Subnet](../../vpc/api-ref/Subnet/index.md) resource or the [SubnetService/Update](../../vpc/api-ref/grpc/Subnet/update.md) gRPC API call.

  {% note warning %}
  
  The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `updateMask` parameter as a single comma-separated string.
  
  {% endnote %}

{% endlist %}

## Create a Managed Service for Apache Airflow™ cluster {#create-airflow-cluster}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder where you want to [create a cluster](../../managed-airflow/operations/cluster-create.md).
  1. Navigate to **Managed Service for&nbsp;Apache&nbsp;Airflow™**.
  1. Click **Create a cluster**.
  1. Under **Basic parameters**, enter a name for your cluster. The name must be unique within the folder.
  1. Under **Access settings**, set a password for the admin user. The password must be not less than 8 characters long and contain at least:

        * One uppercase letter
        * One lowercase letter
        * One number
        * One special character

     {% note info %}

     Save the password locally or memorize it. Managed Service for Apache Airflow™ does not show passwords after they are created.

     {% endnote %}

  1. Under **Network settings**, select:

      * [Availability zone](../../overview/concepts/geo-scope.md): `ru-central1-a`
      * Cloud network: `yq-network`
      * Subnet: `yq-network-ru-central1-a`
      * [Security group](../../vpc/concepts/security-groups.md): default

        Security group settings do not affect access to the [Apache Airflow™ web interface](../../managed-airflow/operations/af-interfaces.md#web-gui).

  1. Under **Dependencies**, specify the pip package name and version restriction:

      ```text
      apache-airflow-providers-yandex>=3.10
      ```

  1. Under **DAG file storage**, select the bucket you created earlier.

  1. Click **Create**.

{% endlist %}

## Prepare the DAG file and run the graph {#dag}

As an example, we use a [directed acyclic graph (DAG)](../../managed-airflow/concepts/index.md#about-the-service) with two vertices:
   * `yq_operator`: Runs a simple query to Yandex Query.
   * `output_operator`: Outputs the result of the `yq_operator` vertex execution.

To prepare a DAG:

1. Create a local file named `yq_dag.py` and copy the following script into it:

    {% cut "yq_dag.py" %}

    ```python
    import datetime

    from airflow.models.dag import DAG
    from airflow.providers.yandex.operators.yq import YQExecuteQueryOperator
    from airflow.operators.python_operator import PythonOperator

    with DAG(
        dag_id="yq_hello_world_operator",
        schedule="@hourly",
        start_date=datetime.datetime.now(),
    ) as dag:

        yq_operator = YQExecuteQueryOperator(
            task_id="yq_operator", sql="SELECT 'Hello, world!'"
        )

        def print_context(ds=None, **kwargs):
            ti = kwargs["ti"]
            print(ti.xcom_pull(task_ids="yq_operator"))

        output_operator = PythonOperator(
            task_id="output_operator", provide_context=True, python_callable=print_context
        )

        yq_operator >> output_operator

    if __name__ == "__main__":
        dag.test()
    ```

    {% endcut %}

1. Upload the DAG file into the Managed Service for Apache Airflow™ cluster. Create a `files/dags` folder in the `airflow-bucket` and upload the `yq_dag.py` file to it.
1. [Open the Apache Airflow™ web interface](../../managed-airflow/operations/af-interfaces.md#web-gui).
1. Make sure the new `yq_hello_world_operator` DAG has appeared in the **DAGs** section.

    {% note info %}

    It may take a few minutes to load a DAG file from the bucket.

    {% endnote %}

1. To run a DAG, first click ![image](../../_assets/managed-airflow/trigger-dag.png =18x) in the line with its name, and then click **Trigger DAG**.

## Check the result {#check-out}

1. In the **DAGs** section, open the `yq_hello_world_operator` graph.
1. Go to the **Grid** section.
1. Select the **yq_operator** task.
1. Go to the **XCom** section.
1. Make sure the `return_value` line has the `'rows': [['Hello, world!']]` element. This indicates the query was successful.

## Delete the resources you created {#clear-out}

To delete the infrastructure and stop paying for the resources you created:

1. [Delete](../../storage/operations/buckets/delete.md) the Object Storage bucket.
1. [Disassociate and delete](../../vpc/operations/delete-route-table.md) the route table.
1. [Delete the NAT gateway](../../vpc/operations/delete-nat-gateway.md#delete-nat-gateway).
1. [Delete](../../managed-airflow/operations/cluster-delete.md) the Apache Airflow™ cluster.
1. Delete the [subnets](../../vpc/operations/subnet-delete.md), [network](../../vpc/operations/network-delete.md), and the [service account](../../iam/operations/sa/delete.md), if required.