Steps to Import Oracle Tables using Sqoop

Oracle database is one of the largely used database in the world. Most of financial organizations are using Oracle for their transaction processing. As mentioned in my other post import Netezza tables using Apache Sqoop, with growing data organizations are moving their computation part to Hadoop ecosystem. In this post, we will check steps to import Oracle tables using Sqoop commands.

Steps to Import Oracle Tables using Sqoop

Most of the organizations and people trying to get data into Hadoop ecosystem, they use various options such as creating flat-files and then import those files to HDFS using ‘hdfs dfs -copyFromLocal’ hdfs command.

If your data is residing in relational databases like Oracle, you can use open-source tool like Apache Sqoop to import tables directly into Hive or HDFS directory. Sqoop support import of single table, all tables and even incremental import. You can use Sqoop command to import data from all relational database that support jdbc connection. Apache Sqoop uses JDBC connector to connect to target databases.

You can read more about Sqoop in official website.

Sqoop Command to Import Oracle Table

Below is the sqoop command connection string to import tables from Oracle database:

sqoop import --connect jdbc:oracle:thin:@host_IP:portnumber:SSID --table tableName --username userName --password password --hive-import --hive-database DatabaseName --create-hive-table --hive-table tableName -m 1

Examples to Import Oracle Table

Below is the example to import EMPLOYEE table from Oracle database:

$ sqoop import --connect jdbc:oracle:thin:@192.168.100.293:1521:ORCL --table EMPLOYEE --username user1 --password welcome1 --hive-import --hive-database vithal_test --create-hive-table --hive-table employee -m 1
Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail.
...
...
OK
Time taken: 4.007 seconds
Loading data to table vithal_test.employee
Table vithal_test.employee stats: [numFiles=1, numRows=0, totalSize=503, rawDataSize=0]
OK
Time taken: 0.951 seconds

Now, check hive database for the imported tables:

> select * from employee;
+---------------+----------------+------------------+--+
| employee.eid  | employee.name  | employee.salary  |
+---------------+----------------+------------------+--+
| 1201.0        | Gopal          | 45000            |
| 1202.0        | Manisha        | 45000            |
| 1203.0        | Masthanvali    | 40000            |
| 1204.0        | Kiran          | 40000            |
| 1205.0        | Kranthi        | 30000            |
| 1206.0        | Kiran1         | 40000            |
| 1207.0        | Kranthi1       | 30000            |
+---------------+----------------+------------------+--+
7 rows selected (0.23 seconds)

Sqoop Command to Import all the Tables from Oracle

Below is the command to import all tables from Oracle database:

sqoop import-all-tables --connect jdbc:oracle:thin:@192.168.100.293:1521:ORCL --username user1 --password welcome1 --hive-import --hive-database vithal_test --create-hive-table -m 1

Sqoop is the easiest way to import data from Oracle databases.