神通MPP数据库的跨库查询

客官°小女子只卖身不卖艺 2024-02-18 10:19 13阅读 0赞

#### 神通MPP数据库的跨库查询 ####

*   *  一. 简介
     *  二. 系统表
     *  三. 跨库查询语法
     *   *  1. 创建外部数据存储服务器
         *  2. 删除外部数据存储服务器
         *  3. 授予普通用户访问外部数据存储服务器权限
         *  4. 回收普通用户访问外部数据存储服务器权限
         *  5. 加密函数
         *  6. 访问外部数据存储服务器 ★
     *  四. 跨库查询：统一用户访问流程
     *   *  1. 【远端】数据存储
         *  2. 【本地库】
     *  五. 跨库查询：非统一用户访问流程
     *   *  1. 【远端】数据存储
         *  2. 【本地库】

### 一. 简介 ###

跨库查询，指无需将远端外部数据存储的数据导入到本地数据库，即可通过本地数据库查询远端外部数据存储的数据，满足本地与本地、本地与远端、远端与远端的表的联合查询能力。

神通 MPP5.0 目前支持的远端外部数据存储的类型有两种，神通 MPP5.0 即 STMPP-5.0、神通 KSTORE4.5 即 KSTORE-4.5。

**补充说明**：  
**跨库查询访问有两种方式：① 统一用户访问；② 非统一用户访问**。  
通过设置配置文件参数 `ENABLE_EXTS_CURRENT_USER` 来控制开启或关闭 统一访问功能。

**统一用户访问**是指，用户（MPP5.0）通过 MPP5.0 访问外部存储服务器的权限 和外部存储服务器的用户（与MPP5.0 用户名相同）权限一致；  
使用统一用户访问功能时，需配置使用一个公共的高权限用户（DBA 权限）作为“**桥梁用户**”访问所有外部存储服务器。  
当使用统一用户访问时不能指定 username 和 password，使用非统一用户访问时必须指定 username 和 password。

### 二. 系统表 ###

神通 MPP5.0 将外部数据存储服务器的信息存储在系统表 `SYS_EXTERNAL_SERVER` 中;  
并将外部数据存储服务器的类型的信息存储在系统表 `SYS_EXTERNAL_STORE` 中。

两表结构如下：  
![在这里插入图片描述][68008e24eacd44c68da23865e52ea98e.png]  
![在这里插入图片描述][5b7c8a11313a4d21a1ae839e32a6456b.png]

### 三. 跨库查询语法 ###

#### 1. 创建外部数据存储服务器 ####

用户通过执行此语句，可以创建服务器类型为神通 MPP5.0、神通 KSTORE4.5 的同构数据存储服务器，服务器信息包含：服务器 IP 地址、端口号、数据库名等。

CREATE EXTERNAL SERVER servername TYPE 'servertype' <ext_connection>;
    <ext_connection>::= [CONNECT TO username IDENTIFIED BY password] USING 'hostname' PORT portnum DATABASE 'databasename'

**参数**：  
CREATE EXTERNAL SERVER：创建外部数据存储服务器，创建成功后会在 `SYS_EXTERNAL_SERVER`系统表中显示创建的外部数据存储服务器的信息；  
servername：创建的外部数据存储服务器的名字；  
servertype：创建外部数据存储服务器的类型，可以指定外部数据存储服务器的类型为神通 MPP5.0 即STMPP-5.0、神通 KSTORE4.5 即 KSTORE-4.5；  
ext\_connection：外部数据存储服务器的连接信息，包括服务器 IP 地址、端口号、数据库名、使用的用户名和密码；  
username：用来连接外部数据存储服务器的用户名；  
password：用来连接外部数据存储服务器的密码；  
hostname：外部数据存储服务器的 IP 地址；  
portnum：外部数据存储服务器的端口号；  
databasename：外部数据存储服务器的数据库名字；

#### 2. 删除外部数据存储服务器 ####

DROP EXTERNAL SERVER servername;

servername：删除的外部数据存储服务器的名字。

#### 3. 授予普通用户访问外部数据存储服务器权限 ####

**说明**：  
超级用户通过执行此语句，可以为普通用户授予访问外部数据存储的权限。

GRANT SELECT ON EXTSERVER servername TO username;

servername：外部数据存储服务器名称；  
username：被授予权限的用户名称。

#### 4. 回收普通用户访问外部数据存储服务器权限 ####

超级用户通过执行此语句，可以回收用户访问外部数据存储的权限。

REVOKE SELECT ON EXTSERVER servername FROM username;

servername：外部数据存储服务器名称；  
username：被回收权限的用户名称。

#### 5. 加密函数 ####

本功能的提供一个函数接口，用户通过执行此函数，可以对用户的密码加密。此函数为统一用户访问功能所用。

SELECT EXTSENCRYPT(username, password);

username：统一用户访问的用户名。  
password：统一用户访问的密码。

**注意**：用户名、密码大小写敏感。

#### 6. 访问外部数据存储服务器 ★ ####

通过 SELECT 语句查询外部数据存储服务器上的关系，需要在访问的关系后加上 **`@@servername`**。

### 四. 跨库查询：统一用户访问流程 ###

#### 1. 【远端】数据存储 ####

（1）在【远端】准备一张表

CREATE USER abc WITH PASSWORD 'abc123456,';
    CREATE TABLE abc.t1(A int);
    INSERT INTO abc.t1 VALUES(1);

（2）在【远端】创建用户作为 “桥梁用户”

CREATE USER DDD WITH PASSWORD 'ddd123456,';

（3）授予桥梁用户 dba 权限

GRANT ROLE SYSDBA TO USER DDD;

#### 2. 【本地库】 ####

【本地端库】，需要通过已经创建好的 **远端用户（桥梁用户）** 和密码来访问远端的元数据，因此需要将 **远端用户（桥梁用户）** & 密码配置在【本地库】配置文件中；

配置成功之后，就可以创建**外部数据存储服务器**并访问远端数据了。  
（1）对桥梁用户（远端用户）加密

SELECT EXTSENCRYPT('DDD','ddd123456,'); --得到加密后密码 'AAAAAA7778797A7B7C72'

（2）将桥梁用户（远端用户）信息存到 `MASTER` 节点的 `conf文件` ★

ENABLE_EXTS_CURRENT_USER=TRUE
    EXTERNAL_SERVER_USER=DDD
    EXTERNAL_SERVER_PASSWORD='AAAAAA7778797A7B7C72'

（3）创建外部数据存储服务器 ★  
配置文件设置好并启动之后，需要为远端数据存储创建服务器，以便于在本地即可访问远端的数据。（此时不需要在此指定用户名密码！）

CREATE EXTERNAL SERVER STMPP_SVR TYPE 'STMPP-5.0' USING '192.168.101.73' PORT 2220 DATABASE 'MASTER';

（4）授予普通用户访问外部数据存储服务器的权限

CREATE USER abc WITH PASSWORD 'abc123456,';
    GRANT SELECT ON EXTSERVER stmpp_svr TO abc;

（5）切换普通用户访问远端数据 ★  
**注意**：本地只是将桥梁用户信息存到配置文件中，访问远端数据时还是使用的普通用户！

CONNECT abc/abc123456,; --切换到普通用户 abc
    SELECT * FROM t1@@stmpp_svr; --t1 为远端表   ★ ★ ★

### 五. 跨库查询：非统一用户访问流程 ###

#### 1. 【远端】数据存储 ####

（1）【远端】数据存储

CREATE USER DDD WITH PASSWORD 'ddd123456,';
    CREATE TABLE ddd.t1(A int);
    INSERT INTO ddd.t1 VALUES(1);

#### 2. 【本地库】 ####

【本地端库】，需要通过已经创建好的远端用户和密码来访问远端的元数据，非统一用户访问需要修改参数 `ENABLE_EXTS_CURRENT_USER`，配置在本地库配置文件中，配置成功之后，就可以创建外部数据存储服务器并访问远端数据了。  
（1）配置 MASTER 节点 conf 文件  
`ENABLE_EXTS_CURRENT_USER` 配置默认是开启的，即默认支持统一用户！

ENABLE_EXTS_CURRENT_USER=FALSE

设置成功后，需要重启mpp服务！  
启动脚本位于Linux环境中：`/etc/init.d/stmpp_STMPPd` ，执行启动脚本：

sh  stmpp_STMPPd  start  master

（2）创建外部数据存储服务器 ★  
配置文件设置好并启动之后，需要为远端数据存储创建服务器，以便于在本地即可访问远端的数据。（此时要指定用户名密码！）

CREATE EXTERNAL SERVER STMPP_SVR TYPE 'STMPP-5.0' CONNECT TO DDD IDENTIFIED BY 'ddd123456' USING '192.168.101.73' PORT 2220 DATABASE 'MASTER';

创建后，可在 SYS\_EXTERNAL\_SERVER 表中看到添加的外部数据存储服务器：  
![在这里插入图片描述][cef7fad23ce5415b8dc70b2a482b5a5c.png]

（3）授予普通用户访问外部数据存储服务器的权限

CREATE USER abc WITH PASSWORD 'abc123456,';
    GRANT SELECT ON EXTSERVER stmpp_svr TO abc;

（4）切换普通用户访问远端数据 ★

CONNECT abc/abc123456,; --切换到普通用户 abc
    SELECT * FROM t1@@stmpp_svr; --t1 为远端表 ★ ★ ★

[68008e24eacd44c68da23865e52ea98e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/811feb6e3cfb4bb6b81de8a7ab4a6ded.png
[5b7c8a11313a4d21a1ae839e32a6456b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/7c4614cfa1d14e8a8bbc8030d6d216bc.png
[cef7fad23ce5415b8dc70b2a482b5a5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/49d7d5877ccd4b9590908ea5cf24d5a5.png