Greenplum导入导出-COPY命令

注：本文翻译自https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-sql_commands-COPY.html

Greenplum中的COPY命令用于在表和文件之间进行数据复制，可以将文件的数据写入到表中，也可以将表中的数据写出到文件。

语法

COPY <table_name> [(<column_name> [, ...])] 
     FROM {'<filename>' | PROGRAM '<command>' | STDIN}
     [ [ WITH ] ( <option> [, ...] ) ]
     [ ON SEGMENT ]

COPY { <table_name> [(<column_name> [, ...])] | (<query>)} 
     TO {'<filename>' | PROGRAM '<command>' | STDOUT}
     [ [ WITH ] ( <option> [, ...] ) ]
     [ ON SEGMENT ]

描述

COPY在Greenplum数据库表和标准文件系统文件之间移动数据。COPY TO将表的内容复制到一个文件(如果复制ON SEGMENT，则根据segment ID复制多个文件)，而COPY FROM将数据从文件复制到表(将数据附加到表中已经存在的任何内容)。COPY TO还可以复制SELECT查询的结果。

如果指定了多个列，COPY只会将指定列中的数据复制到文件中或从文件中复制。如果表中有列不在列列表中，COPY FROM将为这些列插入默认值。

带有文件名的COPY指示Greenplum Database协调器主机直接读取或写入文件。该文件必须可以被协调器主机访问，并且必须从协调器主机的角度指定其名称。

当COPY与ON SEGMENT子句一起使用时，COPY TO会导致segment创建单独的面向segment的文件，这些文件保留在segment主机上。ON SEGMENT的filename参数接受字符串字面值(必需的)，并使用绝对路径或<SEG_DATA_DIR>字符串字面值。当运行COPY操作时，segment id和segment数据目录的路径将替换字符串文字值。

使用COPY TO和一个复制表(DISTRIBUTED replicated)作为源，创建一个文件，其中的行来自单个segment，这样目标文件就不包含重复的行。使用COPY TO和ON SEGMENT子句，将复制表作为源，在包含所有表行的segment主机上创建目标文件。

ON SEGMENT子句允许您将表数据复制到segment主机上的文件中，以便在集群之间迁移数据或执行备份等操作中使用。由ON Segment子句创建的segment数据可以通过gpfdist等工具恢复，这对于高速数据加载非常有用。

当指定PROGRAM时，服务器运行给定的命令并从程序的标准输出中读取，或写入程序的标准输入。该命令必须从服务器的角度指定，并且gpadmin用户可以执行该命令。

当指定了STDIN或STDOUT时，数据通过客户端和协调器之间的连接传输。STDIN和STDOUT不能与ON段子句一起使用。
如果使用了SEGMENT REJECT LIMIT，那么COPY FROM操作将在单行错误隔离模式下操作。在此版本中，单行错误隔离模式仅适用于输入文件中存在格式错误的行，例如，额外或缺少属性、错误数据类型的属性或无效的客户端编码序列。约束错误，如违反了NOT NULL、CHECK或UNIQUE约束，仍将在“全有或全无”输入模式下处理。用户可以指定可接受的错误行数(以每个段为基础)，之后整个COPY FROM操作将被取消，并且不会加载任何行。错误行的计数是按段计算的，而不是按整个加载操作计算的。如果未达到每个段的拒绝限制，则将加载所有不包含错误的行，并丢弃任何错误行。要保留错误行以供进一步检查，请指定LOG ERRORS子句来捕获错误日志信息。错误信息和行存储在Greenplum数据库内部。

输出

在成功完成时，COPY命令返回表单的命令标签，其中count是复制的行数:

COPY <count>

如果在单行错误隔离模式下运行COPY FROM命令，如果由于格式错误而未加载任何行，则将返回以下通知消息，其中count是被拒绝的行数:

NOTICE: Rejected <count> badly formatted rows.

参数

table_name
现有表的名称(可选的模式限定)。

column_name
要复制的列的可选列表。如果未指定列列表，则复制表的所有列。
当以文本格式复制时，默认情况下，bytea类型列中的一行数据最多可达256MB。

query
要复制其结果的SELECT或VALUES命令。请注意，查询周围需要使用圆括号。

filename
输入或输出文件的路径名。输入文件名可以是绝对路径，也可以是相对路径，但输出文件名必须是绝对路径。Windows用户可能需要使用E’'字符串，并在路径名中使用双斜杠。

PROGRAM ‘command’
指定要运行的命令。在COPY FROM中，从命令的标准输出中读取输入，在COPY TO中，将输出写入命令的标准输入。该命令必须从Greenplum数据库协调器主机系统的角度指定，并且必须可以由Greenplum数据库管理员用户(gpadmin)执行。
该命令由shell调用。当向shell传递参数时，去掉或转义对shell有特殊意义的任何特殊字符。出于安全考虑，最好使用固定的命令字符串，或者至少避免在字符串中传递任何用户输入。
当指定ON SEGMENT时，该命令必须由Greenplum数据库管理员用户(gpadmin)在所有Greenplum数据库主segment主机上可执行。该命令由每个Greenplum段实例运行。在命令中是必需的。
请参阅ON SEGMENT子句，了解命令语法要求以及指定该子句时复制的数据。

STDIN
指定输入来自客户机应用程序。STDIN不支持ON SEGMENT子句。

STDOUT
指定输出到客户端应用程序。STDOUT不支持ON SEGMENT子句。

boolean
指定所选选项是打开还是关闭。您可以写TRUE、ON或1来启用该选项，写FALSE、OFF或0来禁用该选项。布尔值也可以省略，在这种情况下假设为TRUE。

FORMAT
选择要读写的数据格式:文本、csv(逗号分隔值)或二进制。默认为文本。

oid
指定复制每一行的OID。(如果为没有oid的表指定oid，或者复制查询，则会引发错误。)

FREEZE
请求复制已经冻结的行数据，就像运行VACUUM FREEZE命令后一样。这是作为初始数据加载的性能选项。只有当被加载的表在当前子事务中被创建或截断，没有游标打开，并且这个事务没有旧的快照时，行才会被冻结。
请注意，一旦成功加载数据，所有其他会话将能够立即看到数据。这违反了MVCC可见性的正常规则，指定此选项的用户应该意识到这可能导致的潜在问题。

DELIMITER
指定分隔文件每行(行)内的列的字符。默认为文本格式中的制表符，CSV格式中的逗号。这必须是一个单字节字符。当使用二进制格式时，不允许此选项。

NULL
指定表示空值的字符串。在文本格式中默认为\N(反斜杠-N)，在CSV格式中默认为不加引号的空字符串。在不想区分空字符串和空字符串的情况下，即使在文本格式中，也可能更喜欢使用空字符串。当使用二进制格式时，不允许此选项。

HEADER
指定文件包含头行，其中包含文件中每个列的名称。在输出时，第一行包含表中的列名，在输入时，第一行被忽略。此选项仅在使用CSV格式时允许。

QUOTE
指定在引用数据值时使用的引号字符。默认是双引号。这必须是一个单字节字符。此选项仅在使用CSV格式时允许。

ESCAPE
指定应该出现在与QUOTE值匹配的数据字符之前的字符。默认值与QUOTE值相同(因此，如果引号字符出现在数据中，则引号字符将加倍)。这必须是一个单字节字符。此选项仅在使用CSV格式时允许。

FORCE_QUOTE
强制对每个指定列中的所有非NULL值使用引号。NULL输出永远不会被引用。如果指定了*，则所有列中的非NULL值将被引用。此选项仅在COPY TO中允许，并且仅在使用CSV格式时允许。

FORCE_NOT_NULL
不要将指定列的值与空字符串进行匹配。在null字符串为空的默认情况下，这意味着空值将被读取为零长度字符串，而不是空值，即使它们没有引号。此选项仅在COPY FROM中允许，并且仅在使用CSV格式时允许。

FORCE_NULL
将指定列的值与空字符串相匹配，即使它已被引用，如果找到匹配，则将值设置为null。在null字符串为空的默认情况下，这将把带引号的空字符串转换为null。此选项仅在COPY FROM中允许，并且仅在使用CSV格式时允许。

ENCODING
指定以encoding_name对文件进行编码。如果省略此选项，则使用当前客户端编码。

ON SEGMENT
在segment主机上指定单独的段数据文件。每个文件都包含由主segment实例管理的表数据。例如，当使用COPY to…将数据从表复制到文件时。ON SEGMENT命令，该命令为主机上的每个segment实例在segment主机上创建一个文件。每个文件都包含由segment实例管理的表数据。
COPY命令不从镜像segment实例和segment数据文件中复制数据。
ON SEGMENT不支持关键字STDIN和STDOUT。

<SEG_DATA_DIR>和字符串字面值用于指定绝对路径和文件名，语法如下:

COPY <table> [TO|FROM] '<SEG_DATA_DIR>/<gpdumpname><SEGID>_<suffix>' ON SEGMENT;

<SEG_DATA_DIR>
表示用于ON SEGMENT复制的segment实例数据目录的绝对路径的字符串字面值。尖括号(<和>)是用于指定路径的字符串字面值的一部分。当COPY运行时，用segment路径替换字符串字面值。绝对路径可以用来代替<SEG_DATA_DIR>字符串字面值。

<SEGID>
表示在复制ON SEGMENT时要复制的segment实例的content ID号的字符串字面值。当指定ON SEGMENT时，是文件名的必需部分。尖括号是用于指定文件名的字符串字面值的一部分。

使用COPY TO，当运行COPY命令时，字符串字面值将被segment实例的content ID所替换。

使用COPY FROM，在文件名中指定segment实例content ID，并将该文件放在segment实例主机上。每个主机上的每个主segment实例都必须有一个文件。当执行COPY FROM命令时，数据将从文件复制到segment实例。

当指定PROGRAM命令子句时，命令中需要字符串字面值，<SEG_DATA_DIR>字符串字面值是可选的。

对于COPY FROM … ON SEGMENT命令，在将数据复制到表中时检查表的分布策略。默认情况下，如果数据行违反表分布策略，则返回错误。您可以使用服务器配置参数gp_enable_segment_copy_checking禁用分发策略检查。

NEWLINE
指定数据文件中使用的换行符- LF(换行，0x0A)， CR(回车，0x0D)或CRLF(回车加换行，0x0D 0x0A)。如果没有指定，Greenplum数据库段将通过查看它接收到的第一行数据并使用遇到的第一个换行符类型来检测换行符类型。

CSV
选择CSV(逗号分隔值)模式。请参见CSV格式。

FILL MISSING FIELDS
在TEXT和CSV的COPY FROM中，指定FILL MISSING FIELDS会在一行数据的末尾有缺失的数据字段时将缺失的尾随字段值设置为NULL(而不是报告错误)。空白行、带有NOT NULL约束的字段以及行上的尾随分隔符仍然会报告错误。

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

SEGMENT REJECT LIMIT count[ROWS|PERCENT]
在单行错误隔离模式下运行COPY FROM操作。如果输入行有格式错误，它们将被丢弃，只要在加载操作期间没有达到任何Greenplum数据库段实例的拒绝限制计数。拒绝限制计数可以指定为行数(默认值)或占总行数的百分比(1-100)。如果使用PERCENT，则只有在gp_reject_percent_threshold参数指定的行数被处理之后，每个段才开始计算坏行百分比。gp_reject_percent_threshold的默认值是300行。约束错误，如违反了NOT NULL、CHECK或UNIQUE约束，仍将在“全有或全无”输入模式下处理。如果没有达到限制，将加载所有好的行，并丢弃任何错误的行。
可以使用Greenplum Database服务器配置参数gp_initial_bad_row_limit更改初始拒绝行的数量限制。

IGNORE EXTERNAL PARTITIONS
从分区表复制数据时，不会从作为外部表的叶子分区复制数据。当不复制数据时，将向日志文件中添加一条消息。
如果未指定此子句，并且Greenplum Database试图从叶子子分区(即外部表)复制数据，则返回错误。

示例

使用竖线(|)作为字段分隔符复制一个表到客户端:

COPY country TO STDOUT (DELIMITER '|');

将数据从文件复制到country表中:

COPY country FROM '/home/usr1/sql/country_data';

将名称以“a”开头的国家复制到一个文件中:

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 
'/home/usr1/sql/a_list_countries.copy';

使用单行错误隔离模式和日志错误将数据从文件复制到sales表中:

COPY sales FROM '/home/usr1/sql/sales_data' LOG ERRORS 
   SEGMENT REJECT LIMIT 10 ROWS;

要复制段数据以供以后使用，请使用ON segment子句。COPY TO ON SEGMENT命令的使用形式如下:

COPY <table> TO '<SEG_DATA_DIR>/<gpdumpname><SEGID>_<suffix>' ON SEGMENT; 

是必需的。但是，可以用绝对路径代替路径中的<SEG_DATA_DIR>字符串字面值。
当您将字符串字面值<SEG_DATA_DIR>和传递给COPY时，COPY将在运行操作时填充适当的值。
例如，如果你有一个mytable，它的segment和镜像segment是这样的:

contentid | dbid | file segment location 
    0     |  1   | /home/usr1/data1/gpsegdir0
    0     |  3   | /home/usr1/data_mirror1/gpsegdir0 
    1     |  4   | /home/usr1/data2/gpsegdir1
    1     |  2   | /home/usr1/data_mirror2/gpsegdir1 

运行命令：

COPY mytable TO '<SEG_DATA_DIR>/gpbackup<SEGID>.txt' ON SEGMENT;

将导致结果：

/home/usr1/data1/gpsegdir0/gpbackup0.txt
/home/usr1/data2/gpsegdir1/gpbackup1.txt

第一列中的content ID是插入到文件路径中的标识符(例如，上面的gpsegdir0/gpbackup0.txt)。文件是在段主机上创建的，而不是在协调器上创建的，就像在标准COPY操作中一样。当使用ON SEGMENT复制时，不会为镜像段创建数据文件。
如果指定了绝对路径，而不是像语句中那样指定<SEG_DATA_DIR>

COPY mytable TO '/tmp/gpdir/gpbackup_<SEGID>.txt' ON SEGMENT;

文件将放在每个段的/tmp/gpdir中。如果需要重新分发，还可以使用gpfdist工具恢复使用COPY to和ON SEGMENT选项生成的数据文件。
下面的例子使用SELECT语句将数据复制到每个段上的文件中:

COPY (SELECT * FROM testtbl) TO '/tmp/mytst<SEGID>' ON SEGMENT;

本例从lineitem表复制数据，并使用PROGRAM子句使用cat实用程序将数据添加到/tmp/lineitem_program.csv文件。该文件放在Greenplum数据库协调器上。

COPY LINEITEM TO PROGRAM 'cat > /tmp/lineitem.csv' CSV; 

本例使用PROGRAM和ON SEGMENT子句将数据复制到段主机上的文件中。在段主机上，COPY命令用段内容ID替换，为段主机上的每个段实例创建一个文件。

COPY LINEITEM TO PROGRAM 'cat > /tmp/lineitem_program<SEGID>.csv' ON SEGMENT CSV;