数据仓库服务 GAUSSDB(DWS)-COPY:语法格式

时间:2024-10-21 16:50:40

语法格式

  • 从一个文件拷贝数据到一个表:
     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 [  ...] ] ];
    
    1. COPY TO语法形式约束如下:

      (query)与[USING] DELIMITER不兼容,即若COPY TO的数据来自于一个query的查询结果,那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。

    2. 对于FIXED FORMATTTER语法后面跟随的copy_option是以空格进行分隔的。
    3. copy_option是指COPY原生的参数形式,而option是兼容外表导入的参数形式。
    4. 语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ ( option [, ...] ) | copy_option [ ...] ] 可以任意排列组合。
    其中可选参数option子句语法为:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    FORMAT 'format_name'
    | OIDS [ boolean ]
    | DELIMITER 'delimiter_character'
    | NULL 'null_string'
    | HEADER [ boolean ]
    | FILEHEADER 'header_file_string'
    | FREEZE [ boolean ]
    | QUOTE 'quote_character'
    | ESCAPE 'escape_character'
    | EOL 'newline_character'
    | NOESCAPING [ boolean ]
    | FORCE_QUOTE { ( column_name [, ...] ) | * }
    | FORCE_NOT_NULL ( column_name [, ...] )
    | ENCODING 'encoding_name'
    | IGNORE_EXTRA_DATA [ boolean ]
    | FILL_MISSING_FIELDS [ boolean ]
    | COMPATIBLE_ILLEGAL_CHARS [ boolean ]
    | PRESERVE_BLANKS [ boolean ]
    | DATE_FORMAT 'date_format_string'
    | TIME_FORMAT 'time_format_string'
    | TIMESTAMP_FORMAT 'timestamp_format_string'
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
    | SERVER 'obs_server_string'
    | BOM [ boolean ]
    | MAXROW [ integer ]
    | FILEPREFIX 'file_prefix_string'
    
    其中可选参数copy_option子句语法为:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    OIDS 
    | NULL 'null_string' 
    | HEADER 
    | FILEHEADER 'header_file_string' 
    | FREEZE 
    | FORCE_NOT_NULL column_name [, ...]
    | FORCE_QUOTE { column_name [, ...] | * }
    | BINARY 
    |  CS V 
    | QUOTE [ AS ] 'quote_character' 
    | ESCAPE [ AS ] 'escape_character' 
    | EOL 'newline_character'
    | ENCODING 'encoding_name' 
    | IGNORE_EXTRA_DATA 
    | FILL_MISSING_FIELDS
    | COMPATIBLE_ILLEGAL_CHARS 
    | PRESERVE_BLANKS 
    | DATE_FORMAT 'date_format_string' 
    | TIME_FORMAT 'time_format_string' 
    | TIMESTAMP_FORMAT 'timestamp_format_string' 
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
    
support.huaweicloud.com/sqlreference-830-dws/dws_06_0230.html