GreenPlum数据库数据加载之copy命令的使用

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

DROP TABLE IF EXISTS "public"."bsscost";
CREATE TABLE "public"."bsscost" (
"id" int4,
"deptid" varchar(500) COLLATE "default",
"specialtyid" varchar(500) COLLATE "default",
"opebusinessesid" varchar(500) COLLATE "default",
"acctperiod" varchar(500) COLLATE "default",
"projectid" varchar(500) COLLATE "default",
"subcontractnum" varchar(500) COLLATE "default",
"banknotecost" numeric(28,2),
"userid" varchar(500) COLLATE "default",
"username" varchar(500) COLLATE "default",
"operationtime" varchar(500) COLLATE "default"
) distributed by (id);

2.准备测试数据

539024,648,587.0,4.0,2012-06,239229.0,197066.0,41908.50,2012-06-30
539043,648,587.0,4.0,2012-06,239231.0,197566.0,420.08,2012-06-30
539045,648,587.0,4.0,2012-06,239231.0,197566.0,778.11,2012-06-30
539046,648,587.0,4.0,2012-06,239231.0,197566.0,764.14,2012-06-30
540317,5313,627.0,8.0,2012-06,239563.0,197527.0,8520.00,2012-06-30
540645,5313,597.0,4.0,2012-06,239593.0,197442.0,1020.92,2012-06-30
540715,5314,629.0,6.0,2012-06,239607.0,197456.0,333.23,2012-06-30
541030,5314,540.0,2.0,2012-06,239661.0,197510.0,5432.00,2012-06-30
540266,5313,629.0,4.0,2012-06,239552.0,197405.0,1042.25,2012-06-30
541088,5314,540.0,2.0,2012-06,239664.0,197513.0,5839.72,2012-06-30

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

--将文件bsscost_output导入到数据表bsscost,DELIMITER选项将不同的字符指定为值分隔符
copy bsscost to '/home/yaml/bsscost_output.csv' WITH DELIMITER AS ',';

默认情况下，COPY会在第一个错误处停止操作，指定SEGMENT REJECT LIMIT 会把COPY操作运行在单行错误隔离模式中。如果COPY操作没有达到操作限制，Greenplum会装载所有正确格式化的行并且丢弃错误行。使用LOG ERRORS子句可以捕获Greenplum数据库内部的数据格式化错误。

 copy bsscost  from '/home/yaml/bsscost_output.txt' with(ENCODING 'UTF8')  delimiter ',' null '' LOG ERRORS SEGMENT REJECT LIMIT 10 ROWS;

可通过执行SELECT gp_read_error_log('bsscost')来查看错误行的信息，其中 bsscost为目标表。
4数据卸载
Copy工具不仅可以把数据从文件加载到数据库的表中，也可以将数据从数据库的表中卸载到操作系统的文件中，使用 copy to语句可实现数据的导出

--header指定导出表头，若不需要可把header去掉，copy 后可直接跟要导出的表，也可写sql语句
 copy (select * from bsscost limit 100 ) to '/home/yaml/bsscost_output.txt' with csv header delimiter AS ',';

总结：copy from 将文件的数据复制到表中， copy是非并行的。使用Greenplum master实例在单个进程中加载数据。建议仅对非常小的数据文件使用copy。copy to 可实现数据卸载。

数据载入使用：copy table…from语句
数据导出使用：copy…to语句
常用参数
分隔符：[DELIMITER [ AS ] ‘delimiter’]
处理空列（含有空格符的是不行的）：[NULL [ AS ] ‘null string’]
记录错误数据，错误日志表自动创建： [LOG ERRORS INTO error_table] [KEEP]
允许错误的行数或者百分比，大于指定值导入失败全部回滚：SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]