当前位置：首页 > news >正文

sqoop安装配置教程,以及从Oracle数据抽取数据的分析

news 来源：原创 2024/5/4 11:24:53

sqoop安装教程

1.使用背景以及介绍

　　Sqoop is a tool designed to transfer data between Hadoop and relational databases. You can use Sqoop to import data from arelational database management system (RDBMS) such as MySQL or Oracleinto the Hadoop Distributed File System (HDFS),transform the data in Hadoop MapReduce, and then export the data backinto an RDBMS

介绍：上文引用自官方文档。意思很明显，sqoop就是将传统关系型数据库中的数据抽取到sqoop上，也能导回RDBMS。而且sqoop现在已经是apache的一个顶级项目，应该比较成熟了。

具体使用和详细信息参考官方文档：http://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html

2.下载解压

因为我使用的hadoop是0.20.X的版本，sqoop不支持。sqoop支持cdh3的版本，不过没有关系，通过拷贝jar的方式可以弥补。

wget http://archive.cloudera.com/cdh/3/hadoop-0.20.2-CDH3B4.tar.gz

wget http://archive.cloudera.com/cdh/3/sqoop-1.2.0-CDH3B4.tar.gz

tar -zxvf sqoop-1.2.0-CDH3B4.tar.gz
tar -zxvf hadoop-0.20.2-CDH3B4.tar.gz

cp hadoop-0.20.2-CDH3B4/hadoop-core-0.20.2-CDH3B4.jar  sqoop-1.2.0-CDH3B4/lib/

3.配置环境变量

vi .bash_profile

#内容如下
export SQOOP_HOME=/home/hadoop/sqoop-1.2.0-CDH3B4

export PATH=$PATH:$SQOOP_HOME/bin

export CLASSPATH=$CLASSPATH:$SQOOP_HOME/ojdbc6.jar:$SQOOP_HOME/lib/*

话说我一开始老是类找不到可是东西明明在lib下了，非常的无奈，还好后来自己摸索出来这样强制指定jar的方式

4.尝试从Oracle数据库抽取300多万条记录

sqoop import --connect jdbc:oracle:thin:@192.168.1.65/wkm --username wkm --password wkm --target-dir /user/hadoop/wkmData2  --table C_PICRECORD_ALL  -m 1


用以上命令执行即可

--connect 后给出连接的数据库地址以及名字

--username  用户名

--password  密码

--targer-dir  hdfs上要保存的输出位置，目录自己会创建，不用我们操心

--table   用的ORACLE 一定要表名大写 因为sqoop是从oracle dictionary table里面读取的 

-m 1  设定1个并行度 ，如果多个并行度 必须确保有主键

输出结果：

注意：如果你的hadoop采用了mesos,不要把CPU和内存资源分配完，要不然会卡在map0% reduce0%

------------------------------------------------1cpu 1G内存----------------------------------------------------------------------------

13/04/16 11:31:05 INFO mapreduce.ImportJobBase: Beginning import of C_PICRECORD_ALL
13/04/16 11:31:05 INFO manager.OracleManager: Time zone has been set to GMT
13/04/16 11:31:08 INFO mapred.JobClient: Running job: job_201304161006_0004
13/04/16 11:31:09 INFO mapred.JobClient:  map 0% reduce 0%
13/04/16 11:31:34 INFO mapred.JobClient:  map 100% reduce 0%
13/04/16 11:40:42 INFO mapred.JobClient: Job complete: job_201304161006_0004
13/04/16 11:40:42 INFO mapred.JobClient: Counters: 18
13/04/16 11:40:42 INFO mapred.JobClient:   Job Counters 
13/04/16 11:40:42 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=563490
13/04/16 11:40:42 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/04/16 11:40:42 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/04/16 11:40:42 INFO mapred.JobClient:     Launched map tasks=1
13/04/16 11:40:42 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/04/16 11:40:42 INFO mapred.JobClient:   File Output Format Counters 
13/04/16 11:40:42 INFO mapred.JobClient:     Bytes Written=503848416
13/04/16 11:40:42 INFO mapred.JobClient:   FileSystemCounters
13/04/16 11:40:42 INFO mapred.JobClient:     HDFS_BYTES_READ=87
13/04/16 11:40:42 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=27953
13/04/16 11:40:42 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=503848416
13/04/16 11:40:42 INFO mapred.JobClient:   File Input Format Counters 
13/04/16 11:40:42 INFO mapred.JobClient:     Bytes Read=0
13/04/16 11:40:42 INFO mapred.JobClient:   Map-Reduce Framework
13/04/16 11:40:42 INFO mapred.JobClient:     Map input records=3133366
13/04/16 11:40:42 INFO mapred.JobClient:     Physical memory (bytes) snapshot=99123200
13/04/16 11:40:42 INFO mapred.JobClient:     Spilled Records=0
13/04/16 11:40:42 INFO mapred.JobClient:     CPU time spent (ms)=169400
13/04/16 11:40:42 INFO mapred.JobClient:     Total committed heap usage (bytes)=18546688
13/04/16 11:40:42 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=1521811456
13/04/16 11:40:42 INFO mapred.JobClient:     Map output records=3133366
13/04/16 11:40:42 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
13/04/16 11:40:43 INFO mapreduce.ImportJobBase: Transferred 480.5073 MB in 577.2022 seconds (852.456 KB/sec)
13/04/16 11:40:43 INFO mapreduce.ImportJobBase: Retrieved 3133366 records.
13/04/16 11:40:43 INFO util.AppendUtils: Appending to directory kakouData


------------------------------------2CPU 1.8G内存-------------------------------------------
13/04/16 13:57:41 INFO mapreduce.ImportJobBase: Beginning import of C_PICRECORD_ALL
13/04/16 13:57:41 INFO manager.OracleManager: Time zone has been set to GMT
13/04/16 13:57:43 INFO mapred.JobClient: Running job: job_201304161356_0001
13/04/16 13:57:44 INFO mapred.JobClient:  map 0% reduce 0%
13/04/16 13:58:10 INFO mapred.JobClient:  map 100% reduce 0%
13/04/16 14:07:14 INFO mapred.JobClient: Job complete: job_201304161356_0001
13/04/16 14:07:14 INFO mapred.JobClient: Counters: 18
13/04/16 14:07:14 INFO mapred.JobClient:   Job Counters 
13/04/16 14:07:14 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=557391
13/04/16 14:07:14 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/04/16 14:07:14 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/04/16 14:07:14 INFO mapred.JobClient:     Launched map tasks=1
13/04/16 14:07:14 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/04/16 14:07:14 INFO mapred.JobClient:   File Output Format Counters 
13/04/16 14:07:14 INFO mapred.JobClient:     Bytes Written=503848416
13/04/16 14:07:14 INFO mapred.JobClient:   FileSystemCounters
13/04/16 14:07:14 INFO mapred.JobClient:     HDFS_BYTES_READ=87
13/04/16 14:07:14 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=27436
13/04/16 14:07:14 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=503848416
13/04/16 14:07:14 INFO mapred.JobClient:   File Input Format Counters 
13/04/16 14:07:14 INFO mapred.JobClient:     Bytes Read=0
13/04/16 14:07:14 INFO mapred.JobClient:   Map-Reduce Framework
13/04/16 14:07:14 INFO mapred.JobClient:     Map input records=3133366
13/04/16 14:07:14 INFO mapred.JobClient:     Physical memory (bytes) snapshot=139059200
13/04/16 14:07:14 INFO mapred.JobClient:     Spilled Records=0
13/04/16 14:07:14 INFO mapred.JobClient:     CPU time spent (ms)=178130
13/04/16 14:07:14 INFO mapred.JobClient:     Total committed heap usage (bytes)=17367040
13/04/16 14:07:14 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=1521811456
13/04/16 14:07:14 INFO mapred.JobClient:     Map output records=3133366
13/04/16 14:07:14 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
13/04/16 14:07:14 INFO mapreduce.ImportJobBase: Transferred 480.5073 MB in 573.1275 seconds (858.5166 KB/sec)
13/04/16 14:07:14 INFO mapreduce.ImportJobBase: Retrieved 3133366 records.