使用copy to/from命令导出导入数据

wpp0303
128 阅读5分钟

使用copy to/from命令导出导入数据

操作场景**

gsql工具提供了元命令\copy进行数据导入导出。\copy只适合小批量、格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。

迁移准备**

  1. 准备弹性云服务器或可通过公网访问GaussDB。

    • 通过弹性云服务器连接GaussDB数据库实例，需要创建一台弹性云服务器。

      创建并登录弹性云服务器，请参见《弹性云服务器快速入门》中“购买弹性云服务器”和“登录弹性云服务器”。

    • 通过公网地址连接GaussDB数据库实例，需具备以下条件。

      1. 先对GaussDB数据库实例绑定公网地址，如何绑定公网地址，请参见绑定弹性公网IP。
      2. 保证本地设备可以访问GaussDB数据库实例绑定的公网地址。

  2. 在准备的弹性云服务器或可访问GaussDB数据库的设备上，安装gsql客户端，并连接到GaussDB数据库实例。详情请参见使用gsql连接数据库。

导出数据**

在准备的弹性云服务器或可访问GaussDB数据库的设备上，连接GaussDB实例后，导出copy_example表的内容。

  • 方式一：将copy_example表的内容导出到stdout，格式为csv，使用双引号作为quote，第四列和第五列强制使用quote包围。

    \copy copy_example to stdout CSV quote as '"' force quote col_4,col_5;
1,iamtext,iamvarchar,"2006-07-07","12:00:00"
2,sometext,somevarchar,"2006-07-07","12:00:00"
3,sometext,somevarchar,"2006-07-07","12:00:00"
4,sometext,somevarchar,"2022-07-07","19:00:02"
5,sometext,somevarchar,"2006-07-07",
6,sometext,somevarchar,"2022-07-07","19:00:02"

  • 方式二：将copy_example表的内容导出到本地，路径为'/tmp/data/'，文件名为'copy_example.csv'，使用'|'作为delimiter，使用双引号作为quote。

    \copy copy_example to '/tmp/data/copy_example.csv' csv delimiter '|' quote '"';

    查看'/tmp/data/copy_example.csv'文件，确认数据已导出。

    1|iamtext|iamvarchar|2006-07-07|12:00:00
2|sometext|somevarchar|2006-07-07|12:00:00
3|sometext|somevarchar|2006-07-07|12:00:00
4|sometext|somevarchar|2022-07-07|19:00:02
5|sometext|somevarchar|2006-07-07|
6|sometext|somevarchar|2022-07-07|19:00:02

  • 方式三：将copy_example表的查询结果集导出到本地，路径为'/tmp/data/'，文件名为'copy_example2.csv'，使用','作为delimiter，使用双引号作为quote。

    \copy (select * from copy_example where col_1 = 1) to '/tmp/data/copy_example2.csv' csv delimiter ',' quote '"';

    查看'/tmp/data/copy_example2.csv'文件，确认数据已导出。

    1,iamtext,iamvarchar,2006-07-07,12:00:00

导入数据**

导入数据到GaussDB实例中，以导入到目标表copy_example为例，其结构如下。

| ``` create table copy_example ( col_1 integer, col_2 text, col_3 varchar(12), col_4 date, col_5 time );

| ----------------------------------------------------------------------------------------------------------------- |

在准备的弹性云服务器或可访问GaussDB数据库的设备上，连接GaussDB实例后，导入数据到目标表copy_example。

-   方式一：从stdin拷贝数据到目标表copy_example。

    ```
    \copy copy_example from stdin csv;
    ```

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

    ```
    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
    >> 2,"sometext","somevarchar",2006-07-07,12:00:00
    >> .
    ```

    查看导入的数据。

    ```
    select * from copy_example;
     col_1 |  col_2   |    col_3    |        col_4        |  col_5   
    -------+----------+-------------+---------------------+----------
         1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
         2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
    (2 rows)
    ```

-   方式二：在本地目录'/tmp/data/'下有example.csv文件，包含header行，使用'|'作为delimiter，使用双引号作为quote。内容如下。

    ```
    header
    3|"sometext"|"somevarchar"|2006-07-07|12:00:00
    4|"sometext"|"somevarchar"|2022-07-07|19:00:02
    ```

    从本地文件example.csv导入数据到目标表copy_example，其中header选项为'on'，自动忽略第一行。quote默认为双引号，因此可以不用指定。

    ```
    \copy copy_example from  '/tmp/data/example.csv' with(header 'on', format 'csv', delimiter '|', date_format 'yyyy-mm-dd',  time_format 'hh24:mi:ss');
    ```

    查看导入的数据。

    ```
    select * from copy_example;
     col_1 |  col_2   |    col_3    |        col_4        |  col_5   
    -------+----------+-------------+---------------------+----------
         1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
         2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
         3 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
         4 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
    (4 rows)
    ```

-   方式三：在本地目录'/tmp/data/'下有example2.csv文件，使用','作为delimiter，使用双引号作为quote，其中第一行缺少最后一个字段，第二行最后多一个字段。内容如下。

    ```
    5,"sometext","somevarchar",2006-07-07
    6,"sometext","somevarchar",2022-07-07,19:00:02,12:00:00
    ```

    从本地文件example2.csv导入数据到目标表copy_example，其中delimiter默认为','，因此可以不用指定，由于指定了容错参数IGNORE_EXTRA_DATA和FILL_MISSING_FIELDS，缺少的字段会用NULL替换，多出的字段被忽略。

    ```
    \copy copy_example from  '/tmp/data/example2.csv' with( format 'csv', date_format 'yyyy-mm-dd',  time_format 'hh24:mi:ss', IGNORE_EXTRA_DATA 'true', FILL_MISSING_FIELDS 'true');
    ```

    查看导入的数据。

    ```
    select * from copy_example;
     col_1 |  col_2   |    col_3    |        col_4        |  col_5   
    -------+----------+-------------+---------------------+----------
         1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
         2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
         3 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
         4 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
         5 | sometext | somevarchar | 2006-07-07 00:00:00 | 
         6 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
    (6 rows)
    ```

#### 相关链接**

有关\copy命令的更多信息，请参见：

-   [COPY（分布式）](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0531.html)
-   [COPY（主备版）](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0542.html)

父主题： [数据库迁移](https://support.huaweicloud.com/usermanual-gaussdb/gaussdb_01_512.html)
avatar
文章
阅读
粉丝