测试的软件版本

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_XXXXXX
err_XXXXXX

err 表需要定期清理

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

建立 gpfdist 外部表

启动 gpfdist 服务

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

检查 gpfdist 服务是否存在

-bash-4.2$ ps -ef | grep gpfdist
gpadmin  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_test001
segment 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: writable
Encoding: GB18030
Format type: csv
Format options: delimiter ',' null ''escape'"' quote '"'
External location: gpfdist://10.0.0.6:8888/gpextdata/test001.txt
Distributed randomly

dw=# \d ext_r_test001 
    External table "public.ext_r_test001"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | bigint                 | 
 name   | character varying(128) | 
Type: readable
Encoding: GB18030
Format type: csv
Format options: delimiter ',' null ''escape'"' quote '"'
External location: gpfdist://10.0.0.6:8888/gpextdata/test001.txt
Segment reject limit: 10 rows
Error 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 kill

ps -ef | grep gpfdist

建立 web 外部表

… 待更新