频道栏目
首页 > 资讯 > SQL Server > 正文

Sql loader的使用分享

18-07-09        来源:[db:作者]  
收藏   我要投稿

Sql loader的使用分享

1.   sqlldr概述

sqlldr 为一种大量数据加载工作,在cmd命令行模式下实现,调用语句如下:

sqlldr userid/password @servicename control=ctlfilename。

满足前提是存储数据的oracle表是已经存在的。

2.    ctl文件内容

OPTIONS ( {[SKIP=integer] [ LOAD = integer ] [ERRORS = integer] [ROWS=integer][BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] } )

LOAD[DATA]

[

  { INFILE | INDDN } {file | * }

  [STREAM | RECORD | FIXED length [BLOCKSIZEsize]|VARIABLE [length] ]

  [ { BADFILE | BADDN } file ]

  {DISCARDS | DISCARDMAX} integr

]

[ {INDDN |INFILE} . . . ]

[ APPEND |REPLACE | INSERT ]

[RECLENTinteger]

[ {CONCATENATE integer | CONTINUEIF { [THIS | NEXT] (start[: end])LAST } OPERATOR{ 'string' | X 'hex' } } ]

INTO TABLE[user.]table

[APPEND |REPLACE | INSERT]

[WHENcondition [AND condition]...]

[FIELDS[delimiter] ]

(

column {

  RECNUM | CONSTANT value | SEQUENCE

  ( { integer | MAX |COUNT} [, increment] ) |[POSITION ( { start [end] | * [ + integer] }) ]

    datatype

    [TERMINATED [ BY ] {WHITESPACE| [X]'character' } ]

    [ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']

  [NULLIF condition ]

  [DEFAULTIF condotion]

  }

  [ ,...]

  )

[INTOTABLE...]

[BEGINDATA]

Line1:

option({[skip==integer] [ load = integer] [ errors = integer] [ rows = integer][ bindsize = integer][silent = (all|feedback|error|discard)]})

其中skip,skip = 1 表示用来跳过数据中的第一行;load = 200000表示不导入所有的数据,只导入跳过skip参数后的200000条数据;errors = 100 表示出错100次后,停止加载;rows=1000表示一次加载的行数,默认值为64;bindsize=33554421,表示每次提交记录缓冲区的大小,默认为256k。

Line2:

Load data

Line3:

Infile [*]‘datapath’

  [ { BADFILE | BADDN } file ]

  {DISCARDS | DISCARDMAX} integer ]

其中*是在ctl文件中没有包含数据文件时使用,若有数据文件的时候最好使用绝对路径并且需要带上单引号,badfile内为抛出的坏文件名,integer为文件的记录大小,discards为被抛弃的文件名。

Line4:

Insert/append/truncate/replace

Insert为默认插入数据的方式,只有在表为空表的时候才可进行数据导入

Append 为在表中数据的末端将数据导入

replace:(用 delete from table 语句),替换成新装载的记录。是数据操作语句(dml),这个操作会放到 rollbacksegement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。

truncate:删除旧记录(用 truncate table 语句),替换成新装载的记录是数据库定义语言(ddl),操作立即生效,原数据不放到 rollbacksegment 中,不能回滚,操作不触发 trigger。。

Line5:

Into table table_name

表示将数据插入某一个表中

Line6:

Field terminatedby”,”optionally enclosed by “”

表示数据以,(逗号)划分,以“”(空格)换行

Line7:

When condition

比如when id = id _ demo为提取id 为id_demo的过滤条件

Line8:

(

(1)Virtual column filter   表示过率没有名字的第一行

           FILLER:控制文件中指定 FILLER,表示该列值不导入表中

(2)position(m:n):指从第 m 个字符开始截止到第 n 个字符作为列值

        position(*+2:15):直接指定数值的方式叫做绝对偏移量,如果使用*号,则为相对偏移量,表示上一个字段哪里结束,这次就哪里开始,相对便宜量也可以再做运算。

        position(*) char(9):这种相对偏移量+类型和长度的优势在于,你只需要为第一列指定开始位置,其他列只需要指定列长度就可以。

(3)若导入的列比表中的列要少,需要在末尾加上comm”0”

(4)column + date ‘yyyy-mm-dd’表示将数据转换为日期类型

(5)

)

Line9:

若要导入不同表,即在表的末尾加上into table anothertablename,再加上限制条件以及column相关的内容

Line10:

Begindate

+输入的数据

3.    SQL*LOADER 的性能与并发操作

(1)、ROWS 的默认值为 64,你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。

(2)、常规导入可以通过使用 INSERT语句来导入数据。DIRECT导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中,可以提高导入数据的性能。当然,在很多情况下,不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

(3)、通过指定 UNRECOVERABLE选项,可以关闭数据库的日志(是否要 ALTER TABLE table_nameNOLOGGING?)。这个选项只能和 DIRECT 一起使用。

(4)、对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务.

SQLLDR   USERID=/  CONTROL=RESULT1.CTL  DIRECT=TRUE   PARALLEL=TRUE

SQLLDR   USERID=/  CONTROL=RESULT2.CTL  DIRECT=TRUE   PARALLEL=TRUE

SQLLDR   USERID=/  CONTROL=RESULT2.CTL  DIRECT=TRUE   PARALLEL=TRUE

当加载大量数据时(大约超过10GB),最好抑制日志的产生:  

ALTER TABLEtable_name NOLOGGING;

这样不产生REDO LOG,可以提高效率。然后在 CONTROL文件中LOAD DATA上面加一行UNRECOVERABLE,此选项必须要与DIRECT共同应用。  

在并发操作时,ORACLE声称可以达到每小时处理100GB数据的能力!其实,估计能到 1-10G 就算不错了,开始可用结构相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。

4.    实例

LOAD DATA

INFILE 'C:\Users\Shen_potato\Desktop\emps.csv'

into table emp10

FIELDS TERMINATED BY ","

(

virtual_column FILLER,

EMPNO "seq_eseq.nextval",

ENAME,

JOB,

MGR,

HIREDATE date 'yyyy-mm-dd',

SAL,

COMM,

DEPTNO

)

相关TAG标签
上一篇:ORACLE函数创建,日期加减函数并且要区分工作日与自然日的问题分析
下一篇:Oracle查询库中所有表名、字段名、字段名说明,查询表的数据条数、表名、中文表名等实例教程
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站