原 GreenPlum中的copy命令

COPY和“\copy”的区别

psql支持文件数据导入到数据库，也支持数据库表数据导出到文件中。 COPY命令和\copy 命令都支持这两类操作，但两者有如下区别：

• COPY 命令是SQL命令，\copy 命令是元命令。
• 执行COPY 命令的用户必须具有SUPERUSER超级权限（将数据通过stdin/stdout方式导入导出情况除外）或授予了默认角色pg_read_server_files、pg_write_server_files及pg_execute_server_program之一的用户，而 \copy 元命令不需要SUPERUSER权限。
• COPY 命令读取或写入数据库服务端主机上的文件，而 \copy 元命令是从psql客户端主机读取或写入文件。
• 从性能方面看， 大数据量导出文件或大文件数据导入数据库，COPY 比 \copy 性能高。

用COPY装载数据

COPY FROM将文件或标准输入中的数据复制到表中，并将数据附加到表内容中。 COPY是非并行的：使用Greenplum master实例在单个进程中加载数据。建议仅对非常小的数据文件使用COPY。

Master主机上的postgres进程必须可以访问COPY 源文件。指定相对于Master主机上的数据目录的COPY 源文件名，或指定绝对路径。

Greenplum使用客户端和master服务器之间的连接从STDIN或STDOUT复制数据。

从文件装载

COPY 命令要求postgres后端打开指定的文件，读取文件并将其附加到表中。为了能够读取文件，后端需要具有对文件的可读权限，并且必须使用master主机上的绝对路径或master数据目录的相对路径来指定文件名。

从STDIN装载

为避免在加载数据之前将数据文件复制到master主机的问题，COPY FROM STDIN使用标准输入通道并将数据直接提供给postgres后端。 COPY FROM STDIN命令启动后，后端将接受数据行，直到一行只包含反斜杠句点（.）。

在psql中使用\copy装载数据

不要将psql \copy命令与COPY SQL命令混淆。 \copy调用常规COPY FROM STDIN并将数据从psql客户端发送到后端。 因此，任何文件都必须驻留在运行psql客户端的主机上，并且必须可由运行客户端的用户访问。

为避免在加载数据之前将数据文件复制到master主机的问题，COPY FROM STDIN使用标准输入通道并将数据直接提供给postgres后端。 COPY FROM STDIN命令启动后，后端将接受数据行，直到一行只包含反斜杠句点（.）。psql将所有这些包装到handy \copy 命令中

输入格式

COPY FROM接受FORMAT参数，该参数指定输入数据的格式。可能的值为TEXT，CSV（逗号分隔值）和BINARY。

FORMAT csv将读取逗号分隔值。默认情况下，FORMAT text使用制表符来分隔值，DELIMITER选项将不同的字符指定为值分隔符。

默认情况下，使用默认客户端编码，可以使用ENCODING选项更改此编码。这对于来自其他操作系统的数据会非常有用。

使用COPY卸载数据

COPY TO在Greenplum的Master主机上用一个Master实例的单进程从一个表复制数据到一个文件（或者标准输入）。使用COPY输出一个表的全部内容，或者使用一个SELECT语句过滤输出。例如：

在单行错误隔离模式中运行COPY

默认情况下，COPY会在第一个错误处停止操作：如果数据包含一个错误，该操作失败并且没有数据被装载。如果用户在单行错误隔离模式中运行COPY FROM，Greenplum会跳过包含格式错误的行并且装载正确格式化的行。单行错误隔离模式只适用于包含格式错误的输入文件中的行。如果数据包含的是约束错误，例如违背NOT NULL，CHECK，或者 UNIQUE约束，操作还是会失败并且不会有数据被装载。

指定SEGMENT REJECT LIMIT 会把COPY操作运行在单行错误隔离模式中。指定每一个Segment上可接受的错误行数，在达到这个行数后整个COPY FROM操作失败并且不会有数据被装载。这个错误行计数是针对每个Greenplum数据库的Segment，而不是针对整个装载操作。

如果COPY操作没有达到操作限制，Greenplum会装载所有正确格式化的行并且丢弃错误行。使用LOG ERRORS子句可以捕获Greenplum数据库内部的数据格式化错误。例如：

有关调查错误行的信息，请见在错误日志中查看不正确的行。

在错误日志中查看不正确的行

如果用户使用了单行错误隔离（见定义一个带有单行错误隔离的外部表或者在单行错误隔离模式中运行COPY），任何由格式错误的行会被Greenplum数据库内部记录下来。

Greenplum数据库用一种表格式捕捉下列错误信息：

用户可以使用Greenplum数据库的内建SQL函数gp_read_error_log()来显示内部记录的格式化错误。例如，这个命令显示表ext_expenses的错误日志信息：

COPY命令详解

在文件和表之间复制数据。

概要

其中option可以是以下之一：

描述

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参数采用字符串文字（必需），并使用绝对路径或字符串文字。 运行COPY操作时，segment ID和segment数据目录的路径将替换为字符串文字值。

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

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

Warning: 建议仅对专业用户使用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是复制的行数：

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

参数

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实例执行。 在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。

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

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

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

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

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

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

对于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查询以从作为外部表的叶子分区中复制数据的信息，请参见下一节“注释”。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。