首页 > 动态语言, 挨踢(IT), 数据库 > 多表关联下的测试数据制造方法(三):使用SQL Loader导入数据

多表关联下的测试数据制造方法(三):使用SQL Loader导入数据

2013年3月12日 发表评论 阅读评论 708 人阅读    

多表关联下的测试数据制造方法系列

  1. 多表关联下的测试数据制造方法(一):思路详解
  2. 多表关联下的测试数据制造方法(二):将数据导出为CSV文件

  在上一节,D瓜哥给大家介绍了一下如何使用存储过程,将数据导出成CSV文件。既然有数据文件了,我们该如何将这些数据导入到数据库中呢?!Oracle数据库提供了SQL Loader这样一个将数据文件导入到数据库中的功能。这篇文章将给大家介绍一下SQL Loader的用法。另外,再炒炒剩饭,把D瓜哥以前使用TCL语言写的脚本分享给大家。

SQL Loader简介

概念

  SQL Loader是ORACLE的数据加载工具,通常用来将操作系统文件(数据)迁移到ORACLE数据库中。SQL Loader是大型数据仓库选择使用的加载方法,因为它提供了最快速的途径(DIRECT,PARALLEL)。

用法

  首先,建立需要导入数据的表。如果存在就不需要新建了。

  其次,编写一个控制文件。内容如下:

LOAD DATA
INFILE 'data.dat'
BADFILE 'data.bad'
DISCARDMAX 1000

INSERT
 INTO TABLE emp
FIELDS TERMINATED BY ","
(
empno NUMBER(4) NOT NULL,
ename CHAR(10),
job CHAR(9),
mgr NUMBER(4),
hiredate DATE 'YYYYMMDD',
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)

  最后,SQL Loader的使用也很简单,只需要一条指令。如下:

sqlldr USERID=scott/tiger CONTROL=load1.ctl DIRECT=TRUE PARALLEL=true
# USERID是配置用户名和密码的
# CONTROL是制定配置文件的
# DIRECT设置导入模式为Direct模式
# PARALLEL设置可以并行,同时导入多个文件

  补充一句,控制文件高亮的一行是控制SQL Loader进行何种操作的。SQL Loader共提供四种操作分类:

  1. insert,为缺省方式,在数据装载开始时要求表为空;
  2. append,在表中追加新记录
  3. replace,删除旧记录,替换成新装载的记录;
  4. truncate,同上

  这里,只给大家介绍这么多。如果想了解详细情况,请阅读Oracle提供的官方文档,文档中详细描述了SQL Loader的方方面面。

TCL导入脚本

  上面简单介绍了一些SQL Loader的一些知识。下面给大家分享要给由TCL写成的使用SQL Loader导入数据的脚本。

  关于TCL语言运行环境的配置,请看“D瓜哥的开源项目:CompareDatamodel”,D瓜哥在这篇文章里做过介绍。

配置文件

  这个配置文件不是上面提到的”控制文件”。废话不多,直接上内容:

data_repeat_msg|已经汇总过数据,请勿重复汇总。
# 下面指定文件存放路径
file_path|c://  
# 数据文件的分隔符。
separater|,
# 包裹数据项的符号
enclosedSymbol|,
# data_ok_msg|数据汇总完毕。
insert_table_failure_prompt|导入失败

# 数据库连接信息
db_sid|ORCL
db_user|SCOTT
db_password|tiger

读取配置文件

  有配置文件了,需要有读取配置文件的脚本,如下:

# **  此脚本用来读取data_dic.txt字典文件,并将相应信息保存到label全局数组里
# **  创建日期:20100816
# **  创建人员:付骋
# **  修改日期:
# **  修改人员:
global label
set fid [open "$absoluteCurrentPath//data_dic.txt" r]
while {[eof  $fid] != 1} {
	set curLine [gets $fid]
	# 遇到空行或者以#开头的行,忽略
	if {[regexp {(^(\s)*#)|(^(\s)*$)} $curLine]} {
		continue
	}
	set curLineList [split $curLine "|"]
	# 数组索引
	set index [lindex $curLineList 0]
	# 对应值
	set value [lindex $curLineList 1]
	set label($index) $value
}
close $fid

  注意,这个脚本不能直接使用。需要导入到其他文件才能用。具体用法见下面。

导入脚本

  上面所有的铺垫工作已经做完。现在该主角登场了。导入脚本如下:

#!/bin/bash
# 040_load_data.tcl \
exec tclsh "$0" ${1+"$@"}
#encoding convertto "UTF-8"

# **  此脚本用来读取data_dic.txt字典文件,并将相应信息保存到label全局数组里
# **  创建日期:20100816
# **  创建人员:D瓜哥,http://www.diguage.com/
# **  修改日期:
# **  修改人员:

encoding system identity

package require Oratcl

# 获得当前路径
set currentPath [file dirname [info script]]
cd $currentPath
#获得当前绝对路径
set absoluteCurrentPath [pwd]

# 导入字典文件读取源文件
source "$absoluteCurrentPath//read_data_dic.tcl"

################################################################
#procedure query_tableFieldtype
#查询要插入数据表的表结构类型说明
#    sInsertTableName 需要插入的表名称
#    columnNamesList  列顺序
#    dateFormat       数据文件中的日期格式。默认是YYYYMMDDHH24MISS。请根据实际情况修改。
################################################################
proc query_tableFieldtype {sInsertTableName columnNamesList fhandleSelect {dateFormat "YYYYMMDDHH24MISS"} } {  
	global label
	
	set listFieldLength [llength $columnNamesList]
	set strSql "select column_name, decode(data_type, 'VARCHAR2', 'CHAR NULLIF (' || column_name || '=BLANKS) \"trim(:' || column_name || ')\"', 'FLOAT', 'DECIMAL EXTERNAL NULLIF(' || column_name || '=BLANKS)', 'NUMBER', decode(data_precision, 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)', decode(data_scale, 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)', 'DECIMAL EXTERNAL NULLIF (' || column_name || '=BLANKS)')), 'DATE', 'DATE \"[string trim $dateFormat]\" NULLIF (' || column_name || '=BLANKS)', null) data_type"
	append strSql " from USER_TAB_COLUMNS where table_name =upper('"
	append strSql $sInsertTableName "') and column_name in ("
	append strSql upper('[join $columnNamesList '),upper(']'))
	
	puts "数据类型:$strSql"
	
	if [catch {orasql $fhandleSelect $strSql} errmsg] {
		error $errmsg
	} elseif {$errmsg != 0 && $errmsg != 100 && $errmsg != 1403} {
		error $errmsg
	}
	
	set listFieldAndType [list]
	while {[oramsg $fhandleSelect] == 0} {
		orafetch $fhandleSelect  -datavariable rowResult -command {
			set sFieldName [lindex $rowResult 0]
			lappend listFieldAndType $sFieldName
			set sFieldType [lindex $rowResult 1]
			lappend listFieldAndType $sFieldType
		}
	}
	
	#获取表字段类型时,因查询不能保证字段先后顺序,加入如下判断
	set listFieldLength [llength $columnNamesList]
	set listFieldType [list]
	for {set i 0} {$i<$listFieldLength} {incr i} {
	   set fieldName [lindex $columnNamesList $i]
	   set fieldName [string trim $fieldName ']
	   for {set j 0} {$j<$listFieldLength} {incr j} {
		  set dbFieldName [lindex $listFieldAndType [expr $j*2]]
		  if {[string toupper $fieldName]==[string toupper $dbFieldName]} {
			lappend listFieldType [lindex $listFieldAndType [expr $j*2+1]]
			break
		  }
	   }
	}
	return $listFieldType  
}


#################################################################
#procedure creat_sqlLoaderCtl
#拼装sqlLoader控制文件
#    tableName    表名
#    columnName   列名
#    columnType   列数据类型
#    dataFileName 源数据文件名
#    loadType     导入类型
#    ctlFileName  需要生成临时的控制文件名
#    separater    数据文件中,各个数据项的分隔符
#    isAppend     数据的添加方式:如果传true,则是添加;如果传false,则会将表中原来的数据删除再添加。
#   (注意:删除时使用truncate,不可恢复。)
#################################################################
proc creat_sqlLoaderCtl {tableName columnName columnType dataFileName ctlFileName separater enclosedSymbol {isAppend true} } {
	set ctlFile [open $ctlFileName w]
	set fieldLength [llength $columnName]
	puts $ctlFile "LOAD DATA"
	puts $ctlFile "INFILE \"$dataFileName\""
	if {$isAppend} {
		puts $ctlFile "APPEND"
	} else {
		puts $ctlFile "TRUNCATE"
	}
	puts $ctlFile "INTO TABLE $tableName"
	puts $ctlFile "FIELDS TERMINATED BY \"[string trim $separater]\""  ;#文件的分隔符
	puts $ctlFile "OPTIONALLY ENCLOSED BY '[string trim $enclosedSymbol]'"  ;#文件的分隔符
	#puts $ctlFile "TRAILING NULLCOLS"
	puts $ctlFile (
	for {set i 0} {$i<$fieldLength } {incr i} {
	   if {$i==[expr $fieldLength-1]} { 
		   puts $ctlFile "[string toupper [lindex $columnName $i]] [lindex $columnType $i] "
	   } else { 
		   puts $ctlFile "[string toupper [lindex $columnName $i]] [lindex $columnType $i],"
	   }
	}
	puts $ctlFile )
	close $ctlFile
	return $ctlFileName
}

proc getDealFileInfo {sInsertTableName columnNamesList isAppend  periodType indiDate fileNamesList} {
	
	global label
	global env
	global argv
#	global sDataBaseUser
	
	global fhandleSelect
#	global fhandleInsert
	
	if [catch {set dbhandle [db_connect $label(db_sid) $label(db_user) $label(db_password)]} logon_errmsg] {
		  error logon_errmsg
	}
	
	if [catch {set fhandleSelect [db_open $dbhandle] } open_errmsg]   {
		  error $open_errmsg
	}
	
	global sSysDate
	global sWorkFilePath
	
	set listField [list]
	set sFilePath [string trim "$label(file_path)"]
	set sWorkFilePath [string trim "$label(file_path)"]
	set sFieldString ""
	#set sStartTime [get_currentDate $fhandleSelect]
	set llRecNum 0
	
	set sqldrLog $env(AGENTAPPLOG)/[lindex $argv 1]\_[lindex $argv 3]_
	
	set listFieldType [query_tableFieldtype $sInsertTableName $columnNamesList $fhandleSelect]
	
	#=====根据文件路径加文件名称判断改目录下是否存在改文件=====    
	foreach sFileName $fileNamesList {
		set sPathAndFileName ""
		append sPathAndFileName $sFilePath
		append sPathAndFileName [string trim $sFileName]
		
		puts $sPathAndFileName
		
		if { ! [file exists $sPathAndFileName] } {
			error "文件不存在,请检查文件:$sPathAndFileName"
		}
		#=====生成临时SQLLADER控件文件=====
		set ctlFileName [string trim $sWorkFilePath]sqlloader/sql_loader_[string trim $sInsertTableName]_[string trim $indiDate]_[string trim $periodType].ctl
		puts "控制文件:$ctlFileName"
		
		set ctlFileName [creat_sqlLoaderCtl $sInsertTableName $columnNamesList $listFieldType $sPathAndFileName $ctlFileName $label(separater) $label(enclosedSymbol) $isAppend]
		
		#拼接SQL Loader日志文件名称
		append sqldrLog [clock format [clock seconds] -format "%Y%m%d%H%M%S"]
		append sqldrLog ".sqldr.log"
		
		#执行SQLLOADER,导入数据到表
		set loadHasError false
		set errorInfo false
		if {[catch {exec sqlldr $label(db_user)/$label(db_password)@$label(db_sid) control=$ctlFileName  log=$sqldrLog errors=0 parallel=true} selectError]} {
			set loadHasError true
			set errorInfo $selectError
			puts $selectError
		}
		if {$loadHasError} {
			error "$label(insert_table_failure_prompt)$errorInfo"
		}
		
		puts "OK,check data"
		#=====文件处理完成,存放备份目录=====
		#从/data目录备份到/backup/data/YYYY-MM-DD/文件需要移走
		set sFileDescPath $indiDate
#		set sDescFileName [string trim $sFileDescPath][string trim $sFileName]
		puts "[string trim $sFileDescPath][string trim $sFileName]"
#		file copy -force $sPathAndFileName $sDescFileName
#		file delete $sPathAndFileName
		#=====记录文件处理的日志=====
	}
}

proc run {} {
	global label 

	#参数
	global argv
	
	# 使用的时候,根据实际情况修改这里
	set tableName "CMN_COL_CFG"
	set columnList [split "COL_CFG_ID,COL_NAME,CREATE_DATE" ","]
	set isAppend  true
	set periodType [lindex $argv 7]
	set date [lindex $argv 5]
	set dataFiles "td.csv"     ;#多个文件,请使用逗号(,)分割
	set dataFileList [split $dataFiles ","]
	
	
	getDealFileInfo $tableName $columnList $isAppend $periodType $date $dataFileList
}

# 调用
run

  由于,SyntaxHighlighter不能提供TCL语言的高亮显示。所以,这里的高亮可能还有一些问题。抱歉!

  D瓜哥最近接触了一些Ruby语言,Ruby真是一个灵活的语言,同样也很好玩。非常适合自定义DSL,以后有时间D瓜哥把这个脚本再用Ruby重写一遍。

  到这里,这个系列基本结束。可能还有一些问题。例如:

  1. 使用先制作成数据文件,然后在使用SQL Loader导入到数据库中这种方式,需要监控数据文件的生成完成时间,然后再使用进行导入。但是,制作和导入,前者在数据库中使用存储过程完成;后者使用TCL(当然也可以使用其他语言完成)。横跨两个环境,监控起来不方便;
  2. 使用这种方式,需要在数据库服务器上安装TCL运行环境;

  综上,D瓜哥认为使用Shell脚本应该可以完整完成这个全部过程。不过,D瓜哥目前还不会写Shell,这个就留给感兴趣的朋友自己练手吧。当然,如果以后D瓜哥使用Shell实现了,还会发布出来的。

 

参考资料

  1. sqlldr的介绍
  2. SQL*Loader 详解
  3. 使用SQL Loader导入大量数据,避免使用SQL频繁写库
  4. Oracle官方文档:SQL*Loader

 

修改日志

  2013年3月28日  补充问题列表。



作 者: D瓜哥,http://www.diguage.com/
原文链接:http://www.diguage.com/archives/109.html
版权声明:非特殊声明均为本站原创作品,转载时请注明作者和原文链接。



如果感觉这篇文章不错,请点击这里的分享按钮,分享到微博等地方去,让更多人受益!
您的支持是D瓜哥最大的写作动力!谢谢!

  1. 树林
    2014年3月31日23:03 | #1

    哈哈瓜哥被你QQ空间引过来了,我这还有最新一版sqlloader要不要

  1. 本文目前尚无任何 trackbacks 和 pingbacks.

无觅相关文章插件,快速提升流量