问题描述

由于提供方提供有sql插入数据语句：开始想用navicat 直接运行这些语句进行导入数据，运行后发现导入很缓慢，基本差不多两个小时，而且插入的数据发现将近有一半的缺失，个人猜测是提交的次数太少（本人直接拿sql用的,sql里只有最后一个commit），后来查询有人建议是1000条commit一次；

经稍微查询后发现，这种大批量数据导入是不适合用语句的。

经研究我找到两种方法快速导入这些数据：

方法一：使用kettle导入

新建一个转换： 在输入组件中找到csv文件输入，在输出组件中找到表输出

分别在两个里面配置相关信息即可。

方法二： 使用oracle的软件sqlldr

流程：使用sqlldr去执行ctl文件脚本去导入数据

sqlldr如果你本地装有oracle 则自带了软件，否则很麻烦 我查了很长时间也没装好，后来在另一篇博客中看到可以直接在oracle官网下载客户端 ，由于我已经在本地装了oracle数据库（哎） 也就不想花时间在去尝试了

进入正题：sqlldr使用

首先在本地做好一个ctl文件

文件说明：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
LOAD DATA  
INFILE "users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，  
truncate --操作类型，用 truncate table 来清除表中原有 记录  
INTO TABLE users -- 要插入记录的表  
Fields terminated by "," -- 数据中每行记录用 "," 分隔  
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时  
trailing nullcols --表的字段没有对应的值时允 许为空  
(  
  virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号  
  user_id number, --字段可以指定类型，否则认 为是 CHARACTER 类型, log 文件中有显示  
  user_name,  
  login_times,  
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换  
)

我的示例：

准备工作： 新建D:\test文件，在里面放入input.ctl（脚本）和lnpdb.txt（需要导入的数据）（我把csv文件直接改了后缀为txt）

input.ctl:

LOAD DATA
CHARACTERSET ZHS16GBK
INFILE 'lnpdb.txt'
APPEND
INTO TABLE LNPDB fields terminated by '@|@' TRAILING NULLCOLS
(NPID,
NPCODE,
PORTOUTNETID,
PORTINNETID,
HOMENETID,
REQDATETIME,
STATE,
CREATETIME,
MODIFYTIME,
REMARK,
NPAREAID,
NPSEGMENT,
SERVICETYPE,
SHARDING_ID
)

lnpdb.txt 的三行数据：

557784@|@15522540751@|@00330220@|@00110220@|@00330220@|@2010-05-07 00:00:00@|@70A@|@2010-05-06 09:52:29@|@2010-05-07 13:36:50@|@@|@022@|@@|@MOBILE@|@15522540751

557809@|@15595640247@|@00338980@|@00248980@|@00338980@|@2010-05-07 00:00:00@|@70A@|@2010-05-07 09:21:54@|@2010-05-07 12:08:48@|@@|@898@|@@|@MOBILE@|@15595640247

557810@|@18976240247@|@00128980@|@00248980@|@00128980@|@2010-05-07 00:00:00@|@70A@|@2010-05-07 09:21:54@|@2010-05-07 12:08:48@|@@|@898@|@@|@MOBILE@|@18976240247

这样准备工作就完成了。

开始导数据：

cmd 到 D:\test 执行 sqlldr dbuser/dbpass@dbservice control=input.ctl

然后就开始导入数据了 等着结果就可以了，最后会有一些相关完成信息

在 D:\test下还会生成一个日志文件 我的是input.log;还有生成一个出错文件，记录错误的数据 我的是inpdb.bad

扩展

由于我是使用kettle 导入数据到远端数据库的，sqlldr我是后来自己测试了一下，向自己安装的oracle数据库中导入数据，也是可以的。

如果你要使用sqlldr导入数据到远端数据库中我自己尝试了一下也是可以的，只需要改下cmd命令就可以了

上面cmd命令改为这样：

sqlldr dbuser/dbpass@10.121.32.38:1521/dbservice control=input.ctl （就是在数据库名前加上端口和ip,注意加/）

以上是我根据自己的记忆整理的，如果有小错误望见谅，还有就是敏感信息我已经更换了文字。欢迎评论指出错误，我有时间看到会更改。