跳转至

使用COPY加载数据

基于URI的COPY加载

SeaboxMPP提供了基于uri的copy加载命令，访问已经定义好的数据源服务(Foreign server)。

用法

`COPY` *table_name* [( *column_name* [,...])] [`PROGRAM`] `FROM` '*uri*|*command*' ( *option* [, ...]) [`WHERE` *condition*]
其中option可以是下列之一： 

 FORMAT format_name   
 DELIMITER 'delimiter_character'  
 NULL 'null_string'   
 HEADER [ boolean ]  
 QUOTE 'quote_character'   
 ESCAPE 'escape_character'  
 FORCE_NOT_NULL ( column_name [, ...] )    
 FORCE_NULL ( column_name [, ...] )  
 ENCODING 'encoding_name'  
 FILL_MISSING_FIELDS 'boolean'  
 NEWLINE 'LF'|'CR'|'CRLF'  
 EXECUTE_ON 'ALL SEGMENTS'|'PER HOST'|'MASTER'|'host *hostname*' | 'executor *executor id*' | 'total executor *executor number*'  
 LOG_ERRORS 'boolean'  
 REJECT_LIMIT 'int'  
 REJECT_LIMIT_TYPE 'percent'|'rows'   
 TRUNCATE 'boolean'
参数说明
table_name
一个已经创建好的表的名称（可以是模式限定的）。
column_name
可选的要被复制的列列表。如果没有指定列列表，则该表的所有列除了生成的列都会被复制。
PROGRAM
指定命令执行模式。
uri
统一资源标志符，见下面的详细说明。
command
搭配PROGRAM参数使用，指定要执行的命令。
FORMAT
选择要读取或者写入的数据格式： text、 csv（逗号分隔值）或者binary。 默认是text。
DELIMITER
指定分隔文件每行中各列的字符。文本格式中默认是一个制表符，而CSV格式中默认是一个逗号。这必须是一个单一的单字节字符。使用binary格式时不允许这个选项。
NULL
指定表示一个空值的字符串。文本格式中默认是 \N（反斜线-N），CSV格式中默认 是一个未加引用的空串。在你不想区分空值和空串的情况下，即使在文本 格式中你也可能更喜欢空串。使用binary格式时不允许这 个选项。
HEADER
指定文件包含标题行，其中有每一列的名称。在输出时，第一行包含来自表的列名。在输入时，第一行会被忽略。只有使用CSV格式时才允许这个选项。
QUOTE
指定一个数据值被引用时使用的引用字符。默认是双引号。 这必须是一个单一的单字节字符。只有使用 CSV格式时才允许这个选项。
ESCAPE
指定应该出现在一个匹配QUOTE值的数据字符之前 的字符。默认和QUOTE值一样（这样如果引用字符 出现在数据中，它会被双写）。这必须是一个单一的单字节字符。 只有使用CSV格式时才允许这个选项。
FORCE_NOT_NULL
不要把指定列的值与空值串匹配。在空值串就是空串的默认情况下， 这意味着空串将被读作长度为零的字符串而不是空值（即使它们没有 被引用）。只有在COPY FROM中使用 CSV格式时才允许这个选项。
FORCE_NULL
将指定列的值与空值串匹配（即使它已经被加上引号），并且在找到 匹配时将该值设置为NULL。在空值串就是空串的默认 情况下，这会把一个被引用的空串转换为 NULL。 只有在COPY FROM中使用 CSV格式时才允许这个选项。
ENCODING
指定文件被以encoding_name编码。如果省略 这个选项，将使用当前的客户端编码。
WHERE
WHERE子句是可选的，其一般形式是：WHERE condition。其中condition是计算结果为boolean类型的任意表达式。任何不满足此条件的行都不会插入到表中。在用实际的行值替换任何变量引用时，如果该行返回true，则该行满足条件。 目前，在WHERE表达式中不允许使用子查询，并且值的计算不会看到COPY本身所做的任何更改（当表达式包含对VOLATILE函数的调用时，这一点很重要）。
FILL_MISSING_FIELDS
在可读外部表的 TEXT和CSV模式下， 指定FILL_MISSING_FIELDS为true时，当一行数据在行或行的末尾缺少数据字段时，将丢失尾字段值设置为 NULL （而不是报告错误）。空行，具有NOT NULL约束的字段和行上的尾随分隔符仍然会报告错误。
NEWLINE 'LF'|'CR'|'CRLF'
指定数据文件中使用的换行符– LF (换行符, 0x0A)， CR (回车符号, 0x0D), 或 CRLF (回车加换行， 0x0D 0x0A). 如果未指定，SeaboxMPP数据库的executor将通过查看其接收的第一行数据并使用遇到的第一个换行符来检测换行类型。
EXECUTE_ON 'ALL SEGMENTS'|'PER HOST'|'MASTER'|'HOST hostname' | 'SEGMENT executor id' | 'TOTAL executor executor number'
搭配PROGRAM参数使用，ALL SEGMENTS是默认值，表示在所有活动（主）executor上执行。PER HOST表示由每个executor主机上的一个executor执行，而不管每个主机的活动executor实例数如何。HOST hostname表示该命令将由指定executor主机上的所有活动（主）executor实例执行。SEGMENT executor_id表示命令只能由指定的executor执行一次。'TOTAL executor executor number'表示命令将由指定数量的executor执行。SeaboxMPP数据库系统将在运行时随机选择特定的executor。
REJECT_LIMIT 'int'
必须和REJECT_LIMIT_TYPE一起使用，如果REJECT_LIMIT_TYPE指定为rows，则到达REJECT_LIMIT指定的行数后，会拒绝继续加载。如果REJECT_LIMIT_TYPE指定为percent，则每个executor只有在处理了参数sc_reject_percent_threshold所指定的行数之后才开始计算坏行百分比。到达REJECT_LIMIT指定的百分比后会拒绝继续加载。如果没有到达上述限制，所有好的行会被加载，任何错误行会被丢弃。
REJECT_LIMIT_TYPE 'percent'|'rows'
必须和REJECT_LIMIT搭配使用，指定单行错误处理的计算类型。
LOG_ERRORS
可选选项，如果REJECT_LIMIT和REJECT_LIMIT_TYPE指定，指定LOG ERRORS可以记录有关具有格式错误的行的信息。 错误日志信息在内部存储，并使用SeaboxMPP数据库内置SQL函数 sc_read_error_log()访问。具体使用方法参见下文说明。
TRUNCATE
可选选项, 总结以下导入用法，导出不支持该参数, 以下为常见用法: (1) copy table from '/home/seabox/xxx' (truncate 'on'); "on" 清空目标表, "off" 不清空目标表, 默认不清空目标表; (2) copy table from 'fdw://server_name/resource' (truncate 'on'); 参数用法同(1); (3) copy foreign table from '/home/seabox/xxx' (truncate 'on'); "on" 外部表不能清空报错, "off" 不清空目标表, 默认不清空目标表; (4) copy foreign table from 'fdw://server_name/resource' (truncate 'on'); 参数用法同(3)。

除了上述参数之外，对于'fdw://pxf_server_name/…'这类uri还有专属的参数说明，参见pxf说明。

URI说明

URI格式说明'fdw://servername/resouce'

SeaboxMPP提供一种URI协议格式，即fdw://，后面的servername表示提供的加载数据源的外部服务器名称，最后的resource表示服务上的资源名称。 不同的资源，资源名称的表示方式会有不同。 根据上一节数据存储服务中的描述来创建外部服务器。具体用法参见CREATE USER MAPPING和CREATE SERVER的SQL说明。

除上述格式外，如果加载数据量很小，还可以使用STDIN作为URI，或者使用/copy这个客户端命令。 SeaboxMPP数据库内置了以下几个外部服务器，这些外部服务器可以被所有用户直接使用而不需要指定user mapping。

file
ftp
http
scfs
scfss
数据库选项说明
sc_reject_percent_threshold
int类型，默认值是300。如果REJECT_LIMIT_TYPE指定为'percent'，则每个executor在读取完sc_reject_percent_threshold行后，开始计算错误行数所占的百分比。
ve_copy_strict_mode
bool类型，默认值是true。如果数据中没有换行，且目标表为列存表，可以打开VE引擎，开启VE并行，并关闭此选项以加快加载速度。

连接数据库后，通过set命令设置参数值：

set ve_enable = on;
set ve_parallel_degree = 4;
set ve_copy_strict_mode = off;
单行错误处理使用说明

指定LOG_ERRORS选项为true时，SeaboxMPP数据库会捕获读取外部表数据时发生的错误。用户可以查看和管理捕获的错误日志数据。

  • 使用内置的SQL函数 sc_read_error_log('table_name')。它需要对table_name具有 SELECT特权。此示例显示使用COPY命令加载到表 expenses中的数据的错误日志信息：

        SELECT * from sc_read_error_log('expenses');
    如果 table_name不存在，该函数返回FALSE。

    错误信息的格式如下表。

    列 类型 描述
    cmdtime timestamptz 错误发生时的时间戳。
    relname text COPY命令的外部表名称或者目标表名称。
    filename text 包含该错误的导入文件的名称。
    linenum int 如果使用的是COPY，这里是错误发生在导入文件的行号。对于使用file://协议或者scfs://协议以及CSV格式的外部表，文件名和行号会被记录。
    bytenum int 对于使用scfs://协议以及TEXT格式数据的外部表：错误发生在导入文件中的字节偏移。scfs按块解析TEXT文件，因此不可能记录行号。CSV文件是一次解析一行，因此对于CSV文件可以跟踪行号。
    errmsg text 错误消息文本。
    rawdata text 被拒绝行的裸数据。
    rawbytes bytea 在有数据库编码错误（使用的客户端编码不能被转换成一种服务器端编码）的情况中，不可能把编码错误记录为rawdata。相反会存储裸字节，并且用户将看到任何非七位ASCII字符的十进制码。

  • 如果指定的表存在错误日志数据，新的错误日志数据将附加到现有的错误日志数据。 错误日志信息不会复制到镜像executor。

  • 使用内置的SQL函数 sc_truncate_error_log('table_name') 删除 table_name的错误日志数据。它需要表所有者权限,此示例删除将数据移动到表中时捕获的错误日志信息expenses:

SELECT sc_truncate_error_log('expenses');

如果 table_name不存在，该函数返回 FALSE。

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

使用COPY导入示例
  1. 使用SeaboxMPP文件服务scfs

创建名为scfs0的外部服务器，这个外部服务器使用名为scfs_fdw的外部数据包装器，这个scfs服务运行在localhost的8081端口上。为当前用户，这个服务器创建一个用户映射。使用这个外部服务器加载scfs提供的文件，格式是text，分隔符是','，用' '表示空值。

create server scfs0 foreign data wrapper scfs_fdw options (host 'localhost', port '8081');
create user mapping for current_user server scfs0;
copy expenses from 'fdw://scfs0/*' (format 'text, delimiter ',', NULL ' ');
  1. 使用Hadoop文件系统pxf

创建名为hdfs0的外部服务器，这个外部服务器使用名为hdfs_pxf_fdw的外部数据包装器。为所有用户，这个服务器创建一个用户映射。使用这个外部服务器加载hdfs下path路径的文件，格式是text，分隔符是','。

=### create server hdfs0 foreign data wrapper hdfs_pxf_fdw;
=### create user mapping for public server hdfs0;
=### copy expenses from 'fdw://hdfs0/path' (format 'text, delimiter ',');
Scfs单行错误处理

单行错误处理为行模式，最多允许有5行错误，记录错误到文件。

copy expenses from 'fdw://scfs0/*.txt' (format 'text, delimiter '|', null ' ', reject_limit '5', REJECT_LIMIT_TYPE 'rows', log_errors 'true');
使用file类型fdw加载csv格式包含标题行

加载filehost上的/data/international/下的所有文件，格式是csv，有标题行。

create server file0 foreign data wrapper file_pxf_fdw options (host 'filehost');
create user mapping for public server file0;
copy expenses from 'fdw://file0/data/international/*' (FORMAT 'CSV' ,HEADER);
使用可执行文件加载text格式

每个host上有一个executor运行/var/load_scripts/get_log_data.sh。

copy log_output program from '/var/load_scripts/get_log_data.sh' (format 'text', delimiter '|', execute_on 'PER HOST');
使用http数据源

加载http://127.0.0.1:12345/test.txt文件

create server http0 foreign data wrapper http_fdw options (host '127.0.0.1', port '12345');
create user mapping for public server http0;
copy expenses from 'fdw://http0/test.txt' (FORMAT 'text');
使用ftp数据源

加载ftp://127.0.0.1:12345/test.txt文件

create server ftp0 foreign data wrapper ftp_fdw options (host '127.0.0.1', port '12345');
create user mapping for public server ftp0;
copy expenses from 'fdw://ftp0/test.txt' (FORMAT 'text');
最优化配置

  • 不推荐使用STDIN或客户端的\copy指令进行大数据量加载，因为这两种指令只在一个coordinator上进行加载，无法充分利用集群性能。

  • SeaboxMPP数据库不支持https或sftp协议，如果需要使用SSL加载，请使用scfss。

  • server, user mapping, copy均可以定义option, 按照从server到copy的顺序，底层option会被上层option覆盖, 灵活利用这个特性优化加载流程。