mysql external table_创建Oracle外部表 External Table

最新推荐文章于 2022-08-03 14:21:14 发布

fc01

最新推荐文章于 2022-08-03 14:21:14 发布

阅读量306

点赞数

文章标签： mysql external table

本文链接：https://blog.csdn.net/weixin_42524762/article/details/113427657

版权

对于处理数据库海量数据的存储，也可以采用以下的方法处理；

主要是针对CSV等格式的外部文件。

1. csv的结构，每个字段以什么为分割

2. external table的创建

3. 如何实现外部表访问外部文件，并显示在数据库里面

例如有一个外部文件名字为temptes.csv

文件内容如下：

sunny,85,85,FALSE,no

sunny,80,90,TRUE,no

overcast,83,86,FALSE,yes

rainy,70,96,FALSE,yes

rainy,68,80,FALSE,yes

rainy,65,70,TRUE,no

overcast,64,65,TRUE,yes

sunny,72,95,FALSE,no

sunny,69,70,FALSE,yes

rainy,75,80,FALSE,yes

sunny,75,70,TRUE,yes

overcast,72,90,TRUE,yes

overcast,81,75,FALSE,yes

rainy,71,91,TRUE,no

要导入到数据库里。

首先创建一个创建一个Directory：必须用sys用户创建 create directory dirtemp as 'F:\EXTERNALDIR'

;

然后授权 grant read,write on directory dirtemp to users; //用户名

把文件temptes.csv放到'F:\EXTERNALDIR'文件里。

使用被授权的用户users创建外部表，表名和你的文件名一定要一致。

Create table temptes

(WTMARKET varchar2(20),

fenli1 varchar2(50),

fenli2 varchar2(20),

errro varchar2(20),

flag varchar2(20)

)

organization external

(

type oracle_loader

default directory dirtemp

access parameters ( fields terminated by ',' )

location (temptes.CSV')

)

reject limit unlimited;

表创建完成;

可以查看数据 select * from temptes;

如果不知道怎么写external table的创建语法，可以利用利用sqlldr生成external_table表的语法；

例如在F盘有个SQLLDR文件的DETAIL.ctl文件：

load data

infile 'DETAIL.csv'

into table global_region_bns_detail

(SBH char terminated by '|',

DBH char terminated by '|',

Y_GJ char terminated by '|',

D_XJ char terminated by '|',

GRXFBV char terminated by '|',

TERMLYBV char terminated by '|',

GYBV_300I char terminated by '|',

GYBV_300O char terminated by '|',

GYBV_ZC char terminated by '|',

ZC char terminated by '|',

ZC_CS char terminated by '|',

ZJ char terminated by '|',

JJ char terminated by '|',

LDDOWN char terminated by '|',

PW char terminated by '|',

LD char terminated by '|',

LD_CS char terminated by '|',

DS char terminated by '|',

BNS_CS char terminated by '|',

BNS_ID char terminated by '|'

)；

利用命令：CMD>sqlldr scott/123@user control=DETAIL.ctl external_table=generate_only;

生成了DETAIL.log文件，打开如下：

SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 4月 10 12:45:27 2013

控制文件: DETAIL.ctl

数据文件: DETAIL.csv

错误文件: DETAIL.bad

废弃文件: 未作指定

(可废弃所有记录)

要加载的数: ALL

要跳过的数: 0

允许的错误: 50

继续: 未作指定

所用路径: 外部表

表 GLOBAL_REGION_BNS_DETAIL,已加载从每个逻辑记录

插入选项对此表 INSERT 生效

列名位置长度中止包装数据类型

------------------------------ ---------- ----- ---- ---- ---------------------

SBH FIRST * | CHARACTER

DBH NEXT * | CHARACTER

Y_GJ NEXT * | CHARACTER

D_XJ NEXT * | CHARACTER

GRXFBV NEXT * | CHARACTER

TERMLYBV NEXT * | CHARACTER

GYBV_300I NEXT * | CHARACTER

GYBV_300O NEXT * | CHARACTER

GYBV_ZC NEXT * | CHARACTER

ZC NEXT * | CHARACTER

ZC_CS NEXT * | CHARACTER

ZJ NEXT * | CHARACTER

JJ NEXT * | CHARACTER

LDDOWN NEXT * | CHARACTER

PW NEXT * | CHARACTER

LD NEXT * | CHARACTER

LD_CS NEXT * | CHARACTER

DS NEXT * | CHARACTER

BNS_CS NEXT * | CHARACTER

BNS_ID NEXT * | CHARACTER

文件需要 CREATE DIRECTORY 语句

------------------------------------------------------------------------

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'F:\'

用于外部表的 CREATE TABLE 语句:

------------------------------------------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_GLOBAL_REGION"

(

"SBH" VARCHAR2(30),

"DBH" VARCHAR2(30),

"Y_GJ" VARCHAR2(30),

"D_XJ" VARCHAR2(30),

"GRXFBV" VARCHAR2(30),

"TERMLYBV" VARCHAR2(30),

"GYBV_300I" VARCHAR2(30),

"GYBV_300O" VARCHAR2(30),

"GYBV_ZC" VARCHAR2(30),

"ZC" VARCHAR2(30),

"ZC_CS" VARCHAR2(30),

"ZJ" VARCHAR2(30),

"JJ" VARCHAR2(30),

"LDDOWN" VARCHAR2(30),

"PW" VARCHAR2(30),

"LD" VARCHAR2(30),

"LD_CS" VARCHAR2(30),

"DS" VARCHAR2(30),

"BNS_CS" VARCHAR2(30),

"BNS_ID" VARCHAR2(30)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'DETAIL.bad'

LOGFILE 'DETAIL.log_xt'

READSIZE 1048576

FIELDS LDRTRIM

REJECT ROWS WITH ALL NULL FIELDS

(

"SBH" CHAR(255)

TERMINATED BY "|",

"DBH" CHAR(255)

TERMINATED BY "|",

"Y_GJ" CHAR(255)

TERMINATED BY "|",

"D_XJ" CHAR(255)

TERMINATED BY "|",

"GRXFBV" CHAR(255)

TERMINATED BY "|",

"TERMLYBV" CHAR(255)

TERMINATED BY "|",

"GYBV_300I" CHAR(255)

TERMINATED BY "|",

"GYBV_300O" CHAR(255)

TERMINATED BY "|",

"GYBV_ZC" CHAR(255)

TERMINATED BY "|",

"ZC" CHAR(255)

TERMINATED BY "|",

"ZC_CS" CHAR(255)

TERMINATED BY "|",

"ZJ" CHAR(255)

TERMINATED BY "|",

"JJ" CHAR(255)

TERMINATED BY "|",

"LDDOWN" CHAR(255)

TERMINATED BY "|",

"PW" CHAR(255)

TERMINATED BY "|",

"LD" CHAR(255)

TERMINATED BY "|",

"LD_CS" CHAR(255)

TERMINATED BY "|",

"DS" CHAR(255)

TERMINATED BY "|",

"BNS_CS" CHAR(255)

TERMINATED BY "|",

"BNS_ID" CHAR(255)

TERMINATED BY "|"

)

location

(

'DETAIL.csv'

)

)REJECT LIMIT UNLIMITED

用于加载内部表的 INSERT 语句:

------------------------------------------------------------------------

INSERT /*+ append */ INTO GLOBAL_REGION_BNS_DETAIL

(

SBH,

DBH,

Y_GJ,

D_XJ,

GRXFBV,

TERMLYBV,

GYBV_300I,

GYBV_300O,

GYBV_ZC,

ZC,

ZC_CS,

ZJ,

JJ,

LDDOWN,

PW,

LD,

LD_CS,

DS,

BNS_CS,

BNS_ID

)

SELECT

"SBH",

"DBH",

"Y_GJ",

"D_XJ",

"GRXFBV",

"TERMLYBV",

"GYBV_300I",

"GYBV_300O",

"GYBV_ZC",

"ZC",

"ZC_CS",

"ZJ",

"JJ",

"LDDOWN",

"PW",

"LD",

"LD_CS",

"DS",

"BNS_CS",

"BNS_ID"

FROM "SYS_SQLLDR_X_EXT_GLOBAL_REGION"

用于清除由以前的语句创建的对象的语句:

------------------------------------------------------------------------

DROP TABLE "SYS_SQLLDR_X_EXT_GLOBAL_REGION"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

从星期三 4月 10 12:45:27 2013 开始运行

在星期三 4月 10 12:45:28 2013 处运行结束

经过时间为: 00: 00: 00.13

CPU 时间为: 00: 00: 00.08；

这里面就有你要的语法，同时还创建了CREATE DIRECTORY 语句。

可以说SQLLDR是external_table的一个补充。

fc01

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql external table_创建Oracle外部表 External Table

对于处理数据库海量数据的存储，也可以采用以下的方法处理；主要是针对CSV等格式的外部文件。1. csv的结构，每个字段以什么为分割2. external table的创建3. 如何实现外部表访问外部文件，并显示在数据库里面例如有一个外部文件名字为temptes.csv文件内容如下：sunny,85,85,FALSE,nosunny,80,90,TRUE,noovercast,83,86,FALS...
复制链接

扫一扫