192.168.101.116
gpfdist -d /data02/files/ -p 8081 -l /data02/files/gpfdist.log -m 102400000 &
需要读取的文件:
cat /data02/files/test.txt
1,apple
2,banana
3,pumk
4,plantegg
5,orange
6,water
192.168.101.115
\c hank hank
确保以下两个参数是on,如果不是,会提示没有权限,以下报错
ERROR: permission denied: no privilege to create a readable gpfdist(s) external table
postgres=# show gp_external_enable_exec ;
gp_external_enable_exec
-------------------------
on
(1 row)
postgres=# show gp_external_grant_privileges;
gp_external_grant_privileges
------------------------------
on
修改参数
gpconfig -c gp_external_grant_privileges -v on //允许普通用户创建外部表,重启集群后生效
gpconfig -c gp_external_enable_exec -v on //enable external tables with EXECUTE,重启集群生效
gpstop -a -r
hank=> create external table tb_fruit (id bigint, name text) location ('gpfdist://192.168.101.116:8081/data02/files/test.txt') format 'TEXT' (delimiter ',');
CREATE EXTERNAL TABLE
hank=>select * from tb_fruit;
ERROR: http response code 404 from gpfdist (gpfdist://192.168.101.116:8081/data02/files/test.txt): HTTP/1.0 404 file not found (url.c:354) (seg0 slice1 db-192-168-100-217.sky-mobi.com:40000 pid=15704) (cdbdisp.c:1322)
查看日志:
gfile stat /data02/files/data02/files/test.txt failure: No such file or directory
fstream unable to open file /data02/files/data02/files/test.txt
2017-03-24 15:52:26 4314 WARN [0:14:2:10] reject request from ::ffff:192.168.100.142, path /data02/files/data02/files/test.txt
改为以下:不要写绝对路径
create external table tb_fruit (id bigint, name text) location ('gpfdist://192.168.101.116:8081/test.txt') format 'TEXT' (delimiter ',');
hank=> select * from tb_fruit ;
id | name
----+----------
1 | apple
2 | banana
3 | pumk
4 | plantegg
5 | orange
6 | water