Sqlldr使用
Sqlldr是oracle为了导入大量数据而提供的一个工具。它可以轻松导入百万级的数据。
命令格式
sqlldr userName/password@dbName control=controlFilePath data=dataFilePath log=logFilePath bad=badFilePath direct=true rows=100160 readsize=20971520 bindsize=20971520 errors=10000
说明
- dbName：数据库名称， userName：用户名，password：密码
- control：控制文件全路径，以上是必须的。
- date：数据文件全路径，一般把control与date分开写，所以date的路径是写在control文件中的。
- log:导入日记输出到哪，bad：导入失败的行输出到哪。这2项不指定默认在control文件所在的目录下
- direct：使用直通路径方式导入，rows：每次提交的记录数，默认: 常规路径 64，readsize：读取的缓冲区大小，bindsize:填充的缓冲区大小
- skip : 跳过记录数,从数据文件中,从第一行开始要计算要跳过的行数 (默认0)
- errors: 允许的错误记录数,当超过则停止导入,oracle12开始不能设置为-1,之前的版本可以,表示不限制
控制文件
用于指定导入数据文件的位置，导入数据与表的关系等。控制文件后缀为ctl。
格式
LOAD DATA
CHARACTERSET ZHS16GBK
INFILE 'E:\card.txt'
truncate INTO TABLE T_CARD_AREA
FIELDS TERMINATED BY ' '
TRAILING NULLCOLS
(START_NUM,END_NUM,AREA_CODE)
说明
- CHARACTERSET ZHS16GBK:导入编码。（不是必须项）
- infile:数据包全路径
- truncate：插入类型。有以下选择：
insert
为缺省方式，在数据装载开始时要求表为空
append 在表中追加新记录replace
删除旧记录(用 delete语句)，替换成新装载的记录
truncate
删除旧记录(用truncate语句)，替换成新装载的记录
4. TERMINATED: 数据分隔符
5. TRAILING NULLCOLS:数据包列没有值时数据库对应字段值为null
6. 最后一行是表字段，最外边是括号，字段间以逗号分隔。
数据列与表的关系
数据包的列多于数据库表的列
在表字段行使用position(x,y)指定把数据包行中多长(字符数)的数据插到数据库列中。
格式：
(PSN POSITION(1:6),PID POSITION(8:10))
实践证明:在sqlldr中一个tab是一个字符
数据包的列少于数据库表的列
如数据包每行只有2列数据，但表有3个字段。
1.FILLER : 数据库表的这一列将不会被插入数据。
格式：
( PSN,PID FILLER, STATUS)
数据包的2列数据会分别赋给PSN,STATUS，PID值为空
2.给表的列设置默认值
格式：
( PSN,PID constant '00000000', STATUS)
数据包的2列数据会分别赋给PSN,STATUS，PID值为00000000
特殊列值
date
数据库列的类型是date类型的,但是数据包是字符串类型.可以在控制文件的表字段列表中设置转换,
格式: 字段名 date 日期格式
如:
LOAD DATA
CHARACTERSET ZHS16GBK
INFILE 'E:\card.txt'
truncate INTO TABLE T_CARD_AREA
FIELDS TERMINATED BY ' '
TRAILING NULLCOLS
(START_NUM,END_NUM,TIM date YYYY-MM-DD hh24:mi:ss)
注意: 在使用java调用sqlldr时,日期格式需要在双引号中,如:
String files = "(START_NUM,END_NUM,TIM date \"YYYY-MM-DD hh24:mi:ss\")"
sysdate
数据库字段的默认值是sysdate,但是导入后发现该列值为空,需要手动指定
格式: 字段 "sysdate"
如:
LOAD DATA
CHARACTERSET ZHS16GBK
INFILE 'E:\card.txt'
truncate INTO TABLE T_CARD_AREA
FIELDS TERMINATED BY ' '
TRAILING NULLCOLS
(START_NUM,END_NUM,TIM "sysdate")
代码调用
Windows和Linux上使用sqlldr略有不同,但是都需要安装oracle客户端,而且客户端必须包含sqlldr和tnsping工具.
验证环境
Windows的打开cmd命令行,Linux的打开命令行,分别输入sqlldr和tnsping orcl (orcl是数据库实例,需要在客户端安装目录下的network/admin/tnsnames.ora中配置),如果两个都没问题,说明sqlldr环境可用.
注意: 客户端默认使用根目录的tnsnames.ora去连接服务端的,如果tnsping orcl出现如下错误:
就是因为tnsnames.ora没有配置服务端信息
可以去客户端的根目录下的tnsnames.ora配置服务端信息或者指定其他的tnsnames.ora,在~/.bashrc加:
export TNS_ADMIN=/home/mlsama/instantclient_11_2/network/admin
如果tnsping失败而且使用sqlldr导入会出现ORA-12154: TNS:could not resolve the connect identifier specified错误
工具类
以下代码windows,Linux都适用,Linux环境下提供了2种解决方法:java直接调用sqlldr命令和把sqlldr命令写入shell脚本,java调用执行shell脚本.出现第二种方式是因为在Linux环境下java直接调用sqlldr命令有时会出现很多莫名其妙的错误,而执行shell脚本可以避免这些问题.
输出缓存区信息工具类
package com.yct.datanalyse.util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
/**
* DESC:异步读取命令的输出类
* AUTHOR:mlsama
* 2020/4/3 10:52
*/
public class StreamGobbler extends Thread {
private static Logger log = LoggerFactory.getLogger(StreamGobbler.class);
/**
* 输出流
*/
InputStream is;
/**
* 输出类型
*/
String type;
StreamGobbler(InputStream is, String type) {
this.is = is;
this.type = type;
}
@Override
public void run() {
try {
InputStreamReader isr = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null){
log.info("sqlldr通道输出:"+type+"-"+line);
}
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
}
shell工具类
package com.yct.datanalyse.util;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
/**
* DESC:执行shell脚本
* AUTHOR:mlsama
* 2020/04/7 17:00
*/
public class ShellUtil {
private final static Logger log = LoggerFactory.getLogger(ShellUtil.class);
/**
* 执行shell脚本
*
* @param scriptPath 脚本路径
* @param para 脚本使用的参数数组
*/
public static Boolean execShell(String scriptPath, String... para) {
Process ps = null;
Boolean execFlag = false;
try {
String[] cmd = new String[]{scriptPath};
//为了解决参数中包含空格
cmd = ArrayUtils.addAll(cmd, para);
//解决脚本没有执行权限
ProcessBuilder builder = new ProcessBuilder("/bin/chmod", "755", scriptPath);
Process process = builder.start();
int chmodRes = process.waitFor();
if (chmodRes == 0){
ps = Runtime.getRuntime().exec(cmd);
StreamGobbler errorGobbler = new StreamGobbler(ps.getErrorStream(), "ERROR");
StreamGobbler outputGobbler = new StreamGobbler(ps.getInputStream(), "OUTPUT");
errorGobbler.start();
outputGobbler.start();
int execResult = ps.waitFor();
if (execResult == 0) {
execFlag = true;
log.info("脚本{}执行成功",scriptPath);
} else {
log.error("脚本{}执行失败",scriptPath);
}
}else {
log.error("脚本{}授权失败",scriptPath);
}
} catch (Exception e) {
log.error("执行脚本{}异常:{}",scriptPath,e);
} finally {
if (ps != null) {
try {
ps.destroy();
} catch (Exception e) {
log.error("销毁Process异常:{}", e);
}
}
}
return execFlag;
}
}
sqlldr工具类
package com.yct.datanalyse.util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Properties;
/**
* DESC:sqlldr工具类
* AUTHOR:mlsama
* 2020/4/1 10:52
*/
public class SqlLdrUtil {
private static final Logger log = LoggerFactory.getLogger(SqlLdrUtil.class);
/**
* 使用sqlldr批量导入数据
* @param user 用户名
* @param password 密码
* @param dbname 数据库名称
* @param tableName 表名
* @param fieldName 表字段列表
* @param contrFilePath 控制文件
* @param dataFilePath 数据包文件路径
* @param sqlldrUseType Linux时使用java还是shell导入
* @param sqlldrConfig sqlldr额外的配置,如:direct=true rows=100000 errors=10000000 readsize=20971520 bindsize=20971520
* @return 是否导入成功
*/ public static boolean insertBySqlLdr(String user, String password, String dbname, String tableName,
String fieldName, String contrFilePath, String dataFilePath,
String sqlldrUseType,String sqlldrConfig) {
log.info("开始对文件{}进行落库", dataFilePath);
boolean flag ;
//生成控制文件
createCtlFile(contrFilePath, dataFilePath, tableName, fieldName);
String logPath = contrFilePath.substring(0, contrFilePath.indexOf(".")) + ".log";
String separator = " ";
String command = new StringBuilder("sqlldr").append(separator)
.append(user).append("/")
.append(password).append("@")
.append(dbname).append(separator)
.append("control=").append(contrFilePath).append(separator)
.append("log=").append(logPath).append(separator)
.append(sqlldrConfig).toString();
log.info("执行的sqllder命令为:{}",command);
// 判断是什么系统
Properties prop = System.getProperties();
String os = prop.getProperty("os.name");
long start = System.currentTimeMillis();
if (os.toLowerCase().startsWith("win")) {
flag = execInWIn(command);
} else {
if ("java".equals(sqlldrUseType)){
flag = execInLinux(command);
}else {
String shellPath = dataFilePath.substring(0,dataFilePath.indexOf(".")) + ".sh";
createShell(command,shellPath);
flag = ShellUtil.execShell(shellPath);
}
}
long end = System.currentTimeMillis();
if (flag) {
log.info("导入文件{}成功，耗时{}s", dataFilePath, (end - start) / 1000);
} else {
log.error("使用sqlldr导入文件{}失败", dataFilePath);
}
return flag;
}
private static void createShell(String command, String shellPath) {
FileWriter fw = null;
try {
String separator = System.getProperty("line.separator");
StringBuilder sb = new StringBuilder();
sb.append("#!/bin/bash")
.append(separator)
.append(command);
fw = new FileWriter(shellPath);
fw.write(sb.toString());
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 写控制文件.ctl
* @param contrFilePath 控制文件地址路径
* @param dataFilePath 数据文件
* @param tableName 表名
* @param fieldName 要写入表的字段:(1,2,...)
*/
private static void createCtlFile(String contrFilePath, String dataFilePath, String tableName, String fieldName) {
FileWriter fw = null;
try {
String separator = System.getProperty("line.separator");
String strctl = "LOAD DATA" + separator
+ "INFILE '" + dataFilePath +"'"+ separator
+ "append INTO TABLE " + tableName + separator
+ "FIELDS TERMINATED BY '\t'"+ separator
+ "TRAILING NULLCOLS" + separator
+ fieldName;
fw = new FileWriter(contrFilePath);
fw.write(strctl);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static boolean execInWIn(String command) {
boolean flag = false;
try {
String[] cmd = new String[] { "cmd.exe", "/C", command }; // 命令
Process process = Runtime.getRuntime().exec(cmd);
StreamGobbler errorGobbler = new StreamGobbler(process.getErrorStream(), "ERROR");
StreamGobbler outputGobbler = new StreamGobbler(process.getInputStream(), "OUTPUT");
errorGobbler.start();
outputGobbler.start();
int exitValue = process.waitFor();
if (exitValue == 0) {
flag = true;
}
process.getOutputStream().close(); // 关闭
} catch (Exception e) {
log.error("win下执行命令失败" + e.getMessage());
}
return flag;
}
public static boolean execInLinux(String command) {
boolean flag = false;
try {
String[] cmd = { "/bin/sh", "-c", command };
Process process = Runtime.getRuntime().exec(cmd);
StreamGobbler errorGobbler = new StreamGobbler(process.getErrorStream(), "ERROR");
StreamGobbler outputGobbler = new StreamGobbler(process.getInputStream(), "OUTPUT");
errorGobbler.start();
outputGobbler.start();
int exitValue = process.waitFor();
if (exitValue == 0) {
flag = true;
}
process.getOutputStream().close(); // 关闭
} catch (Exception e) {
log.error("linux下执行命令失败" + e.getMessage());
}
return flag;
}
}
注意:如果出现错误数据,返回值为非0,即失败,失败数据会被记录到.bad文件中,但是其他数据还是会被导入,无法保证整个文件一定导入成功,可以把errors的值设置大于数据文件行数,如果出现失败,手动去处理.bad文件
问题
Sqlldr和tnsping orcl都正常,但是出现如下错误:
这是密码包含了特殊字符,可以把用户和密码括起来,如下:
sqlldr 'user/"password"'乱码问题
有时候导入的中文字段乱码,这可能是orale客户端与服务端编码不一致或者是数据文件编码与数据库编码不一致导致的.
1. 查看oracle服务器编码
select * from v$nls_parameters where parameter='NLS_CHARACTERSET'
如果是ZHS16GBK,则其编码是GBK,AL32UTF8则是UTF-8
2. 查看oracle客户端编码
查看oracle客户端所在的服务器环境变量NLS_LANG,如果值跟第一步的不一致,则修改
3. 查看数据文件的编码,如果值跟第一步的不一致,则修改
4. 在控制文件中指定编码,如:
LOAD DATA
CHARACTERSET ZHS16GBK
扫一扫
670
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
