CREATE EXTERNAL TABLE

定义一个新的外部表。

概要

CREATE [READABLE] EXTERNAL [TEMPORARY | TEMP] 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]'
           [, ...])
       | ('pxf://path-to-data?PROFILE=profile_name[&SERVER=server_name][&custom-option=value[...]]'))
       | ('s3://S3_endpoint[:port]/bucket_name/[S3_prefix] [region=S3-region] [config=config_file | config_server=url]')
     [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] )]
          | 'CUSTOM' (Formatter=<formatter_specifications>)
    [ OPTIONS ( key 'value' [, ...] ) ]
    [ ENCODING 'encoding' ]
      [ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT count
      [ROWS | PERCENT] ]

CREATE [READABLE] EXTERNAL WEB [TEMPORARY | TEMP] 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>)
     [ OPTIONS ( key 'value' [, ...] ) ]
     [ ENCODING 'encoding' ]
     [ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT count
       [ROWS | PERCENT] ]

CREATE WRITABLE EXTERNAL [TEMPORARY | TEMP] 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]'
          [, ...])
      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>)
    [ OPTIONS ( key 'value' [, ...] ) ]
    [ ENCODING 'write_encoding' ]
    [ DISTRIBUTED BY ({column [opclass]}, [ ... ] ) | DISTRIBUTED RANDOMLY ]

CREATE WRITABLE EXTERNAL [TEMPORARY | TEMP] 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 | config_server=url]')
      [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 [TEMPORARY | TEMP] 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>)
    [ OPTIONS ( key 'value' [, ...] ) ]
    [ ENCODING 'write_encoding' ]
    [ DISTRIBUTED BY ({column [opclass]}, [ ... ] ) | DISTRIBUTED RANDOMLY ]

描述

CREATE EXTERNAL TABLE或CREATE EXTERNAL WEB TABLE在 YMatrix 数据库中创建一个新的可读外部表定义。可读的外部表通常用于快速并行数据加载。定义外部表后，您可以使用SQL命令直接（或并行）查询其数据。例如，您可以选择，联接或排序外部表数据。您也可以为外部表创建视图。可读外部表上不允许DML操作（UPDATE，INSERT，DELETE或TRUNCATE），并且您不能在可读外部表上创建索引。

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

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

参数

READABLE | WRITABLE

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

WEB

在 YMatrix 数据库中创建一个可读或可写的外部Web表定义。可读的外部Web表有两种形式 - 通过http://协议访问文件的表或通过执行OS命令访问数据的表。可写的外部Web表将数据输出到可执行程序，该程序可以接受输入的数据流。外部Web表在查询执行期间不可重新扫描。
s3协议不支持外部Web表。但是，您可以创建一个外部Web表，该表执行第三方工具以从S3读取数据或直接向S3写入数据。

TEMPORARY | TEMP

如果指定，则在 YMatrix 数据库中创建一个临时的可读或可写外部表定义。临时外部表存在于特殊模式中。创建表时，无法指定模式名称。在会话结束时，将自动删除临时外部表。
临时表存在时，当前会话将看不到具有相同名称的已知永久表，除非您引用具有其模式限定名称的永久表。

table_name

新外部表的名称。

column_name

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

LIKE other_table

LIKE子句指定一个表，新的外部表将从该表中自动复制所有列名称，数据类型和 YMatrix 分配策略。如果原始表指定了任何列约束或默认列值，则不会将这些约束复制到新的外部表定义中。

data_type

列的数据类型。

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

对于可读的外部表，指定用于填充外部表或Web表的外部数据源的URI。常规的可读外部表允许使用gpfdist或file协议。外部Web表允许使用http协议。如果省略port，则将http和gpfdist协议的端口假定为8080。如果使用gpfdist协议，则path是相对于gpfdist从中提供文件的目录（启动gpfdist程序时指定的目录）。另外，gpfdist可以使用通配符或其他C样式模式匹配（例如，空格字符为[[:space:]]）来表示目录中的多个文件。
对于可写的外部表，指定gpfdist进程或S3协议的URI位置，该进程将收集 YMatrix segment中的数据输出并将其写入一个或多个命名文件。对于gpfdist，该路径是相对于gpfdist从中提供文件的目录（启动gpfdist程序时指定的目录）的。如果列出了多个gpfdist位置，则发送数据的segment将在可用的输出位置之间平均分配。

ON MASTER

将所有与表相关的操作限制为 YMatrix master。仅允许使用s3或自定义协议创建的可读写外部表。 gpfdist，gpfdists，pxf和file协议不支持ON MASTER。

EXECUTE 'command' [ON ...]

只允许可读的外部Web表或可写的外部表。对于可读的外部Web表，指定要由segment实例执行的OS命令。该command可以是单个OS命令或脚本。 ON子句用于指定哪些segment实例将执行给定命令。
- 默认为ON ALL。该命令将由 YMatrix 数据库系统中所有segment主机上的每个活动primary实例执行。如果命令执行脚本，则该脚本必须位于所有segment主机上的相同位置，并且可由 YMatrix 超级用户（gpadmin）执行。
- ON MASTER仅在master主机上运行命令。
- ON number表示将按指定的segment数执行命令。特定的segment是在运行时由 YMatrix 数据库系统随机选择的。如果命令执行脚本，则该脚本必须位于所有segment主机上的相同位置，并且可由 YMatrix 超级用户（gpadmin）执行。
- HOST表示该命令将由每个segment主机上的一个segment执行（每个segment主机一次），而不管每个主机的活动segment实例数量如何。
- HOST segment_hostname表示命令将由指定的segment主机上的所有活动primary实例执行。
- SEGMENT segment_id表示命令只能由指定的segment执行一次。您可以通过查看系统catalog表gp_segment_configuration中的content编号来确定segment实例的ID。 YMatrix 数据库主数据库的content ID始终为-1。
对于可写外部表，必须准备在EXECUTE子句中指定的command以将数据传递到其中。由于所有要发送数据的segment都会将其输出写入指定的命令或程序，因此ON子句的唯一可用选项是ON ALL。

FORMAT 'TEXT | CSV' (options)

当FORMAT子句标识定界文本（TEXT）或逗号分隔值（CSV）格式时，格式设置选项与PostgreSQL COPY命令可用的格式设置选项相似。如果文件中的数据不使用默认的列定界符，转义符，空字符串等，则必须指定其他格式选项，以便 YMatrix 数据库可以正确读取外部文件中的数据。

FORMAT 'CUSTOM' (formatter=formatter_specification)

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

DELIMITER

指定一个ASCII字符，用于分隔数据的每一行（行）中的列。默认为TEXT模式下的制表符，而CSV模式下为逗号。在用于可读外部表的TEXT模式下，对于将非结构化数据加载到单列表的特殊用例，可以将定界符设置为OFF。
对于s3协议，定界符不能为换行符（\n）或回车符（\r）。

NULL

指定表示NULL值的字符串。在TEXT模式下，默认值为\N（反斜杠-N），在CSV模式下，默认值为无引号的空值。对于不希望将NULL值与空字符串区分开的情况，即使在TEXT模式下，您也可能更喜欢空字符串。使用外部表和Web表时，与该字符串匹配的任何数据项都将被视为NULL值。

ESCAPE

指定用于C转义序列的单个字符（例如\n，\t，\100等）和转义可能用作行或列定界符的数据字符。确保选择一个在实际列数据中未使用的转义字符。对于文本格式的文件，默认转义字符是\（反斜杠），对于csv格式的文件，默认转义字符是"（双引号），但是可以指定另一个字符来表示转义。也可以通过将值'OFF'指定为转义值在文本格式化文件中禁用文本转义。这对于诸如文本格式的Web日志数据之类的数据非常有用，该数据具有许多嵌入式反斜杠，而这些反斜杠并非旨在转义。

NEWLINE

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

HEADER

对于可读的外部表，指定数据文件中的第一行是标题行（包含表列的名称），并且不应包括表的数据在内。如果使用多个数据源文件，则所有文件都必须具有标题行。对于s3协议，标题行中的列名称不能包含换行符（\n）或回车符（\r）。
pxf协议不支持HEADER格式化选项。

QUOTE

指定CSV模式的引号字符。默认值为双引号（"）。

FORCE NOT NULL

在CSV模式下，处理每个指定的列就好像被引用了一样，因此不是NULL值。对于CSV模式下的默认空字符串（两个定界符之间都不存在），这将导致缺失值被评估为零长度字符串。

FORCE QUOTE

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

FILL MISSING FIELDS

对于可读外部表，在TEXT和CSV模式下，指定FILL MISSING FIELDS时，如果一行数据的行或行末尾缺少数据字段，则将缺少的尾随字段值设置为NULL（而不是报告错误）。空白行，具有NOT NULL约束的字段以及行尾的定界符仍然会报告错误。

OPTIONS key 'value'[, key' value' ...]

可选参数。当协议用作外部表的外部表协议时，将参数和值指定设置为自定义数据访问协议的键值对。自定义数据访问协议负责处理和验证键值对。

ENCODING 'encoding'

用于外部表的字符集编码。指定字符串常量（例如'SQL_ASCII'），整数编码数字或DEFAULT以使用默认的客户端编码。

LOG ERRORS [PERSISTENTLY]

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

SEGMENT REJECT LIMIT count [ROWS | PERCENT]

在单行错误隔离模式下运行COPY FROM操作。如果输入行存在格式错误，只要在加载操作期间未在任何YMatrix segment实例上达到拒绝限制计数，它们将被丢弃。拒绝限制计数可以指定为行数（默认）或总行数的百分比（1-100）。如果使用PERCENT，则只有在处理了参数gp_reject_percent_threshold指定的行数之后，每个segment才开始计算错误行百分比。 gp_reject_percent_threshold的默认值为300行。约束错误（例如违反NOT NULL，CHECK或UNIQUE约束）仍将在“全有或全无”输入模式下处理。如果未达到限制，则将加载所有正确的行，并丢弃所有错误行。
注意：读取外部表时，如果未首先触发SEGMENT REJECT LIMIT或未指定SEGMENT REJECT LIMIT，则 YMatrix 数据库会限制可能包含格式错误的初始行数。如果前1000行被拒绝，则COPY操作将停止并回滚。
可以使用 YMatrix 数据库服务器配置参数gp_initial_bad_row_limit更改初始拒绝行数的限制。

DISTRIBUTED BY ({column [opclass]}, [ ... ] )

DISTRIBUTED RANDOMLY

用于声明可写外部表的 YMatrix 数据库分发策略。默认情况下，可写外部表是随机分布的。如果要从中导出数据的源表具有哈希分发策略，并且为可写外部表定义了相同的分发键列和运算符类oplcass，则将消除在interconnect上移动行的需要，从而提高了卸载性能。当您发出诸如INSERT INTO wex_table SELECT * FROM source_table之类的卸载命令时，如果两个表具有相同的哈希分配策略，则可以将这些已卸载的行直接从segment发送到输出位置。

示例

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

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

使用gpfdist协议和gpfdist目录中找到的所有文本格式文件（*.txt）创建一个名为ext_customer的可读外部表。使用竖线（|）作为列定界符，并使用空白作为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 ',' );

使用file协议和一些带有标题行的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表，每个表主机执行一次脚本：

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的文件。使用竖线（|）作为列定界符，并使用空白作为NULL来格式化文件：

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子句时，YMatrix 数据库将捕获读取外部表数据时发生的错误。您可以查看和管理捕获的错误日志数据。

使用内置的SQL函数gp_read_error_log('table_name')。它要求对table_name具有SELECT特权。
如果指定表存在错误日志数据，则新的错误日志数据将附加到现有错误日志数据中。错误日志信息不会复制到mirror。
使用内置的SQL函数gp_truncate_error_log('table_name')删除table_name的错误日志数据。它需要表所有者特权。

当使用gpfdist，gpfdists或file协议定义了多个 YMatrix 数据库外部表并访问Linux系统中的同一命名管道时， YMatrix 数据库会将对命名管道的访问限制为单个读取器。如果第二个读取器尝试访问命名管道，则返回错误。

兼容性

CREATE EXTERNAL TABLE是 YMatrix 数据库扩展。 SQL标准没有为外部表做任何准备。

另见

ALTER EXTERNAL TABLE, DROP EXTERNAL TABLE