使用gsql元命令\COPY导入数据
GaussDB(DWS)的gsql工具提供了元命令\copy进行数据导入。
\copy命令
\copy命令格式以及说明参见表 1 \copy元命令说明。
语法 |
说明 |
---|---|
\copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout } [ with ] [ binary ] [ oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ] [ force quote column_list | * ] [ force not null column_list ] ] |
在任何gsql客户端登录数据库成功后,可以使用该命令进行数据的导入/导出。但是与SQL的COPY命令不同,该命令读取/写入的文件是本地文件,而非数据库服务器端文件;所以,要操作的文件的可访问性、权限等,都是受限于本地用户的权限。
说明:
\COPY只适合小批量,格式良好的数据导入,容错能力较差。导入数据应优先选择GDS或COPY。 |
参数说明
- table
取值范围:已存在的表名。
- column_list
取值范围:任意字段。如果没有声明字段列表,将使用所有字段。
- query
取值范围:一个必须用圆括弧包围的SELECT或VALUES命令。
- filename
- stdin
- stdout
- pstdin
- pstout
- 声明输出打印到gsql的标准输出。
- binary
使用二进制格式存储和读取,而不是以文本的方式。在二进制模式下,不能声明DELIMITER,NULL,CSV选项。指定binary类型后,不能再通过option或copy_option指定CSV、FIXED、TEXT等类型。
- oid
若COPY FROM对象为query或者对于没有oid的表,指定oids标识报错。
取值范围:true/on,false/off。
默认值:false/off。
- delimiter [ as ] 'character'
指定数据文件行数据的字段分隔符。
- 分隔符不能是\r和\n。
- 分隔符不能和null参数相同,CSV格式数据的分隔符不能和quote参数相同。
- TEXT格式数据的分隔符不能包含: \.abcdefghijklmnopqrstuvwxyz0123456789。
- 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
- 分隔符推荐使用多字符和不可见字符。多字符例如'$^&';不可见字符例如E'\x07',E'\x08',E'\x1b'等。
取值范围:支持多字符分隔符,但分隔符不能超过10个字节。
默认值:
- TEXT格式的默认分隔符是水平制表符(tab)。
- CSV格式的默认分隔符为“,”。
- FIXED格式没有分隔符。
- null [ as ] 'string'
取值范围:
- null值不能是\r和\n,最大为100个字符。
- null值不能和分隔符、quote参数相同。
默认值:
- CSV格式下默认值是一个没有引号的空字符串。
- 在TEXT格式下默认值是\N。
- header
指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。
在导入数据时,如果header选项为on,则数据文本第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。
在导出数据时,如果header选项为on,则需要指定fileheader。fileheader是指定导出数据包含标题行的定义文件。如果header为off,则导出数据文件不包含标题行。
取值范围:true/on,false/off。
默认值:false/off。
- quote [ as ] 'character'
默认值:双引号。
- quote参数不能和分隔符、null参数相同。
- quote参数只能是单字节的字符。
- 推荐不可见字符作为quote,例如E'\x07',E'\x08',E'\x1b'等。
- escape [ as ] 'character'
CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。
默认值:双引号。当与quote值相同时,会被替换为'\0'。
- force quote column_list | *
在CSV COPY TO模式下,强制对每个声明的字段的对所有非NULL值都使用引用字符。NULL输出不会被引用。
取值范围:已存在的字段。
- force not null column_list
在CSV COPY FROM模式下,指定的字段输入不能为空。
取值范围:已存在的字段。
示例
1 2 3 4 5 6 7 8 |
create table copy_example ( col_1 integer, col_2 text, col_3 varchar(12), col_4 date, col_5 time ); |
- 示例一:从stdin拷贝数据到目标表copy_example。
1
\copy copy_example from stdin csv;
出现>>符号提示时,输入数据,输入\.时结束。1 2 3 4
Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1,"iamtext","iamvarchar",2006-07-07,12:00:00 >> \.
- 示例二:在本地目录'/local/data/'下有example.csv文件,包含header行,使用'|'作为delimiter,使用双引号作为quote。内容如下:
1|"iamtext"|"iamvarchar"|2006-07-07|12:00:00
2|"iamtext"|"iamvarchar"|2022-07-07|19:00:02
从本地文件example.csv导入数据到目标表copy_example,header选项为'on',自动忽略第一行。quote默认为双引号,因此可以不用指定。1
\copy copy_example from '/local/data/example.csv' with(header 'on', format 'csv', delimiter '|', date_format 'yyyy-mm-dd', time_format 'hh24:mi:ss');
- 示例三:在本地目录'/local/data/'下有example.csv文件,使用','作为delimiter,使用双引号作为quote,其中第一行缺少最后一个字段,第二行多一个字段。内容如下
1,"iamtext","iamvarchar",2006-07-07
2,"iamtext","iamvarchar",2022-07-07,19:00:02,12:00:00
从本地文件example.csv导入数据到目标表copy_example,delimiter默认为',',因此可以不用指定,由于指定了容错参数IGNORE_EXTRA_DATA和FILL_MISSING_FIELDS,缺少的字段会用NULL替换,多出的字段被忽略。
1
\copy copy_example from '/local/data/example.csv' with( format 'csv', date_format 'yyyy-mm-dd', time_format 'hh24:mi:ss', IGNORE_EXTRA_DATA 'true', FILL_MISSING_FIELDS 'true');
- 示例四:将copy_example表的内容导出到stdout,格式为csv,使用双引号作为quote,第四列和第五列强制使用quote包围;
1
\copy copy_example to stdout CSV quote as '"' force quote col_4,col_5;