测试的软件版本

PostgreSQL 8.2.15 (OushuDB 3.4.0.0) (Apache HAWQ 2.4.0.0) (Greenplum Database 4.2.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC clang version 8.0.1 (tags/RELEASE_801/final) compiled on Jan 15 2020 05:46:01

语法

DB=# \h CREATE EXTERNAL TABLE

规范

命名规范

ext_XXXXXXerr_XXXXXX

err表需要定期清理

建议做成存储过程定期清理

建立gpfdist外部表

启动gpfdist服务

nohup gpfdist -d /home/gpadmin -p 8888 > gpfdist.log 2>&1 &

检查gpfdist服务是否存在

-bash-4.2$ ps -ef | grep gpfdistgpadmin  507192 493162  0 01:57 pts/0    00:00:00 gpfdist -d /home/gpadmin -p 8888

建立可写gpfdist外部表

create writable external table ext_w_test001(                             id bigint, name varchar(128)                                                         )                                                                         location('gpfdist://10.0.0.6:8888/gpextdata/test001.txt')  format 'csv'                                                             encoding 'GB18030';

可写测试

insert into ext_w_test001 select generate_series(1,1000,1),1;# 插入数据后登陆服务器10.0.0.6执行下列shell指令 wc -l /home/gpadmin/gpextdata/test001.txt

建立可写gpfdist外部表

create readable external table ext_r_test001(                             id bigint, name varchar(128))                                                                         location('gpfdist://10.0.0.6:8888/gpextdata/test001.txt')  format 'csv'                                                             encoding 'GB18030'log errors into err_r_test001segment reject limit 10 rows;

可读测试

select count(*) from ext_r_test001;select * from ext_r_test001 limit 5;

删除gpfdist外部表

找出对应的外部表和err表

方案一

    External table "public.ext_w_test001" Column |          Type          | Modifiers --------+------------------------+----------- id     | bigint                 |  name   | character varying(128) | Type: writableEncoding: GB18030Format type: csvFormat options: delimiter ',' null '' escape '"' quote '"'External location: gpfdist://10.0.0.6:8888/gpextdata/test001.txtDistributed randomlydw=# \d ext_r_test001     External table "public.ext_r_test001" Column |          Type          | Modifiers --------+------------------------+----------- id     | bigint                 |  name   | character varying(128) | Type: readableEncoding: GB18030Format type: csvFormat options: delimiter ',' null '' escape '"' quote '"'External location: gpfdist://10.0.0.6:8888/gpextdata/test001.txtSegment reject limit: 10 rowsError table: err_r_test001

方案二

SELECT x.location, x.fmttype, x.fmtopts, x.command,                       x.rejectlimit, x.rejectlimittype, x.writable,                             (SELECT relname     FROM pg_class WHERE Oid=x.fmterrtbl) AS errtblname,      pg_catalog.pg_encoding_to_char(x.encoding),x.fmterrtbl = x.reloid AS errortofile                                     FROM pg_catalog.pg_exttable x,      pg_catalog.pg_class c WHERE x.reloid = c.oid AND c.oid = 'public.ext_r_test001'::regclass;

删除外部表(删除之前查看建表语法有没有错误表存在)

drop table err_r_test001;drop external table public.ext_r_test001;drop external table public.ext_w_test001;

关闭gpfdist服务

ps -ef | grep gpfdist | grep -v grep | awk '{print $2}' | xargs killps -ef | grep gpfdist

建立web外部表

...待更新