SQLLOADER的使用

----hushaorui20140914

sqlloader(sqlldr)的用途：

1、主要是大批量的文本数据批量加载到数据库中

2、加载量大概1小时 10G-20G左右（在IO没有瓶颈的情况下），比普通insert操作提高数十倍或者百倍

要求：必须装oracle客户端

 

 

SQLLDR加载数据

测试案例

1、control file文件创建

[ABPCS01]/ratcmid1/2011#visqllaod.sql

"sqllaod.sql"7 lines, 146 characters

LOAD DATA

   INFILE '/home/oracle/a.txt'

   APPEND

   INTO TABLE cs

   Fields terminated by ","

   trailing nullcols

(a,b)

2、原始文件创建

[oracle@SPAYVM1oracle]$ cat a.txt

a,b

c,d

b,e

e,f

ajksjdfkajkdfjkafdjk,1

,2

1,

3、load数据

[ABPCS01]/ratcmid1/2011#sqlldrbtupayprod/1234control=/home/oracle/sqllaod.sql bad=/home/oracle/log/a1.badlog=/home/oracle/log/s.log row=1000;

row=1000 每1000条提交一次

4、查看有问题的数据bad文件

 [oracle@SPAYVM1 oracle]$ cat a1.bad 
ajksjdfkajkdfjkafdjk,1 

5、查看日志

s.log 里面是log信息
[oracle@SPAYVM1 oracle]$ cat s.log 

SQL*Loader: Release 11.2.0.3.0 - Production on Wed Sep 3 10:23:30 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved. 

Control File: sqllaod.sql 
Data File: /home/oracle/a.txt 
Bad File: a.bad 
Discard File: none specified 

(Allow all discards) 

Number to load: ALL 
Number to skip: 0 
Errors allowed: 50 
Bind array: 64 rows, maximum of 256000 bytes 
Continuation: none specified 
Path used: Conventional 

Table CS, loaded from every logical record. 
Insert option in effect for this table: APPEND 
TRAILING NULLCOLS option in effect 

Column Name Position Len Term Encl Datatype 
------------------------------ ---------- ----- ---- ------------------------- 
A FIRST * , CHARACTER 
B NEXT * , CHARACTER 

Record 5: Rejected - Error on table CS, column A. 
ORA-12899: value too large for column"BTUPAYPROD"."CS"."A" (actual: 20, maximum: 2) 


Table CS: 
4 Rows successfully loaded. 
1 Row 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. 


Space allocated for bind array: 33024 bytes(64 rows) 
Read buffer bytes: 1048576 

Total logical records skipped: 0 
Total logical records read: 5 
Total logical records rejected: 1 
Total logical records discarded: 0 

Run began on Wed Sep 03 10:23:30 2014 
Run ended on Wed Sep 03 10:23:51 2014 

Elapsed time was: 00:00:20.74 
CPU time was: 00:00:00.02

 

参数解析

userid -- Oracle 的 username/password[@servicename]
control -- 控制文件，可能包含表的数据

log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log
bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述

参数详解可以参考 http://www.linuxidc.com/Linux/2011-11/46836.htm

 

 

java 

 

 StringBuilder builder = new StringBuilder();
builder.append("sqlldr ").append(dbUsername).append("/").append(dbPassword);
if (dbOracleID != null && !"".equals(dbOracleID)) {
builder.append("@").append(dbOracleID);
}
builder.append(" control=").append(sqlldrControlFile).append(" data=").append(dataFile)
.append(" rows=1000 bindsize=10000000 readsize=10000000");
if (ignoreErr) {
builder.append(" errors=10000000");
}
if (logFile != null && !"".equals(logFile)) {
builder.append(" log=").append(logFile);
}
String sqlldrSql = builder.toString();
log.info("sqlldrSql = {{}}", sqlldrSql);
return sqlldrSql; 

 

sqlldrControlFile 就是类似下面的控制文件

LOAD DATA

    INFILE'/home/oracle/a.txt'

    APPEND

    INTO TABLE cs

    Fields terminated by","

    trailing nullcols

    (a,b)

 

 

----------------------------------------------------------------------------------------------------------

packagecom.bestpay.pas.utils; 

import java.io.BufferedReader; 
import java.io.IOException; 
import java.io.InputStreamReader; 

import lombok.extern.slf4j.Slf4j; 

@Slf4j 
public class ProcessUtil { 
public static int ExecCommand(String command) { 
Process p = null; 
int exitValue = -1; 
BufferedReader reader = null; 
BufferedReader readerErr = null; 
try { 
p = Runtime.getRuntime().exec(command); 
reader = new BufferedReader(new InputStreamReader(p.getInputStream())); 
readerErr = new BufferedReader(newInputStreamReader(p.getErrorStream())); 
exitValue = p.waitFor(); 
String s = null; 
while ((s = reader.readLine()) != null) { 
log.info(s); 
} 
s = null; 
while ((s = readerErr.readLine()) != null) { 
log.error(s); 
} 
} 
catch (Exception e) { 
log.error("Process error: unexpected exception - " + e.getMessage(),e); 
} 
finally { 
if (reader != null) { 
try { 
reader.close(); 
} 
catch (IOException e) { 
log.error("读取流关闭异常！", e); 
} 
} 
if (readerErr != null) { 
try { 
readerErr.close(); 
} 
catch (IOException e) { 
log.error("读取流关闭异常！", e); 
} 
} 
if (null != p) { 
p.destroy(); 
} 
} 
return exitValue; 
} 
}