Using Sqoop

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

• Info on creating connection 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 connection stringCreating a JDBC connection string

A JDBC connection 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 connection string, install the 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 updating your Yandex Data Processing cluster, specify the name of the Object Storage bucket to import the data to. Make sure the Yandex Data Processing service account has write permissions for this bucket.

2. Create JDBC connection strings.

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

4. Install drivers for Sqoop if missing.

5. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connection_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 an HDFS directoryTo an 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 JDBC connect strings.

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

3. Install drivers for Sqoop if missing.

4. Run this command:

   sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
     --connect <JDBC_connection_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 updating your Yandex Data Processing cluster, add the hive:hive.execution.engine key set to mr to the cluster properties.

2. Create JDBC connection strings.

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

4. Install drivers for Sqoop if missing.

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 '<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 JDBC connection strings.

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

3. Install drivers for Sqoop if missing.

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.