CREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE

定义一个新的外部表。

语法

``` sql CREATE [READABLE] EXTERNAL [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, …] | LIKE other_table ) LOCATION ('file://seghost[:port]/path/file' [, …]) | ('scfs://filehost[:port]/file_pattern[#transform=trans_name]' [, …] | ('scfs://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]') [ON COORDINATOR] 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=) [ ENCODING 'encoding' ] [ [LOG ERRORS] executor实例 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 | COORDINATOR | number_of_executor实例s | HOST ['executor实例_hostname'] | executor实例 executor实例_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=) [ ENCODING 'encoding' ] [ [LOG ERRORS] executor实例 REJECT LIMIT count [ROWS | PERCENT] ]

CREATE WRITABLE EXTERNAL [TEMPORARY | TEMP] TABLE table_name ( column_name data_type [, …] | LIKE other_table ) LOCATION('scfs://outputhost[:port]/filename[#transform=trans_name]' [, …]) | ('scfs://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>)
[ 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]') [ON COORDINATOR] 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=) [ ENCODING 'write_encoding' ] [ DISTRIBUTED BY ({column [opclass]}, [ … ] ) | DISTRIBUTED RANDOMLY ] ```

描述

有关外部表的详细信息，请参阅SeaboxMPP数据库管理员指南中的“使用外部表”。

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

CREATE WRITABLE EXTERNAL TABLE或CREATE WRITABLE EXTERNAL WEB TABLE在SeaboxMPP数据库中创建新的可写外部表定义。可写的外部表通常用于将数据库中的数据卸载到一组文件或命名管道中。

可写的外部Web表也可以用于将数据输出到可执行程序。可写的外部表也可以用作SeaboxMPP并行MapReduce计算的输出目标。一旦定义了可写外部表，就可以从数据库表中选择数据并将其插入可写外部表中。 可写的外部表仅允许INSERT操作 ，不允许执行SELECT，UPDATE，DELETE或TRUNCATE。

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

参数

该SQL命令参数说明见下

READABLE | WRITABLE

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

WEB

在SeaboxMPP数据库中创建一个可读或可写的外部Web表定义。 可读的外部Web表有两种形式, 通过http://协议访问文件的表或通过执行OS命令访问数据的表。可写的外部Web表将数据输出到可执行程序，该程序可以接受输入的数据流。外部Web表在查询执行期间不可重新扫描。

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

TEMPORARY | TEMP

如果指定，则在SeaboxMPP数据库中创建一个临时的可读或可写外部表定义。 临时外部表存在于特殊模式中。 创建表时，无法指定模式名称。在会话结束时，将自动删除临时外部表。

临时表存在时，当前会话将看不到具有相同名称的已知永久表，除非您引用具有其模式限定名称的永久表。

table_name

新外部表的名称。

column_name

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

LIKE other_table

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

data_type

列的数据类型。

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

如果使用pxf协议访问外部数据源，请参阅PXF创建外部表文档，以获取有关pxf协议LOCATION子句语法的详细信息。

如果使用s3协议读取或写入S3，请参阅关于S3协议URL， 以获取有关s3协议LOCATION子句语法的其他信息。

对于可读的外部表，指定用于填充外部表或Web表的外部数据源的URI。 常规的可读外部表允许使用scfs或file协议。外部Web表允许使用http协议。 如果省略port，则将http和scfs协议的端口假定为8080。如果使用scfs协议，则path是相对于scfs从中提供文件的目录（启动scfs程序时指定的目录）。另外，scfs可以使用通配符或其他C样式模式匹配（例如，空格字符为[[:space:]]）来表示目录中的多个文件。 例如：

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

对于可写的外部表，指定scfs进程或S3协议的URI位置，该进程将收集SeaboxMPP executor中的数据输出并将其写入一个或多个命名文件。

对于scfs，该路径是相对于scfs从中提供文件的目录（启动scfs程序时指定的目录）的。如果列出了多个scfs位置，则发送数据的executor实例将在可用的输出位置之间平均分配。 例如：

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

在上面的示例中列出了两个scfs位置的情况下，一半的executor实例会将其输出数据发送到data1.out文件，另一半发送到data2.out文件。

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

ON COORDINATOR

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

注意: 在使用ON COORDINATOR子句读取或写入创建的外部表时，请注意潜在的资源影响。当您仅将表操作限制为SeaboxMPP coordinator时，可能会遇到性能问题。

EXECUTE 'command' \[ON ...\]

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

ON ALL。

默认为该值该命令将由SeaboxMPP数据库系统中所有executor实例主机上的每个活动primary实例执行。如果命令执行脚本，则该脚本必须位于所有executor实例主机上的相同位置，并且可由SeaboxMPP超级用户（seabox）执行。

ON COORDINATOR

仅在coordinator主机上运行命令。

注意: 指定ON COORDINATOR子句时，外部Web表不支持日志记录。

ON number

表示将按指定的executor实例数执行命令。 特定的executor实例是在运行时由SeaboxMPP数据库系统随机选择的。 如果命令执行脚本，则该脚本必须位于所有executor实例主机上的相同位置，并且可由SeaboxMPP超级用户（seabox）执行。

HOST

表示该命令将由每个executor实例主机上的一个executor实例执行（每个executor实例主机一次），而不管每个主机的活动executor实例实例数量如何。

HOST executor实例_hostname

表示命令将由指定的executor实例主机上的所有活动primary实例执行。

executor实例

executor实例_id表示命令只能由指定的executor实例执行一次。 您可以通过查看系统catalog表sc_executor实例_configuration中的content编号来确定executor实例实例的ID。 SeaboxMPP数据库主数据库的content ID始终为-1。

对于可写外部表，必须准备在EXECUTE子句中指定的command以将数据传递到其中。由于所有要发送数据的executor实例都会将其输出写入指定的命令或程序，因此ON子句的唯一可用选项是ON ALL。

FORMAT 'TEXT | CSV' (options)

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

有关使用自定义格式的信息，请参阅SeaboxMPP数据库管理员指南中的“加载和卸载数据”。如果使用pxf协议访问外部数据源，请参阅PXF[创建外部表]文档，以获取有关pxf协议FORMAT子句语法的详细信息。

FORMAT 'CUSTOM' (formatter=formatter_specification)

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

如果使用pxf协议访问外部数据源，请参阅PXF[创建外部表]文档，以获取有关pxf协议FORMAT子句语法的详细信息。有关使用自定义格式的一般信息，请参阅SeaboxMPP数据库管理员指南中的“加载和卸载数据”。

DELIMITER

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

NULL

指定表示NULL值的字符串。 在TEXT模式下，默认值为\N（反斜杠-N），在CSV模式下，默认值为无引号的空值。对于不希望将NULL值与空字符串区分开的情况，即使在TEXT模式下，也可能更喜欢空字符串。使用外部表和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）。

如果未指定，SeaboxMPP数据库executor实例将通过查看接收到的第一行数据并使用遇到的第一个换行符类型来检测换行符类型。

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约束的字段以及行尾的定界符仍然会报告错误。

ENCODING 'encoding'

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

LOG ERRORS

这是一个可选的子句，可以在executor实例 REJECT LIMIT子句之前记录有关具有格式错误的行的信息。错误日志信息存储在内部，并可以通过SeaboxMPP数据库内置的SQL函数sc_read_error_log()进行访问。请参阅[注解]以获取有关错误日志信息的信息以及用于查看和管理错误日志信息的内置函数。

executor REJECT LIMIT count \[ROWS | PERCENT\]

在单行错误隔离模式下运行COPY FROM操作。 如果输入行存在格式错误，只要在加载操作期间未在任何SeaboxMPP executor实例上达到拒绝限制计数，它们将被丢弃。 拒绝限制计数可以指定为行数（默认）或总行数的百分比（1-100）。如果使用PERCENT，则只有在处理了参数sc_reject_percent_threshold指定的行数之后，每个executor实例才开始计算错误行百分比。

sc_reject_percent_threshold的默认值为300行。 约束错误（例如违反NOT NULL，CHECK或UNIQUE约束）仍将在“全有或全无”输入模式下处理。如果未达到限制，则将加载所有正确的行，并丢弃所有错误行。

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

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

用于声明可写外部表的SeaboxMPP数据库分发策略。 默认情况下，可写外部表是随机分布的。

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

示例

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

scfs -p 8081 -d /var/data/staging -l /home/seabox/log &

使用scfs协议和scfs目录中找到的所有文本格式文件（*.txt）创建一个名为ext_customer的可读外部表。 使用竖线（|）作为列定界符，并使用空白作为NULL来格式化文件。 还要以单行错误隔离模式访问外部表：

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

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

CREATE EXTERNAL TABLE ext_customer 
   (id int, name text, sponsor text) 
   LOCATION ( 'scfs://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的可写外部表，该表使用scfs将输出数据写入名为sales.out的文件。 使用竖线（|）作为列定界符，并使用空白作为NULL来格式化文件。

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

创建一个可写的外部Web表，将executor实例接收的输出数据通过管道传输到名为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子句时，SeaboxMPP数据库将捕获读取外部表数据时发生的错误。 您可以查看和管理捕获的错误日志数据。

• 使用内置的SQL函数sc_read_error_log('table_name')。

要求对table_name具有SELECT权限。本示例使用COPY命令显示加载到表ext_expenses中的数据的错误日志信息：

SELECT * from sc_read_error_log('ext_expenses');

有关错误日志格式的信息，请参阅SeaboxMPP数据库管理员指南中的查看错误日志中的坏行。

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

• 如果指定表存在错误日志数据，则新的错误日志数据将附加到现有错误日志数据中。 错误日志信息不会复制到mirror。

• 使用内置的SQL函数sc_truncate_error_log('table_name')删除table_name的错误日志数据。需要表所有者权限。本示例删除将数据移入表ext_expenses时捕获的错误日志信息：

SELECT sc_truncate_error_log('ext_expenses'); 

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

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

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

兼容性说明

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

相关SQL命令

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