COPY
功能描述
通过COPY命令实现在表和文件之间复制数据。
COPY FROM从一个文件复制数据到一个表，COPY TO把一个表的数据复制到一个文件。
注意事项
- 当参数enable_copy_server_files关闭时，只允许初始用户执行COPY FROM FILENAME或COPY TO FILENAME命令，当参数enable_copy_server_files打开时，允许具有SYSADMIN权限的用户或继承了内置角色gs_role_copy_files权限的用户执行，但默认禁止对数据库配置文件，密钥文件，证书文件和审计日志执行COPY FROM FILENAME或COPY TO FILENAME，以防止用户越权查看或修改敏感文件。同时enable_copy_server_files打开时，管理员可以通过guc参数safe_data_path设置普通用户可以导入导出的路径必须为设置路径的子路径，未设置此guc参数时候（默认情况），不对普通用户使用的路径进行拦截。该参数会对copy使用路径中的...进行报错处理。
- COPY只能用于表，不能用于视图。
- COPY TO需要读取的表的SELECT权限，COPY FROM需要插入的表的INSERT权限。
- 如果声明了一个字段列表，COPY将只在文件和表之间复制已声明字段的数据。如果表中有任何不在字段列表里的字段，COPY FROM将为那些字段插入缺省值。
- 如果声明了数据源文件，服务器必须可以访问该文件；如果指定了STDIN，数据将在客户前端和服务器之间流动，输入时，表的列与列之间使用TAB键分隔，在新的一行中以反斜杠和句点（\.）表示输入结束。
- 如果数据文件的任意行包含比预期多或者少的字段，COPY FROM将抛出一个错误。
- 数据的结束可以用一个只包含反斜杠和句点（\.）的行表示。如果从文件中读取数据，数据结束的标记是不必要的；如果在客户端应用之间复制数据，必须要有结束标记。
- COPY FROM中\N为空字符串，如果要输入实际数据值\N ，使用\\N。
- COPY FROM不支持在导入过程中对数据做预处理（比如说表达式运算，填充指定默认值等）。如果需要在导入过程中对数据做预处理，用户需先把数据导入到临时表中，然后执行SQL语句通过运算插入到表中，但此方法会导致I/O膨胀，降低导入性能。
- COPY FROM在遇到数据格式错误时会回滚事务，但没有足够的错误信息，不方便用户从大量的原始数据中定位错误数据。
- COPY FROM/TO适合低并发，本地小数据量导入导出。
- COPY使用二进制格式时，不支持分布式下转码。
- COPY是服务端命令，执行环境和数据库服务端进程保持一致；\COPY是客户端元命令，执行环境和客户端gsql保持一致。需要注意的是，当在沙箱环境中使用数据库和gsql时，COPY命令和\COPY命令都使用沙箱内的路径；当在沙箱环境中使用数据库，在沙箱外使用gsql时，COPY命令使用沙箱内的路径，\COPY命令则使用沙箱外的路径。
- 在对建有全局二级索引的基表执行COPY数据导入时，需要开启enable_stream_operator参数，以达到最优数据导入性能。
- 在导出float类型的数据时，推荐将extra_float_digits设为3，避免有效位数丢失造成导出导入前后数据不一致。
- M-Compatibility模式数据库下，不建议在lower_case_table_names参数不同的库之间进行COPY FROM/TO操作。
语法格式
- 从一个文件复制数据到一个表。
COPY table_name [ ( column_name [, ...] ) ] [WITH OIDS] FROM { 'filename' | STDIN } [LOAD] [ LOAD_DISCARD 'discard_path'] [LOAD_BAD 'bad_path'] [ [ WITH ] ( option [, ...] ) | copy_option ];
上述语法中copy_option与option语法不兼容。
- 把一个表的数据复制到一个文件。
COPY table_name [ ( column_name [, ...] ) ] [WITH OIDS] TO { 'filename' | STDOUT } [LOAD] [ [ WITH ] ( option [, ...] ) | copy_option ]; COPY query TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) | copy_option ];
copy_option是指COPY原生的参数形式，而option是兼容外表导入的参数形式。
其中可选参数option子句语法为：FORMAT 'format_name' | FORMAT binary | DELIMITER 'delimiter_character' | NULL 'null_string' | QUOTE 'quote_character' | HEADER [ boolean ] | USEEOF [ boolean ] | ESCAPE 'escape_character' | FORCE_NOT_NULL ( column_name [, ...] ) | FORCE_QUOTE { column_name [, ...] | * } | DATE_FORMAT 'date_format_string' | SMALLDATETIME_FORMAT 'smalldatetime_format_string'其中可选参数copy_option子句语法为：NULL [ AS ] 'null_string' | FORCE_NOT_NULL column_name [, ...] | BINARY | CSV | ESCAPE [ AS ] 'escape_character' | ENCODING 'encoding_name' | DELIMITER [ AS ] 'delimiter_character' | ROWS 'string' | DATE_FORMAT 'date_format_string' | SMALLDATETIME_FORMAT 'smalldatetime_format_string' | SKIP int_number | WHEN { ( start - end ) | column_name } { = | != } 'string' | SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } )
参数说明
- query
其结果将被复制。
取值范围：一个必须用圆括弧包围的SELECT或VALUES命令。
- table_name
表的名称（可以有模式修饰）。
取值范围：已存在的表名。
- column_name
可选的待复制字段列表。
取值范围：如果没有声明字段列表，将使用所有字段。
- STDIN
声明输入是来自标准输入。输入时，表的列与列之间使用TAB键分隔，在新的一行中以反斜杠和句点（\.）表示输入结束。
- STDOUT
声明输出打印到标准输出。
- LOAD
区分COPY语法为gs_loader调用还是用户直接调用，非主动调用接口，不推荐使用。
- LOAD_DISCARD 'discard_path'
指定gs_loader客户端discard文件生成路径，非主动调用接口，不推荐使用。
- LOAD_BAD 'bad_path'
指定gs_loader客户端bad文件生成路径，非主动调用接口，不推荐使用。
- OPTION { option_name ' value ' }
用于指定兼容外表的各类参数。
- FORMAT
取值范围：CSV、TEXT、BINARY。
- CSV格式的文件，可以有效处理数据列中的换行符，但对一些特殊字符处理有欠缺。
- TEXT格式的文件，可以有效处理一些特殊字符，但无法正确处理数据列中的换行符。
- BINARY形式的选项会使得所有的数据被存储/读作二进制格式而不是文本。 这比TEXT和CSV格式的要快一些，但是一个BINARY格式文件可移植性比较差。
缺省值：TEXT
- DELIMITER
指定数据文件行数据的字段分隔符。
- 分隔符不能是\r和\n。
- 分隔符不能和null参数相同，CSV格式数据的分隔符不能和quote参数相同。
- TEXT格式数据的分隔符不能包含： 小写字母、数字和特殊字符.\。
- 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
- 分隔符推荐使用多字符和不可见字符。多字符例如'$^&'；不可见字符例如0x07，0x08，0x1b等。
- 如使用TAB制表符进行CSV文件分隔，使用E'\t'。
取值范围：支持多字符分隔符，但分隔符不能超过10个字节。
缺省值：
- TEXT格式的默认分隔符是水平制表符（tab）。
- CSV格式的默认分隔符为“,”。
- NULL
取值范围：
- null值不能是\r和\n，最大为100个字符。
- null值不能和分隔符、quote参数相同。
缺省值：
- CSV格式下默认值是一个没有引号的空字符串。
- 在TEXT格式下默认值是\N。
- HEADER
指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。header只能用于CSV格式的文件中。
在导入数据时，如果header选项为on，则数据文本第一行会被识别为标题行，会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。
在导出数据时，如果header选项为on，则需要指定fileheader。如果header为off，则导出数据文件不包含标题行。
取值范围：true/on，false/off。
缺省值：false
- ESCAPE
CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。
缺省值：''。当与quote值相同时，会被替换为'\0'。
- FORCE_NOT_NULL ( column_name [, ...] )
在CSV COPY FROM模式下，指定的字段输入不能为空。
取值范围：已存在的字段。
- DATE_FORMAT
导入对于DATE类型指定格式。此参数不支持BINARY格式，会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。
取值范围：合法DATE格式。可参考日期时间函数。
对于DATE类型内建为TIMESTAMP类型的数据库，在导入的时候，若需指定格式，可以参考下面的timestamp_format参数。
- FORMAT
- COPY_OPTION { option_name ' value ' }
用于指定COPY原生的各类参数。
- NULL null_string
取值范围：
- null值不能是\r和\n，最大为100个字符。
- null值不能和分隔符、quote参数相同。
缺省值：
- 在TEXT格式下默认值是\N。
- CSV格式下默认值是一个没有引号的空字符串。
- FORCE NOT NULL column_name [, ...]
在CSV COPY FROM模式下，指定的字段不为空。若输入为空，则将视为长度为0的字符串。
取值范围：已存在的字段。
- BINARY
使用二进制格式存储和读取，而不是以文本的方式。
- 在二进制模式下，不能声明DELIMITER、NULL、CSV选项。
- 指定BINARY类型后，不能再通过option或copy_option指定CSV、TEXT等类型。
- 如果设置了GUC参数support_binary_copy_version='header_encoding'，则通过COPY TO导出的二进制文件头中包含数据库的服务端编码信息，COPY FROM导入二进制文件时对编码信息的一致性进行校验，文件中编码信息须与服务端编码保持一致。
- 如果设置了GUC参数copy_special_character_version='no_error'，则会在导入的过程中屏蔽非法字符编码的校验，将非法编码字符按原样导入，查询时以乱码显示。在了解后果的情况下，请谨慎选择开启此参数。可以在COPY语句中使用LOG ERRORS或LOG ERRORS DATA参数，将错误编码数据记录到错误表中，便于记录与查看。
- 二进制模式下copy_special_character_version='no_error'，仅对TEXT、CHAR、VARCHAR、NVARCHAR2类型的字段生效。
- CSV
打开逗号分隔变量（CSV）模式。指定CSV类型后，不能再通过option或copy_option指定BINARY、TEXT等类型。
- ESCAPE [AS] 'escape_character'
CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。
默认值为''。当与quote值相同时，会被替换为'\0'。
- ENCODING 'encoding_name'
取值范围：有效的编码格式。
缺省值：当前编码格式。
- DATE_FORMAT 'date_format_string'
导入对于DATE类型指定格式。此参数不支持BINARY格式，会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。
取值范围：合法DATE格式。可参考日期时间函数。
对于DATE类型内建为TIMESTAMP类型的数据库，在导入的时候，若需指定格式，可以参考下面的timestamp_format参数。
- SKIP int_number
- WHEN { ( start - end ) | column_name } { = | != } 'string'
- SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } )
数据导入时，SEQUENCE修饰的列，不从数据文件读取数据，通过指定的integer，按照incr递增数值；不指定incr则默认从1开始递增。
COPY FROM目前不支持对分布列指定表达式转换。
COPY FROM能够识别的特殊反斜杠序列如下所示。- \b：反斜杠 （ASCII 8）
- \f：换页（ASCII 12）
- \n：换行符 （ASCII 10）
- \r：回车符 （ASCII 13）
- \t：水平制表符 （ASCII 9）
- \v：垂直制表符 （ASCII 11）
- \digits：反斜杠后面跟着一到三个八进制数，表示ASCII值为该数的字符。
- \xdigits：反斜杠x后面跟着一个或两个十六进制位声明指定数值编码的字符。
- NULL null_string
权限控制示例
m_db=# copy t1 from '/home/xy/t1.csv'; ERROR: COPY to or from a file is prohibited for security concerns HINT: Anyone can COPY to stdout or from stdin. gsql's \copy command also works for anyone. m_db=# grant gs_role_copy_files to xxx;
此错误为非初始用户没有使用copy的权限示例，解决方式为打开enable_copy_server_files参数，则管理员可以使用copy功能，普通用户需要在此基础上加入gs_role_copy_files群组。
示例
--创建SCHEMA。 m_db=# CREATE SCHEMA tpcds; --创建tpcds.ship_mode表。 m_db=# CREATE TABLE tpcds.ship_mode ( 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) ) DISTRIBUTE BY HASH(SM_SHIP_MODE_SK); --向tpcds.ship_mode表插入一条数据。 m_db=# INSERT INTO tpcds.ship_mode VALUES (1,'a','b','c','d','e'); --将tpcds.ship_mode中的数据复制到/home/omm/ds_ship_mode.dat文件中。 m_db=# COPY tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat'; --将tpcds.ship_mode 输出到STDOUT。 m_db=# COPY tpcds.ship_mode TO STDOUT; --将tpcds.ship_mode 的数据输出到STDOUT，使用参数如下：分隔符为','(delimiter ',')，编码格式为UTF8(encoding 'utf8')。 m_db=# COPY tpcds.ship_mode TO STDOUT WITH (delimiter ',', encoding 'utf8'); --将tpcds.ship_mode 的数据输出到STDOUT，使用参数如下：导入格式为CSV（format 'CSV'），引号包围SM_SHIP_MODE_SK字段的导出内容(force_quote(SM_SHIP_MODE_SK))。 m_db=# COPY tpcds.ship_mode TO STDOUT WITH (format 'CSV', force_quote(SM_SHIP_MODE_SK)); --创建tpcds.ship_mode_t1表。 m_db=# 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) ) DISTRIBUTE BY HASH(SM_SHIP_MODE_SK ); --从STDIN复制数据到表tpcds.ship_mode_t1。 m_db=# COPY tpcds.ship_mode_t1 FROM STDIN; --从/home/omm/ds_ship_mode.dat文件复制数据到表tpcds.ship_mode_t1。 m_db=# 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'）。 m_db=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' WITH(format 'text', delimiter E'\t', ignore_extra_data 'true', noescaping 'true'); --删除表和SCHEMA。 m_db=# DROP TABLE tpcds.ship_mode; m_db=# DROP TABLE tpcds.ship_mode_t1; m_db=# DROP SCHEMA tpcds;
