第一句子网 > Oracle数据库导入csv文件(sqlldr命令行)

Oracle数据库导入csv文件(sqlldr命令行)

时间：2024-04-26 08:58:11

1.说明

Oracle数据库导入csv文件，

当csv文件较小时，

可以使用数据库管理工具，

比如DBevaer导入到数据库，

当csv文件很大时，

可以使用Oracle提供的sqlldr命令行工具，

能够快速导入大量数据。

在数据库之间进行数据迁移时，

特别是不同类型的数据库，

使用csv文件是一种很好的选择。

2.sqlldr用法

下面介绍sqlldr用法，

有一个USER_INFO.csv文件，

需要导入到数据库中USER_INFO表，

而且有1.5亿条数据，

要求导入耗时尽量小。

这里先介绍基本的导入方式，

然后介绍direct导入方式，

最后介绍parallel导入方式。

3.获取csv文件

USER_INFO.csv文件内容如下，

这里仅选取了三条测试数据:

MSISDN,PROVINCE_CODE,CREATE_TIME8617512570551,30,-10-31 17:16:098613063310680,33,-10-31 17:16:128613295281345,80,-10-31 17:16:14

4.创建USER_INFO表

CREATE TABLE "USER_INFO" ( "MSISDN" VARCHAR2(16),"PROVINCE_CODE" VARCHAR2(4),"CREATE_TIME" DATE,CONSTRAINT "PK_USER_INFO_MSISDN" PRIMARY KEY ("MSISDN") ) TABLESPACE "USERS";COMMENT ON COLUMN "USER_INFO"."MSISDN" IS '用户手机号';COMMENT ON COLUMN "USER_INFO"."PROVINCE_CODE" IS '省份编码'; COMMENT ON COLUMN "USER_INFO"."CREATE_TIME" IS '用户创建时间';

注意表中字段要和csv文件对应。

5.创建ctl文件

创建名为loaddata_USER_INFO.ctl的控制文件：

options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)load datainfile '/home/oracle/USER_INFO.csv'insert into table "USER_INFO"fields terminated by ',' Optionally enclosed by '\'' (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

第1行是导入参数配置，

已经是支持大量数据导入的参数方案。

第3行infile指定导入的文件是USER_INFO.csv；

第4行into table前面的insert表示导入方式：

insert :默认方式，在导入记录前要求表为空；append :在表中追加新导入的记录；replace :删除旧记录(等价delete from table语句)，替换成新导入的记录；truncate:删除旧记录(等价truncate table语句)，替换成新导入的记录；

into table后面指定导入数据库表USER_INFO，

且表名必须大写；

第5行指定每一行的字段是以逗号(,)分隔；

第6行指定字段是用两个分号(')包围起来的，可选的；

最后一行对应导入的字段，

注意如果导入的是时间字段，

需要指明时间转换的格式。

6.使用sqlldr导入

指定需要导入的数据库，

以及使用的control文件：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO.ctl

导入成功输出日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 31 17:37:49 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.Path used:ConventionalCommit point reached - logical record count 3Table "USER_INFO":3 Rows successfully loaded.Check the log file:loaddata_USER_INFO.logfor more information about the load.

看到3条数据成功导入USER_INFO，

同时会生成loaddata_USER_INFO.log日志，

通过该日志可以查看更详细的导入信息，

如果导入时发生错误，

可以查看到详细的错误信息，

而且还会生成USER_INFO.bad文件，

bad文件原样输出了导入失败的数据。

默认情况下ERRORS达到50个就会停止导入，

如果需要忽略错误继续导入，

可以配置ERRORS最大值为999999999。

7.direct直接路径方式

sqlldr支持direct参数，

使用直接路径方式导效率非常高,

direct参数默认为FALSE，

上面第6步使用的就是默认常规路径方式，

下面使用直接路径方式，

进一步加快数据导入速度。

但是使用直接路径方式会导致表的唯一索引(或主键)失效，

可以在导入前先删除表的唯一索引，

然后在导入完成后重建索引即可，

如果有重复的数据，

需要先去重才能重建索引成功，

经过测试即使需要重建索引，

使用直接路径方式还是比常规路径方式快。

8.创建ctl文件(直接路径方式)

创建名为loaddata_USER_INFO_direct.ctl的控制文件：

options(skip=1,COLUMNARRAYROWS=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)load datainfile '/home/oracle/USER_INFO.csv'insert into table "USER_INFO"fields terminated by ',' Optionally enclosed by '\'' (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

这个文件和第5步中的区别在于，

options中的BINDSIZE换成了COLUMNARRAYROWS，

因为BINDSIZE是常规路径绑定数组的大小，

而COLUMNARRAYROWS是直接路径列数组的行数。

9.删除主键

导入前需要先删除USER_INFO表的主键：

ALTER TABLE USER_INFO DROP CONSTRAINT PK_USER_INFO_MSISDN;

10.使用sqlldr导入(直接路径方式)

基本与第6步的命令相同，增加direct=true：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO_direct.ctl direct=true

数据成功导入日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 10:48:10 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.Path used:DirectLoad completed - logical record count 3.Table "USER_INFO":3 Rows successfully loaded.Check the log file:loaddata_USER_INFO_direct.logfor more information about the load.

11.重建主键

ALTER TABLE USER_INFO ADD CONSTRAINT PK_USER_INFO_MSISDN PRIMARY KEY(MSISDN);

至此使用直接路径方式导入数据成功，

经过测试1.5亿条不重复数据direct方式导入，

花费4分02秒，

然后重建主键花费19分14秒，

总耗时23分16秒。

然而使用常规路径方式导入需要48个小时，

导入效率得到了极大的提升。

12.parallel并行导入

前面使用的是串行方式导入数据，

通过设置parallel为true可以并行导入，

在多核服务器上面能进一步提升导入效率。

需要注意并行方式仅仅在直接路径方式时有效。

13.创建ctl文件(并行方式)

创建名为loaddata_USER_INFO_direct_parallel.ctl的控制文件：

options(skip=1,COLUMNARRAYROWS=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)load datainfile '/home/oracle/USER_INFO.csv'append into table "USER_INFO"fields terminated by ',' Optionally enclosed by '\'' (MSISDN,PROVINCE_CODE,CREATE_TIME "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

这个文件和第8步中的区别在于，

并行方式只支持append导入，

而且要注意USER_INFO表不能有索引，

如果有需要先删除。

14.使用sqlldr导入(并行方式)

基本与第10步的命令相同，增加parallel=true：

sqlldr userid=yuwen/ai123456@10.21.13.14:1521/orcl control=loaddata_USER_INFO_direct_parallel.ctl direct=true parallel=true

数据成功导入日志如下：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 11:08:51 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.Path used:DirectSQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.Load completed - logical record count 3.Table "USER_INFO":3 Rows successfully loaded.Check the log file:loaddata_USER_INFO_direct_parallel.logfor more information about the load.

15.sqlldr命令说明

15.1.用法

SQLLDR keyword=value [,keyword=value,...]

15.2.参数说明

15.3.注意事项

命令行参数可以由位置或关键字指定。

前者的例子是 'sqlldr scott/tiger foo';

后这的例子是 'sqlldr control=foo userid=scott/tiger'。

位置指定参数的时间必须早于，

但不可迟于由关键字指定的参数。

例如允许 'sqlldr scott/tiger control=foo logfile=log',

但是不允许 'sqlldr scott/tiger control=foo log',

即使参数 'log' 的位置正确。

15.4.重要参数

rows：

加载的行数，默认值：常规路径：64，直接路径：全部。

常规路径导入时，指定绑定数组中的行数。直

接路径导入时，指一次从数据文件只读取的行数。

该参数同时受bindsize制约，

如果rows每行实际占用大小超出bindsize的最大可用值，

则rows自动降低到bindsize最大可用值。

bindsize：

默认值：256000，以字节为单位。

为绑定数组指定最大可用空间，

用来存储一次读取的rows的记录，

该值不能太小，至少要能放入一条逻辑记录，

不然有可能造成加载失败，

但是设置一个超大值也没有意义，

浪费空间而且起不到任何效果。

direct：

直接路径，默认值是false。

直接路径加载并不是通过insert语句的方式提交数据，

直接路径的加载甚至不走db_buffer，

因为不会存在缓冲区争用，

直接路径加载直接向数据文件写数据，

因此效率较高。

file：

指定写入的表空间数据文件。

通常是并行加载时应该会用到该参数，

指定file文件后，

要加载的内容即只向指定的数据文件写入数据，

通过这种方式某些情况下可以减小磁盘间的竞争。

skip_unusable_indexes：

是否跳过不可用的索引,默认为false。

如果设置为true。在加载数据的时候，

如果没加载的表中索引为unusable，

则该索引数据不维护，数据加载完以后也不会改变索引的状态。

在oracle中也有一个参数叫skip_unusable_indexes，

但是sqlldr中的这个参数优先级比oracle数据库内部的这个参数优先级高。

如果sqlldr中未设置这个参数，则以oracle中的skip_unusable_indexes参数为准。

skip_index_maintenance:

是否跳过索引维护,默认值为false。

该参数仅在直接路径加载时有效，

如果设置为true，则数据加载过程中并不会维护索引，

因此数据加载完后会导致索引无效。

readsize:

缓冲区大小,默认值1048576字节,最大不超过20M。

该参数设置的值仅当从数据文件中读取数据时有效，

如果数据包含在控制文件中则为64K，并不可修改。

streamsize:

流缓冲区大小，默认值是256000字节。

指定直接路径加载时流缓冲区大小。

multithreading：

是否启用多线程。

默认值：多cpu系统默认为true，单cpu的系统默认则为false。

该参数仅仅在直接路径加载时有效。

date_cache：

日期转换缓存，默认值为1000条。

指定一个专门用于日志转换的缓存区，

如果处理的日期在其中，则不需要转换，

否则直接取出来使用。

16.执行结果详细日志

给出loaddata_USER_INFO_direct_parallel.log日志内容，

可以看到导入使用的配置，耗时等详细信息，

供给大家参考：

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Nov 1 11:20:07 Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.Control File: loaddata_USER_INFO_direct_parallel.ctlData File:/home/oracle/USER_INFO.csvBad File:USER_INFO.badDiscard File: none specified(Allow all discards)Number to load: ALLNumber to skip: 1Errors allowed: 999999999Continuation: none specifiedPath used:Direct - with parallel option.Table "USER_INFO", loaded from every logical record.Insert option in effect for this table: APPENDColumn Name Position Len Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------MSISDNFIRST* , O(') CHARACTER PROVINCE_CODE NEXT* , O(') CHARACTER CREATE_TIMENEXT* , O(') CHARACTER SQL string for column : "to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')"SQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.Table "USER_INFO":3 Rows successfully loaded.0 Rows not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.0 Rows not loaded because all fields were null.Bind array size not used in direct path.Column array rows : 10000Stream buffer bytes: 256000Read buffer bytes:20971520Total logical records skipped:1Total logical records read: 3Total logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 1Total stream buffers loaded by SQL*Loader load thread: 0Run began on Fri Nov 01 11:20:07 Run ended on Fri Nov 01 11:20:13 Elapsed time was:00:00:05.60CPU time was: 00:00:00.44