如何快速批量导入非Oracle DB格式的数据--sqlloader

前言

在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法：

1. A表记录利用toad或者pl/sql工具将其导出为一条条分号隔开的insert语句，然后再执行插入到B表中
2. 建立数据库间的dblink，然后使用 create table B as select * from A@dblink where…,或者insert into B select * from A@dblink where…
3. exp/expdp A表，再imp/impdp到B表
4. 那么除了上面的三种常用的方法，还有其他比较好的方法进行数据迁移吗，下面介绍oracle自带的Sql Loader(sqlldr)的用法。

sqlloader简介

sqlloaderOracle用于数据迁移、将数据从外部文件加载到Oracle数据库的表中，它具有强大的数据解析引擎，对数据文件中数据的格式几乎没有限制。

基本的组成由：

*.ctl：控制文件，与表信息相关，数据入表的逻辑处理（数据加载信息，解析数据，导入数据信息）

*.bad ：执行bat后自动生成，错误日志，保存导入文件出现错误的记录

*.log ：执行bat后自动生成，日志文件，可以查看导入的情况

*.dis：废弃文件

常用的参数命令：

• userid – ORACLE 用户名/口令
• control – 控制文件名，默认 格式 *.ctl
• log – 记录导入时的日志文件，默认为 控制文件(去除扩展名).log
• bad – 坏数据文件，默认为 控制文件(去除扩展名).bad
• data – 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
• discard – 废弃文件名
• discardmax – 允许废弃的文件的数目
• skip – 要跳过的逻辑记录的数目 (默认 0)
• load – 要加载的逻辑记录的数目 (全部默认)
• rows – 对于传统常规路径（Conventional Path）导入的情况，代表一次提交（Commit）的行数（默认：6 最大值：65534）
• bindsize – 每次提交记录的缓冲区的最大值（仅适用于传统常规路径加载），默认256000 Bytes
• readsize – 读取缓冲区的大小 (适用于传统常规路径和直接路径加载)，默认 1048576。
• errors – 允许的错误记录数，可以用他来控制一条记录都不能错 (默认 50)
• silent – 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
• direct – 使用直接路径 (默认 FALSE)
• parfile – 参数文件: 包含参数说明的文件的名称
• parallel – 执行并行加载 (默认 FALSE)
• file – 要从以下对象中分配区的文件

ROWS

对于传统常规路径（Conventional Path）导入的情况，代表一次提交（Commit）的行数（默认：6 最大值：65534）

BINDSIZE

通过配置BINDSIZE的值，加快加载导入效率，而且配置的值要比默认值和通过参数ROWS计算的缓冲区大小更优先。
即BINDSIZE能够制约ROWS，如果ROWS提交的数据需要的缓冲区大于BINDSIZE的配置值，会以BINDSIZE的设定为准

※在设定参数时，一定要同时考虑ROWS和BINDSIZE的设定。

READSIZE
读取缓冲区的大小 (适用于传统常规路径和直接路径加载)，默认 1048576。

READSIZE负责读取的缓冲区大小，而BINDSIZE负责提交的缓冲区大小，如果READSIZE小于BINDSIZE，那么READSIZE会自动增加。

通过设置READSIZE为更大的值，可以在提交前读取更多的数据到Buffer中去

sqlloader安装

1. 下载并解压软件

地址：https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

下载包：

• sqlloader所需的基础包：instantclient-basic-windows.x64-19.6.0.0.0dbru.zip
• sqlloader工具包： instantclient-tools-windows.x64-19.6.0.0.0dbru.zip

NOTE:直接下载oracle client客户端即可

使用方式

使用一个控制文件(*.ctl) 和一个数据文件（*.csv），步骤如下：

• 首先在数据库中创建好需要导入数据的表；
• 创建数据文件,*.csv 文件等类型的文件；
• 创建控制文件 *.ctl，数据入表的逻辑处理。
• 执行sqload命令加载导入数据

1）. 首先在数据库中创建好需要导入数据的表

create table user_info (
userid int,

username varchar2(50),

address varchar2(500),

sex varchar2(2),

phone_number varchar2(13)

email varchar2(50),

certificate_no VARCHAR2(20)

)

2）. 建立数据文件， users_data.csv 文件

01412401,李四,广东深圳龙华,M,13444455568,WJ@email.com,310101198504069999
01412402,张三,广东深圳龙华,M,13444455567,HH@email.com,310101198504069998
01412403,王二,广东深圳福田,M,13444455566,WJ@email.com,310101198504069997
01412404,李达,广东深圳南山,M,13444455565,HH@email.com,310101198504069996

3）. 建立一个控制文件 users_load_data.ctl

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行 ,rows=128 代表每128行数--据提交一次
LOAD DATA  
INFILE "/home/oracle/script/users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件,  
--truncate --操作类型，用 truncate table 来清除表中原有 记录  
append INTO TABLE test_users -- 要插入记录的表，这里插入到另外一张表里  
Fields terminated by "," -- 数据中每行记录用 "," 分隔  
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时  
trailing nullcols --表的字段没有对应的值时允 许为空  
(  
  virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号  
  userid,  
  username,  
  address ,  
  phone_number,
  email  ,
  certificate_no  
)

在操作类型 truncate 位置可用以下中的一值:

• insert ：为缺省方式，在数据装载开始时要求表为空
• append：在表中追加新记录
• replace：删除旧记录(用 delete from table 语句)，替换成新装载的记录
• truncate ：删除旧记录(用 truncate table 语句)，替换成新装载的记录

时间类型转换

字段 DATE “YYYY-MM-DD HH24:MI:SS” – 指定接受日期的格式，相当用 to_date() 函数转换

4）.执行命令

普通版：

在安装好sqlload命令目录中打开CMD命令，

然后再命令行窗口执行：

sqlldr userid=username/password@ip:port/dbName control=d:\users_load_data.ctl log=d:\userload.log

普通导入速度比较慢，一秒才几条，这样导入跟一条条插入数据差不多，因此应该善用其参数，加快加载导入数据

升级版：

将命令行改成这样：

sqlldr userid=username/password@ip:port/dbName control=d:\users_load_data.ctl log=d:\userload.log errors=100000 bindsize=8000000 rows=5000

这样可以配置可以在一秒1万条左右，加快导入速度，节省了很多时间。

NOTE:

当加载海量数据时（大约超过10GB），最好禁止日志的产生,这样不产生REDO LOG，可以提高效率,在 CONTROL 文件中 load data 上面加一行：unrecoverable， 此选项必须要与DIRECT共同应用.

对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.parallel=true

各位看官还可以吗？喜欢的话，动动手指点个💗，点个关注呗！！谢谢支持！
欢迎关注，原创技术文章第一时间推出