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 结构如下:

MstarIDFundNameDateValue

在数据库中准备好待导入的数据表(使用了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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值