概要

CREATE [READABLE] EXTERNAL TABLE table_name     
    ( column_name data_type [, ...] | LIKE other_table )
     LOCATION ('file://seghost[:port]/path/file' [, ...])
       | ('gpfdist://filehost[:port]/file_pattern[#transform=trans_name]'
           [, ...]
       | ('gpfdists://filehost[:port]/file_pattern[#transform=trans_name]'
           [, ...])
       | ('gphdfs://hdfs_host[:port]/path/file')
       | ('s3://S3_endpoint[:port]/bucket_name/[S3_prefix]
             [region=S3-region]
            [config=config_file]')
     [ON MASTER]
     FORMAT 'TEXT' 
           [( [HEADER]
              [DELIMITER [AS] 'delimiter' | 'OFF']
              [NULL [AS] 'null string']
              [ESCAPE [AS] 'escape' | 'OFF']
              [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
              [FILL MISSING FIELDS] )]
          | 'CSV'
           [( [HEADER]
              [QUOTE [AS] 'quote'] 
              [DELIMITER [AS] 'delimiter']
              [NULL [AS] 'null string']
              [FORCE NOT NULL column [, ...]]
              [ESCAPE [AS] 'escape']
              [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
              [FILL MISSING FIELDS] )]
          | 'AVRO' 
          | 'PARQUET'
          | 'CUSTOM' (Formatter=<formatter_specifications>)
    [ ENCODING 'encoding' ]
      [ [LOG ERRORS] SEGMENT REJECT LIMIT count
      [ROWS | PERCENT] ]

CREATE [READABLE] EXTERNAL WEB TABLE table_name     
   ( column_name data_type [, ...] | LIKE other_table )
      LOCATION ('http://webhost[:port]/path/file' [, ...])
    | EXECUTE 'command' [ON ALL 
                          | MASTER
                          | number_of_segments
                          | HOST ['segment_hostname'] 
                          | SEGMENT segment_id ]
      FORMAT 'TEXT' 
            [( [HEADER]
               [DELIMITER [AS] 'delimiter' | 'OFF']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CSV'
            [( [HEADER]
               [QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE NOT NULL column [, ...]]
               [ESCAPE [AS] 'escape']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CUSTOM' (Formatter=<formatter specifications>)
     [ ENCODING 'encoding' ]
     [ [LOG ERRORS] SEGMENT REJECT LIMIT count
       [ROWS | PERCENT] ]

CREATE WRITABLE EXTERNAL TABLE table_name
    ( column_name data_type [, ...] | LIKE other_table )
     LOCATION('gpfdist://outputhost[:port]/filename[#transform=trans_name]'
          [, ...])
      | ('gpfdists://outputhost[:port]/file_pattern[#transform=trans_name]'
          [, ...])
      | ('gphdfs://hdfs_host[:port]/path')
      FORMAT 'TEXT' 
               [( [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF'] )]
          | 'CSV'
               [([QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE QUOTE column [, ...]] ]
               [ESCAPE [AS] 'escape'] )]
           | 'AVRO' 
           | 'PARQUET'

           | 'CUSTOM' (Formatter=<formatter specifications>)
    [ ENCODING 'write_encoding' ]
    [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]

CREATE WRITABLE EXTERNAL TABLE table_name
    ( column_name data_type [, ...] | LIKE other_table )
     LOCATION('s3://S3_endpoint[:port]/bucket_name/[S3_prefix]
            [region=S3-region]
            [config=config_file]')
      [ON MASTER]
      FORMAT 'TEXT' 
               [( [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF'] )]
          | 'CSV'
               [([QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE QUOTE column [, ...]] ]
               [ESCAPE [AS] 'escape'] )]

CREATE WRITABLE EXTERNAL WEB TABLE table_name
    ( column_name data_type [, ...] | LIKE other_table )
    EXECUTE 'command' [ON ALL]
    FORMAT 'TEXT' 
               [( [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF'] )]
          | 'CSV'
               [([QUOTE [AS] 'quote'] 
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE QUOTE column [, ...]] ]
               [ESCAPE [AS] 'escape'] )]
           | 'CUSTOM' (Formatter=<formatter specifications>)
    [ ENCODING 'write_encoding' ]
    [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]

描述

有关外部表的详细信息请参阅Greenplum数据库管理员指南中的装载和卸载数据。

CREATE EXTERNAL TABLE 或CREATE EXTERNAL WEB TABLE 在Greenplum数据库中创建一个新的可读外部表定义。可读外部表通常用于快速并行数据加载。 定义外部表后，可以使用SQL命令直接（并行）查询其数据。 例如, 用户可以选择，加入或排序外部表数据。用户还可以创建外部表的视图。 DML操作 (更新, 插入, 删除, 或 TRUNCATE) 在可读外部表上不可操作，用户不能在可读外部表上创建索引。

CREATE WRITABLE EXTERNAL TABLE 或 CREATE WRITABLE EXTERNAL WEB TABLE 在Greenplum数据库中创建一个新的可写外部表定义。 可写外部表通常用于将数据从数据库卸载到一组文件或命名管道中。 可写外部Web表也可用于将数据输出到可执行程序。可写外部表格也可以用作Greenplum并行MapReduce计算的输出目标。 一旦写入外部表被定义，可以从数据库表中选择数据并将其插入到可写外部表中。 可写外部表仅允许 插入 操作 – 选择, 更新, 删除 或 TRUNCATE 不被允许。

常规外部表和外部Web表之间的主要区别是它们的数据源。常规可读外部表访问静态平面文件，而外部Web表访问动态数据源 - 无论是在Web服务器上还是通过执行OS命令或脚本。

该 FORMAT 子句用于描述外部表格文件的格式。 有效的文件格式是分隔文本 (TEXT) 逗号分隔值(CSV) 格式, 与PostgreSQL可用的格式化选项类似 COPY 命令。 如果文件中的数据不使用默认列分隔符，转义字符，空字符串等，则必须指定其他格式选项，以便外部文件中的数据被Greenplum数据库正确读取。 有关使用自定义格式的信息，请参阅Greenplum数据库管理员指南中的装载和卸载数据。

为了gphdfs 协议, 用户可以指定 AVRO 或 PARQUET 在 FORMAT 子句读取或写入Avro或Parquet格式文件。 有关Avro和Parquet文件的信息， 参阅gphdfs协议的HDFS文件格式支持。

在创建写入或从Amazon Web Services（AWS）S3存储区中读取的外部表之前，必须配置Greenplum数据库以支持协议。S3外部表可以使用CSV或文本格式的文件 可写的S3外部表仅支持插入 操作. 请参见 s3协议配置。

参数

READABLE | WRITABLE

指定外部表的类型，默认可读。可读外部表用于将数据加载到Greenplum数据库中。可写外部表用于卸载数据。

WEB

在Greenplum数据库中创建可读或可写的外部Web表定义。 有两种形式的可读外部Web表格 - 那些访问文件的形式 http:// 协议或通过执行OS命令访问数据的协议。 可写外部Web表将数据输出到可接受数据输入流的可执行程序。在执行查询期间，外部Web表不能重新计算。

该 s3协议不支持外部Web表。但是，用户可以创建一个外部Web表，执行第三方工具直接从S3读取数据或向S3写入数据。

table_name

新外部表的名称。

column_name

在外部表定义中创建的列的名称。与常规表不同，外部表不具有列约束或默认值，因此不要指定。

LIKE other_table

该LIKE 子句指定新的外部表自动复制所有列名，数据类型和Greenplum分发策略的表。 如果原始表指定了任何列约束或默认列值，那么它们将不会被复制到新的外部表定义中。

data_type

列的数据类型。

LOCATION ('protocol://host[:port]/path/file' [, ...])

对于可读外部表，指定用于填充外部表或Web表的外部数据源的URI。常规可读外部表允许 gpfdist 或 文件 协议。 外部Web表允许 http 协议。 如果端口被省略, 端口 8080 被假定为 http和 gpfdist 协议, 端口 9000为 gphdfs 协议。 如果使用 gpfdist 协议, the 路径 是相对于gpfdist服务文件的目录 （启动gpfdistgpfdist 程序时指定的目录)。此外， gpfdist 使用通配符或其他C-style模式匹配(例如， 空格符为[[:space:]])表示 目录中的多个文件。例如：

'gpfdist://filehost:8081/*'
'gpfdist://masterhost/my_load_file'
'file://seghost1/dbfast1/external/myfile.txt'
'http://intranet.example.com/finance/expenses.csv'

对于 gphdfs 协议, URI的 LOCATION 不能包含以下四个字符： \, ', <, >. 如果URI包含任何此类字符，该CREATE EXTERNAL TABLE 命令将返回错误。

如果用户正在使用MapR集群gphdfs 协议,用户需要指定一个特定的集群和文件：

• 要指定默认群集，MapR配置文件中的第一个目录 /opt/mapr/conf/mapr-clusters.conf, 使用以下语法指定表的位置：

   LOCATION ('gphdfs:///file_path')

  该 file_path 是文件路径。
• 要指定配置文件中列出的另一个MapR集群，请使用以下语法指定文件：

   LOCATION ('gphdfs:///mapr/cluster_name/file_path')

  cluster_name 是在配置文件中指定的集群的名称并且 file_path是文件的路径

对于可写外部表,指定 gpfdist 进程或S3协议将会从Greenplum的Segment收集输出的数据并将其写入一个或多个命名文件。 对于gpfdist 该路径 是相对于 gpfdist服务文件的目录 (启动 gpfdist 程序时指定的目录)。如果多个 gpfdist 列 出了位置, 发送数据的Segment将在可用的输出位置间均匀分配 例如:

'gpfdist://outputhost:8081/data1.out',
'gpfdist://outputhost:8081/data2.out'

有两个 gpfdist 如上述示例中列出的位置，一半的Segment将其输出数据发送到 data1.out 文件，并且 另一半发送到 data2.out 文件中。

在可选择的 #transform=trans_name中, 用户可以 指定要在加载或提取数据时应用的转换。 trans_name 是用户运行 gpfdist 实用程序指定的YAML配置文件的转换名称。有关 指定转换的信息，请参见Greenplum实用指南中的gpfdist。

如果用户指定gphdfs作为将文件读取或写入Hadoop文件系统（HDFS）的协议，用户可以指定 FORMAT 子句通过用AVRO 或 PARQUET。

有关指定Avro或Parquet文件位置的信息， 参阅 gphdfs协议的HDFS文件格式支持。

当用户用 s3 协议创建一个外部表时,只支持 TEXT 和CSV格式。 这些文件可以是gzip压缩格式。 该 s3 协议 该S3协议识别gzip格式并解压缩文件。只支持gzip压缩格式。

用户可以指定 s3 协议 访问Amazon S3上的数据或Amazon S3兼容服务上的数据。 在用户用 s3 协议创建外部表之前, 用户必须配置Greenplum数据库。有关配置Greenplum数据库的信息， 参阅 s3协议配置。

对于 s3协议来说, LOCATION 子句指定数据文件为表上传的S3端点和存储桶名称。对于可写外部表，用户可以为插入到表中的数据创建新文件时指定一个可选的S3文件前缀。

如果为只读S3表指定了S3_prefix ，则 s3选择那些具有指定的S3文件前缀的文件。

注解: 尽管 S3_prefix 是语法的可选部分，但应始终为可写和只读S3表包含一个S3前缀，以分隔数据集作为 CREATE EXTERNAL TABLE 的语法。

The LOCATION 子句中的config参数 指定包含Amazon Web Services (AWS)连接凭据和通信参数所需的s3 协议配置参数文件的位置。 有关s3 配置文件参数的信息， 参见 s3协议配置文件。

这是一个使用 s3协议定义的只读外部表的示例。 .

CREATE READABLE EXTERNAL TABLE S3TBL (date text, time text, amt int)
   LOCATION('s3://s3-us-west-2.amazonaws.com/s3test.example.com/dataset1/normal/
      config=/home/gpadmin/aws_s3/s3.conf')
   FORMAT 'csv';

S3桶位于S3端点s3-us-west-2.amazonaws.com， s3的桶名称为s3test.example.com。 桶中文件的s3前缀为 /dataset1/normal/。配置文件位于所有Greenplum数据库的Segment的 /home/gpadmin/s3/s3.conf 中。

这个只读外部表示例指定相同的位置，并使用 region参数指定Amazon s3区域us-west-2 。

CREATE READABLE EXTERNAL TABLE S3TBL (date text, time text, amt int)
   LOCATION('s3://amazonaws.com/s3test.example.com/dataset1/normal/
      region=s3-us-west-2 config=/home/gpadmin/aws_s3/s3.conf')
   FORMAT 'csv';

该端口在LOCATION 子句中的URL中是可选的。如果在LOCATION的子句的URL中未指定 端口 则 < clause, the s3配置文件参数 加密会影响s3协议使用的端口 (port 80 for HTTP 或port 443 for HTTPS)。如果指定端口，则使用该端口，而不管加密设置如何。例如，如果在 s3配置文件中的LOCATION子句中指定了端口80，并且 encryption=true，则HTTPS请求将发送到端口80端口而不是443，并记录警告。

LOCATION子句中的config参数指定包含Amazon Web Services（AWS）连接凭据和通信参数的所需s3协议配置文件的位置。 将s3的配置文件参数 版本设置为 2 并在LOCATION子句中指定 region 参数。 (如果版本是2， 则LOCATION子句中需要region 参数。)在LOCATION子句中定义服务时，可以在 URL中指定服务端点，并在 region参数中指定服务器。这是一个LOCATION 子句示例， 包含了一个region 参数并指定了一个Amazon S3兼容服务。

LOCATION ('s3://test.company.com/s3test.company/dataset1/normal/ region=local-test
      config=/home/gpadmin/aws_s3/s3.conf')

当 版本参数是2时， 还可以指定一个Amazon S3端点。 此示例指定了Amazon S3端点。

LOCATION ('s3://s3-us-west-2.amazonaws.com/s3test.example.com/dataset1/normal/ region=us-west-2
      config=/home/gpadmin/aws_s3/s3.conf')

更多关于s3协议的信息，参阅Greenplum数据库管理员指南中的s3://协议。

ON MASTER

将所有与表相关的操作限制在Greenplum主Segment。 仅允许使用 s3或自定义协议创建的可读写的外部表。gpfdist, gpfdists, gphdfs,和文件协议 不支持 ON MASTER。

注意： 在使用 ON MASTER子句创建的外部表进行阅读或写入时，请注意潜在的资源影响。将表操作仅限于Greenplum主分区时，可能会遇到性能问题。

EXECUTE 'command' [ON ...]

允许只读可读外部Web表或可写外部表。 对于可读取的外部Web表，要指定由Segment实例执行的OS命令。 该 命令可以是单个OS命令或脚本。 ON子句用于指定哪些Segment实例将执行给定的命令。

• ON ALL是默认值。 该命令将由Greenplum数据库系统中所有Segment主机上的每个活动（主）Segment实例执行。如果命令执行一个脚本，该脚本必须位于所有Segment主机上的相同位置，并且可由Greenplum超级用户 (gpadmin)执行。
• ON MASTER仅在主机主机上运行命令。
  注意： 当指定ON MASTER子句时，外部web表不支持日志记录 。
• ON number表示命令将由指定数量的Segment执行。Greenplum数据库系统在运行时随机选择特定的Segment。如果命令执行脚本，该脚本必须位于所有Segment主机上的相同位置，并且可由Greenplum超级用户(gpadmin)执行。
• HOST意味着命令将由每个Segment主机上的一个Segment执行（每个Segment主机一次），而不管每个主机的活动Segment实例数如何。
• HOST segment_hostname表示该命令将由指定Segment主机上的所有活动（主）Segment实例执行。
• SEGMENT segment_id表示命令只能由指定的Segment执行一次。 用户可以通过查看系统目录表 gp_segment_configuration中的内容编号来确定Segment实例的ID。 Greenplum数据库的Master的 内容 ID始终为 -1。

对于可写外部表， EXECUTE子句中指定的 命令必须准备好将数据管道传输到其中。由于具有发送数据的所有Segment都将其输出写入指定的命令或程序， 因此 ON的唯一可选项为ON ALL。

FORMAT 'TEXT | CSV | AVRO | PARQUET' (options)

指定外部或Web表格数据的格式 - 纯文本 (TEXT)或或逗号分隔值(CSV) 格式。

仅使用 gphdfs协议支持 AVRO 和 PARQUET格式。

有关指定AVRO 和 PARQUET文件格式时的选项的信息，参阅gphdfs协议的HDFS文件格式支持。

FORMAT 'CUSTOM' (formatter=formatter_specification)

指定自定义数据格式。 formatter_specification 指定用于格式化数据的函数，后跟格式化函数的逗号分隔参数。 格式化程序规范的长度，包括Formatter=的字符串的长度可以高达约50K字节。

有关使用自定义格式的信息，请参阅 Greenplum数据库管理员指南中的“装载和卸载数据”。

DELIMITER

指定单个ASCII字符，用于分隔每行（行）数据中的列。 默认值为TEXT模式下的制表符， CSV格式为逗号。在可读外部表的TEXT模式下，对于将非结构化数据加载到单列表中的特殊用例，可以将分隔符设置为OFF

对于s3协议，分隔符不能是换行符 (\n)或回车字符(\r)。

NULL

指定表示NULL值的字符串。在 TEXT 模式下，默认值是 \N（反斜杠-N）， CSV模式中不含引号的空值。在 TEXT模式下用户可能更希望不想将NULL值与空字符串区分开的情况下，也能使用 NULL 字符串。使用外部和Web表时，与此字符串匹配的任何数据项将被视为NULL值。使用外部和Web表格时，与此字符串匹配的任何数据项将被视为NULL值。

作为text 格式的示例，此FORMAT 子句可用于指定两个单引号 ('') 的字符串为NULL 值。

FORMAT 'text' (delimiter ',' null '\'\'\'\'' )

ESCAPE

指定用于C转义序列的单个字符 (例如 \n,\t,\100, 等等) 以及用于转义可能被视为行或列分隔符的数据字符。 确保选择在实际列数据中的任何地方都不使用的转义字符。 默认转义字符是文本格式文件的\（反斜杠）和csv格式文件的 " (双引号) ，但是可以指定其他字符来表示转义，也可以禁用文本转义 通过指定值'OFF'作为转义值， 格式化的文件对于诸如文本格式的Web日志数据之类的数据非常有用，这些数据具有许多不希望转义的嵌入式反斜杠。

NEWLINE

指定数据文件中使用的换行符– LF (换行符, 0x0A)， CR (回车符号, 0x0D), 或 CRLF (回车加换行， 0x0D 0x0A). 如果未指定，Greenplum数据库的Segment将通过查看其接收的第一行数据并使用遇到的第一个换行符来检测换行类型。

HEADER

对于可读外部表，指定数据文件中的第一行是标题行（包含表列的名称），不应作为表的数据包含。 如果使用多个数据源文件，则所有文件必须有标题行。

对于 s3协议，标题行中的列名不能包含换行符 (\n) 或回车符 (\r)。

QUOTE

指定 CSV模式的报价字符。 默认值为双引号 (")。

FORCE NOT NULL

在CSV 模式下，处理每个指定的列，就像它被引用一样，因此不是一个 NULL 值。对于 CSV模式中的默认空字符串（两个分隔符之间不存在），这将导致将缺少的值作为零长度字符串计算。

FORCE QUOTE

在可写外部表的CSV 模式下，强制引用用于每个指定列中的所有非NULL 值。 NULL 输出从不引用。

FILL MISSING FIELDS

在可读外部表的 TEXT和CSV模式下， 指定 FILL MISSING FIELDS时，当一行数据在行或行的末尾缺少数据字段时，将丢失尾字段值设置为 NULL （而不是报告错误）。空行，具有NOT NULL约束的字段和行上的尾随分隔符仍然会报告错误。

ENCODING 'encoding'

字符集编码用于外部表。 指定一个字符串常量 (如 'SQL_ASCII'), 一个整数编码号或者 DEFAULT 来使用默认的客户端编码。 参见字符集支持。

LOG ERRORS

这是一个可选的子句，可以在SEGMENT REJECT LIMIT 子句之前记录有关具有格式错误的行的信息。 错误日志信息在内部存储，并使用Greenplum数据库内置SQL函数 gp_read_error_log()访问。

参见注解 有关错误日志信息和内置函数的信息，以查看和管理错误日志信息。

SEGMENT REJECT LIMIT count [ROWS | PERCENT]

在单行错误隔离模式下运行COPY FROM操作。如果输入行具有格式错误，则它们将被丢弃，前提是在加载操作期间在任何Greenplum的Segment实例上未达到拒绝限制计数。 The 拒绝限制计数可以指定为行数（默认值）或总行数百分比（1-100）。如果使用PERCENT，则每个Segment只有在处理了参数 gp_reject_percent_threshold所指定的行数之后才开始计算坏行百分比。 gp_reject_percent_threshold的默认值为300行。诸如违反 NOT NULL, CHECK， 或 UNIQUE约束的约束错误仍将以“all-or-nothing”输入模式进行处理。 如果没有达到限制，所有好的行将被加载，任何错误行被丢弃。

注解： 读取外部表时，如果未首先触发 SEGMENT REJECT LIMIT 或未指定SEGMENT REJECT LIMIT，则Greenplum数据库将限制可能包含格式错误的初始行数。 如果前1000行被拒绝，则 COPY操作将被停止并回滚。

可以使用Greenplum数据库服务器配置参数 gp_initial_bad_row_limit更改初始拒绝行的数量限制。 有关参数的信息，请参阅服务器配置参数 。

DISTRIBUTED BY (column, [ ... ] )

DISTRIBUTED RANDOMLY

用于为可写外部表格声明Greenplum数据库分发策略。默认情况下，可写外部表是随机分布的。如果要从中导出数据的源表具有散列分发策略，则为可写外部表定义相同的分发密钥列可以通过消除在互连上移动行的需要来改善卸载性能。 当用户发出诸如 INSERT INTO wex_table SELECT * FROM source_table，的卸载命令时，如果两个表具有相同的散列分布策略，则可以将卸载的行直接从Segment发送到输出位置。

示例

在端口8081 的后台启动 gpfdist文件服务器程序，从目录 /var/data/staging提供文件：

gpfdist -p 8081 -d /var/data/staging -l /home/gpadmin/log &

创建一个名为 ext_customer的可读外部表使用的 gpfdist 协议和gpfdist 目录中找到的任何文本格式的文件（*.txt） 文件格式化为管道 (|)作为列分隔符，空格为NULL。 也可以在单行错误隔离模式下访问外部表：

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

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

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

使用文件协议和具有标题行的几个CSV格式的 文件创建名为 ext_expenses的可读外部表：

CREATE EXTERNAL TABLE ext_expenses (name text, date date, 
amount float4, category text, description text) 
LOCATION ( 
'file://seghost1/dbfast/external/expenses1.csv',
'file://seghost1/dbfast/external/expenses2.csv',
'file://seghost2/dbfast/external/expenses3.csv',
'file://seghost2/dbfast/external/expenses4.csv',
'file://seghost3/dbfast/external/expenses5.csv',
'file://seghost3/dbfast/external/expenses6.csv' 
)
FORMAT 'CSV' ( HEADER );

创建一个可读的外部Web表，每个Segment主机执行一次脚本：

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

创建一个名为sales_out 的可写外部表，它使用 gpfdist将输出数据写入名为sales.out的文件。 文件格式化为管道(|)作为列分隔符，空格空。

CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales) 
   LOCATION ('gpfdist://etl1:8081/sales.out')
   FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
   DISTRIBUTED BY (txn_id);

创建一个可写的外部Web表，其将Segment接收的输出数据管理到名为to_adreport_etl.sh的可执行脚本：

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

使用上面定义的可写外部表来卸载所选数据：

INSERT INTO campaign_out SELECT * FROM campaign WHERE 
customer_id=123;

注解

指定LOG ERRORS子句时，Greenplum数据库会捕获读取外部表数据时发生的错误。 用户可以查看和管理捕获的错误日志数据。

• 使用内置的SQL函数 gp_read_error_log('table_name')。它需要对table_name具有 SELECT特权。 此示例显示使用COPY命令加载到表 ext_expenses 中的数据的错误日志信息：

  SELECT * from gp_read_error_log('ext_expenses');

  有关错误日志格式的信息，请参阅 Greenplum数据库管理员指南中的在错误日志中查看不正确的行

  如果 table_name不存在，该函数返回FALSE。

• 如果指定的表存在错误日志数据，新的错误日志数据将附加到现有的错误日志数据。 错误日志信息不会复制到镜像Segment。
• 使用内置的SQL函数 gp_truncate_error_log('table_name') 删除 table_name的错误日志数据。 它需要表所有者权限,此示例删除将数据移动到表中时捕获的错误日志信息 ext_expenses:

  SELECT gp_truncate_error_log('ext_expenses'); 

  如果 table_name不存在，该函数返回 FALSE。

  指定*通配符以删除当前数据库中现有表的错误日志信息。指定字符串 *.*以删除所有数据库错误日志信息，包括由于以前的数据库问题而未被删除的错误日志信息。如果指定*，则需要数据库所有者权限。 如果指定了*.* 则需要操作系统超级用户权限。

HDFS文件格式支持gphdfs协议

如果用户指定了gphdfs协议将文件读取或写入Hadoop文件系统（HDFS）， 则可以通过使用 FORMAT子句指定文件格式来读取或写入Avro或Parquet格式文件。

CREATE EXTERNAL TABLE tablename (column_spec) LOCATION ( 'gphdfs://location') FORMAT 'AVRO' 

作为 位置 参数的一部分，用户可以指定读取或写入文件的选项。 在文件名后，用户可以使用HTTP查询字符串语法指定参数？并在字段值对之间使用＆。

'gphdfs://myhdfs:8081/avro/singleAvro/array2.avro?compress=true&compression_type=block&codec=snappy' FORMAT 'AVRO'

有关使用外部表读取和编写Avro和Parquet格式文件的信息，，请参阅Greenplum数据库管理员指南 中的“装载和卸载数据”。

Avro文件

对于可读外部表，唯一有效的参数是 模式。阅读多个Avro文件时，可以指定包含Avro模式的文件。 请参阅 Greenplum数据库管理员指南中的“Avro模式覆盖”。

对于可写外部表，可以指定 模式，命名空间，和参数进行压缩。

 'compress=true&codec=snappy'

这两组参数指定 deflate 压缩并且等效于

 'compress=true&codec=deflate&codec_level=1'
 'compress=true&codec_level=1'

gphdfs Avro文件的限制

对于Greenplum数据库可写外部表定义，列不能指定 NOT NULL子句。

Greenplum数据库仅支持Avro文件中的单个顶级模式，或者在 CREATE EXTERNAL TABLE命令中使用模式参数指定。 如果Greenplum数据库检测到多个顶级模式，则会返回错误。

Greenplum数据库不支持Avro 地图数据类型，并在遇到错误时返回错误。

当Greenplum数据库从Avro文件读取数组时，数组将转换为文本文本值。 例如，数组 [1,3]转换为'{1,3}'。

支持用户定义的类型（UDT），包括数组UDT。 对于可写外部表，类型将转换为字符串。

Parquet 文件

对于外部表，可以在该 位置指定的文件之后添加参数。此表列出了有效的参数和值。

s3协议配置

s3协议与URI一起使用以指定Amazon Simple Storage Service（Amazon S3）存储区中文件的位置。该协议创建或下载 LOCATION 子句指定的所有文件。每个Greenplum数据库的Segment实例一次使用多个线程下载或上传一个文件。请参阅s3:// 协议 在 Greenplum数据库管理员指南”。

配置和使用S3外部表

s3协议限制

关于S3协议URL

对于 s3 协议，用户可以在 CREATE EXTERNAL TABLE命令的 LOCATION 子句中指定文件的位置和可选的配置文件位置。语法如下：

's3://S3_endpoint[:port]/bucket_name/[S3_prefix] [region=S3_region] [config=config_file_location]'

s3 协议要求用户指定S3端点和S3桶名称。 每个Greenplum数据库Segment实例必须能够访问S3位置。 可选的S3_prefix 值用于为只读S3表选择文件，也可用作为S3可写表上传文件时使用的文件名前缀。

要在 LOCATION子句中指定ECS端点（Amazon S3兼容服务），必须将s3 配置文件参数设置为 版本 2。 版本 参数 控制LOCATION 中是否使用 region 参数。 用户还可以在 版本参数为2时指定Amazon S3位置。 有关 版本 参数的信息，参见s3协议配置文件。

对于可写的S3表，s3协议URL指定了Greenplum数据库上传表的数据文件的端点和存储桶名称。 对于上传文件的S3用户标识， S3桶权限必须为 Upload/Delete。 由于将数据插入到表中，S3文件前缀用于上传到S3位置的每个新文件。 请参阅关于S3数据文件。

对于只读S3表，S3文件前缀是可选的。 如果指定了 S3_prefix，则s3协议将以指定前缀开头的所有文件选择为外部表的数据文件。 s3协议不使用斜杠字符(/) 作为分隔符，因此前缀后面的斜杠字符将被视为前缀本身的一部分。

例如，考虑以下5个文件，每个文件都有名为 S3_endpoint 的 s3-us-west-2.amazonaws.com 和 bucket_name test1:

s3://s3-us-west-2.amazonaws.com/test1/abc
s3://s3-us-west-2.amazonaws.com/test1/abc/
s3://s3-us-west-2.amazonaws.com/test1/abc/xx
s3://s3-us-west-2.amazonaws.com/test1/abcdef
s3://s3-us-west-2.amazonaws.com/test1/abcdefff

• 如果S3 URL是作为s3://s3-us-west-2.amazonaws.com/test1/abc提供的， 那么 abc前缀会选择所有5个文件。
• 如果S3 URL被提供为s3://s3-us-west-2.amazonaws.com/test1/abc/, 则 abc/ prefix 选择文件 s3://s3-us-west-2.amazonaws.com/test1/abc/ 和 s3://s3-us-west-2.amazonaws.com/test1/abc/xx.
• 如果S3 URL以s3://s3-us-west-2.amazonaws.com/test1/abcd提供，则 abcd前缀会选择文件 s3://s3-us-west-2.amazonaws.com/test1/abcdef 和 s3://s3-us-west-2.amazonaws.com/test1/abcdefff

S3_prefix中不支持通配符；然而，S3前缀的功能就好像一个通配符紧随着前缀本身。

由S3 URL (S3_endpoint/bucket_name/S3_prefix) 选择的所有文件都用作外部表的源，因此它们必须具有相同的格式。 每个文件还必须包含完整的数据行。数据行不能在文件之间分割。对于正在访问文件的S3用户标识，S3文件权限必须是Open/Download和View

有关Amazon S3端点的信息，请参阅 http://docs.aws.amazon.com/general/latest/gr/rande.html#s3_region。 有关S3桶和文件夹的信息，请参阅Amazon S3文档https://aws.amazon.com/documentation/s3/。 有关S3文件前缀的信息，请参阅Amazon S3文档列出密钥分层使用前缀和分隔符。

config参数指定包含AWS连接凭据和通信参数的所需 s3 协议配置文件的位置。 参阅关于S3协议配置参数。

s3协议配置文件

gpseg_data_dir/gpseg-prefixN/s3/s3.conf

gpseg_data_dir 是Greenplum数据库Segment数据目录的路径， gpseg-prefix 是Segment前缀， N是Segment的ID。 在初始化Greenplum数据库系统时，会设置Segment数据目录，前缀和ID。

如果Segment主机上有多个Segment实例，则可以通过在每个Segment主机上创建单个位置来简化配置。 然后可以使用s3协议 LOCATION子句中的config参数指定位置的绝对路径。但是，请注意，只读和可写S3外部表对于它们的连接使用相同的参数值。 如果要为只读和可写S3表配置协议参数不同，则在创建每个表时，必须使用两种不同的s3协议配置文件， 并在 CREATE EXTERNAL TABLE语句中指定正确的文件。

次示例指定gpadmin主目录的 the s3目录中的单个文件位置：

config=/home/gpadmin/s3/s3.conf

主机上的所有Segment实例都使用文件 /home/gpadmin/s3/s3.conf。

[default]
secret = "secret"
accessid = "user access id"
threadnum = 3
chunksize = 67108864

用户可以使用Greenplum数据库 gpcheckcloud 实用程序来测试S3配置文件。 参阅 使用gpcheckcloud工具。

兼容性

CREATE EXTERNAL TABLE 是Greenplum数据库扩展。SQL标准没有规定外部表。

另见

CREATE TABLE AS, CREATE TABLE, COPY, SELECT INTO, INSERT