本文详细介绍了如何使用Java和SQL*Loader将3TB的文本数据高效导入Oracle数据库，包括数据表创建、控制文件设计、处理空值和文件格式问题，以及并行导入的优化技巧。
最近工作将3T左右的数据导入oracle，按月分类了文件夹，每个文件夹有当月每天的文本文件，每月大概300G。
第一步：分析原始数据文件的格式，创建表，由于数据量大，按每天进行分区，即使这样，每个分区也有10G左右的大小，oracle建议单个表不超过2G，查询应该会较慢
，另原始文件的第一列不需要
create table T_GPSDATA_WEIXINGDINGWEI
(
bus_id VARCHAR2(96) not null,
line_id VARCHAR2(96) not null,
child_line_id VARCHAR2(96) not null,
orgz_id NUMBER,
lct_status NUMBER,
lng NUMBER(15,6),
lat NUMBER(15,6),
lct_altitude NUMBER(15,6),
lct_time DATE,
lct_speed NUMBER(15,2),
lct_direction NUMBER(38,2),
edr_speed NUMBER(15,2),
edr_miles NUMBER(15,2)
PARTITION BY RANGE (LCT_TIME)
INTERVAL(NUMTODSINTERVAL(1,'DAY'))
STORE IN (GPSDATA_WEIXINGDINGWEI)
(
PARTITION LCT_TIME_PART01 VALUES LESS THAN (TO_DATE('2015-05-01','yyyy-mm-dd')) TABLESPACE GPSDATA_WEIXINGDINGWEI
);
第二步：创建控制文件的初步模型，导入过程发现了一些问题，需要调整控制文件
load data
characterset AL32UTF8
infile 'E:\gpsdata\201506\weixingdingwei\卫星定位信息2015060205'
append into table T_GPSDATA_WEIXINGDINGWEI
fields terminated by ','
( terminated,
bus_id,
line_id,
child_line_id,
orgz_id,
lct_status,
lng,
lat,
lct_altitude,
lct_time "to_date(:lct_time,'''yyyy-mm-dd hh24:mi:ss''')",
lct_speed,
lct_direction,
edr_speed,
edr_miles
)
第三步：实验发现几个问题
第一：文本类型不是txt格式，这个容易，直接写个bat将所有文件类型转换成txt格式
第二：打开文本文件发现是UTF-16LE格式，这个亲身试验，导入报错，手动改成utf-8格式，就可以导入，在网上找了个文本格式转换的java源代码，具体参考博文后面的BatchConvertFileEncode.java
第三：lct_time,
bus_id,
line_id,
child_line_id的有些记录为空(2个双引号括起来，中间没有任何内容)，并且原始数据文件每个字段都用双引号括起来了，控制文件修改如下：
第四：每个文件夹有几百个文件，我不可能手动输入infile的每个具体的文件位置及名称，还有每个文件的名称都包含中文，也会报错，用java解决，因此自己写了个代码，具体
参考博文后面的zip3.java
options(errors=999999999) //允许的错误数，根据需要修改
load data
characterset AL32UTF8
infile 'E:\gpsdata\201506\weixingdingwei\
卫星定位信息2015060205
'
append into table T_GPSDATA_WEIXINGDINGWEI
fields terminated by ',' //表示每个字段由逗号分隔
OPTIONALLY ENCLOSED BY '"' //表示字段由双引号括起来
( terminated filler, //加上filler表示不导入该列
bus_id "nullif(:bus_id,'')", //nullif对该字段做判断，如果为''(中间没有字符)，则插入null显示在oracle就是空
line_id "nullif(:line_id,'')",
child_line_id "nullif(:child_line_id ,'')",
orgz_id,
lct_status,
lng,
lat,
lct_altitude,
lct_time "to_date(nullif(:lct_time,''),'''yyyy-mm-dd hh24:mi:ss''')",
lct_speed,
lct_direction,
edr_speed,
edr_miles
)
最终通过java代码生成如下的控制文件，(zip3.java生成的不完全是，主要是表的字段不一样，这个手动好改)：
点击(
此处)折叠或打开
options
(errors=999999999
)
load
data
characterset AL32UTF8
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060114.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060115.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060116.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060117.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060118.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060119.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060120.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060121.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060122.txt'
infile
'E:\gpsdata\201506\weixingdingwei2\weixingdingwei2015060123.txt'
append
into
table T_GPSDATA_WEIXINGDINGWEI
fields
terminated
by
','
OPTIONALLY ENCLOSED
BY
'"'
(
terminated filler
,
bus_id
"nullif(:bus_id,'')"
,
line_id
"nullif(:line_id,'')"
,
child_line_id
"nullif(:child_line_id ,'')"
,
orgz_id
,
lct_status
,
lng
,
lat
,
lct_altitude
,
lct_time
"to_date(nullif(:lct_time,'null'),'''yyyy-mm-dd hh24:mi:ss''')"
,
lct_speed
,
lct_direction
,
edr_speed
,
edr_miles
)
第四步：在处理好的文件夹下，输入cmd，输入：sqlldr userid=gpsdata/gpsdata@its0 control=000input.ctl rows=100160 readsize=20971520 bindsize=20971520 PARALLEL=TRUE
这里用的是并行的(
PARALLEL=TRUE)方式导入，速度较快，具体可参考sqlldr性能优化
http://blog.csdn.net/kangkangwanwan/article/details/51869994
另外详细的sqlldr参数可参考
https://wenku.baidu.com/view/63737c19cc7931b765ce1517.html
------附上2个java代码
BatchConvertFileEncode.java的内容，只需修改路径，文件转换前后的格式，文件类型即可
点击(
此处)折叠或打开
package test
;
import
java
.
io
.
File
;
import
java
.
io
.
FileFilter
;
import
java
.
io
.
FileInputStream
;
import
java
.
io
.
FileOutputStream
;
import
java
.
io
.
IOException
;
import
java
.
io
.
InputStreamReader
;
import
java
.
io
.
OutputStreamWriter
;
import
java
.
util
.
ArrayList
;
import
java
.
util
.
List
;
/**
* 批量转换文件编码
*
* @date 2014-10-23
*
* @author xsoftlab.net
*/
public
class BatchConvertFileEncode
{
/**
* 获取文件或文件夹 不存在则创建
*
* @param path
* 文件或文件夹路径
* @return 已有/新创建的文件
* @throws IOException
* 可能产生的异常
*/
public
static
File
getFile
(
String path
)
throws
IOException
{
File
file
=
new
File
(path
)
;
if
(
file
.
isDirectory
(
)
)
{
if
(
!
file
.
exists
(
)
)
file
.
mkdirs
(
)
;
}
else
{
// 判断目标文件所在的目录是否存在
if
(
!
file
.
getParentFile
(
)
.
exists
(
)
)
file
.
getParentFile
(
)
.
mkdirs
(
)
;
if
(
!
file
.
exists
(
)
)
file
.
createNewFile
(
)
;
}
return
file
;
}
/**
* 递归查找指定后缀名的文件
*
* @param folder
* 目标文件夹
* @param suffix
* 目标后缀名
* @return 找到的文件集合
*/
public
static
List
<
File
> searchFile
(
File folder
,
final
String suffix
)
{
List
<
File
>
result
=
new
ArrayList
<
File
>
(
)
;
File
[
] subFolders
= folder
.
listFiles
(
new
FileFilter
(
)
{
// 运用内部匿名类获得文件
@
Override
public
boolean
accept
(
File pathname
)
{
// 实现FileFilter类的accept方法
if
(pathname
.
isDirectory
(
)
|
|
(pathname
.
isFile
(
)
&
& pathname
.
getName
(
)
.
toLowerCase
(
)
.
endsWith
(suffix
.
toLowerCase
(
)
)
)
)
// 根据文件后缀名过滤
return true
;
return false
;
}
}
)
;
if
(subFolders
!
=
null
)
{
for
(
File
file
: subFolders
)
{
if
(
file
.
isFile
(
)
)
{
// 如果是文件则将文件添加到结果列表中
result
.
add
(
file
)
;
}
else
{
// 如果是文件夹，则递归调用本方法，然后把所有的文件加到结果列表中
result
.
addAll
(searchFile
(
file
, suffix
)
)
;
}
}
}
return
result
;
}
/**
* 单个文件转换编码
*
* @param file
* 要转换的文件
* @param tarFile
* 转换后的文件
* @param charset
* 转换前的编码
* @param tarCharset
* 转换后的编码
* @throws IOException
* 可能出现的异常
*/
public
static
void convertFileEncode
(
File
file
,
File tarFile
,
String
charset
,
String tarCharset
)
throws
IOException
{
InputStreamReader
reader
=
null
;
OutputStreamWriter
writer
=
null
;
int
length
;
char
[
] b
=
new
char
[3
* 1024
]
;
try
{
// 打开文件输出流
reader
=
new
InputStreamReader
(
new
FileInputStream
(
file
)
,
charset
)
;
writer
=
new
OutputStreamWriter
(
new
FileOutputStream
(tarFile
)
,
tarCharset
)
;
while
(
(
length
=
reader
.
read
(b
)
)
!
=
-1
)
{
writer
.
write
(b
, 0
,
length
)
;
writer
.
flush
(
)
;
}
}
finally
{
// 关闭文件流
if
(
reader
!
=
null
)
reader
.
close
(
)
;
if
(
writer
!
=
null
)
writer
.
close
(
)
;
}
}
/**
* 根据文件夹批量转换编码
*
* @param folder
* 要转换的文件夹
* @param tarFolder
* 输出文件夹
* @param charset
* 转换前的编码
* @param tarCharset
* 转换后的编码
* @param suffix
* 要转换的文件后缀名
* @throws IOException
* 可能出现的异常
*/
public
static
void convertFileEncodeByFolder
(
String folder
,
String tarFolder
,
String
charset
,
String tarCharset
,
String suffix
)
throws
IOException
{
String relTar
=
null
;
List
<
File
>
result
= searchFile
(
new
File
(folder
)
, suffix
)
;
// 调用方法获得文件数组
System
.out
.
println
(
"找到 "
+
result
.
size
(
)
+
" 个需要转换的文件"
)
;
// 文件尾部处理
if
(
!folder
.
endsWith
(
"/"
)
&
&
!folder
.
endsWith
(
"\\"
)
)
folder
+
=
File
.separator
;
if
(
!tarFolder
.
endsWith
(
"/"
)
&
&
!tarFolder
.
endsWith
(
"\\"
)
)
tarFolder
+
=
File
.separator
;
for
(
File
file
:
result
)
{
// 使目标文件夹目录层次与源文件夹对应
relTar
= tarFolder
+
file
.
getAbsolutePath
(
)
.
replace
(folder
.
replace
(
"/"
,
"\\"
)
,
""
)
;
convertFileEncode
(
file
,
getFile
(relTar
)
,
charset
, tarCharset
)
;
}
System
.out
.
println
(
"转换成功!"
)
;
}
public
static
void main
(
String
[
] args
)
{
try
{
convertFileEncodeByFolder
(
"E:\\gpsdata\\201506\\weixingdingwei"
,
"E:\\gpsdata\\201506\\weixingdingwei2"
,
"UTF-16LE"
,
"UTF-8"
,
"txt"
)
;
}
catch
(
IOException e
)
{
e
.
printStackTrace
(
)
;
}
}
}
zip3.java，其中
zipPath是文件夹目录，
ctlfile是控制文件名称，
oldstr，
newstr意思是需要将文件名称内容为
oldstr替换为newstr，
里面有些函数，比如重命名文件，比如自动创建控制文件(主要是为了不手动输入每个文件的位置名称)等，根据需要运行，注释不需要的功能即可
点击(
此处)折叠或打开
package test
;
import
java
.
io
.
BufferedInputStream
;
import
java
.
io
.
BufferedOutputStream
;
import
java
.
io
.
BufferedWriter
;
import
java
.
io
.
File
;
import
java
.
io
.
FileInputStream
;
import
java
.
io
.
FileNotFoundException
;
import
java
.
io
.
FileOutputStream
;
import
java
.
io
.
FileWriter
;
import
java
.
io
.
FilenameFilter
;
import
java
.
io
.
IOException
;
import
java
.
util
.
ArrayList
;
import
java
.
util
.
zip
.
ZipEntry
;
import
java
.
util
.
zip
.
ZipInputStream
;
public
class zip3
{
public
static
void main
(
String
[
] args
)
throws
IOException
{
// File dir = new File("F:\\ICDATA\\ic卡数据txt版\\201511\\20151108");
// String strcmd = "cmd /c start F:\\ICDATA\\ic卡数据txt版\\201511\\20151108\\000.bat"; //调用我们在项目目录下准备好的bat文件，如果不是在项目目录下，则把“你的文件名.bat”改成文件所在路径。
// run_cmd(strcmd,dir); //调用上面的run_cmd方法执行操作
String zipPath
=
"E:\\gpsdata\\201506\\weixingdingwei"
;
String ctlfile
=
"000input.ctl"
;
String oldstr
=
"卫星定位信息"
;
String newstr
=
"weixingdingwei"
;
ArrayList
<
String
> filelist
=
getFiles
(
new
ArrayList
<
String
>
(
)
,zipPath
)
;
for
(
String str
:filelist
)
{
System
.out
.
println
(str
)
;
/*批量重命名文件*/
renameFile
(str
,oldstr
,newstr
)
;
/*自动创建控制文件*/
// createCtlFile(ctlfile,str);
/*自动拷贝bat文件*/
// copyFile("F:\\ICDATA\\icdatatxt\\000自动导入.bat",str+"\\000自动导入.bat");
/*自动执行bat文件*/
// File dir = new File(str);
// String strcmd = "cmd /c start "+str+"\\000.bat"; //调用我们在项目目录下准备好的bat文件，如果不是在项目目录下，则把“你的文件名.bat”改成文件所在路径。
// run_cmd(strcmd,dir); //调用上面的run_cmd方法执行操作
// try {
// Thread.sleep(60000);
// } catch (InterruptedException e1) {
// e1.printStackTrace();
// }
/*自动执行数据导入*/
// File dir = new File(str);
// String strcmd = "cmd /c start "+str+"\\000自动导入.bat"; //调用我们在项目目录下准备好的bat文件，如果不是在项目目录下，则把“你的文件名.bat”改成文件所在路径。
// run_cmd(strcmd,dir); //调用上面的run_cmd方法执行操作
// try {
// Thread.sleep(60000);
// break;
// } catch (InterruptedException e1) {
// e1.printStackTrace();
// }
/*自动删除000.txt文件*/
// File f = new File(str+"\\000.txt"); // 输入要删除的文件位置
// if(f.exists())
// f.delete();
// else
// System.out.println("未找到文件");
}
}
static
void
renameFile
(
String path
,
String oldstr
,
String newstr
)
{
File filename
=
new
File
(path
)
;
File
[
] filelist
= filename
.
listFiles
(
)
;
for
(
File
file
:filelist
)
{
String oldname
=
file
.
getName
(
)
;
//System.out.println(oldname);
if
(oldname
.
indexOf
(
".txt"
)
!
=
-1
)
{
if
(oldname
.
indexOf
(oldstr
)
!
=
-1
)
{
String newname
= oldname
.
replace
(oldstr
, newstr
)
;
System
.out
.
println
(newname
)
;
file
.
renameTo
(
new
File
(path
+
'\\'
+newname
)
)
;
}
}
else
{
System
.out
.
println
(
"不是txt类型文件"
)
;
}
}
}
/*创建一个指内容的ctl文件*/
static
void createCtlFile
(
String ctlfile
,
String filePath
)
throws
IOException
{
String data1
=
"load data\r\n"
;
String data2
=
"characterset AL32UTF8\r\n"
;
String data3
=
"infile '"
;
String data4
=
"\\"
;
String data5
=
"'"
;
String data6
=
"\r\n"
;
String datamonth
=
"append into table T_GPSDATA_INSTATION\r\n"
;
String datatable
=
"fields terminated by ','\r\n"
+
"( terminated filler,\r\n"
+
" bus_id,\r\n"
+
" line_id,\r\n"
+
" child_line_id,\r\n"
+
" orgz_id,\r\n"
+
" lct_status,\r\n"
+
" lng,\r\n"
+
" lat,\r\n"
+
" lct_altitude,\r\n"
+
" lct_time "
+
"\""
+
"to_date(:lct_time,'''yyyy-mm-dd hh24:mi:ss''')"
+
"\",\r\n"
+
" lct_speed,\r\n"
+
" lct_direction,\r\n"
+
" edr_speed,\r\n"
+
" edr_miles,\r\n"
+
" time_in "
+
"\""
+
"to_date(:time_in,'''yyyy-mm-dd hh24:mi:ss''')"
+
"\",\r\n"
+
" next_stop_id\r\n"
+
")"
;
String filenameTemp
= filePath
+
"\\"
+ctlfile
;
//文件路径+文件名称
System
.out
.
println
(filenameTemp
)
;
File filename
=
new
File
(filePath
)
;
File
[
] strarr
= filename
.
listFiles
(
)
;
File
file
=
new
File
(filenameTemp
)
;
if
(
!
file
.
exists
(
)
)
{
try
{
file
.
createNewFile
(
)
;
}
catch
(
IOException e
)
{
// TODO Auto-generated catch block
e
.
printStackTrace
(
)
;
}
}
byte
[
] byteindata1
= data1
.
getBytes
(
)
;
byte
[
] byteindata2
= data2
.
getBytes
(
)
;
try
{
FileOutputStream
output
=
new
FileOutputStream
(filenameTemp
)
;
try
{
output
.
write
(byteindata1
)
;
output
.
write
(byteindata2
)
;
byte
[
] byteinfilepath
= filePath
.
getBytes
(
)
;
byte
[
] byteindata3
= data3
.
getBytes
(
)
;
byte
[
] byteindata4
= data4
.
getBytes
(
)
;
byte
[
] byteindata5
= data5
.
getBytes
(
)
;
byte
[
] byteindata6
= data6
.
getBytes
(
)
;
byte
[
] byteindatamonth
= datamonth
.
getBytes
(
)
;
byte
[
] byteindatatable
= datatable
.
getBytes
(
)
;
for
(
int i
= 0
; i
< strarr
.
length
; i
+
+
)
{
//System.out.println(strarr[i].getName());
if
(strarr
[i
]
.
getName
(
)
.
equals
(
"000input.ctl"
)
)
{
continue
;
}
byte
[
] byteinstrname
= strarr
[i
]
.
getName
(
)
.
getBytes
(
)
;
output
.
write
(byteindata3
)
;
output
.
write
(byteinfilepath
)
;
output
.
write
(byteindata4
)
;
output
.
write
(byteinstrname
)
;
output
.
write
(byteindata5
)
;
output
.
write
(byteindata6
)
;
}
output
.
write
(byteindatamonth
)
;
output
.
write
(byteindatatable
)
;
output
.
flush
(
)
;
output
.
close
(
)
;
}
catch
(
IOException e
)
{
// TODO Auto-generated catch block
e
.
printStackTrace
(
)
;
}
}
catch
(
FileNotFoundException e
)
{
// TODO Auto-generated catch block
e
.
printStackTrace
(
)
;
}
}
/*复制文件到指定目录下*/
static
void copyFile
(
String path
,
String copyPath
)
{
try
{
//path: "f://downloads//kon.jpg"
FileInputStream input
=
new
FileInputStream
(path
)
;
//可替换为任何路径何和文件名
//copyPath: "f://kon.jpg"
FileOutputStream
output
=
new
FileOutputStream
(copyPath
)
;
//可替换为任何路径何和文件名
int
in
=input
.
read
(
)
;
while
(in!=-1
)
{
output
.
write
(
in
)
;
in
=input
.
read
(
)
;
}
}
catch
(
IOException e
)
{
System
.out
.
println
(e
.
toString
(
)
)
;
}
}
/*
* 通过递归得到某一路径下所有的目录及其文件
*/
static
ArrayList
<
String
>
getFiles
(
ArrayList
<
String
> filelist
,
String filePath
)
{
File root
=
new
File
(filePath
)
;
File
[
] files
= root
.
listFiles
(
)
;
for
(
File
file
:files
)
{
if
(
file
.
isDirectory
(
)
)
{
getFiles
(filelist
,
file
.
getAbsolutePath
(
)
)
;
}
else
{
int a
=0
;
String fileParentPath
=
file
.
getParent
(
)
;
for
(
String str
:filelist
)
{
if
(str
.
endsWith
(fileParentPath
)
)
{
a
=1
;
}
}
if
(a
=
=0
)
{
filelist
.
add
(
file
.
getParent
(
)
)
;
//System.out.println("显示:"+fileParentPath);
}
}
}
return filelist
;
}
/*
* 添加问价后缀
*/
static
void getFileNames
(
String path
)
{
File
file
=
new
File
(path
)
;
if
(
file
.
exists
(
)
)
{
File
[
] files
=
file
.
listFiles
(
)
;
if
(files
.
length
=
= 0
)
{
System
.out
.
println
(
"文件夹是空的!"
)
;
return
;
}
else
{
for
(
File file2
: files
)
{
if
(file2
.
isDirectory
(
)
)
{
getFileNames
(file2
.
getAbsolutePath
(
)
)
;
}
else
{
file2
.
renameTo
(
new
File
(file2
.
getParent
(
)
+
"/"
+ file2
.
getName
(
)
+
".txt"
)
)
;
}
}
}
}
else
{
System
.out
.
println
(
"文件不存在!"
)
;
}
}
/*执行bat文件*/
/*指定bat执行目录*/
static
void run_cmd
(
String strcmd
,
File dir
)
{
//
Runtime rt
=
Runtime
.
getRuntime
(
)
;
//Runtime.getRuntime()返回当前应用程序的Runtime对象
Process ps
=
null
;
//Process可以控制该子进程的执行或获取该子进程的信息。
try
{
ps
= rt
.
exec
(strcmd
,
null
,dir
)
;
//该对象的exec()方法指示Java虚拟机创建一个子进程执行指定的可执行程序，并返回与该子进程对应的Process对象实例。
ps
.
waitFor
(
)
;
//等待子进程完成再往下执行。
}
catch
(
IOException e1
)
{
e1
.
printStackTrace
(
)
;
}
catch
(
InterruptedException e
)
{
// TODO Auto-generated catch block
e
.
printStackTrace
(
)
;
}
int i
= ps
.
exitValue
(
)
;
//接收执行完毕的返回值
if
(i
=
= 0
)
{
System
.out
.
println
(
"执行完成."
)
;
}
else
{
System
.out
.
println
(
"执行失败."
)
;
}
ps
.
destroy
(
)
;
//销毁子进程
ps
=
null
;
}
/*不指定执行目录*/
// static void run_cmd(String strcmd) {
// //
// Runtime rt = Runtime.getRuntime(); //Runtime.getRuntime()返回当前应用程序的Runtime对象
// Process ps = null; //Process可以控制该子进程的执行或获取该子进程的信息。
// try {
// ps = rt.exec(strcmd); //该对象的exec()方法指示Java虚拟机创建一个子进程执行指定的可执行程序，并返回与该子进程对应的Process对象实例。
// ps.waitFor(); //等待子进程完成再往下执行。
// } catch (IOException e1) {
// e1.printStackTrace();
// } catch (InterruptedException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
// int i = ps.exitValue(); //接收执行完毕的返回值
// if (i == 0) {
// System.out.println("执行完成.");
// } else {
// System.out.println("执行失败.");
// }
//
// ps.destroy(); //销毁子进程
// ps = null;
// }
/**/
}
扫一扫
5364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
