Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1966959
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3065
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2012-07-01 15:51:30

日常中,大量有格式的数据存放在平面文件中,如csv、txt中。通过sql*loader可以很方便导入到oracle数据库中。

先看一个例子,把平面文件test_action.txt中的数据导入到test_action表中。
test_action.txt
--------
"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"
--------

test_action表结构

控制文件
test_action.ctl
----
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )
----

导入语句
$ sqllder user/password errors=1000 control='/home/oracle/test_action.ctl' 



一、sql*loader的组成

1.命令
sqlldr user/password errors=n control='/xx/xx/xx/xx.ctl'  log='/xx/xx.log' bad='/xx/xx.bad'
--errors:允许失败的最大行数,默认50。超过errors sqlldr自动停止。
--control:控制文件
--log:日志,不指定也可,默认在执行sqlldr命令所在的目录。
--bad:记录导入失败的数据,默认在执行sqlldr命令所在的目录。


2.平面文件
平面文件中存放有规律的数据,比如以","逗号作为列之间的分割,各列以双引号""包围。
如下:
"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"


3.控制文件.ctl
也就是上面命令中的control=。控制文件时平面文件与数据库表之间的桥梁。控制文件指定平面文件各列对应表中的哪些列。
如:
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )

二、常用参数
sqlldr help=y可以查看所有参数

DIRECT:直接路径加载,它直接将数据写入到database block中,大大加速了导入速度。默认值是false。使用direct=true,表中的触发器失效。 
PARALLEL:并行加载。默认值是false。
rows:多少行提交一次
bindsize:每次提交记录(依据rows参数)的缓冲区的大小,默认255420 bytes,一般设置为20971520(20M),目前还没测出最大值。与rows配合使用。在conventional path load(一般方式)下有效。bindsize制约rows,如果rows提交的数据,
即 行*行长>bindsize,那么还是以bindsize为准。所以增大rows,一定要考虑bindsize是否够用。
readsize:读取缓冲区的大小 (默认 1048576),一般设置成20971520(20M)。readsize负责读,而bindsize负责提交。如果readsize小于bindsize,那么readsize会自动增加。


三、加速导入
1.表改为nologging
执行sqlldr命令之前,最好把相关表的logging模式改为nologging,这样会减少redo日志产生,同时也就加速了导入的速度。导完后再改为logging模式。
alter table table_name nologging;
alter table table_name logging;

2.PARALLEL
在命令中加入parallel=true
并行技术

3.DIRECT
使用direct path load(直接路径加载),触发器会失效。
有效的约束有:
NOT NULL
UNIQUE
PRIMARY KEY (unique-constraints on not-null columns)
失效的约束:
CHECK constraints
Referential constraints (FOREIGN KEY)

当然,也可以parallel=true+direct=true

4.rows
rows=n,导入n行提交一次。

5.bindsize:
和rows结合使用,决定一次提交的行数。

6.readsize



四、控制文件参数
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )


以上面为例:
1.CHARACTERSET :字符集,如UTF8 
infile 平面文件所在位置,这里可以写多个infile,也就是多个平面文件导入到一张表里
infile '/home/oracle/1.csv'
infile '/home/oracle/2.csv'

2.xx into table:append into --追加
                  truncate into --把表的数据都删除,然后导入
                  insert into --空表可以采用insert

3.fields terminated by "," :以逗号作为分隔
4.optionally enclosed by '"':列被双引号包围

5.函数
(1)日期转换:平面文件中
"2012-06-25 00:03:01",
那么ctl中,要根据平面文件日期格式在ctl中标明
LOG_TIME date "yyyy-mm-dd hh24:mi:ss",

(2)upper,转成大写
格式:col1 "upper(:col1)"
如:ACTION_TYPE "upper(:action_type)"


如果表中某列的类型为varchar2(300),那么在ctl中就要写成char(300)

SQLLDR默认输入流为数据类型为CHAR 长度为 255 所以当 begindata section里面的 输入流 长于255 的时候会报错的


五、sqllder的两种方式
Direct path load和conventional path load。
从这里可以明显看出Direct path load 与(常用方式)的区别
1.Conventional Path Load
使用sql insert方式插入数据。
特点:
(1)适用表中含有索引
(2)适用簇表:load data to a clustered table。direct path load不支持load data to  a clustered table簇表。
(3)满足所有约束constraint,如索引、not null等。
(5)你想知道哪些数据被rejected,信息记录在sqlldr的log和bad文件中。
(6)触发表中触发器
(7)在插入时,别的用户可以操作该表

2.Direct Path Load
直接把数据写到database block中,效率比Conventional Path Load高得多。Direct Path Load不经过oracle的sql这一层,也没有parseing、executing这些过程,对于oracle数据库的整个负载都是一个减轻。插入数据时,只是在表的HWM之上直接重新分配新的数据块,而不是在freelist中查找可以insert 的块。
注意:direct path load方式也会产生redo,因为插入数据时会分配新extend,oracle必须记录这些redo信息。
特点:
(1)direct path load不是满足所有的constraint,只满足primary key、unique、not null
(2)不能插入cluster table
(3)direct path load,在插入时,别的用户不能操作该表(不能进行DML操作,不能建立、重建索引,甚至查询)
(4)不会触发触发器
(5)适用于将大量数据导入到空表或者非空表,这个表最好不要含有触发器、参考约束。




Conventional Path Load(传统导入)优化实验:
1.rows=10000,10000行提交一次
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000
##Space allocated for bind array:                 255420 bytes(99 rows)
##Elapsed time was:     00:03:05.90
#这里rows=10000,而实际是99 rows提交一次,原因在于bindsize<设置的rows*行长,所以采用默认的bindsize为准。解决办法:加大bindsize。

2.rows=10000,10000行提交一次,加大bindsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=20971520
##Space allocated for bind array:               20970240 bytes(8128 rows) //依然bindsize<设置的rows*行长
##Elapsed time was:    // 00:00:52.51 效率大大提高
#提示specified value for readsize(1048576) less than bindsize(20971520)
解决办法:可以忽略,或者设置readsize和bindsize等值(意义不大,当readsize

3.rows=10000,10000行提交一次,继续加大bindsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=25800000 
##Space allocated for bind array:               25800000 bytes(10000 rows)//终于可以一次提交10000行数据了
##Elapsed time was:     00:00:50.37  // 增大bindsize后,效果有所提高

4.row=10000,10000行提交一次,添加readsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/p2sp_action.ctl' rows=10000 readsize=25800000 bindsize=25800000 
##Space allocated for bind array:               25800000 bytes(10000 rows)
##Elapsed time was:     00:00:49.49 //添加readsize参数后,效率有所提高

5.rows=10000,10000行提交一次,添加parallel=true
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 readsize=25800000 bindsize=25800000  parallel=true
##Space allocated for bind array:               25800000 bytes(10000 rows)
##Elapsed time was:     00:00:48.86 //效率又提高了

总结:rows+bindsize+readsize是传统导入最优方案。


Direct Path load实验:
$ sqlldr scott/tiger@67 errors=1000000 control='/home/oracle/test_action.ctl' direct=true 
##Elapsed time was:     00:00:06.54//速度惊人啊!



远程sqlldr导入数据
在A服务器上有csv文件,要导入到B库中



用sqlldr+tnsnames.ora就可以实现

1.首先要在A服务器安装oracle client,client版本最好和DB版本相同
如果A服务器没有oracle DB,那么安装oracle client来获得sqlldr命令,安装的时候选择自定义安装,选择oracle database utilties和oracle net两项即可。

2.A库配置tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/product/11.1.0/client/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
B =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.67)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = hou)
    )
  )


3.A服务器上执行sqlldr命令,csv数据和ctl控制文件都在A服务器
$ sqlldr scott/tiger@67 errors=10 control='/home/oracle/test.ctl'


阅读(8108) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册