外部表探究

外部表探究

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后，可能在加载时出错（未能明确行记录结束符）。