openGauss FDW(Foreign Data Wrappers)

        FDW(Foreign Data Wrappers)插件允许在openGauss里访问其他异构数据库的表，openGauss支持Foreign Data Wrappers for oracle （oracle_fdw），Foreign Data Wrappers for MySQL（mysql_fdw）和Foreign Data Wrappers for PostgreSQL（Postgres_fdw），从而支持在openGauss中访问异构其他数据库。
        使用postgres_fdw插件不需要重新编译openGauss，具有系统管理员权限用户直接使用create extension创建扩展组件，普通用户即可create server配置异构数据库连接参数，create user mapping创建异构用户映射关系，CREATE FOREIGN TABLE创建指定数据库的外表。
        使用oracle_fdw和mysql_fdw插件需要安装相应数据库的客户端包，同时需要重新编译openGauss，在configure时配置enable_mysql_fdw和enable_oracle_fdw。数据库里创建扩展与上面使用postgres_fdw类似。

Tips：
        如果编译安装时，没有加入–enable-mysql-fdw选项，可以在openGauss安装完成后，再次编译mysql_fdw，然后手动将编译产物mysql_fdw.so放到对应的安装目录 lib/postgresql/，将 mysql_fdw–1.0–1.1.sql，mysql_fdw–1.1.sql，mysql_fdw–1.0.sql，mysql_fdw.control 放到对应的安装目录 share/postgresql/extension/ 即可。【Oracle也是一样操作】

        另外，感谢“多米爸比”的文章分享(https://www.modb.pro/db/37650), 本文是对该文章实验操作的复现，并且做了相应的完善，希望能帮助到有需要的小伙伴。

软件环境

操作系统：    CentOS 7.6
数据库版本： openGauss 1.1.0 源码(截止2021年02月21日)
Oracle版本： Oracle 11.2.0.4
MySQL版本： MySQL5.7.32
GCC版本：     7.3.0  (原文档要求的gcc-8.2.0因为存在BUG，已经替换为gcc-7.3.0)
其他系统环境初始化配置请参考相关文档自行操作，此文略。

一、数据库客户端包安装

1. MySQL(Mariadb)头文件

        opengauss源码编译开启enable_mysql_fdw需要依赖头文件mariadb_com.h，需要安装软件包：mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm

## 下载依赖的RPM包
wget http://yum.mariadb.org/5.5/centos7-amd64/rpms/MariaDB-common-5.5.68-1.el7.centos.x86_64.rpm
wget http://repo.okay.com.mx/centos/7/x86_64/release/crypto-policies-20170816-1.git2618a6c.el7.noarch.rpm
wget http://repo.okay.com.mx/centos/7/x86_64/release/openssl11-libs-1.1.0i-1.el7.x86_64.rpm
wget http://repo.okay.com.mx/centos/7/x86_64/release/mariadb-connector-c-3.0.10-1.el7.x86_64.rpm
wget http://repo.okay.com.mx/centos/7/x86_64/release/mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm

## 安装RPM包
rpm -ivh MariaDB-common-5.5.68-1.el7.centos.x86_64.rpm  ## [如已安装mariadb-libs则可以不用安装该软件，会有冲突]
rpm -ivh crypto-policies-20170816-1.git2618a6c.el7.noarch.rpm
rpm -ivh openssl11-libs-1.1.0i-1.el7.x86_64.rpm
rpm -ivh mariadb-connector-c-3.0.10-1.el7.x86_64.rpm
rpm -ivh mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm

2. Oracle客户端包

下载地址：https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

yum install oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm 
yum install oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm 

安装完上面两个包后，修改下动态库环境变量：

# vi /etc/ld.so.conf
---------------------------------------
include ld.so.conf.d/*.conf
/usr/lib/oracle/11.2/client64/lib
---------------------------------------
# ldconfig

二、openGauss-sever 源码编译

1. 安装依赖包

yum install -y autoconf gcc gcc-c++ libaio-devel ncurses-devel pam-devel libffi-devel libtool unzip python
yum install -y java-1.8.0-openjdk* psmisc bzip2 cmake rsync python3  python3-devel libxml2-devel
yum install -y flex bison readline readline-devel glibc-devel patch lksctp*
yum install wget net-tools dwz zip libtirpc -y
yum install perl-parent \
perl-HTTP-Tiny \
perl-podlators \
perl-Pod-Perldoc \
perl-Pod-Escapes \
perl-Text-ParseWords \
perl-Encode \
perl-Pod-Usage \
perl-libs \
perl-macros \
perl-Time-HiRes \
perl-constant \
perl-Socket \
perl-Time-Local \
perl-Carp \
perl-Storable \
perl-PathTools \
perl-Scalar-List-Utils \
perl-Exporter \
perl-threads-shared \
perl-threads \
perl-Filter \
perl-File-Temp \
perl-File-Path \
perl-Pod-Simple \
perl-Getopt-Long \
perl-srpm-macros \
perl -y
yum install gpm-devel mpfr-devel  libmpc-devel glibc-devel*.i686 libgcc*.i686 unixODBC unixODBC-devel libtool-ltdl-devel libtool -y   ## 编译第三方软件是需要

2. 修改默认Python3版本

## 查看Python3版本
python3 -V
## 查看默认Python版本
python -V
## 修改默认Python版本
mv /usr/bin/python /usr/bin/python_bak2
ln -s /usr/bin/python3 /usr/bin/python
python -V

3. 获取软件包并解压( 源码包时间：2021.02.02 )

mkdir /soft
chown 775 /soft
cd /soft
## 下载openGauss源码包(当前版本源码约167MB)
wget https://gitee.com/opengauss/openGauss-server/repository/archive/master.zip?ref=master&sha=c0220c3e0b3e98c5a7190450f5b7a9b881857f9b&format=zip&captcha_type=yunpian&token=7766e7b2d03d4ed28cab45e9f10d430f&authenticate=d676486351b6409797aa23a8e9f21236
tail -fn 200 wget-log   ## 查看wget下载进度
mv master.zip\?ref\=master  opengauss-openGauss-server-master.zip   ## 重命名openGauss源码包名称

## 下载第三方软件源码包(约809MB)
wget https://gitee.com/opengauss/openGauss-third_party/repository/archive/master.zip?ref=master&sha=18ea255e57c34094120ed5c7e25dd6dcb9ee6f8e&format=zip&captcha_type=yunpian&token=d686ab4d471145d9b7250a6dfe996dcf&authenticate=63f944174c924a4db4cd83a3842a90e9
tail -fn 200 wget-log   ## 查看wget下载进度
mv master.zip\?ref\=master opengauss-openGauss-third_party-master.zip   ## 重命名第三方源码包名称

## 下载gcc-7.3.0(约107MB) 
wget http://mirror.hust.edu.cn/gnu/gcc/gcc-7.3.0/gcc-7.3.0.tar.gz  ## (原官网文档要求的gcc-8.2.0因为存在BUG，已经替换为gcc-7.3.0，建议使用华中科大的gcc源下载速度快)

## 解压软件包
cd /soft/
unzip opengauss-openGauss-server-master.zip
unzip opengauss-openGauss-third_party-master.zip

4. [可选]编译构建第三方软件

注意：我们也可以不执行这一步的编译工作，直接使用官方已经编译好的第三方lib库，避免重新编译构建lib库消耗大量时间。
        建议小伙伴如非必要尽量别自行编译源码、浪费大量的安装时间和故障排查时间，基本每隔一段时间的编译源码都会碰到不一样的问题，源码更新太快，没必要这么折腾。
        官方提供已编译好的第三方lib库地址：
        https://opengauss.obs.cn-south-1.myhuaweicloud.com/1.1.0/openGauss-third_party_binarylibs.tar.gz

4.1 编译安装依赖包

#########################编译安装gcc-7.3.0(1.1.0版本的官方文档要求用户自行编译安装gcc，旧版本文档的要求仅是拷贝至指定路径即可)#####################
## 依赖软件安装，相关软件下载地址：http://gcc.gnu.org/pub/gcc/infrastructure/
wget https://gcc.gnu.org/pub/gcc/infrastructure/isl-0.18.tar.bz2
tar -jxvf isl-0.18.tar.bz2
cd /soft/isl-0.18
./configure
make && make install
vi /etc/ld.so.conf
---------------------
## Add:
/usr/local/lib
---------------------
ldconfig

## 编译安装gcc-7.3.0
tar -zxvf gcc-7.3.0.tar.gz
cd /soft/gcc-7.3.0
./configure
nohup make -j4 && make install &
tail -f nohup.out

#########################编译安装CMake(版本>=3.4.3)#####################
下载地址：https://github.com/Kitware/CMake/tags
tar -zxvf cmake-3.10.3.tar.gz
cd /root/cmake-3.10.3
./configure
gmake && gmake install

4.2  执行一键式第三方源码编译

## 确认默认Python版本是Python3
[root@opengauss1 ~]# python -V
Python 3.6.8

## 开始编译
cd /soft/openGauss-third_party/build
nohup sh build_all.sh &      
tail -f nohup.out

## 查看各模块编译进度
tail -f buildtools_build.log
tail -f dependency_build.log
tail -f platform_build.log

错误处理：

## 错误：“g++: fatal error: Killed signal terminated program cclplus” 
原因：脚本中的编译过程都添加了-sj参数，并发数太大导致错误。
解决办法：编译过程中降低make 并发数，或者直接使用make命令。使用一键式脚本的话需要修改脚本。

## 错误：”out of memory allocating xxx bytes after a total of xxx bytes”
报错原因：脚本中的编译过程都添加了-sj参数，同时机器配置较低，内存不足，并发数太大导致错误。
解决办法：编译过程中降低make 并发数，或者直接使用make命令。使用一键式脚本的话需要修改脚本。

## 错误：”Host GCC version must be at least 5.1, your version is 4.8.5“
报错原因： 虽然源码安装了gcc-7.3.0，但是系统残留默认的gcc-4.8.5
解决办法：卸载已有的旧版本gcc-4.8.5
根据依赖卸载：
rpm -e libtool-2.4.2
rpm -e gcc-c++-4.8.5
rpm -e gcc-4.8.5
yum install libtool-2.4.2 -y

## 其他错误还没有列出来，手动处理的问题较多，小伙伴碰到的问题不一定和我的一样，具体问题具体处理吧

5. 配置编译使用的环境变量

export CODE_BASE=/soft/openGauss-server                     # openGauss-server的路径
export BINARYLIBS=/soft/openGauss-third_party_binarylibs    # binarylibs的路径
export GAUSSHOME=/gauss
export GCC_PATH=$BINARYLIBS/buildtools/centos7.6_x86_64/gcc7.3/
export CC=$GCC_PATH/gcc/bin/gcc
export CXX=$GCC_PATH/gcc/bin/g++
export LD_LIBRARY_PATH=$GAUSSHOME/lib:$GCC_PATH/gcc/lib64:$GCC_PATH/isl/lib:$GCC_PATH/mpc/lib/:$GCC_PATH/mpfr/lib/:$GCC_PATH/gmp/lib/:$LD_LIBRARY_PATH
export PATH=$GAUSSHOME/bin:$GCC_PATH/gcc/bin:$PATH

6. 手动编译release版本

## configure编译并配置enable_mysql_fdw和enable_oracle_fdw
mkdir -p /gauss/app
mkdir -p /gauss/data
cd /soft/openGauss-server
./configure  --prefix=$GAUSSHOME \
--datadir=/gauss/data  \
--with-pgport=26000 \
--gcc-version=7.3.0 \
--with-3rdpartydir=$BINARYLIBS \
--with-readline \
--with-zlib \
--with-libxml \
--enable-mysql-fdw \
--enable-oracle-fdw \
--enable-thread-safety \
CC=g++ CFLAGS="-O2 -g3"

nohup make -j4 &   ## 编译时间较长，后台运行以免中断

make install

错误解决：

## 错误一：####################################################################
oracle_fdw.cpp:56:10: fatal error: storage/lock.h: No such file or directory
 #include "storage/lock.h"
          ^~~~~~~~~~~~~~~~
compilation terminated.

## 解决：
cd /soft/openGauss-server/src/include/storage/lock
cp *.h /soft/openGauss-server/src/include/storage/

三、初始化与配置

1. 初始化

## 配置用户和环境变量
groupadd dbgrp
useradd -g dbgrp omm
chown -R omm:dbgrp /gauss
cat >> /home/omm/.bash_profile <<EOF
export GAUSSHOME=/gauss
export LD_LIBRARY_PATH=\$GAUSSHOME/lib:\$LD_LIBRARY_PATH
export PATH=\$GAUSSHOME/bin:\$PATH
EOF
cat ~/.bash_profile
source ~/.bash_profile

## 初始化数据库
su - omm
gs_initdb --nodename=db1 \
--pgdata=/gauss/data/db1 \
--encoding=UTF-8 \
--locale=en_US.UTF-8 \
--username=omm \          ## 指定初始用户名，否则默认使用当前操作系统的用户名
--pwpasswd=gauss@123 \
--security                ## 创建的数据库用户权限受到限制，默认不再具有public schema的使用权限

2. 配置

vi /gauss/data/db1/postgresql.conf
----------------------------------------------
## 修改：
port=26000
listen_addresses = '0.0.0.0'
unix_socket_directory = ''
unix_socket_permissions = 0777
shared_buffers = 512MB
cstore_buffers = 128MB
log_directory = '/gauss/logs/pg_log'
----------------------------------------------

3. 启动服务

gs_ctl start -D /gauss/data/db1

4. 创建测试用户

[omm@opengauss2 ~]$ gsql -d postgres -p 26000 -U omm -r
gsql ((GaussDB Kernel V500R001C20 build ) compiled at 2021-02-02 16:42:44 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# create user scott IDENTIFIED BY 'gauss@123';     -- 普通用户可以创建server及使用外部表

四、FDW测试

1. mysql_fdw测试

1>  mysql_fdw使用介绍

        • 使用mysql_fdw需要连接MariaDB或者MySQL Server，MariaDB或MySQL Server请自行安装。
        • 加载mysql_fdw扩展：CREATE EXTENSION mysql_fdw;
        • 创建服务器对象：CREATE SERVER
        • 创建用户映射：CREATE USER MAPPING
        • 创建外表：CREATE FOREIGN TABLE 外表的表结构需要与MySQL/MariaDB侧的表结构保持一致。注意MySQL/MariaDB侧的表的第一个字段必须具有唯一性约束（如PRIMARY KEY、UNIQUE等）。
        • 对外表做正常的操作，如 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 EXPLAIN 、 ANALYZE 、 COPY 等。
        • 删除外表：DROP FOREIGN TABLE
        • 删除用户映射：DROP USER MAPPING
        • 删除服务器对象：DROP SERVER
        • 删除扩展：DROP EXTENSION mysql_fdw

2>  注意事项

        • 在openGauss上建立外表时，不会同步在MariaDB/MySQL Server上建表，需要自己利用MariaDB/MySQL Server的客户端连接MariaDB/MySQL Server建表。
        • 创建USER MAPPING时使用的MariaDB/MySQL Server用户需要有远程连接MariaDB/MySQL Server及对表相关操作的权限。使用外表前，可以在openGauss server所在的机器上，使用MariaDB/MySQL Server的客户端，使用对应的用户名密码确认能否成功连接MariaDB/MySQL Server并进行操作。
        • 对外表执行DML操作时，出现Can’t initialize character set SQL_ASCII (path: compiled_in) 错误。由于MariaDB不支持SQL_ASCII编码格式，目前只能通过修改openGauss database的编码格式解决该问题。修改database编码格式的方式为update pg_database set encoding = pg_char_to_encoding(‘UTF-8’) where datname = ‘postgres’; datname根据实际情况填写。注意修改完编码格式后，需要重新开启一个gsql会话，才能使mysql_fdw使用更新后的参数。也可以通过在执行 gs_initdb 时，使用–locale=LOCALE ，指定默认的编码格式为 非SQL_ASCII 编码。
        • 两个mysql外表间的SELECT JOIN不支持下推到MariaDB/MySQL Server执行，会被分成两条SQL语句传递到MariaDB/MySQL Server执行，然后在openGauss处汇总处理结果。
        • 不支持IMPORT FOREIGN SCHEMA语法。
        • 不支持对外表进行CREATE TRIGGER操作。

3> 示例

• 创建扩展(用户必须有sysadmin权限)

$ gsql -p 26000 -U omm postgres
-- 创建扩展
postgres=>  create extension mysql_fdw with schema public;
CREATE EXTENSION

-- 查看扩展的版本
postgres=> select mysql_fdw_version();
 mysql_fdw_version 
-------------------
             20503
             
-- 授权mysql_fdw权限给普通用户scott
postgres=> grant usage on foreign data wrapper mysql_fdw to scott;
GRANT

• 使用FDW外部表(普通用户scott操作)

-- 创建server
$ gsql -d postgres -p 26000 -U scott -r
postgres=> create server server_mysql foreign data wrapper mysql_fdw options(host '192.168.0.101',port '3306');

-- 创建用户映射
postgres=> create user mapping for scott server server_mysql options(username 'root',password 'mysql@123');

-- 创建外部表(数据结构与mysql端一致，不允许创建外部表的索引等)
postgres=> create foreign table f_mysql_t1(id int,name varchar(80))
           server server_mysql options(dbname 'mydb',table_name 'opengauss_fdw_mysql');
-- 不允许创建外部表的主键和索引，否则会报错：“ERROR:  cannot create index on foreign table "f_mysql_t1"”

-- 读写外部表f_mysql_t1(mysql数据库表 --> mydb.opengauss_fdw_mysql)
-- 对外部表可以做的操作：INSERT、UPDATE、DELETE、SELECT、EXPLAIN、ANALYZE、COPY，其他操作可能受限.
postgres=> select * from f_mysql_t1;
 id |           name
----+--------------------------
  1 | This is write from mysql
postgres=> insert into f_mysql_t1 values(2,'This is write from opengauss');
INSERT 0 1
postgres=> select * from f_mysql_t1;
 id |             name
----+------------------------------
  1 | This is write from mysql
  2 | This is write from opengauss

-- 注意：mysql端表必须有主键或唯一索引，否则会报错
postgres=> insert into f_mysql_t1 values(1002);
ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation

2. oracle_fdw测试

与mysql_fdw类似，注意LD_LIBRARY_PATH配置了oracle的lib路径（/usr/lib/oracle/11.2/client64/lib）

1> oracle_fdw使用介绍

        • 使用oracle_fdw需要连接Oracle，Oracle server请自行安装。
        • 加载oracle_fdw扩展：CREATE EXTENSION oracle_fdw;
        • 创建服务器对象：CREATE SERVER
        • 创建用户映射：CREATE USER MAPPING
        • 创建外表：CREATE FOREIGN TABLE 外表的表结构需要与Oracle数据库中的表结构保持一致。注意Oracle server侧的表的第一个字段必须具有唯一性约束（如PRIMARY KEY、UNIQUE等）。
        • 对外表做正常的操作，如 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 EXPLAIN 、 ANALYZE 、 COPY 等。
        • 删除外表：DROP FOREIGN TABLE
        • 删除用户映射：DROP USER MAPPING
        • 删除服务器对象：DROP SERVER
        • 删除扩展：DROP EXTENSION oracle_fdw

2> 注意事项

        • 在openGauss上建立外表时，不会在Oracle数据库中同步建表，需要自行在Oracle数据库中建表。
        • 执行CREATE USER MAPPING时使用的Oracle用户需要有远程连接Oracle数据库及对表相关操作的权限。使用外表前，可以在openGauss server所在的机器上，使用Oracle的客户端，使用对应的用户名密码确认能否成功连接Oracle并进行操作。
        • 执行CREATE EXTENSION oracle_fdw;时，出现 libclntsh.so: cannot open shared object file: No such file or directory。原因是Oracle的开发库libclntsh.so不在系统的相关路径中，可以先找到libclntsh.so的具体路径，然后将该so文件所在的文件夹加到 /etc/ld.so.conf 中。比如libclntsh.so的路径为 /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 ，那么就将该文件的路径 /usr/lib/oracle/11.2/client64/lib/ 加到 /etc/ld.so.conf 文件末尾。然后执行 ldconfig使修改生效即可。注意此操作需要root权限。
        • 两个Oracle外表间的SELECT JOIN不支持下推到Oracle server执行，会被分成两条SQL语句传递到Oracle执行，然后在openGauss处汇总处理结果。
        • 不支持IMPORT FOREIGN SCHEMA语法。
        • 不支持对外表进行CREATE TRIGGER操作。

3> 示例

• 创建扩展(用户必须有sysadmin权限)

-- 创建扩展(用户必须有sysadmin权限)
$ gsql -p 26000 -U omm postgres
postgres=#  create extension oracle_fdw with schema public;
CREATE EXTENSION

-- 查看扩展版本
postgres=# select oracle_diag();
                         oracle_diag
--------------------------------------------------------------
 oracle_fdw 2.1.0, PostgreSQL 9.2.4, Oracle client 11.2.0.4.0

-- 赋予scott用户使用oracle_fdw权限
postgres=# grant usage on foreign data wrapper oracle_fdw to scott;
GRANT

• 使用FDW外部表(普通用户scott操作)

-- 创建server
$ gsql -d postgres -p 26000 -U scott -r
postgres=> create server server_oracle foreign data wrapper oracle_fdw options(dbserver '192.168.0.101:1521/oradb');
CREATE SERVER

-- 创建用户映射
postgres=> create user mapping for scott server server_oracle options(user 'jack',password 'oracle');
CREATE USER MAPPING

-- 创建外部表(schema和table的名称必须大写，oracle数据字典中默认都是大写)
postgres=> create foreign table f_oracle_t1(id number,name varchar(200))
           server server_oracle options(schema 'JACK',table 'T1');
CREATE FOREIGN TABLE

-- 读写外部表f_oracle_t1(oracle数据库表 --> jack.t1)
-- 对外部表可以做的操作：INSERT、UPDATE、DELETE、SELECT、EXPLAIN、ANALYZE、COPY，其他操作可能受限.
postgres=> select * from f_oracle_t1;
 id |   name
----+----------
  1 | BeiJing
  2 | ShangHai
postgres=> insert into f_oracle_t1 values(3,'xi''an');
INSERT 0 1
postgres=> select * from f_oracle_t1;
 id |   name
----+----------
  1 | BeiJing
  2 | ShangHai
  3 | xi'an