Contact UsGet started
© 2024 Iron Hive LLC Belgrade

Importing data from Yandex Managed Service for MySQL® clusters using Sqoop

Written by
Updated at September 9, 2024

The Sqoop 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.
  2. Run the import.
  3. Check the import for correctness.

If you no longer need the resources you created, delete them.

Note

Sqoop is not supported for Yandex Data Processing clusters version 2.0 and higher. Alternatively, use Apache Spark™ features.

Getting started

Note

Place the clusters and the VM instance in the same cloud network.

  1. Create a cloud network.
  2. Create a subnet in the ru-central1-c availability zone.
  3. Set up an NAT gateway for the new subnet: this is required for the Yandex Data Processing cluster operation.

You can create other resources manually or using Terraform.

Manually

  1. Create a Managed Service for MySQL® cluster in any suitable configuration with the following settings:

    • DB name: db1
    • Username: user1

  2. To import the data to the Object Storage bucket:

    1. Create a bucket with restricted access.

    2. Create a service account with the following roles:

    3. Grant this service account read and write permissions for this bucket.

  3. Create a Yandex Data Processing cluster in any suitable configuration.

    Specify the settings for the storage to import the data to:

    • Service account: Name of the previously created service account.
    • Bucket name: Name of the previously created bucket.
    • Services: Sqoop.

    Services:

    • HBase
    • HDFS
    • Sqoop
    • Yarn
    • Zookeeper

    • Services:

      • HDFS
      • Hive
      • Mapreduce
      • Sqoop
      • Yarn

    • Properties: The hive:hive.execution.engine key with the mr value.

    Services:

    • HBase
    • HDFS
    • Sqoop
    • Yarn
    • Zookeeper

  4. Create a virtual machine to connect to Managed Service for MySQL® and Yandex Data Processing clusters.

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

Using Terraform

  1. If you do not have Terraform yet, install it.

  2. Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.

  3. Configure and initialize a provider. There is no need to create a provider configuration file manually, you can download it.

  4. Place the configuration file in a separate working directory and specify the parameter values. If you did not add the authentication credentials to environment variables, specify them in the configuration file.

  5. Download the clusters-mysql-data-proc-and-vm.tf configuration file and save it to the same working directory.

    This file describes:

  6. Specify the infrastructure parameters in the clusters-mysql-data-proc-and-vm.tf configuration file under locals:

    • folder_id: ID of the folder to create resources in.

    • network_id: ID of the previously created cloud network.

    • subnet_id: ID of the previously created subnet.

    • storage_sa_id: ID of the service account to use for creating a bucket in Object Storage.

    • data_proc_sa: Name of the service account for the Yandex Data Processing cluster. The name must be unique within the folder.

    • my_cluster_version: MySQL® version of the Managed Service for MySQL® cluster.

    • my_cluster_password: Password of the user1 user of the db1 Managed Service for MySQL® database.

    • vm_image_id: ID of the public image with Ubuntu and no GPU, e.g., for Ubuntu 20.04 LTS.

    • vm_username and vm_public_key: Username and absolute path to a public SSH key that will be used to access the virtual machine. By default, the specified username is ignored in the Ubuntu 20.04 LTS image. A user with the ubuntu username is created instead. Use it to connect to the instance.

    • bucket_name: Object Storage bucket name. The name must be unique within the entire Object Storage.

    • dp_public_key: Absolute path to a public SSH key for the Yandex Data Processing cluster.

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

  7. Make sure the Terraform configuration files are correct using this command:

    terraform validate

    If there are any errors in the configuration files, Terraform will point them out.

  8. Create the required infrastructure:

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. 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.

Preparing the source cluster

  1. Connect to the db1 database of the Yandex Managed Service for MySQL® cluster as user1.

  2. Add test data to the database. The example uses a simple table with people's names and ages:

    1. Create a table:

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

    2. Populate the table with data:

      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

To enable database parallelism, Sqoop allows you to split imported data not only by the primary key but also other table columns. In the 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********

  1. Complete all the prerequisite steps.

  2. Run this command:

    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"

  1. Complete all the prerequisite steps.

  2. Run this command:

    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"

  1. Complete all the prerequisite steps.

  2. Run this command:

    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"

  1. Complete all the prerequisite steps.

  2. Run this command:

    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"

Verify the import

If the import was successful, you will see the contents of the persons table.

Download the files with import results from the bucket.

  1. Connect over SSH to the Yandex Data Processing subcluster host to store the data.

  2. Run this command:

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

  1. Connect over SSH to the Yandex Data Processing subcluster host to store the data.

  2. Run this command:

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

  1. Connect over SSH to the Yandex Data Processing subcluster host to store the data.

  2. Run this command:

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

Deletе the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

  1. Delete the VM.

  2. If you reserved a public static IP address for the VM, release and delete it.

  3. Delete the clusters:

  4. If you created a Yandex Object Storage bucket, delete it.

  5. Delete the subnet.

  6. Delete the route table.

  7. Delete the NAT gateway.

  8. Delete the cloud network.

  9. Delete the service account.

To delete the infrastructure created with Terraform:

  1. In the terminal window, go to the directory containing the infrastructure plan.

  2. Delete the clusters-mysql-data-proc-and-vm.tf configuration file.

  3. Make sure the Terraform configuration files are correct using this command:

    terraform validate

    If there are any errors in the configuration files, Terraform will point them out.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

    This will delete all the resources described in clusters-mysql-data-proc-and-vm.tf.

Delete the resources you created manually:

  1. Subnet
  2. Route table
  3. NAT gateway
  4. Cloud network
Previous
Exchanging data with Managed Service for ClickHouse®
Next
Importing data from a Managed Service for PostgreSQL cluster using Sqoop