Using Sqoop from Scratch

Updated on 2024-12-13 GMT+08:00

View PDF

Sqoop is an open-source tool that transfers data between Hadoop (Hive) and traditional databases like MySQL and PostgreSQL. It can import data from relational databases, including MySQL, Oracle, and PostgreSQL, to Hadoop HDFS. It can also import HDFS data to a relational database.

Prerequisite

The Sqoop component and dependent services have been selected during cluster creation.
Install the client. For details, see Installing a Client (MRS 3.x or Later). To facilitate the use of Sqoop, you need to install a full client when using Sqoop. If the installation directory is /opt/client, change it to the actual installation directory.
If Kerberos authentication has been enabled for the cluster, you need to create or obtain a user with the required permission to run Sqoop commands. The created user must be added to the hadoop, supergroup, and hive groups. For details, see Creating a User.
The JDBC driver package (for example, mysql-connector-java-5.1.47.jar) for the corresponding database has been saved in the client directory /Sqoop/sqoop/lib. The permission and user group have been adjusted to match other JAR packages in the directory.

Exporting Data from HDFS to MySQL

Log in to the node where the Sqoop client is deployed.
Initialize environment variables.

source /opt/client/bigdata_env
If Kerberos authentication is enabled for the cluster, run the kinit command to authenticate the user. If Kerberos authentication is not enabled for the cluster, go to 4.

For example, the sqoop_user user is authenticated using the following command:

kinit sqoop_user

Operate the Sqoop client.

sqoop export --connect jdbc:mysql://10.100.xxx.xxx:3306/test --username root --password xxx --table component13 -export-dir hdfs://hacluster/user/hive/warehouse/component_test3 --fields-terminated-by ',' -m 1

For details about more parameters, see Common Sqoop Commands and Parameters.

**Table 1** Parameters
Parameter	Description
--connect	Specifies the URL for connecting to JDBC. The value format is jdbc:mysql://IP address of the MySQL database:MySQL Port/Database name.
--username	Specifies the username for connecting to the MySQL database.
-password	Specifies the password for connecting to the MySQL database. To prevent any potential information leaks that may arise from commands containing authentication passwords, it is advisable to disable the command history recording function before executing any commands to avoid security risks.
-table <table-name>	Specifies the name of the MySQL table used to store exported data.
-export-dir <dir>	Specifies the HDFS path of the Sqoop table to be exported.
--fields-terminated-by	Specifies the delimiter of the exported data, which must be the same as that in the HDFS data table to be exported.
-m or -num-mappers <n>	Starts n (4 by default) maps to import data concurrently. The value cannot be greater than the maximum number of maps in a cluster.
-direct	Imports data to a relational database using a database import tool, for example, mysqlimport of MySQL, more efficient than the JDBC connection mode.
-update-key <col-name>	Specifies the column used for updating the existing data in a relational database.
-update-mode <mode>	Specifies how updates are performed. The value can be updateonly or allowinsert. This parameter is used only when the relational data table does not contain the data record to be imported. For example, if the HDFS data to be imported to the destination table contains a data record id=1 and the table contains an existing data record id=2, the update will fail.
-input-null-string <null-string>	This parameter is optional. If it is not specified, null will be used.
-input-null-non-string <null-string>	This parameter is optional. If it is not specified, null will be used.
-staging-table <staging-table-name>	Creates a table with the same data structure as the destination table for storing data before it is imported to the destination table. This parameter is used to ensure transaction security during data import to relational database tables. If multiple transactions exist during data import, the failure of one transaction can impact others. This parameter can be used to prevent issues caused by incorrect or duplicate data records.
-clear-staging-table	Clears data in the staging table before data is imported if the staging-table is not empty.

Importing Data from MySQL to Hive

Log in to the node where the Sqoop client is deployed.
Initialize environment variables.

source /opt/client/bigdata_env
If Kerberos authentication is enabled for the cluster, run the kinit command to authenticate the user. If Kerberos authentication is not enabled for the cluster, go to 4.

For example, the sqoop_user user is authenticated using the following command:

kinit sqoop_user

Operate the Sqoop client.

sqoop import --connect jdbc:mysql://10.100.xxx.xxx:3306/test --username root --password xxx --table component --hive-import --hive-table component_test2 --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile

**Table 2** Parameters
Parameter	Description
--hive-import	Imports data from a relational database to MRS Hive.
--delete-target-dir	Deletes the existing target file (if any) from Hive and imports again.
-append	Appends data to an existing dataset in HDFS. Once this parameter is used, Sqoop imports data to a temporary directory, renames the temporary file where the data is stored, and moves the file to a formal directory to avoid duplicate file names in the directory.
-as-avrodatafile	Imports data to a data file in the Avro format.
-as-sequencefile	Imports data to a sequence file.
-as-textfile	Imports data to a text file. After the text file is generated, you can run SQL statements in Hive to query the result.
--as-parquetfile	Imports data to a Parquet file.
-boundary-query <statement>	Specifies the SQL statement for performing boundary query. Before importing data, use a SQL statement to obtain a result set and import the data in the result set. The data format can be -boundary-query 'select id,creationdate from person where id = 3' (indicating a data record whose ID is 3) or select min(<split-by>), max(<split-by>) from <table name>. The fields to be queried cannot contain fields whose data type is string. Otherwise, the error message "java.sql.SQLException: Invalid value for getLong()" is displayed.
-columns<col,col,col...>	Specifies the fields to be imported. The format is **-Column id,Username**.
-direct	Imports data to a relational database using a database import tool, for example, mysqlimport of MySQL, more efficient than the JDBC connection mode.
-direct-split-size	Splits the imported streams by byte. Especially when data is imported from PostgreSQL using the direct mode, a file that reaches the specified size can be divided into several independent files.
-inline-lob-limit	Sets the maximum value of an inline LOB.
-m or -num-mappers	Starts n (4 by default) maps to import data concurrently. The value cannot be greater than the maximum number of maps in a cluster.
-query, -e<statement>	Imports data from the query result. To use this parameter, you must specify the -target-dir and -hive-table parameters and use the query statement containing the WHERE clause as well as $CONDITIONS. Example: *-query'select from person where $CONDITIONS' -target-dir /user/hive/warehouse/person -hive-table person**
-split-by<column-name>	Specifies the column of a table used to split work units. Generally, the column name is followed by the primary key ID.
-table <table-name>	Specifies the relational database table from which data is obtained.
-target-dir <dir>	Specifies the HDFS path.
-warehouse-dir <dir>	Specifies the directory for storing data to be imported. This parameter is applicable when data is imported to HDFS but cannot be used when you import data to Hive directories. This parameter cannot be used together with -target-dir.
-where	Specifies the WHERE clause when data is imported from a relational database, for example, -where 'id = 2'.
-z,-compress	Compresses sequence, text, and Avro data files using the GZIP compression algorithm. Data is not compressed by default.
–compression-codec	Specifies the Hadoop compression codec. GZIP is used by default.
–null-string <null-string>	Specifies the string to be interpreted as NULL for string columns.
–null-non-string<null-string>	Specifies the string to be interpreted as null for non-string columns. If this parameter is not specified, NULL will be used.
-check-column (col)	Specifies the column for checking incremental data import, for example, id.
-incremental (mode) append or lastmodified	Incrementally imports data. append: appends records, for example, appending records that are greater than the value specified by last-value. lastmodified: appends data that is modified after the date specified by last-value.
-last-value (value)	Specifies the maximum value (greater than the specified value) of the column after the last import. This parameter can be set as required.

Sqoop Usage Example

Importing data from MySQL to HDFS using the sqoop import command
sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --query 'SELECT * FROM component where $CONDITIONS and component_id ="MRS 1.0_002"' --target-dir /tmp/component_test --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile
Exporting data from OBS to MySQL using the sqoop export command
sqoop export --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component14 -export-dir obs://obs-file-bucket/xx/part-m-00000 --fields-terminated-by ',' -m 1
Importing data from MySQL to OBS using the sqoop import command
sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component --target-dir obs://obs-file-bucket/xx --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile
Importing data from MySQL to OBS tables outside Hive using the sqoop import command
sqoop import --connect jdbc:mysql://10.100.231.134:3306/test --username root --password xxx --table component --hive-import --hive-table component_test01 --fields-terminated-by "," -m 1 --as-textfile

MySQL Driver Package Is Missing During Data Import or Export

If the error "Could not load db driver class: com.mysql.jdbc.Driver" is reported when you run the sqoop import or sqoop export command, the MySQL driver package is missing. Download the MySQL driver package from the MySQL official website, decompress it, upload it to the Client installation directory/Sqoop/sqoop/lib, and run the command again.

Figure 1 Error indicating that the MySQL driver package is missing
Click to enlarge

Parent topic: Using Sqoop

Previous topic: Using Sqoop

Next topic: Common Sqoop Commands and Parameters

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot