greenplum创建外部表笔记-readable篇

关注

原创最新推荐文章于 2026-08-31 14:52:37 发布 3.2k 阅读 AI 写同款· GEO 优化 ›

Greenplum外部表原理 外部表是greenplum的一种数据表，它与普通表不同的地方是：外部表是用来访问存储在greenplum数据库之外的数据。如普通表一样，可使用SQL对外部表进行查询和插入操作。外部表主要用于Greenplum数据的导入及导出。本文按照以下顺序介绍外部表：外部表创建和使用，外部表读写实现机制，外部数据转换，外部表的查询计划，外部表的事务，使用可读外部表加载数据。一 外部表创建和使用 Gre... 阅读详情

外部表的应用：
1.创建可读外部表例子：
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(10),
work_area varchar(10),
entry_date date,
work_position varchar(10),
salary_amount decimal(18,2),
phone_number varchar(11),
e_mail varchar(50)
)
LOCATION ('gpfdist://192.168.245.128:8099/data1/gpfdist/employee.txt')
FORMAT 'TEXT'(DELIMITER '|+|')
ENCODING 'UTF-8'
LOG ERRORS INTO ERROR_EMPLOYEE SEGMENT REJECT LIMIT 1000 rows
;^C
[gpadmin@mdw gpfdist]$ psql -d gpload_testdb -f employee.txt
psql:employee.txt:16: NOTICE: Error table "error_employee" does not exist. Auto generating an error table with the same name

psql:employee.txt:16: ERROR: delimiter must be a single ASCII character, or 'off'

这部分的错误是由于分割符的问题，应该是不支持‘|+|’这种分割方式，下面换成|竖线分割

[gpadmin@mdw gpfdist]$ cat>employee.txt
^C
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE TEST.EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(32),
work_area varchar(32),
entry_date date,
work_position varchar(32),
salary_amount decimal(18,2),
phone_number varchar(32),
e_mail varchar(50)
)
LOCATION ('gpfdist://192.168.245.128:8099/employee.txt')
FORMAT 'TEXT'(DELIMITER '|')
ENCODING 'GBK'
LOG ERRORS INTO TEST.ERROR_EMPLOYEE SEGMENT REJECT LIMIT 1000 rows
;
^C

这里使用的是gpfdist协议方式创建外部表

LOCATION ('gpfdist://192.168.245.128:8099/employee.txt')内部的

gpfdist://192.168.245.128:8099/employee.txt读取的是gpfdist -d directory里面的文件，这里不需要指定全路径，只需要指定directory内部的文件

encoding是设置编码格式

LOG ERRORS INTO TEST.ERROR_EMPLOYEE SEGMENT REJECT LIMIT 1000 rows 这里使用rows 选项，是指定错误记录行数，错误行的计录数是每个segment的，不是一个整个数据库系统的。

[gpadmin@mdw gpfdist]$ psql -d gpload_testdb -f employee.txt
psql:employee.txt:17: NOTICE: Error table "error_employee" does not exist. Auto generating an error table with the same name
CREATE EXTERNAL TABLE
然后创建成功

2.开启gpfdist服务
[gpadmin@mdw gpfdist]$ gpfdist -d /data1/gpfdist/ -p 8099 -l /home/gpadmin/log &

[1] 9015

也可以使用nohup gpfdist -d /data1/gpfdist/ -p 8099 >> /home/gpadmin/log &

这种方式去挂起gpfdist服务

当然，也可以在一台segment节点上起多个gpfdist服务

参数说明，-d表示数据文件目录，-p表示端口号，-l表示日志文件

/home/gpadmin/log 这个log是一个文件，不是一个目录

[gpadmin@mdw gpfdist]$ Serving HTTP on port 8099, directory /data1/gpfdist

[gpadmin@mdw gpfdist]$
[gpadmin@mdw gpfdist]$
[gpadmin@mdw gpfdist]$
[gpadmin@mdw gpfdist]$ jobs
[1]+ Running gpfdist -d /data1/gpfdist/ -p 8099 -l /home/gpadmin/log &

gpload_testdb=#create table test.employee (like test.ex_employee);

gpload_testdb=# insert into test.employee select * from test.ex_employee;
INSERT 0 14

gpload_testdb=# show server_encoding;
server_encoding
-----------------
UTF8
(1 row)

gpload_testdb=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)

查询表数据
gpload_testdb=# select * from test.employee;
employee_id | employee_name | entry_area | work_area | entry_date | work_position | salary_amount | phone_number | e_mail
-------------+---------------+------------+-----------+------------+---------------+---------------+--------------+--------
000001 | 涓€ | 鍖椾含 | 鍖椾含 | 2016-01-01 | technician | 10000.00 | 18511130000 | NULL
000002 | 浜 | 鍖椾含 | 鍖椾含 | 2016-01-01 | technician | 10000.00 | 18511130001 | NULL
000003 | 涓 | 鍖椾含 | 鍖椾含 | 2016-01-01 | manager | 20000.00 | 18511130002 | NULL
000004 | 鍥 | 鍖椾含 | 鍖椾含 | 2016-01-04 | sales | 10000.00 | 18511130003 | NULL
000005 | 浜 | 鍖椾含 | 鍖椾含 | 2016-01-05 | sales | 10000.00 | 18511130004 | NULL
000006 | 鍏 | 澶╂触 | 鍖椾含 | 2016-01-05 | sales | 10000.00 | 18511130005 | NULL
000007 | 涓 | 澶╂触 | 娣卞湷 | 2016-01-05 | sales | 10000.00 | 18511130006 | NULL
000008 | 鍏 | 澶╂触 | 澶╂触 | 2016-01-08 | sales | 10000.00 | 18511130007 | NULL
000009 | 涔 | 澶╂触 | 澶╂触 | 2016-01-09 | sales | 10000.00 | 18511130008 | NULL
000010 | 鍗 | 澶╂触 | 娣卞湷 | 2016-01-09 | sales | 10000.00 | 18511130009 | NULL
000011 | 鍗佷竴 | 骞垮窞 | 娣卞湷 | 2016-01-09 | sales | 10000.00 | 18511130010 | NULL
000012 | 鍗佷簩 | 骞垮窞 | 骞垮窞 | 2016-01-12 | sales | 10000.00 | 18511130011 | NULL
000013 | 鍗佷笁 | 骞垮窞 | 骞垮窞 | 2016-01-13 | sales | 10000.00 | 18511130012 | NULL
000014 | 鍗佸洓 | 骞垮窞 | 涓婃捣 | 2016-01-14 | sales | 10000.00 | 18511130013 | NULL
(14 rows)
设置客户端字符集session级别
gpload_testdb=# \encoding GBK
gpload_testdb=# select * from test.employee;
employee_id | employee_name | entry_area | work_area | entry_date | work_position | salary_amount | phone_number | e_mail
-------------+---------------+------------+-----------+------------+---------------+---------------+--------------+--------
000001 | 一 | 北京 | 北京 | 2016-01-01 | technician | 10000.00 | 18511130000 | NULL
000002 | 二 | 北京 | 北京 | 2016-01-01 | technician | 10000.00 | 18511130001 | NULL
000003 | 三 | 北京 | 北京 | 2016-01-01 | manager | 20000.00 | 18511130002 | NULL
000004 | 四 | 北京 | 北京 | 2016-01-04 | sales | 10000.00 | 18511130003 | NULL
000005 | 五 | 北京 | 北京 | 2016-01-05 | sales | 10000.00 | 18511130004 | NULL
000006 | 六 | 天津 | 北京 | 2016-01-05 | sales | 10000.00 | 18511130005 | NULL
000007 | 七 | 天津 | 深圳 | 2016-01-05 | sales | 10000.00 | 18511130006 | NULL
000008 | 八 | 天津 | 天津 | 2016-01-08 | sales | 10000.00 | 18511130007 | NULL
000009 | 九 | 天津 | 天津 | 2016-01-09 | sales | 10000.00 | 18511130008 | NULL
000010 | 十 | 天津 | 深圳 | 2016-01-09 | sales | 10000.00 | 18511130009 | NULL
000011 | 十一 | 广州 | 深圳 | 2016-01-09 | sales | 10000.00 | 18511130010 | NULL
000012 | 十二 | 广州 | 广州 | 2016-01-12 | sales | 10000.00 | 18511130011 | NULL
000013 | 十三 | 广州 | 广州 | 2016-01-13 | sales | 10000.00 | 18511130012 | NULL
000014 | 十四 | 广州 | 上海 | 2016-01-14 | sales | 10000.00 | 18511130013 | NULL

(14 rows)

以上是gpfdist加载外部数据的用法，当然也可以使用gpfdists工具，这个是gpfdist的安全版本，支持ssl加密

--------------------------------------------------------------------------------------------------------

以上是今天的笔记，比较粗，望大神指点一二，谢谢

----------------------------

gp初学者

收录于