GreenPlum 外部表external table 实战

在第一个greenplum集群下面的master服务器上，启动gpfdist服务，在00_mdw启动gpfdist服务
执行启动命令：

nohup $GPHOME/bin/gpfdist -d /home/gpadmin -p 8888 > /tmp/gpfdist.log 2>&1 &

查看启动日志：cat /tmp/gpfdist.log，日志信息如下：

[root@00_mdw ~]# cat /tmp/gpfdist.log
nohup: ignoring input
2017-05-17 14:42:04 21634 INFO Before opening listening sockets - following listening sockets are available:
2017-05-17 14:42:04 21634 INFO IPV6 socket: [::]:8888
2017-05-17 14:42:04 21634 INFO IPV4 socket: 0.0.0.0:8888
2017-05-17 14:42:04 21634 INFO Trying to open listening socket:
2017-05-17 14:42:04 21634 INFO IPV6 socket: [::]:8888
2017-05-17 14:42:04 21634 INFO Opening listening socket succeeded
2017-05-17 14:42:04 21634 INFO Trying to open listening socket:
2017-05-17 14:42:04 21634 INFO IPV4 socket: 0.0.0.0:8888
Serving HTTP on port 8888, directory /home/gpadmin
[root@00_mdw ~]# 

查看后台进程：ps -ef|grep gpfdist，会有一个gpfdist的进程在运行：

[root@00_mdw ~]# ps -ef|grep gpfdist
root     19563 19514  0 17:48 pts/0    00:00:00 grep gpfdist
root     21634     1  0 May17 ?        00:00:00 /opt/greenplum/greenplum-db/./bin/gpfdist -d /home/gpadmin -p 8888
[root@00_mdw ~]# 

然后在 第二个greenplum集群的master上创建外部表，创建的sql语句如下：

create external table public.test001_ext_1 (
id integer,
name varchar(128)
)
location (
/*'gpfdist://192.168.121.53:8888/gpextdata/test001.txt',
'gpfdist://192.168.121.53:8888/gpextdata/test002.txt'*/
'gpfdist://192.168.121.53:8888/gpextdata/*.txt'
)
Format 'TEXT' (delimiter as E'|' null as '' escape 'OFF')
--Encoding 'GB18030' Log errors into public.test001_err segment reject limit 10 rows
;

创建外部表的执行过程：

[root@dwhm01_2_111 ~]# su - gpadmin
[gpadmin@dwhm01_2_111 ~]$ psql -d yueworld_db
psql (8.2.15)
Type "help" for help.

yueworld_db=# create external table public.test001_ext_1 (
yueworld_db(# id integer,
yueworld_db(# name varchar(128)
yueworld_db(# )
yueworld_db-# location (
yueworld_db(# /*'gpfdist://192.168.121.53:8888/gpextdata/test001.txt',
yueworld_db*# 'gpfdist://192.168.121.53:8888/gpextdata/test002.txt'*/
yueworld_db(# 'gpfdist://192.168.121.53:8888/gpextdata/*.txt'
yueworld_db(# )
yueworld_db-# Format 'TEXT' (delimiter as E'|' null as '' escape 'OFF')
yueworld_db-# --Encoding 'GB18030' Log errors into public.test001_err segment reject limit 10 rows
yueworld_db-# ;
CREATE EXTERNAL TABLE
yueworld_db=# 

blog源地址：[http://blog.csdn.net/mchdba/article/details/72522854](http://blog.csdn.net/mchdba/article/details/72522854)，博主黄杉，谢绝转载
执行查询，查询外部表，可以看到查询出来的数据

yueworld_db=# select * from public.test001_ext_1;
 id |    name    
----+------------
  1 | aaa
  2 | badfs
  3 | sdfasdkfji
  4 | dsaisfdaf
 77 | fsdlf
 88 | fdsjoigll;
 99 | g;leknlll
(7 rows)

yueworld_db=# 

判断是否能够及时查询到外部表最新的数据？ 去外部表所在的服务器(也就是 第一个greenplum集群)上的txt文件里面，添加一条记录12|mchtest，

[gpadmin@00_mdw ~]$ vim /home/gpadmin/gpextdata/test001.txt

1|aaa
2|badfs
3|sdfasdkfji
4|dsaisfdaf
12|mchtest

然后再去 第二个greenplum集群里面直接查询外部表，看是否能查询到数据，看到多了一条记录12 | mchtest

yueworld_db=# select * from public.test001_ext_1;
 id |    name    
----+------------
  1 | aaa
  2 | badfs
  3 | sdfasdkfji
  4 | dsaisfdaf
 12 | mchtest
 77 | fsdlf
 88 | fdsjoigll;
 99 | g;leknlll
(8 rows)

yueworld_db=# 

创建可写外部表：

create writable external table public.t01 (  
id integer,
name varchar(128)
)  
 location (  
'gpfdist://externalserver:8090/gpextdata/t01.txt',
'gpfdist:// externalserver:8090/gpextdata/t02.txt' 
)  
 FORMAT 'text' (delimiter ',' null '' escape '\\')  
ENCODING 'GB18030';