南大通用GBase 8a的全文索引功能安装部署方法

本文介绍GBase 8a MPP集群的全文索引的安装方法。该功能是通过插件的形式实现,需要单独安装。用户在使用时如果需要全文索引,请一起向厂商索取对应的全文索引插件。 该插件可以在集群安装后的任何时刻,进行安装。

本文只介绍V95版本。

参考

GBase 8a全文索引功能安装部署方法
GBase 8a全文索引创建、更新和删除方法
GBase 8a全文索引常用配置文件和配置参数
GBase 8a全文索引提高模糊查询性能使用样例
GBase 8a全文索引多分词器的功能介绍和使用

环境

2节点集群,1个调度节点,2个数据节点(其中1个复用)。

[gbase@gbase_rh7_001 ~]$  gcadmin
CLUSTER STATE:         ACTIVE
VIRTUAL CLUSTER MODE:  NORMAL

=============================================================
|           GBASE COORDINATOR CLUSTER INFORMATION           |
=============================================================
|   NodeName   | IpAddress  | gcware | gcluster | DataState |
-------------------------------------------------------------
| coordinator1 | 10.0.2.101 |  OPEN  |   OPEN   |     0     |
-------------------------------------------------------------
=========================================================================================================
|                                    GBASE DATA CLUSTER INFORMATION                                     |
=========================================================================================================
| NodeName |                IpAddress                 | DistributionId | gnode | syncserver | DataState |
---------------------------------------------------------------------------------------------------------
|  node1   |                10.0.2.101                |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node2   |                10.0.2.115                |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------

安装包

GBase8a_MPP_Cluster-NoLicense-fulltext-9.5.2.37-redhat7.3-x86_64.tar.bz2

要注意其版本9.5.2.37要和集群的版本对应,并核对操作系统redhat7 以及CPU种类X86_64。

解压缩

通过tar命令进行解压缩。请注意在V95版本只使用操作系统dbaUser,一般是gbase,进行操作,所以解压也放到gbase能访问的目录下。解压缩包自带_fulltext字样,不会和集群安装包产生冲突。

[gbase@gbase_rh7_001 ~]$ tar xvf GBase8a_MPP_Cluster-NoLicense-fulltext-9.5.2.37-redhat7.3-x86_64.tar.bz2
gcinstall_fulltext/
gcinstall_fulltext/gcwareGroup.json
gcinstall_fulltext/fulltext.py
gcinstall_fulltext/license.txt
gcinstall_fulltext/InstallTar.py
gcinstall_fulltext/FileCheck.py
gcinstall_fulltext/rootPwd.json
gcinstall_fulltext/extendCfg.xml
gcinstall_fulltext/replaceStop.py
gcinstall_fulltext/SSHThread.py
gcinstall_fulltext/demo.options
gcinstall_fulltext/RestoreLocal.py
gcinstall_fulltext/InstallFuns.py
gcinstall_fulltext/rmt.py
gcinstall_fulltext/example.xml
gcinstall_fulltext/CGConfigChecker.py
gcinstall_fulltext/pexpect.py
gcinstall_fulltext/cluster.conf
gcinstall_fulltext/gccopy.py
gcinstall_fulltext/gbase_data_timezone.sql
gcinstall_fulltext/gcexec.py
gcinstall_fulltext/unInstall_fulltext.py
gcinstall_fulltext/SetSysEnv.py
gcinstall_fulltext/CorosyncConf.py
gcinstall_fulltext/BUILDINFO
gcinstall_fulltext/dependRpms
gcinstall_fulltext/gcinstall_fulltext.py
gcinstall_fulltext/fulltext.tar.bz2
[gbase@gbase_rh7_001 ~]$ 

配置文件

配置文件和集群安装包完全一致。进入解压缩后的gcinstall_fulltext目录,修改配置文件。其中demo.options只是个例子,配置文件名随意。

[gbase@gbase_rh7_001 gcinstall_fulltext]$ cat demo.options
installPrefix= /opt/gbase
coordinateHost = 10.0.2.101
coordinateHostNodeID = 101
dataHost = 10.0.2.101,10.0.2.115
#existCoordinateHost =10.0.2.101
#existDataHost =10.0.2.101
dbaUser = gbase
dbaGroup = gbase
dbaPwd = 'gbase1234'
rootPwd = ''
#rootPwdFile = rootPwd.json
#updateConf=demo.conf
[gbase@gbase_rh7_001 gcinstall_fulltext]$

停服务

所有调度和数据节点都停掉。

[gbase@gbase_rh7_001 gcinstall_fulltext]$ gcluster_services all stop
Stopping GCMonit success!
Stopping gcrecover :                                       [  OK  ]
Stopping gcluster :                                        [  OK  ]
Stopping gcware :                                          [  OK  ]
Stopping gbase :                                           [  OK  ]
Stopping syncserver :                                      [  OK  ]
[gbase@gbase_rh7_001 gcinstall_fulltext]$ 

安装

V9的参数只有操作系统gbase用户的密码,用于远程执行。

[gbase@gbase_rh7_001 gcinstall_fulltext]$ ./gcinstall_fulltext.py --dbaUserPwd=gbase1234
CoordinateHost:
10.0.2.101
DataHost:
10.0.2.101    10.0.2.115
Are you sure to install fulltext on these gcluster nodes. ([Y,y]/[N,n])? y
10.0.2.101      Install fulltext successfully.
10.0.2.115      Install fulltext successfully.
[gbase@gbase_rh7_001 gcinstall_fulltext]$ 

启动服务

启动所有节点的数据库服务。

[gbase@gbase_rh7_001 gcinstall_fulltext]$ gcluster_services all start
Starting gcware :                                          [  OK  ]
Starting gcluster :                                        [  OK  ]
Starting gcrecover :                                       [  OK  ]
Starting gbase :                                           [  OK  ]
Starting syncserver :                                      [  OK  ]
Starting GCMonit success!
[gbase@gbase_rh7_001 gcinstall_fulltext]$ 

检验

建表和数据

gbase> create table t1(id int, name varchar(100), dept int,birth date,memo longtext);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)

gbase> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL,
  "dept" int(11) DEFAULT NULL,
  "birth" date DEFAULT NULL,
  "memo" longtext
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> insert into t1 values(1,'张三',1,'1990-09-09','张三的个人建立和备注');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> insert into t1 values(2,'李四',1,'1980-08-08','李四的个人建立和备注');
Query OK, 1 row affected (Elapsed: 00:00:00.12)

gbase> select * from t1;
+------+--------+------+------------+--------------------------------+
| id   | name   | dept | birth      | memo                           |
+------+--------+------+------------+--------------------------------+
|    1 | 张三   |    1 | 1990-09-09 | 张三的个人建立和备注           |
|    2 | 李四   |    1 | 1980-08-08 | 李四的个人建立和备注           |
+------+--------+------+------------+--------------------------------+
2 rows in set (Elapsed: 00:00:00.01)

建索引

gbase> create fulltext index idx_memo on t1(memo);
Query OK, 0 rows affected (Elapsed: 00:00:00.16)
Records: 0  Duplicates: 0  Warnings: 0

gbase> update index idx_memo on t1;
Query OK, 2 rows affected (Elapsed: 00:00:01.98)

gbase> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL,
  "dept" int(11) DEFAULT NULL,
  "birth" date DEFAULT NULL,
  "memo" longtext,
  FULLTEXT "idx_memo" ("memo")
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SHOW INDEX FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          1 | idx_memo |            1 | memo        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (Elapsed: 00:00:00.01)

gbase>

测试全文索引查询

通过contains进行全文查询。

gbase> select * from t1 where contains(memo,'建立');
+------+--------+------+------------+--------------------------------+
| id   | name   | dept | birth      | memo                           |
+------+--------+------+------------+--------------------------------+
|    1 | 张三   |    1 | 1990-09-09 | 张三的个人建立和备注           |
|    2 | 李四   |    1 | 1980-08-08 | 李四的个人建立和备注           |
+------+--------+------+------------+--------------------------------+
2 rows in set (Elapsed: 00:00:00.02)

gbase> select * from t1 where contains(memo,'张');
+------+--------+------+------------+--------------------------------+
| id   | name   | dept | birth      | memo                           |
+------+--------+------+------------+--------------------------------+
|    1 | 张三   |    1 | 1990-09-09 | 张三的个人建立和备注           |
+------+--------+------+------------+--------------------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase>

多全文列测试

注意contains里面的 ‘张|李’ 其中的|代表或者。

gbase> create fulltext index idx_name on t1(name);
Query OK, 0 rows affected (Elapsed: 00:00:00.29)
Records: 0  Duplicates: 0  Warnings: 0

gbase> update index idx_name on t1;
Query OK, 2 rows affected (Elapsed: 00:00:01.98)

gbase> select * from t1 where contains(name,'张|李');
+------+--------+------+------------+--------------------------------+
| id   | name   | dept | birth      | memo                           |
+------+--------+------+------------+--------------------------------+
|    1 | 张三   |    1 | 1990-09-09 | 张三的个人建立和备注           |
|    2 | 李四   |    1 | 1980-08-08 | 李四的个人建立和备注           |
+------+--------+------+------------+--------------------------------+
2 rows in set (Elapsed: 00:00:00.09)