在第一个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';