CREATE EXTERNAL TABLE
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]')
[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>)
[ ENCODING 'encoding' ]
[ [LOG ERRORS] 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>)
[ ENCODING 'encoding' ]
[ [LOG ERRORS] 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>)
[ 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 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>)
[ ENCODING 'write_encoding' ]
[ DISTRIBUTED BY ({column [opclass]}, [ ... ] ) | DISTRIBUTED RANDOMLY ]
描述
有关外部表的详细信息，请参阅Greenplum数据库管理员指南中的“使用外部表”。
CREATE EXTERNAL TABLE或CREATE EXTERNAL WEB TABLE在Greenplum数据库中创建一个新的可读外部表定义。 可读的外部表通常用于快速并行数据加载。 定义外部表后，您可以使用SQL命令直接（或并行）查询其数据。 例如，您可以选择，联接或排序外部表数据。 您也可以为外部表创建视图。 可读外部表上不允许DML操作（UPDATE，INSERT，DELETE或TRUNCATE），并且您不能在可读外部表上创建索引。
CREATE WRITABLE EXTERNAL TABLE或CREATE WRITABLE EXTERNAL WEB TABLE在Greenplum数据库中创建新的可写外部表定义。 可写的外部表通常用于将数据库中的数据卸载到一组文件或命名管道中。 可写的外部Web表也可以用于将数据输出到可执行程序。 可写的外部表也可以用作Greenplum并行MapReduce计算的输出目标。 一旦定义了可写外部表，就可以从数据库表中选择数据并将其插入可写外部表中。 可写的外部表仅允许INSERT操作 – 不允许执行SELECT，UPDATE，DELETE或TRUNCATE。
常规外部表和外部Web表之间的主要区别在于它们的数据源。 常规可读的外部表访问静态平面文件，而外部Web表访问动态数据源 - 在Web服务器上或通过执行OS命令或脚本。
参数
- READABLE | WRITABLE
- 指定外部表的类型，默认为可读。 可读的外部表用于将数据加载到Greenplum数据库中。 可写的外部表用于卸载数据。
- WEB
- 在Greenplum数据库中创建一个可读或可写的外部Web表定义。 可读的外部Web表有两种形式 - 通过http://协议访问文件的表或通过执行OS命令访问数据的表。 可写的外部Web表将数据输出到可执行程序，该程序可以接受输入的数据流。 外部Web表在查询执行期间不可重新扫描。
- s3协议不支持外部Web表。 但是，您可以创建一个外部Web表，该表执行第三方工具以从S3读取数据或直接向S3写入数据。
- TEMPORARY | TEMP
- 如果指定，则在Greenplum数据库中创建一个临时的可读或可写外部表定义。 临时外部表存在于特殊模式中。 创建表时，无法指定模式名称。 在会话结束时，将自动删除临时外部表。
- 临时表存在时，当前会话将看不到具有相同名称的已知永久表，除非您引用具有其模式限定名称的永久表。
- table_name
- 新外部表的名称。
- column_name
- 要在外部表定义中创建的列的名称。 与常规表不同，外部表没有列约束或默认值，因此请不要指定它们。
- LIKE other_table
- LIKE子句指定一个表，新的外部表将从该表中自动复制所有列名称，数据类型和Greenplum分配策略。 如果原始表指定了任何列约束或默认列值，则不会将这些约束复制到新的外部表定义中。
- data_type
- 列的数据类型。
- LOCATION ('protocol://[host[:port]]/path/file' [, ...])
- 如果使用pxf协议访问外部数据源，请参阅PXF创建外部表文档， 以获取有关pxf协议LOCATION子句语法的详细信息。
- 如果使用s3协议读取或写入S3，请参阅关于S3协议URL， 以获取有关s3协议LOCATION子句语法的其他信息。
- 对于可读的外部表，指定用于填充外部表或Web表的外部数据源的URI。 常规的可读外部表允许使用gpfdist或file协议。 外部Web表允许使用http协议。 如果省略port，则将http和gpfdist协议的端口假定为8080。 如果使用gpfdist协议，则path是相对于gpfdist从中提供文件的目录（启动gpfdist程序时指定的目录）。 另外，gpfdist可以使用通配符或其他C样式模式匹配（例如，空格字符为[[:space:]]）来表示目录中的多个文件。 例如：
-
'gpfdist://filehost:8081/*' 'gpfdist://masterhost/my_load_file' 'file://seghost1/dbfast1/external/myfile.txt' 'http://intranet.example.com/finance/expenses.csv'
- 对于可写的外部表，指定gpfdist进程或S3协议的URI位置，该进程将收集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。
- ON MASTER
- 将所有与表相关的操作限制为Greenplum master。
仅允许使用s3或自定义协议创建的可读写外部表。
gpfdist，gpfdists，pxf和file协议不支持ON MASTER。
Note: 在使用ON MASTER子句读取或写入创建的外部表时，请注意潜在的资源影响。 当您仅将表操作限制为Greenplum master时，可能会遇到性能问题。
- EXECUTE 'command' [ON ...]
- 只允许可读的外部Web表或可写的外部表。
对于可读的外部Web表，指定要由segment实例执行的OS命令。
该command可以是单个OS命令或脚本。
ON子句用于指定哪些segment实例将执行给定命令。
- 默认为ON ALL。 该命令将由Greenplum数据库系统中所有segment主机上的每个活动primary实例执行。 如果命令执行脚本，则该脚本必须位于所有segment主机上的相同位置，并且可由Greenplum超级用户（gpadmin）执行。
- ON MASTER仅在master主机上运行命令。
Note: 指定ON MASTER子句时，外部Web表不支持日志记录。
- ON number表示将按指定的segment数执行命令。 特定的segment是在运行时由Greenplum数据库系统随机选择的。 如果命令执行脚本，则该脚本必须位于所有segment主机上的相同位置，并且可由Greenplum超级用户（gpadmin）执行。
- HOST表示该命令将由每个segment主机上的一个segment执行（每个segment主机一次），而不管每个主机的活动segment实例数量如何。
- HOST segment_hostname表示命令将由指定的segment主机上的所有活动primary实例执行。
- SEGMENT segment_id表示命令只能由指定的segment执行一次。 您可以通过查看系统catalog表gp_segment_configuration中的content编号来确定segment实例的ID。 Greenplum数据库主数据库的content ID始终为-1。
对于可写外部表，必须准备在EXECUTE子句中指定的command以将数据传递到其中。 由于所有要发送数据的segment都会将其输出写入指定的命令或程序，因此ON子句的唯一可用选项是ON ALL。
- FORMAT 'TEXT | CSV' (options)
- 当FORMAT子句标识定界文本（TEXT）或逗号分隔值（CSV）格式时， 格式设置选项与PostgreSQL COPY命令可用的格式设置选项相似。 如果文件中的数据不使用默认的列定界符，转义符，空字符串等，则必须指定其他格式选项，以便Greenplum数据库可以正确读取外部文件中的数据。 有关使用自定义格式的信息，请参阅Greenplum数据库管理员指南中的“加载和卸载数据”。
- 如果使用pxf协议访问外部数据源， 请参阅PXF创建外部表文档，以获取有关pxf协议FORMAT子句语法的详细信息。
- FORMAT 'CUSTOM' (formatter=formatter_specification)
- 指定自定义数据格式。 formatter_specification指定用于格式化数据的函数，后跟格式化函数的逗号分隔参数。 格式化程序规范的长度（包括Formatter=的字符串）最多可达到约50K字节。
- 如果使用pxf协议访问外部数据源， 请参阅PXF创建外部表文档， 以获取有关pxf协议FORMAT子句语法的详细信息。
- 有关使用自定义格式的一般信息，请参阅Greenplum数据库管理员指南中的“加载和卸载数据”。
- 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）。 如果未指定，Greenplum数据库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约束的字段以及行尾的定界符仍然会报告错误。
- 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实例上达到拒绝限制计数，它们将被丢弃。 拒绝限制计数可以指定为行数（默认）或总行数的百分比（1-100）。 如果使用PERCENT，则只有在处理了参数gp_reject_percent_threshold指定的行数之后，每个segment才开始计算错误行百分比。 gp_reject_percent_threshold的默认值为300行。 约束错误（例如违反NOT NULL，CHECK或UNIQUE约束）仍将在“全有或全无”输入模式下处理。 如果未达到限制，则将加载所有正确的行，并丢弃所有错误行。
-
Note: 读取外部表时，如果未首先触发SEGMENT REJECT LIMIT或未指定SEGMENT REJECT LIMIT， 则Greenplum数据库会限制可能包含格式错误的初始行数。 如果前1000行被拒绝，则COPY操作将停止并回滚。
可以使用Greenplum数据库服务器配置参数gp_initial_bad_row_limit更改初始拒绝行数的限制。 有关参数的信息，请参阅服务器配置参数。
- DISTRIBUTED BY ({column [opclass]}, [ ... ] )
- DISTRIBUTED RANDOMLY
- 用于声明可写外部表的Greenplum数据库分发策略。 默认情况下，可写外部表是随机分布的。 如果要从中导出数据的源表具有哈希分发策略，并且为可写外部表定义了相同的分发键列和运算符类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/gpadmin/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子句时，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。
- 如果指定表存在错误日志数据，则新的错误日志数据将附加到现有错误日志数据中。 错误日志信息不会复制到mirror。
- 使用内置的SQL函数gp_truncate_error_log('table_name')删除table_name的错误日志数据。
它需要表所有者特权。本示例删除将数据移入表ext_expenses时捕获的错误日志信息：
SELECT gp_truncate_error_log('ext_expenses');如果table_name不存在，则该函数返回FALSE。
指定*通配符可删除当前数据库中现有表的错误日志信息。 指定字符串*.*以删除所有数据库错误日志信息，包括由于先前的数据库问题而未被删除的错误日志信息。 如果指定*，则需要数据库所有者特权。 如果指定*.*，则需要操作系统超级用户特权。
当使用gpfdist，gpfdists或file协议定义了多个Greenplum数据库外部表并访问Linux系统中的同一命名管道时， Greenplum数据库会将对命名管道的访问限制为单个读取器。 如果第二个读取器尝试访问命名管道，则返回错误。
兼容性
CREATE EXTERNAL TABLE是Greenplum数据库扩展。 SQL标准没有为外部表做任何准备。