postgresql FDW概念、用法与原理小结

最近突然遇到了一批使用fdw的场景，整理记录一把。

一、 强大的FDW

       FDW (foreign-data wrapper，外部数据包装器)，可以让我们在PG中使用SQL查询极为丰富的外部数据：

  • 本实例和其他pg实例中的pg库
  • 主流关系型数据库：Oracle、MySQL、SQL Server等
  • NoSQL数据库：ClickHouse、MongoDB、Redis、Neo4j等
  • 外部文件：csv、josn、pg_dump、xml
  • Web文件：S3、Twitter、Facebook等
  • 更多类型参考：Foreign data wrappers – PostgreSQL wiki

二、 FDW四件套

下面的例子希望从本实例的postgres库访问clair库的layer表

首先在目标库创建一个用户，并授权它可以查询layer表

psql -dclair
create user clair_r with password '123456';
grant select on public.layer to clair_r;

按照四部曲：

1. create extension

处理外部数据源的插件，每类数据库各有不同，需要分别安装。

创建语句

create extension postgres_fdw;
  • 使用yum安装的，需要执行yum install postgresql-contrib安装对应版本包
  • 使用源码安装的，需要在源码解压目录编译插件包，否则会有报错
clair=# create extension postgres_fdw;ERROR:  could not open extension control file "/…/base/share/extension/postgres_fdw.control": No such file or directory

解决方法

  • cd /源码解压目录/contrib/postgres_fdw    如果不知道在哪，搜索postgres_fdw.control位置
  • make & make install
  • 不需重启，再次执行create extension postgres_fdw;

对应视图

select * from pg_foreign_data_wrapper;

查看插件

select * from pg_extension ;
或
\dx

删除语句

drop extension postgres_fdw;

2. create server

目标库连接串，要访问哪个ip、端口、db名

创建语句

CREATE SERVER clair_serverFOREIGN DATA WRAPPER postgres_fdwOPTIONS (host '192.83.123.89', port '5432', dbname 'clair');

对应视图

select * from pg_foreign_server;
或
\des

删除语句

drop SERVER foreign_server;

3. create user mapping

用户映射：目标库使用哪个用户、密码，可以单独创建一个用户也可以用现有的

CREATE USER MAPPING FOR postgresSERVER clair_serverOPTIONS (user 'clair_r', password '123456');

对应视图

select * from pg_user_mappings;
或
\deu+

删除语句

DROP USER MAPPING for user_name SERVER server_name;

4. create foreign table

本地外部表对应目标库哪张表或视图，外部表字段可以少于目标表和视图，只取自己需要的

CREATE FOREIGN TABLE fdw_layer(id integer NOT NULL,name character varying(128)
)SERVER clair_serverOPTIONS (schema_name 'public', table_name 'layer');

对应视图 

select * from pg_foreign_table;

删除语句

drop FOREIGN TABLE foreign_table;

5. 查询外部表

\d fdw_layer
select * from fdw_layer;

三、 其他常见fdw用法

1. mysql_fdw

按照四部曲

create extension mysql_fdw;CREATE SERVER mysql_serverFOREIGN DATA WRAPPER mysql_fdwOPTIONS (host '192.83.123.89', port '3306', dbname 'mydb');CREATE USER MAPPING FOR pg_rwSERVER mysql_serverOPTIONS (user 'mydb_r', password '123456');CREATE FOREIGN TABLE fdw_mysql_mytab (id int,name character varying(128)
)
SERVER mysql_server
OPTIONS (dbname 'mydb',table_name 'mytab'
);

查询外部表

psql -Upg_rw
select count(*) from fdw_mysql_mytab;

2. file_fdw

将csv格式的pg错误日志创建为外部表。file_fdw不需要用户映射，因此只有3步

create extension file_fdw;CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw;CREATE FOREIGN TABLE postgres_log
(log_time timestamp,user_name text,database_name text,process_id integer,connection_from text,session_id text,session_line_num bigint
) SERVER log_server
OPTIONS (format 'csv', header 'false', filename '/data/postgresql-01.csv', delimiter ',', null'');

四、 fdw原理

以下内容来自：https://oyo-byte.github.io/2018/08/03/learn_about_pgfdw/

       实现FDW的核心是实现一组回调函数，其中最核心有7个。无论外部数据源自身能力如何, 这7个接口是实现通过外部表对象访问该数据源的必须接口，定义都位于fdwapi.h。

回调函数

PG中的调用时机

作用

GetForeignRelSize

优化器生成访问路径的过程中对外部表估算访问代价时

提供外部表对于计算访问代价所需的基础数据，如表的元组数以及元组的平均长度,并将这些数据保存在输入参数baserel的字段”rows”以及”width”中

GetForeignPaths

生成对外部表访问路径时

生成对目标外部表的访问路径(通过PG中的接口createforeignscanpath()生成)

GetForeignPlan

优化器生成扫描外部表的查询计划节点时

生成访问目标外部表的ForeignScan计划节点(通过PG中的接口make_foreignscan())

BeginForeignScan

执行器即将开始执行ForeignScan算子，进行该算子相关的初始化时

获取执行ForeignScan算子所需的信息，并将它们组织并保存在ForeignScanState中

IterateForeignScan

执行ForeignScan算子过程中需要获取下一元组时

读取外部数据源的一行数据，并将它组织为PG中的Tuple(即TupleTableSlot). 当该回调函数返回一个空的TupleTableSlot结构时, 迭代器停止迭代

ReScanForeignScan

执行Nested Loop过程中需要重置Inner Scan时(即Outter Scan需要向前推进一行时)

将外部数据源的读取位置重置回最初的起始位置

EndForeignScan

ForeignScan算子执行完成时

释放整个ForeignScan算子执行过程中占用的外部资源或FDW中的资源

  • Parser: 包含对SQL的语法解析，语义校验，查询重写
  • Optimizer：生成查询计划
  • Executor：按照火山模型执行查询计划的算子并向上返回数据

https://img-blog.csdnimg.cn/20200117161940130.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlaHV5aV9Jbg==,size_16,color_FFFFFF,t_70

参考

PostgreSQL Insider – How to link to Oracle databases using oracle_fdw (part 1)

http://v0.pigsty.cc/pdf/fdw-pgconf-2019.pdf

学习PostgreSQL的FDW(#1) | oYo-Byte

https://oyo-byte.github.io/2018/10/07/how_to_write_a_pg_fdw/

Foreign data wrappers – PostgreSQL wiki

https://www.postgresql.org/docs/14/postgres-fdw.html

http://www.postgres.cn/docs/9.3/file-fdw.html

查看全文

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.dgrt.cn/a/367413.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章：

https://img-blog.csdnimg.cn/20200117161940130.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlaHV5aV9Jbg==,size_16,color_FFFFFF,t_70

postgresql FDW概念、用法与原理小结

最近突然遇到了一批使用fdw的场景，整理记录一把。 一、 强大的FDW FDW (foreign-data wrapper，外部数据包装器)，可以让我们在PG中使用SQL查询极为丰富的外部数据：
本实例和其他pg实例中的pg库主流关系型数据库：Oracle……

编程日记2023/1/20 2:30:53

FITC-PEG-DBCO，Fluorescein-PEG-DBCO，荧光素-PEG-DBCO

FITC-PEG-DBCO，DBCO-PEG-FITC，Fluorescein-PEG-DBCO，荧光素-聚乙二醇-二苯并环辛炔，荧光素-PEG-DBCO Product specifications： 1.CAS No：N/A 2.Molecular weight：1000，2000&#xff……

编程日记2023/1/20 2:30:20

Python采集周边烤肉店数据，康康哪一家最好吃？

人生苦短，我用Python
这不是天气开始突然大范围降温了吗？
降温就要吃烤肉啊 滋辣滋辣的声音特别好听～
放假吃烤肉真的特别快乐~
天冷了，逛街…… 天冷了，吃烤肉…… 天冷了，喝奶茶……
有温度的冬天&a……

编程日记2023/1/20 2:29:45

2023年你的年度目标OKR制定好了吗？

Tita 新绩效一体化：绩效管理周期综合指南 企业通常非常重视员工评估，但绩效管理不仅仅是一次年度对话。虽然绩效评估是员工过去三个月、六个月甚至 12 个月工作的结果，但绩效管理周期远远超出了员工评估。
事实上，绩效周期由四个……

编程日记2023/1/20 2:27:11

python使用sentinelsat库下载sentinel影像数据

GIS遥感不分家，最近开始找一些影像的下载脚本了，这两天搞定了哨兵和modis的，分别贴一下 鉴于《Python中使用sentinelsat包自动下载Sentinel系列数据》这篇文章已经写得非常全乎，这里就简单补充一下，放个最简单的下载脚……

编程日记2023/1/20 2:25:13

Golang 从菜鸟到大咖的必经之路_GO 语言的转义字符、注释、规范和代码风格要求

目录 一、GO 语言转义字符
A.Golang 常用的转义字符（escape char）:
B.课程练习
二、Go 语言注释：
A.注释（Comment）:
B.Go 语言中的注释类型：
C.注释不会被编译
D.shifttab
三、规范的代码风格要求……

编程日记2023/1/20 2:24:37

计网必会：电子邮件、SMTP协议

文章目录SMTP概念SMTP的操作过程——发送邮件-接收邮件细品：发送邮件与HTTP的对比邮件报文格式和MIME邮件访问协议SMTP概念
SMTP是电子邮件中的主要协议，它能使用TCP可靠数据传输服务，从发送方的服务器向接收方发送邮件， SMTP&am……

编程日记2023/1/20 2:21:36

Python操作文件及其内容的常用方式

Python操作文件及其内容的常用方式 文章目录Python操作文件及其内容的常用方式1：修改文件名1.1：修改指定文件名1.2：修改目录下的所有文件的文件名2：读取文件2.1：读取文件内容2.1.1：按行读取2.1.2&#xff1……

编程日记2023/1/20 2:19:41

SpringBoot+VUE前后端分离项目学习笔记 – 【25 SpringBoot实现1对1、1对多、多对多关联查询】

新增课程Course页面，实现学生选课功能、课程教授老师选择等功能 1. 课程与授课老师是一对一关系 因为course表仅记录了teacherid，而页面需要的是老师的名字 select course.*,sys_user.id from course left join sys_user **on ** course.teacher_id sys……

编程日记2023/1/20 2:19:05

深度理解机器学习1-自然语言处理

深度解析机器学习
第1章　自然语言处理
描述自然语言处理及其应用。解释不同的文本预处理技术。对文本语料库执行文本预处理。解释Word2Vec和GloVe的词嵌入功能。使用Word2Vec和GloVe生成词嵌入。使用NLTK、Gensim和Glove-Python库用于文本预处理以及生成词嵌入。
1.2　自然……

编程日记2023/1/20 2:17:08

反序列化渗透与攻防(五)之shiro反序列化漏洞

Shiro反序列化漏洞
Shiro介绍
Apache Shiro是一款开源安全框架，提供身份验证、授权、密码学和会话管理。Shiro框架直观、易用，同时也能提供健壮的安全性
Apache Shiro 1.2.4及以前版本中，加密的用户信息序列化后存储在名为remember-me的Cookie中。攻击者可以使用Shiro的默……

编程日记2023/4/16 14:52:13

vue2+vue3

vue2vue3尚硅谷vue2vue2 课程简介【02:24】vue2 Vue简介【17:59】vue2 Vue官网使用指南【14:07】vue2 搭建Vue开发环境【13:54】vue2 Hello小案例【22:25】了解： 不常用常用：id 更常用 简单class差值总结vue 实例vue 模板 ： 先 取 &#xff0……

编程日记2023/4/16 14:52:00

【hello Linux】环境变量

目录 1. 环境变量的概念 2. 常见的环境变量 3. 查看环境变量 4. 和环境变量相关的命令 5. 环境变量的组织方式 6. 通过代码获取环境变量 7. 通过系统调用获取环境变量 Linux🌷 在开始今天的内容之前，先来看一幅图片吧！ 不知道你们是否和我一……

编程日记2023/4/16 14:51:36

【Linux基础】常用命令整理

ls命令
-a选项，可以展示隐藏的文件和文件夹-l选项，以列表形式展示内容-h，需要和-l搭配使用，可以展示文件的大小单位ls -lah等同于la -a -l -h
cd命令（change directory）
语法：cd [Linux路径]……

编程日记2023/4/16 14:51:26

客快物流大数据项目（一百一十二）：初识Spring Cloud

文章目录
初识Spring Cloud
一、Spring Cloud简介
二、SpringCloud 基础架构图…

编程日记2023/4/16 14:51:11

C和C++中的struct有什么区别

区别一： C语言中： Struct是用户自定义数据类型（UDT）。 C语言中： Struct是抽象数据类型（ADT），支持成员函数的定义。
区别二：
C中的struct是没有权限设置的&#xff0c……

编程日记2023/4/16 14:50:59

docker的数据卷详解

数据卷 数据卷是宿主机中的一个目录或文件，当容器目录和数据卷目录绑定后，对方修改会立即同步
一个数据卷可以同时被多个容器同时挂载，一个容器也可以被挂载多个数据卷
数据卷作用：容器数据持久化 /外部机器和容器间接通信 /容器……

编程日记2023/4/16 14:50:50

13、Qt生成dll-QLibrary方式使用

Qt创建dll，使用QLibrary类方式调用dll
一、创建项目
1、新建项目->其他项目->Empty qmake Project->Choose 2、输入项目名，选择项目位置，下一步 3、选择MinGW，下一步 4、完成 5、.pro中添加TEMPLATE subdirs&#xff……

编程日记2023/4/16 14:50:38

基于mapreduce 的 minHash 矩阵压缩

Minhash作用： 对大矩阵进行降维处理，在进行计算俩个用户之间的相似度。
比如： 俩个用户手机下载的APP的相似度，在一个矩阵中会有很多很多的用户要比较没俩个用户之间的相似度是一个很大的计算任务 如果首先对这个矩阵降维处理&am……

编程日记2023/4/16 14:50:37

关于hashmap使用迭代器的问题

keySet获得的只是key值的集合，valueSet获得的是value集合，entryset获得的是键值对的集合。 package com.test2.test;import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;public class mapiterator……

编程日记2023/4/16 14:50:37

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注