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

浏览: 8418

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/
推荐 7
本文由 MarsJ 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

6 个评论

感谢,非常详细
sqoop能够将SqlServer的数据更新插入到mongoDB吗?如果可以效率如何呀~!
Sqoop可以把SQL Server的数据导入Hive、Hadoop,所以理论上也可以导入到MongoDB。

但是我这边没有实践过MongoDB,所以没法回答效率如何的问题。你感兴趣可以试试呀。
marsj,可以帮忙看个问题么,使用sqoop查看数据库表是提示空指针的错误。

系统版本:centos
hadoop版本:Hadoop 2.7.2
sqoop版本:Sqoop 1.4.6
数据库版本:oracle 12.1.0.2.0
jdbc包:ojdbc6.jar
问题描述:用sqoop命令查看数据库,报空指针错误,请marsj大神帮忙看下,十分感谢!!
代码如下:

[root@bigdata lib]# sqoop list-tables --connect jdbc:oracle://192.168.1.116:1521/orcl12c --username sh --password oracleWarning: /root/opt/sqoop-1.4.6/../hbase does not exist! HBase imports will fail.Please set $HBASE_HOME to the root of your HBase installation.Warning: /root/opt/sqoop-1.4.6/../hcatalog does not exist! HCatalog jobs will fail.Please set $HCAT_HOME to the root of your HCatalog installation.Warning: /root/opt/sqoop-1.4.6/../accumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.Warning: /root/opt/sqoop-1.4.6/../zookeeper does not exist! Accumulo imports will fail.Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.16/11/12 01:50:19 INFO sqoop.Sqoop: Running Sqoop version: 1.4.616/11/12 01:50:19 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.16/11/12 01:50:19 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.16/11/12 01:50:19 INFO manager.SqlManager: Using default fetchSize of 100016/11/12 01:50:19 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerExceptionjava.lang.NullPointerException at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:759) at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49) at org.apache.sqoop.Sqoop.run(Sqoop.java:143) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
刚开始接触这块,把oracle导入到hdfs中,oracle不会安装。请问oracle在Ubuntu安装有没有什么资料?
MarsJ , 很感谢你的教学! 我想请教一下关于sqoop导入hive表的一个问题:
背景:已通过sqoop将MySQL的表A全部导入至Hive,后来有需求要在Hive上多加一列用于存储新的内容,该列的内容还在MySQL的表B上
问题:是否可以通过Sqoop将表B上的该列内容导入到Hive的新加列中?
如果您看到有时间的话,指点指点小弟

要回复文章请先登录注册