Oracle快速导出数据文件

最新推荐文章于 2024-07-08 16:10:39 发布

lubinsu

最新推荐文章于 2024-07-08 16:10:39 发布

阅读量2.4k

点赞数

本文链接：https://blog.csdn.net/lubinsu/article/details/46953021

版权

Oracle 同时被 3 个专栏收录

90 篇文章 0 订阅

订阅专栏

Java

20 篇文章 0 订阅

订阅专栏

Linux

19 篇文章 0 订阅

订阅专栏

数据文件生成方式有多种，但是大多效率低下，对于大型数据仓库来说，高效导出文件是迫切需求：

这里通过shell+java的方式集成老熊写的数据文件生成工具，效率比普通的导出方法高70%：

shell文件生成脚本：

#!/usr/bin/bash
#created by lubinsu
#2015.07.13
source ~/.bash_profile
srcDbNm=$1
srcDb=`sed -n /${srcDbNm}=/p ${etl_dispatch}/java_etl/connectorCfg | awk -F= '{print $2}'`
descDir=$2
srcSql=$3
fileNm=$4
echo ${srcSql} > ${descDir}/$$.sql
${etl_dispatch}/java_etl/sqluldr2_aix64_9208.bin USER=${srcDb} sql=${descDir}/$$.sql head=no FILE=${descDir}/${fileNm} field='^'
rm ${descDir}/$$.sql

Java调用脚本：这里implement Callable接口，实现了多线程的调用方式：

/**   
 * @Title: Db2FileSync.java
 * @Package etl.etlUtils
 * @Description: TODO
 * @author Lubin Su
 * @date 2015年7月15日 下午12:43:09
 * @version V1.0   
 */
package etl.etlUtils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.Callable;

import etl.dao.impl.GenericDaoImpl;

/**
 * 
 * @author Lubin Su
 * @date 2015年7月15日 下午12:43:09
 * @version V1.0
 */
public class Db2FileSync implements Callable<Object> {
	
	private String procId;
	private String srcDb;
	private String destDir;
	private String srcSql;
	private String fileNm;
	private String statCycleId;

	/**
	 * <p>Title: 构造函数</p>
	 * <p>Description: </p>
	 * @param procId
	 * @param srcDb
	 * @param destDir
	 * @param srcSql
	 * @param fileNm
	 * @param statCycleId
	 */
	public Db2FileSync(String procId, String srcDb, String destDir,
			String srcSql, String fileNm, String statCycleId) {
		super();
		this.procId = procId;
		this.srcDb = srcDb;
		this.destDir = destDir;
		this.srcSql = srcSql;
		this.fileNm = fileNm;
		this.statCycleId = statCycleId;
	}
	/**
	 * 数据表以文件的方式下发
	 * @author lubinsu
	 * @date 2015年7月12日
	 * @param procId
	 * @throws UnsupportedEncodingException 
	 */
	public void db2FileExtractByProcInfo(String srcDb, String descDir, String srcSql, String fileNm) throws UnsupportedEncodingException {
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date vStartTime = new Date();
		System.out.println("Start sending ... " + sdf.format(vStartTime));
		String[] cmd = new String[5];
		//环境变量
		String[] env = {"lubinsu_dir=/int_file/lubinsu","AUTHSTATE=compat","TERM=vt100","SHELL=/usr/bin/bash","NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK","SSH_CLIENT=132.228.78.73 49796 22","SSH_TTY=/dev/pts/4","LOCPATH=/usr/lib/nls/loc","USER=css_int","ODMDIR=/etc/objrepos","ORACLE_BASE=/oracle/app/oracle","TMOUT=0","MAIL=/usr/spool/mail/css_int","PATH=:/usr/java6_64/bin:/opt/freeware/bin:/usr/local/bin:/bin:/usr/bin:/usr/sbin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/home/css_app/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/urs/ccs/bin:/bin:/usr/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin:.","css_int_HOME=/int_file","LOGIN=css_int","PWD=/int_file/lubinsu/scripts/etl_dispatch/css_etl","JAVA_HOME=:/usr/java6_64","EDITOR=vi","JAVA_COMPILER=NONE","LANG=AMERICAN_AMERICA.ZHS16GBK","etl_dir=/int_file/etl_dir","TZ=Asia/Shanghai","LIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib","SHLVL=1","HOME=/int_file","etl_dispatch=/int_file/lubinsu/scripts/etl_dispatch/css_etl","LC__FASTMSG=true","MAILMSG=[YOU HAVE NEW MAIL]","LOGNAME=css_int","CLASSPATH=::/usr/java6_64/lib","SSH_CONNECTION=132.228.78.73 49796 132.228.27.132 22","CLCMD_PASSTHRU=1","SHLIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib","ODS_HOME=/int_file/ODS_ETL/","ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_2","_=/bin/env","OLDPWD=/int_file/lubinsu/etl_data","NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat","LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib"};
		Process prc = null;
		int exitVal = 0;
		cmd[0] = "/int_file/lubinsu/scripts/etl_dispatch/css_etl/java_etl/db2File.sh";
		cmd[1] = srcDb;
		cmd[2] = descDir;
		cmd[3] = srcSql;
		cmd[4] = fileNm;
		try {
			prc = Runtime.getRuntime().exec(cmd, env);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		InputStream stderr = prc.getErrorStream();
		InputStreamReader isr = new InputStreamReader(stderr);
		BufferedReader br = new BufferedReader(isr);
		String line = null;
		//打印返回信息
		try {
			while ((line = br.readLine()) != null)
				System.out.println(line);
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			exitVal = prc.waitFor();
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		//打印返回号
		if (exitVal > 0) {
			System.out.println("Process exitValue: " + exitVal);
		}
		//获取结束时间
		Date vEndTime = new Date();
		System.out.println("End sending ... " + sdf.format(vEndTime) + ",elapsed time: " + (vEndTime.getTime() - vStartTime.getTime())/1000 + " seconds.");
	}
	/**
	 * 
	 * @return
	 * @throws Exception
	 * @see java.util.concurrent.Callable#call()
	 */
	@Override
	public Object call() throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date vStartDt = new Date();
		GenericDaoImpl.update("INSERT INTO shell_proc_db_2_db_log(proc_id, run_msg, status, stat_cycle_id) VALUES(?, ?, ?, ?)", new String[]{procId, sdf.format(vStartDt) + " 文件开始导出", "2", statCycleId});
		db2FileExtractByProcInfo(srcDb, destDir, srcSql, fileNm.toUpperCase() + "_" + statCycleId + ".DEL");
		
		GenericDaoImpl.update("UPDATE shell_proc_db_2_db_log o SET o.status = 0, modify_dt = SYSDATE, run_msg = o.run_msg || CHR(13) || to_char(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') || ' 文件生成结束' WHERE proc_id = ? and stat_cycle_id = ?", new String[] { procId, statCycleId});
		GenericDaoImpl.update("UPDATE shell_proc_db_2_db_cfg o SET o.last_succ_cycle = ? WHERE o.proc_id = ?", new String[] { statCycleId, procId});
		return null;
	}

}

获取配置的流程信息：

public static List<Map<String, Object>> getProcIdForDb2File() {
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
		Date vNowDate = new Date();
		String vNowDateStr = sdf.format(vNowDate);
		ResultSetHandler rsh = new BeanListHandler();
		String vRunSql = "SELECT proc_id, src_db, src_sql, dest_dir, file_nm, stat_cycle_id\n" +
						"  FROM (SELECT o.run_type,\n" + 
						"               o.proc_id,\n" + 
						"               o.src_db,\n" + 
						"               o.src_sql,\n" + 
						"               o.dest_dir,\n" + 
						"               o.file_nm,\n" + 
						"               o.last_succ_cycle,\n" + 
						"               CASE\n" + 
						"                 WHEN o.run_type = 2 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 3 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 4 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - o.run_hour, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 1 THEN\n" + 
						"                  to_char(add_months(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), -1), 'yyyymm')\n" + 
						"               END stat_cycle_id\n" + 
						"          FROM shell_proc_db_2_db_cfg o\n" + 
						"         WHERE o.etl_type = 'Db2File'\n" + 
						"           AND o.state = 'A'\n" + 
						"           AND CASE\n" + 
						"                 WHEN o.run_type = 2 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 3 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 4 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 1 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymm') || o.run_day || o.run_hour || o.run_minute, 'yyyymmddhh24mi')\n" + 
						"               END <= SYSDATE) v\n" + 
						" WHERE v.last_succ_cycle < v.stat_cycle_id\n" + 
						"   AND NOT EXISTS (SELECT 1\n" + 
						"          FROM shell_proc_db_2_db_log i\n" + 
						"         WHERE i.proc_id = v.proc_id\n" + 
						"           AND i.stat_cycle_id = v.stat_cycle_id)";
		
		List<Map<String, Object>> arr = null;
		try {
			arr = (List<Map<String, Object>>) GenericDaoImpl.query(vRunSql, null, rsh);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arr;
	}

调用测试：

public static void main(String[] args) throws IOException, InterruptedException {
		int taskSize = 13;
		// 创建一个线程池
		ExecutorService pool = Executors.newFixedThreadPool(taskSize);
		// 创建多个有返回值的任务
		//List<Future> list = new ArrayList<Future>();
		
		/*List<Map<String, Object>> procs = DataSynchronizer.getProcIdForDb2Db();
		for (Map<String, Object> map : procs) {
			Callable<Object> db2Db = new Db2DbSync(map.get("PROC_ID").toString(), map.get("PROC_NAME").toString());
			pool.submit(db2Db);
		}*/
		
		List<Map<String, Object>> db2Files = DataSynchronizer.getProcIdForDb2File();
		for (Map<String, Object> map : db2Files) {
			Callable<Object> db2File = new Db2FileSync(map.get("PROC_ID").toString(), map.get("SRC_DB").toString(), map.get("DEST_DIR").toString(), map.get("SRC_SQL").toString(), map.get("FILE_NM").toString(), map.get("STAT_CYCLE_ID").toString());
			pool.submit(db2File);
		}
		pool.shutdown();
	}