Sqoop 1.4.6 导入实战 (RDB含MySQL和Oracle)

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

1. 安装使用
   1. Sqoop-1.4.6.jar
   2. JDBC包
2. 导入
   1. 从RDB导入数据至HDFS
      1. Oracle
      2. MySQL
   2. 从RDB导入数据至Hive
      1. Oracle
      2. MySQL
      3. 导入全部表
      4. 空值处理
   3. 增量导入
      1. 导入新数据
      2. 导入更新数据
      3. 导入数据表的子集
      4. 保存上次导入的值
      5. 增量导入总结
3. 密码保护
4. 压缩
5. 关于版本

转载请注明出处！

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_D

sqoop 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-dir

sqoop 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<'Q2'" --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’且<’当前系统时间’的所有数据记录，不同之处在于–append参数表示根据更新要求直接将抽取出来的数据附加至目标目录，而–merge-key参数则表示此更新操作将分成2个MR Job，Job1将表中的更新数据导入到HDFS中一个临时目录下，Job2将结合新数据和已经存在HDFS上的旧数据按照merge-key指定的字段进行合并（类似于去重），并输出到目标目录，每一行仅保留最新的值。 
此模式支持导入至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 append
16/04/12 19:25:10 INFO tool.ImportTool:   --check-column ID
16/04/12 19:25:10 INFO tool.ImportTool:   --last-value 5

可以利用sqoop metastore保存导入导出的参数及其值

1. 创建sqoop job

sqoop 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

1. 执行sqoop job

sqoop job --exec test

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

增量导入总结

1. Sqoop 1.4.6支持的增量导入方式只有append附加新数据记录和lastmodified新增修改数据两种，其中append不支持导入至Hive，而lastmodifed支持导入至Hive。
2. 在自动化导入中，务必使用sqoop job的方式，在crontab中写入，并导出日志信息。

3. 密码保护（非命令行密码显示输入）

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

1. -P 
   这种方式是使用-P参数替代-password，但这种方式在运行命令之后必须手动输入密码。
2. –password-file filename 
   这种方式是事先将密码写入文件filename（此文件必须在HDFS上），然后通过–password-file进行读取 
   先创建文件并上传到HDFS中，并将读写权限设置为400：

echo -n 'research' > sqoop.pwd
hdfs dfs -put sqoop.pwd /user/nanyue/
hdfs dfs -chmod 400 /user/nanyue/sqoop.pwd
rm -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<'Q2'" --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<'Q2'" --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/