GBase 8a数据库dblink使用示例
GBase8a dblink使用示例:
1.准备集群环境953版本的两个集群，均存在vc1.数据库为同名test;其中106.35是目标集群；106.194是源集群
源sql：
create database test;
use test;
create table t1 (a int);
insert into t1 values (10);
select * from t1;
目标sql：
create database test;
use test;
设置源集群集群的默认vc
[gbase@gc194 ~]$ gccli
GBase client 9.5.3.1.114099. Copyright (c) 2004-2019, GBase. All Rights Reserved.
gbase> set default_vc for root=vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
gbase> set default_vc for gbase=vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase>
目标集群：35 源集群：194 网关：35
2.拷贝GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10网关包到目标集群35。设置dataSource下面的配置文件内容
[root@gc35 dataSource]# pwd
/opt/dblink/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10/conf/dataSource
[root@gc35 dataSource]# cat gbase_link1.properties
[ds1]
dataSource_IP=192.168.106.194
dataSource_port=5258
dataSource_dbname=test
dataSource_dbtype=gcluster
dataSource_user=gbase
dataSource_pwd=gbase20110531
3.设置目标集群35的配置文件/opt/9531/gcluster/config/gbase_8a_gcluster.cnf，其中gbase_dblink_gateway_ip为网关启动的ip地址。
gbase_dblink_gateway_ip=192.168.106.35
gbase_dblink_gateway_port=9898
并重启集群服务gcluster_services all restart
gbase> show variables like '%dblink%';
+--------------------------------------------------+----------------+
| Variable_name | Value |
+--------------------------------------------------+----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_insert_select_optimization | 1 |
| gbase_dblink_gateway_ip | 192.168.106.35 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | |
| gbase_dblink_standby_gateway_port | 0 |
| gcluster_dblink_direct_data_exchange | 1 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
+--------------------------------------------------+----------------+
11 rows in set (Elapsed: 00:00:00.00)
gbase>
注：集群的各个c节点均需设置
4.在网关包所在192.168.106.35启动网关服务
配置/opt/dblink/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10/conf/conf.properties增加
gbase.gt.port=9898
gbase.gt.encode=gbk
gbase.gt.pagesize=10000
load data type : batch=1, insert values=0
gbase.gt.load.data.type=1
gbase.gt.table.use.decimal=1
paging query : not=0, yes=1;default=0
gbase.gt.gc.paging.query=0
gbase.gt.st.paging.query=0
gbase.gt.orcl.paging.query=0
commit type : transaction commit=0, paging commit=1
gbase.gt.commit.type=0
timeout
gbase.gt.wait.timeout=7200
timeout to fetch gcluster datasource(second) : default=108000
gbase.gt.gc.fetch.timeout=108000
增加目标集群35的ip地址到网关的配置文件中/opt/dblink/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10/conf/gcluster/gbase8a_gcluster.properties
[gc25]
gcluster_IP=192.168.106.35
gcluster_port=5258
gcluster_user=root
gcluster_pwd=
gcluster_encode=gbk
增加源集群的ip地址(62为源)（root/gbase）到网关配置文件中（可以insert into select）；63为目标
[gc47]
gcluster_IP=10.10.55.62
gcluster_port=5258
gcluster_user=root
gcluster_pwd=
gcluster_encode=uft8
[gc48]
gcluster_IP=10.10.55.62
gcluster_port=5258
gcluster_user=gbase
gcluster_pwd=gbase20110531
gcluster_encode=uft8
网关所在机器35启动网关服务gt.sh
[root@gc35 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10]# pwd
/opt/dblink/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10
[root@gc35 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10]# sh gt.sh
[root@gc35 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.10]# ps -ef |grep gate
root 18061 1 1 17:31 pts/3 00:00:00 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.1.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.5-bin.jar:lib/jaxen-1.1-beta-6.jar:lib/json-lib-2.4-jdk15.jar:lib/jtds-1.2.5.jar:lib/log4j-1.2.15.jar:lib/ojdbc6.jar:lib/commons-configuration-1.8.jar:lib/oscarJDBC.jar:lib/oscarClusterJDBC.jar cn.com.gbase.gbaseGateway.server.GBaseGateway
root 18169 17870 0 17:31 pts/3 00:00:00 grep --color=auto gate
5.目标集群35进行dblink3创建。--using后引用的名称为dataSource下配置文件名称
create database link dblink3 connect to '' identified by '' using 'gbase_link1';
dblink查询
select * from gbase.db_links;
gbase> create database link dblink1 connect to '' identified by '' using 'gbase_link1';
Query OK, 0 rows affected (Elapsed: 00:00:00.08)
gbase> select * from gbase.db_links;
+--------+---------+-------------+----------+----------+-------------+---------------------+
| owner | db_link | dblink_priv | username | password | host | created |
+--------+---------+-------------+----------+----------+-------------+---------------------+
| public | dblink3 | PUBLIC | | NULL | gbase_link1 | 2019-12-02 17:14:46 |
+--------+---------+-------------+----------+----------+-------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)
6.目标集群可以使用dblink3查看源数据表的数据了
select * from t1@dblink3；
[gbase@gc35 ~]$ gccli
GBase client 9.5.3.1.114099. Copyright (c) 2004-2019, GBase. All Rights Reserved.
gbase> use vc1.test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show tables;
Empty set (Elapsed: 00:00:00.01)
gbase> select * from t1@dblink3;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (Elapsed: 00:00:00.06)
-- 通过目标集群给源集群设置表信息
passthrough link dblink3 using 'DROP TABLE IF EXISTS t1a;';
passthrough link dblink3 using 'CREATE TABLE t1b(a int) DISTRIBUTED BY ('a');';
passthrough link dblink3 using 'insert into t1b select * from t1;';
gbase> passthrough link dblink3 using 'insert into t1b select * from t1;';
Query OK, 1 row affected (Elapsed: 00:00:00.26)
gbase> select * from t1b@dblink3;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (Elapsed: 00:00:00.13)
select * from t1@dblink3 union select * from t1b@dblink3;
直连查询
直连查询
gbase> set _t_gcluster_dblink_clear_syntax_constraints=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select id from test_raw@dblink1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (Elapsed: 00:00:00.68)
