概要

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 ]

FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
FILL MISSING FIELDS
LOG ERRORS [ SEGMENT REJECT LIMIT count [ ROWS | PERCENT ] ]
IGNORE EXTERNAL PARTITIONS

描述

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

如果指定了列列表，则COPY仅将指定列中的数据复制到文件中或从文件复制。 如果表中的任何列不在列列表中，则COPY FROM将为这些列插入默认值。

带有文件名的COPY指示Greenplum数据库master主机直接从文件读取或写入文件。 该文件必须可供master主机访问，并且必须从master主机的角度指定名称。

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

使用复制表（DISTRIBUTED REPLICATED）作为源的COPY TO将创建一个文件，其中包含来自单个segment的行，以便目标文件不包含重复的行。 将COPY TO与ON SEGMENT子句一起使用，并将复制表作为源，则在包含所有表行的segment主机上创建目标文件。

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

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

当指定STDIN或STDOUT时，数据将通过客户端和master之间的连接进行传输。 STDIN和STDOUT不能与ON SEGMENT子句一起使用。

如果使用SEGMENT REJECT LIMIT，则COPY FROM操作将在单行错误隔离模式下运行。 在此版本中，单行错误隔离模式仅适用于输入文件中格式错误的行 - 例如，多余或缺失的属性，错误的数据类型的属性或无效的客户端编码序列。 约束错误（例如违反NOT NULL，CHECK或UNIQUE约束）仍将在“全有或全无”输入模式下处理。 用户可以指定可接受的错误行数（基于每个segment），之后将终止整个COPY FROM操作，并且不会加载任何行。 错误行的计数是按segment而不是整个加载操作计数的。 如果未达到每个segment拒绝的限制，那么将加载所有不包含错误的行，并丢弃所有错误行。 要保留错误行以供进一步检查，请指定LOG ERRORS子句以捕获错误日志信息。 错误信息和该行存储在Greenplum内部数据库中。

COPY 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数据库master主机系统的角度指定该command，并且该命令必须由Greenplum数据库管理员用户（gpadmin）执行。

该command由shell程序调用。 将参数传递给shell时，请删除或转义对shell具有特殊含义的任何特殊字符。 出于安全原因，最好使用固定的命令字符串，或者至少避免在字符串中传递任何用户输入。

当指定ON SEGMENT时，该命令必须由Greenplum数据库管理员用户（gpadmin）在所有Greenplum数据库primary segment主机上可执行。 该命令由每个Greenplum segment实例执行。 <SEGID>在command中是必需的。

有关命令语法要求和指定该子句时要复制的数据的信息，请参见ON SEGMENT子句。

STDIN

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

STDOUT

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

boolean

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

FORMAT

选择要读取或写入的数据格式：text，csv（逗号分隔值）或binary。 默认为text。

OIDS

指定为每行复制OID。 （如果为没有OID的表指定了OIDS，或者在复制查询的情况下，将引发错误。）

FREEZE

请求复制已冻结的行的数据，就像在运行VACUUM FREEZE命令之后一样。 这旨在用作初始数据加载的性能选项。 仅当在当前子事务中已创建或截断了要加载的表，没有打开游标并且该事务没有任何较旧的快照时，行才会被冻结。

请注意，一旦成功加载数据，所有其他会话将立即能够看到数据。 这违反了MVCC可见性的常规规则，指定此选项的用户应注意可能引起的潜在问题。

DELIMITER

指定用于分隔文件每一行（行）中各列的字符。 默认为text格式的制表符，CSV格式的逗号。 这必须是一个单字节字符。 使用binary格式时，不允许使用此选项。

NULL

指定表示空值的字符串。 默认值为文本格式\N（反斜杠-N），以及CSV格式的无引号的空字符串。 对于不想将空值与空字符串区分开的情况，甚至可能以text格式使用空字符串。 使用binary格式时，不允许使用此选项。

Note: 使用COPY FROM时，与该字符串匹配的任何数据项都将存储为空值，因此您应确保使用与COPY TO相同的字符串。

HEADER

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

QUOTE

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

ESCAPE

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

FORCE_QUOTE

强制将引号用于每个指定列中的所有非NULL值。 NULL输出从不引用。 如果指定*，则在所有列中都引用非NULL值。 仅在COPY TO中和使用CSV格式时才允许使用此选项。

FORCE_NOT_NULL

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

ENCODING

指定文件以encoding_name编码。 如果省略此选项，则使用当前的客户端编码。 有关更多详细信息，请参见下面的注释。

ON SEGMENT

在segment主机上指定各个segment数据文件。 每个文件都包含由primary segment实例管理的表数据。 例如，当使用COPY TO...ON SEGMENT命令从表中将数据复制到文件时， 该命令会在segment主机上为主机上的每个segment实例创建一个文件。 每个文件都包含由segment实例管理的表数据。

COPY命令不会从mirror实例和segment数据文件之间复制数据。

ON SEGMENT不支持关键字STDIN和STDOUT。

<SEG_DATA_DIR>和<SEGID>字符串文字用于使用以下语法指定绝对路径和文件名：

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

<SEG_DATA_DIR>

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

<SEGID>

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

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

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

当指定了PROGRAM command子句时， command中需要<SEGID>字符串文字，而<SEG_DATA_DIR>字符串文字是可选的。 请参阅示例。

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

NEWLINE

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

CSV

选择逗号分隔值（CSV）模式。 请参阅CSV格式。

FILL MISSING FIELDS

在TEXT和CSV的COPY FROM more中， 指定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数据库segment实例上达到拒绝限制计数，它们将被丢弃。 拒绝限制计数可以指定为行数（默认）或总行数的百分比（1-100）。 如果使用PERCENT， 则只有在处理了参数gp_reject_percent_threshold指定的行数之后， 每个segment才开始计算错误行百分比。 gp_reject_percent_threshold的默认值为300行。 约束错误（例如违反NOT NULL，CHECK或UNIQUE约束）仍将在“全有或全无”输入模式下处理。 如果未达到限制，则将加载所有正确的行，并丢弃所有错误行。

Note: 如果未先触发或未指定SEGMENT REJECT LIMIT，则Greenplum数据库会限制可能包含格式错误的初始行数。 如果前1000行被拒绝，则COPY操作将停止并回滚。

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

IGNORE EXTERNAL PARTITIONS

从分区表复制数据时，不会从外部表的叶子分区复制数据。 不复制数据时，将在日志文件中添加一条消息。

如果未指定此子句，并且Greenplum数据库尝试从作为外部表的叶子分区中复制数据，则返回错误。

有关指定SQL查询以从作为外部表的叶子分区中复制数据的信息，请参见下一节“注释”。

注解

COPY只能与表一起使用，而不能与外部表或视图一起使用。 但是，您可以执行COPY (SELECT * FROM viewname) TO ...

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

COPY仅处理特定名称的表，它不会在子表之间复制数据。 因此，例如COPY table TO显示的数据与SELECT * FROM ONLY table相同。 但是COPY (SELECT * FROM table) TO ...可用于转储继承层次结构中的所有数据。

同样，要从具有作为外部表的叶子分区的分区表中复制数据，请使用SQL查询来复制数据。 例如，如果表my_sales包含带有作为外部表的叶子分区， 则此命令COPY my_sales TO stdout返回错误。 此命令将数据发送到stdout：

BINARY关键字使所有数据以二进制格式而不是文本形式存储/读取。 它比普通的文本模式要快一些，但是二进制格式的文件在计算机体系结构和Greenplum数据库版本之间的移植性较差。 另外，如果数据为二进制格式，则不能以单行错误隔离模式运行COPY FROM。

您必须对通过COPY TO读取的表具有SELECT特权， 并且对于通过COPY FROM将值插入其中的表具有插入特权。 在命令中列出的列上具有列特权就足够了。

COPY命令中命名的文件由数据库服务器而不是客户端应用程序直接读取或写入。 因此，它们必须位于Greenplum数据库master主机上（而不是客户端）或可被其访问。 Greenplum数据库系统用户（服务器运行时使用的用户ID），而不是客户端，必须可以访问它们并对其进行读写。 COPY命名文件只允许数据库超级用户使用，因为它允许读取或写入服务器有权访问的任何文件。

COPY FROM将调用任何触发器并检查目标表上的约束。 但是，它不会调用重写规则。 请注意，在此版本中，不针对单行错误隔离模式评估违反约束的情况。

COPY输入和输出受DateStyle影响。 为了确保可移植到可能使用非默认DateStyle设置的其他Greenplum数据库安装， 在使用COPY TO之前，应将DateStyle设置为ISO。 避免转储IntervalStyle设置为sql_standard的数据也是一个好主意， 因为对于IntervalStyle不同设置的服务器可能会误解负间隔值。

输入数据将根据ENCODING选项或当前客户端编码进行解释， 而输出数据将以ENCODING或当前客户端编码进行编码， 即使数据没有通过客户端，而是由服务器直接从文件读取或写入文件 。

在文本模式下从文件复制XML数据时，服务器配置参数xmloption会影响对复制的XML数据的验证。 如果该值为content（默认值），则将XML数据验证为XML内容片段。 如果参数值为document，则将XML数据验证为XML文档。 如果XML数据无效，则COPY返回错误。

默认情况下，COPY在第一个错误时停止操作。 如果是COPY TO，这应该不会导致问题，但是目标表已经在COPY FROM中接收到了较早的行。 这些行将不可见或不可访问，但仍会占用磁盘空间。 如果故障在大型COPY FROM操作中发生，则可能会浪费大量磁盘空间。 您可能希望调用VACUUM来恢复浪费的空间。 另一种选择是使用单行错误隔离模式来过滤掉错误行，同时仍然加载正确的行。

运行COPY FROM...ON SEGMENT命令时， 服务器配置参数gp_enable_segment_copy_checking控制在将数据复制到表中时是否检查表分发策略（来自表DISTRIBUTED子句）。 默认设置为检查分发策略。 如果数据行违反了segment实例的分配策略，则返回错误。 有关该参数的信息，请参阅服务器配置参数。

COPY TO...ON SEGMENT命令生成的表数据可用于通过COPY FROM...ON SEGMENT恢复表数据。 但是，使用COPY TO命令生成文件时，将根据表分发策略来分发恢复到segment的数据。 如果尝试还原表数据并且在运行COPY FROM...ON SEGMENT之后更改了表分发策略，则COPY命令可能会返回表分发策略错误。

当您指定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。

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

当不是超级用户的Greenplum数据库用户运行COPY命令时，该命令可以由资源队列控制。 资源队列必须配置有ACTIVE_STATEMENTS参数，该参数指定分配给该队列的角色可以执行的查询数量的最大限制。 Greenplum数据库不会将成本值或内存值应用于COPY命令，仅具有成本或内存限制的资源队列不会影响COPY命令的运行。

有关资源队列的信息，请参阅Greenplum数据库管理员指南中的“使用资源队列进行资源管理”。

文件格式

COPY支持的文件格式。

percentage sign = % | vertical bar = *| | backslash = \
                

示例

使用竖线（|）作为字段定界符，将表复制到客户端：

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;

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

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

<SEGID>是必需的。 但是，您可以用绝对路径替换路径中的<SEG_DATA_DIR>字符串文字。

将字符串文字<SEG_DATA_DIR>和<SEGID>传递给COPY时， 运行操作时COPY将填充适当的值。

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

第一列中的内容ID是插入文件路径中的标识符（例如，上面的gpsegdir0/gpbackup0.txt）。 文件是在segment上而不是在master上创建的，就像在标准COPY操作中那样。 使用ON SEGMENT复制时，不会为mirror创建任何数据文件。

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

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

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

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

兼容性

SQL标准中没有COPY语句。

COPY table_name [(column_name [, ...])] FROM
                    {'filename' | PROGRAM 'command' | STDIN}
    [ [WITH]
      [ON SEGMENT]
      [BINARY]
      [OIDS]
      [HEADER]
      [DELIMITER [ AS ] 'delimiter_character']
      [NULL [ AS ] 'null string']
      [ESCAPE [ AS ] 'escape' | 'OFF']
      [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
      [CSV [QUOTE [ AS ] 'quote']
           [FORCE NOT NULL column_name [, ...]]
      [FILL MISSING FIELDS]
      [[LOG ERRORS]
      SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]

COPY { table_name [(column_name [, ...])] | (query)} TO {'filename' | PROGRAM 'command' | STDOUT}
      [ [WITH]
        [ON SEGMENT]
        [BINARY]
        [OIDS]
        [HEADER]
        [DELIMITER [ AS ] 'delimiter_character']
        [NULL [ AS ] 'null string']
        [ESCAPE [ AS ] 'escape' | 'OFF']
        [CSV [QUOTE [ AS ] 'quote']
             [FORCE QUOTE column_name [, ...]] | * ]
      [IGNORE EXTERNAL PARTITIONS ]

请注意，在此语法中，BINARY和CSV被视为独立的关键字，而不是FORMAT选项的参数。

另见

CREATE EXTERNAL TABLE