Importing data from Yandex Managed Service for PostgreSQL to Yandex Data Processing using Sqoop

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.

Getting startedGetting started

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

You can create other resources manually or using Terraform.

ManuallyManually

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

   • DB name: db1
   • Username: user1

2. To import data to an Object Storage bucket:

   1. Create a bucket with restricted access.

   2. Create a service account with the following roles:

      • dataproc.agent
      • dataproc.provisioner
      • monitoring.viewer
      • storage.viewer
      • storage.uploader

   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:

   Object Storage

   HDFS directory

   Apache Hive

   Apache HBase

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

   Services:

   • HBase
   • HDFS
   • Sqoop
   • Yarn
   • Zookeeper

   • Services:

     • HDFS
     • Hive
     • Mapreduce
     • Sqoop
     • Yarn

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

   Services:

   • HBase
   • HDFS
   • Sqoop
   • Yarn
   • Zookeeper

4. Create a virtual machine for connecting to Managed Service for PostgreSQL and Yandex Data Processing clusters.

5. 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.
   • To the Managed Service for PostgreSQL cluster.

Using TerraformUsing 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-postgresql-data-proc-and-vm.tf configuration file and save it to the same working directory.

   This file describes:

   • Security groups for the clusters and VM.
   • Service account for the Yandex Data Processing cluster.
   • Object Storage bucket.
   • Managed Service for PostgreSQL cluster.
   • Yandex Data Processing cluster.
   • Virtual machine with public internet access.

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

   • folder_id: ID of the folder 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.

   • pg_cluster_version: PostgreSQL version of the Managed Service for PostgreSQL cluster.

   • pg_cluster_password: Password for user1 in the Managed Service for PostgreSQL database named db1.

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

   • vm_username and vm_public_key: Username and absolute path to the public SSH key to use for accessing 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 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 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.

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 this command to view the planned changes:

      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.

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the 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 clusterPreparing the source cluster

1. Connect to the Managed Service for PostgreSQL cluster's database named db1 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 databaseImporting the database

To enable database 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 PostgreSQL cluster ID: c9qgcd6lplrs********.

Verify the importVerify the import

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

Deleting the created resourcesDeleting the created resources

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