对于数据加载,GreenPlum数据库提供copy工具，copy工具源于PostgreSql数据库，之前在发布的GreenPlum建表相关的文章中也提到过copy命令，copy命令不仅支持表于表之间的数据加载，也支持文件于表之间的数据加载和表对文件的数据卸载。使用copy命令进行数据加载，数据需要经过Master节点分发到Segment节点，同样使用copy命令进行数据卸载，数据也需要由Segment发送到Master节点，由Master节点汇总后再写入外部文件，这样就限制了数据加载与卸载的效率，但是数据量较小的情况下，copy命令就非常方便。下面测试通过copy命令实现操作系统文件到数据库中表的数据加载。

1.创建测试表

dbdream=# create table ORD_PAY (id int,ORD_ID BIGint,PAY_SEQ int,PAY_MNS_CD int,PAY_DTM timestamp,RFN_PSS_AMT numeric(10,2),INST_ID int,INST_DTM timestamp,MDF_ID varchar(10)) DISTRIBUTED BY(id);
CREATE TABLE

2.准备测试数据

[gpadmin@mdw ~]$ head ORD_PAY.txt 
1,13082410952,101,100,,0.00,201908,2013/8/24 11:45:35,admin
2,13082411024,101,100,2013/8/26 17:10:00,179.00,106600,2013/8/24 11:45:38,admin
3,13082411019,101,100,2013/9/2 10:20:00,598.00,410488,2013/8/24 11:45:42,admin
4,13082411026,101,100,2013/9/3 10:00:00,199.00,104149,2013/8/24 11:45:42,admin
5,13082411031,101,100,2013/9/2 10:50:00,199.00,107464,2013/8/24 11:45:49,admin
6,13082411030,101,100,2013/9/3 19:00:00,199.00,106357,2013/8/24 11:45:51,admin
7,13082410945,101,100,2013/8/28 14:00:00,0.00,500019,2013/8/24 11:45:51,admin
8,13082411042,101,100,2013/8/27 17:50:00,199.00,106600,2013/8/24 11:46:03,admin
9,13082411025,101,100,2013/9/5 11:00:00,199.00,104524,2013/8/24 11:46:05,admin
10,13082411043,101,100,2013/8/27 9:10:00,199.00,107858,2013/8/24 11:46:05,admin

测试数据共计100万条，文件大小80M，里面包含部分字段有空值的数据，也包含部分字段类型不匹配的数据，通过逗号分隔。

3.数据加载

本实验从使用默认参数加载开始，一步一步解决加载过程中遇到的问题。

dbdream=> copy ORD_PAY from '/home/gpadmin/ORD_PAY.txt' with delimiter ',' null '';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

使用COPY命令进行外部文件加载数据，必须要超级用户才可以。

dbdream=# copy ORD_PAY from '/home/gpadmin/ORD_PAY.txt' with delimiter ',';
ERROR:  value "13082410952" is out of range for type integer  (seg0 sdw1:40000 pid=28652)
CONTEXT:  COPY ord_pay, line 1, column 1

这是因为ORD_ID字段的是INT数据类型，INT是短整型数字类型，支持范围是-2147483648到2147483647，可是加载的数据为11位的数字，明显超出了这个范围，所以会遇到上面的错误，解决方法是将ORD_ID字段的数据类型改为BIGINT，BIGINT是长整型数字类型，支持范围为-9223372036854775808到9223372036854775807，完全可以支持这些数据。

dbdream=# \d ORD_PAY
                Table "public.ord_pay"
   Column    |            Type             | Modifiers 
-------------+-----------------------------+-----------
 id          | integer                     | 
 ord_id      | integer                     | 
 pay_seq     | integer                     | 
 pay_mns_cd  | integer                     | 
 pay_dtm     | timestamp without time zone | 
 rfn_pss_amt | numeric(10,2)               | 
 inst_id     | integer                     | 
 inst_dtm    | timestamp without time zone | 
 mdf_id      | character varying(10)       | 
Distributed by: (id)

dbdream=# alter table ord_pay alter column ORD_ID type bigint;
ALTER TABLE

在修改字段长度后，上面的错误已经解决，但是对于空数据，还是会遇到问题。

dbdream=# copy ORD_PAY from '/home/gpadmin/ORD_PAY.txt' with delimiter ',';
ERROR:  invalid input syntax for type timestamp: ""  (seg0 sdw1:40000 pid=28656)
CONTEXT:  COPY ord_pay, line 1, column 1

这是因为第一行数据的一个时间字段为空，解决这个问题GreenPlum提供了几种方法，一种方法是通过null参数明确指定转义空值的数据，另一种方法是将空数据用\N代替(在下文数据卸载部分会有演示)，从上述准备的数据文件可以看到，空值是由,,表示，下面通过指定null参数进行加载进行演示。

dbdream=# copy ORD_PAY from '/home/gpadmin/ORD_PAY.txt' with delimiter ',' null '';
ERROR:  invalid input syntax for integer: "sys15"  (seg0 sdw1:40000 pid=28727)
CONTEXT:  COPY ord_pay, line 976599, column 976599

可见在指定null参数时，包含空值的数据可以成功加载，但是遇到了字段类型不匹配的情况，也就是错误数据，解决这个问题可以修改数据文件中错误的数据，还可以通过指定记录并跳过错误数据，将正确的数据加载到数据库，这是常用的方法，毕竟大多数情况都不知道数据文件中存在多少错误的数据，直接修改这些数据很麻烦，下面进行演示。

dbdream=# copy ORD_PAY from '/home/gpadmin/ORD_PAY.txt' with delimiter ',' null '' LOG ERRORS INTO ORD_PAY_ERRS SEGMENT REJECT LIMIT 100;
NOTICE:  Error table "ord_pay_errs" does not exist. Auto generating an error table with the same name
WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
NOTICE:  Found 1 data formatting errors (1 or more input rows). Errors logged into error table "ord_pay_errs"
COPY 999999

LOG ERRORS INTO参数指定错误数据记录到哪张表中，这张表并不需要自己创建，GreenPlum如果发现这张表不存在会自动创建。SEGMENT REJECT LIMIT参数指定最大跳过的错误数，如果错误数据超过这个数值限制，加载则会失败，从上面的输出信息可以看到，成功加载了999999条数据，有一条错误数据被记录到了错误日志表中(ORD_PAY_ERRS)。下面看看错误日志表的结构。

dbdream=# \d ord_pay_errs
           Table "public.ord_pay_errs"
  Column  |           Type           | Modifiers 
----------+--------------------------+-----------
 cmdtime  | timestamp with time zone |                --操作时间
 relname  | text                     |                --表名
 filename | text                     |                --文件名
 linenum  | integer                  |                --错误行号
 bytenum  | integer                  |
 errmsg   | text                     |                --错误信息
 rawdata  | text                     |                --整行数据
 rawbytes | bytea                    |                --行大小
Distributed randomly

下面查看下错误日志表中记录的错误数据信息。

dbdream=# SELECT linenum,errmsg,rawdata FROM ord_pay_errs;
 linenum |                       errmsg                |                       rawdata      
---------+---------------------------------------------+-------------------------------------------------------
  976599 | invalid input syntax for integer: "sys15",  |976599,13101819742,101,100,,0.00,sys15,2013/10/18 14:48:
|                              column inst_id | 56,admin
(1 row)
Time: 2.135 ms

从错误日志表的ERRMSG字段可以看到错误信息是说INST_ID字段的数据类型是数字，可是存在包含字符的数据，RAWDATA字段是错误数据的完整数据信息，如果错误数据很少，可以通过INSERT操作把修改后的数据插入到表中，如果错误数据很多，可以把错误数据保存成文件，修改后在通过COPY工具加载到数据库中。

dbdream=# insert into ORD_PAY values(976599,13101819742,101,100,null,0.00,15,'2013/10/18 14:48:56','admin');
INSERT 0 1

下面看下这100万条数据的数据分布情况。

dbdream=# select gp_segment_id,count(*) from ORD_PAY group by 1;
 gp_segment_id | count  
---------------+--------
             2 | 333385
             1 | 333259
             0 | 333356
(3 rows)

可见数据分布比较均匀，基本平均分布在3个Segment节点上。

4.数据卸载

Copy工具不仅可以把数据从文件加载到数据库的表中，也可以将数据从数据库的表中卸载到操作系统的文件中。如下：

dbdream=# copy ORD_PAY to '/home/gpadmin/ord_pay_output.txt' WITH DELIMITER AS ',';
COPY 1000000

下面查看下卸载的文件信息。

[gpadmin@mdw ~]$ head ord_pay_output.txt 
1,13082410952,101,100,\N,0.00,201908,2013-08-24 11:45:35,admin
3,13082411019,101,100,2013-09-02 10:20:00,598.00,410488,2013-08-24 11:45:42,admin
8,13082411042,101,100,2013-08-27 17:50:00,199.00,106600,2013-08-24 11:46:03,admin
2,13082411024,101,100,2013-08-26 17:10:00,179.00,106600,2013-08-24 11:45:38,admin
4,13082411026,101,100,2013-09-03 10:00:00,199.00,104149,2013-08-24 11:45:42,admin
9,13082411025,101,100,2013-09-05 11:00:00,199.00,104524,2013-08-24 11:46:05,admin
13,13082410999,101,100,2013-08-25 18:20:00,199.00,104268,2013-08-24 11:46:17,admin
5,13082411031,101,100,2013-09-02 10:50:00,199.00,107464,2013-08-24 11:45:49,admin
10,13082411043,101,100,2013-08-27 09:10:00,199.00,107858,2013-08-24 11:46:05,admin
14,13082410968,101,100,2013-08-27 10:00:00,298.00,211247,2013-08-24 11:46:17,admin

可见，在使用copy命令卸载数据时，空数据以\N的方式卸载，\N是null的转义，下面使用这个卸载的数据文件进行加载，看看加载后，被设置成\N的字段是否为空。

dbdream=# truncate table ord_pay;
TRUNCATE TABLE
dbdream=# copy ORD_PAY from '/home/gpadmin/ord_pay_output.txt' with delimiter ',' LOG ERRORS INTO ORD_PAY_ERRS SEGMENT REJECT LIMIT 100;
COPY 1000000
dbdream=# select * from ord_pay where id=1;
 id |   ord_id    | pay_seq | pay_mns_cd | pay_dtm | rfn_pss_amt | inst_id |      inst_dtm       | mdf_id 
----+-------------+---------+------------+---------+-------------+---------+---------------------+--------
  1 | 13082410952 |     101 |        100 |         |        0.00 |  201908 | 2013-08-24 11:45:35 | admin
(1 row)

可见在使用COPY工具时，在数据文件中\N表示的为控制。

本文固定链接: https://www.dbdream.com.cn/2016/01/greenplum%e6%95%b0%e6%8d%ae%e5%ba%93%e6%95%b0%e6%8d%ae%e5%8a%a0%e8%bd%bd%e4%b9%8bcopy%e5%91%bd%e4%bb%a4%e7%9a%84%e4%bd%bf%e7%94%a8/ | 信春哥，系统稳，闭眼上线不回滚！