版权声明:转载时请标注http://blog.csdn.net/mr_errol
外部表,相对于数据库内部表来说的,意思是将操作系统文件作为一个数据库,数据从文件而来。
我把它理解成类似指针的东西,告诉用户,这个表的数据是从哪些文件来的。
外部表的创建,这里写的比网上其他介绍复杂许多,简单的用法,自行百度,这里只考虑 多文件
大数据、每行数据没有分隔符,字段靠截取、外部白导入类型是oracle loader的条件。网上的
一些文章只做了一些简单的介绍和用法,只能满足简单的需求,而真是需要使用到外部表的一半都
是上百万级别的大数据。先看看基本语法,里面的参数将会一一详解。
语法如下:
create table "table1"
(
id varchar2(4),
name varchar2(20)
)ORGANIZATION external --外部表的关键字
(
type oracle_loader --外部表有两种类型,这里使用oracle_loader
DEFAULT DIRECTORY DATA_FILE_PATH --文件目录
ACCESS PARAMETERS( --参数
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
SKIP 10
BADFILE 'DATA_LOG_PATH':'table1.bad'
NODISCARDFILE
LOGFILE 'DATA_LOG_PATH':'table1.log'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"id" (1,4) char(4) nullif ("id" = blanks),
"name" (5,24) char(20) nullif ("name" = blanks)
)
)
location
(
test1.txt,
test2.txt
)REJECT LIMIT UNLIMITED;
使用外部表需要注意一下几点:
1、数据文件(test1.txt,test2.txt)的存放路径必须是数据库服务器能够访问到的地方!
这点非常关键,许多产品一半是数据库服务器跟应用后台分离的方式,所以,这就要求文件能
够放在数据库服务器上,而不是后台服务器,或者两者之间加一块共享存储,这里两台都能访
问。
2、创建外部表之前,需要先创建目录对象DATA_FILE_PATH,也就是,数据文件需要放在这
个目录对象中,也就是,这个目录对象的路径,是数据库服务器上的路径。创建语法如下:
CREATE OR REPLACE DIRECTORY IMPORT_DATA_FILE_PATH AS '/home/file'
由于上面的例子我把生成的log、bad文件单独存放,所以建立两个目录。
3、执行创建外表语句 ,可以在oracle客户端执行,且创建表不加载数据,也就是数据如果有错,
或者文件不存在,不会报错,创建是成功的,只有当insert到正式表时,才是加载数据的过程。
4、外部表只能查询,不能做DML操作,外部表只能查询,不能做DML操作,外部表只能查询,
不能做DML操作,重要的事情说3遍。外部表只能当作中间工具,建立好外部表时,使用
insert into 正式表 select * from 外部表;
加载数据到正式表中。这个过程可以使用查询并行和DML并行,提升加载性能(这里有坑,后面
解释)。
现在来讲讲外部表的一些参数
1、TYPE 外部表的类型,有两种,一种是oralce_loader,一种是ORACLE_DATAPUMP,后者
没用过,也讲不来,自行百度。oralce_loader类型,就可以把外部表当作多个sqlldr,如果没有
了解过sqlldr,可以先了解一下,比较外部表的本质还是这个。具体有多少个sqlldr呢,差不多
location里面有多少个文件,就有多少个sqlldr,所以,使用外部表导入大数据,性能会比一个
sqlldr好得多。
2、DEFAULT DIRECTORY 就是指定目录对象,文件一定要在这里面!!!
3、RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK 指定数据库的字符集,
数据库字符集可以查询出来。
select userenv(‘language’) from dual;
4、BADFILE 指定bad文件存在路径,什么是bad文件,就是数据里面,违反了字段规则的数据
,就会被当中bad数据放到里面。如果不需要生成,则写NOBADFILE.
5、DISCARDFILE 丢弃的文件,跟bad文件不一样,这个是违反了筛选条件的数据,上面的例
子没写,这里写下:
SKIP 10
BADFILE 'DATA_LOG_PATH':'table1.bad'
NODISCARDFILE
LOGFILE 'DATA_LOG_PATH':'table1.log'
LOAD WHEN ("id" != blanks)
6、LOAD WHEN 加载条件,结合上面说,就是筛选条件,这里面的写法只能指定字段的值,不
能限制类型,而且语法限制条件也很多,sql的函数不能用,只能使用and or = != 这些。上面
DISCARDFILE ,就是如果不满足这个load when,则这行数据将会被丢弃,放到你指定的文件中
去,如果不需要生成,则NODISCARDFILE。
回过来说这个load when ,写法也很多,可以指定字段,也可以指定长度,比如:
load when (1:20) != blanks,就是说,从1到20个字符不能是空白。这个load when官方文档也
说的很少,具体的用法,需要自己慢慢摸索。
7、SKIP N 跳过,顾名思义,加载数据时候告诉Oracle跳过多少行,这里挺坑,一个数据文件,
跳过没问题,如果有多个数据文件,它只能跳过第一个文件的n行,这不坑爹吗?还有,上面说
insert到正式表的可以使用并行,注意!!!!!SKIP,只能在非并行模式下使用!!!!!!!
如果需要使用并行DML或者查询,麻烦把这个参数去掉!!!!!
8、READSIZE 1048576 这个是指定一次性读取最大值。
9、FIELDS LDRTRIM 加载的时候,每个字段都要trim一下
10、REJECT ROWS WITH ALL NULL FIELDS 就是说如果字段没有数据,则为null
11、字段规则,很简单,如果是有分隔符的写法,网上很多,这里模拟的数据是没有分隔符的,
类似于这样:
132334342354656546646454564564564564535242
“id” (1,4) char(4) nullif (“id” = blanks) 分别是字段名,截取起始位置,类型和长度,条件是
如果为空格则字段为Null。这里是字符串类型的,如果是number类型的字段,条件这么写:
“age” (21,23) INTEGER EXTERNAL(3)
12、location 里面就是需要加载的文件名字
13、REJECT LIMIT UNLIMITED 默认是有限制的加载,这里设置的是无限制加载。
外部表的参数太多了,有一些没用过,也不知道怎么用,有兴趣的,可以去oracle官方文档里面
慢慢看,全英文的。
http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#SUTIL012
外部表的效率,还是非常可观的,500W数据,使用sqlldr导入的话,需要大概8-10分钟加载完,
如果使用外部,开了并行,1分钟搞定。