1. 什么是透明网关
2. 如何使用透明网关
1. 启动网关：
[gbase@liuyang-node-2 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19]$ sh gt.sh
2. 检查网关是否启动：
[gbase@liuyang-node-2 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19]$ ps -aux|grep gateway
gbase 26450 15.0 0.5 4598560 46796 pts/0 Sl 09:19 0:01 java -Dfile.encoding=UTF-8 -Dcom.sun.management.jmxremote -Xmx2048m -cp gateway/gbaseGateway.jar:lib/commons-beanutils-1.7.0.jar:lib/commons-collections-3.2.2.jar:lib/commons-lang-2.4.jar:lib/commons-logging-1.1.jar:lib/commons-net-1.4.1.jar:lib/dom4j-1.6.1.jar:lib/ezmorph-1.0.6.jar:lib/gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:lib/jaxen-1.1-beta-6.jar:lib/json-lib-2.4-jdk15.jar:lib/jtds-1.2.5.jar:lib/log4j-api-2.17.0.jar:lib/ojdbc8.jar:lib/commons-configuration-1.8.jar:lib/mysql-connector-java-8.0.23.jar:lib/tdgssconfig.jar:lib/terajdbc4.jar:lib/log4j-core-2.17.0.jar:lib/oscarJDBC.jar:lib/oscarClusterJDBC.jar:lib/hive-jdbc-3.1.0-standalone.jar cn.com.gbase.gbaseGateway.server.GBaseGateway
gbase 26466 0.0 0.0 112832 992 pts/0 S+ 09:19 0:00 grep --color=auto gateway
为什么要检查是否启动呢，因为网关未启动时不会出现报错，而如果未安装最新的java，网关不会开启，这时需要通过yum安装java
3.配置网关
[gbase@liuyang-node-2 conf]$ pwd
/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf
[gbase@liuyang-node-2 conf]$ vi conf.properties
conf.properties配置文件用来配置网关，
gbase.gt.port是网关的端口号，默认为9898
gbase.gt.encode是网关的字符集设置，
主要使用的就是这两个，当更改配置文件后，需要重新启动网关才能生效
4.配置源端信息dblink
[gbase@liuyang-node-2 dataSource]$ pwd
/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf/dataSource
[gbase@liuyang-node-2 dataSource]$ cat gbase_link2.properties
[gcluster]
dataSource_IP=10.10.55.36
dataSource_port=5258
dataSource_dbname=test
dataSource_dbtype=gcluster
dataSource_user=root
dataSource_pwd=
dataSource_charset=utf-8
dataSource_IP为源端集群的ip，dataSource_port为集群的端口号，dataSource_dbname为数据库名，dataSource_dbtype为数据库类型，
dataSource_vc可指定vc，不加此参数会以默认vc进行查询set default_vc for root=name暂时此参数还不存在
根据源端数据库类型可以写为mysql/gcluster/oracle/hive/teradata,
dataSource_user和dataSource_pwd分别是账户和密码，dataSource_charset是设置字符集
如果源端是td数据库的话多一个参数
dataSource_url=jdbc:teradata://10.10.58.0/TMODE=ANSI,CHARSET=ASCII,CLIENT_CHARSET=UTF8,database=test
如果源端是异构数据库的话还需要配置
[gbase@liuyang-node-2 conf]$ pwd
/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf
[gbase@liuyang-node-2 conf]$ vi gcluster/gbase8a_gcluster.properties
[gc5]
gcluster_IP=10.10.13.120
gcluster_port=5258
gcluster_user=root
gcluster_pwd=
gcluster_encode=utf-8
用来进行回连，这里填写的是目标数据库信息.
若是同源数据库，“insert into 本地表 select * from 源端表 ”不需要配置上述信息，而insert into 源端表 select * from 本地表 ”需要配置上述信息。
为使用方便最好还是配置好信息，而且需要将目标集群的所有coor节点都写入配置文件
5.配置目标集群的配置文件
在目标集群的/opt/10.10.13.120/gcluster/config/gbase_8a_gcluster.cnf文件中增加
gbase_dblink_gateway_ip=和gbase_dblink_gateway_port=,他们是网关的ip和端口号，然后重启服务
注意：若目标集群有多个coor节点，dblink只能在更改了配置文件的节点使用
6.在目标数据库创建dblink
create database link link2 connect to '' identified by '' using 'oracle_link1';
create private database link link1 connect to ''identified by '' using 'gbase_link2';
select * from gbase.db_links;可查看创建的dblink信息
drop private database link link11;删除私有dblink
7.使用dblink
本地表与远端表不可直接进行join
gbase> select * from t1 join t1@link1;
ERROR 1149 (42000): (GBA-02SC-1001) DBLink table join with (normal table || from sub query) is forbidden
可以将远端表作为子查询进行join
gbase> select * from t1 join (select * from t1@link1) aa;
+------+------+
| a | a |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+------+------+
18 rows in set (Elapsed: 00:00:00.31)
异构数据库的表不可直接进行查询，需要将其作为子查询
gbase> select * from t1@link2;
ERROR 1149 (42000): (GBA-02SC-1001) DBLink table from heterogeneous data source must belong to the relation subquery.
gbase> select * from (select * from t1@link2) a ;
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 |
+------+------+------+------+
2 rows in set (Elapsed: 00:00:00.53)
直通模式:请求网关直接转发sql语句dao源端数据库执行，只支持自动提交模式。
支持insert 、insert select 、delete、update、truncate、merge、create、drop
passthrough link link2 using ’insert into t2 select * from 2’;
8.多vc使用
_t_gcluster_dblink_ignore_use_db为1远端连接会跟随远端默认vc
_t_gcluster_dblink_ignore_use_db为0时远端连接会跟随本地
gbase> use vc2.test;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show processlist;
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
| Id | User | Host | vc | db | Command | Time | State | Info |
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | NULL | Daemon | 9032 | Waiting for event lock | NULL |
| 49 | gbase | 10.10.11.14:55812 | NULL | NULL | Sleep | 3375 | | NULL |
| 74 | root | localhost | vc1 | test2 | Query | 0 | NULL | show processlist |
| 91 | root | 10.10.13.120:64959 | vc2 | test | Sleep | 3 | | NULL |
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> select * from t1@link1;
+------+
| a |
+------+
| 2 |
+------+
1 row in set (Elapsed: 00:00:00.12)
gbase> use vc1.test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show processlist;
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
| Id | User | Host | vc | db | Command | Time | State | Info |
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | NULL | Daemon | 9046 | Waiting for event lock | NULL |
| 49 | gbase | 10.10.11.14:55812 | NULL | NULL | Sleep | 3389 | | NULL |
| 74 | root | localhost | vc1 | test2 | Query | 0 | NULL | show processlist |
| 91 | root | 10.10.13.120:64959 | vc1 | test | Sleep | 2 | | NULL |
+----+-----------------+--------------------+------+-------+---------+------+------------------------+------------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| test2 |
+--------------------+
源端的进程可以看到连接通道显示vc1.test，但是源端数据库不存在vc1.test
常见问题：
gbase> select * from t1@link1;
ERROR 1105 (HY000): connect gateway server timeout. Connection refused
透明网关未启动
gbase> select * from t1@link1;
ERROR 1105 (HY000): The message from gateway is invalid.
检查datasource文件，ip密码等是否写错
gbase> insert into t1@link1 select * from t1;
ERROR 1105 (HY000): errorCode: 1045, errorMsg: Access denied for user 'root'@'10.10.13.120' (using password: YES)
检查网关的gbase8a_gcluster.properties文件，目标集群的密码错误
gbase> insert into t1@link1 select * from t1;
ERROR 1105 (HY000): errorCode: 0, errorMsg: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
检查网关的gbase8a_gcluster.properties文件,未配置目标集群的信息或不正确
gbase> insert into t1@link1 select * from t1;
ERROR 1105 (HY000): errorCode: 1064, errorMsg: You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near '`t1`' at line 1
检查网关的gbase8a_gcluster.properties文件,端口号写为gnode的端口号
