Importing Table Data from Oracle DB to Hive DB Table in Hadoop Using SQOOP

Apache Sqoop can be used to import data from any relational DB into HDFS, Hive or HBase.

Sqoop command can be used for both Importing Data from any DB to HDFS/Hive/Hbase and Exporting from HDFS to any other DB like HANA/Oracle

Here i am explaining about using the SQOOP command to bring/importing the data from relational DB (oracle) into Hive DB.

Below Picture Tell you how Sqoop import command Works in the Backgroud.



How this Sqoop Command works in Hadoop.

When Sqoop Command execute,

1. First it will connect to the Relational DB using the credentials provided int he Sqoop command.
2. Next It will look for the Schema and table.
3. Next it will select the data 
4. Next bring data into Hadoop HDFS file system. 
5. And then It will look for Where you are going to import in Hadoop like Hive/HBASE etc.. this case it is Hive. 
6. Next it will connect to hive and look for the Schema in the Hive. 
7. Next it will check the table is Existing or not, if not it will create and Dump the data. 
8. If the table is already exists  then look for the Insert or Overwrite and dump the data.

Sqoop Command Syntax

sqoop import --connect 'jdbc:oracle:thin:@//scan-prd-109.hadoopcomp.com:1542/TSMPRD.hadoopcomp.com' \
    --verbose                             \
    --username DWRPTRO                    \
    --password DWRPTRO                    \
    --table TEMP1                         \
    --hive-import                         \
    --hive-database 'XXX'                 \
    --hive-table TEMP1                    \
    --hive-drop-import-delims             \
    --target-dir '/tmp/$Table'            \
    --hive-overwrite                      \
     -m 1


connect- this string determinse where to connect
username- Username to the relational DB
password- Password to the above user
table- WHich table name you want import
hive-import - This is target Env. Hive and it is Import
hive-database -Represent Hive DB name
hive-table- Hive Table Name in that Hive DB
hive-drop -  It will drop the Delimeters while importing
target-dir - it is HDFS target Directory in Hadoop
hive-overwrite - Overwrite the data in Hive (truncate and load)
m- Number of Mappers to allocate for this import

Thank You 
Sagar Saiboyani