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 for importing 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://<DB_server_address>:5432/<DB_name>

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

jdbc:mysql://<DB_server_address>:3306/<DB_name>

jdbc:mysql://c-<cluster_ID>.rw.mdb.yandexcloud.net:3306/<DB_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 data to an 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 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’s host to store the data.

4. Install drivers for Sqoop if required.

5. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <database_username> \
     --P \
     --table '<database_table_name>' \
     --target-dir 's3a://<bucket_name_for import>/<destination_directory>' \
     --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 data to an HDFS directory:

1. Create connect strings for JDBC.

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

3. Install drivers for Sqoop if required.

4. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <database_username> \
     --table '<database_table_name>' \
     --target-dir '<HDFS_directory>' \
     --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 data to a Hive table:

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

2. Create connect strings for JDBC.

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

4. Install drivers for Sqoop if required.

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_username> \
     --P \
     --table '<table_name_in_source_database>' \
     --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’s host to store the data.

3. Install drivers for Sqoop if required.

4. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connect_string> \
     --username <source_database_username> \
     --P \
     --table '<table_name_in_source_database>' \
     --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.