1.sqlldr介绍
2.实战
先有需求:
将000000_0.txt文件导入oracle数据库
000000_0.txt大小:123MB,几十万行
000000_0.txt内容格式:
20160514|未知|未知|未知|3G|057431041116|55349|41116|28173301261774|460007863660008|1.224609375|13.0|\N|\N
20160514|HTC|手机|D728w|3G|057431001157|55097|1157|35153907023514|460006620667069|0.513671875|10.0|\N|\N
20160514|HTC|手机|D728w|3G|057431043861|55084|43861|35153907033487|460027685036740|2937.208984375|8052.0|\N|\N
20160514|HTC|手机|D728w|3G|057431003011|55084|3011|35153907033487|460027685036740|1.755859375|56.0|\N|\N
导入的数据库表结构:
create table TEST201605
(
"时间" VARCHAR2(100),
"终端厂商" VARCHAR2(100),
"终端分类" VARCHAR2(100),
"终端型号" VARCHAR2(100),
"小区网络类型" VARCHAR2(100),
"小区网元编号" VARCHAR2(100),
lac VARCHAR2(100),
ci VARCHAR2(100),
imei VARCHAR2(100),
imsi VARCHAR2(100),
"3G流量" VARCHAR2(100),
"3G时长" VARCHAR2(100),
"4G流量" VARCHAR2(100),
"4G时长" VARCHAR2(100)
)
2.1编写控制文件 load.ctl:
load data
CHARACTERSET UTF8 //查看下文件的编码,设为同一个编码
infile "E:\datafile\000000_0.txt" //只能一个个导入,可以把多个文件合并到一个里,然后导入
append
into table TEST201605
fields terminated by '|'
trailing nullcols
(
"时间",
"终端厂商",
"终端分类",
"终端型号",
"小区网络类型",
"小区网元编号",
LAC,
CI,
IMEI,
IMSI,
"3G流量",
"3G时长",
"4G流量",
"4G时长"
)
2.2 windows下
dos命令窗,cd 到load.ctl所在目录
使用命令:
sqlldr daily/mdasil@daily-74 control=load.ctl log=log.log bad=bad.log errors=5000 rows=1000 bindsize=10485760
linux:
load data
CHARACTERSET UTF8
infile "/datafile-import/000021_0.txt"
append
into table jingfen_201605_1415
fields terminated by '|'
trailing nullcols
(
SHI_JIAN,
ZDCS,
ZDFL,
ZDXH,
XQWLLX,
XQWYBH,
LAC,
CI,
IMEI,
IMSI,
THREEG_LL,
THREEG_SZ,
FOURG_LL
)
sqlldr daily/mdasil control=/datafile-import/ctl/44.ctl log=/datafile-import/log/log20160526.log bad=/datafile-import/log/bad20160526.log
rows与bindsize相关,rows默认64行,如果不修改bindsize,只修改rows无效。
bindsize --( 每次提交记录的缓冲区的大小,字节为单位,默认256000)
dos窗口会打印:
达到提交点 - 逻辑记录计数 441884 达到提交点 - 逻辑记录计数 441954 达到提交点 - 逻辑记录计数 442024 达到提交点 - 逻辑记录计数 442094