7. 数据的装载与卸载

7.1 使用 copy 命令加载与导出数据

对于数据加载,GreenPlum数据库提供copy工具，copy工具源于PostgreSQL数据库。

copy命令不仅支持表于表之间的数据加载，也支持文件于表之间的数据加载和表对文件的数据卸载。

使用copy命令进行数据加载，数据需要经过Master节点分发到Segment节点，同样使用copy命令进行数据卸载，数据也需要由Segment发送到Master节点，由Master节点汇总后再写入外部文件。

这样就限制了数据加载与卸载的效率，但是数据量较小的情况下，copy命令比较方便。

创建测试用的表

create table test_products (pid text, name text) distributed by(pid);

准备好测试使用的数据

注意：不要在数据底部多留回车符，否则可能会无法导入数据。

[gpadmin@mdw ~]$ cat test_products_data.txt 13082410952,apple13082411024,pear13082411019,tomato13082411026,banana13082411031,avocado13082411030,mango13082410945,coconut[gpadmin@mdw ~]$

加载数据

需要使用 superuser 权限才能使用 copy。

[gpadmin@mdw ~]$ psql gpdb1psql (8.2.15)Type "help" for help.gpdb1=# copy test_products from '/home/gpadmin/test_products_data.txt' with delimiter ',';COPY 7gpdb1=# select * from test_products;     pid     |  name   -------------+--------- 13082411026 | banana 13082411031 | avocado 13082410952 | apple 13082410945 | coconut 13082411024 | pear 13082411030 | mango 13082411019 | tomato(7 rows)gpdb1=#

导出数据

gpdb1=# copy test_products to '/home/gpadmin/to_test_products_data.txt' with delimiter as ',';COPY 7

查看导出的数据

[gpadmin@mdw ~]$ head to_test_products_data.txt 13082411024,pear13082411030,mango13082411026,banana13082410952,apple13082411019,tomato13082411031,avocado13082410945,coconut

7.2 使用 外部表 加载与导出数据

7.2.1 加载数据

具体方法：先把数据文件传至段节点上，然后在数据库中建立外部表，之后再从外部表将数据导入普通的表中。

• 使用 外部表 加载数据

将数据传至段节点

[gpadmin@mdw ~]$ scp to_visit_rec.txt gpadmin@sdw01:/home/gpadmin/to_visit_rec.txt                                           100%  566KB 566.4KB/s   00:00

创建普通表

create table visit_rec(lid text, rq text, sid text, phone text) distributed by(lid);CREATE TABLE

创建外部表

gpdb1=# create external table visit_rec_ext(lid text, rq text, sid text, phone text) location('file://sdw01:4000/home/gpadmin/to_visit_rec.txt') format 'TEXT' (delimiter as ',');CREATE EXTERNAL TABLEgpdb1=# gpdb1=# gpdb1=# select * from visit_rec_ext limit 10;                                                     lid      |     rq     |        sid         |     phone      --------------+------------+--------------------+---------------- aa0000000003 | 2017-02-12 | 320100198111094932 | 86464891414940 aa0000000002 | 2016-06-14 | 320100198201049885 | 86280836662206 aa0000000001 | 2016-12-18 | 320100198108027902 | 86575223283792 aa0000000008 | 2016-09-29 | 320100198005267550 | 86131859836692 aa0000000007 | 2016-07-01 | 320100198209077702 | 86418017471368 aa0000000006 | 2016-04-20 | 320100198210178676 | 86501950511004 aa0000000005 | 2016-03-03 | 320100198211196809 | 86441210343396 aa0000000004 | 2016-06-21 | 320100198104018577 | 86545811842414 aa0000000010 | 2016-11-04 | 320100198202031636 | 86405913792670 aa0000000011 | 2016-11-13 | 320100198103121970 | 86613244807022(10 rows)

将外部表中的数据导入至普通表

gpdb1=# insert into visit_rec select * from visit_rec_ext;INSERT 0 10000

• 使用 gpfdist （具备并行能力）的情况，在数据量较大时建议使用此工具。

启动 gpfdist

注意： -d 后追加 gpfdist 的启动目录（执行路径），-p 为监听端口号。

[gpadmin@mdw ~]$ nohup gpfdist -d /home/gpadmin -p 11234 >/tmp/gpfdist.log &[1] 109946[gpadmin@mdw ~]$ nohup: ignoring input and redirecting stderr to stdout

创建普通表

create table visit_rec(lid text, rq text, sid text, phone text) distributed by(lid);CREATE TABLE

创建外部表

注意：端口为 11234，路径支持 主节点，开始位置为 /home/gpadmin 。

gpdb1=# create external table visit_rec_ext(lid text, rq text, sid text, phone text) location('gpfdist://mdw:11234/to_visit_rec.txt') format 'TEXT' (delimiter as ',');CREATE EXTERNAL TABLEgpdb1=# gpdb1=# select * from visit_rec_ext limit 10;                                                     lid      |     rq     |        sid         |     phone      --------------+------------+--------------------+---------------- aa0000000003 | 2017-02-12 | 320100198111094932 | 86464891414940 aa0000000002 | 2016-06-14 | 320100198201049885 | 86280836662206 aa0000000001 | 2016-12-18 | 320100198108027902 | 86575223283792 aa0000000008 | 2016-09-29 | 320100198005267550 | 86131859836692 aa0000000007 | 2016-07-01 | 320100198209077702 | 86418017471368 aa0000000006 | 2016-04-20 | 320100198210178676 | 86501950511004 aa0000000005 | 2016-03-03 | 320100198211196809 | 86441210343396 aa0000000004 | 2016-06-21 | 320100198104018577 | 86545811842414 aa0000000010 | 2016-11-04 | 320100198202031636 | 86405913792670 aa0000000011 | 2016-11-13 | 320100198103121970 | 86613244807022(10 rows)

将数据导入普通表

gpdb1=# \timing onTiming is on.gpdb1=# insert into visit_rec select * from visit_rec_ext;INSERT 0 10000Time: 637.920 msgpdb1=#

7.2.2 导出数据

直接使用外部表的方式，是类似的（location内的写法不同），这里不再介绍。

首先还是启动 gpfdist。

创建可写外部表, 并导出至可写外部表

gpdb1=# create writable external table visit_rec_ext(lid text, rq text, sid text, phone text) location('gpfdist://mdw:11234/upload_visit_rec.txt') format 'TEXT' (delimiter as ',');CREATE EXTERNAL TABLETime: 28.360 msgpdb1=# gpdb1=# select * from visit_rec limit 10;     lid      |     rq     |        sid         |     phone      --------------+------------+--------------------+---------------- aa0000000008 | 2016-09-29 | 320100198005267550 | 86131859836692 aa0000000013 | 2016-09-21 | 320100198210219043 | 86375866075026 aa0000000022 | 2016-10-13 | 320100198010078177 | 86288151549448 aa0000000035 | 2016-05-04 | 320100198208205526 | 86423837698161 aa0000000044 | 2016-04-01 | 320100198101016985 | 86219813954462 aa0000000057 | 2016-08-01 | 320100198104028944 | 86190739918444 aa0000000066 | 2017-01-11 | 320100198009178176 | 86732260721357 aa0000000071 | 2016-09-07 | 320100198106173289 | 86406121879362 aa0000000079 | 2016-05-26 | 320100198105254367 | 86282287058722 aa0000000080 | 2016-01-27 | 320100198107083521 | 86195065972361(10 rows)Time: 80.288 msgpdb1=# insert into visit_rec_ext select * from visit_rec;INSERT 0 10000Time: 310.303 msgpdb1=# \q[gpadmin@mdw ~]$ pwd/home/gpadmin[gpadmin@mdw ~]$ head upload_visit_rec.txt aa0000000051,2016-05-08,320100198012128758,86892417968168aa0000000110,2016-12-02,320100198107047742,86874579371075aa0000000176,2016-08-27,320100198008136165,86608096812018aa0000000240,2016-02-28,320100198007183144,86392776479352aa0000000316,2016-08-08,320100198105156560,86238811922590aa0000000378,2016-04-27,320100198201292433,86224043004142aa0000000437,2016-05-27,320100198210055947,86530398601158aa0000000503,2016-10-06,320100198106289712,86554278368832aa0000000569,2016-09-07,320100198004307408,86704096376631aa0000000631,2016-12-08,320100198211162036,86107191955534

7.3 使用 gpload 加载数据

GreenPlum数据库除了可以使用copy和外部表的方式加载数据外，还可以使用gpload工具进行数据加载。gpload工具是对外部表的封装，但是不需要在数据库中创建外部表，可以直接将数据从数据库外的文件加载到数据库的表中。

使用gpload工具，需要编写gpload工具的控制文件：

nano gpload.yml

------VERSION: 1.0.0.1DATABASE: gpdb1USER: gpadminHOST: mdwPORT: 5432GPLOAD:  INPUT:    - SOURCE:	LOCAL_HOSTNAME:          - mdw        PORT: 11234        FILE:          - to_visit_rec.txt    - COLUMNS:          - lid: text          - rq:  text          - sid: text          - phone: text    - FORMAT: text    - DELIMITER: ','    - ERROR_LIMIT: 25    - error_table: visit_rec_err  OUTPUT:    - TABLE: visit_rec    - MODE: INSERT

加载数据

[gpadmin@mdw ~]$ gpload -f gpload.yml 2017-04-28 17:02:37|INFO|gpload session started 2017-04-28 17:02:372017-04-28 17:02:37|INFO|setting schema 'public' for table 'visit_rec'2017-04-28 17:02:37|INFO|started gpfdist -p 11234 -P 11235 -f "to_visit_rec.txt" -t 302017-04-28 17:02:38|INFO|running time: 1.13 seconds2017-04-28 17:02:38|INFO|rows Inserted          = 100002017-04-28 17:02:38|INFO|rows Updated           = 02017-04-28 17:02:38|INFO|data formatting errors = 02017-04-28 17:02:38|INFO|gpload succeeded[gpadmin@mdw ~]$ nano gpload.yml