当前位置: 首页>数据库>正文

java sql执行中线程中断 sql还会在继续执行吗 java执行sqlldr

公司的业务场景如下:

某一个体系需要进行重构,其中包括表的重新设计和库表数据迁移。而这一块是我负责的,经历了从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文件

java sql执行中线程中断 sql还会在继续执行吗 java执行sqlldr,java sql执行中线程中断 sql还会在继续执行吗 java执行sqlldr_数据迁移,第1张

这样导是很快的,2kw数据也用不了10分钟,但如果这么轻轻松松就搞定了,还叫坑吗?

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

 

坑2:导出文件有问题?

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

java sql执行中线程中断 sql还会在继续执行吗 java执行sqlldr,java sql执行中线程中断 sql还会在继续执行吗 java执行sqlldr_sqlldr_02,第2张

首先我们看到,用这个“导出为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,其他的就七七八八差不多,那就大错特错了。记录自己,与君共勉,加油!!!

 

 

 

 

 

 

 

 

 

 

 

 


https://www.xamrdz.com/database/6es1924660.html

相关文章: