欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

用sqlldr进行数据迁移 博客分类： 数据库db 数据迁移sqlldrsqlloader效率存储过程 

程序员文章站 2024-03-24 11:16:28
...

公司的业务场景如下：

某一个体系需要进行重构，其中包括表的重新设计和库表数据迁移。而这一块是我负责的，经历了从Java，到DML，到PLSQL的方案转移。之所以写这么多，是想记录我自己的探索过程，如果想看使用sqlldr进行数据迁移的直接步骤，直接跳到“方案3”的结尾处。

 

方案1：用Java进行数据迁移

最先开始选择用Java，是因为之前接触存储过程不是很多，用Java来写，操作对象集合，配置2个datasource，直接查询出来了再insert进行，比较方便。因为这个不是我们这次的重点，就不详叙。简单大致说一下，用的框架是jdbcTemplate，其中插入的时候需要注意的是，采用jdbcTemplate的batchInsert的批量提交方式，减少与数据库的交互次数，提供性能。去掉索引PK等越是后，效率大概是3w条/s。但有一个表特别大有将近2kw条记录，然后领导说，这个方案不行，让我用sqlldr。方案1流产

 

方案2：用DML语句进行数据迁移

由于没用过sqlldr啊，没办法，看想想能不能用其他的方式来做，既然公司的库都是在一个局域网，那我干嘛不直接用DBLINK来做呢？话不多说，我们上代码

-- 建立 DBLINK
create public database link my_dblink connect to old_username  identified by old_password
　　 using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =old_service)
)
)';

建好DB_LINK之后，我们直接以如下形式进行数据插入（实验证明，这也是最高效的）

insert into XXX (select colnum_a,colnum_b from old_service.YYY@my_dblink)

不要用下面这种形式，进行创建。因为这样创建，oracle会给你自动优化，你的字段类型本来想是定义为varchar2(255)的，说不定就只有varchar2(60)了，所以表还是创建好了再进行插入（PS.虽然这种方式比上面的方式快。一个是DML一个是DDL，效率肯定是不一样的）

create table XXX as (select colnum_a,colnum_b from old_service.YYY@my_dblink)

这种确确实实是最高效的，但这个方案出现了2个问题

1.有人说，你这个没法记录日志

2.领导坚持用sqlldr

没办法，那只能用sqlldr了

PS.这里我要很愤慨的说一句，数据迁移强烈建议大家用这种DML的方式进行数据插入。有人说没日志，下面的第三种方案是有日志了，但谁TM看呢，我去。第二，领导说用sqlldr，为什么？因为他觉得sqlldr快，既然他要的是快，你就告诉他，sqlldr不可能有这种DML的形式快（sqlldr慢不在于插入慢，在于前面的准备工作慢，后面我会细说sqlldr）

坑爹的，第二个方案也这样流产了

 

方案3：用sqlldr进行数据迁移

没办法，那就老老实实用sqlldr吧，不会就去学嘛。sqlldr在输入插入方面是特别快的，比insert快几个数量级，但为什么我说sqlldr的方案比第二个方案慢呢？因为sqlldr主要负责的是把数据导进去，那数据从哪来呢？

 

这里我简单说一下sqlldr吧

首先你有一个文件叫“user.csv”，内容是这样的

1,tom,23,11-AUG-14

2,jack,25,11-AUG-25

然后你有一个表USER，字段如下 id,name,age,created_time

然后用sqlldr就可以很轻松的把你文件里的数据导到表里面去，sqlldr来导，他怎么知道1对应id，tom对应name，又或者不是“tom,23”对应name呢？

 

执行sqlldr的时候，需要一个ctl文件，我们命名这个文件叫“user.ctl”，内容如下

LOAD DATA
INFILE '/home/oracle/data/user.csv'
APPEND INTO TABLE user
FIELDS TERMINATED BY ','
TRAILING NULLCOLS 
(id,name,age,created_time)

然后我们到oracle的bin目录下，确认找得到sqlldr这个命令，然后在这个目录下执行这个命令

-- silent=feedback,header表示去掉一些回显，如果不加的话，就一直在命令行打数据“插入XXX条”，这样会降低效率的
-- direct=true最好加上，这个是不考虑索引约束啊之类的，总之加了这个，数据会快很多（大概快一倍），不加他会插入的时候考虑一些别的因素，导致插入变慢。
sqlldr new_username/new_password /home/oracle/data/user.ctl silent=feedback,header direct=true

 

PS.大家插入数据的时候，最好还是只建表，不要加索引PK之类，弄好了再加，不然会特别耗时，虽然数据导完了再加索引，本身也是很耗时的- -！不过毕竟比一边插一边检查来得好一些

执行完后，就插入完毕了，去数据库检查一下数据，然后加上你的索引，唯一，PK等，这样就算是迁移完毕了。

 

是不是看上去像童话一样美好？

BUT！理论上的东西，放到现实场景下，就会出现各种问题，我这里还是想重申一个观点就是，HelloWorld确实很重要的，但你要是觉得会写HelloWorld，就能把实际案例上，工作中的问题解决了，那就大错特错了

 

首先我们来说一下用sqlldr会出现的坑

坑1：数据哪来呢？

我们这个demo里面是2条数据，都是我们手写的，但生产上，数据2条吗？2kw还差不多，那这2kw数据怎么来。目前我所知道的比较快捷的方法是用PL/SQL工具来导出是最快的，把你需要的select语句写好，然后放到PL/SQL中去执行，选择导出为CSV文件


用sqlldr进行数据迁移 博客分类： 数据库db 数据迁移sqlldrsqlloader效率存储过程 
这样导是很快的，2kw数据也用不了10分钟，但如果这么轻轻松松就搞定了，还叫坑吗？

不要急，我们一步步发现问题，一步步来解决问题

 

坑2：导出文件有问题？

我们先看看导出来的数据是什么样子的（这里我们就先只导了4条）

用sqlldr进行数据迁移 博客分类： 数据库db 数据迁移sqlldrsqlloader效率存储过程 

首先我们看到，用这个“导出为CSV文件”这个功能，导出来的会自带一条记录--“字段名称”，显然如果我们要用sqlldr来把这个“字段名称”插进去肯定是不行的，需要我们删掉第一行

（注：sqlldr可能有参数设定，不扫描第一行，又或者说这个“导出为CSV文件”功能，可能可以不导这个字段行。这2个都有可能，但笔者没试过，如果有试过的朋友，麻烦评论一下，也好帮助大家）

 

坑3：单元数据如何分隔

接着上面的坑，不管能不能让他不出现第一行，就算是出现，也可以手动删掉第一行，但问题大头不仅仅是在这第一行的问题上折腾。大家看一下 它的规则，他是用引号进行包裹，然后以逗号分隔，那你觉得某个表的memo字段（备注信息），可不可能出现引号和冒号的情况呢？答案是，肯定有！

 

鉴于对PL/SQL的”导出为CSV文件“功能不熟，我们只能另寻他路，既然摆脱工具的束缚，就不会出现上面所述的坑，既然这样那我们就自己来写数据

 

怎么写数据？答案是跑存储过程

我们也不卖关子了，现在一次性把所有可能遇到的坑全部说出来，再上代码

 

1.确保你的分隔符不会出现在数据信息中

比如一开始我想用”{“来做分隔符，结果我发现有这样的数据：”用户备注{用户明天来取货}“。然后我想用”|||“来做分隔符，没有人用这个吧，结果发现又有这样的数据”- -|||“

总结：选分隔符的时候，尽量选少重复的，然后对各个可能出现这个分隔符的做查询，有则修改，比如把所有的”|||“改成空字符等

 

2.去掉换行符

因为sqlldr默认是一行数据为一条记录，有的字段，他有回车换行符，这样导致你得到的数据会换行，这时候sqlldr会认为是2条记录，这样肯定是会插入失败的

 

3.时间问题

时间建议大家还是将date数据转为字符串，然后读取的时候，再按指定格式的字符串来转换，不然有时候是真的会出现不识别的情况的（测试环境直接拉下来的date可以用sqlldr，生产环境拉下来的date由于编码不一样，变成中文了，导致sqlldr不识别）

然后在ctl文件中，字段后面加上 "TO_DATE(:created_time,'yyyy-mm-dd hh24:mm:ss')"，当然你写出来的date，也要是这种格式的

 

4.字段数据过长

如果不指定，sqlldr有一个默认最长字符的限制，sqlldr默认字符串的最大长度为255，多于255的会舍弃，但我们有的字段，长度确确实实是多于255，那怎么解决呢？

好解决，在ctl文件中，字段后面加上”char(2000)“，具体多少根据你的实际需求来

话不多说，上代码

 

写数据文件的存储过程函数

create or replace directory MY_DIR
  as '/home/oracle/data';

declare
  file_out  Utl_File.file_type;
  log_out   Utl_File.file_type;
  file_path varchar2(100);
  file_name varchar2(100);
  log_name  varchar2(100);
  count_sum number(10);
  date_start Date;
  date_end Date;

  cursor c is (
  select
   r.id ID,
   s.id UID_ORI,
   replace(replace(r.password,CHR(10),''),CHR(13),'') TX_PASSWORD,
   s.password LOGIN_PASSWORD,
   s.username SYSTEM_NAME,
   replace(replace(r.name,CHR(10),''),CHR(13),'') NAME,
   case
     when r.gender like '%男%' then
      0
     when r.gender = 'male' then
      0
     when r.gender like '%女%' then
      1
     when r.gender = 'female' then
      1
     else
      NULL
   end GENDER,
   r.gender GENDER_ORI,
   r.status_code STATUS_CODE,
   replace(replace(s.email_validate,CHR(10),''),CHR(13),'') EMAIL,
   substr(replace(replace(s.realname,CHR(10),''),CHR(13),''),1,20) NICKNAME,
   replace(replace(r.remark,CHR(10),''),CHR(13),'') REMARK,
   r.application_source_code FROM_SYSTEM,
   decode(r.created_by, NULL, 'Admin', replace(replace(r.created_by,CHR(10),''),CHR(13),'')) CREATED_BY,
   r.created_by created_by_ORI,
   to_char(decode(r.created_timestamp, NULL, sysdate, r.created_timestamp),'yyyy-mm-dd hh24:mi:ss') CREATED_TIME,
   r.created_timestamp created_timestamp_ORI,
   decode(r.updated_by, NULL, 'Admin', replace(replace(r.updated_by,CHR(10),''),CHR(13),'')) UPDATED_BY,
   r.updated_by updated_by_ORI,
   to_char(decode(r.updated_timestamp, NULL, sysdate, r.updated_timestamp),'yyyy-mm-dd hh24:mi:ss') UPDATED_TIME,
   r.updated_timestamp updated_timestamp_ORI
    from crm_customer r, hyt_user_asso o, bbs_members s
   where o.biostime_id = to_char(r.id)
     and o.mama100_username = s.username);
begin
  file_path := 'MY_DIR';
  file_name := 'cust_customer_2.csv';
  log_name  := 'cust_customer_2.log';
  file_out  := Utl_File.fopen(file_path, file_name, 'W');
  log_out   := Utl_File.fopen(file_path, log_name, 'W');
  count_sum := 0;
  date_start := sysdate;

  Utl_File.put_line(log_out,'=====================CUST_CUSTOMER_2开始，导出与BBS_MEMBERS相关联的记录=======================');
  for my_c in c loop
    Utl_File.put_line(file_out,my_c.ID||'|||'||my_c.TX_PASSWORD||'|||'||my_c.LOGIN_PASSWORD||'|||'||my_c.SYSTEM_NAME||'|||'||my_c.NAME||'|||'||my_c.GENDER||'|||'||my_c.STATUS_CODE||'|||'||my_c.EMAIL||'|||'||my_c.NICKNAME||'|||'||my_c.REMARK||'|||'||my_c.FROM_SYSTEM||'|||'||my_c.CREATED_BY||'|||'||my_c.CREATED_TIME||'|||'||my_c.UPDATED_BY||'|||'||my_c.UPDATED_TIME);
    count_sum := count_sum + 1;
  end loop;
  date_end := sysdate;
  Utl_File.put_line(log_out,'========CUST_CUSTOMER_2结束=========导出数据：'||count_sum||'==========耗时：'||(date_end-date_start)*24*60*60||'秒');
  Utl_File.fclose(file_out);
  Utl_File.fclose(log_out);
end;

 

sqlldr所需要的ctl文件内容

LOAD DATA
INFILE '/home/oracle/data/cust_customer_2.csv'
APPEND INTO TABLE cust_customer
FIELDS TERMINATED BY '|||'
TRAILING NULLCOLS 
(ID,TX_PASSWORD,LOGIN_PASSWORD,SYSTEM_NAME,NAME,GENDER,STATUS_CODE,EMAIL,NICKNAME,REMARK char(2000),FROM_SYSTEM,CREATED_BY,CREATED_TIME "TO_DATE(:created_time,'yyyy-mm-dd hh24:mm:ss')",UPDATED_BY,UPDATED_TIME "TO_DATE(:updated_time,'yyyy-mm-dd hh24:mm:ss')")

最后切换到oracle的bin目录，执行sqlldr命令

sqlldr new_username/new_password /home/oracle/data/cust_customer_2.ctl silent=feedback,header direct=true

 

经测试，2000w数据，写出来需要20分钟，导进去仅需要3~5分钟

 

总结就是，sqlldr在进行数据导入是非常迅速的，但很多时候问题会变成，如何得到数据文件，一般都是自己写出来，如果是通过工具生成，就得考虑我上面说的那几个坑了，我把生产环境的所有问题，尽可能的给大家罗列出来，还是那句话，HelloWorld是很重要的，但如果你要是觉得你会HelloWorld，其他的就七七八八差不多，那就大错特错了。记录自己，与君共勉，加油！！！

 

 

 

 

 

 

 

 

 

 

 

 

  • 用sqlldr进行数据迁移 博客分类： 数据库db 数据迁移sqlldrsqlloader效率存储过程 
  • 大小: 46.6 KB
  • 用sqlldr进行数据迁移 博客分类： 数据库db 数据迁移sqlldrsqlloader效率存储过程 
  • 大小: 5.3 KB
相关标签： 数据迁移 sqlldr sqlloader 效率 存储过程

上一篇： Katz平滑的实现

下一篇： 复制指定目录下的全部文件到另一个目录中

推荐阅读