sqoop1.4.6连接oracle,Sqoop 1.4.6 导入实战 (RDB含MySQL和Oracle)

Sqoop-1.4.6使用手册-v1.0-import部分安装使用Sqoop-1.4.6.jar

JDBC包

导入从RDB导入数据至HDFSOracle

MySQL

从RDB导入数据至HiveOracle

MySQL

导入全部表

空值处理

增量导入导入新数据

导入更新数据

导入数据表的子集

保存上次导入的值

增量导入总结

密码保护

压缩

关于版本

转载请注明出处！

Sqoop是Hadoop与其他关系型数据库之间进行数据抽取、转换的一座坚固的桥梁，可以在关系型数据库以及Hadoop生态系统支持的数据存储方式(HDFS、Hive、HBase)之间进行导入和导出操作。

注意几点

1. 密码安全(第3节)

2. incremental append模式不支持hive导入至hive

3. Sqoop命令中的表名及字段名等是大小写敏感的

1. 安装使用

解压Sqoop二进制包并配置好环境变量

1. sqoop-1.4.6.jar

第一步先将sqoop jar包拷贝至Hadoop的mapreduce目录下：cp $SQOOP_HOME/sqoop-1.4.6.jar $HADOOP/share/hadoop/mapreduce/

2. JDBC jar包

第二步将Oracle和MySQL分别需要用到的jar包拷贝至$SQOOP_HOME/lib下cp ojdbc6.jar $SQOOP_HOME/lib/cp mysql-connector-java-5.1.38-bin.jar $SQOOP_HOME/lib/

2. 导入

1. 从RDB导入表数据至HDFS

1. Oracle

从Oracle数据库的Schema为pdbORCL中导入表PUB_DATE_Dsqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM -password 123456 --table PUB_DATE_D --warehouse-dir /user/nanyue/oracletest -m 1

此时会在/user/nanyue/下生成目录为oracletest的目录，且表中的数据均在这个目录下，字段之间以逗号(，)作为分隔符。

2. MySQL

如果不指定目录，默认文件导入到/user/nanyue下，指定目录用–warehouse-dir或–target-dirsqoop import --connect jdbc:mysql://mysql:3306/taobao --username root --password 123456 --table we --warehouse-dir /mytargetdir -m 1

2. 从RDB导入表数据至Hive

1. Oracle

从Oracle中导入表数据至Hive中的default数据库，并直接创建该表(即原本此表结构在hive中是不存在的)sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM -password 123456 --table PUB_DATE_D --hive-import --hive-database default --create-hive-table -m 1

通过这种方式导入的数据在hive中生成的数据内容(HDFS文件)以hive中的默认分隔符进行字段分隔；

2. MySQL

当表在Hive不存在时，添加–create-hive-table则在指定数据库中添加同名表sqoop import --connect jdbc:mysql://mysql:3306/taobao --username root --password 123456 --table we --hive-import --hive-database default --create-hive-table -m 1

3. 导入全部表

如果要导入某一个数据库的所有表，可以直接执行一次命令即可，如下：sqoop import-all-tables --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --hive-import --hive-database oracle -m 1

如果在导入所有表中的绝大多数表，则可以用参数–exclude-tables排除不需要导入的表(多表以逗号分隔)即可。

4. 空值的处理

默认情况下，如果RDB中存在空值，则导入时用字符串常量null(小写)代替所有空值。此种处理方式并不符合大多数的空值处理要求。而Sqoop提供了2个参数以供处理空值：

其中null-string用于处理数据库中文本类型的字段，null-non-string用于处理非文本类型的字段sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table PUB_DATE_D --where "QUARTER_NAME --hive-import --hive-database default --null-string '\\N' --null-non-string '\\N'

3. 增量导入

1. 导入新数据

RDB中的数据库表中的数据仅附加，不做修改(append模式)，需要有一个检查字段，和此字段中导入的最后值，则利用以下3个参数进行增量导入，导入id字段中值大于2的数据记录：(这种方式不能导入已有数据的更新记录)，每次导入会打印出这3个参数的值。

导入至Hive暂时不支持append模式，所以需要使用导入至HDFS的方式进行。--incremental append--check-column id--last-value 2

具体示例：sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table WE --warehouse-dir /user/hive/warehouse/ --null-string '\\N' --null-non-string '\\N' --incremental append --check-column ID --last-value 4

2. 导入更新数据

导入更新数据时使用lastmodified模式，每次导入会打印出这3个参数的值。这种模式要求标识字段必须是日期值(适合类型有date,time,datetime,timestamp)--incremental lastmodified--check-column last_update_date--last-value "2016-04-14 19:11:20"

更新数据是指在数据表中发生变化的数据，指定一个标识字段，并指定一个更新值，当目的路径已存在该目录时，需要添加一个–append或–merge-key。

下列命令将导出表WE中字段LT的值>’2016-04-13 19:11:10’且此模式支持导入至Hive表，但是即便使用了merge-key参数也无法使新数据和旧数据进行合并去重

示例1：sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table WE --warehouse-dir /user/hive/warehouse/ --null-string '\\N' --null-non-string '\\N' --incremental lastmodified --check-column LT --last-value '2016-04-13 19:11:10' --append

示例2：sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table WE --warehouse-dir /user/hive/warehouse/ --null-string '\\N' --null-non-string '\\N' --incremental lastmodified --check-column LT --last-value '2016-04-13 19:11:10' --merge-key ID

3. 导入数据表的子集(可看做增量导入)

下列实例仅导入QUARTER_NAME为Q3和Q4的数据(按天增量时，可考虑按照某个日期字段或时间字段取子集)sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM -password research --table PUB_DATE_D --where "QUARTER_NAME>='Q3'" --hive-import --hive-database default

4. 保存上次导入的值

无论incremental是append模式还是lastmodified模式，都需要指定标识字段和上次更新值，如果手动写命令更新导入，则需要记录每一次的导入后打印的值，如下：16/04/12 19:25:10 INFO tool.ImportTool: --incremental append16/04/12 19:25:10 INFO tool.ImportTool: --check-column ID16/04/12 19:25:10 INFO tool.ImportTool: --last-value 5

可以利用sqoop metastore保存导入导出的参数及其值创建sqoop jobsqoop job --create test -- import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table WE --hive-import --hive-table we1 --incremental lastmodified --check-column LT --last-value '2016-04-13 19:11:10' --merge-key ID执行sqoop jobsqoop job --exec test

每次执行sqoop job test，sqoop metastore会保存此次job中last-value的最新值(即每次执行的系统时间)，无需手动人工记住，以便于自动增量导入。

增量导入总结Sqoop 1.4.6支持的增量导入方式只有append附加新数据记录和lastmodified新增修改数据两种，其中append不支持导入至Hive，而lastmodifed支持导入至Hive。

在自动化导入中，务必使用sqoop job的方式，在crontab中写入，并导出日志信息。

3. 密码保护(非命令行密码显示输入)

在上述的所有示例中是直接用password参数指定密码，可以通过以下两种方式进行密码保护。-P

这种方式是使用-P参数替代-password，但这种方式在运行命令之后必须手动输入密码。

–password-file filename

这种方式是事先将密码写入文件filename(此文件必须在HDFS上)，然后通过–password-file进行读取

先创建文件并上传到HDFS中，并将读写权限设置为400：echo -n 'research' > sqoop.pwdhdfs dfs -put sqoop.pwd /user/nanyue/hdfs dfs -chmod 400 /user/nanyue/sqoop.pwdrm -rf sqoop.pwd

导入测试sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table PUB_DATE_D --where "QUARTER_NAME --hive-import --hive-database default

4. 压缩

sqoop默认支持压缩，其默认的压缩格式是gzip，直接在sqoop import命令末尾添加–compress即可执行默认压缩，也可以使用–compression-codec参数指定压缩格式，例如下面示例为指定bzip2压缩格式。sqoop import --connect jdbc:oracle:thin:@//myoracle:1521/pdbORCL --username DM --password-file sqoop.pwd --table PUB_DATE_D --where "QUARTER_NAME --hive-import --hive-database default --compress --compression-codec org.apache.hadoop.io.compress.BZip2Codec

5. 关于Hive版本

上述操作在以下版本中都可以顺利执行：Hadoop 2.7.2

Hive 1.2.1

Sqoop 1.4.6

当我将Hive替换成2.0时，会包缺包的错误，此时，从Hive中拷贝一些包到$SQOOP_HOME/lib下即可。cp $HIVE_HOME/lib/log4j-1.2-api-2.4.1.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/log4j-api-2.4.1.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/log4j-core-2.4.1.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/antlr-2.7.7.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/antlr4-runtime-4.5.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/antlr-runtime-3.4.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/calcite-avatica-1.5.0.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/calcite-core-1.5.0.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/calcite-linq4j-1.5.0.jar $SQOOP_HOME/lib/cp $HIVE_HOME/lib/hive-cli-2.0.0.jar $SQOOP_HOME/lib/