新建表
哈希分布表
将表中某列指定为哈希分布列,然后将数据按照哈希算法的取值存储到不同的节点上。每个节点上只存储一部分数据。这种存储策略,将大表数据进行分拆,实现分布式存储,是大型数据中心最常用的数据分布方式。
HASH分布列选取规则
- 尽量选择count(distinct)值大的列做Hash分布列,让数据均匀分布。
- 优先考虑大表间的JOIN,尽量让大表JOIN条件的列为Hash分布列(相关子查询的相关JOIN也可以参考此原则),以使得大表间的JOIN可以直接分布式执行。
- 其次考虑GROUP BY,尽量让GROUP BY带有Hash分布列,让分组聚合一步完成。
- 通常是等值查询的列,并且使用的频率很高的应考虑建立为hash分布列。
- 选择某数据列随机性很大的字段,避免部分节点的热查询。
注意事项
- hash分布键只能选择 1 个。
- hash分布键只能选择varchar字符型和int等整数类型的列。
- 作为hash分布列的列不能进行update。
- HASH分布列不允许设置 default 值。
- 尽量保持hash join的等值关联列在类型定义上完全相同,如char和varchar类型进行关联,可能出现结果为空情况,原因是char型不足最大长度时用空格补齐,varchar则没有空格,如果关联则需要trim空格。
- 另外,由于GBase 8a MPP Cluster是列存储的集群架构,因此当列数较多,访问的数据记录又非常离散时,会造成大量的离散I/O,严重影响查询性能。 GBase 8a MPP Cluster提供行列混存功能,即通过存储冗余行的信息,提高I/O性能。
举例
create table IF NOT EXISTS TB_DW_AC_ACCT_DETAIL_BILL_SUM(
ACCT_ITEM_ID VARCHAR(5) comment '帐目编码',
USER_ID VARCHAR(13) comment '用户编码',
CREATE_TIME DATETIME comment '生成时间',
BEGIN_TIME DATETIME comment '帐单开始时间',
END_TIME DATETIME comment '帐单截止时间',
RECEIVABLE_FEE int comment '应收费用',
FAV_FEE int comment '优惠费用',
ACCT_CYCLE_ID int comment '帐务周期编码',
FAV_ID VARCHAR(1) comment '优惠编码',
BILL_TYPE_ID VARCHAR(1) comment '帐单类型',
ACCT_ID VARCHAR(12) comment '帐户标识',
TAX_RATE int comment '<2G',
TAX_FEE bigint comment '',
TAX_OFF_FEE bigint comment ''
) DISTRIBUTED BY ('USER_ID');
复制表
复制表在集群的每个节点都保存一份全量数据,再与其它表进行关联查询时可以直接在本节点上完成,无需与其它节点进行交互,因此性能最优。但由于各个节点上数据完全相同,导致存储空间增加。
一般数据量初始化后比较固定、数据量相对大表比较小(比如百万条记录以下级别的)的表,可以创建为复制表;常见的有编码表、维表等。
说明
复制表表名尾部不允许是_n{number}编号,例如, mytable_n1,mytable_n12 是不允许使用的,因为GBase集群的分布表在gnode的层以_n{number}的表名进行数据的存储和管理;如果复制表以_n{number}的形式命名,就有可能造成gnode层的表冲突和混乱。
例如
复制表命名为mytab_n1,那么再创建分布表mytab时,在sg01组的gnode中就会出现mytab_n1表的冲突。
举例
create table IF NOT EXISTS TB_DW_DIC_ZC_SERVICEGROUP_ID(
serv_id1 BIGINT comment '业务大类ID',
serv_name1 VARCHAR(256) comment '业务大类名称',
serv_id2 BIGINT comment '业务小类ID'
serv_name2 VARCHAR(256) comment '业务小类名称',
serv_id3 BIGINT comment '源自业务ID',
serv_name3 VARCHAR(256) comment '源自业务名称',
serv_type VARCHAR(256) comment '业务类型'
) REPLICATED;
建议
100万数据以上建哈希分布表
100万以下建复制表
新建、删除分区
分区表增加分区
gbase> alter table pt add partition(partition p2 values less than(30));
gbase> alter table pt add partition (partition p3 values less than maxvalue);
分区表删除分区
gbase> alter table pt drop partition p1;
--删除分区会直接删除分区中的数据
PS:为提高效率,减少分区个数,建议按月作为分区
CREATE TABLE dw.dws_user_icms_fact_klnb_crdt_appl_loan_cur_test5(
appl_id varchar(57) DEFAULT NULL COMMENT '申请号',
txn_sn_no varchar(120) DEFAULT NULL COMMENT '交易流水号',
……
day date DEFAULT NULL
) distributed by ('appl_id')
PARTITION BY RANGE (day)
(PARTITION part_202101 VALUES LESS THAN (20210131) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
PARTITION part_202102 VALUES LESS THAN (20210228) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
……)
常用命令
新建测试表
gbase> CREATE TABLE t (a int NOT NULL DEFAULT 1, b varchar(10));
一:增加列
gbase> ALTER TABLE t ADD column c varchar(10) null;
二:删除列
gbase> ALTER TABLE t DROP column b;
三:修改表名称
gbase> alter table t rename ttt;
或者
gbase> rename table ttt to t;
四:修改列名称
gbase> alter table ttt change c d varchar(10);
c:原字段名
d:新字段名
五:变更列的位置至最前
gbase> alter table ttt modify d varchar(10) first;
六:变更某列的位置到指定列的后面
gbase> alter table ttt modify d varchar(10) after a;
新建分区表
gbase> create table pt (i int ,c varchar(10),d date) partition by range(i) ( partition p0 values less than(10),partition p1 values less than(20));
七:分区表增加分区
gbase> alter table pt add partition(partition p2 values less than(30));
gbase> alter table pt add partition (partition p3 values less than maxvalue);
八:分区表删除分区
gbase> alter table pt drop partition p1;
--删除分区会直接删除分区中的数据
九:删除hash列
删除表定义中的哈希列定义,使得表变为随机分布表
gbase> drop distributed column on testtt;
十:修改表的预租磁盘
gbase> alter table wh autoextend on next 2M;
十一:关闭表的预租磁盘
gbase> alter table wh autoextend off;