神通MPP数据库的跨库查询

一. 简介

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

神通 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 中。

两表结构如下：

三. 跨库查询语法

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'
1
2

参数：
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;
1

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

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

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

GRANT SELECT ON EXTSERVER servername TO username;
1

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

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

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

REVOKE SELECT ON EXTSERVER servername FROM username;
1

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

5. 加密函数

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

SELECT EXTSENCRYPT(username, password);
1

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);
1
2
3

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

CREATE USER DDD WITH PASSWORD 'ddd123456,';
1

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

GRANT ROLE SYSDBA TO USER DDD;
1

2. 【本地库】

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

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

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

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

ENABLE_EXTS_CURRENT_USER=TRUE
EXTERNAL_SERVER_USER=DDD
EXTERNAL_SERVER_PASSWORD='AAAAAA7778797A7B7C72'
1
2
3

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

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

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

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

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

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

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

1. 【远端】数据存储

（1）【远端】数据存储

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

2. 【本地库】

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

ENABLE_EXTS_CURRENT_USER=FALSE
1

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

sh  stmpp_STMPPd  start  master
1

（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';
1

创建后，可在 SYS_EXTERNAL_SERVER 表中看到添加的外部数据存储服务器：

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

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

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

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