GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用

怼烎@ 2022-01-09 19:37 567阅读 0赞

Greenplum的外部表和ORACLE的外部表一样，都是数据存储在数据库之外的表。它的外部表除了可以加载本地的数据，还可以通过gpfdist工具并行加载数据。由于本地方式加载的效率低，已经被gpfdist取代。本文先介绍本地方式加载，后介绍gpfdist方式。

### 1、本地方式加载 ###

#### 1.1、创建外部表 ####

rhnschema=# CREATE EXTERNAL TABLE ext_rhn1 (package_id numeric, primary_xml bytea, filelist bytea, other bytea, created timestamp(6) with time zone, modified timestamp(6) with time zone) LOCATION ('file://pg03:5432/u01/rhnpackagerepodata.csv') FORMAT 'CSV';

注意：  
1、数据文件必须位于segment节点；  
2、主机名后面的端口号随便写，也可以不写；  
3、如果文件有头部信息，则在format后面跟上(HEADER)参数；  
4、file后面必须使用主机名，使用IP创建外部表可以成功，但是查询时会报错。  
具体的创建外部表的语法可以参考[官方手册][Link 1]。

#### 1.2、数据装载测试 ####

在装载之前，打开时间记录器，统计下整个加载过程耗时多久。  
![GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用][GreenPlum_gpfdist]  
数据分布情况如下图所示：  
![GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用][GreenPlum_gpfdist 1]  
数据分布相对来讲非常均匀，GreenPlum数据库是自动提交，不需要触发commit命令进行提交(Greenplum下想要回滚操作，比较麻烦，所以尽量不要在GreenPlum数据库做DML测试性的操作)。

### 2、gpfdist的使用 ###

gpfdist可以实现并行加载，需要先启动gpfdist进程及监听端口，这个命令在Master和Segment节点的GPHOME/bin目录下，如果配置了GP的环境变量，可以直接使用，如果在没有安装GP的服务器上使用gpfdist工具，只需要将gpfdist命令的文件拷贝到相应的服务器上即可使用。

#### 2.1 启动gpfdist服务 ####

[gpadmin@pg01 ~]$ gpfdist -d /u01 -p 5555  -l /tmp/gpfdist.log &
    [gpadmin@pg01 ~]$ more /tmp/gpfdist.log 
    2019-06-19 14:20:24 16560 INFO Before opening listening sockets - following listening sockets are available:
    2019-06-19 14:20:24 16560 INFO IPV6 socket: [::]:5555
    2019-06-19 14:20:24 16560 INFO IPV4 socket: 0.0.0.0:5555
    2019-06-19 14:20:24 16560 INFO Trying to open listening socket:
    2019-06-19 14:20:24 16560 INFO IPV6 socket: [::]:5555
    2019-06-19 14:20:24 16560 INFO Opening listening socket succeeded
    2019-06-19 14:20:24 16560 INFO Trying to open listening socket:
    2019-06-19 14:20:24 16560 INFO IPV4 socket: 0.0.0.0:5555
    Serving HTTP on port 5555, directory /u01

#### 2.2 创建外部表 ####

rhnschema=# CREATE EXTERNAL TABLE ext_rhn2 (package_id numeric, primary_xml bytea, filelist bytea, other bytea, created timestamp(6) with time zone, modified timestamp(6) with time zone) LOCATION ('gpfdist://pg01:5555/rhnpackagerepodata.csv') FORMAT 'CSV';
    CREATE EXTERNAL TABLE

在gpfdist下，即可以使用主机名，也可以使用IP。后面的文件路径不能使用绝对路径，因为gpfdist启动时候指定了扫描路径为/u01。  
当查询外部表的总记录数时，报如下错误：

rhnschema=# select count(*) from ext_rhn2;                                                                                              ERROR:  gpfdist error - line too long in file /u01/rhnpackagerepodata.csv near (148241 bytes)  (seg2 slice1 192.168.120.19:40000 pid=20990)
    DETAIL:  External table ext_rhn2, line 3 of gpfdist://pg01:5555/rhnpackagerepodata.csv: ""

出现此错误，使用-m参数重新启动，如下：

[gpadmin@pg01 ~]$ kill -9 `ps -ef|grep gpfdist|grep -v grep|awk '{print $2}'`
    [gpadmin@pg01 ~]$ gpfdist -d /u01 -m 268435456 -p 5555 -l /tmp/gpfdist.log &

\-m参数的值默认是32K，最大为256M，这里设置为最大268435456 bytes(256M)。再次查询正常，如下图:  
![GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用][GreenPlum_gpfdist 2]

#### 2.3 装载数据测试 ####

使用gpfdist测试下装载速度，如下：  
![GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用][GreenPlum_gpfdist 3]  
通过测试可以看到，使用本地文件的方式加载，需要102399ms，而使用gpfdist工具加载数据，需要57361ms。如果测试数据更多，加载的文件更大，速度还会更加明显。  
数据分布情况如下图所示：  
![GreenPlum数据的装载与卸载之外部表以及gpfdist工具的使用][GreenPlum_gpfdist 4]  
数据分布情况和本地文件方式加载的分布情况一样。另外GreenPlum数据库查询数据，先扫描到的数据会直接返回，也就是多次查询的结果可能是不一样的，但是使用gpfdist工具加载，查询结果基本不会是像本地文件加载那样直接从第一条开始有序返回，因为本地加载没有使用并行，在加载的时候数据是从第一条开始有序插入的，而gpfdist工具加载数据是并行加载的，最先插入到数据库的数据并不一定是从第一条数据开始的。

#### 2.4 卸载数据测试 ####

使用可写外部表卸载数据时，如果使用gpfdist工具，就可以实现并行卸载，而且数据不需要经过Master节点，直接由Segment节点写入到外部文件中，效率比较高，卸载大量数据时，使用这种方式会节省大量的时间。  
使用下面的命令创建可写外部表：

rhnschema=# CREATE WRITABLE EXTERNAL TABLE unload_ext_rhn (package_id numeric, primary_xml bytea, filelist bytea, other bytea, created timestamp(6) with time zone, modified timestamp(6) with time zone) LOCATION ('gpfdist://pg01:5555/unload_rhnpackagerepodata.csv') FORMAT 'CSV';
    CREATE EXTERNAL TABLE

由于gpfdist启动过程中，指定了扫描路径为/u01，所以上述gpfdist后面不能跟绝对路径，否则会报错。  
卸载数据如下：

rhnschema=# insert into unload_ext_rhn select *from rhnpackagerepodata;
    INSERT 0 77810

可写外部表，仅支持数据写入操作，并不支持数据的更改和删除操作。也不支持对表进行truncate操作。如果卸载数据时出错，只能删除外部表的外部文件，再重新卸载。

转载于:https://blog.51cto.com/candon123/2411097

[Link 1]: https://gpdb.docs.pivotal.io/5190/ref_guide/sql_commands/CREATE_EXTERNAL_TABLE.html
[GreenPlum_gpfdist]: https://s1.51cto.com/images/blog/201906/19/f4070d21cad736da44452f110026febf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[GreenPlum_gpfdist 1]: https://s1.51cto.com/images/blog/201906/19/59d83593df4342b8571378c90e1dfb79.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[GreenPlum_gpfdist 2]: https://s1.51cto.com/images/blog/201906/19/f5ad2b3fb8c6c9e23a850b266f5f1194.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[GreenPlum_gpfdist 3]: https://s1.51cto.com/images/blog/201906/19/53d905d51ddcc48a4ffbed7073b27b54.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[GreenPlum_gpfdist 4]: https://s1.51cto.com/images/blog/201906/19/e5b3ef9636f56636906e779a52596732.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=