更新时间:2025-08-04 GMT+08:00
分享

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 [ ...] ] 可以任意排列组合。

参数说明

表1 COPY参数说明

参数

描述

取值范围

query

将查询结果进行拷贝。

一个必须用圆括弧包围的SELECT或VALUES命令。

table_name

表的名字(可以有模式修饰)。

已存在的表名。

column_name

可选的待拷贝字段列表。

如果没有声明字段列表,将使用所有字段。

STDIN

声明输入是来自标准输入。

-

STDOUT

声明输出打印到标准输出。

-

FIXED

打开字段固定长度模式。

  • 在字段固定长度模式下,不能声明DELIMITER,NULL,CSV选项。
  • 指定FIXED类型后,不能再通过option或copy_option指定BINARY、CSV、TEXT等类型。
  • 每条记录的每个字段长度相同。
  • 长度不足的字段以空格填充,数字类型字段左对齐,字符字段右对齐。
  • 字段和字段之间没有分隔符。

[USING] DELIMITER 'delimiters'

在文件中分隔各个字段的字符串,分隔符最大长度不超过10个字节。

不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。

在文本模式下,缺省是水平制表符,在CSV模式下是一个逗号。

WITHOUT ESCAPING

在TEXT格式中,不对'\'和后面的字符进行转义。

仅支持TEXT格式。

LOG ERRORS

若指定该参数,则开启对于COPY FROM语句中数据类型错误的容错机制,相关错误行的错误记录会记录到此库中的public.pgxc_copy_error_log表中,备后续查阅。

须知:

此容错选项的使用限制如下:

  • 此容错机制仅捕捉COPY FROM过程中CN节点上数据解析过程中相关的数据类型错误(DATA_EXCEPTION),诸如CN与DN之间的网络交互错误或者是DN上的表达式转换错误等CN数据解析逻辑之外的过程无法涵盖在内。
  • 在每个库第一次使用时COPY FROM容错时,请先行检查public.pgxc_copy_error_log(COPY错误表)是否存在,若不存在请调用copy_error_log_create() 函数创建;若存在,请转移此表数据并删除这张表后,调用copy_error_log_create() 函数创建。更多关于表public.pgxc_copy_error_log的字段信息,请参见表3
  • 若在指定了LOG ERRORS的COPY FROM运行时,public.pgxc_copy_error_log不存在(未创建或者已删除)或表定义不符合copy_error_log_create() 中的预设表定义,则会报错。因此请确定此COPY错误表是使用copy_error_log_create() 函数创建的,否则可能导致容错的COPY FROM语句无法正常执行。
  • COPY已有的容错选项(如IGNORE_EXTRA_DATA)开启时,对应类型的错误会按照已有的方式处理而不会报出异常,因此错误表也不会有相应数据。
  • 此容错机制的覆盖范围与GDS Foreign Table的容错范围相同。推荐用户通过表名列以及COPY FROM语句开始时间戳对查询结果进行过滤。错误数据处理可参考处理错误表章节。

仅支持导入(即COPY FROM)时指定。

LOG ERRORS DATA

LOG ERRORS DATA和LOG ERRORS的区别:

  • LOG ERRORS DATA会登记错误行数据信息(rawrecord)。
  • 使用该参数时,若错误内容过于复杂可能存在写入容错表失败的风险,导致任务失败。

-

REJECT LIMIT 'limit'

与LOG ERROR选项共同使用,对COPY FROM的容错机制设置数值上限,一旦此COPY FROM语句错误数据超过选项指定条数,则会按照原有机制报错。

说明:

LOG ERRORS中描述的容错机制,REJECT LIMIT的计数也是按照执行COPY FROM的CN上遇到的解析错误数量计算,而不是每个DN上的错误数量,请与GDS容错机制区别开。

正整数(1-INTMAX),'unlimited'(无最大值限制)

缺省值:

  • 若未指定LOG ERRORS,则会报错;
  • 若指定LOG ERRORS,则默认为0。

FORMATTER

在固定长度模式中(即声明了FIXED参数),定义每一个字段在数据文件中的位置。按照column(offset,length)格式定义每一列在数据文件中的位置。

  • offset取值不能小于0,以字节为单位。
  • length取值不能小于0,以字节为单位。
  • 所有列的总长度和不能大于1GB。
  • 文件中没有出现的列默认以空值代替。

OPTION { option_name ' value ' }

用于指定兼容外表的各类参数。

具体请参见表2

COPY_OPTION { option_name ' value ' }

用于指定COPY原生的各类参数。

具体请参见表3

表2 OPTION参数详情

参数

描述

取值范围

FORMAT

数据源文件的格式。

支持CSV、TEXT、FIXED、BINARY类型的文件。

  • CSV格式的文件,可以有效处理数据列中的换行符,但对一些特殊字符处理有欠缺。
  • TEXT格式的文件,可以有效处理一些特殊字符,但无法正确处理数据列中的换行符。
  • FIXED格式的文件,适用于每条数据的数据列都比较固定的数据,长度不足的列会添加空格补齐,过长的列则会自动截断。
  • BINARY形式的选项会使得所有的数据被存储/读作二进制格式而不是文本。 这比TEXT和CSV格式的要快一些,但是一个BINARY格式文件可移植性比较差。

缺省值:TEXT

OIDS

为每行拷贝内部对象标识(oid)。

说明:

若COPY FROM对象为query或者对于没有oid的表,指定oids标识报错。

  • true/on
  • false/off

缺省值:false

DELIMITER

指定数据文件行数据的字段分隔符。
  • 分隔符不能是\r和\n。
  • 分隔符不能和null参数相同,CSV格式数据的分隔符不能和quote参数相同。
  • TEXT格式数据的分隔符不能包含: 小写字母、数字和特殊字符.\。
  • 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
  • 分隔符推荐使用多字符和不可见字符。多字符例如'$^&';不可见字符例如0x07,0x08,0x1b等。
  • 建议TEXT格式下多字符分隔符中的字符不要完全相同,例如不建议使用delimiter '---' 。

支持多字符分隔符,但分隔符不能超过10个字节。

缺省值:

  • TEXT格式的默认分隔符是水平制表符(tab)。
  • CSV格式的默认分隔符为“,”。
  • FIXED格式没有分隔符。

NULL

用来指定数据文件中空值的表示方式。

  • null值不能是\r和\n,最大为100个字符。
  • null值不能和分隔符、quote参数相同。

缺省值:

  • CSV格式下默认值是一个没有引号的空字符串。
  • 在TEXT格式下默认值是\N。

HEADER

指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。

  • 在导入数据时,如果header选项为on,则数据文件中第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。
  • 在导出数据时,如果header选项为on,则需要指定fileheader。如果header为off,则导出数据文件不包含标题行。
  • true/on
  • false/off

缺省值:false

QUOTE

CSV格式文件下,指定一个数据值被引用时使用的引用字符。

缺省值:双引号

  • quote参数不能和分隔符、null参数相同。
  • quote参数只能是单字节的字符。
  • 推荐不可见字符作为quote,例如0x07,0x08,0x1b等。

ESCAPE

CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。

单字节字符。

缺省值:双引号

EOL 'newline_character'

指定导入导出数据文件换行符样式。

支持多字符换行符,但换行符不能超过10个字节。

常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。

  • EOL参数只能用于TEXT格式的导入导出,不支持CSV格式和FIXED格式导入。为了兼容原有EOL参数,仍然支持导出CSV格式和FIXED格式时指定EOL参数为0x0D或0x0D0A。
  • EOL参数不能和分隔符、null参数相同。
  • EOL参数不能包含:.abcdefghijklmnopqrstuvwxyz0123456789。

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

若数据源文件比外表定义列数多,是否会忽略对多出的列。该参数只在数据导入过程中使用。

  • 参数为true/on,若数据源文件比外表定义列数多,则忽略行尾多出来的列。
  • 参数为false/off,若数据源文件比外表定义列数多,会显示如下错误信息。
    1
    extra data after last expected column
    

缺省值:false/off

须知:

如果行尾换行符丢失,使两行变成一行时,设置此参数为true将导致后一行数据被忽略掉。

COMPATIBLE_ILLEGAL_CHARS

导入非法字符容错参数,仅对COPY FROM导入有效。

此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。

导入非法字符容错规则如下:

  • 对于'\0',容错后转换为空格;
  • 对于其他非法字符,容错后转换为问号;
  • 如果compatible_illegal_chars为true/on标识导入时对于非法字符进行容错处理,则若NULL、DELIMITER、QUOTE、ESCAPE设置为空格或问号则会通过如"illegal chars conversion may confuse COPY escape 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。
  • 参数为true/on,则导入时遇到非法字符进行容错处理,非法字符转换后入库,不报错,不中断导入。
  • 参数为false/off,导入时遇到非法字符进行报错,中断导入。

缺省值:false/off

FILL_MISSING_FIELDS

当数据加载时,若数据源文件中一行的最后一个字段缺失的处理方式。

  • 参数为true/on,当数据导入时,若数据源文件中一行数据的最后一个字段缺失,则把最后一个字段的值设置为NULL,不报错。
  • 参数为false/off,如果最后一个字段缺失会显示如下错误信息。
    missing data for column "tt"

缺省值: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编码。

  • true/on
  • false/off

缺省值: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及以上集群版本支持。

  • 参数为true/on,表示针对BYTEAOID、CHAROID、NAMEOID、TEXTOID、BPCHAROID、VARCHAROID、NVARCHAR2OID、CSTRINGOID对应类型的列数据,保留拖尾 \t,不进行修剪。
  • 参数为false/off,表示针对所有数据类型的数据拖尾的空白字符(包括空格,\t,\v,\f)进行修剪。

缺省值为false/off。

表3 COPY_OPTION参数详情

参数

描述

取值范围

OIDS

为每行拷贝内部对象标识(oid)。

若COPY FROM对象为query或者对于没有oid的表,指定oids标识报错。

-

NULL null_string

用来指定数据文件中空值的表示。
须知:

在使用COPY FROM的时候,任何匹配这个字符串的字符串将被存储为NULL值,所以应该确保指定的字符串和COPY TO相同。

  • null值不能是\r和\n,最大为100个字符。
  • null值不能和分隔符、quote参数相同。

缺省值:

  • 在TEXT格式下默认值是\N。
  • CSV格式下默认值是一个没有引号的空字符串。

HEADER

指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。

  • 导入数据时,如果header选项为on,则数据文件中第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。
  • 导出数据时,如果header选项为on,则需要指定fileheader。如果header为off,则导出数据文件不包含标题行。

FILEHEADER

导出数据时用于定义标题行的文件,一般用来描述每一列的数据信息。

须知:
  • 仅在header为on或true的情况下有效。
  • fileheader指定的是绝对路径。
  • 该文件只能包含一行标题信息,并以换行符结尾,多余的行将被丢弃(标题信息不能包含换行符)。
  • 该文件包括换行符在内长度不超过1M。

-

FREEZE

将COPY加载的数据行设置为已经被frozen,就等同于这些数据行执行过VACUUM FREEZE。

该参数为一个初始数据加载的性能选项。仅当以下三个条件同时满足时,数据行才会被frozen:

  • 在同一事务中create或truncate这张表之后执行COPY。
  • 当前事务中没有打开的游标。
  • 当前事务中没有原有的快照。
须知:

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格式文件下的引号字符。

  • quote参数不能和分隔符、null参数相同。
  • quote参数只能是单字节的字符。
  • 推荐不可见字符作为quote,例如0x07,0x08,0x1b等。

缺省值:双引号。

ESCAPE [AS] 'escape_character'

CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。

缺省值为双引号。当与quote值相同时,会被替换为'\0'。

EOL 'newline_character'

指定导入导出数据文件换行符样式。

支持多字符换行符,但换行符不能超过10个字节。

常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。

  • EOL参数只能用于TEXT格式的导入导出,不支持CSV格式和FIXED格式。为了兼容原有EOL参数,仍然支持导出CSV格式和FIXED格式时指定EOL参数为0x0D或0x0D0A。
  • EOL参数不能和分隔符、null参数相同。
  • EOL参数不能包含:.abcdefghijklmnopqrstuvwxyz0123456789。

ENCODING 'encoding_name'

指定文件编码格式名称。

有效的编码格式。

缺省值:当前编码格式。

IGNORE_EXTRA_DATA

指定当数据源文件比外表定义列数多时,忽略行尾多出来的列。该参数只在数据导入过程中使用。

若不使用该参数,在数据源文件比外表定义列数多,会显示如下错误信息。
1
extra data after last expected column

-

COMPATIBLE_ILLEGAL_CHARS

指定导入时对非法字符进行容错处理,非法字符转换后入库。不报错,不中断导入。该参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。仅对COPY FROM导入有效。

若不使用该参数,导入时遇到非法字符进行报错,中断导入。

导入非法字符容错规则如下:

  1. 对于'\0',容错后转换为空格;
  2. 对于其他非法字符,容错后转换为问号;
  3. 若compatible_illegal_chars为true/on标识,导入时对于非法字符进行容错处理,则若NULL、DELIMITER、QUOTE、ESCAPE设置为空格或问号则会通过如"illegal chars conversion may confuse COPY escape 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。

-

FILL_MISSING_FIELDS

当数据加载时,若数据源文件中一行的最后一个字段缺失的处理方式。

须知:

目前COPY指定此Option实际不会生效,即不会有相应的容错处理效果(不生效)。需要额外注意的是,打开此选项会导致解析器在CN数据解析阶段(即COPY错误表容错的涵盖范围)忽略此数据问题,而到DN重新报错,从而使得COPY错误表(打开LOG ERRORS REJECT LIMIT)在此选项打开的情况下无法成功捕获这类少列的数据异常。因此请不要指定此选项。

  • true/on
  • false/off

缺省值: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及以上集群版本支持。

  • 参数为true/on,表示针对BYTEAOID、CHAROID、NAMEOID、TEXTOID、BPCHAROID、VARCHAROID、NVARCHAR2OID、CSTRINGOID对应类型的列数据,保留拖尾 \t,不进行修剪。
  • 参数为false/off,表示针对所有数据类型的数据拖尾的空白字符(包括空格,\t,\v,\f)进行修剪。

缺省值为false/off。

COPY FROM能够识别的特殊反斜杠序列如下所示。

表4 特殊反斜杠序列

反斜杠序列

含义

\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_BLANKStrue', 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;

相关文档