[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Tutorials](index.md) > Exporting a database to Yandex Data Processing

# Exporting a database to Yandex Data Processing

# Importing data from Yandex Managed Service for MySQL® to Yandex Data Processing using Sqoop


The [Sqoop](../../data-proc/operations/sqoop-usage.md) utility allows you to import databases to the Yandex Data Processing cluster. Depending on the Yandex Data Processing cluster configuration, you can import data to:

* Yandex Object Storage bucket
* HDFS directory
* Apache Hive
* Apache HBase

To use Sqoop to import the source cluster databases to the Yandex Data Processing target cluster:

1. [Prepare the source cluster](#prepare).
1. [Run the import](#import).
1. [Check the import for correctness](#check).

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

{% note info %}

Sqoop is not supported for Yandex Data Processing clusters version 2.0 and higher. Alternatively, use [Apache Spark™ features](https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html).

{% endnote %}


## Required paid resources {#paid-resources}

* Yandex Data Processing cluster: use of computing resources with a Yandex Data Processing markup, use of network drives, retrieval and storage of logs, volume of outgoing traffic (see [Yandex Data Processing pricing](../../data-proc/pricing.md)).
* Managed Service for MySQL® cluster, which includes computing resources allocated to hosts, storage and backup size (see [Managed Service for MySQL® pricing](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* NAT gateway: hourly use of the gateway and its outgoing traffic (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Object Storage bucket: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).
* VM instance: use of computing resources, storage, public IP address, and OS (see [Compute Cloud pricing](../../compute/pricing.md)).


## Getting started {#before-you-begin}

{% note info %}

Place the clusters and the [VM instance](../../compute/concepts/vm.md) in the same [cloud network](../../vpc/concepts/network.md#network).

{% endnote %}

1. [Create a cloud network](../../vpc/operations/network-create.md).
1. [Create a subnet](../../vpc/operations/subnet-create.md) in the `ru-central1-c` availability zone.
1. [Set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) for the new subnet: this is a prerequisite for the Yandex Data Processing cluster.

You can create other resources manually or using Terraform.

### Manually {#create-manual}

1. [Create a Managed Service for MySQL® cluster](../operations/cluster-create.md) in any suitable [configuration](../concepts/instance-types.md) with the following settings:

    * **DB name**: `db1`
    * **Username**: `user1`

1. To import data to an Object Storage bucket:

    1. [Create a bucket](../../storage/operations/buckets/create.md) with restricted access.
    1. [Create a service account](../../iam/operations/sa/create.md) with the following roles:

        * [dataproc.agent](../../data-proc/security/index.md#dataproc-agent)
        * [dataproc.provisioner](../../data-proc/security/index.md#dataproc-provisioner)
        * [monitoring.viewer](../../monitoring/security/index.md#monitoring-viewer)
        * [storage.viewer](../../storage/security/index.md#storage-viewer)
        * [storage.uploader](../../storage/security/index.md#storage-uploader)

    1. [Grant this service account](../../storage/operations/buckets/edit-acl.md) read and write permissions for this bucket.

1. [Create a Yandex Data Processing cluster](../../data-proc/operations/cluster-create.md) in any suitable [configuration](../../data-proc/concepts/instance-types.md).

    Specify the settings for the storage to import the data to:
    
    {% list tabs group=storage_system %}
    
    - Object Storage {#storage}
    
        * **Service account**: Name of the previously created service account.
        * **Bucket name**: Name of the bucket you created earlier.
        * **Services**: `Sqoop`.
    
    - HDFS directory {#hdfs}
    
        **Services**:
    
        * `HBase`
        * `HDFS`
        * `Sqoop`
        * `Yarn`
        * `Zookeeper`
    
    - Apache Hive {#hive}
    
        * **Services**:
    
            * `HDFS`
            * `Hive`
            * `Mapreduce`
            * `Sqoop`
            * `Yarn`
    
        * **Properties**: `hive:hive.execution.engine` key with the `mr` value.
    
    - Apache HBase {#hbase}
    
        **Services**:
    
        * `HBase`
        * `HDFS`
        * `Sqoop`
        * `Yarn`
        * `Zookeeper`
    
    {% endlist %}

1. [Create a virtual machine](../../compute/operations/vm-create/create-linux-vm.md) for connecting to Managed Service for MySQL® and Yandex Data Processing clusters.

1. If you are using security groups for your clusters and VM instance, configure them to allow connecting:

    * [To the VM instance and Yandex Data Processing cluster](../../data-proc/operations/security-groups.md).
    * [To the Managed Service for MySQL® cluster](../operations/connect/index.md#configure-security-groups).

### Using Terraform {#create-terraform}

1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform).
1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file.
1. [Configure and initialize a provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf).
1. Place the configuration file in a separate working directory and [specify the parameter values](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file.

1. Download the [clusters-mysql-data-proc-and-vm.tf](https://github.com/yandex-cloud-examples/yc-data-proc-mysql-data-import/blob/main/clusters-mysql-data-proc-and-vm.tf) configuration file to the same working directory.

    This file describes:

    * [Security groups](../../vpc/concepts/security-groups.md) for the clusters and VM.
    * [Service account](../../iam/concepts/users/service-accounts.md) for the Yandex Data Processing cluster.
    * [Object Storage bucket](../../storage/concepts/bucket.md).
    * Managed Service for MySQL® cluster.
    * Yandex Data Processing cluster.
    * Virtual machine with public internet access.

1. Specify the infrastructure settings in the `clusters-mysql-data-proc-and-vm.tf` configuration file under `locals`:

    * `folder_id`: [ID of the folder](../../resource-manager/operations/folder/get-id.md) to create resources in.
    * `network_id`: ID of the cloud network you created earlier.
    * `subnet_id`: ID of the subnet you created earlier.
    * `storage_sa_id`: ID of the service account to use for creating a bucket in Object Storage.
    * `data_proc_sa`: Name of the Yandex Data Processing cluster service account. The name must be unique within the folder.
    * `my_cluster_version`: MySQL® version of the Managed Service for MySQL® cluster.
    * `my_cluster_password`: Password for `user1` of the Managed Service for MySQL® database named `db1`.
    * `vm_image_id`: ID of the public [image](../../compute/operations/images-with-pre-installed-software/get-list.md) with Ubuntu without GPU, e.g., for [Ubuntu 20.04 LTS](https://yandex.cloud/en/marketplace/products/yc/ubuntu-20-04-lts).
    * `vm_username` and `vm_public_key`: Username and absolute path to the [public SSH key](../../compute/operations/vm-connect/ssh.md#creating-ssh-keys) to use for accessing the virtual machine. By default, the specified username is ignored in the [Ubuntu 20.04 LTS](https://yandex.cloud/en/marketplace/products/yc/ubuntu-20-04-lts) image. A user with the `ubuntu` username is created instead. Use it to connect to the VM.
    * `bucket_name`: Bucket name in Object Storage. The name must be unique within the entire Object Storage.
    * `dp_public_key`: Absolute path to the [public SSH key](../../data-proc/operations/connect-ssh.md) for the Yandex Data Processing cluster.

        For an SSH connection to the hosts of a Yandex Data Processing cluster version 1.x , use the `root` username.

1. Validate your Terraform configuration files using this command:

    ```bash
    terraform validate
    ```

    Terraform will display any configuration errors detected in your files.

1. Create the required infrastructure:

    1. Run this command to view the planned changes:
    
       ```bash
       terraform plan
       ```
    
       If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
    
    1. If everything looks correct, apply the changes:
       1. Run this command:
    
          ```bash
          terraform apply
          ```
    
       1. Confirm updating the resources.
       1. Wait for the operation to complete.

    All the required resources will be created in the specified folder. You can check resource availability and their settings in the [management console](https://console.yandex.cloud).

{% endlist %}

## Set up the source cluster {#prepare}

1. [Connect](../operations/connect/index.md) to the `db1` database in the Yandex Managed Service for MySQL® cluster as `user1`.
1. Add test data to the database. The example uses a simple table with people's names and ages:

    1. Create a table:

        ```sql
        CREATE TABLE persons (
            Name VARCHAR(30) NOT NULL,
            Age INTEGER DEFAULT 0,
            PRIMARY KEY (Name)
        );
        ```

    1. Populate the table with data:

        ```sql
        INSERT INTO persons (Name, Age) VALUES
            ('Anna', 19),
            ('Michael', 65),
            ('Fred', 28),
            ('Alsou', 50),
            ('Max', 27),
            ('John', 34),
            ('Dmitry', 42),
            ('Oleg', 19),
            ('Alina', 20),
            ('Maria', 28);
        ```

## Importing the database {#import}

To enable [database parallelism](https://sqoop.apache.org/docs/1.4.2/SqoopUserGuide.html#_controlling_parallelism), Sqoop allows you to split imported data not only by the primary key but also by other table columns. In this example, the data is split by the `age` column.

Let's assume that:

* FQDN of the Yandex Data Processing subcluster host for data storage: `rc1c-dataproc-d-vfw6fa8x********.mdb.yandexcloud.net`.
* Bucket name in Object Storage.
* Directory names in Object Storage and HDFS: `import-directory`.
* Apache Hive database name: `db-hive`.
* Name of the Apache HBase column family: `family1`.
* Names of the HBase and Hive tables: `import-table`.
* Managed Service for MySQL® cluster ID: `c9qgcd6lplrs********`

{% list tabs group=storage_system %}

- Object Storage {#storage}

    1. [Complete all the prerequisite steps](../../data-proc/operations/sqoop-usage.md#object-storage).
    1. Run this command:

        ```bash
        sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
            --connect "jdbc:mysql://c-c9qgcd6lplrs********.rw.mdb.yandexcloud.net:3306/db1" \
            --username "user1" \
            --P \
            --table "persons" \
            --target-dir "s3a://<bucket_name>/import-directory" \
            --split-by "age"
        ```

- HDFS directory {#hdfs}

    1. [Complete all the prerequisite steps](../../data-proc/operations/sqoop-usage.md#hdfs).
    1. Run this command:

        ```bash
        sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
            --connect "jdbc:mysql://c-c9qgcd6lplrs********.rw.mdb.yandexcloud.net:3306/db1" \
            --username "user1" \
            --table "persons" \
            --target-dir "import-directory" \
            --P \
            --split-by "age"
        ```

- Apache Hive {#hive}

    1. [Complete all the prerequisite steps](../../data-proc/operations/sqoop-usage.md#apache-hive).
    1. Run this command:

        ```bash
        sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
            --connect "jdbc:mysql://c-c9qgcd6lplrs********.rw.mdb.yandexcloud.net:3306/db1" \
            --username "user1" \
            --P \
            --table "persons" \
            --hive-import \
            --create-hive-table \
            --hive-database "db-hive" \
            --hive-table "import-table" \
            --split-by "age"
        ```

- Apache HBase {#hbase}

    1. [Complete all the prerequisite steps](../../data-proc/operations/sqoop-usage.md#apache-hbase).
    1. Run this command:

        ```bash
        sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
            --connect "jdbc:mysql://c-c9qgcd6lplrs********.rw.mdb.yandexcloud.net:3306/db1" \
            --username "user1" \
            --P \
            --table "persons" \
            --hbase-create-table \
            --column-family "family1" \
            --hbase-table "import-table" \
            --split-by "age"
        ```

{% endlist %}

## Verifying the import {#check}

If your import is successful, you will see the contents of the `persons` table.

{% list tabs group=storage_system %}

- Object Storage {#storage}

    [Download](../../storage/operations/objects/download.md) the files with import results from the bucket.

- HDFS directory {#hdfs}

    1. [Connect](../../data-proc/operations/connect-ssh.md) over SSH to the Yandex Data Processing subcluster’s host to store the data.
    1. Run this command:

        ```bash
        hdfs dfs -cat /user/root/import-directory/*
        ```

- Apache Hive {#hive}

    1. [Connect](../../data-proc/operations/connect-ssh.md) over SSH to the Yandex Data Processing subcluster’s host to store the data.
    1. Run this command:

        ```bash
        hive -e "SELECT * FROM import-table;"
        ```

- Apache HBase {#hbase}

    1. [Connect](../../data-proc/operations/connect-ssh.md) over SSH to the Yandex Data Processing subcluster’s host to store the data.
    1. Run this command:

        ```bash
        echo -e "scan 'import-table'" | hbase shell -n
        ```

{% endlist %}

## Deleting the created resources {#clear-out}

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

{% list tabs group=instructions %}

- Manually {#manual}

    1. [Delete the VM](../../compute/operations/vm-control/vm-delete.md).
    1. If you reserved a public static IP address for the virtual machine, release and [delete it](../../vpc/operations/address-delete.md).
    1. Delete the clusters:

        * [Managed Service for MySQL®](../operations/cluster-delete.md)
        * [Yandex Data Processing](../../data-proc/operations/cluster-delete.md)

    1. If you created an Yandex Object Storage bucket, [delete it](../../storage/operations/buckets/delete.md).
    1. [Delete the subnet](../../vpc/operations/subnet-delete.md).
    1. [Delete the route table](../../vpc/operations/delete-route-table.md).
    1. [Delete the NAT gateway](../../vpc/operations/delete-nat-gateway.md).
    1. [Delete the cloud network](../../vpc/operations/network-delete.md).
    1. [Delete the service account](../../iam/operations/sa/delete.md).

- Terraform {#tf}

    To delete the infrastructure created with Terraform:

    1. In the terminal window, go to the directory containing the infrastructure plan.
    
        {% note warning %}
    
        Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.
    
        {% endnote %}
    
    1. Delete resources:
    
        1. Run this command:
    
            ```bash
            terraform destroy
            ```
    
        1. Confirm deleting the resources and wait for the operation to complete.
    
        All the resources described in the Terraform manifests will be deleted.

    Delete the resources you created manually:

    1. [Subnet](../../vpc/operations/subnet-delete.md)
    1. [Route table](../../vpc/operations/delete-route-table.md)
    1. [NAT gateway](../../vpc/operations/delete-nat-gateway.md)
    1. [Cloud network](../../vpc/operations/network-delete.md)

{% endlist %}