Using Sqoop

Sqoop is used to import databases to the Yandex Data Processing cluster from external sources. The section includes:

• Details about creating connect strings and setting up drivers for Sqoop.

• Scoop commands to import data to:

  • Yandex Object Storage.
  • HDFS directory.
  • Apache Hive.
  • Apache HBase.

Creating a JDBC connect stringCreating a JDBC connect string

A JDBC connect string has the following format:

jdbc:postgresql://<database_server_address>:5432/<database_name>

jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<database_name>

jdbc:mysql://<database_server_address>:3306/<database_name>

jdbc:mysql://c-<cluster_ID>.rw.mdb.yandexcloud.net:3306/<database_name>

Installing a JDBC driverInstalling a JDBC driver

For Sqoop to connect to the database using a JDBC connect string, install a JDBC driver:

MYSQL_VER="8.0.25" && \
wget "https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-${MYSQL_VER}.tar.gz" \
    --output-document 'mysql-connector-java.tar.gz' && \
sudo tar -xf mysql-connector-java.tar.gz -C /var/lib/sqoop/ && \
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/var/lib/sqoop/mysql-connector-java-${MYSQL_VER}.jar

Importing with SqoopImporting with Sqoop

To Object StorageTo Object Storage

This type of import is available if the Sqoop component is enabled in the Yandex Data Processing cluster.

To import the data to the Object Storage bucket:

1. When creating or editing the Yandex Data Processing cluster, specify the bucket name to import the data to Object Storage. Make sure that the Yandex Data Processing service account has write permissions for this bucket.

2. Create connect strings for JDBC.

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

4. Install drivers for Sqoop if they are not installed.

5. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <database_user_name> \
     --P \
     --table '<database_table_name>' \
     --target-dir 's3a://<name_of_target_bucket>/<target_folder>' \
     --split-by '<table_column>'
   

   Where --split-by is the table column used for splitting.

   Warning

   Do not specify the name of an existing bucket directory.

To the HDFS directoryTo the HDFS directory

This type of import is available if the following services are enabled in the Yandex Data Processing cluster:

• HBase
• HDFS
• Sqoop
• Yarn
• Zookeeper

To import the data to the HDFS directory:

1. Create connect strings for JDBC.

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

3. Install drivers for Sqoop if they are not installed.

4. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <database_user_name> \
     --table '<database_table_name>' \
     --target-dir '<HDFS_folder>' \
     --P \
     --split-by '<table_column>'
   

   Where --split-by is the table column used for splitting.

   Warning

   Do not specify the name of an existing HDFS directory.

To Apache HiveTo Apache Hive

This type of import is available if the following services are enabled in the Yandex Data Processing cluster:

• HDFS
• Hive
• Mapreduce
• Sqoop
• Yarn

To import the data to the Hive table:

1. When creating or editing the Yandex Data Processing cluster, add to the cluster properties the hive:hive.execution.engine key with the mr value.

2. Create connect strings for JDBC.

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

4. Install drivers for Sqoop if they are not installed.

5. Create a Hive database:

   hive -e "CREATE DATABASE <Hive_database_name>;"
   

6. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <source_database_user_name> \
     --P \
     --table '<source_database_table_name>' \
     --hive-import \
     --create-hive-table \
     --hive-database '<Hive_database_name>' \
     --hive-table '<Hive_table_name>' \
     --split-by '<table_column>'
   

   Where --split-by is the table column used for splitting.

   Warning

   Do not specify the name of an existing Hive table.

To Apache HBaseTo Apache HBase

This type of import is available if the following services are enabled in the Yandex Data Processing cluster:

• HBase
• HDFS
• Sqoop
• Yarn
• Zookeeper

To import data to Apache HBase:

1. Create connect strings for JDBC.

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

3. Install drivers for Sqoop if they are not installed.

4. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <source_database_user_name> \
     --P \
     --table '<source_database_table_name>' \
     --hbase-create-table \
     --column-family '<HBase_column_family>' \
     --hbase-table '<HBase_table_name>' \
     --split-by '<table_column>'
   

   Where --split-by is the table column used for splitting.

   Warning

   Do not specify the name of an existing HBase table.