外部表定义#

创建可读外部表或创建可读外部WEB表是在OushuDB中创建一个新的可读外部表定义。可读外部表通常用于快速并行数据加载。一旦定义了外部表，就可以使用SQL命令直接（并行）查询其数据。例如，可以对外部表数据进行查询、关联或排序。也可以为外部表创建视图。不允许对可读外部表执行DML操作（UPDATE、INSERT、DELETE或TRUNCATE）。

创建可写外部表或创建可写外部WEB表是在OushuDB中创建一个新的可写外部表定义。可写外部表通常用于将数据从数据库卸载到一组文件或命名管道中。可写外部web表也可用于将数据输出到可执行程序。一旦定义了可写外部表，就可以从数据库表中选择数据并将其插入可写外部表中。不允许对可写外部表执行SELECT，DML仅支持INSERT，不支持TRUNCATE、DELETE、UPDATE。

在 OushuDB 中,Web外部表可通过两种方式访问动态数据源：可以在Web服务器上通过 http://url 访问文件表，也可以通过执行操作系统命令或脚本访问数据表。

定义外部表时，需使用 location 子句指定外部数据的位置,使用 format 子句指定外部表文件格式。

LOCATION 子句指定外部数据的位置。location 字符串以协议字符串开头，该字符串指定用于访问数据的存储类型和协议。你可以使用以下协议访问外部表数据源，但不能在一个CREATEA EXTERNAL TABLE命令中混合使用多个协议。

file:// 协议指定操作系统文件位置的URI中。URI包括主机名、端口和文件路径。
gpfdist:// 协议指定由一个或多个OushuDB文件服务器gpfdist实例提供服务的数据文件。
http:// 协议指定一个或多个 http url，并与web表一起使用。
hdfs:// 协议指定由 hadoop hdfs 文件系统提供服务的数据文件。

备注

file:// 协议的外部表是只读表, 不推荐使用。相反，请使用gpfdist://、gpfdists://、hdfs://或COPY命令。

FORMAT子句指定如何格式化外部数据。对于gpfdist或hdfs协议外部表，有效的平面文件格式是分隔文本(TEXT)格式和逗号分隔值(CSV)格式，如果文件中的数据不使用默认列分隔符、转义符、换行符、null字符等，则必须在FORMAT子句选项中指定文件的分隔符（DELIMITER）、转义符（ESCAPE）、换行符（NEWLINE）、null字符(NULL)选项，以便OushuDB正确读取外部文件中的数据；对于hdfs协议外部表，6.0版本新增支持创建ORC和Parquet格式可读、可写外部表。

备注

OushuDB目前只有hdfs协议支持ORC和Parquet格式。

以下内容是外部表定义的 SQL 命令，包含创建、修改和删除外部表：

外部表创建权限#

创建外部表前需要确保用户有相应协议外表创建权限。superuser或管理员默认具有所有种类协议（读/写）外表创建的权限。对于普通用户，需要管理员或superuser授予或取消其对（hdfs/gpfdist/http）协议（可读/可写）外部表创建权限。

以hdfs协议外表为例：

授予用户（可读/可写）外表创建权限

alter role exthdfsrole1 createexttable(type='readable',protocol='hdfs');
alter role exthdfsrole1 createexttable(type='writable',protocol='hdfs');

取消用户的（可读/可写）外表外表创建权限

alter role exthdfsrole1 nocreateexttable(type='readable',protocol='hdfs');
alter role exthdfsrole1 nocreateexttable(type='writable',protocol='hdfs');

创建外部表#

创建一个外部表，使用 CREATE EXTERNAL TABLE命令。在这个命令里，需声明新表名称，各列名称及其数据类型，基于命令的EXECUTE子句或基于URL的LOCATION子句的外部数据来源，数据格式。

当创建一个可读外部表时，location 子句需指定使用一种协议指向外部数据源，format 子句需指定TEXT、CSV文本格式。比如：使用gpfdist协议在gpfdist目录中找到（.txt）格式的所有文本文件，来创建一个名为ext_customer的可读外部表。这些文件的格式是以‘|’作为列分隔符，ascii的space字符表示null。还可以在单行错误隔离模式下访问外部表的SQL定义：

CREATE READABLE EXTERNAL TABLE ext_customer
   (id int, name text, sponsor text)
   LOCATION ( 'gpfdist://filehost:8081/*.txt' )
   FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
   LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5;

或者创建与上面相同的可读外部表定义，但使用CSV格式文件的示例：

CREATE READABLE EXTERNAL TABLE ext_customer
   (id int, name text, sponsor text)
   LOCATION ( 'gpfdist://filehost:8081/*.csv' )
   FORMAT 'CSV' ( DELIMITER ',' );

当创建一个可读web外部表时，除location子句方式指定外部数据方式外，还可以使用execute子句运行指定脚本（脚本必须位于所有segment主机上的同一位置）来访问外部数据源。比如：在第五个虚拟节点执行指定脚本创建一个外部表的SQL定义：

CREATE READABLE EXTERNAL WEB TABLE log_output (
    linenum int,
    message text
)
EXECUTE '/var/load_scripts/get_log_data.sh' ON 5
FORMAT 'TEXT' (DELIMITER '|');

当创建一个可写外部表时，location子句使用上述协议之一指向外部数据源，format子句指定TEXT、CSV、ORC或CUSTOM用户自定义文本格式。例如：以下SQL命令显示如何创建可写hdfs外部表以自由加载、卸载、查询和插入数据：

CREATE WRITABLE EXTERNAL TABLE tbexternal (
  a int,
  b decimal(10,2),
  t text,
  d date)
LOCATION ('hdfs://host1:port1/tbexternaldir')
FORMAT 'csv' (DELIMITER '|');

此表支持写入，用户可以通过将数据写入同一路径’/tbexternaldir’来卸载或将数据插入此表。操作此外部表的一些示例如下所示：

INSERT INTO tbexternal SELECT * FROM tb;
INSERT INTO tbexternal SELECT * FROM tb WHERE t like 'test%';
INSERT INTO tb SELECT * FROM tbexternal;
INSERT INTO tb SELECT * FROM tbexternal WHERE t like 'test%';

可以通过创建一个相同location的可读外部表查询该可写外部表的数据：

CREATE READABLE EXTERNAL TABLE tbexternal_r (
  a int,
  b decimal(10,2),
  t text,
  d date)
LOCATION ('hdfs://host1:port1/tbexternaldir')
FORMAT 'csv' (DELIMITER '|')
LOG ERRORS INTO err_tbexternal SEGMENT REJECT LIMIT 5;

SELECT a,b FROM tbexternal_r WHERE b > 10;
SELECT T1.a, T1.d FROM tbexternal_r T1, tbinternal T2 WHERE T1.t = T2.t;

对于创建的hdfs协议的ORC或Parquet格式可读外部表，不支持错误表且不需要指定任何选项。以下命令显示如何创建hdfs协议的ORC格式可读外部表：

CREATE READABLE EXTERNAL TABLE orcexternal (
      p text,
      q text
)
LOCATION ('hdfs://host1:port1/orcexternaldir')
FORMAT 'ORC';

创建一个可写的ORC或Parquet外表，使用 OPTIONS 子句指定可选项，如：压缩类型。

CREATE WRITABLE EXTERNAL TABLE orcexternal (
      p text,
      q text
)
LOCATION ('hdfs://host1:port1/orcexternaldir')
FORMAT 'ORC' OPTIONS ("orc.write.format" '0.11', "orc.compress" 'lz4');

CREATE WRITABLE EXTERNAL TABLE orcexternal (
      p text,
      q text
)
LOCATION ('hdfs://host1:port1/orcexternaldir')
FORMAT 'Parquet' OPTIONS ("parquet.compression" 'lz4');

当创建一个可写Web外部表时，使用location子句指定外部数据源或execute子句指定执行脚本，使用format子句指定TEXT、CSV文本格式。例如：创建一个可写的外部web表，该表将segment接收到的输出数据管道化到名为_adreport_etl.sh的可执行脚本：

CREATE  EXTERNAL WEB TABLE campaign_out (LIKE campaign)
EXECUTE '/var/unload_scripts/to_adreport_etl.sh'
FORMAT 'TEXT' (DELIMITER '|');

修改外部表#

修改外部表，使用ALTER TABLE命令。

ALTER TABLE orcexternal RENAME TO orc_ext;

ALTER TABLE orc_ext SET SCHEMA new_schema;

修改外部表结构时，必须使用ALTER EXTERNAL TABLE命令。

增加一个列，使用下面命令：

ALTER EXTERNAL TABLE orc_ext ADD COLUMN col int;

删除一个列，使用下面命令：

ALTER EXTERNAL TABLE orc_ext DROP COLUMN col;

修改列数据类型，使用下面命令：

ALTER EXTERNAL TABLE log_output alter column message TYPE varchar(200);

外部表不支持约束修改、默认值修改。

删除外部表#

删除外部表时，使用DROP EXTERNAL TABLE命令。

DROP EXTERNAL TABLE orc_ext;

使用外部表#

使用外部表的步骤如下：

定义外部表,配置数据库并启动创建外部表时指定的协议。
启动location子句中指定协议对应的文件服务器，且对协议和数据库进行相关配置
将数据文件放置在location子句中对应的位置
使用SQL命令查询使用外部表

OushuDB 产品文档

外部表定义

本页目录

外部表定义#

外部表创建权限#

创建外部表#

修改外部表#

删除外部表#

使用外部表#