从mysql增量导出到hive库
增量导入参数:--check-column Source column to check for incremental
--incremental Define an incremental import of type
--last-value Last imported value in the incremental
--columns 指定源表字段
--enclosed-by 行业分隔符
目前可以使用两种方式:
-
append模式
可以使用append 加 --where 来达到根据时间戳来增量更新。
sqoop import --connect jdbc:mysql://192.168.100.36:3306/test -username admin -password admin --table test_mysql_to_hive_mic --where "jap_date_time >20160101.100101 and jap_date_time <=20161127.100101" --hive-database mysql --hive-table test_mysql_to_hive --split-by des_mysql_id --hive-import --incremental append --check-column des_mysql_id --last-value 0 --enclosed-by "\t"
-
lastmodified模式
直接使用lastmodified来根据时间戳增量更新。
x
sqoop import --connect jdbc:mysql://192.168.100.36:3306/test -username admin -password admin --table test_mysql_to_hive_mic --hive-database mysql --hive-table test_mysql_to_hive --split-by des_mysql_id --hive-import --incremental lastmodified --merge-key des_mysql_id --check-column jap_date_time --last-value "1980-11-28.11:30:00" --enclosed-by "\t"
-
问题记录
-
linux下执行Java调用略有不同,需要注意空格转义等问题,调用时可以指定运行环境,避免执行不生效。
xxxxxxxxxx
Process process = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", cmdA});
-
网上说sqoop默认行业分隔符为逗号,但是我使用sqoop导入到hive时,查看却是没有分隔符的。
所以,脚本和建hive脚本同时使用--enclosed-by "\t"比较靠谱
-
bin/sqoop import \ --connect jdbc:mysql://itcast01:3306/userdb \ --username root \ --password root123 \ --table emp --m 1 \ --target-dir /emp_append \ --incremental append \ --check-column id \ --last-value 1203 |
sqoop import --connect jdbc:mysql://192.168.72.1:3306/tianzhicloud_security --username root --password root --target-dir hdfs://centoshostnameKL1:9000/queryresult/sys_user1 --table sys_user --hive-table sys_user --hive-import --m 1 --incremental lastmodified --check-column createtime --last-value '2011-11-30 16:59:43.1' ; 按时间增量导入hive |
sqoop import --connect jdbc:mysql://192.168.72.1:3306/tianzhicloud_security --username root --password root --target-dir hdfs://centoshostnameKL1:9000/queryresult/sys_user --table sys_user --m 1 --incremental append --check-column userId --last-value '20' ;按字段导入增量hdfs |
sqoop1.4.6 全量导入与增量导入 与使用技巧
全量导入:
1 |
|
增量导入:(根据时间促来导入,如果表中没有时间促属性,可以增加一列时间簇)
1 |
|
从HIVE中到出道mysql中
1 |
|
Append模式增量导入
sqoop import --connect 'jdbc:mysql://localhost:3306/test'
--username root
--password root
--table role
-hive-database sqoopdb -hive-table role
--hive-import -m -1
--incremental append
--check-column dateid
--last-value "2017-12-01"
--check-column 可以是一列或者多列,用来检测数据是否按增量数据导入
--last-value 上次导入的检测字段的最大值
--incremental 增量的导入模式,有两种分别为Append和Lastmodified
常见问题:
一、sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--fields-terminated-by '\t'
要想通过sqoop把hive的表导入MySql,hive上的表分隔符一定要是'\t',sqoop才能顺利导入,如果你使用默认的,即使在用sqoop导入的时候通过参数--input-fields-terminated-by '\001'也不行的。一定是--input-fields-terminated-by '\t' 才行。
二 sqoop导入hive数据到MySql碰到hive表中列的值为null的情况。
在导入数据的过程中,如果碰到列值为null的情况,hive中为null的是以\N代替的,所以你在导入到MySql时,需要加上两个参数:--input-null-string '\\N' --input-null-non-string '\\N',多加一个'\',是为转义。如果你通过这个还不能解决字段为null的情况,还是报什么NumberFormalt异常的话,那就是比较另类的了,没有关系,我们还是要办法解决,这就是终极武器。呵呵
你应该注意到每次通过sqoop导入MySql的时,都会生成一个以MySql表命名的.java文件,然后打成JAR包,给sqoop提交给hadoop 的MR来解析Hive表中的数据。那我们可以根据报的错误,找到对应的行,改写该文件,编译,重新打包,sqoop可以通过 -jar-file ,--class-name 组合让我们指定运行自己的jar包中的某个class。来解析该hive表中的每行数据。脚本如下:一个完整的例子如下:
- ./bin/sqoop export --connect "jdbc:mysql://localhost/aaa?useUnicode=true&characterEncoding=utf-8"
- --username aaa --password bbb --table table
- --export-dir /hive/warehouse/table --input-fields-terminated-by '\t'
- --input-null-string '\\N' --input-null-non-string '\\N'
- --class-name com.chamago.sqoop.codegen.bi_weekly_sales_item
- --jar-file /tmp/sqoop-chamago/bi_weekly_sales_item.jar
复制代码
上面--jar-file 参数指定jar包的路径。--class-name 指定jar包中的class。
这样就可以解决所有解析异常了。
下面贴下sqoop经常用的命令,
1 导入MySQL表到Hive
- ./sqoop import --connect jdbc:mysql://localhost/aaa?useUnicode=true&characterEncoding=utf-8 --username
- aaa --password bbb --table table2 --hive-import
复制代码
三 sqoop增量倒入
sqoop支持两种增量MySql导入到hive的模式,
一种是 append,即通过指定一个递增的列,比如:
--incremental append --check-column num_iid --last-value 0
另种是可以根据时间戳,比如:
--incremental lastmodified --check-column created --last-value '2012-02-01 11:0:00'
就是只导入created 比'2012-02-01 11:0:00'更大的数据。
Sqoop (sqoop.apache.org)工具是hadoop环境下连接关系数据库,和hadoop存储系统的桥梁,支持多种关系数据源和hive,hdfs,hbase的相互导入。一般情况下,关系数据表存在于线上环境的备份环境,需要每天进行数据导入,根据每天的数据量而言,sqoop可以全表导入,对于每天产生的数据量不是很大的情形可以全表导入,但是sqoop也提供了增量数据导入的机制。
sqoop1与sqoop2对比:
版本号对比
两代之间是两个完全不同的版本,不兼容
sqoop1:1.4.x
sqoop2:1.99.x
sqoop2比sqoop1的改进
(1) 引入sqoop server,集中化管理connector等
(2) 多种访问方式:CLI,Web UI,REST API
(3) 引入基于角色 的安全机制
sqoop2和sqoop1的功能性对比
功能 | Sqoop 1 | Sqoop 2 |
用于所有主要 RDBMS 的连接器 | 支持 | 不支持 解决办法: 使用已在以下数据库上执行测试的通用 JDBC 连接器: Microsoft SQL Server 、 PostgreSQL 、 MySQL 和 Oracle 。 此连接器应在任何其它符合 JDBC 要求的数据库上运行。但是,性能可能无法与 Sqoop 中的专用连接器相比 |
Kerberos 安全集成 | 支持 | 不支持 |
数据从 RDBMS 传输至 Hive 或 HBase | 支持 | 不支持 解决办法: 按照此两步方法操作。 将数据从 RDBMS 导入 HDFS 在 Hive 中使用相应的工具和命令(例如 LOAD DATA 语句),手动将数据载入 Hive 或 HBase |
数据从 Hive 或 HBase 传输至 RDBMS | 不支持 解决办法: 按照此两步方法操作。 从 Hive 或 HBase 将数据提取至 HDFS (作为文本或 Avro 文件) 使用 Sqoop 将上一步的输出导出至 RDBMS | 不支持 按照与 Sqoop 1 相同的解决方法操作 |
sqoop1和sqoop2的架构对比
(1) : sqoop1的架构图
版本号为1.4.x为sqoop1
在架构上:sqoop1使用sqoop客户端直接提交的方式
访问方式:CLI控制台方式进行访问
安全性:命令或脚本中指定用户数据库名及密码
(2) : sqoop2的架构图
版本号为1.99x为sqoop2
在架构上:sqoop2引入了sqoop server,对connector实现了集中的管理
访问方式:REST API、 JAVA API、 WEB UI以及CLI控制台方式进行访问
CLI方式访问,会通过交互过程界面,输入的密码信息丌被看到,同时Sqoop2引入基亍角色的安全机制,Sqoop2比Sqoop多了一个Server端。
sqoop1与sqoop2优缺点比较 :
- sqoop1优点架构部署简单
sqoop1的缺点命令行方式容易出错,格式紧耦合,无法支持所有数据类型,安全机制不够完善,例如密码暴漏,
安装需要root权限,connector必须符合JDBC模型
- sqoop2的优点多种交互方式,命令行,web UI,rest API,conncetor集中化管理,所有的链接安装在sqoop server上,完善权限管理机制,connector规范化,仅仅负责数据的读写。
sqoop2的缺点,架构稍复杂,配置部署更繁琐。