网站聊天代码,销售管理系统包括哪几大模块,网站建设网上书店,办公管理软件定制注: 本文为云贝教育 刘峰 原创#xff0c;请尊重知识产权#xff0c;转发请注明出处#xff0c;不接受任何抄袭、演绎和未经注明出处的转载。 前言
Wrapper#xff08;FDW#xff09;是一项关键特性#xff0c;它赋予数据库用户直接通过SQL语句访问存储于外部数据源的能…注: 本文为云贝教育 刘峰 原创请尊重知识产权转发请注明出处不接受任何抄袭、演绎和未经注明出处的转载。 前言
WrapperFDW是一项关键特性它赋予数据库用户直接通过SQL语句访问存储于外部数据源的能力。遵循SQL/MED标准设计FDW使PostgreSQL能够无缝对接多种异构数据库系统以及非数据库类数据源。
FDW机制由四个核心组件构成
Foreign Data Wrapper特定于各数据源的库定义了如何建立与外部数据源的连接、执行查询及处理其他操作。例如“postgres_fdw”用于连接其他PostgreSQL服务器“mysql_fdw”则专门连接MySQL数据库。Foreign Server在本地PostgreSQL中定义一个外部服务器对象对应实际的远程或非本地数据存储实例。User Mapping为每个外部服务器设置用户映射明确哪些本地用户有权访问并提供相应的认证信息如用户名和密码。Foreign Table在本地数据库创建表结构作为外部数据源中表的映射。对这些“外表”发起的SQL查询将被转换并传递给相应的FDW在外部数据源上执行。
随着FDW生态的日益繁荣各类针对不同数据源的FDW插件层出不穷如postgres_fdw实现PostgreSQL间的互连互通mysql_fdw、oracle_fdw分别用于连接MySQL和Oracle数据库还有file_fdw可用于读取文本文件或CSV等非结构化数据。
接下来我们重点介绍FDW功能使用。 一、外部表
使用PostgreSQL的file_fdw扩展可以创建一个外部表来读取和查询文件系统中的文本或CSV文件。以下是如何通过file_fdw实现外部表的详细步骤
使用场景当有个1G表数据CSV格式需要和本地表运算出报表结果 1.1 准备csv数据
create table testpc (oid int,relname text);
insert into testpc select oid,relname from pg_class limit 100;
COPY (SELECT * FROM testpc) TO /tmp/testpc.csv WITH CSV DELIMITER ,;
1.2 创建外部表
1、--添加扩展
CREATE EXTENSION file_fdw;2、--创建 SERVER FOR file
create server pg_file_server foreign data wrapper file_fdw;3、--创建外部表与外部文件结构一致
create foreign table testpc_file_fdw
(oid int,relname text)
server pg_file_server
options(filename /tmp/testpc.csv,format csv,header true,delimiter ,);
1.3 与本地表进行运算
postgres# explain select count(1) from pg_class a,testpc_file_fdw b where a.oidb.oid;QUERY PLAN
----------------------------------------------------------------------------------------Aggregate (cost28.82..28.83 rows1 width8)- Hash Join (cost9.43. .28.63 rows75 width0)Hash Cond: (a.oid (b.oid)::oid)- Seq Scan on pg_class a (cost0.00..16.96 rows396 width4)- Hash (cost8.50..8.50 rows75 width4)- Foreign Scan on testpc_file_fdw b (cost0.00..8.50 rows75 width4)Foreign File: /tmp/testpc.csvForeign File Size: 2409 b
(8 rows) 二、访问其它集群中的pg库
由于pg内部不同的数据库不能访问pg_fdw就能解决此问题。
注如无特殊说明以下操作均在当前库操作
2.1 创建扩展
create extension postgres_fdw;
2.2 创建外部数据服务 CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 192.168.2.104, dbname postgres, port 5666);
查看外部服务
postgres# \desList of foreign serversName | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------------------------------------------------------------------------------------------------------------------------------------------------pgserver | postgres | postgres_fdw | | | | (host 192.168.2.104, dbname postgres, port 5666) |
(1 row)
删除外部服务
rop SERVER pgserver cascade; 2.3 将外部服务授权给指定用户
grant usage on FOREIGN SERVER pgserver to postgres;
2.4 创建外部服务的mapping
用于连接指向用户及指定数据库
CREATE USER MAPPING FOR postgres SERVER pgserver OPTIONS (userpostgres, passwordpostgres);
查看mapping关系
select * from information_schema.user_mappings;authorization_identifier | foreign_server_catalog | foreign_server_name
-----------------------------------------------------------------------postgres | postgres | pgserver
(1 row)postgres# \deu
List of user mappingsServer | User name
---------------------pgserver | postgres
(1 row)
如果密码配置错误用以下语句修改 ALTER USER MAPPING FOR postgres SERVER pgserver OPTIONS (SET password1qazWSX );
补充
删除外部mapping
DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC } SERVER server_name 2.5 在远端创建表
postgres# create table test1(id int);
CREATE TABLE
postgres# insert into test1 values(1);
INSERT 0 1
postgres# select * from test1;id
----1
(1 row)
postgres# \d test1Table public.test1Column | Type | Collation | Nullable | Default
-----------------------------------------------id | integer | | | 2.6 创建外部表
CREATE FOREIGN TABLE localt11 (id int) SERVER pgserver OPTIONS (table_name test1);
补充
查看外部表
postgres# \detList of foreign tablesSchema | Table | Server
----------------------------public | localt1 | pgserverpublic | localt11 | pgserver
(2 rows) 删除外部表
drop FOREIGN table localt11 ; 2.7 访问自身集群中的pg库
实现过程和第二章节一样
实现目标通过postgres数据库中的postgres用户访问appdb中appuser在schema:appuser中的表t1
\c postgres postgres
postgres# CREATE SERVER appdbserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 192.168.2.103, dbname appdb, port 5666);
CREATE SERVERpostgres# CREATE USER MAPPING FOR postgres SERVER appdbserver OPTIONS (userappuser, password1qazWSX);
CREATE USER MAPPING\c appdb appuser
appdb select * from t1;id
----1234
(4 rows)\c postgres postgres
CREATE FOREIGN TABLE lt2 (id int) SERVER appdbserver OPTIONS (schema_nameappuser,table_name t1);postgres# select * from lt2;id
----1234
(4 rows)
注意
添加外部表时需要指定schme_name参数否则会到默认public模式下找数据 三、访问oracle中的表 3.1 安装oracle客户端 根据不同数据库版本自行到oracle官网下载如LINUX.X64_193000_client_home包
上传解压即可 3.2 配置环境变量
/etc/profileexport ORACLE_HOME/root/oracle
export PATH$ORACLE_HOME/bin:$PATH:/pg14/soft/bin
export LD_LIBRARY_PATH$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN$ORACLE_HOME/network/admin
3.3 配置oracle全局动态库
echo /root/oracle/lib /etc/ld.so.conf
ldconfig 3.4 测试连接oracle
sqlplus sys/oracle192.168.2.115:1521/orclcdb as sysdba 3.5 安装oracle_fdw
下载地址GitHub - laurenz/oracle_fdw: PostgreSQL Foreign Data Wrapper for Oracle
上传解压安装
[rootpg01 oracle_fdw-master]#
cd oracle_fdw-master/
make
make install 3.6 创建oracle_fdw扩展
[pg14pg01 ~]$ psql postgres postgres
psql (13. 8)
Type help for help.postgres# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
如果碰到如下问题
postgres# CREATE EXTENSION oracle_fdw;
ERROR: could not load library /pg14/soft/lib/postgresql/oracle_fdw.so: libclntshcore.so.19.1: cannot open shared object file: No such file or directory
先通过 ldd命令跟踪库文件的调用过程比如
[pg14pg01 ~]$ ldd /pg14/soft/lib/postgresql/oracle_fdw.solinux-vdso.so.1 (0x00007ffefaf42000)libclntsh.so.19.1 /pg14/soft/lib/libclntsh.so.19.1 (0x00007fdbd3063000)libc.so.6 /lib64/libc.so.6 (0x00007fdbd2c95000)libnnz19.so /pg14/soft/lib/libnnz19.so (0x00007fdbd254d000)libdl.so.2 /lib64/libdl.so.2 (0x00007fdbd2349000)libm.so.6 /lib64/libm.so.6 (0x00007fdbd2047000)libpthread.so.0 /lib64/libpthread.so.0 (0x00007fdbd1e2b000)libnsl.so.1 /lib64/libnsl.so.1 (0x00007fdbd1c11000)librt.so.1 /lib64/librt.so.1 (0x00007fdbd1a09000)libaio.so.1 /lib64/libaio.so.1 (0x00007fdbd1807000)libresolv.so.2 /lib64/libresolv.so.2 (0x00007fdbd15ed000)/lib64/ld-linux-x86-63. so.2 (0x00007fdbd7295000)libclntshcore.so.19.1 not foundlibclntshcore.so.19.1 not found
而libclntshcore.so.19.1这个包在oracle客户端的lib目录下可以通过以下方式尝试解决 1、通过软链接
示例
ln -s /root/oracle/lib/libclntsh.so.12.1 /pg14/soft/lib/libclntsh.so.12.1 2、直接cp
示例
root
cp libclntshcore.so.19.1 /pg14/soft/lib/
chown pg14:pg14 /pg14/soft/lib/libclntshcore.so.19.1 3.7 创建对oracle的外部表
postgres# create server oradb_test foreign data wrapper oracle_fdw options(dbserver 192.168.0.215:1521/ora_db);
postgres# grant usage on foreign server oradb_test to postgres;
postgres# create user mapping for postgres server oradb_test options(user MG_APP,password QWERasdf);#创建外部表
postgres# create foreign table orat1
(id int
) server oradb_test options(schema appuser,table t1);
postgres# select * from orat1 limit 10; 四、FDW的演进与未来展望
随着时间推移FDW的功能不断丰富和优化包括增强性能、增加更多类型的FDW以及改善用户体验。如今FDW已经成为PostgreSQL生态不可或缺的一部分大大提高了其数据集成能力。
展望未来FDW将继续成为实现跨多种数据库和非传统数据源交互的关键技术尤其是在大数据时代背景下对于构建复杂的企业级数据湖和数据仓库解决方案具有深远意义。同时随着云原生和分布式计算范式的兴起FDW也将顺应潮流持续演进以适应更加多样化且充满挑战性的应用场景。
