ITPub博客

首页 > 数据库 > Oracle > SQL*Loader的使用总结(三)

SQL*Loader的使用总结(三)

原创 Oracle 作者:梓沐 时间:2016-02-15 10:29:42 0 删除 编辑
SQL*Loader对不同文件及格式的处理方法
1.多个数据文件,导入同一张表
通常对于逻辑比较复杂的系统可能存在这种情况,因为导出的数据来源于多个系统, 因此可能提供给DBA的也是多个数据文件。这种情况并不一定需要执行多次加载,只需要在控制文件中做适当配置即可。不过有一点非常重要,提供的数据文件中的数据存放格式必须完全相同。

创建演示表Manager表
  1. --创建Manager表
    CREATE TABLE MANAGER
    (
    MGRNO     NUMBER,
    MNAME     VARCHAR2(30),
    JOB       VARCHAR2(300),
    REMARK    VARCHAR2(4000)
    );
    Table created.

有多个数据文件,分别如下:
  1. --数据文件1
    [oracle@cancer ~]$ cat ldr_case8_1.dat
    10,SMITH,SALES MANAGER
    11,ALLEN.W,TECH MANAGER
    16,BLAKE,HR MANAGER

    --数据文件2
    [oracle@cancer ~]$ cat ldr_case8_2.dat
    12,WARD,SERVICE MANAGER
    13,TURNER,SELLS DIRECTOR
    15,JAMES,HR DIRECTOR

    --数据文件3
    [oracle@cancer ~]$ cat ldr_case8_3.dat
    17,MILLER,PRESIDENT

创建控制文件,制定多个INFILE参数即可,控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case8.ctl
    LOAD DATA
    INFILE ldr_case8_1.dat
    INFILE ldr_case8_2.dat
    INFILE ldr_case8_3.dat
    TRUNCATE INTO TABLE MANAGER
    FIELDS TERMINATED BY ","
    (MGRNO,MNAME,JOB)

执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case8.ctl
    Commit point reached - logical record count 3
    Commit point reached - logical record count 6
    Commit point reached - logical record count 7

    --查看结果
    SQL> select * from manager;
    MGRNO      MNAME      JOB              REMARK
    ---------- ---------- ---------------- ----------
    10         SMITH      SALES MANAGER
    11         ALLEN.W    TECH MANAGER
    16         BLAKE      HR MANAGER
    12         WARD       SERVICE MANAGER
    13         TURNER     SELLS DIRECTOR
    15         JAMES      HR DIRECTOR
    17         MILLER     PRESIDENT

2.同一个数据文件,导入不同表
控制文件提供了多种逻辑判断方式,只要能把逻辑清晰地描述出来,SQL*Loader就能 按照指定的逻辑执行加载。
数据文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case9.dat
    BON  SMITH CLEAK       3904
    BON  ALLEN SALER,M     2891
    BON  WARD  SALER,"S"   3128
    BON  KING  PRESIDENT   2523
    MGR  10 SMITH    SALES MANAGER
    MGR  11 ALLEN.W  TECH MANAGER
    MGR  16 BLAKE    HR MANAGER
    TMP  SMITH 7369 CLERK    1020 20
    TMP  ALLEN 7499 SALESMAN 1930 30
    TMP  WARD  7521 SALESMAN 1580 30
    TMP  JONES 7566 MANAGER  3195 20

需求是将MGR开头的记录导入到Manager表,以BON开头的记录导入到BONUS表,其他记录存放到废弃文件中,创建控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case9.ctl
    LOAD DATA
    INFILE ldr_case9.dat
    DISCARDFILE ldr_case9.dsc
    TRUNCATE
    INTO TABLE BONUS
    WHEN TAB='BON'
    (
    TAB FILLER POSITION(1:3),
    ENAME POSITION(6:10),
    JOB POSITION(*+1:20),
    SAL POSITION(*+3:27)
    )
    INTO TABLE MANAGER
    WHEN TAB='MGR'
    (
    TAB FILLER POSITION(1:3),
    MGRNO POSITION(6:7),
    MNAME POSITION(9:15),
    JOB POSITION(*+2:30)
    )

虽然这个控制文件看起来比之前的都要复杂,但只有一个新语法,即关键字,我们这里通过WHEN来实现判断,很容易理解。同时,指定了DISCARDFILE参数,以生成不满足加载条件的废弃文件,如果你有心,不妨等执行完sqlldr命令后査看ldr_case9.dsc文件和ldr_case9.log文件。
另外注意,控制文件中WHEN逻辑判断不支持OR关键字,因此如果你的判断条件有多个,则只能通过AND连接,而不能直接使用OR。
执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case9.ctl
    Commit point reached - logical record count 12

    --查看结果
    SQL> select * from bonus;
    ENAME      JOB       SAL        COMM
    ---------- --------- ---------- ----------
    SMITH      CLEAK     3904
    ALLEN      SALER,M   2891
    WARD       SALER,"S" 3128
    KING       PRESIDENT 2523

    SQL> select * from manager;
    MGRNO   MNAME      JOB                  REMARK
    ------- ---------- -------------------- ----------
    10      SMITH      SALES MANAGER
    11      ALLEN.W    TECH MANAGER
    16      BLAKE      HR MANAGER

这里贴一下log日志和废弃日志
  1. --log日志
    [oracle@cancer ~]$ cat ldr_case9.log

    Control File:   ldr_case9.ctl
    Data File:      ldr_case9.dat
     Bad File:     ldr_case9.bad
     Discard File: ldr_case9.dsc
    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array:     64 rows, maximum of 256000 bytes
    Continuation:    none specified
    Path used:      Conventional

    Table BONUS, loaded when TAB = 0X424f4e(character 'BON')
    Insert option in effect for this table: TRUNCATE

      Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    TAB                                   1:3     3           CHARACTER            
     (FILLER FIELD)
    ENAME                                6:10     5           CHARACTER            
    JOB                             NEXT+1:20    19           CHARACTER            
    SAL                             NEXT+3:27    24           CHARACTER            

    Table MANAGER, loaded when TAB = 0X4d4752(character 'MGR')
    Insert option in effect for this table: TRUNCATE

      Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    TAB                                   1:3     3           CHARACTER            
     (FILLER FIELD)
    MGRNO                                 6:7     2           CHARACTER            
    MNAME                                9:15     7           CHARACTER            
    JOB                             NEXT+2:30    28           CHARACTER            

    Record 8: Discarded - failed all WHEN clauses.
    Record 9: Discarded - failed all WHEN clauses.
    Record 10: Discarded - failed all WHEN clauses.
    Record 11: Discarded - failed all WHEN clauses.
    Record 12: Discarded - failed all WHEN clauses.

    Table BONUS:
     4 Rows successfully loaded.
     0 Rows not loaded due to data errors.
     8 Rows not loaded because all WHEN clauses were failed.
     0 Rows not loaded because all fields were null.


    Table MANAGER:
     3 Rows successfully loaded.
     0 Rows not loaded due to data errors.
     9 Rows not loaded because all WHEN clauses were failed.
     0 Rows not loaded because all fields were null.


    Space allocated for bind array:                   7168 bytes(64 rows)
    Read   buffer bytes: 1048576

    Total logical records skipped:          0
    Total logical records read:            12
    Total logical records rejected:         0
    Total logical records discarded:        5

    Run began on Wed Jan 27 14:20:36 2016
    Run ended on Wed Jan 27 14:20:36 2016

    Elapsed time was:     00:00:00.13
    CPU time was:         00:00:00.03

  1. --废弃日志
    [oracle@cancer ~]$ cat ldr_case9.dsc
    TMP  SMITH 7369 CLERK    1020 20
    TMP  ALLEN 7499 SALESMAN 1930 30
    TMP  WARD  7521 SALESMAN 1580 30
    TMP  JONES 7566 MANAGER  3195 20

3.数据文件前N行不想导入
假如某天你接到一项数据加载需求,用户提供了一份100万行的数据文件,告诉你只导后50万行,恭喜,你接到了一个正常的需求!
实现的方式较多,比如修改数据文件,只保留后50万行(Windows下借助EditPlus 这类文本工具可以轻松实现,Linux/UNIX下通过TAIL等命令也可以轻易实现),如果你人很懒,不想修改文件,那正合sqlldr胃口,人家早早地就提供好了SKIP参数专用于满足此类需求。
数据文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case10.dat
    SQL> select ename,mgr,job,sal from emp;
    ENAME        MGR JOB             SAL
    ---------- ----- --------- ---------
    SMITH       7902 CLERK         1020
    ALLEN       7698 SALESMAN      1930
    WARD        7698 SALESMAN      1580
    JONES       7839 MANAGER       3195
    MARTIN      7698 SALESMAN      1580
    BLAKE       7839 MANAGER       3180
    CLARK       7839 MANAGER       2172
    SCOTT       7566 ANALYST       3220
    KING             PRESIDENT     4722
    TURNER      7698 SALESMAN      1830
    ADAMS       7788 CLERK         1320
    JAMES       7698 CLERK         1280
    FORD        7566 ANALYST       3220
    MILLER      7782 CLERK         1022

我们只对该数据文件只从第4行开始导入,即前3行不进行导入,创建控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case10.ctl
    LOAD DATA
    INFILE ldr_case10.dat
    TRUNCATE INTO TABLE BONUS
    (
    ENAME position(1:6),
    XCOL FILLER position(13:16),
    JOB position(18:26),
    SAL position(32:35)
    )

执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case10.ctl skip=3
    Commit point reached - logical record count 14

    --查看结果
    SQL> select * from bonus;
    ENAME       JOB      SAL       COMM
    ---------- --------- ---------- ----------
    SMITH      CLERK     1020
    ALLEN      SALESMAN  1930
    WARD       SALESMAN  1580
    JONES      MANAGER   3195
    MARTIN     SALESMAN  1580
    BLAKE      MANAGER   3180
    CLARK      MANAGER   2172
    SCOTT      ANALYST   3220
    KING       PRESIDENT 4722
    TURNER     SALESMAN  1830
    ADAMS      CLERK     1320

    ENAME      JOB       SAL       COMM
    ---------- --------- ---------- ----------
    JAMES      CLERK     1280
    FORD       ANALYST   3220
    MILLER     CLERK     1022

如果用户要求较高,明确指定只加载第XX到第XX行的记录,sqlldr还有一个参数叫LOAD,配置LOAD参数即可轻松实现。
这里仍使用上述数据文件,需求改为只导入第4到9行的记录,我们连控制文件都不需要修改,只需要在执行sqlldr时再加上LOAD参数即可:
执行sqlldr命令(即skip=3跳过前3行,load=6,加载接下来的6行记录),并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case10.ctl skip=3 load=6
    Commit point reached - logical record count 6

    --查看结果
    SQL> select * from bonus;
    ENAME      JOB        SAL       COMM
    ---------- --------- ---------- ----------
    SMITH      CLERK     1020
    ALLEN      SALESMAN  1930
    WARD       SALESMAN  1580
    JONES      MANAGER   3195
    MARTIN     SALESMAN  1580
    BLAKE      MANAGER   3180

4.加载的数据中有换行符
由于标准换行符也是sqlldr识别数据行结束的标志符,因此要将含换行符的数据加载到表中稍复杂一点点,而且需要根据实际情况来处理,不同情况的处理方式也不一样, 但基本思路是相同的,就是要同sqlldr指明什么时候才需要进行换行操作。
1)手工指定的换行符
在手工指定换行符的情况下,数据文件中的换行符并不是标准的换行标志,而是用户自定义的一个标识字符(或多个字符组成),这种情况的处理比较简单,如数据文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case11_1.dat
    10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.
    11,ALLEN.W,TECH MANAGER,This is ALLEN.W.\nHe is a Tech Manager.
    16,BLAKE,HR MANAGER,This is BLAKE.\nHe is a Hr Manager.

我们可以通过控制文件,在数据加载前处理remark列的数据,将用户指定的字符替换为chr(10),即标准换行符,创建控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case11_1.ctl
    LOAD DATA
    INFILE ldr_case11_1.dat
    TRUNCATE INTO TABLE MANAGER
    FIELDS TERMINATED BY ","
    (
    MGRNO,
    MNAME,
    JOB,
    REMARK "REPLACE(:remark,'\\n',chr(10))"
    )

这里需要注意的是,替换时必须指定"\\n"而不只是"\n",因为"\n"会被SQLLDR 识别成换行符并转换成换行标志,这样可能导致数据加载出错。而是默认转义符,指定该转义符后sqlldr就会将"\n"识别成普通字符了。
执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_1.ctl
    Commit point reached - logical record count 3

    --查看结果
    SQL> select * from manager;
    MGRNO   MNAME    JOB             REMARK                      
    -----   -------  --------------- ----------------------------
      10   SMITH    SALES MANAGER   THIS IS SMITH.
                                    He is a Sales Manager.
      11   ALLEN.W  TECH MANAGER    THIS IS ALLEN.W
                                    He is a Tech Manager.
      16   BLAKE    HR MANAGER      THIS IS BLAKE.
                                    He is a Hr Manager.

2)指定FIX属性处理换行符(定长数据文件专用)
数据文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case11_2.dat
    10 SMITH   SALES MANAGER THIS IS SMITH.
    HE IS A SALES MANAGER.    
    11 ALLEN.W TECH MANAGER  THIS IS ALLEN.W
    HE IS A TECH MANAGER.    
    16 BLAKE   HR MANAGER    THIS IS BLAKE.
    HE IS A HR MANAGER. 

创建控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case11_2.ctl
    LOAD DATA
    INFILE ldr_case11_2.dat "fix 68"
    TRUNCATE INTO TABLE MANAGER
    (
    MGRNO POSITION(1:2),
    MNAME POSITION(*+1:10),
    JOB POSITION(*+1:24),
    REMARK POSITION(*+1:65)
    )

FIX是INFILE关键字的一个属性,INFILE不仅有FIX属性,还有VAR和STR等属性
执行sqlldr命令,并查看结果
  1. --执行sqlldr命令                                                                                                                  
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_2.ctl
    Commit point reached - logical record count 3

    --查看结果
    SQL> select * from manager;
    MGRNO   MNAME    JOB             REMARK                      
    -----   -------  --------------- ----------------------------
      10   SMITH    SALES MANAGER   THIS IS SMITH.
                                    HE IS A SALES MANAGER.
      11   ALLEN.W  TECH MANAGER    THIS IS ALLEN.W
                                    HE IS A TECH MANAGER.
      16   BLAKE    HR MANAGER      THIS IS BLAKE.
                                    HE IS A HR MANAGER.

这种方式其实就是在加载数据文件之前,先通过FIX属性指定每行的长度(这里每行68个字符,包括换行符在内,该例子上不够字符的用空格来代替了,注意上面数据文件中的结尾的空格),到了指定长度就换行,不管中间有没有换行符,因此仅能用于定长字符串的数据文件,因为只有字符串定长,你才知道应该在INFILE处指定什么值。

3)指定VAR属性处理换行符(行头部标识换行)
前面提到INFILE关键字还支持VAR属性,语法格式为INFILE filename "var n", n的值不能超过40,否则会报错,如果不指定n则默认值为5。
本小节就演示通过这种方式处理换行符。总的来说,这确实是相当有才的一种方式, 首先通过VAR属性在每行开头指定一个固定长度的字符串,该字符串指明该行的长度,通过这种方式支持变长字符串。
数据文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case11_3.dat
    06110,SMITH,SALES MANAGER,This is SMITH.
    He is a Sales Manager.
    06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.
    He is a Tech Manager.
    05516,BLAKE,HR MANAGER,This is BLAKE.
    He is a Hr Manager.

数据文件中每行开头的061,063,055分别表示该行取61,63,55个字符
创建控制文件如下:
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case11_3.ctl
    LOAD DATA
    INFILE ldr_case11_3.dat "var 3"
    TRUNCATE INTO TABLE MANAGER
    FIELDS TERMINATED BY ","
    (MGRNO,MNAME,JOB,REMARK)

执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_3.ctl
    Commit point reached - logical record count 3

    --查看结果
    SQL> select * from manager;
    MGRNO MNAME     JOB             REMARK                      
    ------ --------- --------------- -----------------------------
       10 SMITH     SALES MANAGER   This is SMITH.
                                    He is a Sales Manager.
       11 ALLEN.W   TECH MANAGER    This is ALLEN.W.
                                    He is a Tech Manager.
       16 BLAKE     HR MANAGER      This is BLAKE.
                                    He is a Hr Manager.

①这种方式可以视为第2种方式的变种,该方法不在INFILE处指定行长度,而改为在每行的行首位置标注该行长度,以支持变长类型的数据格式。
②综合来看,这两种方式实用价值都比较低,先不说用户是否真有如此耐心来生成这样格式的数据文件,就算用户按照这种格式提供,要知道windows、Linux/UNIX下因操作系统自身原因,对换行符识别也不同,在Windows环境下换行标志由"回车chr(13)+换行chr(10)" 两个字节组成,而Linux/UNIX环境则是"换行chr(10)"一个字节,也就是说指定了长度,操作系统变了以后,长度有可能也得跟着变,这就造成用户提供的数据文件通用性较差。
③有没有一种更简单的方式呢?事实上确实没有太简单的方式,在前面的内容中就提到, 你需要为sqlldr指明什么时候应该换行,因此对数据文件进行预处理是必然的,不过相对来讲,下面将要介绍的方式更易于操作,也更可行一些。

4)指定STR属性处理换行符(行尾部标识换行)
这种方式也需要先对数据文件做处理,在记录换行处打上一个标记,比如"丨"(当然可以定义为其他字符,但注意不要与要导入的数据有冲突),这样sqlldr见到该字符就知道换行的时候到了。
由于单个字符出现在导入数据中的机率较高,因此建议换行标志尽可能由多个字符组成,通常习惯于定义“字符+换行符”作为新的换行标记,这里我们也采用这种方式。
数据文件和控制文件如下:
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case11_4.dat
    10,SMITH,SALES MANAGER,This is SMITH.
    He is a Sales Manager.|
    11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
    He is a Tech Manager.|
    16,BLAKE,HR MANAGER,This is BLAKE.
    He is a Hr Manager.|

    --控制文件
    [oracle@cancer ~]$ cat ldr_case11_4.ctl
    LOAD DATA
    INFILE ldr_case11_4.dat "str '|\n'"
    TRUNCATE INTO TABLE MANAGER
    FIELDS TERMINATED BY ","
    (MGRNO,MNAME,JOB,REMARK)

执行sqlldr命令,并查看结果
  1. --执行sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_4.ctl
    Commit point reached - logical record count 3

    --查看结果
    SQL> select * from manager;
    MGRNO MNAME     JOB             REMARK                      
    ------ --------- --------------- -----------------------------
       10 SMITH     SALES MANAGER   This is SMITH.
                                    He is a Sales Manager.
       11 ALLEN.W   TECH MANAGER    This is ALLEN.W.
                                    He is a Tech Manager.
       16 BLAKE     HR MANAGER      This is BLAKE.
                                    He is a Hr Manager.

STR属性中支持两种字符指定方式:
'char_string':普通字符,即标准的可见字符,不过也有些不可见字符可以通过下列反斜杠标识的方式在字符串模式中指定:
\n:表示换行。
\t:表示行制表符(tab)。
\f:表示换页。
\v:表示列制表符。
\r:表示回车。
说到这里,又不得不再次提及Windows和Linux/UNIX对换行符识别的差异,Linux/UNIX下指定"\n"即可,Windows下需要指定"\r\n"才表示一个完整的换行符。
X'hex_string':二进制字符。对于一些不可见字符,如像回车换行这类字符,可以将其转换成十六进制,然后再通过str X'hex_str'方式指定。

比如上述控制文件中的功能如果用二进制字符表示,形式如下:
  1. INFILE ldr_case11_4.dat "str X'7C0A'"
要査看指定字符的十六进制编码,可以通过UTL_RAW.CAST_TO_RAW生成,例如:
  1. SQL> select utl_raw.cast_to_raw('|'||chr(10)) from dual;
    UTL_RAW.CAST_TO_RAW('|'||CHR(1
    ---------------------------------
    7C0A

这种方式相当于自定义一个换行标志,标准换行符不再拥有特殊的意义,只是作为要加载数据的一部分。较第一种方式而言最大的优势是,数据文件相对更容易处理,只需要在生成数据文件时,最后一列附加一个指定字符即可,对于稍有SQL基础的人来说,这都是小case。

本文内容参考<涂抹Oracle-三思笔记>一书,该书是基于Windows,本文引用了该书的脚本和结论的整理在Linux亲自测试通过,并对一些小问题进行了处理

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29812844/viewspace-1988870/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长PLS/QL开发,SQL调优和改写,数据库设计

注册时间:2014-08-18

  • 博文量
    161
  • 访问量
    1087639