流漓 · 客

Appearance

海量数据库外表创建 ​

这里说的是MySQL数据库

如果需要创建oracle外表：请使用 oracle_fdw 插件

mysql_fdw ​

支持创建外部数据封装器mysql_fdw，连接MySQL数据库，并能在外部表上进行查询、插入、更新和删除操作。

注意事项：

  • 两个MySQL外表间的SELECT JOIN不支持下推到MariaDB/MySQL Server执行，会被分成两条SQL语句传递到MariaDB/MySQL Server执行，然后在Vastbase处汇总处理结果
  • 不支持IMPORT FOREIGN SCHEMA语法
  • 不支持对外表进行CREATE TRIGGER操作
  • MySQL的表必须要求有主键或唯一索引

查看主节点 ​

有超级权限就行，不一定非得服务器执行，如果在服务器执行并且服务端是集群才才查看主节点

bash
su - vastbase
hasctl -c /usr/local/has/vastbase.yml list

创建FDW ​

注意只有 超级用户 才能创建FDW，FDW是在DB内生效的，所以需要切换DB，进入DB操作

sql
# 连接数据库，使用vsql默认是vastbase用户
vsql
# 切换数据库
\c database_name
# 确认用户和数据库信息
\c
# 创建FDW【一定要在目标DB中操作，mysql_fdw名称是固定的】
create extension mysql_fdw;
# 给目标用户授权
grant usage on foreign data wrapper mysql_fdw to user_name;
# 查看已经建立的FDW信息
\dew

例

sql
vastbase=# \c database_name
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "database_name",用户 "vastbase".
database_name=# \c
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "database_name",用户 "vastbase".
database_name=# create extension mysql_fdw;
CREATE EXTENSION
database_name=# grant usage on foreign data wrapper mysql_fdw to user_name;
GRANT
database_name=# \dew
                       外部数据封装器列表
   名称    |  拥有者  |     处理函数      |        验证         
-----------+----------+-------------------+---------------------
 file_fdw  | vastbase | file_fdw_handler  | file_fdw_validator
 mysql_fdw | vastbase | mysql_fdw_handler | mysql_fdw_validator
(2 行记录)

定义远程服务器 ​

因为前面已经进行了授权，所以除非特殊说明，下面操作都使用普通用户操作，避免使用超级用户导致后面的权限问题！！！

bash
# 连接数据库【主节点可以不指定主机】
vsql -hxxx.xxx.xxx.xxx -Uuser_name -W'user_password' -ddatabase_name
# 创建链接【mysql_fdw_server 是 远程服务器 的别名，可以自定义,xxx.xxx.xxx.xxx MySQL地址，3306 MySQL端口】
create server mysql_fdw_server foreign data wrapper mysql_fdw options(HOST 'xxx.xxx.xxx.xxx',PORT '3306');
# 查看 外部服务器列表
\des

例

sql
database_name=# \c
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "database_name",用户 "user_name".
database_name=# create server mysql_fdw_server foreign data wrapper mysql_fdw options(HOST 'xxx.xxx.xxx.xxx',PORT '3306');
CREATE SERVER
CREATE USER MAPPING
database_name=# \des
                 外部服务器列表
       名称        |   拥有者     | 外部数据封装器 
------------------+-------------+----------------
 mysql_fdw_server | user_name   | mysql_fdw
 vbaudit_server   |             | file_fdw
(2 行记录)

定义用户映射 ​

因为前面已经进行了授权，所以除非特殊说明，下面操作都使用普通用户操作，避免使用超级用户导致后面的权限问题！！！

在目标库定义用户映射，即本地的用户可以映射为源库的指定用户

sql
# 创建映射
create user mapping for user_name server mysql_fdw_server options(username 'mysql_username',password 'mysql_password');
# 查看用户映射
\deu+

user_name：替换为指定用户名

mysql_fdw_server：远程服务器名，在 定义远程服务器 定义

username：MySQL用户名

password：MySQL用户的密码

例

sql
database_name=# \c
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "database_name",用户 "user_name".
database_name=# create user mapping for user_name server mysql_fdw_server options(username 'mysql_username',password 'mysql_password');
database_name-# \deu+
                                   列出用户映射
      服务器       |  用户名:     |             FDW选项            
------------------+-------------+-----------------------------------------------------------------------------------
 mysql_fdw_server | user_name   | (username 'mysql_username', password 'encrypt........rTWm4a')
(1 行记录)

使用外部表 ​

在真正能访问源库数据之前，我们还需要把源库的表结构同步到目标库上，根据不同的场景会有不同的方法

明确定义外部表 ​

当源表有很多字段，但仅需要几个字段，可以明确定义一个外部表来指定字段，这种场景下只有源表指定字段发生变化，才会影响查询

sql
CREATE foreign TABLE schema_name.table_name  (
  id varchar(32)  NOT NULL,
  orgcode varchar(30),
  orgname varchar(500),
  org_full_name varchar(500),
  orgwords varchar(100),
  orgnameabb varchar(200) 
) server mysql_fdw_server options(DBNAME 'mysql_dataabse_name',table_name 'mysql_table_name');

schema_name：要创建到的海量模式

table_name：在对应模式里显示的表名

mysql_fdw_server：远程服务器名，在 定义远程服务器 定义

DBNAME：MySQL数据库名

table_name：MySQL表名

直接导入外部表 ​

绝大部分时候，仅需要直接使用源表的结构，这种场景下，通过直接导入的方式即可批量建立外部表。

注意：如果源表结构发生变化，大概率可能会造成查询失败，需要重新导入一次表结构

仅导入指定的表

sql
import foreign schema mysql_dataabse_name limit to (table_name1,table_name2,...) from server mysql_fdw_server into schema_name;

一次性导入指定DB下的所有表

sql
import foreign schema mysql_dataabse_name from server mysql_fdw_server into schema_name;

mysql_dataabse_name：替换为源数据库名

table_name1,table_name2,...：替换为需要导入的表

mysql_fdw_server：远程服务器名，在 定义远程服务器 定义

schema_name：海量当前数据库的某模式名

查看外部表信息 ​

查看已有的外部表信息

sql
select * from information_schema.foreign_tables;

例

sql
database_name=# select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name 
-----------------------+----------------------+--------------------+------------------------+---------------------
 database_name         | audit                | vb_audit_log       | database_name          | vbaudit_server
 database_name         | schema_name          | table_name         | database_name          | mysql_fdw_server
(2 行记录)

删除外部表 ​

指定表名称，删除多个表

sql
drop foreign table schema_name.table_name1,schema_name.table_name2;

构造sql语句批量删除

sql
select
	'drop foreign table ' || t.table_schema || '.' || t.table_name || ';' as drop_sql
from information_schema.tables t
where t.table_type in ('FOREIGN') and t.table_schema in ('table_name1', 'table_name2');

删除FDW扩展来删除所有外部表

见 删除数据库插件

其他操作 ​

删除数据库插件 ​

需要超级用户

sql
# 删除数据库插件
drop extension mysql_fdw;
# 如果有其他对象依赖于此插件需要加上cascade关键字
drop extension mysql_fdw cascade;

升级插件 ​

执行以下语句在当前数据库中升级 mysql_fdw 模块

sql
database_name=# alter extension mysql_fdw update;
NOTICE:  version "1.1" of extension "mysql_fdw" is already installed
ALTER EXTENSION