Oracle的sql loader用于将CSV等文本文件格式的数据快速的导入到表中。其基本的用法如下:
第一步:编写ctl文件
$ vi /dump/sqlload/sqlldr0815.ctl
load data
characterset ZHS16GBK
infile '/dump/sqlload/20120801-0815.csv'
insert
into table "POSTCARD_TEMP_20120815"
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( SEND_PROVINCE,
SEND_TIME,
SENT_ZIPCODE,
POST_OFFICE,
POST_CODE,
MERCHANT_NAME,
PACKAGE_ID
)
其中 characterset 为字符集选项,如果涉及到中文,一般用ZHS16GBK或者utf8。
关键字insert表示从一张新表中插入数据,要求表为空,为缺省模式,如果是truncate或者replace,则表示把目标表清空掉再导入数据,append表示在原表中新增数据。
fields terminated by ','表示每个字段以','来进行分隔。
optionally enclosed by '"'表示每个字段用"进行包围。
trailing nullcols表示将没有对应值的列都置为null。
括号内则是表中的各个字段。里面可以指定Datatype,不指定的话,默认为CHARACTER。
比如
时间格式就需要指定,在字段后加入DATE "YYYY-mm-dd hh24:mi:ss"。
如果是数据源是mysql导出的,因为空值导出为\N,所以需要在字段后加上NULLIF(XX='\\N')。
同时还要注意从msyql导出时,可能会遇到字符内容里含有'\n’的行,则会多生成一行,这时就需要在导完后,用'wc -l name.txt'来检查行数跟表的行数对不对应,如果多了行,则需要处理掉换行符才可以继续第二步(可以在表中处理或者在导出的文件中处理)。
第二步:用sqlldr命令导入数据
$ sqlldr user/pwd@service control='/dump/sqlload/sqlldr0815.ctl'direct=y errors=80000
其中 user/pwd 为用户密码,service为本地的tnsname里的服务名,control的值为第一步编写的ctl文件。
direct=y 表示不使用sql语句,从文件中读数据,直接将其写入数据库的block中,将直接绕过sql引擎和undo生成,同时还可能避免生成redo日志。这种方式导入数据速度最快,但是如果数据库是dataguard模式,将会导致主从不同步。
errors表示如果允许数据导入出错的行数,只要不超过errors,则可以跳过该行继续导入。导入失败的数据会写入到bad文件中去,等sqlldr结束了,需要把bad文件的中的数据处理后再次导入。还可以指定log的存放路径,默认存放在ctl同一目录。可以通过查看其日志来验证是否成功导入,如果里面的records skipped/recordsrejected/records discarded均为0, records read的数目也跟csv文件的行数相同,说明数据已经全部导入,入下图所示。但是还是要去表中查看是否出现乱码等问题。