Gaussdb 100 分布式集群数据操作之route by

Gaussdb 100在集群部署下，支持通过路由规则将SQL语句发送给对应的DN节点执行，执行过程如下图所示：

以下为访问示例（演示环境为三节点分布式集群）。

1. 新建路由规则

DROP DISTRIBUTE RULE IF EXISTS enmo_muggle;
CREATE DISTRIBUTE RULE enmo_muggle(key char(32) not null)
AS LIST(key) (
groupid 1 values ('dn1'),
groupid 2 values ('dn2'),
groupid 3 values ('dn3')
);

2. 创建表

DROP TABLE IF EXISTS muggle;
CREATE TABLE muggle(id int, key char(32) not null) DISTRIBUTE BY
LIST(key)
(
groupid 1 values ('dn1'),
groupid 2 values ('dn2'),
groupid 3 values ('dn3')
);

3.未设置路由规则，操作数据时，会自动将数据按(groupid)落盘至指定DN节点。

SQL> route by null;

Succeed.

SQL> 

SQL>  INSERT INTO muggle VALUES(1, 'dn1');

1 rows affected.

SQL> INSERT INTO muggle VALUES(2, 'dn2');

1 rows affected.

SQL> INSERT INTO muggle VALUES(3, 'dn3');

1 rows affected.

SQL> commit;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
1            dn1                             
2            dn2                             
3            dn3                             

3 rows fetched.

SQL> route by node 1;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
1            dn1                             

1 rows fetched.

SQL> route by node 2;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
2            dn2                             

1 rows fetched.

SQL> route by node 3;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
3            dn3                             

1 rows fetched.

SQL> 

4. 指定路由规则名操作数据，则按路由规则名落盘数据。

SQL> ROUTE BY RULE enmo_muggle(key) VALUES('dn1');

Succeed.

SQL> INSERT INTO muggle VALUES(1, 'dn1');

1 rows affected.

SQL> ROUTE BY RULE enmo_muggle(key) VALUES('dn2');

Succeed.

SQL> INSERT INTO muggle VALUES(2, 'dn2');

1 rows affected.

SQL> ROUTE BY RULE enmo_muggle(key) VALUES('dn3');

Succeed.

SQL> INSERT INTO muggle VALUES(3, 'dn3');

1 rows affected.

SQL> commit;

Succeed.

SQL> route by node 1;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
1            dn1                             

1 rows fetched.


SQL> route by node 2;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
2            dn2                             

1 rows fetched.

SQL> route by node 3;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
3            dn3                             

1 rows fetched.

SQL> route by null;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
1            dn1                             
2            dn2                             
3            dn3                             

3 rows fetched.

5. 操作数据时，若指定按（groupid）节点ID，则数据落盘在指定的DN节点，操作数据不会按规则名进行落盘。

SQL> route by node 1;

Succeed.

SQL> insert into muggle values(1,'dn1');

1 rows affected.

SQL> insert into muggle values(2,'dn2');

1 rows affected.

SQL> insert into muggle values(3,'dn3');  

1 rows affected.

SQL> commit;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------
1            dn1                             
2            dn2                             
3            dn3                             

3 rows fetched.

SQL> route by node 2;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------

0 rows fetched.

SQL> route by node 3;

Succeed.

SQL> select * from muggle;

ID           KEY                             
------------ --------------------------------

0 rows fetched.