PG-访问外部数据源数据之file_fdw插件

2024-04-05 16:11:42

SQL/MED(SQL Management of External Data ）

PostgreSQL可以使用SQL/MED特性功能通过SQL语句访问外部数据源的数据。实现类似Oracle dblink的功能。

数据流向图

支持访问的外部数据源类型

文件(file_fdw)

在 PostgreSQL 数据库中访问数据库主机文件，文件需具备一定的格式，如：CSV和TEXT。它需要file_fdw插件扩展支持。

数据库

关系型数据库

在 PostgreSQL 数据库中访问远程的数据库，例如 PostgreSQL，Oracle ，MySQL，SQL Server 等。

非关系型数据库

MongoDB 、Red is 、Cassandra 等非关系型数据库

大数据

ElasticSearch 、Hadoop 等

file_fdw

基于文件类型的外部数据源的访问，PostgreSQL 使用 file_fdw 外部扩展访问本地文件，文件的格式要求为 text 、csv 或者 binary

file_fdw 插件使用步骤

创建 file_fdw 外部扩展
创建 foreign server 外部服务(即：指连接外部数据源的连接信息)
设置本地文件格式为 file_fdw 可识别的格式
创建外部表

配置步骤

创建 file_fdw 插件

需要超级权限用户登录PostgreSQL

CREATE EXTENSION file_fdw;

创建 foreign server 外部服务

CREATE SERVER svc_file FOREIGN DATA WRAPPER file_fdw ;

检查确认

-- 通过 \des 元命令查询当前库中已创建的外部服务
\des

创建测试文件

cat > /tmp/f1.txt <<-EOF
1	a
2	b
3	c
EOF

创建外部表

CREATE FOREIGN TABLE f1_test (
  id int4,
  flag text
) SERVER svc_file
OPTIONS (filename '/tmp/f1.txt', format 'text');

OPTIONS 参数指定 file_fdw 的选项：

filename: 指定要访问的文件路径和名称，需指定文件的绝对路径
format: 指定文件的格式，支持的格式为 text 、csv 、binary 。默认为 text 。
header: 指定文件是否包含字段名称行，此选项仅对 csv 格式有效，通常将数据库表数据导出到文件时才会使用此选项
delimiter：设置字段的分隔符，text 格式的文件字段分隔符默认为 tab 键
encoding：设置文件的编码

查看外部表

-- 通过 \det 元命令查看当前数据库中的外部表列表
\det

通过外部表访问文件数据

select * from f1_test;

外部表实质上不存储数据，只是指向外部数据源的一个链接，可理解成操作系统层面的软链接，数据依旧存储在外部数据源中。通过 file_fdw 外部扩展使 PostgreSQL 数据库就像访问数据库表一样访问外部文件。

目前基于 file_fdw 的外部表仅支持只读，不支持 INSERT/UPDATE/DELETE 操作

file_fdw 应用场景

通过外部表访问 PostgreSQL 数据库的 csv 日志，方便分析数据库运行日志。
1. 配置 postgresql.conf 文件，修改日志格式为csv
2. 其它步骤参考上面配置步骤过程

码农公寓