VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

使用vsql元命令导入数据

vsql工具提供了元命令\copy进行数据导入。

命令格式

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout }
[ with ] [ binary ] [ delimiter [ as ] 'character' ] [without escaping] [ null [ as ] 'string' ]
[ csv [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ]
[ force quote column_list | * ] [ force not null column_list ] ]  [ batch_size n ]  [ show_line_number ] [compatible _illegal_chars]

注意事项

  • 在任何vsql客户端登录数据库成功后,可以使用该命令进行数据的导入/导出。但是与SQL的COPY命令不同,该命令读取/写入的文件是本地文件,而非数据库服务器端文件;所以,要操作的文件的可访问性、权限等,都是受限于本地用户的权限。

  • \COPY只适合小批量、格式良好的数据导入,不会对非法字符做预处理,也无容错能力,无法适用于含有异常数据的场景。导入数据应优先选择COPY。

参数说明

  • table

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

    取值范围:已存在的表名。

  • column_list

    可选的待拷贝字段列表。

    取值范围:任意字段。如果没有声明字段列表,将使用所有字段。

  • query

    其结果将被拷贝。

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

  • filename

    文件的绝对路径。执行copy命令的用户必须有此路径的写权限。

  • stdin

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

  • stdout

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

  • pstdin

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

  • pstout

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

  • binary

    使用二进制格式存储和读取,而不是以文本的方式。在二进制模式下,不能声明DELIMITER、NULL、CSV选项。指定binary类型后,不能再通过option或copy_option指定CSV、FIXED、TEXT等类型。

  • delimiter [ as ] 'character'

    指定数据文件行数据的字段分隔符。

    取值范围:支持多字符分隔符,但分隔符不能超过10个字节。

    默认值:

    • TEXT格式的默认分隔符是水平制表符(tab)。
    • CSV格式的默认分隔符为“,”。
    • FIXED格式没有分隔符。
    • 分隔符不能是\r和\n。
    • 分隔符不能和null参数相同。
    • CSV格式数据的分隔符不能和quote参数相同。
    • TEXT格式数据的分隔符不能包含: .abcdefghijklmnopqrstuvwxyz0123456789。
    • 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
    • 分隔符推荐使用多字符和不可见字符。多字符例如'^&',设置方法为delimiter '^&',不可见字符例如0x07,0x08,0x1b等,设置方法为delimiter E'\x1b'
  • null [ as ] 'string'

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

    取值范围:支持指定多字符,最大为100个字符。

    默认值:

    • CSV格式下默认值是一个没有引号的空字符串。
    • 在TEXT格式下默认值是\N(包括'\'和'N'两个字符)。
    • null值不能包含\r或\n。
    • null值不能和分隔符、quote参数相同。
    • 在CSV格式下,COPY TO导出模式下,为避免数据混淆,当null值与字段内的取值相同时,会将字段数据用quote字符包裹,以作区分。
  • header

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

    在导入数据时,如果header选项为on,则数据文本第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。

    在导出数据时,如果header选项为on,则需要指定fileheader。fileheader是指定导出数据包含标题行的定义文件。如果header为off,则导出数据文件不包含标题行。

    取值范围:true/on,false/off。

    默认值:false

  • quote [ as ] 'character'

    CSV格式文件下的引号字符。

    默认值:""

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

    CSV格式下,用来指定逃逸字符,逃逸字符后的字符会被当作字段内容处理。逃逸字符只能指定为单字节字符。

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

  • force quote column_list | *

    在CSV COPY TO模式下,强制在每个声明的字段周围对所有非NULL值都使用引号包围。NULL输出不会被引号包围。

    取值范围:已存在的字段。

  • force not null column_list

    在CSV COPY FROM模式下,指定的字段输入不能为空。

    取值范围:已存在的字段。

  • batch_size n

    对导入的数据总行数进行分批,每批数据提交n行。

    • 行数 n 必须是正整数。

    • 默认情况下n为0,表示不启用分批提交。

    • 如果总行数无法被 n 除尽,则最后一批提交行数为数据总量除以n的余数。
    • 使用此功能时,不支持指定换行符导入,不支持二进制格式导入。
  • show_line_number

    导入完成后显示导入行数。

    • 默认情况下\copy不显示导入行数。
    • 使用此功能时,不支持指定换行符导入,不支持二进制格式导入。
  • without escaping

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

    取值范围:仅支持TEXT格式。

  • compatible _illegal_chars

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

    取值范围:true/on,false/off。

    • 参数为true/on,则导入时遇到非法字符进行容错处理,非法字符转换后入库,不报错,不中断导入。
    • 参数为false/off,导入时遇到非法字符进行报错,中断导入。

    缺省值:false/off

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

    (1)对于'\0',容错后转换为空格。

    (2)对于其他非法字符,容错后转换为问号。

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

示例

示例1: \copy导入的基本操作

1、创建目标表a。

CREATE TABLE a(a int);

2、导入数据。

从stdin拷贝数据到目标表a。

\copy a from stdin;

出现>>符号提示时,输入数据,输入\.时结束。

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> \.

3、查询导入目标表a的数据。

SELECT * FROM a;

返回结果如下:

a 
---
1
2
(2 rows)

4、从本地文件拷贝数据到目标表a。假设存在本地文件/home/vastbase/2.csv。

  • 分隔符为“,”。
  • 在导入过程中,若数据源文件比外表定义的列数多,则忽略行尾多出来的列。

    \copy a FROM '/home/vastbase/2.csv' WITH (delimiter',',IGNORE_EXTRA_DATA 'on');
    

示例2: 分批导入,导入成功后返回行数。

1、创建测试表并插入数据。

create table t_117234(col1 int, col2 varchar(20), col3 char(10));
insert into t_117234 values (1,'tom','red'),(2,'ken','yellow'),(3,'LiuMing','purple'),(4,'Lily','pink'),(5,'Dim','blue'),(6,'LiHua','green'),(7,'Tricy','orange'),(8,'Mina','black'),(9,'joe','silver'),(10,'Lisa','white'),(11,'Adam','gold'),(12,'Bob','coffee'),(13,'Ken','gray'),(14,'Robert','brown');

2、查看测试表数据。

select * from t_117234;

返回结果如下,表中当前共有14条数据:

 col1 |  col2   |    col3
------+---------+------------
    1 | tom     | red
    2 | ken     | yellow
    3 | LiuMing | purple
    4 | Lily    | pink
    5 | Dim     | blue
    6 | LiHua   | green
    7 | Tricy   | orange
    8 | Mina    | black
    9 | joe     | silver
   10 | Lisa    | white
   11 | Adam    | gold
   12 | Bob     | coffee
   13 | Ken     | gray
   14 | Robert  | brown
(14 rows)

3、生成后续使用的导入文件。

copy t_117234 to '/tmp/test_117234.csv' ;

4、使用上一步得到的文件进行导入。

\copy t_117234 from '/tmp/test_117234.csv' batch_size 4 show_line_number

返回结果如下:

....COPY 14

5、验证导入结果:

select * from t_117234;

返回结果如下,导入后原表中数据翻倍,共28条:

 col1 |  col2   |    col3
------+---------+------------
    1 | tom     | red
    2 | ken     | yellow
    3 | LiuMing | purple
    4 | Lily    | pink
    5 | Dim     | blue
    6 | LiHua   | green
    7 | Tricy   | orange
    8 | Mina    | black
    9 | joe     | silver
   10 | Lisa    | white
   11 | Adam    | gold
   12 | Bob     | coffee
   13 | Ken     | gray
   14 | Robert  | brown
    1 | tom     | red
    2 | ken     | yellow
    3 | LiuMing | purple
    4 | Lily    | pink
    5 | Dim     | blue
    6 | LiHua   | green
    7 | Tricy   | orange
    8 | Mina    | black
    9 | joe     | silver
   10 | Lisa    | white
   11 | Adam    | gold
   12 | Bob     | coffee
   13 | Ken     | gray
   14 | Robert  | brown
(28 rows)