=================15 The ORACLE_LOADER Access Driver======================
1 RECORDS 关键字后定义如何识别数据行
常用 RECORDS DELIMITED BY NEWLINE 定义换行,并指明字符集 CHARACTERSET UTF8。对于特殊字符需要单独定义, 如SKIP X——跳过X行数据,有些文件第一行是列名,需要跳过第一行,则使用SKIP 1。
2 FIELDS 关键字后定义如何识别字段,常用如下:
FIELDS TERMINATED BY ','——字段分割符,按‘,’分割;
ENCLOSED BY 'x'——字段引用符,包含在此符号内的数据都当成一个字段;例如一行数据格式为:"abc","a""b,""c,"。使用参数
TERMINATED BY ',' ENCLOSED BY '"'后,系统会读到两个字段,第一个字段是[abc],第二个字段值是[a"b,"b,]。
FIELDS TERMINATED BY "," LDRTRIM——描述字段的终止符
LRTRIM——删除首尾空白字符
MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL
3 外部表对错误的处理
BADFILE和NOBADFILE子句
用于指定将捕捉到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误。
如果未指定该参数,则系统自动在源目录下生成与外部表同名的.BAD文件。
BADFILE记录本次操作的结果,下次将会被覆盖。
LOGFILE和NOLOGFILE子句
在access parameters中加入LOGFILE 'LOG_FILE.log'子句,则所有Oracle的错误信息放入'LOG_FILE.log'中;
而NOLOGFILE子句则表示不记录错误信息到log中,如忽略该子句,系统自动在源目录下生成与外部表同名的.LOG文件。
REJECT LIMIT UNLIMITED子句
在创建外部表时最后加入LIMIT子句,表示可以允许错误的发生个数。默认值为0。设定为UNLIMITED则错误不受限制。
*************************!!!注意!!!********************************
--外部表经常遇到BUFFER不足的情况,因此尽可能的增大READSIZE;
--换行符不对产生的问题。在不同操作系统中换行符的表示方法不一样,碰到错误日志提示若是换行符问题,可以使用UltraEdit打开,直接看十六进制;
--特定行报错时,查看带有'BAD'的日志文件,其中保存了出错的数据,用记事本打开看看哪里出错,是否存在外部表定义相冲突
*************************外部表的特性********************************************************
--位于文件系统之中,按一定格式分割,如文本文件或其它类型的表都可作为外部表;
--对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载到数据库中;
--外部数据表都是只读的,因此在外部表中国不能够执行DML操作,也不能创建索引;
--可以查询操作和连接,也可以并行操作;
--数据在数据库的外部组织,是操作系统文件;
--操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
****************************创建外部表的注意事项*************************************************
1 需要先创建目录对象并授权
在创建目录对象时注意它的路径是否正确,在Linux操作系统中,路径是区分大小写的
SQL>CREATE OR REPLACE DIRECTORY dump_dir AS '/oradata/exterltab';
给用户授予指定目录的操作权限:
SQL>grant read,write on directory dump_dir to etl;
2 对操作系统文件的要求
建立外部表时,必须指定操作系统文件所使用的分隔符号。并且该分隔符有且只有一个。
创建外部表时,不能含有标题列。如果这个标题信息与外部表的字段类型不一致,在查询时就会出错;如果数据类型恰巧一致,这个标题信息Oracle数据库也会当作普通记录来对待;
当Oracle数据库系统访问这个操作系统文件时,会在这个文件所在的目录自动创建一个日志文件,无论最后是否访问成功,这个日志文件都会如期创建。
查看这个日志文件,可以了解数据库访问外部表的频率、是否访问成功等等。默认情况下该日志与外部表在同一目录下产生。
3 在建立临时表时的相关限制
在创建外部表时,并没有在数据库中创建表,也不会为外部表分配任何存储空间。
创建外部表只是在数据字典中创建了外部表的元数据,以便对应访问外部表中的数据,而不在数据库中存储外部表的数据。
简单来说,数据库存储的只是与外部文件的一种对应关系,如字段与字段的对应关系,而没有存储实际的数据。
4 删除外部表或目录对象
创建时先创建目录对象,再创建外部表;删除则是先删除外部表,再删除目录对象,如果目录对象有多个表,应删除所有表之后再删除目录对象;
如果在未删除外部表的情况下,强制删除了目录对象,在查询到被删除的外部表时,将收到"对象不存在"的错误信息;
=========================外部表的优势=================================
1 外部表可以使用复杂的where条件有选择的加载数据;
2 能够合并(MERGE)数据,可以取一个填满数据的操作系统文件,并由它更新现有的数据库记录;
3 能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分;
======================================================================
type oracle_loader --说明外部文件访问方式:oracle_loader或oracle_datapump(9i不支持)
type oracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select生成,故不支持oracle_loader
default directory dat_dir --指定外部表的存放目录
--——————----系统根据这些描述信息来生成外部表的格式------——————--
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --记录默认以换行符结束
SKIP 0 //跳过0行数据
fields terminated by ',' //按‘,’分割
badfile 'deptxt.bad' //存放处理失败的记录文件描述
logfile 'deptxt.log' //日志文件
READSIZE 1024 //Oracle读取输入数据文件所用的默认缓冲区,此处为MB
FIELDS TERMINATED BY "," LDRTRIM // 按‘,’结尾的字段
missing field values are null //空缺字段值为null
REJECT ROWS WITH ALL NULL FIELDS //--所有为空值的行被跳过并且记录到bad file.
RECORDS FIXED 20 FIELDS //记录固定的20个字段
RECORDS VARIABLE 2 FIELDS TERMINATED BY ','
RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
-------------------------------描述外部文件(文本文件)各个列的定义----------------------------------------
(
clumn_name1 CHAR(255) TERMINATED BY ",",
clumn_name2 CHAR(255) TERMINATED BY ",",
clumn_name3 CHAR(255) TERMINATED BY ","
)
location('tb1.exp','tb2.exp') --产生外部表的内容将填充到这些文件中
parallel // --按并行方式来填充
REJECT LIMIT UNLIMITED //允许发生错误的个数不受限制