外部表探究

外部表探究

1. 引入

达梦数据库中提及的表可以分为两类,分别为数据库内部表(基表、水平分区表、Huge表)和外部表。
数据库内部表由数据库管理系统自行组织管理,而外部表在数据库的外部组织,即从操作系统文件装载而来
建立外部表时不会产生段、簇、数据页等存储结构,只是把与表相关的定义放在数据字典而已。


2. 使用限制

  • <表名>指定了所要建立的外部基表名。如果<模式名>缺省,则缺省为当前模式。表名需要是合法的标识符,且满足 SQL 语法要求;

  • 外部表的表名最大长度为128个字符;

  • 所建外部基表至少要包含一个<列名>指定的列,在一个外部基表中,各<列名>不得相同。一张外部基- 表中至多可以包含 2048 列;

  • 外部基表不能存在大字段列

  • 外部基表不能存在任何约束条件

  • 外部基表不能为临时表,不能建立分区

  • 外部基表上不能建立任何索引

  • 外部基表是只读的,不存在表锁,不允许任何针对外部表的增删改数据操作,不允许TRUNCATE外部表操作

  • 外部表支持查询 ROWID、USER 和 UID 伪列,不支持查询 TRXID 伪列


3. 语法格式

TIPS:

  1. 控制文件路径,以及数据文件路径建议采用绝对路径。
  2. 控制文件中没使用<参数选项>的 RECORDS 指定行分隔符,则在数据文件中的一行数据必须以回车结束。
  3. 控制文件中OPTIONS选项参数目前OPTIONS中支持DATA、LOG、ERRORS、BADFILE、NULL_STR、SKIP、CHARACTER_CODE
    选项,以逗号(,)分隔,含义同dmfldr快速装载。

外部表探究

外部表探究
外部表探究
FROM子句四种句式:

句式1: 控制文件
FROM '控制文件路径';

例如:Linux操作系统字符集en_US.UTF-8,数据文件路径/dmdata/data/EXT/data.txt,列分隔符竖线,控制文件data.ctl
# data.txt 数据文件
1|张三|男
2|王五|女
3|赵五|男
4|孙李|男


# data.ctl 控制文件
OPTIONS (
 CHARACTER_CODE='UTF-8'
 LOG='/dmdata/data/EXT/data.log'
)
LOAD DATA
INFILE '/dmdata/data/EXT/data.txt'
BADFILE '/dmdata/data/EXT/data.bad'
INTO TABLE FSEXT1
FIELDS '|'


# 创建外部表
CREATE EXTERNAL TABLE FSEXT1 (id int, name varchar(20), gender char(2))
FROM '/dmdata/data/EXT/data.ctl';

# 验证外部表查询数据内容
SELECT * FROM FSEXT1;

外部表探究

句式2: 数据文件单独指定,参数列表作为控制文件另一种表现形式(FROM子句的SQL短语)。  
FROM DATAFILE '数据文件路径' [参数列表];

例如:Linux操作系统字符集en_US.UTF-8,数据文件路径/dmdata/data/EXT/data.txt,列分隔符竖线,无控制文件
# data.txt 内容
1|张三|男
2|王五|女
3|赵五|男
4|孙李|男

# 建外部表,带控制参数列表
CREATE EXTERNAL TABLE FSEXT2 (id int, name varchar(20), gender char(2))
FROM DATAFILE '/dmdata/data/EXT/data.txt'
PARMS (
  FIELDS DELIMITED BY '|',
  RECORDS DELIMITED BY 0X0A,
  BADFILE '/dmdata/data/EXT/data.bad',
  LOG '/dmdata/data/EXT/data.log',
  CHARACTER_CODE 'UTF-8' 
);

外部表探究

句式3: 利用数据库对象目录指定控制文件的目录所在位置
FROM DEFAULT DIRECTORY 目录对象名 LOCATION ('控制文件名称');

例如:Linux操作系统字符集en_US.UTF-8,数据文件路径/dmdata/data/EXT/data.txt,列分隔符竖线,无控制文件
# data.txt 内容
1|张三|男
2|王五|女
3|赵五|男
4|孙李|男

# data2.ctl 切记:如果使用目录对象,则不要在控制文件中写绝对路径,否则找不到莫名错误“外部数据错误”。
OPTIONS (
 CHARACTER_CODE='UTF-8'
 LOG='data2.log'
)
LOAD DATA
INFILE 'data.txt'
BADFILE 'data2.bad'
INTO TABLE FSEXT3
FIELDS '|'


# 建目录对象
CREATE OR REPLACE DIRECTORY EXT_DIR AS '/dmdata/data/EXT';
GRANT READ ON DIRECTORY EXT_DIR TO DMDBA;

# 建外部表
CREATE EXTERNAL TABLE FSEXT3 (id int, name varchar(20), gender char(2))
FROM DEFAULT DIRECTORY EXT_DIR LOCATION ('data2.ctl');
  

外部表探究

句式4: 通过目录对象指定数据文件所在位置,手工指定控制参数
FROM DATAFILE DEFAULT DIRECTORY 目录名 LOCATION ('数据文件名称') [参数列表];    

例如:Linux操作系统字符集en_US.UTF-8,数据文件路径/dmdata/data/EXT/data.txt,列分隔符竖线,无控制文件
# data.txt 内容
1|张三|男
2|王五|女
3|赵五|男
4|孙李|男

# 创建外部表
drop table FSEXT3;
CREATE EXTERNAL TABLE FSEXT3 (id int, name varchar(20), gender char(2))
FROM  DATAFILE DEFAULT DIRECTORY EXT_DIR LOCATION ('data.txt')
PARMS (
 FIELDS DELIMITED BY '|',
 CHARACTER_CODE 'UTF-8'
);

外部表探究


4. 实战用例

导出城市表成文本文件city.txt,再用外部表加载查询
SET LINESHOW OFF FEEDBACK OFF HEADING OFF NULL_SHOW ON ECHO OFF
SET LINESIZE 800 PAGESIZE 0 TIMING OFF TRIMSPOOL OFF
SPOOL city.txt REP
SELECT CITY_ID||','||CITY_NAME||','||REGION_ID FROM "DMHR"."CITY";
SPOOL OFF

# city.txt
BJ ,北京,1
SJZ,石家庄,1
SH ,上海,2
NJ ,南京,2
GZ ,广州,3
HK ,海口,3
WH ,武汉,4
CS ,长沙,4
SY ,沈阳,5
XA ,西安,6
CD ,成都,7
CQ ,aaa,7

# 雷区:查看文本文件编码
[dmdba@dmdb EXT]$ file city.txt
city.txt: UTF-8 Unicode text

# 创建外部表
drop table "DMHR"."FSCITY";
CREATE EXTERNAL TABLE "DMHR"."FSCITY" (ID CHAR(3), NAME VARCHAR(20), RID TINYINT)
FROM DATAFILE '/dmdata/data/EXT/city.txt'
PARMS (
   CHARACTER_CODE 'UTF-8',
   FIELDS DELIMITED BY ',',
   RECORDS DELIMITED BY 0x0a,
   ERRORS 5,
   NULL_STR 'aaa',
   BADFILE '/dmdata/data/EXT/city.log',
   LOG '/dmdata/data/EXT/city.bad'        
);


select * from "DMHR"."FSCITY";

外部表探究


5. 学习总结

  1. 外部表的数据存于数据库外部,支持纯文本文件。
  2. 外部表由于不属于数据库内部表,无法做增删改操作,也无法建索引,仅支持查询。
  3. 加载数据文件建议看清楚字符编码,指定准确的字符集。
  4. 因外部表数据来自于数据库外部,对它的修改操作非常方便。
  5. 特别注意操作系统之间的差异,在不知情的情况下,从windows上编辑拷贝到Linux后,可能在加载时出错(未能明确行记录结束符)。
上一篇:ckeditor粘贴word文档图片的解决方案


下一篇:SublimeText粘贴图片保存到本地