内容简介: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 ,博主黄杉,谢绝转载
执行查询,查询外部表,可以看到查询出来的数据
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';
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 「Flask实战」鱼书项目实战一
- 「Flask实战」鱼书项目实战三
- 「Flask实战」鱼书项目实战四
- 「Flask实战」鱼书项目实战六
- RocketMQ实战系列从理论到实战
- 「Flask实战」flask鱼书项目实战二
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。