Greenplum 数据的装载与卸载

最新推荐文章于 2022-08-06 23:38:52 发布

weixin_33749242

最新推荐文章于 2022-08-06 23:38:52 发布

阅读量996

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/crooner/blog/918984

版权

2019独角兽企业重金招聘Python工程师标准>>>

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,apple
13082411024,pear
13082411019,tomato
13082411026,banana
13082411031,avocado
13082411030,mango
13082410945,coconut
[gpadmin@mdw ~]$

加载数据

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

[gpadmin@mdw ~]$ psql gpdb1
psql (8.2.15)
Type "help" for help.

gpdb1=# copy test_products from '/home/gpadmin/test_products_data.txt' with delimiter ',';
COPY 7
gpdb1=# 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,pear
13082411030,mango
13082411026,banana
13082410952,apple
13082411019,tomato
13082411031,avocado
13082410945,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 TABLE
gpdb1=# 
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 TABLE
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=# \timing on
Timing is on.
gpdb1=# insert into visit_rec select * from visit_rec_ext;
INSERT 0 10000
Time: 637.920 ms
gpdb1=#

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 TABLE
Time: 28.360 ms
gpdb1=# 
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 ms
gpdb1=# insert into visit_rec_ext select * from visit_rec;
INSERT 0 10000
Time: 310.303 ms
gpdb1=# \q
[gpadmin@mdw ~]$ pwd
/home/gpadmin
[gpadmin@mdw ~]$ head upload_visit_rec.txt 
aa0000000051,2016-05-08,320100198012128758,86892417968168
aa0000000110,2016-12-02,320100198107047742,86874579371075
aa0000000176,2016-08-27,320100198008136165,86608096812018
aa0000000240,2016-02-28,320100198007183144,86392776479352
aa0000000316,2016-08-08,320100198105156560,86238811922590
aa0000000378,2016-04-27,320100198201292433,86224043004142
aa0000000437,2016-05-27,320100198210055947,86530398601158
aa0000000503,2016-10-06,320100198106289712,86554278368832
aa0000000569,2016-09-07,320100198004307408,86704096376631
aa0000000631,2016-12-08,320100198211162036,86107191955534

7.3 使用 gpload 加载数据

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

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

nano gpload.yml

---
---
VERSION: 1.0.0.1
DATABASE: gpdb1
USER: gpadmin
HOST: mdw
PORT: 5432
GPLOAD:
  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:37
2017-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 30
2017-04-28 17:02:38|INFO|running time: 1.13 seconds
2017-04-28 17:02:38|INFO|rows Inserted          = 10000
2017-04-28 17:02:38|INFO|rows Updated           = 0
2017-04-28 17:02:38|INFO|data formatting errors = 0
2017-04-28 17:02:38|INFO|gpload succeeded
[gpadmin@mdw ~]$ nano gpload.yml

转载于:https://my.oschina.net/crooner/blog/918984