南大通用Gbase8a MPP Cluster 管理-03分布表、复制表使用

原创飞天 2024-06-27

470

本文主要介绍gbase8a中表的管理，如果你对gbase8a还不了解，请先参考下面这几篇文章了解学习：

Gbase8a MPP Cluster产品介绍以及安装部署

南大通用GBase8a MPP Cluster介绍以及安装部署

南大通用Gbase8a MPP Cluster 管理-01集群状态管理

南大通用Gbase8a MPP Cluster 管理-02分布信息管理

下面，让我们一起开始分布表和复制表的实验学习。
在gbase 8a MPP系统中，主要包含两种表类型：分布表和复制表。

分布表

分布表可以使数据按指定的策略分布存储在不同的主机上，从而实现分布式数据存储和分布式计算，解决大数据存储容量扩展和计算性能扩展的问题。
分布策略：采用hash分布、random分布策略存储数据
默认创建的表是随机分布表，每个节点上只保留部分表数据。
Hash分布表能实现相同数据在同一节点上，实现本地化运算，推荐大表建成hash分布表。

随机分布表

创建表时不指定分布列，默认创建的表即为随机分布表。数据平均分布于集群的所有运算节点，每个节点管理部分表数据。

创建随机分布表

create table testtabrandom(id int,name varchar(10)) ;

查看表的类型

gbase> select dbName,tbName,isReplicate,hash_column   from gbase.table_distribution   where tbname='testtabrandom';
+--------+---------------+-------------+-------------+
| dbName | tbName        | isReplicate | hash_column |
+--------+---------------+-------------+-------------+
| testdb | testtabrandom | NO          | NULL        |
+--------+---------------+-------------+-------------+
1 row in set (Elapsed: 00:00:00.00)

从查询结果可以看出，isReplicate为'NO',说明表是分布表，hash_column为NULL，说明testtabrandom表是随机分布表。

插入500条数据并查看数据分布情况

gcluster_random_insert参数：
用于控制随机分布表在执行insert value 时，发起coordinator的集群节点上存在单机节点，数据分布到单机的原则。
0:insert value 数据都落在和发起集群节点相同的单机节点上;
1:insert value 进入的每条数据，采用random()%分片数 原则，随机落在任意单机节点。
注:发起 coordinator 节点上不存在gnode节点时，目前数据分布逻辑为 insert value 进入的每条数据，采用 random()%分片数 原则，随机落在任意单机节点。

gbase> show variables like 'gcluster_random_insert';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| gcluster_random_insert | 0     |
+------------------------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> set global gcluster_random_insert=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

#创建存储过程插入数据
gbase> delimiter $$
gbase> create procedure insertdata()
    -> begin
    ->   declare i int default 1; 
    -> while i<501 do 
    -> insert into testtabrandom(id,name) values(i,to_char(i));
    -> set i=i+1;  
    -> end while;
    -> end;
    -> $$
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

gbase> delimiter ;
gbase> call insertdata();
Query OK, 1 row affected (Elapsed: 00:00:11.86)

gbase> select count(*) from testtabrandom;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (Elapsed: 00:00:00.03)

#查看各节点的总数据量情况
在953版本中加入参数gcluster_segment_id_replace，默认值为0，不启动segment_id功能；为1时，开启segment_id功能。现在开启参数：
gbase> set gcluster_segment_id_replace = 1; 
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

说明：segmentid可以通过gcadmin showdistribution命令获取
gbase> system gcadmin showdistribution                       

                                Distribution ID: 10 | State: new | Total segment num: 3

             Primary Segment Node IP                   Segment ID                 Duplicate Segment node IP
========================================================================================================================
|                 192.168.100.10                 |         1          |                 192.168.100.12                 |
------------------------------------------------------------------------------------------------------------------------
|                 192.168.100.12                 |         2          |                 192.168.100.14                 |
------------------------------------------------------------------------------------------------------------------------
|                 192.168.100.14                 |         3          |                 192.168.100.10                 |
========================================================================================================================

gbase>  select count(*) from testtabrandom where segment_id=1;
+----------+
| count(*) |
+----------+
|      162 |
+----------+
1 row in set (Elapsed: 00:00:00.01)

gbase>  select count(*) from testtabrandom where segment_id=2;
+----------+
| count(*) |
+----------+
|      165 |
+----------+
1 row in set (Elapsed: 00:00:00.01)

gbase>  select count(*) from testtabrandom where segment_id=3;
+----------+
| count(*) |
+----------+
|      173 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

#通过数据字典查看各节点的数据百分比情况
gbase> select * from information_schema.cluster_table_segments a where table_schema='testdb' and table_name='testtabrandom';
+--------------+--------------+---------------+--------+----------------+-----------------+--------------------+--------------+
| TABLE_VC     | TABLE_SCHEMA | TABLE_NAME    | SUFFIX | HOST           | TABLE_DATA_SIZE | TABLE_STORAGE_SIZE | DATA_PERCENT |
+--------------+--------------+---------------+--------+----------------+-----------------+--------------------+--------------+
| vcname000001 | testdb       | testtabrandom | n1     | 192.168.100.10 |            1799 |               2855 | 32.4027%     |
| vcname000001 | testdb       | testtabrandom | n2     | 192.168.100.12 |            1840 |               2896 | 33.1412%     |
| vcname000001 | testdb       | testtabrandom | n3     | 192.168.100.14 |            1913 |               2969 | 34.4561%     |
+--------------+--------------+---------------+--------+----------------+-----------------+--------------------+--------------+
3 rows in set (Elapsed: 00:00:00.00)

从查询结果可以看出，数据在各个节点上的分布很均匀，达到预期效果。

哈希分布表

按照hash分布策略将数据分布于所有运算节点，每个节点管理部分表数据。查询速度快，但各节点数据分布不一定平均，有数据倾斜风险，需优化选择hash分布列。
选取distributed by列字段的原则：

在多表JOIN查询时，表中某列经常用于JOIN等值关联；
表中该列通常是等值查询的列，并且使用的频率很高；
做group by操作时，分组字段；
表中重复值较少的列，尽量让数据均匀分布。

被选为distributed by列字段，有如下限制说明：

当前只支持INT、BIGINT、varchar、decimal数据类型。
distributed by列的值，不允许进行update操作。

创建哈希分布表

create table testtabhash(id int,name varchar(10)) distributed by('id') ;

查看表的类型

gbase> select dbName,tbName,isReplicate,hash_column   from gbase.table_distribution   where tbname='testtabhash';
+--------+-------------+-------------+-------------+
| dbName | tbName      | isReplicate | hash_column |
+--------+-------------+-------------+-------------+
| testdb | testtabhash | NO          | id          |
+--------+-------------+-------------+-------------+
1 row in set (Elapsed: 00:00:00.00)

从查询结果可以看出，isReplicate为'NO',说明表是分布表，hash_column为id，说明testtabhash表是哈希分布表，哈希分布列是id。

插入500条数据并查看数据分布情况

#创建存储过程插入数据
gbase> delimiter $$
gbase> create procedure insertdata2()
    -> begin
    ->   declare i int default 1; 
    -> while i<101 do 
    -> insert into testtabhash(id,name) values(i,to_char(i));
    -> set i=i+1;  
    -> end while;
    -> end;
    -> $$
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> delimiter ;
gbase> call insertdata2();
Query OK, 1 row affected (Elapsed: 00:00:03.49)

gbase> select count(*) from testtabhash;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (Elapsed: 00:00:00.04)

#查看各节点的总数据量情况
gbase> select count(*) from testtabhash where segment_id=1;
+----------+
| count(*) |
+----------+
|      142 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

gbase> select count(*) from testtabhash where segment_id=2;
+----------+
| count(*) |
+----------+
|      174 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

gbase> select count(*) from testtabhash where segment_id=3;
+----------+
| count(*) |
+----------+
|      184 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

#通过数据字典查看各节点的数据百分比情况
gbase> select * from information_schema.cluster_table_segments where table_schema='testdb' and table_name='testtabhash1';
+--------------+--------------+--------------+--------+----------------+-----------------+--------------------+--------------+
| TABLE_VC     | TABLE_SCHEMA | TABLE_NAME   | SUFFIX | HOST           | TABLE_DATA_SIZE | TABLE_STORAGE_SIZE | DATA_PERCENT |
+--------------+--------------+--------------+--------+----------------+-----------------+--------------------+--------------+
| vcname000001 | testdb       | testtabhash | n1     | 192.168.100.10 |            1619 |               2675 | 29.1554%     |
| vcname000001 | testdb       | testtabhash | n2     | 192.168.100.12 |            1923 |               2979 | 34.6299%     |
| vcname000001 | testdb       | testtabhash | n3     | 192.168.100.14 |            2011 |               3067 | 36.2147%     |
+--------------+--------------+--------------+--------+----------------+-----------------+--------------------+--------------+
3 rows in set (Elapsed: 00:00:00.01)

从查询结果可以看出，数据在各个节点上的分布还是比较均匀的，达到预期效果。

复制表

● 复制表将会存在于各个节点上，即表的名字和数据完全一致。
● 需要使用REPLICATED关键字来创建复制表。
● 复制表不用拉表即可实现本地运算，效率高。
● 一般来说，小表（维度表）可以被创建成复制表。
● 一些表频繁参与JOIN查询表也可以被创建成复制表

创建复制表

create table testtabrep(id int,name varchar(10)) replicated ;

查看表的类型

gbase> select dbName,tbName,isReplicate,hash_column   from gbase.table_distribution where tbname='testtabrep'; 
+--------+------------+-------------+-------------+
| dbName | tbName     | isReplicate | hash_column |
+--------+------------+-------------+-------------+
| testdb | testtabrep | YES         | NULL        |
+--------+------------+-------------+-------------+
1 row in set (Elapsed: 00:00:00.00)

从查询结果可以看出，isReplicate为'YES',说明testtabrep表是复制表。

插入500条数据并查看数据分布情况

gbase> delimiter $$
gbase> create procedure insertdata3()
    -> begin
    ->   declare i int default 1; 
    -> while i<101 do 
    -> insert into testtabrep(id,name) values(i,to_char(i));
    -> set i=i+1;  
    -> end while;
    -> end;
    -> $$
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> delimiter ;
gbase> call insertdata3();
Query OK, 1 row affected (Elapsed: 00:00:15.43)

gbase> select count(*) from testtabrep;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (Elapsed: 00:00:00.05)

gbase> select * from information_schema.cluster_table_segments a where table_schema='testdb' and table_name='testtabrep';
+--------------+--------------+------------+--------+----------------+-----------------+--------------------+--------------+
| TABLE_VC     | TABLE_SCHEMA | TABLE_NAME | SUFFIX | HOST           | TABLE_DATA_SIZE | TABLE_STORAGE_SIZE | DATA_PERCENT |
+--------------+--------------+------------+--------+----------------+-----------------+--------------------+--------------+
| vcname000001 | testdb       | testtabrep | n1     | 192.168.100.10 |            4981 |               6037 | 100%         |
+--------------+--------------+------------+--------+----------------+-----------------+--------------------+--------------+
1 row in set (Elapsed: 00:00:00.00)

从查询结果可以看出，数据只在1个节点，复制表的数据分布情况是不能通过这种方法查看的。复制表的数据分布情况需要登录到各gnode节点上查看gnode节点的数据文件大小，查看方法如下：
#node1节点
[gbase@node1 ~]$ cd  /data/192.168.100.10/gnode/userdata/gbase/testdb/sys_tablespace/testtabrep
[gbase@node1 testtabrep]$ ll
total 16
-rw------- 1 gbase gbase 1034 Jun 28 10:26 C00000.seg
-rw------- 1 gbase gbase 1032 Jun 28 10:26 C00000.seg.1
-rw------- 1 gbase gbase 1459 Jun 28 10:26 C00001.seg
-rw------- 1 gbase gbase 1456 Jun 28 10:26 C00001.seg.1
#node2节点
[gbase@node2 ~]$ cd /data/192.168.100.12/gnode/userdata/gbase/testdb/sys_tablespace/testtabrep
[gbase@node2 testtabrep]$ ll
total 16
-rw------- 1 gbase gbase 1034 Jun 28 10:26 C00000.seg
-rw------- 1 gbase gbase 1032 Jun 28 10:26 C00000.seg.1
-rw------- 1 gbase gbase 1459 Jun 28 10:26 C00001.seg
-rw------- 1 gbase gbase 1456 Jun 28 10:26 C00001.seg.1
#node3节点
[gbase@node3 ~]$ cd /data/192.168.100.14/gnode/userdata/gbase/testdb/sys_tablespace/testtabrep
[gbase@node3 testtabrep]$ ll
total 16
-rw------- 1 gbase gbase 1034 Jun 28 10:26 C00000.seg
-rw------- 1 gbase gbase 1032 Jun 28 10:26 C00000.seg.1
-rw------- 1 gbase gbase 1459 Jun 28 10:26 C00001.seg
-rw------- 1 gbase gbase 1456 Jun 28 10:26 C00001.seg.1

可以看出，各节点的数据文件大小完全一致，达到预期效果。

分布表、复制表使用场景

表类型	使用场景
复制表	维度表、参数表：这些表的内容几乎不会变动，数据量一般不大，适合作为复制表存储在各个节点上。变动极少的基础表：例如用户基础信息表，数据量可能很大，但更新频率不高，适合作为复制表以减少JOIN操作时的数据传输。数据量极少的频繁访问表：如汇总结果表，数据量小但访问频率高，使用复制表可以提高查询性能。
分布表	是主表，数据量超大的表：如明细表、事实表等，这些表的数据量巨大，适合采用分布存储方式以节省存储空间并提高查询效率。随机分布表: 数据随机分散到所有节点上，数据尽量按行数平均，适合数据访问模式不确定的情况。哈希分布表：适合于JOIN操作频繁且有明确JOIN键的场景。

表类型

使用场景

复制表

维度表、参数表：这些表的内容几乎不会变动，数据量一般不大，适合作为复制表存储在各个节点上。
变动极少的基础表：例如用户基础信息表，数据量可能很大，但更新频率不高，适合作为复制表以减少JOIN操作时的数据传输。
数据量极少的频繁访问表：如汇总结果表，数据量小但访问频率高，使用复制表可以提高查询性能。

分布表

是主表，数据量超大的表：如明细表、事实表等，这些表的数据量巨大，适合采用分布存储方式以节省存储空间并提高查询效率。
随机分布表: 数据随机分散到所有节点上，数据尽量按行数平均，适合数据访问模式不确定的情况。
哈希分布表：适合于JOIN操作频繁且有明确JOIN键的场景。

附录一、临时表

使用关键词TEMPORARY，临时表被限制在当前连接中，当连接关闭时，临时表会自动被删除。临时表可以是随机分布表、hash分布表、复制表中的任意一种表类型。
注意事项：

临时表支持除 ALTER 之外的所有 DDL 及 DML 操作。
临时表不能被备份。
临时表支持在当前连接中使用查询结果导出语句导出表中数据。

创建临时表

#随机分布临时表
gbase> CREATE TEMPORARY TABLE tem_table (a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
#hash分布临时表
gbase> CREATE TEMPORARY TABLE tem_table_hash (a int) distributed by ('a');
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
#复制临时表
gbase> CREATE TEMPORARY TABLE tem_table_rep (a int) replicated;            
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

附录二、表结构和表数据复制（CTAS使用）

根据列定义以及投影列创建新的表结构，并将 SELECT查询的数据复制到所创建的新表中。不指定目标表的类型时，默认创建随机分布表。

语法：
CREATE TABLE table_name [REPLICATED] [DISTRIBUTED BY] [AS] SELECT...；

示例：
#创建随机分布表t2
create table t2 as select * from t1 ;
#创建hash分布表t2
create table t2 distributed by ('fx') as select * from t1;
#创建复制表t2
create table t2 replicated as select * from t1;

附录三、表结构复制

分为两类：
类型1、根据列定义以及投影列创建新的表结构。…as select…limit 0;
类型2、只创建表结构，没有数据。目标表和源表的表类型完全一致。…like…

类型1示例：
create table t2 distributed by ('fx') as select * from t1 limit 0;
create table t2 replicated as select * from t1 limit 0;
create table t2 as select * from t1 limit 0;
类型2示例：
CREATE TABLE table_name2 LIKE table_name1;

总结

在选择GBase 8a数据库的分布表和复制表时，需要综合考虑数据量、数据访问模式、更新频率以及查询性能等因素，在实际操作中，可能需要结合性能测试和实际运行情况来不断调整和优化选择。

墨力计划南大通用gbase gbase 8a

最后修改时间：2024-06-28 10:38:31

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Gbase数据库（共6篇）

包括gbase8a,gbase8s,gbase8c数据库

南大通用Gbase8a MPP Cluster 管理-03分布表、复制表使用

Gbase8a MPP Cluster产品介绍以及安装部署

南大通用Gbase8a MPP Cluster 管理-01集群状态管理

南大通用Gbase8a MPP Cluster 管理-02分布信息管理

分布表

随机分布表

创建随机分布表

查看表的类型

插入500条数据并查看数据分布情况

哈希分布表

创建哈希分布表

查看表的类型

插入500条数据并查看数据分布情况

复制表

创建复制表

查看表的类型

插入500条数据并查看数据分布情况

分布表、复制表使用场景

附录一、临时表

创建临时表

附录二、表结构和表数据复制（CTAS使用）

附录三、表结构复制

总结

文章被以下合辑收录

评论