【大数据开发运维解决方案】sqoop增量导入oracle/mysql数据到hive时时间字段为null处理

前面几篇文章详细介绍了sqoop全量增量导入数据到hive，比如文章（sqoop避免输入密码自动增量job脚本介绍）里面的案例和给出的其他相关文章的连接，笔者使用sqoop导入oracle数据到hive是为了做增量cube用，但是在前面的某篇文章Sqoop全量同步mysql/Oracle数据到hive中大家可以看到我导入的数据如果有时间字段的话我都是在hive指定成了string类型，虽然这样可以处理掉时间字段在hive为空的问题，但是在kylin创建增量cube时需要指定一个时间字段来做增量，所以上面那种方式不行，这里的处理方式为把string改成timestamp类型，看实验：

一、源库创建测试表

创建测试表：

create table INR_TR
(
  empno    NUMBER,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  sal      NUMBER(7,2),
  etltime  DATE,
  hiredate VARCHAR2(20),
  birth    VARCHAR2(20)
);

初始化数据：

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (1, 'er', 'CLERK', 800.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2017', '2017-3-27');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (2, 'ALLEN', 'SALESMAN', 1600.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2017', '2017-3-27');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (3, 'WARD', 'SALESMAN', 1250.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2017', '2017-3-27');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (4, 'JONES', 'MANAGER', 2975.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2018', '2018-3-27');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (5, 'MARTIN', 'SALESMAN', 1250.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2017', '2017-3-27');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (6, 'zhao', 'DBA', 1000.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2018', '2018-3-28');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (7, 'yan', 'BI', 100.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2018', '2018-3-28');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (8, 'dong', 'JAVA', 400.00, to_date('22-03-2019 17:24:42', 'dd-mm-yyyy hh24:mi:ss'), '2017', '2017-3-28');

insert into inr_tr (EMPNO, ENAME, JOB, SAL, ETLTIME, HIREDATE, BIRTH)
values (9, 'test', 'test', 999.00, to_date('25-03-2019 18:54:39', 'dd-mm-yyyy hh24:mi:ss'), '2018', '2018-3-28');
commit;

接下来去hive创建表，这里指定etltime 列数据类型为string，birth 字段指定为timestamp类型：

create table INR_TR
(
  empno   int,
  ename   string,
  job     string,
  sal     float,
  etltime string,
  hiredate int,
  birth    timestamp
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
location '/user/hive/warehouse/exter_inr_tr'; 

二、初始化

接下来通过sqoop对INR_TR做全量初始化：

[root@hadoop ~]# hadoop fs -rmr /user/hive/warehouse/exter_inr_tr
[root@hadoop ~]# sqoop import --connect jdbc:oracle:thin:@192.168.1.6:1521:orcl --direct --username scott --password tiger --query “SELECT EMPNO,ENAME,JOB,SAL,ETLTIME,to_number(HIREDATE),to_date(BIRTH,‘yyyy-mm-dd’) FROM INR_TR WHERE $CONDITIONS” -m 1 --target-dir /user/hive/warehouse/exter_inr_tr --fields-terminated-by ‘\t’
Warning: /hadoop/sqoop/…/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
19/03/27 15:54:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/03/27 15:54:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/03/27 15:54:47 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop will not process this sqoop connection, as an insufficient number of mappers are being used.
19/03/27 15:54:47 INFO manager.SqlManager: Using default fetchSize of 1000
19/03/27 15:54:47 INFO tool.CodeGenTool: Beginning code generation
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/hadoop/hbase/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/hadoop/hive/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
19/03/27 15:54:48 INFO manager.OracleManager: Time zone has been set to GMT
19/03/27 15:54:48 INFO manager.SqlManager: Executing SQL statement: SELECT EMPNO,ENAME,JOB,SAL,ETLTIME,to_number(HIREDATE),to_date(BIRTH,‘yyyy-mm-dd’) FROM INR_TR WHERE (1 = 0)
19/03/27 15:54:48 INFO manager.SqlManager: Executing SQL statement: SELECT EMPNO,ENAME,JOB,SAL,ETLTIME,to_number(HIREDATE),to_date(BIRTH,‘yyyy-mm-dd’) FROM INR_TR WHERE (1 = 0)
19/03/27 15:54:48 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /hadoop
Note: /tmp/sqoop-root/compile/b958039e61f21d341fd0c9dc1d3d5ea5/QueryResult.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
19/03/27 15:54:51 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/b958039e61f21d341fd0c9dc1d3d5ea5/QueryResult.jar
19/03/27 15:54:51 INFO mapreduce.ImportJobBase: Beginning query import.
19/03/27 15:54:51 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/03/27 15:54:52 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/03/27 15:54:52 INFO client.RMProxy: Connecting to ResourceManager at /192.168.1.66:8032
19/03/27 15:54:56 INFO db.DBInputFormat: Using read commited transaction isolation
19/03/27 15:54:56 INFO mapreduce.JobSubmitter: number of splits:1
19/03/27 15:54:56 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1553650718296_0005
19/03/27 15:54:57 INFO impl.YarnClientImpl: Submitted application application_1553650718296_0005
19/03/27 15:54:57 INFO mapreduce.Job: The url to track the job: http://hadoop:8088/proxy/application_1553650718296_0005/
19/03/27 15:54:57 INFO mapreduce.Job: Running job: job_1553650718296_0005
19/03/27 15:55:06 INFO mapreduce.Job: Job job_1553650718296_0005 running in uber mode : false
19/03/27 15:55:06 INFO mapreduce.Job: map 0% reduce 0%
19/03/27 15:55:21 INFO mapreduce.Job: map 100% reduce 0%
19/03/27 15:55:22 INFO mapreduce.Job: Job job_1553650718296_0005 completed successfully
19/03/27 15:55:22 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=144127
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=87
HDFS: Number of bytes written=604
HDFS: Number of read operations=4
HDFS: Number of large read operations=0
HDFS: Number of write operations=2
Job Counters
Launched map tasks=1
Other local map tasks=1
Total time spent by all maps in occupied slots (ms)=11694
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=11694
Total vcore-milliseconds taken by all map tasks=11694
Total megabyte-milliseconds taken by all map tasks=11974656
Map-Reduce Framework
Map input records=9
Map output records=9
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1462
CPU time spent (ms)=10320
Physical memory (bytes) snapshot=198336512
Virtual memory (bytes) snapshot=2135072768
Total committed heap usage (bytes)=96993280
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=604
19/03/27 15:55:22 INFO mapreduce.ImportJobBase: Transferred 604 bytes in 29.927 seconds (20.1825 bytes/sec)
19/03/27 15:55:22 INFO mapreduce.ImportJobBase: Retrieved 9 records.

看一下hdfs文件内容：

[root@hadoop ~]# hdfs dfs -cat /user/hive/warehouse/exter_inr_tr/part-m-00000
1	er	CLERK	800	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00.0
2	ALLEN	SALESMAN	1600	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00.0
3	WARD	SALESMAN	1250	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00.0
4	JONES	MANAGER	2975	2019-03-22 17:24:42.0	2018	2018-03-27 00:00:00.0
5	MARTIN	SALESMAN	1250	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00.0
6	zhao	DBA	1000	2019-03-22 17:24:42.0	2018	2018-03-28 00:00:00.0
7	yan	BI	100	2019-03-22 17:24:42.0	2018	2018-03-28 00:00:00.0
8	dong	JAVA	400	2019-03-22 17:24:42.0	2017	2017-03-28 00:00:00.0
9	test	test	999	2019-03-25 18:54:39.0	2018	2018-03-28 00:00:00.0

查一下hive表：

hive> select * from inr_tr;
OK
1	er	CLERK	800.0	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00
2	ALLEN	SALESMAN	1600.0	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00
3	WARD	SALESMAN	1250.0	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00
4	JONES	MANAGER	2975.0	2019-03-22 17:24:42.0	2018	2018-03-27 00:00:00
5	MARTIN	SALESMAN	1250.0	2019-03-22 17:24:42.0	2017	2017-03-27 00:00:00
6	zhao	DBA	1000.0	2019-03-22 17:24:42.0	2018	2018-03-28 00:00:00
7	yan	BI	100.0	2019-03-22 17:24:42.0	2018	2018-03-28 00:00:00
8	dong	JAVA	400.0	2019-03-22 17:24:42.0	2017	2017-03-28 00:00:00
9	test	test	999.0	2019-03-25 18:54:39.0	2018	2018-03-28 00:00:00

从上面结果已经能看到时间列已经是timestamp格式了。