人大金仓分析型数据库COPY装载数据

目录

文件装载

STDIN装载

\copy装载数据

输入格式

错误隔离

优化性能

---

        COPY FROM将文件或标准输入中的数据复制到表中，并将数据附加到表内容中。 COPY是非并行的：使用master实例在单个进程中加载数据。建议仅对非常小的数据文件使用COPY。主机上的后端进程必须可以访问COPY 源文件。指定相对于主机上的数据目录的COPY 源文件名，或指定绝对路径。客户端和master服务器之间的连接从STDIN或STDOUT复制数据。

文件装载

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

COPY table_name FROM /path/to/filename;

STDIN装载

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

COPY table_name FROM STDIN;

\copy装载数据

        不要将\copy命令与COPY SQL命令混淆。 \copy调用常规COPY FROM STDIN并将数据从客户端发送到后端。 因此，任何文件都必须驻留在运行客户端的主机上，并且必须可由运行客户端的用户访问。 为避免在加载数据之前将数据文件复制到master主机的问题，COPY FROM STDIN使用标准输入通道并将数据直接提供给数据库后端。 COPY FROM STDIN命令启动后，后端将接受数据行，直到一行只包含反斜杠句点（\.）。

\copy table_name FROM filename;

输入格式

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

COPY table_name FROM /path/to/filename WITH (FORMAT csv);

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

COPY table_name FROM /path/to/filename WITH (FORMAT text, DELIMITER '|');

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

COPY table_name FROM /path/to/filename WITH (ENCODING 'latin1');

错误隔离

        默认情况下，COPY会在第一个错误处停止操作：如果数据包含一个错误，该操作失败并且没有数据被装载。如果用户在单行错误隔离模式中运行COPY FROM，数据库会跳过包含格式错误的行并且装载正确格式化的行。单行错误隔离模式只适用于包含格式错误的输入文件中的行。如果数据包含的是约束错误，例如违背NOT NULL，CHECK，或者 UNIQUE约束，操作还是会失败并且不会有数据被装载。指定SEGMENT REJECT LIMIT 会把COPY操作运行在单行错误隔离模式中。指定每一个实例上可接受的错误行数，在达到这个行数后整个COPY FROM操作失败并且不会有数据被装载。这个错误行计数是针对每个数据库的实例，而不是针对整个装载操作。

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

=> COPY country FROM '/data/gpdb/country_data'
WITH DELIMITER '|' LOG ERRORS
SEGMENT REJECT LIMIT 10 ROWS;

优化性能

        使用下列技巧优化数据装载和后续的查询性能。

• 在装载数据到现有表中之前删掉索引。在现有数据上创建一个数据比随着每个行被装载而增量更新索引更快。用户可以临时增加maintenance_work_mem服务器配置参数来帮助加速CREATE INDEX命令，不过装载性能会被影响。只有在系统中没有活动用户时才删除并重建索引
• 在装载数据到新表中时，最后创建索引。创建表，装载数据，然后才创建任何需要的索引
• 在装载数据后运行ANALYZE。如果一个表中的数据受到了显著的更改，运行ANALYZE或者VACUUM ANALYZE来为查询优化器更新表统计信息。当前的统计信息能确保优化器在查询规划时做出最好的决定并且避免由于不准确或者不存在的统计信息造成的糟糕性能
• 在装载错误后运行 VACUUM 。如果装载错误没有运行在单行错误隔离模式中，操作会在第一个错误处停止。目标表会包含在错误发生前已经装载的行。用户无法访问这些行，但是它们仍占据磁盘空间。使用VACUUM 命令来恢复被浪费的空间