Java调用SqlLoader将大文本导入数据库

sunny1009

发布于 2022-05-06 09:09:07

1.1K0

发布于 2022-05-06 09:09:07

文章被收录于专栏：ElasticsearchElasticsearch

Java调用SqlLoader将大文本导入数据库

业务场景:将一千万条数据，大约500M的文本文档的数据导入到数据库

　　分析:通过Java的IO流解析txt文本文档，拼接动态sql实现insert入库，可以实现，缺点如下

第一:IO流解析大文本文件对机器性能要求较高，测试大约消耗2G左右的内存

第二:拼接sql语句insert一千万条数据大约需要2小时时间，长时间insert会锁表，如果是核心业务表，例如订单表，会造成大量用户无法下单，影响数据库的性能

第三:这种操作可扩展性不强，每次只能针对指定的表，指定的列操作

针对以上缺点，现在通过接口调用数据库系统命令实现，通过可视化界面，选择要导入的表，要导入那些字段，上传指定的txt文本，会自动生成对应的模板文件，实现大批量数据高效率的导入到数据库，通过可配置化即可实现，相对前一种思路扩展性较强，

具体接口如下

 1 package com.sun.sqlloader.api;
 2 /**
 3  * SqlLoader接口
 4  * @ClassName: ISqlLoader  
 5  * @author sunt  
 6  * @date 2017年11月15日 
 7  * @version V1.0
 8  */
 9 public interface ISqlLoader {
10 
11     /**
12      * 自动生成控制文件
13      * @Title: ctlFileWriter 
14      * @author sunt  
15      * @date 2017年11月15日
16      * @param fileRoute 数据文件地址路径(文件所在磁盘目录)
17      * @param fileName 数据文件名
18      * @param tableName 表名
19      * @param fieldName 要写入表的字段
20      * @param ctlfileName 控制文件名
21      * @return void
22      */
23     void ctlFileWriter(String fileRoute,String fileName,String tableName,String fieldName,String ctlfileName);
24     
25     /**
26      * 执行系统dos命令
27      * @Title: Executive 
28      * @author sunt  
29      * @date 2017年11月15日
30      * @param user 数据库的用户名
31      * @param pwd 数据库的密码
32      * @param database 连接数据库的地址
33      * @param fileRoute 文件路径
34      * @param ctlfileName 控制文件名
35      * @param logfileName 日志文件名
36      * @return void
37      */
38     void Executive(String user,String pwd,String database,String fileRoute,String ctlfileName,String logfileName);
39 }

 1 package com.sun.sqlloader.api.impl;
 2 
 3 
 4 import java.io.BufferedReader;
 5 import java.io.FileWriter;
 6 import java.io.IOException;
 7 import java.io.InputStream;
 8 import java.io.InputStreamReader;
 9 import java.nio.charset.Charset;
10 import java.util.Date;
11 
12 import org.apache.log4j.Logger;
13 import org.springframework.stereotype.Service;
14 
15 import com.sun.sqlloader.api.ISqlLoader;
16 /**
17  * SqlLoader接口实现
18  * @ClassName: SqlLoaderImpl  
19  * @author sunt  
20  * @date 2017年11月15日 
21  * @version V1.0
22  */
23 @Service
24 public class SqlLoaderImpl implements ISqlLoader{
25 
26     private Logger logger = Logger.getLogger(SqlLoaderImpl.class);
27     
28     @Override
29     public void ctlFileWriter(String fileRoute, String fileName, String tableName, String fieldName,String ctlfileName) {
30         FileWriter fw = null;
31         String strctl = "OPTIONS (skip=0)" + // 0是从第一行开始  1是 从第二行
32         " LOAD DATA CHARACTERSET AL32UTF8 INFILE '"+fileRoute+""+fileName+"'" + //设置字符集编码SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
33         " APPEND INTO TABLE "+tableName+"" + ////覆盖写入
34         " FIELDS TERMINATED BY '\\|'" + //数据中每行记录用","分隔 ,TERMINATED用于控制字段的分隔符，可以为多个字符。|需要转译
35         " OPTIONALLY  ENCLOSED BY \"'\"" + //源文件有引号 ''，这里去掉    ''''"
36         " TRAILING NULLCOLS "+fieldName+"";  //表的字段没有对应的值时允许为空  源数据没有对应，写入null
37         try {
38             fw = new FileWriter(fileRoute + "" + ctlfileName);
39             fw.write(strctl);
40         } catch (IOException e) {
41             e.printStackTrace();
42         } finally {
43             try {
44                 fw.flush();
45                 fw.close();
46             } catch (IOException e) {
47                 logger.error("生成控制器文件异常...");
48                 e.printStackTrace();
49             }
50         }
51     }
52 
53     @Override
54     public void Executive(String user, String pwd, String database, String fileRoute, String ctlfileName,String logfileName) {
55         InputStream ins = null;
56         //要执行的DOS命令  --数据库  用户名  密码  user/password@database
57         String dos="sqlldr "+user+"/"+pwd+"@"+database+" control="+fileRoute+""+ctlfileName+" log="+fileRoute+""+logfileName;
58         logger.info("执行的dos命令:" + dos);
59         String[] cmd = new String[] { "cmd.exe", "/C", dos }; // 命令cmd /c dir：是执行完dir命令后关闭命令窗口cmd /k dir：是执行完dir命令后不关闭命令窗口。
60         try {
61             Process process = Runtime.getRuntime().exec(cmd);
62             ins = process.getInputStream(); // 获取执行cmd命令后的信息
63 
64             BufferedReader reader = new BufferedReader(new InputStreamReader(ins,Charset.forName("GBK")));//解决dos下中文输出乱码
65             String line = null;
66             long startTime = new Date().getTime();
67             while ((line = reader.readLine()) != null) {
68                 logger.info("调用dos执行的结果==========>" + line); // 输出
69             }
70             int exitValue = process.waitFor();
71             if (exitValue == 0) {
72                 logger.info("返回值:" + exitValue + "\n数据导入成功");
73                 logger.info("总共耗时:" + (new Date().getTime() - startTime) / 1000 + "秒");
74             } else {
75                 logger.info("返回值:" + exitValue + "\n数据导入失败");
76             }
77 
78             process.getOutputStream().close(); // 关闭
79         } catch (Exception e) {
80             e.printStackTrace();
81         }
82     }
83     
84 }

生成测试数据的代码

 1 package com.sun.sqlloader;
 2 
 3 import java.io.BufferedWriter;
 4 import java.io.File;
 5 import java.io.FileOutputStream;
 6 import java.io.IOException;
 7 import java.io.OutputStreamWriter;
 8 
 9 /**
10  * 循环将数据按照指定的格式写入文本文件
11  * @ClassName: OperaFile  
12  * @author sunt  
13  * @date 2017年11月15日 
14  * @version V1.0
15  */
16 public class OperaFile {
17 
18     /**
19      * 写数据到文件
20      * @Title: writeFile 
21      * @author sunt  
22      * @date 2017年11月15日
23      * @return void
24      */
25     public static void writeFile(String filePath) throws IOException {
26         File fout = new File(filePath);
27         FileOutputStream fos = new FileOutputStream(fout);
28         BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fos));
29         for (Long i = 0L; i < 10000000; i++) {
30             bw.write(i + "|测试数据"+i+"|");
31             bw.newLine();
32         }
33         bw.close();
34     }
35 }