使用sqoop将mysql与Oracle中的数据按照日期与时间段分区存入hdfs中_sqoop 如何设置 按照旧表最大日期更新操作

今天遇到一个需求，这里简单介绍一下。原先把很多的信息都汇总到一个trace的表中，其中包括人脸识别后的对比数据、某学校卡口的进入车辆数据、访客记录、门禁刷卡记录与一卡通消费记录等，然后在HUE中新建一个WorkFlow，每天的凌晨进行增量导入与数据处理。后面甲方那边感觉汇总的很好，结果要求按照小时更新。这个对于之前结构变化就有点大了，原先sqoop都是overwrite数据表，然后在hive里面处理就好了。现在如果按照小时的话，就要增量导入到hive中，其中各个数据存储也不能跟之前一样直接导入到hdfs中，现在就需要根据日期与小时数分区存入数据到hdfs中。

后面我会讲解一下解决的步骤。首先还是先写好指令在shell里面先自己测试一下，看看能不能通过sqoop按照日期和小时数分区导入数据到hdfs中。

一、设置两个参数，获取当天日期与上一小时数

这里因为这写sqoop语句会写在一个sh文件中，按照分区导入的日期和小时数会外部传入参数进去，所以为了模拟这个效果，我在测试时候现在shell中设置date_today与hour两个参数：

> date_today=`date -d "today" +%Y-%m-%d`
> hour=`date -d "-1 hour" +"%H"`

二、按照日期与小时数分区从Mysql中导入数据

中间遇到一些小问题，这里先直接贴成功的代码。

sqoop import  --connect "jdbc:mysql://192.168.102.18:3306/school?useUnicode=true&characterEncoding=utf-8" --username root --password P@ssw0rd4321 --driver com.mysql.jdbc.Driver --query 'select * from snap_match where alarmTime>date_sub(date_format(now(), "%Y-%m-%d %H"), interval 1 hour) and alarmTime<date_format(now(), "%Y-%m-%d %H") and $CONDITIONS' --target-dir /user/activemq_topic/snap_match/$date_today/$hour --fields-terminated-by '\t' --lines-terminated-by '\n' --null-string '\\N' --null-non-string '\\N' --m 1;

错误a.首先看这个SQL语句：

select * from snap_match where alarmTime>date_sub(date_format(now(), "%Y-%m-%d %H"), interval 1 hour) and alarmTime<date_format(now(), "%Y-%m-%d %H")

之前这里的双引号我都是用的单引号，但是这样会跟外部的单引号冲突，导致后面的sql语句解析不了。所以需要改成这的双引号。

错误b.改成双引号后执行又出了个错：

可以很明显的看到说缺少了$CONDITIONS，然后我去查了查，如果用了where的判断的话，则一定要在后面加上 $CONDITIONS条件。

这里边几点需要注意的坑：

1.“–connect “：CDH Sqoop1 使用 里边用一些 JDBC Connection Strings 的语法介绍和例子，但是 Oracle 的我试了不行，正确的写法应该如：jdbc:oracle:thin:@<HOST>:<PORT>:<DATABASE_NAME>,区别在于 thin 后边是需要又一个 冒号 的，很容易错，还有 与 中间的连接也是冒号，因为有的写法是 “/“，但是这里需要注意的是，这里是 冒号。

 

2.“–query“：如果这里SQL语句里边用过单引号了，那外边必须要用双引号；

•     这里的SQL假如直接使用 “FROM STORE“的话，是会报 table 找不见，或者不存在的，因为使用“–query“的话没有指定 Schema ，所以这里必须使用 '.'的形式
•     where 后边必须有 $CONDITIONS 条件，sqoop 运行的时候，看日志发现sqoop 会在这里插入（1=0）或（1=1）来控制这条语句的执行。外边使用双引号的话，$CONDITIONS 前边需要加反斜杠 即：\$CONDITIONS。 Free form query in Sqoop Import with WHERE clause

3.“–split-by” ：需要指定一个 int 类型的列名，一般是主键。sqoop 会计算这个字段的 MIN 和 MAX ，然后结合 fetchSize 来确定 怎么切分数据块。这个字段必填。

4.“–direct“：没加这个之前，导入特别慢，中间经常会出现 “Connect reset“，这个没关系，一会儿它又回自动连接上。但是确实太慢了。使用这个字段后，导入速度又快又稳定，这个字段代表的意思应该是 使用的是关系数据库自带的导入导出工具。最好加上这个字段配置。

所以因为我在sql中用了 where，所以需要添加这个条件 $CONDITIONS  。然后mysql导入数据执行成功。

三、按照日期与小时数分区从Oracle中导入数据

这里从Oracle导入数据问题又跟MySQL中遇到的不一样，就单独出来汇总到这篇博客中：

https://blog.csdn.net/JJBOOM425/article/details/105574161

这里也直接给测试成功的指令：

sqoop import --connect jdbc:oracle:thin:@192.168.99.19:1521:jcxydb --username sjbzk --password sjbzk --driver oracle.jdbc.driver.OracleDriver --query "SELECT * FROM GXTS_MJSKXX WHERE TO_DATE(SKSJ,'yyyy-mm-dd HH24:mi:ss')>TO_DATE(TO_DATE(sysdate-1/24,'yyyy-mm-dd HH24'),'yyyy-mm-dd HH24:mi:ss') AND TO_DATE(SKSJ,'yyyy-mm-dd HH24:mi:ss')<TO_DATE(TO_DATE(sysdate,'yyyy-mm-dd HH24'),'yyyy-mm-dd HH24:mi:ss') AND \$CONDITIONS" --target-dir /user/jcxydb/GXTS_MJSKXX/$date_today/$hour --fields-terminated-by '\t' --lines-terminated-by '\n' --null-string '\\N' --null-non-string '\\N' --m 1;

执行这两条指令一定记得第一步的两个参数，因为这里  --target-dir /user/jcxydb/GXTS_MJSKXX/$date_today/$hour  这个参数是hdfs的目的路径，可以看到这里有两个外部传入的参数作为其中的路径。所以要先给这两个参数赋值，不然会有问题。

拓展与延伸：

这里虽然成功了，但是后面继续鼓捣的时候还遇到一个问题，还好大佬及时提出了。可以看  --fields-terminated-by '\t'  这个参数。它就是字段分割符，这里要注意hive是有自己的默认字段分隔符的，不了解的童鞋可以百度看看。是 '\001' ，所以这里我写了 '\t' 那么后面我hive在load的时候则要记得将字段分隔符改为 '\t' ，不然两边不匹配则会失败。不过为了更方便一点，我后面也将这个参数就改为  '\001' 。