Sqoop安装与使用(sqoop-1.4.5 on hadoop 1.0.4)

《Sqoop安装与使用(sqoop-1.4.5 on hadoop 1.0.4)》由会员分享，可在线阅读，更多相关《Sqoop安装与使用(sqoop-1.4.5 on hadoop 1.0.4)（12页珍藏版）》请在装配图网上搜索。

1、Sqoop安装与使用(sqoop-1.4.5 on hadoop 1.0.4)1.什么是SqoopSqoop即 SQL to Hadoop ，是一款方便的在传统型数据库与Hadoop之间进行数据迁移的工具，充分利用MapReduce并行特点以批处理的方式加快数据传输，发展至今主要演化了二大版本，Sqoop1和Sqoop2。Sqoop工具是hadoop下连接关系型数据库和Hadoop的桥梁，支持关系型数据库和hive、hdfs，hbase之间数据的相互导入，可以使用全表导入和增量导入。那么为什么选择Sqoop呢？高效可控的利用资源，任务并行度，超时时间。 数据类型映射与转化，可自动进行，用户也可

2、自定义 支持多种主流数据库，MySQL,Oracle，SQL Server，DB2等等2.Sqoop1和Sqoop2对比的异同之处两个不同的版本，完全不兼容 版本号划分区别，Apache版本：1.4.x(Sqoop1); 1.99.x(Sqoop2) CDH版本 : Sqoop-1.4.3-cdh4(Sqoop1) ; Sqoop2-1.99.2-cdh4.5.0 (Sqoop2) Sqoop2比Sqoop1的改进 引入Sqoop server，集中化管理connector等 多种访问方式：CLI,Web UI，REST API 引入基于角色的安全机制3.Sqoop1与Sqoop2的架构图Sq

3、oop架构图1Sqoop架构图24.Sqoop1与Sqoop2的优缺点比较Sqoop1Sqoop2架构仅仅使用一个Sqoop客户端引入了Sqoop server集中化管理connector，以及rest api，web，UI，并引入权限安全机制部署部署简单，安装需要root权限，connector必须符合JDBC模型架构稍复杂，配置部署更繁琐使用 命令行方式容易出错，格式紧耦合，无法支持所有数据类型，安全机制不够完善，例如密码暴漏多种交互方式，命令行，web UI，rest API，conncetor集中化管理，所有的链接安装在Sqoop server上，完善权限管理机制，connector规

4、范化，仅仅负责数据的读写5.Sqoop的安装部署5.0 安装环境hadoop：hadoop-1.0.4sqoop：sqoop-1.4.5.bin_hadoop-1.0.05.1 下载安装包及解压tar -zxvf sqoop-1.4.5.bin_hadoop-1.0.0.tar.gzln -s ./package/sqoop-1.4.5.bin_hadoop-1.0.0/ sqoop5.2 配置环境变量和配置文件cd sqoop/conf/mv sqoop-env-template.sh sqoop-env.shvi sqoop-env.sh在sqoop-env.sh中添加如下代码#Set p

5、ath to where bin/hadoop is available#export HADOOP_COMMON_HOME=export HADOOP_COMMON_HOME=/home/hadoop/hadoop-2.2.0#Set path to where hadoop-*-core.jar is available#export HADOOP_MAPRED_HOME=export HADOOP_MAPRED_HOME=/home/hadoop/hadoop-2.2.0#set the path to where bin/hbase is available#export HBASE_

6、HOME=export HBASE_HOME=/home/hadoop/hbase-0.96.2-hadoop2#Set the path to where bin/hive is available#export HIVE_HOME=export HIVE_HOME=/home/hadoop/apache-hive-0.13.1-bin#Set the path for where zookeper config dir is#export ZOOCFGDIR=export ZOOCFGDIR=/home/hadoop/zookeeper-3.4.5 (如果数据读取不设计hbase和hive

7、，那么相关hbase和hive的配置可以不加，如果集群有独立的zookeeper集群，那么配置zookeeper，反之，不用配置)。5.3 copy需要的lib包到Sqoop/lib所需的包：hadoop-core包、Oracle的jdbc包、mysql的jdbc包（oracle的jar包：ojdbc6.jar,mysql的jar包mysql-connector-java-5.1.17.jar）cp /hadoop/hadoop-core-2.2.0.jar /sqoop/lib/cp /ojdbc6.jar /sqoop/lib/cp /mysql-connector-java-5.1.17

8、.jar /sqoop/lib/5.4 添加环境变量vi /.bash_profile添加如下内容view sourceprint?1.#Sqoop2.export SQOOP_HOME=/home/hadoop/sqoop3.export PATH=$PATH:$SQOOP_HOME/binsource /.bash_profile5.5 测试oracle数据库的连接使用连接oracle数据库，列出所有的数据库hadoopeb179 sqoop$sqoop list-databases -connect jdbc:oracle:thin:10.1.69.173:1521:ORCLBI -us

9、ername huangq -P或者sqoop list-databases -connect jdbc:oracle:thin:10.1.69.173:1521:ORCLBI -username huangq -password 123456或者MySQL：sqoop list-databases -connect jdbc:mysql:/172.19.17.119:3306/ -username hadoop -password hadoopWarning: /home/hadoop/sqoop/./hcatalog does not exist! HCatalog jobs will f

10、ail.Please set $HCAT_HOME to the root of your HCatalog installation.Warning: /home/hadoop/sqoop/./accumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.Warning: $HADOOP_HOME is deprecated.14/08/17 11:59:24 INFO sqoop.Sqoop: Running Sq

11、oop version: 1.4.5Enter password:14/08/17 11:59:27 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.14/08/17 11:59:27 INFO manager.SqlManager: Using default fetchSize of 100014/08/17 11:59:51 INFO manager.OracleManager: Time zone has been set to GMTMRDRPMKFOW_QHOrac

12、le数据库的表导入到HDFS注意：默认情况下会使用4个map任务，每个任务都会将其所导入的数据写到一个单独的文件中，4个文件位于同一目录，本例中 -m1表示只使用一个map任务 文本文件不能保存为二进制字段，并且不能区分null值和字符串值null 执行下面的命令后会生成一个ENTERPRISE.java文件，可以通过ls ENTERPRISE.java查看，代码生成是sqoop导入过程的必要部分，sqoop在将源数据库中的数据写到HDFS前，首先会用生成的代码将其进行反序列化hadoopeb179 $sqoop import -connect jdbc:oracle:thin:10.1.69

13、.173:1521:ORCLBI -username huangq -password 123456 -table ORD_UV -m 1 -target-dir /user/sqoop/test -direct-split-size 67108864Warning: /home/hadoop/sqoop/./hcatalog does not exist! HCatalog jobs will fail.Please set $HCAT_HOME to the root of your HCatalog installation.Warning: /home/hadoop/sqoop/./a

14、ccumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.Warning: $HADOOP_HOME is deprecated.14/08/17 15:21:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.514/08/17 15:21:34 WARN tool.BaseSqoopTool: Setting your password on the command-l

15、ine is insecure. Consider using -P instead.14/08/17 15:21:34 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.14/08/17 15:21:34 INFO manager.SqlManager: Using default fetchSize of 100014/08/17 15:21:34 INFO tool.CodeGenTool: Beginning code generation14/08/17 15:21:4

16、6 INFO manager.OracleManager: Time zone has been set to GMT14/08/17 15:21:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ORD_UV t WHERE 1=014/08/17 15:21:46 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoopNote: /tmp/sqoop-hadoop/compile/328657d577512bd2c61

17、e07d66aaa9bb7/ORD_UV.java uses or overrides a deprecated API.Note: Recompile with -Xlint:deprecation for details.14/08/17 15:21:47 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/328657d577512bd2c61e07d66aaa9bb7/ORD_UV.jar14/08/17 15:21:47 INFO manager.OracleManager: Time zo

18、ne has been set to GMT14/08/17 15:21:47 INFO manager.OracleManager: Time zone has been set to GMT14/08/17 15:21:47 INFO mapreduce.ImportJobBase: Beginning import of ORD_UV14/08/17 15:21:47 INFO manager.OracleManager: Time zone has been set to GMT14/08/17 15:21:49 INFO db.DBInputFormat: Using read co

19、mmited transaction isolation14/08/17 15:21:49 INFO mapred.JobClient: Running job: job_201408151734_002714/08/17 15:21:50 INFO mapred.JobClient: map 0% reduce 0%14/08/17 15:22:12 INFO mapred.JobClient: map 100% reduce 0%14/08/17 15:22:17 INFO mapred.JobClient: Job complete: job_201408151734_002714/08

20、/17 15:22:17 INFO mapred.JobClient: Counters: 1814/08/17 15:22:17 INFO mapred.JobClient: Job Counters14/08/17 15:22:17 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=1586214/08/17 15:22:17 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=014/08/17 15:22:17 INFO map

21、red.JobClient: Total time spent by all maps waiting after reserving slots (ms)=014/08/17 15:22:17 INFO mapred.JobClient: Launched map tasks=114/08/17 15:22:17 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=014/08/17 15:22:17 INFO mapred.JobClient: File Output Format Counters14/08/17 15:22:17 INFO mapre

22、d.JobClient: Bytes Written=147214/08/17 15:22:17 INFO mapred.JobClient: FileSystemCounters14/08/17 15:22:17 INFO mapred.JobClient: HDFS_BYTES_READ=8714/08/17 15:22:17 INFO mapred.JobClient: FILE_BYTES_WRITTEN=3375514/08/17 15:22:17 INFO mapred.JobClient: HDFS_BYTES_WRITTEN=147214/08/17 15:22:17 INFO

23、 mapred.JobClient: File Input Format Counters14/08/17 15:22:17 INFO mapred.JobClient: Bytes Read=014/08/17 15:22:17 INFO mapred.JobClient: Map-Reduce Framework14/08/17 15:22:17 INFO mapred.JobClient: Map input records=8114/08/17 15:22:17 INFO mapred.JobClient: Physical memory (bytes) snapshot=192405

24、50414/08/17 15:22:17 INFO mapred.JobClient: Spilled Records=014/08/17 15:22:17 INFO mapred.JobClient: CPU time spent (ms)=154014/08/17 15:22:17 INFO mapred.JobClient: Total committed heap usage (bytes)=50377523214/08/17 15:22:17 INFO mapred.JobClient: Virtual memory (bytes) snapshot=269957120014/08/

25、17 15:22:17 INFO mapred.JobClient: Map output records=8114/08/17 15:22:17 INFO mapred.JobClient: SPLIT_RAW_BYTES=8714/08/17 15:22:17 INFO mapreduce.ImportJobBase: Transferred 1.4375 KB in 29.3443 seconds (50.1631 bytes/sec)14/08/17 15:22:17 INFO mapreduce.ImportJobBase: Retrieved 81 records.数据导出Orac

26、le和HBase使用export可将hdfs中数据导入到远程数据库中 export -connect jdbc:oracle:thin:192.168.*.*:*:*-username *-password=* -m1table VEHICLE-export-dir /user/root/VEHICLE向Hbase导入数据 sqoop import -connect jdbc:oracle:thin:192.168.*.*:*:*-username*-password=*-m 1 -table VEHICLE -hbase-create-table -hbase-table VEHICLE-h

27、base-row-key ID -column-family VEHICLEINFO -split-by ID5.6 测试Mysql数据库的使用前提：导入mysql jdbc的jar包 测试数据库连接sqoop list-databases connect jdbc:mysql:/192.168.10.63 username rootpassword 123456Sqoop的使用以下所有的命令每行之后都存在一个空格，不要忘记（以下6中命令都没有进行过成功测试）mysqlhdfssqoop export connectjdbc:mysql:/192.168.10.63/ipjusername r

28、ootpassword 123456table ipj_flow_userexport-dir hdfs:/192.168.10.63:8020/user/flow/part-m-00000前提：(1)hdfs中目录/user/flow/part-m-00000必须存在(2)如果集群设置了压缩方式lzo，那么本机必须得安装且配置成功lzo(3)hadoop集群中每个节点都要有对mysql的操作权限hdfsmysqlsqoop import connectjdbc:mysql:/192.168.10.63/ipjtable ipj_flow_usermysqlhbasesqoop import

29、connectjdbc:mysql:/192.168.10.63/ipjtable ipj_flow_userhbase-table ipj_statics_testhbase-create-tablehbase-row-key idcolumn-family imeihbasemysql关于将Hbase的数据导入到mysql里，Sqoop并不是直接支持的，一般采用如下3种方法：第一种：将Hbase数据扁平化成HDFS文件，然后再由Sqoop导入.第二种：将Hbase数据导入Hive表中，然后再导入mysql。第三种：直接使用Hbase的Java API读取表数据，直接向mysql导入不需要使

30、用Sqoop。mysqlhivesqoop import connectjdbc:mysql:/192.168.10.63/ipjtable hive_table_testhive-importhive-table hive_test_table 或create-hive-table hive_test_tablehivemysqlsqoop export connectjdbc:mysql:/192.168.10.63/ipjusername hivepassword 123456table target_tableexport-dir /user/hive/warehouse/uv/dt=

31、mytable前提：mysql中表必须存在 Sqoop其他操作列出mysql中的所有数据库sqoop list-databases connect jdbc:mysql:/192.168.10.63:3306/ usernameroot password 123456列出mysql中某个库下所有表sqoop list-tables connect jdbc:mysql:/192.168.10.63:3306/ipj usernameroot password 1234566 Sqoop1的性能测试数据：表名：tb_keywords行数：11628209数据文件大小：1.4G测试结果：HDFS-

32、DBHDFS-DBSqoop428s166sHDFSFILEDB209s105s从结果上来看，以FILE作为中转方式性能是要高于SQOOP的,原因如下：本质上SQOOP使用的是JDBC，效率不会比MYSQL自带的导入导出工具效率高 以导入数据到DB为例，SQOOP的设计思想是分阶段提交，也就是说假设一个表有1K行，那么它会先读出100行（默认值），然后插入，提交，再读取100行如此往复即便如此，SQOOP也是有优势的，比如说使用的便利性，任务执行的容错性等。在一些测试环境中如果需要的话可以考虑把它拿来作为一个工具使用。其他参考资料：使用Sqoop将HDFS/Hive/HBase与MySQL/Oracle中的数据相互导入、导出