Sqoop Commands – Import Export

Let’s study about the Sqoop Commands – Import Export,

Before getting into Sqoop Commands we should know these points,

* In order to perform data transfer will use two keyword “import” and “export

* We should have the knowledge of RDBMS, means which type of RDBMS. In our command we are using mysql.

* We should know the login details of RDBMS like username and password.

* If we are exchanging data within the system means we can use localhost, for remote systems means instead of localhost need to write ip-address.

* In command we need to specify the database name.

* In query “-m” specify the number of mappers to be launched.

* Sqoop query contains  “–connect”  takes jdbc url and connects with the database provided in the url.

Sqoop import

Imports data from relational databases (RDBMS) to Hadoop distributed file system (HDFS) like Hive and Hbase.

Sqoop import from mysql to hdfs

In this case we are importing data from mysql to hdfs, there are mainly four cases shown in the below diagram, will see one by one.

Import All Tables

In this stage we are importing all the tables from mysql to hdfs.

Example       

Let us take an example of importing all tables from the “beyond-corner” database. The list of tables as shown below

Tables
emp1
emp2
emp3

By using below command we can  import all the tables from beyond-corner database

$ sqoop import-all-tables

–connect jdbc:mysql://localhost/beyond-corner

–username “root”

–password “beyonduser”

-m 1

–driver  com.mysql.jdbc.Driver

The fallowing command is used to verify all the table data of beyond-corner database in HDFS.

$ hadoop fs -ls /user/beyondcorner/newdata

Import Single table

In this stage we are importing single tables from mysql to hdfs.

Example

Let us take an example of importing single tables from the “beyond-corner” database.

By using below command we can import single tables from beyond-corner database.

$ sqoop import

–connect jdbc:mysql://localhost/beyond-corner

–table “emp1”

–username “root”

–password “beyonduser”

–m 1

–driver com.mysql.jdbc.Driver

The fallowing command is used to verify single table data of beyond-corner database in HDFS.

$ hadoop fs -ls /user/beyondcorner/newdata/emp1

Import Subset of Table Data

In this stage we will import a subset of a table using the ‘where’ clause.

Example

Let us take an example of below tables named as “emp1” it is in a database called “beyond-corner” in mysql.

IdNameAge
101Ram40
102Ali35
103Usha24
104Smitha28

The fallowing command is used to import subset of table data from beyond-corner database. The subset query retrieves the age of employee whose age is more then 30.

$ sqoop import

–connect jdbc:mysql://localhost/beyond-corner

–username “root”

–password “beyonduser”

–table “emp1”

–m 1

–where “age>30”

–target-dir ‘/home/beyondcorner/newdata/emp1’

The fallowing command is used to verify subset of table data from beyond-corner database in HDFS.

$ hadoop fs -cat /home/beyondcorner/newdata/emp1/part-m-00000

Incremental Import

In this technique it imports only the newly added rows in a table. here we need to add keywords like ‘incremental’, ‘check-column’, and ‘last-value’ options to perform the incremental import.

Example

Let us assume that we are adding new row of data into emp1 table is as shown below,

105,Roy,29

The fallowing command is used to import incremental data from emp1 table.

$ sqoop import

–connect jdbc:mysql://localhost/beyond-corner

–username “root”

–password “beyonduser”

–table “emp1”

–m 1

–incremental append

–check-column id

-last value 104

The fallowing command is used to verify incremental data from emp1 table in HDFS.

$ hadoop fs -cat /home/beyondcorner/newdata/emp1/part-m-00001

Sqoop import from mysql to hive

Here we are importing data from mysql to hive.

Example

By using below command we can import emp1 table from mysql into hive.

$sqoop import

–connect jdbc:mysql://localhost/beyond-corner

–table “emp1”

–username “root”

–password “beyonduser”

–hive-import –target-dir /user/hive/warehouse

-m 1

–driver com.mysql.jdbc.Driver

The fallowing command is used to verify emp1 table in hive

$ hadoop fs –ls /user/hive/warehouse

Sqoop import from mysql to hbase

Here we are importing data from mysql to hbase.

Example

By using below command we can import emp1 table from mysql into hbase.

$ sqoop import

–connect jdbc:mysql://localhost/beyond-corner

–table “emp1”

–username “root”

–password “beyonduser”

–hbase-table “newemp”

–column-family “emp_details”

–hbase-row-key id

-m 1

–driver com.mysql.jdbc.Driver

Note: In hbase shell we need to create “newemp” table, so that we can import mysql table emp1 to hbase table newemp.

References

https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_literal_sqoop_import_literal

that’s all about the sqoop import, next article will study about sqoop export.