sqlldr的使用

1、背景

在项目中，需要每日通过三方晨星基金公司下载数据文件，并同步导入到内部数据库使用。当数据量大至百万级、千万级时，可通过ORACLE的sqlldr工具，快速地将数据批量导入。

2、sqlldr运行环境

（1）安装好ORACLE客户端
Q：测试是否可以使用sqlldr命令？
A：在服务器上执行命令：

sqlldr

若可看到：
SQL*Loader: Realease 11.2.0.4.0 ……
…………………………
……（以下省略）……
…………………………
等产品版本信息，以及sqlldr的使用介绍、参数说明等信息时，说明sqlldr可以使用。

（2）配置环境变量
Q：服务器已安装ORACLE客户端，但在某用户下执行时，出现错误sqlldr: command not found 怎么办？
A：需要在该用户下配置ORACLE环境变量。
拓：在linux服务器上，执行如下命令，显示隐藏文件

l -a

找到文件 .bash_profile ，往里追加 ORACLE环境变量：

export ORACLE_HOME=/was/soft/oracle/product/11.2.0.4/ch
export PATH=$ORACLE_HOME/bin:$PATH

拓：若机器上安装了多个版本ORACLE，可以使用linux的whereis命令进行定位。

whereis sqlldr

3、数据文件和数据表

（1）数据文件
项目中使用的数据文件为.csv格式，打开观察数据文件中包含的关键信息，以决定控制文件需要采用哪些参数完成编写。
· 首行是否为表字段名称
· 每一行记录的每个值之间的分隔符
· 字段的界定符
· 数据文件最后一行是否为空

（2）数据表
项目中的表FundTotalIndexReturn 结构如下：

MstarID	FundName	Date	Value

在数据库中准备好待导入的数据表（使用了PL/SQL）。
建表语句如下：

create table FUNDTOTALINDEXRETURN
(
  mstarid		VARCHAR2(20) NOT NULL,
  fundname		VARCHAR2(100),
  incomedate	DATE NOT NULL,
  incomerate	NUMBER(19,5)
  );

4、控制文件

sqlldr通过控制文件( .ctl ) 确定数据导入的各种信息，包括：编码格式、源数据文件、数据导入形式、分隔符、数据位空时的处理、导入表的名称和字段等。
以项目中使用的控制文件 FundTotalIndexReturn.ctl 作示例：

 load data
 characterset 'UTF-8'
 infile '/datasync/FundTotalIndexReturn_20190708.csv'
 append into table FundTotalIndexReturn_BAK
 fields terminated by ','
 trailing nullcols
 (mstarid,
 fundname,
 incomedate DATE "YYYY-MM-DD",
 incomerate DECIMAL EXTERNAL 
 TERMINATED BY WHITESPACE )

参数说明：

• characterset ： 编码格式。

• infile ： 待导入数据文件路径，文件名。可写多个infile… 指定多个数据文件。

• append ： 数据导入方式：追加。

  数据导入可选方式共有四种：
  insert：为缺省方式，在数据装载开始时要求表为空。
  append：在表中追加新记录。
  replace：删除旧记录（delete from table），替换成新装载的记录
  truncate：删除旧记录（truncate table），替换成新装载的记录

• fields terminated by ‘,’：字段分隔符为逗号,

• trailing nullcols：表的字段没有对应的值时允许为空。

• TERMINATED BY WHITESPACE：数据文件末行没有数据时，需要加上这一句，表示以空行结尾。
  若数据文件末行为空，而控制文件上没有加上这一行，会出现导入失败的情况。

5、sqlldr 导入命令

命令格式：

sqlldr userid=userName/password@service control=test.ctl 
[logs=test.log bad=test.bad skip=1 load=200000 errors=3000 rows=10000  bindsize=62914560…]

参数说明：

• userName：数据库用户名
• password：数据库用户密码
• service：数据库服务名
• control=test.ctl：指定控制文件所在路径及名称
• logs=test.log：指定sqlldr生成的日志文件存放路径及名称
• bad=test.bad：指定导入出错的数据日志存放的路径及名称
• skip=1：跳过第一行
• load=200000：不导入所有数据，只导入前200000 行数据
• errors=3000：允许导入出错的记录数量，超过指定的3000后，停止加载
• rows=10000：指定一次加载的行数
• bindsize=62914560：表示每次提交记录缓冲区的大小

以项目中使用导入命令为例：

sqlldr userid=name/pwd@testdb:1521/ibs control=/sqlldr/command/FundTotalIndexReturn.ctl 
log=sqlldr/logs/FundTotalIndexReturn_20190708.log
bad=sqlldr/logs/FundTotalIndexReturn_20190708.bad
skip=1 bindsize=62914560 errors=3000 rows=10000

在服务器上执行导入命令后，可查看日志文件的导入结果。