大数据量文件导入数据库

发现问题

最近项目中遇到需要导数据到远程数据库中,数据库服务器与应用程序服务器不在一台服务器上。 之前项目中使用insert all into table A(col1,col2) values (1,1) table A(col1,col2) values(2,2) select 1 from dual,导入600+M文件需要一个小时。 发现还有个3G的文件需要导入,算下来需要5-6小时。效率太差。

代码

分析问题

测试

*1、测试100M文件

1)insert all与sqlldr导入100万数据,发现时间大致都是20分种
2)去掉索引后,sqlldr导入100万数据3分钟就搞定。
3)应用服务器与数据库服务器要隔离,那剩下的问题就是网络传输了
(生产环境在同一个机房,千兆网,应该还可以接受)。
复制代码

*2 测试3G文件

1)测试导入3G文件:使用sqlldr没有删除索引的时候,用了3.3个小时,
   之前使用insert all需要5-6个小时,快了一半
2)测试导入3G文件:使用sqlldr并删除索引,只用了30分钟,
   bingo,就这个了。数据导入后再代码创建索引。
复制代码

结论

  • 1、导入前先删除索引,导完再建索引(建索引可能也要时间)
  • 2、使用sqlldr方式导入

后续

  • 1、能不能设置数据库spool相关设置
  • 2、增加并行度测试:包括单个文件并行和多个文件并行
  • 3、可以使用从系统表中读取表结构、表字段的方式, 实现更灵活的生成CTL方式,实现更自动的导数方法

代码


package tset;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

public class testTimer {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// 写控制文件.ctl
		long startTime=System.currentTimeMillis();
		String fileRoute = "E:\\";// 文件地址路径
		
		String fileName = "CRM_TRATION_EXP_S09_20181025.dat";// 数据文件名
		String tableName = "tration_temp4ctl";// 表名
		String fieldName = "C_TANO,C_CUSTNO,C_CUSTTYPE,C_AGENCYNO,C_FUNDACCO,C_TRADEACCO,C_NETNO,C_RATIONNO,F_BALANCE,C_RATIONTERM,L_DELAY,L_ALLOWFAIL,L_RATIONDATE,F_AGIO,C_RATIONSTATUS,C_FUNDCODE,C_SHARETYPE,L_TOTALTIMES,F_TOTALBALANCE,F_TOTALSHARES,D_FIRSTDATE,D_LASTDATE,D_PROTOCOLENDDATE,D_CLOSEDATE,L_SUCTIMES,L_MAXSUCTIMES,D_CDATE,D_OPENDATE,C_SARATIONNO";
		String colAttr="VARCHAR2,VARCHAR2,CHAR,CHAR,VARCHAR2,VARCHAR2,VARCHAR2,VARCHAR2,NUMBER,VARCHAR2,NUMBER,NUMBER,NUMBER,NUMBER,VARCHAR2,VARCHAR2,CHAR,NUMBER,NUMBER,NUMBER,DATE,DATE,DATE,DATE,NUMBER,NUMBER,DATE,DATE,VARCHAR2";		
	
		String logfileName = tableName+".log";
		String ctlfileName = tableName+".ctl";// 控制文件名
		String cols=processCol(fieldName,colAttr);

		stlFileWriter(fileRoute, fileName, tableName, cols, ctlfileName);
		// 要执行的DOS命令
		String user = "test";
		String psw = "test";
		String Database = "//10.50.101.101:1521/uat";// IP要指向数据库服务器的地址
		
		Executive(user, psw, Database, fileRoute, ctlfileName, logfileName);
		
		long costTime=System.currentTimeMillis()-startTime;
		System.out.println(tableName+"内存:"+(Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory())/1024L+"kb");
		System.out.println(tableName+"内存:"+(Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory())/1024L/1024L+"Mb");
		System.out.println(tableName+"时间:"+costTime/1000f+"秒");
		System.out.println(tableName+"时间:"+costTime/1000/60/60f+"小时");
	}
	
	private static String processCol(String fieldName,String colAttr){
		String[] fields=fieldName.split(",");
		String[] attrs=colAttr.split(",");
		
		StringBuffer sb = new StringBuffer("(");
		for (int i=0;i<attrs.length;i++) {
			sb.append(fields[i]);
			if(attrs[i].equalsIgnoreCase("DATE")){
				sb.append(" DATE\"yyyy-MM-dd\"");
			}
			sb.append(",");
		}

		if (sb.charAt(sb.length() - 1) == ',') {
			sb.deleteCharAt(sb.length() - 1);
		}
		sb.append(")");
		
		return sb.toString();
	}
	
    
    /**
     * * 写控制文件.ctl
     * @param fileRoute 数据文件地址路径
     * @param fileName 数据文件名
     * @param tableName 表名
     * @param fieldName 要写入表的字段
     * @param ctlfileName 控制文件名
     * 
     * String strctl = "OPTIONS (skip=0)" +   // 0是从第一行开始  1是 从第二行
        " LOAD DATA INFILE '"+fileRoute+""+fileName+"'" +
        " APPEND INTO TABLE "+tableName+"" + //覆盖写入
        " FIELDS TERMINATED BY ',' " +  //--数据中每行记录用","分隔 ,TERMINATED用于控制字段的分隔符,可以为多个字符。
        " OPTIONALLY  ENCLOSED BY \"'\"" +  //源文件有引号 '',这里去掉    ''''"
        " TRAILING NULLCOLS "+fieldName+"";//--表的字段没有对应的值时允许为空  源数据没有对应,写入null
     */
	public static void stlFileWriter(String fileRoute, String fileName,String tableName, String fieldName, String ctlfileName) {
		FileWriter fw = null;
		String strctl = "OPTIONS (skip=0)" + " LOAD DATA INFILE '" + fileRoute
				+ "" + fileName + "'" + "truncate  INTO TABLE " + tableName + ""
				+ " FIELDS TERMINATED BY ''" +
				// " FIELDS TERMINATED BY '0x0'" +
				// " OPTIONALLY  ENCLOSED BY \"'\"" +
				" TRAILING NULLCOLS " + fieldName + "";
		try {
			//File file = new File(fileRoute + "" + ctlfileName);
			//file.deleteOnExit();
			fw = new FileWriter(fileRoute + "" + ctlfileName);
			fw.write(strctl);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fw.flush();
				fw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
    
    /**
     * 调用系统DOS命令
     * @param user 
     * @param psw
     * @param Database
     * @param fileRoute 文件路径
     * @param ctlfileName 控制文件名
     * @param logfileName 日志文件名
     */
    public static void Executive(String user,String psw,String Database,String fileRoute,String ctlfileName,String logfileName)
    {
        InputStream ins = null;
        //要执行的DOS命令  --数据库  用户名  密码  user/password@database
        String dos="sqlldr "+user+"/"+psw+"@"+Database+" control="+fileRoute+""+ctlfileName+" log="+fileRoute+""+logfileName;
        System.out.println(dos); // 输出
        String[] cmd = new String[]{ "cmd.exe", "/C", dos }; // 命令
        int lineCount=0;
        try
        {
            Process process = Runtime.getRuntime().exec(cmd);
            ins = process.getInputStream(); // 获取执行cmd命令后的信息
            
            BufferedReader reader = new BufferedReader(new InputStreamReader(ins));
            String line = null;
            while ((line = reader.readLine()) != null){
            	lineCount++;
            }
            System.out.println("行数:"+lineCount);
            
            int exitValue = process.waitFor();
            if(exitValue==0){
                System.out.println("------返回值:" + exitValue+"\n数据导入成功-----");
            }else{
                System.out.println("------返回值:" + exitValue+"\n数据导入失败-----");
            }
            process.getOutputStream().close(); // 关闭
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}

复制代码

才开始写博客,记录自己的一步一行

转载于:https://juejin.im/post/5bee1465f265da611f074599

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值