GBase8a dblink使用示例

最新推荐文章于 2024-09-29 08:44:52 发布

gbase笔记

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量66

点赞数

文章标签：数据库 java sql

原文链接：https://www.modb.pro/db/1774005492712542208

版权

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> 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>

注：集群的各个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 dblink3 connect to '' identified by '' using 'gbase_link1';
Query OK, 0 rows affected (Elapsed: 00:00:00.08)

6.目标集群可以使用dblink3查看源数据表的数据了
select * from t1@dblink3；
[gbase@gc35 ~]$ gccli

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)