Tuesday, October 14, 2014

Sqoop Oracle Example : Getting Started with Oracle -> HDFS import/extract


In this post, we'll get Sqoop (1.99.3) connected to an Oracle database, extracting records to HDFS.

Add Oracle Driver to Sqoop Classpath

The first thing we'll need to do is copy the oracle JDBC jar file into the Sqoop lib directory.  Note, this directly may not exist.  You may need to create it.

For me, this amounted to:
➜  sqoop  mkdir lib
➜  sqoop  cp ~/git/boneill/data-lab/lib/ojdbc6.jar ./lib

Add YARN and HDFS to Sqoop Classpath

Next, you will need to add the HDFS and YARN jar files to the classpath of Sqoop.  If you recall from the initial setup, the classpath is controlled by the common.loader property in the server/conf/catalina.properties file.  To get things submitting to the YARN cluster properly, I added the following additional paths to the common.loader property:

common.loader=${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar,${catalina.home}/../lib/*.jar,/Users/bone/tools/hadoop/share/hadoop/common/*.jar,/Users/bone/tools/hadoop/share/hadoop/yarn/lib/*.jar,/Users/bone/tools/hadoop/share/hadoop/mapreduce/*.jar,/Users/bone/tools/hadoop/share/hadoop/tools/lib/*.jar,/Users/bone/tools/hadoop/share/hadoop/common/lib/*.jar,/Users/bone/tools/hadoop/share/hadoop/hdfs/*.jar,/Users/bone/tools/hadoop/share/hadoop/yarn/*.jar

Note, the added paths.

*IMPORTANT* : Restart your Sqoop server so it picks up the new jar files. 
(including the driver jar!)

Create JDBC Connection

After that, we can fire up the client, and create a connection with the following:

bin/sqoop.sh client
...
sqoop> create connection --cid 1
Creating connection for connector with id 1
Please fill following values to create new connection object
Name: my_datasource
Connection configuration
JDBC Driver Class: oracle.jdbc.driver.OracleDriver
JDBC Connection String: jdbc:oracle:thin:@change.me:1521:service.name
Username: your.user
Password: ***********
JDBC Connection Properties:
There are currently 0 values in the map:
entry# HIT RETURN HERE!
Security related configuration options
Max connections: 10
New connection was successfully created with validation status FINE and persistent id 1

Create Sqoop Job

Next step is to make a job.  This is done with the following:

sqoop> create job --xid 1 --type import
Creating job for connection with id 1
Please fill following values to create new job object
Name: data_import

Database configuration

Schema name: MY_SCHEMA
Table name: MY_TABLE
Table SQL statement:
Table column names:
Partition column name: UID
Nulls in partition column:
Boundary query:

Output configuration

Storage type:
  0 : HDFS
Choose: 0
Output format:
  0 : TEXT_FILE
  1 : SEQUENCE_FILE
Choose: 0
Compression format:
  0 : NONE
...
Choose: 0
Output directory: /user/boneill/dump/

Throttling resources

Extractors:
Loaders:
New job was successfully created with validation status FINE  and persistent id 3

Everything is fairly straight-forward. The output directory is the HDFS directory to which the output will be written.

 Run the job!

This was actually the hardest step because the documentation is out of date. (AFAIK)  Instead of using "submission", as the documentation states.  Use the following:

sqoop> start job --jid 1
Submission details
Job ID: 3
Server URL: http://localhost:12000/sqoop/
Created by: bone
Creation date: 2014-10-14 13:27:57 EDT
Lastly updated by: bone
External ID: job_1413298225396_0001
	http://your_host:8088/proxy/application_1413298225396_0001/
2014-10-14 13:27:57 EDT: BOOTING  - Progress is not available

From there, you should be able to see the job in YARN!

After a bit of churning, you should be able to go over to HDFS and find your files in the output directory.

Best of luck all.  Let me know if you have any trouble.



5 comments:

Affity Solutions said...

Thanks for your ideas. You can also find the details on Affity Solutions, at the software development company. The main object of the Affity Solutions is to provide quality web services and is among the few software development company in Nagpur.

Vishwa V said...
This comment has been removed by the author.
Vishwa V said...

Thanks for the details Brian.

I added all the hadoop paths in which the jars are stored but I still get the error "Class not found" when I start the job.
Please note that hadoop jobs are working fine and they are up and running.
Can you please let me know what might be the issue ?

regards,
Vishwanagendran

Venu said...

Thanks a lot by default apache website giving info about sqoop with mysql, your blog helped me to understand sqoop with oracle .... thanks a lot. Keep share more sqoop interview questions

Kits Online Training said...

nice blog, thanks for sharing
Dataguard Online Training Institute

Best Etl Testing Online Training

Best Oracle Golden Gate Online Training institute from india

Best Vmware Online Training institute from Hyderabad india