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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 前面几篇文章详细介绍了sqoop全量增量导入数据到hive,大家可以看到我导入的数据如果有时间字段的话我都是在hive指定成了string类型,虽然这样可以处理掉时间字段在hive为空的问题,但是在kylin创建增量cube时需要指定一个时间字段来做增量,所以上面那种方式不行,这里的处理方式为把string改成timestamp类型,看实验:

前面几篇文章详细介绍了sqoop全量增量导入数据到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格式了。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL 分布式计算 大数据
大数据处理平台Hive详解
【7月更文挑战第15天】Hive作为基于Hadoop的数据仓库工具,在大数据处理和分析领域发挥着重要作用。通过提供类SQL的查询语言,Hive降低了数据处理的门槛,使得具有SQL背景的开发者可以轻松地处理大规模数据。然而,Hive也存在查询延迟高、表达能力有限等缺点,需要在实际应用中根据具体场景和需求进行选择和优化。
|
3天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
20 3
|
8天前
|
关系型数据库 MySQL 数据库
MySQL 保姆级教程(八):创建计算字段
MySQL 保姆级教程(八):创建计算字段
|
6天前
|
关系型数据库 MySQL
mysql使用 CONCAT(字段,字段) 函数拼接
mysql使用 CONCAT(字段,字段) 函数拼接
|
16天前
|
存储 弹性计算 大数据
阿里云ECS以其强大的弹性计算与存储能力,为大数据处理提供了灵活、高效、成本优化的解决方案
阿里云ECS在大数据处理中发挥关键作用,提供多样化实例规格适应不同需求,如大数据型实例适合离线计算。ECS与OSS集成实现大规模存储,通过Auto Scaling动态调整资源,确保高效运算。案例显示,使用ECS处理TB级数据,速度提升3倍,成本降低40%,展现其在弹性、效率和成本优化方面的优势。结合阿里云生态系统,ECS助力企业数据驱动创新。
31 1
|
20小时前
|
分布式计算 DataWorks 调度
MaxCompute产品使用合集之如何将数据迁移到CDH Hive
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
26天前
|
存储 关系型数据库 MySQL
解读 MySQL 容器信息:`docker inspect` 字段详解
解读 MySQL 容器信息:`docker inspect` 字段详解
40 1
|
12天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
10天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
979 6
Mysql 数据库主从复制
|
10天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。

热门文章

最新文章

推荐镜像

更多