使用GreenPlum/postgres的copy命令导出/导入数据

Dear 丶 2022-05-08 14:40 637阅读 0赞

### 1. COPY命令 ###

#### 1.1 postgres ####

postgres的`COPY`命令可以快速的导出/导入数据到postgresql数据库中，支持常用的文件格式，如：txt、sql、csv、压缩文件、二进制格式等。特别适合批量导出和导入数据，速度比较快。

`COPY TO`把一个表的所有内容都拷贝到一个文件；  
`COPY FROM`从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。

> 注意：  
> COPY只能用于表，不能用于视图。

#### 1.2 GreenPlum ####

对于数据的加载，GreenPlum数据库也提供了copy工具（源于PostgreSQL数据库），使用方法同postgres数据库的copy命令。copy命令支持文件与表之间的数据的导入和导出。

> **注意：**  
> 使用copy命令进行数据导入时，数据需要经过Master节点分发到Segment节点；同样使用copy命令进行数据卸载，数据也需要由Segment发送到Master节点，由Master节点汇总后再写入外部文件。这样就限制了数据加载与卸载的效率，但是数据量较小的情况下，copy命令就非常方便。  
>   
>   
> 当数据量比较大时可以考虑使用GreenPlum的**gpload**工具来导入。

--------------------

### 2. 使用copy命令导出/导入数据的步骤 ###

> 通常使用`psql`工具来进行数据的导出和导入。

#### 2.1 准备工作 ####

##### (1) 创建用于存放导出文件（或待导入文件）的目录，并赋予权限； #####

> **注意：**  
> 使用root用户

# 创建目录
    [root@node8 /]# mkdir /tmp/data
    
    # 赋予权限
    [root@node8 /]# chmod 777 /tmp/data/

##### (2) 进入psql并切换数据库 #####

> **注意：**  
> 如果是postgres数据库，则切换到`postgres`用户；  
> 如果是GreenPlum的MPP，则切换到`gpadmin`用户；

# 由于我使用的是GreenPlum的MPP，所以切换gpadmin用户
    # ---------------------------------------------------------
    [root@node8 ~]# su - gpadmin
    
    # 进入psql
    [gpadmin@node8 ~]$ psql
    
    # 使用`\c`命令切换数据库，切换到要导出或导入数据的数据库
    # ---------------------------------------------------------
    postgres=# \c resdata
    You are now connected to database "resdata" as user "gpadmin".

--------------------

### 3. 开始使用copy命令导出或导入数据 ###

# 使用 copy 命令将 resdata 库下的 aa_test 表中的数据导出到 /tmp/data/test.csv 文件中
    # ---------------------------------------------------------
    resdata=# COPY aa_test TO '/tmp/data/test.csv' WITH csv;
    COPY 2
    
    # 使用 copy 命令将 /tmp/data/test.csv 文件中内容导入到 resdata 库下的 aa_test 表中
    # ---------------------------------------------------------
    resdata=# COPY aa_test  FROM '/tmp/data/test.csv' WITH csv;
    COPY 2

> COPY TO还能拷贝SELECT查询的结果。如果声明了一个字段列表，COPY将只在文件和表之间拷贝已声明字段的数据。 如果表中有任何不在字段列表里的字段，那么COPY FROM将为那些字段插入缺省值。

# 使用select命令
    COPY (select * from bb_test capture_time > '2018-08-16 00:00:00' and capture_time < '2018-08-17 00:00:00') TO '/tmp/data/test_20180816.csv' with csv;