GreenPlum/postgres copy命令导出/导入数据

一、COPY命令简单实用

1、copy在postgres与GreenPlum介绍

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命令导出/导入数据的步骤

 

COPY aa_test  FROM '/tmp/data/test.csv'  csv HEADER NULL '';

 

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

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

注意：
使用root用户

# 创建目录
[root@node8 /]# mkdir /tmp/data

# 赋予权限
[root@node8 /]# chmod 777 /tmp/data/
12345
(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".
1234567891011

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
123456789

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

# 使用select命令
---------------------

 

二、copy命令详细介绍

1、Copy的基本语法

Copy的作用是复制数据在数据表和文件之间。
Copy在PostgreSql中的语法是(来自文档):

1）  将文件中的数据复制到表中：
COPY table_name [ ( column_name [, ...] ) ]
   FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

2）将表中的数据复制到文件中：
COPY { table_name [ ( column_name [, ...] )] | ( query ) }
    TO{ 'filename' | PROGRAM 'command' | STDOUT }
    [[ WITH ] ( option [, ...] ) ]
其中option的设置的参数如下：
   FORMAT format_name
   OIDS [ boolean ]
   FREEZE [ boolean ]
   DELIMITER 'delimiter_character'
   NULL 'null_string'
   HEADER [ boolean ]
   QUOTE 'quote_character'
   ESCAPE 'escape_character'
   FORCE_QUOTE { ( column_name [, ...] ) | * }
   FORCE_NOT_NULL ( column_name [, ...] )
   ENCODING 'encoding_name'

2、Copy的参数解释和示例

     FORMAT：指复制到文件的文件类型,如：CSV,TEXT。
       OIDS  ：指复制到文件时带上oid，但是当某个表没有oid时就会出错。
       FREEZE ：冻结数据，然后执行VACUUM FREEZE。
       DELIMITER：指在导出文件时的分隔符指定需要用单引号。在TEXT时默认为tab，CSV文件默认是逗号。不支持binary文件格式。
      HEADER：指在复制到文件时带上表字段名称。
       NULL：指定null值，默认为N。
       ENCODING：指定文件的编码，如果没有指定就默认使用客户端的字符集。
       STDIN：指的是客户端程序的输入流。
STDOUT：指向是客户端的输出流。
 
  在执行COPY FROM时table_name就需要实际存在的表，其中字段是可以自选的，如：
1.         COPYemp(ename) FROM “E://emp.txt”
需要注意的是字段类型要匹配并且文件也只要一个字段的值。
2.         COPYemp FROM “E://emp.txt”
文件中需要包含emp表中的字段的值，或tab，或‘，’等分割开的数据
  在执行COPY TO时的一些注意，解释和示例：
1.         COPYemp TO STDOUT (DELIMITER ‘|’)
指的是输出在客户端并且以‘|’为分隔符
2.         COPY (select* from emp) TO ‘E://emp.csv’ (FORMAT ‘CSV’,DELIMITER ‘|’,HEADER true,NULL ‘’’’’’)
Table_name是可以为动态视图的，并且在后面的括号中参数可以包含多个，多个参数以逗号分隔开。HERDER的值可以使true，false，1,0，on，off，需要注意的是HERDER参数只有在FORMAT为CSV时生效。
3.         COPY empTO PROGRAM ‘zip > E://emp.zip’
参数PROGRAM指的是使用操作系统内部的程序对输出文件进行加工，上面的作用是将emp导出并且压缩。

 

转载自：
原文：https://blog.csdn.net/chuan_day/article/details/44099859 

原文：https://blog.csdn.net/lijingjingchn/article/details/83055550