file外部表简析
file外部表简析
如果不想要gpfdist来当做服务器，还有一种简单的方式-file，直接基于文件路径的方式。
[gpadmin@d1 ~]$ cat nohup.out
Serving HTTP on port 8081, directory /data1/cdrdata
Serving HTTP on port 8082, directory /data2/cdrdata
gtlions=# create external table e_file1(txt text) location ('file://d1:5432/home/gpadmin/nohup.out') format 'text' (delimiter 'off'); CREATE EXTERNAL TABLE
gtlions=# select * from e_file1; txt
-----------------------------------------------------
Serving HTTP on port 8081, directory /data1/cdrdata
Serving HTTP on port 8082, directory /data2/cdrdata
(2 rows)
gtlions=# drop external table e_file1;
DROP EXTERNAL TABLE
gtlions=# create external table e_file1(txt text) location ('file://d1-1:5432/home/gpadmin/nohup.out','file://d1-1:5432/home/gpadmin/nohup.out2') format 'text' (delimiter 'off');
CREATE EXTERNAL TABLE
gtlions=# select * from e_file1;
txt
-----------------------------------------------------
Serving HTTP on port 8081, directory /data1/cdrdata
Serving HTTP on port 8082, directory /data2/cdrdata
Serving HTTP on port 8081, directory /data1/cdrdata
Serving HTTP on port 8082, directory /data2/cdrdata
(4 rows)
gtlions=# create external table e_file1(txt text) location ('file://d1-1:5432/home/gpadmin/nohup.out','file://d1-1:5432/home/gpadmin/nohup.out2','file://d1-1:5432/home/gpadmin/nohup.out1','file://d1-1:5432/home/gpadmin/nohup.out3') format 'text' (delimiter 'off');
CREATE EXTERNAL TABLE
gtlions=# select * from e_file1; ERROR: Could not assign a segment database for "file://d1-1:5432/home/gpadmin/nohup.out3". There are more external files than primary segment databases on host "d1-1"
提示说超过的文件数的限制，这个限制参数在视图中可以查询得到：
gtlions=# select * from pg_max_external_files;
hostname | maxfiles
----------+----------
d3-1 | 3
d4-1 | 3
d1-1 | 3
d1-2 | 3
d3-2 | 3
d2-2 | 3
d2-1 | 3
d4-2 | 3
(8 rows)
被限制到3个了，暂时没找到在什么地方可以修改这个数量限制。
还有一个视图gp_segment_configuration可以查看seg的name。
