Sqoop Import- Importing Data From RDBMS to HDFS

In the last article, we discussed Sqoop Export. In this article we will discuss Sqoop import, a tool which we use for importing tables from RDBMS to HDFS is the Sqoop Import tool. Here, we will learn how to Sqoop import multiple tables from RDBMS database to Hadoop HDFS.

Moreover, we will learn the purpose of importing in Sqoop, Sqoop import syntax as well as Sqoop import query examples to understand it well.

So, let’s start the Sqoop Import tutorial.

Sqoop Import and its Purpose

A tool, which we use for importing tables from RDBMS to HDFS is the Sqoop Import tool. Basically, here each row in a table is considered as a record in HDFS. Moreover, when we talk about text files all records are stored as text data.

Whereas when we talk about Avro and sequence files all records are stored as binary data here. Basically, we can say the Sqoop Import all tables as individual tables from RDBMS to HDFS.

Let’s discuss HDFS Features

Sqoop Import Syntax

To import data into HDFS we use the following syntax for importing in Sqoop. Such as:
$ sqoop import (generic-args) (import-args)
$ sqoop-import (generic-args) (import-args)
The very advantage is we can type the sqoop import arguments in any order with respect to one another. However, when it comes to the Hadoop generic arguments, those must precede any import arguments only.
Basically, here all the arguments are grouped into collections which are organized by function. However, some collections are present in several tools here. For example, the “common” arguments.
Table 1. Sqoop Import – Common arguments

a. Connecting to a Database Server
Sqoop is designed to import tables from a database into HDFS. To do so, you must specify a connect string that describes how to connect to the database. The connect string is similar to a URL, and is communicated to Sqoop with the –connect argument. That defines the server and database to connect to; also specify the port.

For example:
$ sqoop import –connect jdbc:mysql://database.example.com/employees
Table 2. Sqoop Import – Validation arguments More Details

Table 3. Sqoop Import – Import control arguments

Although, both –null-string and –null-non-string arguments are optional.However, we use the string “null” if not specified.

b. Selecting the Data to Import
Basically, Sqoop imports data in a table-centric fashion. we generally use the –table argument while selecting the table to import. like, –table employees. However, this argument in a database can also identify a VIEW or other table-like entity.
However, all the data is written to HDFS in its “natural order”. That is a table containing columns A, B, and C results in an import of data in Sqoop. Such as:
A1,B1,C1
A2,B2,C2
…
By selecting a subset of columns, with –columns argument we can control their ordering. The only condition is that it should include a comma-delimited list of columns to import. Like: –columns “name,employee_id,jobtitle”.

c. Free-form Query Imports
We can also import the result set of an arbitrary SQL query in Sqoop. Also, we can specify a SQL statement with the –query argument. Despite using the –table, –columns and –where arguments.

While we import a free-form query, we need to specify a destination directory with –target-dir.
In addition, we can import the results of a query in parallel. Afterwards,  each map task will need to execute a copy of the query, with results partitioned by bounding conditions inferred by Sqoop. However, our query must include the token $CONDITIONS. That each Sqoop process will replace with a unique condition expression. Also important to select a splitting column with –split-by.

For example:
$ sqoop import \
–query ‘SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS’ \
–split-by a.id –target-dir /user/foo/joinresults
By specifying a single map task with -m 1, the query can be executed once and imported serially.
$ sqoop import \
–query ‘SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS’ \
-m 1 –target-dir /user/foo/joinresults

d. Controlling Parallelism
From most database sources, Sqoop imports data in parallel. Also, to perform the import in sqoop by using the -m or –num-mappers argument. Moreover, we can specify the number of map tasks (parallel processes) to use each of these arguments takes an integer value which corresponds to the degree of parallelism to employ. 

e. Controlling Distributed Cache
Basically, in $SQOOP_HOME/lib folder, Sqoop will copy the jars to job cache every time when starting a Sqoop job. However,  when  Oozie launched it, this is unnecessary since Oozie uses its own Sqoop share lib which keeps Sqoop dependencies in the distributed cache. Although, for the Sqoop dependencies Oozie will do the localization on each worker node only once during the first Sqoop job. Also, reuse the jars on worker node for subsequential jobs. 

f. Controlling the Sqoop Import Process
The import process in sqoop will use JDBC, by default. That provides a reasonable cross-vendor import channel. However, by using database-specific data movement tools, some databases can perform imports in a more high-performance fashion.

In addition, inside your home directory in HDFS, Sqoop will import a table named foo to a directory named foo. For example, the Sqoop import tool will write to /user/someuser/foo/(files) if your username is some user. However, we can adjust the parent directory of the import with the –warehouse-dir argument. For example:
$ sqoop import –connnect <connect-str> –table foo –warehouse-dir /shared \
   …

g. Controlling transaction isolation
Basically, to import data the read committed transaction isolation in the mappers are used in Sqoop. In all ETL workflows, this may not be the ideal. Yet it may desire to reduce the isolation guarantees. However, to instruct Sqoop to use read uncommitted isolation level we can use the –relaxed-isolation option.

Although, on all databases, the read-uncommitted isolation level is not supported. For example, Oracle. So specifying the option –relaxed-isolation may not be supported on all databases.

h. Controlling type mapping
Basically, to map most SQL types to appropriate Java or Hive representatives, Sqoop is preconfigured. Although, here also the default mapping might not be suitable for everyone. Also, might be overridden. Either by –map-column-java (for changing the mapping to Java) or –map-column-hive (for changing Hive mapping).
Table 4. Parameters for overriding mapping

Basically, Sqoop is expecting the comma-separated list of mapping in the form <name of column>=<new type>. For example:
$ sqoop import … –map-column-java id=String,value=Integer
Also, Sqoop will raise the exception in case that some configured mapping will not be used.

i. Incremental Imports
There is an incremental import mode offered by Sqoop. That can be used to retrieve only rows newer than some previously imported set of rows.
The following arguments control incremental imports in sqoop:
Table 5. Sqoop Import – Incremental import arguments

Basically, there are two types of incremental imports in Sqoop.One is appended and second is last modified. Moreover, to specify the type of incremental import to perform, we can also use the –incremental argument.

j. File Formats
Basically, there are two file formats in which we can import data. One is delimited text or other is SequenceFiles.

k. Large Objects
In particular ways, Sqoop handles large objects (BLOB and CLOB columns). However, if this data is truly large, then these columns should not be fully materialized in memory for manipulation, as most columns are. Despite, their data is handled in a streaming fashion. 
Table 6. Sqoop Import – Output line formatting arguments

Table 7. Sqoop Import – Input parsing arguments

Table 8. Sqoop Import – Hive arguments

l. Importing Data Into Hive
Uploading our data into files in HDFS is Sqoop’s import tool’s main function. However, if we have a Hive metastore associated with our HDFS cluster, Sqoop can also import the data into Hive. It is possible by generating and executing a CREATE TABLE statement to define the data’s layout in Hive. Also, it is the very simple method to import data into Hive, like adding the –hive-import option to your Sqoop command line.

Table 9. Sqoop Import – HBase arguments

m. Importing Data Into HBase
Beyond HDFS and Hive, Sqoop supports additional import targets. Like Sqoop can also import records into a table in HBase.

Table 10. Sqoop Import – Accumulo arguments

n. Importing Data Into Accumulo
Also, in Accumulo, Sqoop supports importing records into a table.

Table 11. Sqoop Import – Code generation arguments

o. Additional Import Configuration Properties
Some additional properties which can be configured by modifying conf/sqoop-site.xml. However, Properties can be specified the same as in Hadoop configuration files.

For example:

<property>
<name>property.name</name>
<value>property.value</value>
</property>

On the command line in the generic arguments, they can also be specified.  For example:
sqoop import -D property.name=property.value …
Table 12. Sqoop Import – Additional import configuration properties

Sqoop Import Example

Basically, we will understand how to use the import tool in a variety of situations by the following examples.
In addition, a basic import of a table named EMPLOYEES in the corp database:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES
Also, a basic import requiring a login:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–username SomeUser -P
Enter password: (hidden)
So selecting specific columns from the EMPLOYEES table:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–columns “employee_id,first_name,last_name,job_title”
Controlling the import parallelism (using 8 parallel tasks):
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
   -m 8
Storing data in SequenceFiles, and setting the generated class name to com.foocorp.Employee:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–class-name com.foocorp.Employee –as-sequencefile
Also, specifying the delimiters to use in a text-mode import:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–optionally-enclosed-by ‘\”‘
Basically here, importing the data to Hive:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–hive-import
Also, here, only importing new employees:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–where “start_date > ‘2010-01-01′”
Afterwards, changing the splitting column from the default:
$ sqoop import –connect jdbc:mysql://db.foo.com/corp –table EMPLOYEES \
–split-by dept_id
Then, we are verifying that an import was successful:
$ hadoop fs -ls EMPLOYEES
Found 5 items
drwxr-xr-x   – someuser somegrp          0 2010-04-27 16:40 /user/someuser/EMPLOYEES/_logs
-rw-r–r–   1 someuser somegrp    2913511 2010-04-27 16:40 /user/someuser/EMPLOYEES/part-m-00000
-rw-r–r–   1 someuser somegrp    1683938 2010-04-27 16:40 /user/someuser/EMPLOYEES/part-m-00001
-rw-r–r–   1 someuser somegrp    7245839 2010-04-27 16:40 /user/someuser/EMPLOYEES/part-m-00002
-rw-r–r–   1 someuser somegrp    7842523 2010-04-27 16:40 /user/someuser/EMPLOYEES/part-m-00003
$ hadoop fs -cat EMPLOYEES/part-m-00000 | head -n 10
0,joe,smith,engineering
1,jane,doe,marketing
…
After having already imported the first 100,000 rows of a table, Here performing an incremental import of new data:
$ sqoop import –connect jdbc:mysql://db.foo.com/somedb –table sometable \
–where “id > 100000” –target-dir /incremental_dataset –append
In the corp database, there is an import of a table named EMPLOYEES. That uses validation to validate the import. By using the table row count and the number of rows copied into HDFS. 
$ sqoop import –connect jdbc:mysql://db.foo.com/corp \
–table EMPLOYEES –validate

So, this was all in Sqoop Import tutorial. Hope you like our explanation.

5.Conclusion

Hence, in this article, we have learned the whole concept of Sqoop Import. Also, we have seen various Sqoop Import examples and Sqoop import syntax. However, if you want to ask any query regarding, please ask in the comment section below. We will definitely get back to you.
See also – Sqoop Mainframe Tool & why Should learn Sqoop
For reference