SQL/MED((Management of External Data) )早在 PostgreSQL8.4 版本已谈到,但是到 9.1 版本已经可以创建外部表( foreign tables), 并通过外部表访问外部数据,这也是 SQL/MED 的主要目的之一。 SQL/MED 可以访问多种类型的外部数据,可以是操作系统的文件,也可以是其它类型数据库如 oracle ,MySQL 等,关于支持的外部数据类型,详见 http://wiki.postgresql.org/wiki/Foreign_data_wrappers 这篇 blog 主要讨论如何在数据库里直接访问文件系统上的 CSV 格式的数据文件。
环境准备
在测试库上 copy 表
1 |
skytf=# copy ( select * from skytf.activity limit 10000) to '/home/postgres/script/tf/skytf.activity.csv' ; |
备注:在公司的测试库上取表 skytf.activity 少量数据,注意格式为 csv。
传送文件
将步骤1产生的文件上传到笔记本虚拟机,因为 PostgreSQL9.1 是装在笔记本虚拟机上。
部署 File_fdw
加载 file_fdw 外部模块
1 |
mydb=# create extension file_fdw with schema mydb; |
备注:以 postgres 用户连接虚拟机上的库 mydb, 创建外部模块 file_fdw。
创建 SERVER (外部服务器)
1 |
mydb=# create server ser_file FOREIGN DATA WRAPPER file_fdw; |
注:以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器,是本地库访问外部数据的桥梁。
创建外部表
1 |
mydb=# CREATE FOREIGN TABLE fr_activity ( |
备注:这里创建外部表,并指定外部 Server 为 ser_file,在测试过程中,发现外部表有很多限制, 目前发现了以下:
- default 值不支持;
-
索引不支持;
到了这步,终于完成了外部表创建的所有步骤,接下来验证下是否真的可以访问操作系统文件数据。
数据查询
1 |
mydb=# select * from fr_activity limit 1; |
备注: 果然文件’/home/postgres/script/tf/skytf.activity.csv’ 数据可以直接访问了。
执行计划
1 |
mydb=# explain select count(*) from fr_activity; |
备注:外部表的PLAN里显示了外部文件的信息,包括文件路径,大小等。
附一: CREATE SERVER 语法
CREATE SERVER
Name
CREATE SERVER – define a new foreign server
Synopsis
CREATE SERVER server_name [ TYPE ‘server_type’ ] [ VERSION ‘server_version’ ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option ‘value’ [, … ] ) ]
Description
CREATE SERVER defines a new foreign server. The user who defines the server becomes its owner.
A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings.
The server name must be unique within the database.
Creating a server requires USAGE privilege on the foreign-data wrapper being used.
附二: CREATE FOREIGN TABLE 语法
CREATE FOREIGN TABLE
Name
CREATE FOREIGN TABLE – define a new foreign table
Synopsis
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ NULL | NOT NULL ] }
[, … ]
] )
SERVER server_name
[ OPTIONS ( option ‘value’ [, … ] ) ]
Description
CREATE FOREIGN TABLE will create a new foreign table in the current database. The table will be owned by the user issuing the command.
原创文章,作者:3628473679,如若转载,请注明出处:https://blog.ytso.com/tech/database/236429.html