Greenplum Web External Tables事例

Defining Command-Based Web External Tables
执行的命令或者程序需要在主机上已经存在，创建外部表的命令时可以在ON关键字后面指定在哪些节点上执行。
ON ALL
| MASTER
| number_of_segments
| HOST ['segment_hostname']
| SEGMENT segment_id ]
下面给出几个例子
aligputf8=# CREATE EXTERNAL WEB TABLE t1_ext (output text)
aligputf8-# EXECUTE 'python /home/gpadmin1/joe.wangh/1.py'
aligputf8-# FORMAT 'TEXT';
CREATE EXTERNAL TABLE
aligputf8=# select * from t1_ext;
 output 
---------
 hadoop5
 hadoop5
 hadoop6
 hadoop6
 hadoop4
 hadoop4
(6 rows)
aligputf8=# CREATE EXTERNAL WEB TABLE t2_ext (output text)                                                                          EXECUTE 'python /home/gpadmin1/joe.wangh/1.py' on master                                                                            FORMAT 'TEXT';
CREATE EXTERNAL TABLE
aligputf8=# select * from t2_ext;
 output 
---------
 hadoop5
(1 row)
aligputf8=# CREATE EXTERNAL WEB TABLE t3_ext (output text)                                                                          EXECUTE 'python /home/gpadmin1/joe.wangh/1.py' on host 'hadoop6'                                                                    FORMAT 'TEXT';
CREATE EXTERNAL TABLE
aligputf8=# select * from t3_ext;
 output 
---------
 hadoop6
 hadoop6
(2 rows)
aligputf8=# CREATE EXTERNAL WEB TABLE t4_ext (output text)                                                                          EXECUTE 'python /home/gpadmin1/joe.wangh/1.py' on 4                                                                                 FORMAT 'TEXT';
CREATE EXTERNAL TABLE
aligputf8=# select * from t4_ext;
 output 
---------
 hadoop4
 hadoop6
 hadoop5
 hadoop5
(4 rows)

Defining URL-Based Web External Tables

aligputf8=# CREATE EXTERNAL WEB TABLE t5_ext (name1 text,name2 text)                                                                LOCATION (                                                                                                                          'http://10.20.151.9/1.dat',                                                                                                         'http://10.20.151.9/2.dat'                                                                                                          )                                                                                                                                   FORMAT 'TEXT' (DELIMITER ',');
CREATE EXTERNAL TABLE
aligputf8=# select * from t5_ext;
 name1 | name2
-------+-------
 haha  | haha2
 hehe  | hehe2
 xx    | xx2
 uu    | uu2
(4 rows)
文件内容如下
[gpadmin1@hadoop5 htdocs]$ cat 1.dat
haha,haha2
hehe,hehe2
[gpadmin1@hadoop5 htdocs]$ cat 2.dat
xx,xx2
uu,uu2