1、外部表介绍

在磐维数据库中，用户可以将外部数据源（如文件系统、外部表、视图等）的数据映射为数据库中的一张表，并对其进行查询、连接和并行操作等，就像对普通表进行操作一样，从而实现跨数据库的数据访问。通过这种方式，可以方便地整合不同数据库系统中的数据，实现数据的共享和协同工作。

下面讲述在磐维数据库中操作远程mysql数据库表的全部过程，希望对大家有所帮助！

2、环境说明

3、环境准备

3.1 外部（mysql）服务器上操作

3.1.1.准备测试用户
mysql> create user extuser1 identified by '******';
Query OK, 0 rows affected (0.02 sec)

3.1.2.准备测试表
mysql> create table testdb.exttab(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into  testdb.exttab values(1,'a');
insert into  testdb.exttab values(2,'b');
Query OK, 1 row affected (0.02 sec)

mysql> insert into  testdb.exttab values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  testdb.exttab values(3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from  testdb.exttab;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.02 sec)

mysql> 
3.1.3 授权
mysql>  GRANT ALL PRIVILEGES ON testdb.exttab TO 'extuser1'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 

3.2 本地（磐维）服务器上操作

3.2.1.准备测试用户和数据库
postgres=# CREATE DATABASE ywdb;
CREATE DATABASE
postgres=# \c ywdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ywdb" as user "omm".
ywdb=# create user localuser password '******';
CREATE ROLE
ywdb=# alter user localuser sysadmin;

4、创建外部表

4.1 创建mysql_fdw扩展

[omm@node1 ~]$ gsql -d ywdb -p 17700 -U localuser -r
Password for user localuser: 
gsql ((PanWeiDB_V2.0-S3.1.0_B01) compiled at 2024-12-19 11:44:29 commit 5d08dc9 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

ywdb=>  CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
ywdb=> 

4.2 创建远程（外部）服务器对象，定义主机地址、端口、数据库名 

ywdb=> CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.*.*.20', port '3306', dbname 'testdb');
ERROR:  invalid option "dbname"
HINT:  Valid options in this context are: host, port, init_command, secure_auth, use_remote_estimate, ssl_key, ssl_cert, ssl_ca, ssl_capath, ssl_cipher

ywdb=>  CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.*.*.20', port '3306');
CREATE SERVER
ywdb=> 

4.3 创建用户到一个外部服务器的新映射

ywdb=> CREATE USER MAPPING FOR localuser SERVER mysql_server OPTIONS (username 'extuser1', password 'Wel%1234');
CREATE USER MAPPING
ywdb=>

4.4 创建外部表

ywdb=> CREATE FOREIGN TABLE F_A( id INT, name VARCHAR(10) ) SERVER mysql_server OPTIONS (dbname 'testdb',TABLE_NAME 'exttab');
CREATE FOREIGN TABLE 
ywdb=> \d
                                List of relations
  Schema   |             Name              |     Type      |   Owner   | Storage 
-----------+-------------------------------+---------------+-----------+---------
 localuser | F_A                           | foreign table | localuser | 
 public    | pg_type_nonstrict_basic_value | view          | omm       | 
 public    | pw_login_info                 | view          | omm       | 
(3 rows)

5、访问外部表

ywdb=>  select * from F_A;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

ywdb=> 

6、碰到的问题

6.1 磐维数据库中创建user mapping报错：

ywdb=> CREATE USER MAPPING FOR localuser SERVER mysql_server OPTIONS (username 'extuser1', password '******');
ERROR:  syntax error at or near "FOR"
LINE 1: CREATE USER MAPPING FOR localuser SERVER mysql_server OPTION...

--查看语法
postgres=# \h create user mapping
Command:     CREATE USER MAPPING
Description: define a new mapping of a user to a foreign server
Syntax:
CREATE USER MAPPING FOR panweidb_user SERVER server_name OPTIONS(
    username 'user name', 
    password 'user password' 
);

说明：当在OPTIONS中出现password选项时，需要保证openGauss每个节点的$GAUSSHOME/bin目录下存在usermapping.key.cipher和usermapping.key.rand文件，如果不存在这两个文件，请使用gs_guc工具生成并使用gs_ssh工具发布到openGauss每个节点的$GAUSSHOME/bin目录下。
用户的口令会加密后保存到系统表PG_USER_MAPPING中，加密时需要使用usermapping.key.cipher和usermapping.key.rand作为加密密码文件和加密因子。首次使用前需要通过如下命令创建这两个文件，并将这两个文件放入各节点目录$GAUSSHOME/bin，且确保具有读权限。gs_ssh工具可以协助您快速将文件放入各节点对应目录下。

生成usermapping.key.cipher和usermapping.key.rand文件：
[omm@node1 ~]$ gs_ssh -c "gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin"
其中-S参数指定default时会随机生成密码，用户也可为-S参数指定密码，此密码用于保证生成密码文件的安全性和唯一性，用户无需保存或记忆。其他参数详见《工具与命令参考》中“服务端工具 > gs_guc工具说明。
参考文章：https://blog.csdn.net/weixin_53596073/article/details/140945228

--再次执行CREATE USER MAPPING
ywdb=> CREATE USER MAPPING FOR localuser SERVER mysql_server OPTIONS (username 'extuser1', password '******');
ERROR:  syntax error at or near "FOR"
LINE 1: CREATE USER MAPPING FOR localuser SERVER mysql_server OPTION...
临时解决办法：
ywdb=> set b_format_behavior_compat_options='set_keyword_as_colname, b_format_escape,enable_user_as_keyword';
SET
长期解决办法：
修改数据库的postgresql.conf文件加入：
b_format_behavior_compat_options='set_keyword_as_colname, b_format_escape,enable_user_as_keyword'

6.2 磐维数据库访问外部表报错：

ywdb=>  select * from F_A;
ERROR:  failed to connect to MySQL: Plugin caching_sha2_password could not be loaded: /root/dependency/mariadb10.5.8/mariadb-10.5.8/mariadb/lib/plugin/caching_sha2_password.so: cannot open shared object file: Permission denied

解决办法：登录mysql数据库，执行：
mysql> ALTER USER 'extuser1'@'%' IDENTIFIED WITH mysql_native_password BY '******';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 

7、参考文档

https://blog.csdn.net/weixin_53596073/article/details/140945228

关于作者：
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~