file外部表--有更新版本！

最新推荐文章于 2023-06-06 09:20:08 发布

gtlions

最新推荐文章于 2023-06-06 09:20:08 发布

阅读量944

点赞数

分类专栏： GPDB Mangement

本文链接：https://blog.csdn.net/gtlions/article/details/8875316

版权

GPDB Mangement 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

如果不想要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。