ORACLE迁移MYSQL之load和ETL

前言

     ORACLE和MYSQL是目前市面上使用最广泛的两款关系型数据库软件，因为两款数据库在存储过程，函数，触发器和sql等语法上存在较大差异，所以迁移一套完整的ORACLE 到MYSQL，需要处理好不同数据类型的差异和各种编码的差异。此文章主要分享了迁移数据上的一些方法和数据类型上的一些区别对比和选择。

环境说明：

源数据库（ORACLE 数据库）：
ip：192.169.100.107 单机

目标数据库（mysql数据库），目标端需要安装ORACLE客户端，或者直接复制ORACLE的lib库到目标库。
ip : 192.169.100.247 单机

迁移前的数据类型对比：

 注意：

   1.mysql中的date类型为日期类型（YYYY-MM-DD） 范围：1000-01-01/9999-12-31 ，不包含时间值，所以可根据情况判断使用date类型还是datetime类型替换oracle的date类型（取决于源端是否包含具体时间），推荐使用datetime 替换date类型。

   2.如果oracle源端使用的日期格式默认值时（default sysdate），

datetime和timestamp可以添加默认值

drop table datetest1;
create table datetest1(id int,`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间');

建议使用dateime作为时间字段，因为timestamp时间字段的范围有限制

 



 




   3.mysql 端默认字符集推荐使用：utf8mb4。

NUMBER(*,0)  最大限制可以保留126位数字
decimal 最大限制为64位数字
所以建议NUMBER(*,0)  对应到 decimal(64,0),或者int/mediumint/bigint(不够大 bigint 才19位)。






 


一、load的方式迁移数据。


       对于生产环境的数据库，动辄上TB 甚至PB级的数据量和数千张表，对于这样的数据量和表的数量，我们就需要考虑比较快捷的方式去迁移数据。


      数据的导出有很多有方式，比如oralce可以使用：PL/SQL、toad、Navicat for ORACLE或者其他工具导出数据，但是这些工具往往会因为数据量的问题而受到各种局限，对于少量的数据时，使用这些工具是比较合适的，但是当数据量达到海量时，不仅导出速度无法保证，而且无法直接落地到服务器，从而大大的限制了我们对数据的处理。


     因此我推荐一个小的工具包sqlload2（或者也可以直接使用ketle将数据直接导出）,该工具可快速的将源数据导出成为txt/csv(推荐csv格式，csv格式可以更好的处理分隔符和封闭符的问题)，因为在正式环境中，导入的数据的正确性尤为重要，生产数据又可能出现各种特殊的符号，仅仅使用传统的逗号作为分隔符已无法完全保证数据在导出或者导入时能正确的分隔，所以需要同时使用分隔符和封闭符，这样才能保证数据的正确性，经过多次验证建议使用特使的16进制字符：0x07 作为分隔符,并且该工具支持并行导出、以及多种分隔符、封闭符、自动拆分文件和通配符等等丰富的功能。


    

  1. 迁移流程(以下模拟一个源库包含大量数据，并且业务无法长时间停止，需要增量更新)
    2. 

  2.  需要用到的工具列表：
    3. 





1.ORACLE文件导出工具sqlludr2 


链接：https://pan.baidu.com/s/1JVo1BETvTJXPQQHburfVOg 
提取码：fxwf 
复制这段内容后打开百度网盘手机App，操作更方便哦


  2.ETL工具ketle


    kettle可自行到官网下载：https://www.hitachivantara.com/en-us/products/data-management-analytics.html?source=pentaho-redirect（kettle为开源软件，后续也有推出收费版本）

















        3.使用sqlldr2导出数据并使用load加载数据：




1.安装ORACLE客户端，并配置好环境变量：


安装之后创建network/admin/文件夹，然后创建文件tnsnames.ora（用于连接源端ORACLE数据库）
zkl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl )
      (SERVER=DEDICATED )
　　　 (INSTANCE_NAME = testdb1)
  )


2.配置好oracle的环境变量，指定lib 库


export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORALCE_HOME/jlib:ORACLE_HOME/rdbms/jlib


  3.上传sqlludr2工具并使用如下命令导出数文件


./sqluldr2_linux64_10204.bin USER=用户名/密码@IP地址：端口号/数据库名称 query="查询语句" table=表名称 head=no charset=utf8 field=0x07 file=/sqlfile^CJ_WXMACINFO.csv log=/sqlfile/log.txt file=/sqlfile/data/WJ_WXMACINFO%B.CSV size=20MB safe=yes


常用参数说明：（更多参数可以参考：./sqluldr2_linux64_10204.bin help=yes）


query:query参数如果整表导出，可以直接写表名，如果需要查询运算和where条件，query=“sql文本”，也可以把复杂sql写入到文本中由query调用。
head：是否导出表头
charset：指定字符集
field：默认是逗号分隔符，通过field参数指定分隔符
file：导出的文件名
log：日志文件
size:对于大表可以输出到多个文件中，指定行数分割或者按照文件大小分割rows=500000


示例：（导出db库里面的test表里面的前10条数据，并且分4个并行线程，导出的文件每20M截断）
./sqluldr2_linux64_10204.bin USER=test/test@10.10.10.10/db query="select /*+ parallel(4) */ *from test where rownum<=100000" table=test head=no charset=utf8 field=0x07 file=/tmp/test.csv log=/tmp/log.txt file=/tmp/test%B.CSV size=20MB safe=yes


后续可写入脚本批量导出。


  4.使用load在目标数据库批量入库：
    load data infile '/tmp/test.csv' into table db.test character set utf8 fields terminated by '0x07' enclosed by '"';

















二、kettle做增量更新。


    1.kettle简介


       ETL是数据抽取（Extract）、清洗（Cleaning）、转换（Transform）、装载（Load）的过程。是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。（典型的ETL工具：商业软件：Informatica、IBM Datastage、Oracle ODI、Microsoft SSIS…开源软件：Kettle、Talend、CloverETL、Kettle，Octopus …）


      Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行，绿色无需安装，数据抽取高效稳定。Kettle 中文名称叫水壶，该项目的主程序员MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。


      Kettle这个ETL工具集，它允许你管理来自不同数据库的数据，通过提供一个图形化的用户环境来描述你想做什么，而不是你想怎么做。


      Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。


    2.下载和安装


1. kettle是一款开源软件，下载过后无需安装，解压到任意目录即可。 由于Kettle 是采用java 编写，因此需要在本地有JVM 的运行环境；
2.找到解压目录下的data-integration，然后找到Spoon.bat （linux下找到spoon.sh），右键编辑设置java环境变量：
    a.右键编辑Spoon.bat




           b.配置java环境变量




          c.配置java运行时允许的虚拟内存大小（如果业务量较大，建议调整）




           d.右键以管理员方式运行Spoon.bat，启动kettle程序:




         3.配置资源库


    资源库是指用来存储kettle的配置信息的原数据库，我们可以将我们所有的kettle转换和job，以及数据库连接信息直接存储在数据库中，这样就不用每次新建转换时都去配置数据库连接。（该配置对全局生效）


       a.连接资源库




b.弹出密码框，默认密码是admin,选择+ 号新增一个资源库，然后选择第二个数据库作为资源库（也可以根据需求选择其他作为资源库），然后再点击新建，并设置好资源库名称。


       


         c.然后在弹出的对话框中配置好数据库的连接信息（连接前需要先将数据库的驱动放到kettle-6.1.0.1-196\data-integration\lib 目录下）：




     4.新建转换，配置增量更新：


            a.在核心对象中分别选择两个表输入，作为旧数据源和新数据源，分别配置数据库连接；


            b.字段选择用来规范来源数据的格式和类型；


            c.合并记录用于对比新旧数据源的差异，并将数据对比的结果放到标志字段中；


            d.Switch/Case用来决定差异的数据是应该更新还是修改还是删除。




           新数据源：


         


 


 


          旧数据源：


       


 


 


 


               合并记录：（一定要先排序，不然比较的结果会有问题）


               注意：合并记录的使用前提是2个数据源都按比较关键字排过序，否则合并之后的数据不准确，可能会多出很多。


               


 


 


               


该步骤用于将两个不同来源的数据合并，这两个来源的数据分别为旧数据和新数据，该步骤将旧数据和新数据按照指定的关键字匹配、比较、合并。


需要设置的参数：


旧数据来源：旧数据来源的步骤（需要更新的库）


新数据来源。新数据来源的步骤（更新数据的源库）


标志字段：设置标志字段的名称，标志字段用于保存比较的结果，比较结果有下列几种。


1. “identical” – 旧数据和新数据一样


2. “changed” – 数据发生了变化;


3. “new” – 新数据中有而旧数据中没有的记录


4. “deleted” –旧数据中有而新数据中没有的记录


关键字段：用于定位两个数据源中的同一条记录。


比较字段：对于两个数据源中的同一条记录中，指定需要比较的字段。


合并后的数据将包括旧数据来源和新数据来源里的所有数据，对于变化的数据，使用新数据代替旧数据，同时在结果里用一个标示字段，来指定新旧数据的比较结果。


注意：


旧数据和新数据需要事先按照关键字段排序。


旧数据和新数据要有相同的字段名称。


 


Switch/Case 判断：


              


               标志字段的取值包含如下4种情况：


               


 


       更新


            


 


 


       删除：


        


 


 


   5.新建ob,并配置定时任务，即可完成自动增量更新：


       a.新建job：


         


       b.将通用对象中的start 、转换和成功三个空间拖入到job中，并连线




 


      c.选择好创建的转换，然后保存：




     d.配置定时任务：（也可以通过windows或者linux脚本进行调用）


        




windows下调用kettle程序：
cd C:\soft\kettle\data-integration
kitchen /file C:\soft\job名称 /level Basic /logfile E:\timing.log
@pause

linux下调用kettle程序：
./kitchen.sh -rep 192.168.0.13.PDI_Repository -user username -pass password -dir /目录名称 -job job名称 -level=basic>>/log/job.log




/data/kettle-6.1.0.1-196/kettle-6.1.0.1-196/kettle-6.1.0.1-196/data-integration/kitchen.sh -rep=etl -user=admin -pass=admin -dir=/gkdb_center -job=test.jkb -level:basic>>/tmp/kettle_job.log




/data/kettle-6.1.0.1-196/kettle-6.1.0.1-196/kettle-6.1.0.1-196/data-integration/kitchen.sh -norep -file=/data/kettle-6.1.0.1-196/ktr/test.kjb -log=/tmp/log.log





./kitchen.sh -rep=kettle1 -user=admin -pass=admin -level=Basic -job=job




/data/kettle-6.1.0.1-196/kettle-6.1.0.1-196/kettle-6.1.0.1-196/data-integration/kitchen.sh -norep -file=/data/kettle-6.1.0.1-196/ktr/test.kjb -log=/tmp/log.log




/root/etl/kettle-6.1.0.1-196/kettle-6.1.0.1-196/kettle-6.1.0.1-196/data-integration/pan.sh -file=/root/etl/test.ktr -log=/root/etl/etl.log


 




 至此使用load+kettle的的迁移和增量更新配置完成。 


 


mysql 快速添加注释：（通过以下的sql 批量获取需要添加注释的表的sql）




SELECT
concat(
    'alter table ',
    table_schema, '.', table_name,
    ' modify column ', column_name, ' ', column_type, ' ',
    if(is_nullable = 'YES', ' ', 'not null '),
    if(column_default IS NULL, '',
        if(
            data_type IN ('char', 'varchar')
            OR
            data_type IN ('date', 'datetime', 'timestamp') AND column_default != 'CURRENT_TIMESTAMP',
            concat(' default ''', column_default,''''),
            concat(' default ', column_default)
        )
    ),
    if(extra is null or extra='','',concat(' ',extra)),
    ' comment ''', column_comment, ''';'
) '组合语句'
FROM information_schema.columns
WHERE table_schema = 'test2'
    AND table_name = 'tag';




 


 


kettle自动将空字符串 ‘’ 转换为null的问题：


kettle会自动将空字符串转换为null，这将可能导致kettle在插入数据的时候无法插入（提示字段xx无法为空，但是该值得默认值又是‘’空字符串）


结局方案：


在C:\Users\用户名\.kettle目录中找到kettle.properties文件，增加：


KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y


 







            

            
posted @ 
2020-10-16 16:45 
数据库小白（专注） 
阅读(941) 
评论(0) 
编辑 
收藏 
举报