外部表定义

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

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

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

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

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

  • gpfdist://protocol指定由一个或多个OushuDB文件服务器gpfdist实例提供服务的数据文件。
  • http://protocol指定一个或多个http url,并与web表一起使用。
  • hdfs://protocol指定由hadoop hdfs文件系统提供服务的数据文件。
  • hive://protocol指定由Hive数据库提供服务的数据文件。

Note

不推荐使用file://。相反,请使用gpfdist://、gpfdists://、hive://、hdfs://或COPY命令。

FORMAT子句指定如何格式化外部数据,对于gpfdist协议,有效的平面文件格式(包括HDFS中的文件)是分隔文本(TEXT)格式和逗号分隔值(CSV)格式。 版本3.0增加了对HDFS协议中orc格式的支持。如果文件中的数据不使用默认列分隔符、转义符、空字符串等,则必须指定其他格式选项,以便OushuDB正确读取外部文件中的数据。

Note

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

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

创建外部表

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

当创建一个可读外部表时,location子句需指定使用一种协议指向外部数据源,format子句需指定TEXT、CSV、ORC或CUSTOM用户自定义文本格式。比如:使用gpfdist协议在gpfdist目录中找到(.txt)格式的所有文本文件,来创建一个名为ext_customer的可读外部表。这些文件的格式是以‘|’作为列分隔符,空白空间为空。还可以在单行错误隔离模式下访问外部表的SQL定义:

CREATE 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 EXTERNAL TABLE ext_customer
   (id int, name text, sponsor text)
   LOCATION ( 'gpfdist://filehost:8081/*.csv' )
   FORMAT 'CSV' ( DELIMITER ',' );

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

CREATE 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 '|')
LOG ERRORS INTO err_tbexternal SEGMENT REJECT LIMIT 5;

此表支持读取和写入,路径’/tbexternaldir’中的所有文件都可以读取以进行查询或加载,用户还可以通过将数据写入同一路径’/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%';

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

以下命令显示如何以orc格式创建可写hdfs外部表:

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

以下命令显示如何以orc格式创建可写hive外部表:

CREATE WRITABLE EXTERNAL TABLE orcexternal (
  p text,
  q text
)
LOCATION ('hive://host1:port1/dbname/orctablename’)
FORMAT 'ORC' (COMPRESSTYPE 'lz4');

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

CREATE WRITABLE 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命令查询使用外部表