COPY
功能描述
通过COPY命令实现在表和文件之间拷贝数据。COPY FROM从一个文件拷贝数据到一个表,COPY TO把一个表的数据拷贝到一个文件。
COPY FROM/TO适用于低并发,本地小数据量导入导出的场景。

- 由于云上DWS是全托管服务,用户无法登录后台集群节点,所以云上禁止使用COPY语法,可采用“\COPY”命令进行操作(仅gsql客户端支持,其他客户端包括SQL编辑器均不支持“\COPY”命令)。
- 云上推荐将数据文件放到OBS上,使用OBS外表进行入库。
注意事项
- 以安全模式(云上安全模式不支持关闭)启动CN、DN的开关,那么当前模式下禁止使用COPY FROM FILENAME或COPY TO FILENAME语法,可采用\copy的方式进行规避,请参考如何使用\copy导入导出中的示例。
- COPY只能用于表,不能用于视图。
- 对任何要插入数据的表必须有插入权限。
- 如果声明了一个字段列表,COPY将只在文件和表之间拷贝已声明字段的数据。如果表中有任何不在字段列表里的字段,COPY FROM将为这些字段插入缺省值。
- 如果声明了数据源文件,服务器必须可以访问该文件;如果指定了STDIN,数据将在客户前端和服务器之间流动,输入时,表的列与列之间使用TAB键分隔,在新的一行中以反斜杠和句点(\.)表示输入结束。
- 如果数据文件的任意行包含比预期多或者少的字段,COPY FROM将抛出一个错误。
- 数据的结束可以用一个只包含反斜杠和句点(\.)的行表示。如果从文件中读取数据,数据结束的标记是不必要的;如果在客户端应用之间拷贝数据,必须要有结束标记。
- COPY FROM中\N为空字符串,如果要输入实际数据值\N ,使用\\N。
- COPY FROM不支持在导入过程中对数据做预处理(比如说表达式运算,填充指定默认值等)。如果需要在导入过程中对数据做预处理,用户需先把数据导入到临时表中,然后执行SQL语句通过运算插入到表中,但此方法会导致I/O膨胀,降低导入性能。
- COPY FROM在遇到数据格式错误时会回滚事务,但没有足够的错误信息,不方便用户从大量的原始数据中定位错误数据。
- 支持使用COPY TO向OBS导出TEXT或CSV格式数据,但不支持使用COPY FROM从OBS导入数据。对于导出到OBS上的带utf8 BOM的CSV格式文件,如果有导入需求,建议使用OBS导入,否则可能无法正确识别BOM字段。
语法格式
- 从一个文件拷贝数据到一个表:
1 2 3 4 5 6 7 8 9 10
COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ USING ] DELIMITERS 'delimiters' ] [ WITHOUT ESCAPING ] [ LOG ERRORS ] [ LOG ERRORS data ] [ REJECT LIMIT 'limit' ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ];
语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ ( option [, ...] ) | copy_option [ ...] ] 可以任意排列组合。
- 把一个表的数据拷贝到一个文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
COPY table_name [ ( column_name [, ...] ) ] TO { 'filename' | STDOUT } [ [ USING ] DELIMITERS 'delimiters' ] [ WITHOUT ESCAPING ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ]; COPY query TO { 'filename' | STDOUT } [ WITHOUT ESCAPING ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ];
- COPY TO语法形式约束如下:
(query)与[USING] DELIMITER不兼容,即若COPY TO的数据来自于一个query的查询结果,那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。
- 对于FIXED FORMATTER语法后面跟随的copy_option是以空格进行分隔的。
- copy_option是指COPY原生的参数形式,而option是兼容外表导入的参数形式。
- 语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ ( option [, ...] ) | copy_option [ ...] ] 可以任意排列组合。
- COPY TO语法形式约束如下:
参数说明
参数 |
描述 |
取值范围 |
---|---|---|
query |
将查询结果进行拷贝。 |
一个必须用圆括弧包围的SELECT或VALUES命令。 |
table_name |
表的名字(可以有模式修饰)。 |
已存在的表名。 |
column_name |
可选的待拷贝字段列表。 |
如果没有声明字段列表,将使用所有字段。 |
STDIN |
声明输入是来自标准输入。 |
- |
STDOUT |
声明输出打印到标准输出。 |
- |
FIXED |
打开字段固定长度模式。
|
|
[USING] DELIMITER 'delimiters' |
在文件中分隔各个字段的字符串,分隔符最大长度不超过10个字节。 |
不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。 在文本模式下,缺省是水平制表符,在CSV模式下是一个逗号。 |
WITHOUT ESCAPING |
在TEXT格式中,不对'\'和后面的字符进行转义。 |
仅支持TEXT格式。 |
LOG ERRORS |
若指定该参数,则开启对于COPY FROM语句中数据类型错误的容错机制,相关错误行的错误记录会记录到此库中的public.pgxc_copy_error_log表中,备后续查阅。
须知:
此容错选项的使用限制如下:
|
仅支持导入(即COPY FROM)时指定。 |
LOG ERRORS DATA |
LOG ERRORS DATA和LOG ERRORS的区别:
|
- |
REJECT LIMIT 'limit' |
与LOG ERROR选项共同使用,对COPY FROM的容错机制设置数值上限,一旦此COPY FROM语句错误数据超过选项指定条数,则会按照原有机制报错。
说明:
LOG ERRORS中描述的容错机制,REJECT LIMIT的计数也是按照执行COPY FROM的CN上遇到的解析错误数量计算,而不是每个DN上的错误数量,请与GDS容错机制区别开。 |
正整数(1-INTMAX),'unlimited'(无最大值限制) 缺省值:
|
FORMATTER |
在固定长度模式中(即声明了FIXED参数),定义每一个字段在数据文件中的位置。按照column(offset,length)格式定义每一列在数据文件中的位置。 |
|
OPTION { option_name ' value ' } |
用于指定兼容外表的各类参数。 |
具体请参见表2。 |
COPY_OPTION { option_name ' value ' } |
用于指定COPY原生的各类参数。 |
具体请参见表3。 |
参数 |
描述 |
取值范围 |
||
---|---|---|---|---|
FORMAT |
数据源文件的格式。 |
支持CSV、TEXT、FIXED、BINARY类型的文件。
缺省值:TEXT |
||
OIDS |
为每行拷贝内部对象标识(oid)。
说明:
若COPY FROM对象为query或者对于没有oid的表,指定oids标识报错。 |
缺省值:false |
||
DELIMITER |
指定数据文件行数据的字段分隔符。
|
支持多字符分隔符,但分隔符不能超过10个字节。 缺省值:
|
||
NULL |
用来指定数据文件中空值的表示方式。 |
缺省值:
|
||
HEADER |
指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。
|
缺省值:false |
||
QUOTE |
CSV格式文件下,指定一个数据值被引用时使用的引用字符。 |
缺省值:双引号
|
||
ESCAPE |
CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。 |
单字节字符。 缺省值:双引号 |
||
EOL 'newline_character' |
指定导入导出数据文件换行符样式。 |
支持多字符换行符,但换行符不能超过10个字节。 常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。
|
||
FORCE_QUOTE { ( column_name [, ...] ) | * } |
在CSV COPY TO模式下,强制对每个声明的字段的所有非NULL值都使用引号包围。NULL输出不会被引号包围。 |
已存在的字段。 |
||
FORCE_NOT_NULL ( column_name [, ...] ) |
在CSV COPY FROM模式下,指定的字段输入不能为空。 |
已存在的字段。 |
||
ENCODING |
指定数据文件的编码格式名称。 |
缺省为当前数据库编码格式。 常用的编码格式有UTF8、GBK、GB18030,GB18030有两个版本,GB18030和GB18030_2022。GB18030_2022,用于支持汉字最新的国家标准GB 18030-2022。 |
||
IGNORE_EXTRA_DATA |
若数据源文件比外表定义列数多,是否会忽略对多出的列。该参数只在数据导入过程中使用。 |
缺省值:false/off
须知:
如果行尾换行符丢失,使两行变成一行时,设置此参数为true将导致后一行数据被忽略掉。 |
||
COMPATIBLE_ILLEGAL_CHARS |
导入非法字符容错参数,仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 导入非法字符容错规则如下:
|
缺省值:false/off |
||
FILL_MISSING_FIELDS |
当数据加载时,若数据源文件中一行的最后一个字段缺失的处理方式。 |
缺省值:false/off |
||
DATE_FORMAT |
导入对于DATE类型指定格式。仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法DATE格式。可参考时间、日期处理函数和操作符。
须知:
对于指定为ORACLE兼容类型的数据库(建库时指定DBCOMPATIBILITY取值为“ORA”),则DATE类型内建为TIMESTAMP类型。在导入的时候,若需指定格式,可以参考下面的timestamp_format参数。 |
||
TIME_FORMAT |
导入对于TIME类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法TIME格式,不支持时区。可参考时间、日期处理函数和操作符。 |
||
TIMESTAMP_FORMAT |
导入对于TIMESTAMP类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法TIMESTAMP格式,不支持时区。可参考时间、日期处理函数和操作符。 |
||
SMALLDATETIME_FORMAT |
导入对于SMALLDATETIME类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符。 |
||
SERVER |
指定OBS SERVER,仅在COPY TO时生效。指定该参数时filename为OBS上的路径,表示导出到OBS。 |
已创建的OBS SERVER名。 |
||
BOM |
标识是否对导出的CSV格式文件添加utf8 BOM字段。仅在COPY TO并且指定了有效SERVER参数时生效,仅支持导出文件为utf8编码。 |
缺省值:false |
||
MAXROW |
标识导出文件的最大行数,超出该值会生成新的文件。仅在COPY TO并且指定了有效SERVER参数时生效。当HEADER取值为true时MAXROW需要大于1。该参数需要与FILEPREFIX同时指定。 |
1~ 2147483647。 |
||
FILEPREFIX |
指定导出文件名的前缀。仅在COPY TO并且指定了有效SERVER参数时生效。该参数需要与MAXROW同时指定。 |
合法的字符串,且不能以/开始或结尾。 |
||
PRESERVE_BLANKS |
用于定长导入时,控制每列数据拖尾的空白字符(包括空格,\t,\v,\f)是否保留。该参数仅8.2.0.100及以上集群版本支持。 |
缺省值为false/off。 |
参数 |
描述 |
取值范围 |
||
---|---|---|---|---|
OIDS |
为每行拷贝内部对象标识(oid)。 若COPY FROM对象为query或者对于没有oid的表,指定oids标识报错。 |
- |
||
NULL null_string |
用来指定数据文件中空值的表示。
须知:
在使用COPY FROM的时候,任何匹配这个字符串的字符串将被存储为NULL值,所以应该确保指定的字符串和COPY TO相同。 |
缺省值:
|
||
HEADER |
指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。 |
|
||
FILEHEADER |
导出数据时用于定义标题行的文件,一般用来描述每一列的数据信息。
须知:
|
- |
||
FREEZE |
将COPY加载的数据行设置为已经被frozen,就等同于这些数据行执行过VACUUM FREEZE。 该参数为一个初始数据加载的性能选项。仅当以下三个条件同时满足时,数据行才会被frozen:
须知:
COPY完成后,所有其他会话将会立刻识别到这些数据。但这违反了MVCC可见性的一般原则,会导致潜在的风险。 |
- |
||
FORCE NOT NULL column_name [, ...] |
在CSV COPY FROM模式下,指定的字段输入不能为空。 |
已存在的字段。 |
||
FORCE QUOTE { column_name [, ...] | * } |
在CSV COPY TO模式下,强制在每个声明的字段周围对所有非NULL值都使用引号包围。NULL输出不会被引号包围。 |
已存在的字段。 |
||
BINARY |
使用二进制格式存储和读取,而不是以文本的方式。在二进制模式下,不能声明DELIMITER,NULL,CSV选项。指定BINARY类型后,不能再通过option或copy_option指定CSV、FIXED、TEXT等类型。 |
- |
||
CSV |
打开逗号分隔变量(CSV)模式。指定CSV类型后,不能再通过option或copy_option指定BINARY、FIXED、TEXT等类型。 |
- |
||
QUOTE [AS] 'quote_character' |
CSV格式文件下的引号字符。
|
缺省值:双引号。 |
||
ESCAPE [AS] 'escape_character' |
CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。 |
缺省值为双引号。当与quote值相同时,会被替换为'\0'。 |
||
EOL 'newline_character' |
指定导入导出数据文件换行符样式。 |
支持多字符换行符,但换行符不能超过10个字节。 常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。
|
||
ENCODING 'encoding_name' |
指定文件编码格式名称。 |
有效的编码格式。 缺省值:当前编码格式。 |
||
IGNORE_EXTRA_DATA |
指定当数据源文件比外表定义列数多时,忽略行尾多出来的列。该参数只在数据导入过程中使用。
若不使用该参数,在数据源文件比外表定义列数多,会显示如下错误信息。
|
- |
||
COMPATIBLE_ILLEGAL_CHARS |
指定导入时对非法字符进行容错处理,非法字符转换后入库。不报错,不中断导入。该参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。仅对COPY FROM导入有效。 若不使用该参数,导入时遇到非法字符进行报错,中断导入。 导入非法字符容错规则如下:
|
- |
||
FILL_MISSING_FIELDS |
当数据加载时,若数据源文件中一行的最后一个字段缺失的处理方式。
须知:
目前COPY指定此Option实际不会生效,即不会有相应的容错处理效果(不生效)。需要额外注意的是,打开此选项会导致解析器在CN数据解析阶段(即COPY错误表容错的涵盖范围)忽略此数据问题,而到DN重新报错,从而使得COPY错误表(打开LOG ERRORS REJECT LIMIT)在此选项打开的情况下无法成功捕获这类少列的数据异常。因此请不要指定此选项。 |
缺省值:false/off。 |
||
DATE_FORMAT 'date_format_string' |
导入对于DATE类型指定格式。此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。
说明:
对于指定为ORACLE兼容类型的数据库(建库时指定DBCOMPATIBILITY取值为“ORA”),则DATE类型内建为TIMESTAMP类型。在导入的时候,若需指定格式,可以参考下面的timestamp_format参数。 |
合法DATE格式。可参考时间、日期处理函数和操作符。 |
||
TIME_FORMAT 'time_format_string' |
导入对于TIME类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法TIME格式,不支持时区。可参考时间、日期处理函数和操作符。 |
||
TIMESTAMP_FORMAT 'timestamp_format_string' |
导入对于TIMESTAMP类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法TIMESTAMP格式,不支持时区。可参考时间、日期处理函数和操作符。 |
||
SMALLDATETIME_FORMAT 'smalldatetime_format_string' |
导入对于SMALLDATETIME类型指定格式。此参数仅对COPY FROM导入有效。 此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。 |
合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符。 |
||
PRESERVE_BLANKS |
用于定长导入时,控制每列数据拖尾的空白字符(包括空格,\t,\v,\f)是否保留。该参数仅8.2.0.100及以上集群版本支持。 |
缺省值为false/off。 |
COPY FROM能够识别的特殊反斜杠序列如下所示。
反斜杠序列 |
含义 |
---|---|
\b |
反斜杠 (ASCII 8) |
\f |
换页(ASCII 12) |
\n |
换行符 (ASCII 10) |
\r |
回车符 (ASCII 13) |
\t |
水平制表符 (ASCII 9) |
\v |
垂直制表符 (ASCII 11) |
\digits |
反斜杠后面跟着一到三个八进制数,表示ASCII值为该数的字符。 |
\xdigits |
反斜杠x后面跟着一个或两个十六进制位声明指定数值编码的字符。 |
示例(仅gsql客户端支持,其他客户端不支持)
将tpcds.ship_mode中的数据拷贝到/home/omm/ds_ship_mode.dat文件中:
1
|
\COPY tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat'; |
将tpcds.ship_mode中的查询结果拷贝到/home/omm/ds_ship_mode.dat文件中:
1
|
\COPY (SELECT * FROM tpcds.ship_mode WHERE a <> null) TO '/home/omm/ds_ship_mode.dat'; |
将tpcds.ship_mode输出到stdout:
1
|
\COPY tpcds.ship_mode TO stdout; |
创建tpcds.ship_mode_t1表:
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tpcds.ship_mode_t1 ( SM_SHIP_MODE_SK INTEGER NOT NULL, SM_SHIP_MODE_ID CHAR(16) NOT NULL, SM_TYPE CHAR(30) , SM_CODE CHAR(10) , SM_CARRIER CHAR(20) , SM_CONTRACT CHAR(20) ) WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(SM_SHIP_MODE_SK ); |
从stdin拷贝数据到表tpcds.ship_mode_t1:
1
|
\COPY tpcds.ship_mode_t1 FROM stdin; |
从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1:
1
|
\COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat'; |
从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为TEXT(format 'text'),分隔符为'\t'(delimiter E'\t'),忽略多余列(ignore_extra_data 'true'),不指定转义(noescaping 'true'):
1
|
\COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' WITH(format 'text', delimiter E'\t', ignore_extra_data 'true', noescaping 'true'); |
从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为FIXED(FIXED),指定定长格式(FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20))),忽略多余列(ignore_extra_data),有数据头(header):
1
|
\COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' FIXED FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20)) header ignore_extra_data; |
从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为FIXED(FIXED),指定定长格式(FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20))),忽略多余列(ignore_extra_data),有数据头(header),保留末尾\t字符:
1
|
\COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' (FORMAT 'fixed', FORMATTER (SM_SHIP_MODE_SK(0,2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20)), PRESERVE_BLANKS‘true', HEADER 'true', IGNORE_EXTRA_DATA 'true'); |
将tpcds.ship_mode_t1导出为OBS的 '/bucket/path/'路径上的TEXT格式文件ds_ship_mode.dat。需要指定包含OBS访问信息的“server”option参数:
1
|
COPY tpcds.ship_mode_t1 TO '/bucket/path/ds_ship_mode.dat' WITH (format 'text', encoding 'utf8', server 'obs_server'); |
将tpcds.ship_mode_t1导出为OBS的 '/bucket/path/'路径上的CSV格式文件。需要指定包含OBS访问信息的“server”option参数。其中文件包含标题行,包含BOM头,单文件最大行数1000行(超出1000行生成新的文件),自定义文件名前缀为“justprefix”:
1
|
COPY (select * from tpcds.ship_mode_t1 where SM_SHIP_MODE_SK=1060) TO '/bucket/path/' WITH (format 'csv', header 'on', encoding 'utf8', server 'obs_server', bom 'on', maxrow '1000', fileprefix 'justprefix'); |
删除tpcds.ship_mode_t1:
1
|
DROP TABLE tpcds.ship_mode_t1; |