外部表定义
==========

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

创建可写外部表或创建可写外部WEB表是在OushuDB中创建一个新的可写外部表定义。可写外部表通常用于将数据从数据库卸载到一组文件或命名管道中。可写外部web表也可用于将数据输出到可执行程序。一旦定义了可写外部表，就可以从数据库表中选择数据并将其插入可写外部表中。目前只有hdfs协议和hive协议支持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 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  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  EXTERNAL TABLE orcexternal (
        p text,
        q text
  )
  LOCATION ('hdfs://host1:port1/orcexternaldir')
  FORMAT 'ORC' (COMPRESSTYPE 'lz4');

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

::

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