GBase 8a同义词synonym使用样例

发表于 作者 laozizhu

GBase 8a支持同义词synonym，本文用862Build43版本对同义词功能进行了适用，并对适用场景进行建议。

语法

其中public 关键字创建公共同义词，用户无需指定库名就可以访问。否则默认创建的是私有同义词，只有在当前指定库可用。

create [public] synonym SYNONYM_NAME for DBNAME.OBJECT_NAME;

大数据场景

除了传统的同义词使用场景（这部分请自行网络搜索），对GBase 8a这种大数据分析场景，一般会有非常多的对象，特别是表，会有几十万，几百万的表。

这么多的对象，对操作系统的文件系统提出了压力，在访问一个目录下几百万的文件，也非常耗时且消耗IO资源。

通过同义词，可以考虑将对象，分类到不同的支持库下，比如按月分库。但在主库，用同义词，这样可以极大的减少每个库下文件数量，减少磁盘IO，从而提升了系统性能。

比如视图，可以在其它库建视图，然后在主库建同义词。视图的元数据，可以用原始库，也可以用同义词的元数据表。

样例环境

如下创建了3个数据库，其中dbmain是主库，db1是测试库1， db2是测试库2。在2个测试库分别建了1个表。

[gbase@rh6-1 ~]$ gccli testdb

GBase client 8.6.2.43-R30.124361. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> create database db1;
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> create database db2;
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> create database dbmain;
Query OK, 1 row affected (Elapsed: 00:00:00.01)

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create table db1_t1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.36)

gbase> insert into db1_t1 values(1),(2);
Query OK, 2 rows affected (Elapsed: 00:00:00.38)
Records: 2  Duplicates: 0  Warnings: 0

gbase> use db2;
Query OK, 0 rows affected (Elapsed: 00:00:00.22)

gbase> create table db2_t1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.49)

gbase> insert into db2_t1 values(221),(222);
Query OK, 2 rows affected (Elapsed: 00:00:00.41)
Records: 2  Duplicates: 0  Warnings: 0

表的私有同义词样例

在dbmain分别创建db1和db2的两个同义词。

注意：同义词并不检查对象是否存在。

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> create synonym db1_t1 for db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

gbase> create synonym db2_t1 for db2.db2_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

我们对同义词的表可以查询，变动数据等操作，就如同本地表一样。

表私有同义词查询


gbase> select * from db1_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.02)

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  221 |
|  222 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

表私有同义词DML


gbase> insert into db2_t1 values (2001),(2002);
Query OK, 2 rows affected (Elapsed: 00:00:00.26)
Records: 2  Duplicates: 0  Warnings: 0

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  221 |
|  222 |
| 2001 |
| 2002 |
+------+
4 rows in set (Elapsed: 00:00:00.01)

gbase> update db2_t1 set id=id+1;
Query OK, 4 rows affected (Elapsed: 00:00:00.84)
Rows matched: 4  Changed: 4  Warnings: 0

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  222 |
|  223 |
| 2002 |
| 2003 |
+------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> delete db2_t1 where id>=2003;
Query OK, 1 row affected (Elapsed: 00:00:00.40)

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  222 |
|  223 |
| 2002 |
+------+
3 rows in set (Elapsed: 00:00:00.01)

表私有同义词DDL

当前版本，不支持对同义词的表结构变动。因为它不是个表。而同义词又没有【表结构】

gbase> alter table db2_t1 add column(name varchar(100));
ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'dbmain.db2_t1'

表共有同义词样例

共有同义词创建后，不需要使用库前缀，可以直接使用，等同于【全局】可用。

gbase> create public synonym pub_db1_t1 for db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> use db2;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.06)

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.01)

gbase> use gclusterdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

表公有同义词的DML/DDL 操作和私有同义词无区别，就不多写了。

视图的同义词

我们在db1创建一个视图，然后再dbmain创建一个同义词。

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)


gbase> create view v_t1 as select * from db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.07)

gbase> select * from v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create synonym v_t1 for db1.v_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

共有视图的DML，遵循视图是否可以的规定，当前版本不支持视图的insert。DDL不支持。

gbase> insert into v_t1 values(111),(112);
ERROR 1149 (42000): (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster.
gbase>

其它同义词

任何数据库的对象，都可以创建同义词，包括同义词的同义词。

gbase> create synonym v_v_t1 for dbmain.v_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from v_v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

查看同义词

查看当前库可用同义词

能使用的私有同义词和共有同义词

gbase> select * from gbase.synonyms where owner=database() or owner=''\G;
*************************** 1. row ***************************
       owner: dbmain
synonym_name: db1_t1
object_owner: db1
 object_name: db1_t1
     db_link: NULL
*************************** 2. row ***************************
       owner: dbmain
synonym_name: db2_t1
object_owner: db2
 object_name: db2_t1
     db_link: NULL
*************************** 3. row ***************************
       owner: dbmain
synonym_name: v_t1
object_owner: db1
 object_name: v_t1
     db_link: NULL
*************************** 4. row ***************************
       owner: dbmain
synonym_name: v_t_notexist
object_owner: db1
 object_name: v_t1_178291728712
     db_link: NULL
*************************** 5. row ***************************
       owner:
synonym_name: v_t_notexist
object_owner: db1
 object_name: v_t1_178291728712
     db_link: NULL
5 rows in set (Elapsed: 00:00:00.00)

查看同义词的创建语法

gbase> show create synonym v_v_t1;
+---------------+----------------------------------------------+
| Synonym       | Create synonym                               |
+---------------+----------------------------------------------+
| dbmain.v_v_t1 | CREATE SYNONYM dbmain.v_v_t1 FOR dbmain.v_t1 |
+---------------+----------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show create synonym v_t1;
+-------------+-----------------------------------------+
| Synonym     | Create synonym                          |
+-------------+-----------------------------------------+
| dbmain.v_t1 | CREATE SYNONYM dbmain.v_t1 FOR db1.v_t1 |
+-------------+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

提示

同义词不检查对象是否存在

利用这一点，可以提前创建同义词，等真是对象存在后，该同义词及时生效。


gbase> show create synonym v_t_notexist;
+---------------------+--------------------------------------------------------------+
| Synonym             | Create synonym                                               |
+---------------------+--------------------------------------------------------------+
| dbmain.v_t_notexist | CREATE SYNONYM dbmain.v_t_notexist FOR db1.v_t1_178291728712 |
+---------------------+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from v_t_notexist;
ERROR 1146 (42S02): Table 'db1.v_t1_178291728712' doesn't exist

gbase> create view db1.v_t1_178291728712 as select * from db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> select * from v_t_notexist;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase>

同义词不能和对象重名

和对象一样，作用域内不能重复。不能创建和表名重复的同义词，同样的，实体表也不能和同义词重名；

gbase> select * from v_t_notexist;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> create view v_t_notexist as select * from testdb.t1;
ERROR 1712 (HY000): gcluster view error: name 'dbmain.v_t_notexist' is already used by an existing synonym.
gbase>

权限

public 同义词必须有管理员权限，只有库级权限的是无法创建全局同义词的。

[gbase@rh6-1 ~]$ gccli -utestdb -ptestdb testdb

GBase client 8.6.2.43-R35.508906e415d. Copyright (c) 2004-2022, GBase.  All Righ

gbase> create public synonym pub_sy_t1 for t1;
ERROR 1822 (HY000): CREATE command denied to user 'testdb'@'localhost' for public synonym 'pub_sy_t1'
gbase>
Post Views: 528

相关文章:

  1. GBase8a MPP Cluster集群命令行客户端gccli和gncli的使用参数
  2. GBase 8a集群如何批量导出表结构
  3. GBase 8a集群加载的详细过程日志排查加载性能问题
  4. GBase 8a集群创建用户create user完整语法
  5. GBase 8a集群通过SQL获得加载错误数据信息日志
  6. GBase 8a资源通过管理元数据查看当前配置
  7. GBase 8a限制数据库单个用户的最大连接数
  8. GBase 8a版本(V8,V9)升级操作手顺
  9. GBase 8a里通过rsync加速调度coor节点的扩容和替换效率
  10. GBase 8a查看哪些存储过程用到了某张表