Oracle高速导入CSV数据

说着是导入CSV，其实格式不定，文本也可以，只是导入数据几十个G的时候大多数都是用CSV来存的，所以就把名字写成了导入CSV。

 

这里说的是导入数据，之前我也写过导入库、表、schema等方式，对于外来的纯数据，没有怎么纪录过，所以这篇文章主要是针对数据的导入。

方式用Oracle自带的SQL loader。命令式sqlldr，这个工具是安装完Oracle就有的，不需要额外下载。效率有多快呢？我4个varchar的字段每秒差不多写入20万条吧。

 

注意，这种超快的导入方式是在服务器上执行的，所以需要相关权限，一般拿不到权限的就用PLsql吧，虽然相对SQL loader慢，不过对于小批量数据还是可以的

 

本次我导入的数据大小为25G.

环境：centos7

工具：SQL Loader

相关原理：官网文档

操作方法：1、需要定义好一个Oracle执行的脚本， .ctl结尾的文件

2、需要sqlldr 命令启动导入程序，程序会找到之前写的脚本按照脚本信息去处理

 

我的具体步骤：

1、把数据文件上传到服务器指定文件夹

2、写好导入配置脚本

3、启动命令

 

脚本格式：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行，可以不写
LOAD DATA
INFILE "users_data.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，

--操作类型参数，多个可选，新表也可以不用
/*
truncate  用 truncate table 来清除表中原有记录
insert     --为缺省方式，在数据装载开始时要求表为空
append  --在表中追加新记录
replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
*/

INTO TABLE users -- 要插入记录的表
Fields terminated by "," -- 数据中每行记录用 "," 分隔
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时

/*
trailing nullcols --表的字段没有对应的值时允许为空，都有值得新表可以不写
*/

(
  virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号，可以不写
  --字段类型可以指定，也可以不写，先默认是表的字段类型，或者默认是 CHARACTER，  
  user_id number, 
  user_name,
  login_times,
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换
--建议除了时间指定字段类型，其他的都不要写，根据表默认就行了
)

 

 

我的脚本：

因为我有多个不同表的文件导入，所以写了多个配置文件，举例两个吧：

1、单文件，无空值，无特殊情况，新表：
load data
infile '/home/oracle/csvdata/tcd.csv'
into table T_NEW_COMMODITY_DUMMY_DTAILE
fields terminated by ',' optionally enclosed by '"'
(
id,
cardno,
passwd
)



2、多文件，表中已有数据不能删除和覆盖，只能增加，数据有空值:
[[email protected] tcd]$ ls
tcd0.bad  tcd0.csv  tcd1.csv  tcd2.bad  tcd2.csv  tcd3.csv  tcd4.csv  tcd.csv  tcd.ctl  tcd.log
[[email protected] tcd]$ cat tcd.ctl
load data
infile '/home/oracle/csvdata/tcd/tcd1.csv' 
infile '/home/oracle/csvdata/tcd/tcd2.csv' 
infile '/home/oracle/csvdata/tcd/tcd3.csv' 
infile '/home/oracle/csvdata/tcd/tcd4.csv'

append --增加参数

into table T_NEW_COMMODITY_DUMMY
fields terminated by ',' optionally enclosed by '"'
(
id,
cardno,
passwd,
codeurl
)

脚本写完了后，在linux的Oracle用户角色下执行脚本命令，不要去SQL plus里面执行

命令：

sqlldr 用户/密码 control=/配置文件地址/配置文件.ctl

执行后就开始数据刷屏了，速度极快，在ctl文件目录下会打出日志，报错了可以去看日志，跟多详细参数和定义，可以去官网看文档，很详细的。

一点小秘密：

如果导入的时候有错误产生，那么错误数据会被存到bad文件中，该文件和数据文件、日志文件同目录下，举个例子，我导入的数据varchar设定为100，但是有些数据超多了100，所以报错没有导入，怎么处理呢，先修改字段长度，之后不是覆盖重新导入，而是直接把配置文件的指向改成bad文件，bad文件的格式和导入文件格式会是一致的，直接导入bad文件就行了，不用重新开始来个几千万的导入。导入完后bad即使数据成功了也不会被删除，那么怎么看是否被处理成功了呢，再去查看日志文件没有报错就是成功的。如果有，就再继续处理。