Sqoop 1.99.3 How to import Oracle data into HDFs

Step One: Enter the client shell

fulong@fbi008:~$ sqoop.sh Client

Sqoop Home directory:/home/fulong/sqoop/sqoop-1.99.3-bin-hadoop200

Sqoop shell:type ' help ' or ' \h ' for help.

Sqoop:000> Set server--host FBI003--port 12000--webapp Sqoop

Server is set successfully

Sqoop:000> Show Version--all

Client version:

Sqoop 1.99.3 Revision 2404393160301df16a94716a3034e31b03e27b0b

Compiled by Mengweid on Fri Oct 14:15:53 EDT 2013

Server version:

Sqoop 1.99.3 Revision 2404393160301df16a94716a3034e31b03e27b0b

Compiled by Mengweid on Fri Oct 14:15:53 EDT 2013

Protocol version:

[1]

Step Two: Create a connection

Sqoop:000> Create Connection--cid 001

Creating connection for connector with ID 1

Please fill following values to create new Connection object

Name:147patents

Connection Configuration

JDBC Driver Class:oracle.jdbc.driver.OracleDriver

JDBC Connection STRING:JDBC:ORACLE:THIN:@192.168.0.147:1521:ORCLGBK

Username:zhaobiao

Password: Hu Jintao

JDBC Connection Properties:

There are currently 0 values in the map:

entry#

Security related configuration options

Max connections:100

New connection is successfully created with validation status FINE and persistent ID 1

Step three: Create a job

I tried the update command here, so I entered the wrong tablename the first time I created the job:

sqoop:000> Create Job

Required argument--xid is missing.

sqoop:000> Create job--xid 1--type Import

Creating job for connection with ID 1

Please fill following values to create new Job Object

Name:importpatents

Database Configuration

Schema Name:zhaobiao

Table Name:zhaobiao

Table SQL Statement:

Table column names:

Partition Column Name:

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

1:default

2:deflate

3:gzip

4:bzip2

5:lzo

6:lz4

7:snappy

choose:0

Output Directory:/data/zhaobiao

Throttling Resources

Extractors:

Loaders:

New job is successfully created with validation status FINE and persistent ID 1

sqoop:000> Update Job

Required argument--jid is missing.

sqoop:000> Update Job--jid 1

Updating job with ID 1

Please update job metadata:

Name:importpatents

Database Configuration

Schema Name:zhaobiao

Table name:cms_news_0625

Table SQL Statement:

Table column names:

Partition Column Name:

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

1:default

2:deflate

3:gzip

4:bzip2

5:lzo

6:lz4

7:snappy

choose:0

Output Directory:/data/zhaobiao

Throttling Resources

Extractors:

Loaders:

Job is successfully updated with status FINE

Summarize:

1.create job must specify a connection ID

A job ID was automatically assigned after the 2.create job was completed

3.update job, you need to specify a job ID

4. The specified output directory during the creation of the job specifies the path in HDFs

Step Fourth: Execute job

sqoop:000> Start Job--jid 1

2014-06-27 12:44:17 Cst:failure_on_submit

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/webkf/tools/

Exception:org.apache.hadoop.mapred.FileAlreadyExistsException:Output Directory Hdfs://fulonghadoop/data/zhaobiao already exists

sqoop:000> Start Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 12:44:53 cst:booting-progress is not available

sqoop:000> Status Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 12:50:11 cst:running-0.00%

sqoop:000> Status Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 13:36:49 cst:running-25.00%

sqoop:000> Status Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 13:39:24 cst:running-35.00%

sqoop:000> Status Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 13:47:19 cst:running-40.00%

sqoop:000> Status Job--jid 1

Submission Details

Job id:1

Server url:http://fbi003:12000/sqoop/

Created By:fulong

Creation Date:2014-06-27 12:44:53 CST

Lastly updated By:fulong

External id:job_1403248336137_0005

http://FBI003:8088/proxy/application_1403248336137_0005/

2014-06-27 14:08:36 cst:succeeded

Counters:

Org.apache.hadoop.mapreduce.JobCounter

slots_millis_maps:16499226

Total_launched_maps:10

slots_millis_reduces:0

Other_local_maps:15

Org.apache.hadoop.mapreduce.lib.output.FileOutputFormatCounter

bytes_written:421226094

Org.apache.hadoop.mapreduce.lib.input.FileInputFormatCounter

bytes_read:0

Org.apache.hadoop.mapreduce.TaskCounter

map_input_records:0

merged_map_outputs:0

physical_memory_bytes:1185689600

spilled_records:0

committed_heap_bytes:627179520

cpu_milliseconds:307570

failed_shuffle:0

virtual_memory_bytes:6542311424

split_raw_bytes:1437

map_output_records:644083

gc_time_millis:5279

Org.apache.hadoop.mapreduce.FileSystemCounter

file_write_ops:0

file_read_ops:0

file_large_read_ops:0

file_bytes_read:0

hdfs_bytes_read:1437

file_bytes_written:931780

hdfs_large_read_ops:0

Hdfs_write_ops:20

Hdfs_read_ops:40

hdfs_bytes_written:431907904

Org.apache.sqoop.submission.counter.SqoopCounters

rows_read:644083

Job executed successfully

Next question: How do I import data into hbase?