基于EC的数据处理

功能描述

SQL on other openGauss是Extension Connector的功能之一，支持openGauss数据库实例发送SQL语句给另一个openGauss数据库实例执行，并获取执行结果，执行结果可以参与本地表关联查询或者入本地表或入本地文件系统。

支持openGauss数据类型

SQL on other openGauss目前仅支持以下数据类型：

表 1 SQL on other openGauss支持的数据类型

须知：

• openGauss的编码方式设置为SQL_ASCII时，length()函数返回的是字符串数据的字节数，而不是实际的字符数。因此查询exec_on_extension返回数据的length时请注意，例如：

  select c2,length(c2) from exec_on_extension('libra','select * from a;') as (c1 int, c2 text);

• 对于openGauss返回的数据类型，需要使用上表中对应的类型去接收（在AS子句中指定）。如果openGauss返回的类型不在上表中，或没有按照指定对应关系去接收，则可能会出现结果不正确或转换失败。比如openGauss返回类型VARCHAR(10)需要使用VARCHAR(n)（n>=10）或TEXT来接收。

• 当openGauss端数据类型定义为CHAR(n)时，对于字符串长度小于n的情况会自动补齐空格，当这种数据传输到openGauss并转换为text类型时，字符串尾部的空格保留。返回的第二列就是字符串的字节数，而不是实际字符数。

• 对于TIMESTAMP[(p)] WITH TIME ZONE的数据类型，要求远端数据库的时区和本地数据库的时区设置一致，否则可能出现结果错误。

使用前的对接配置

SQL on other openGauss需要unixODBC-2.3.4及openGauss ODBC，openGauss ODBC需要unixODBC-2.3.0的库支撑。主要流程与SQL on Oracle类似（相同的部分此处不予赘述）：

1. 以操作系统用户omm登录数据库主节点。

   在该节点上执行以下2-7，中间不要切换节点。

2. （可选）配置Data Source密钥文件。

   该密钥文件将用于加密Data Source对象中的敏感字段username/password。若不配置系统会默认使用$GAUSSHOME/bin中server的密钥文件：server.key.cipher和server.key.rand。

   a. 使用gs_guc工具生成密钥文件。

   gs_guc encrypt –M source –K ‘用户密钥串’ –D  ‘密钥文件存放目录’

   • 用户密钥串需至少包含3种字符，且不少于8个字符。
   • 生成后的密钥文件有两个，分别为datasource.key.cipher和datasource.key.rand。文件名称需固定不可变更。
   • datasource.key.cipher和datasource.key.rand这两个文件，需分发到数据库实例各节点的$GAUSSHOME/bin下。

   b. 将密钥文件放入指定位置$GAUSSHOME/bin下。然后使用gs_om ec工具将密钥文件发送到数据库实例其他节点。

   gs_om -t ec -m install --key-files --force

   更多详细信息请参考《工具参考》中“服务端工具 > gs_om”章节。

3. 准备package.zip压缩包，放置于$GAUSSHOME/utilslib/fc_conf/$DSN下。路径不存在部分用户可自行创建，$DSN为以DSN为名的文件夹。DSN名必须由字母，数字，下划线组成。压缩包包含如下内容：

   openGauss-*.*.0-ODBC.tar.gz

   该压缩包可在安装包解压后找到。执行如下命令，打包为package.zip：

   zip -r package.zip openGauss-*.*.0-ODBC.tar.gz

4. 配置（远端）openGauss数据库实例侦听参数。 a. 以操作系统用户omm登录数据库主节点。 b. 设置远端数据库实例的侦听IP（通过remoteip设置无需执行此步骤）。

   在需要接受远程服务的DN上（假设其主机名为Linux-235，IP为10.11.12.16），将其对外提供服务的网卡IP或主机名（英文逗号分隔）添加到侦听列表中去（一般为本机IP，如果列表中已有则可以不用设置），如

   gs_guc reload -Z datanode -N Linux-235 -I all -c "listen_addresses='localhost,10.11.12.16'"

   更详细的说明请参考章节Linux下配置数据源中的7。

   c. 在远端数据库实例DN设置认证方式。

   假定本地openGauss数据库实例的各节点IP地址为：10.11.12.13，10.11.12.14，10.11.12.15，如设置sha256连接认证方式，则可在远端openGauss数据库实例上进行如下设置：

   gs_guc reload -Z datanode -N all -I all -h "host all all 10.11.12.13/32 sha256"
   gs_guc reload -Z datanode -N all -I all -h "host all all 10.11.12.14/32 sha256"
   gs_guc reload -Z datanode -N all -I all -h "host all all 10.11.12.15/32 sha256"

   对于本地openGauss数据库实例有很多节点时，且其IP连续、在一个网段，可进行批量设置，如：

   # 允许IP为10.11.12.x的任何主机进行连接访问
   gs_guc reload -Z datanode -N all -I all -h "host all all 10.11.12.0/24 sha256"
   # 允许IP为10.11.x.x的任何主机进行连接访问
   gs_guc reload -Z datanode -N all -I all -h "host all all 10.11.0.0/16 sha256"

   对于本地openGauss数据库实例有很多节点时，且其IP不连续或不在同网段，则用户可用EC的remoteIP功能进行设置（将需要设置的IP放入文本文件中，例：/opt/host/hostfile（文件路径和文件名可变更，但需要确保有读取权限）），其中每行一个IP，然后再执行如下命令设置IP：

   gs_om -t ec -m add -N /opt/host/hostfile -U username --type=remoteip
   gs_om -t ec -m add -N /opt/host/hostfile -U username --type=remoteip -L  # -L为本地模式，需要在全部节点完成上述步骤并执行该命令。

   1. 重启远端数据库实例（通过remoteip设置无需执行此步骤）。

      gs_om -t stop
      gs_om -t start

5. 设置DSN.ini配置文件。

   在$GAUSSHOME/utilslib/fc_conf/$DSN下生成DSN.ini文件。用户需保证文件内容与格式正确。参考示例如下：

   [libra]                              # DSN
   Driver=mppdbDriver                   # Driver名称
   Description=Sample PostgreSQL DSN    # 描述，可以没有
   Servername=XX.XX.XX.XX               # 待连接的远端openGauss数据库实例的DN的IP地址
   Database=postgres                    # 待连接的远端openGauss数据库实例的数据库名称
   Port=XXXX                            # 待连接的远端openGauss数据库实例的DN端口号
   Sslmode=allow                        # 开启SSL模式
   Fetch=10000                          # 使用服务器游标时，每次从远端拿到的数据量，默认100。可根据内存及查询结果情况调整。

6. 执行EC自动化部署命令。

   gs_om -t ec -m add -N DSN -U username --type=native
   gs_om -t ec -m add -N DSN -U username --type=native -L  # -L为本地模式

   说明：

   使用本地模式时，需要在各个节点上分别执行步骤1-5。

7. 调用EC的restart命令，重启数据库实例，终止om_monitor进程，以使openGauss的进程感知到环境变量的变化。

   gs_om -t ec -m restart -U username
   gs_om -t ec -m restart -U username -L   #-L为本地模式
   gs_om -t stop && gs_om -t start         #仅本地模式下执行

   说明：

   • 使用本地模式时，需要在各个节点上分别步骤1-6。
   • 本地模式不启停数据库实例，因此需要手动执行启停命令。

8. 创建Data Source对象。

   连接数据库，并创建Data Source对象。比如创建对应远端openGauss数据库实例的Data Source对象ds_libra：

   openGauss=# CREATE DATA SOURCE ds_libra type 'MPPDB' OPTIONS (DSN 'libra', USERNAME 'libra_user', PASSWORD 'libra_pwd', ENCODING 'UTF8');

   各字段含义、对象修改方法与SQL on Oracle中的9类似。

9. 连接远端openGauss数据库实例。

   假设Data Source对象ds_libra已经创建好，使用exec_on_extension函数进行连接，比如查询远端openGauss数据库实例中一张表a (c1 int)：

   openGauss=# SELECT * FROM exec_on_extension('ds_libra', 'select * from a;') AS (c1 int);

注意事项

1. 目录$GAUSSHOME/utilslib和其中的文件env_ec使用约束请参考《工具参考》中“服务端工具 > gs_om”章节。
2. 需要注意数据库实例用户对目录$GAUSSHOME/utilslib（包括其中的内容）和文件$GAUSSHOME/bin/datasource.key.*至少具备读写权限。建议在每次使用gs_om ec工具之前确保数据库实例用户的读写权限，否则可能会引起升级、扩容等操作的失败。
3. 用户需要保证package.zip中的内容完整，DSN.ini中的内容正确。安装部署完成后不进行修改，否则搭建好的环境有无法正常使用的风险。
4. 数据库实例管理员创建一个Data Source对象（对应odbc.ini中的某一个DSN），然后将该Data Source对象的使用权限赋予某一个用户，则该用户通过exec_on_extension就可以访问odbc.ini中这个DSN对应的远端数据库了。
5. 目前内存约束不完善，内存压力放在远端DN。查询大数据场景时，可能导致远端数据库实例内存吃紧，从而导致远端数据库实例无法操作。

6. 数据迁移时需要通过as子句指定表结构才能迁移成功。比如，将远端openGauss数据库实例中表a (c1 int)数据迁移至本地表b (c1 int)，使用如下语句：

   openGauss=# CREATE TABLE b AS SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int);
   NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
   HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
   INSERT 0 1

7. omm用户不能进行远程连接。

示例

-- 创建Data Source
openGauss=# CREATE DATA SOURCE librA OPTIONS (dsn 'odbc_librA', username 'mppcom', password 'Gs@123456');
-- 建远程表、插入数据
openGauss=# SELECT * FROM exec_on_extension('librA', 'create table a (c1 int);') AS (c1 text);
 c1 
----
(0 rows)
openGauss=# SELECT * FROM exec_on_extension('librA', 'insert into a values (911);') AS (c1 text);
 c1 
----
(0 rows)
-- 执行计划，如果计划在目标库是多列显示则此处需要以对应的列数返回
-- 此处远端openGauss数据库实例中参数 explain_perf_mode=normal
openGauss=# SELECT * FROM exec_on_extension('librA', 'explain select * from a;') AS (c1 text);
                    QUERY PLAN                    
--------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Node/s: All datanodes
(2 rows)
openGauss=# SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int);
 c1  
-----
 911
(1 row)
-- 此处发送的SQL语句返回两列，但函数exec_on_extension只返回一列，注意靠前匹配原则
openGauss=# SELECT * FROM exec_on_extension('librA', 'select * from a a1 inner join a a2 on a1.c1=a2.c1;') AS (c1 int);
 c1  
-----
 911
 911
(2 rows)
-- 与本地表关联
openGauss=# CREATE TABLE b AS SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
INSERT 0 1
openGauss=# INSERT INTO b SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int);
INSERT 0 1
openGauss=# SELECT * FROM b WHERE b.c1 in (SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int));
 c1  
-----
 911
 911
(2 rows)
-- 其他用户使用该Data Source
openGauss=# CREATE USER tmp_usr IDENTIFIED BY 'Gs@123456';
openGauss=# GRANT USAGE ON DATA SOURCE librA TO tmp_usr;
openGauss=# \c - tmp_usr
openGauss=> SELECT * FROM exec_on_extension('librA', 'select * from a;') AS (c1 int);
 c1  
-----
 911
(1 row)
-- 清除Data Source、表和用户
openGauss=>\c - omm
openGauss=# SELECT * FROM exec_on_extension('librA', 'drop table a;') AS (c1 text);
 c1 
----
(0 rows)
openGauss=# DROP DATA SOURCE librA;
openGauss=# DROP TABLE b;
openGauss=# DROP USER tmp_usr;

异常处理

EC对接openGauss时产生的常见异常，请参见表2。

表 2 常见EC对接openGauss的异常处理

相关链接

CREATE DATA SOURCE，SQL on Oracle，《工具参考》中“服务端工具 > gs_om”章节