OushuDB 创建和管理外部表（中）

创建外部表

创建一个外部表，使用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 5FORMAT '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 '|');