Greenplum数据库设计开发规范参考.docx

Greenplum数据库设计开发规范参考.docx

《Greenplum数据库设计开发规范参考.docx》由会员分享，可在线阅读，更多相关《Greenplum数据库设计开发规范参考.docx（59页珍藏版）》请在冰豆网上搜索。

Greenplum数据库设计开发规范参考

Greenplum数据库设计开发规范参考

Greenplum数据库设计开发规范

参考文档

2016年7月

1前言

1.1文档目的

随着Greenplum数据库仓库平台应用逐步上线，为了保证Greenplum数据仓库系统平台的平稳运行，保证系统的可靠性、稳定性、可维护性和高性能，特制定本开发规范，以规范基于Greenplum平台的应用开发，提高开发质量。

1.2文档范围

本规范主要包含Greenplum数据仓库平台应用开发的设计开发规范要求；适合于本行所有基于Greenplum数据仓库平台的应用开发。

1.3预期读者

Greenplum数据仓库平台应用的设计与开发人员；

Greenplum数据仓库平台的系统管理人员和数据库管理员；

Greenplum数据仓库平台的运行维护人员；

1.4参考资料

《GPDB43AdminGuide.pdf》

《GPDB43BestPractices.pdf》

2开发规范检查项

本规范主要用于指导Greenplum数据库平台的开发，通过规范要求提升开发质量。

本规范所提出的观点都是基于Greenplum数据库产品的最佳实践。

同样，作为系统或者项目的管理者，也可以通过该规范对开发质量进行审查和监督。

本章节的检查列表，是帮助系统管理人员审查开发质量，关注重点检查项。

检查项目列表：

序号

分类

检查项描述

1

系统级

是否有按照开发规范创建数据库角色：

1、创建子系统专用的用户

2、非超级用户

3、ETL跑批用户与前端用户区分开

2

资源队列检查：

数据库角色归属的资源队列是否符合规范，不允许使用默认队列pg_default

3

tablespace检查：

1、是否安装规范要求创建独立的tablespace。

2、表是否按照要求创建到该tablespace中。

3、检查相应的用户是否有配置默认tablespace

4

表属主检查：

检查表的属主（owner）是否按照规范，表属主都应该是子系统的用户，一般属主应该是跑批用户（*_trans）。

属主不允许是超级用户

5

库表设计

检查子系统的中表数量

6

检查分区表设计是否符合规范

1、如果表太大需要按天划分分区，只在半年内保留内的天分区；

2、按月分区只在5年内保留月分区；

3、五年前的历史分区都采用年分区；

4、拉链表会有特殊的分区，如：

p3*******、p0*******

5、单个分区表，子分区数量不要超过300个。

6、检查是否有没用的分区。

是否有没用的子分区则需要结合具体的业务需求来定

7

检查是否需要设置为分区表，分区粒度是否合适。

按照生产环境判断分区粒度的规则：

1、表的总记录数超过3亿，单表容量超过50GB，需要把表设计为分区表

2、该表在每个实例上记录数小于50万的表，无需进行分区，根据生产环境上实例数计算表总记录数小于XXX条记录，不需要设置为分区表

3、单个子分区的记录数小于1000万，说明分区粒度太细

8

检查默认分区是否有过多的数据记录

9

检查表压缩设计，统计各种压缩表的数量。

如果表的记录数小于1000万，该表不需要设计为压缩表。

10

倾斜率检查

11

ETL任务

automation中是否有配置子系统对应的vacuum和analyze任务

1、每日analyze任务

2、每周analyze任务

3、每周vacuum任务

12

automation中是否有配置相应的子系统的增加分区的操作

3GP与TD的差异关注点

经过信用卡集市的项目实施，总结的了一些编程和数据核对方面的GP数据库产品与TD数据库产品的特性差异。

1、中文字排序：

TD的中文字排序是按照汉语拼音的顺序进行排列。

而GP数据库的中文字排序由于其内码处理的关系，并不是按照中文拼音的顺序排列。

如需要达到按照拼音顺序排列的结果，需要对中文字符串进行处理。

目前已经封装了一个函数string_convert，用于转换后进行排序。

举例:

selectname,string_convert（name）fromtableAorderby2;

注意：

string_convert函数的结果不能用于展示，只可用于排序（orderby）。

2、char、varchar字段中汉字的长度差异

在目前TD库中，所有char、varchar字段定义中都把字符集设置为了LATIN,LATIN编码就代表在TD中是按照单字节的格式保存。

而经测试TD中中文字编码实际上是GBK。

这样一个汉字存储TD中长度为2。

在GP库中，所有数据存储编码统一为UTF8，即使一个汉字实际有3个字节，在数据库存储中一个汉字长度为1。

3、char、varchar字段中字母忽略大小写的差异

在目前TD库中，所有char和varchar字段的定义中都有NOTCASESPECIFIC关键字，指明字符的处理会忽略大小写。

在GP数据库中，所有存入数据库的都是统一按照原样大小写存储，并且处理时不会改变其属性，也不会忽略大小写。

如果需要忽略大小写，只能够采用upper或者lower等方法。

注意：

目前部分程序中已经采用upper操作实现忽略大小写的处理。

但upper或者lower操作必须要在GP库内部处理。

TD数据是按照LATIN单字节存储，不能在TD输出时就操作upper或者lower。

否则存储内容很可能会被破坏掉。

4、四舍五入算法的差异

GP数据库所采用的四舍五入算法是传统的四舍五入算法，如：

10.12345->10.1235

10.12355->10.1236

10.12346->10.1235

TD数据库所采用的四舍五入的算法比较特殊，主要是遇到5进位时的差异：

10.12345->10.1234

10.12355->10.1236

10.12365->10.1236

10.12375->10.1238

10.12346->10.1235（这个与GP相同）

4系统级设计

系统级对象是指整个GP集群级别的对象，并不属于某个数据库（Database）。

包括：

系统用户（Role）、表空间（Tablespace）、资源队列（Resourcequeue）等。

4.1用户设计

数据库中规划几类用户（DBRole）：

超级用户，公共查询用户，公共数据区用户以及各个集市系统的用户。

GP数据库用户信息列举如下：

序号

归属区域

用户类型

用户名

允许登录

权限描述

1

系统级

SYS

gpadmin

是

超级用户，用于运维

2

FRONT

dwituser

是

公共查询用户

3

公共数据区

BATCH

dwshdata_trans

是

公共数据区（sdata/pdata/ods）跑批用户

4

BACKEND

dwshdata_qry

否

公共数据层（sdata/pdata/ods）只读权限，用于其他用户继承

5

信用卡集市

BATCH

dwccd_trans

是

ccard子系统跑批用户，对象属主，公共数据select权限

6

FRONT

dwccarduser

是

前端查询，拥有建表权限，公共数据select权限

7

BATCH

dwbip_trans

是

bip子系统跑批用户，对象属主，公共数据select权限

8

FRONT

dwbipuser

是

前端查询，拥有建表权限，公共数据select权限

9

BATCH

dwcsp_trans

是

csp子系统跑批用户，对象属主，公共数据select权限

10

FRONT

dwcspuser

是

前端查询，拥有建表权限，公共数据select权限

11

BACKEND

dwccard_qry

否

查询权限继承：

grantdwccard_qryto...

12

市场风险集市

BATCH

dwmrdm_trans

是

mrdm子系统跑批用户，ccard属主，公共数据select权限

13

FRONT

dwmrdmuser

是

前端查询，拥有建表权限，公共数据select权限

XX集市

每种用户详细描述参加如下章节。

4.1.1超级用户

GP集群初始化好之后，默认的超级用户是gpadmin。

gpadmin用户既是操作系统用户也是数据库的超级用户。

应用于数据库的日常维护和故障处理，如：

数据库启停，数据库状态监控，数据库恢复等操作。

4.1.2公共查询用户

GP数据库中创建一个独立的公共查询用户dwituser。

该用于拥有数据库中所有表的查询权限，用于前台业务查询应用。

因此，所有表创建好后都需要给dwituser赋予select权限。

4.1.3公共数据区用户

公共数据区（sdata/pdata）主要用于接收从TD及其他系统导入的数据，并且对数据进行初步加工。

该区域的数据是其他集市的基础数据源，数据会供给上层所有集市系统。

该区主要是后台跑批任务，创建一个独立的后台跑批用户dwshdata_trans。

该用户是公共数据区库表的数据，拥有所有权限。

另外上层集市系统的用户都拥有公共数据区的select权限。

4.1.4集市系统用户

对于每一个集市系统参考以前TD的用户管理模式，都创建2类用户：

（1）一类是后台跑批用户，命名为*_trans，是归属子系统（schema）下表的属主（即owner），并且拥有其他子系统（schema）下表的查询权限；上线投产操作时，使用该用户创建表和其他对象，并且负责给其他用户赋权；

（2）另一类是前台查询用户，命名为*user，拥有其归属子系统（schema）下表的增删改查权限，并且拥有其他子系统（schema）下表的查询权限。

以信用卡决策分析系统集市为例，其下有三个子系统：

ccd、bip、csp。

各个子系统分别创建一个后台跑批用户：

dwccd_trans、dwbip_trans、dwcsp_trans；分别创建一个前台查询用户：

dwccarduser、dwbipuser、dwcspuser。

所有用户拥有公共数据区的select权限。

如果一个集市系统下面的子系统不止一个，为了方面于彼此之间授予select权限，可以创建一个专用于继承select权限的的用户（该用户不可登陆数据库）。

如：

信用卡集市dwccard_qry用户，信用卡集市的所有表、公共数据区的所有表都给该用户授予select权限。

然后dwccard_qry用户再把自身权限授予集市的所有其他用户，其他用户都拥有了查询权限。

以后，新增的库表也只需要把select权限授予dwccard_qry

用户间授权方法：

grantroledwccard_qrytodwccd_trans,dwbip_trans,dwcsp_trans,dwccarduser,dwbipuser,dwcspuser

所有集市系统都参考信用卡集市系统的用户创建自己的用户。

4.2数据库表空间设计

表空间（tablespace）允许DB管理员使用多个文件系统来存储数据库对象，从而可以决定如何更好的利用他们的物理储存设备。

使用表空间，允许用户使用不同性能的磁盘来存储访问频度不同的数据库对象。

例如，将经常使用的表放在高性能磁盘的文件系统上（比如SSD固态盘），而将其他表放在普通硬盘的文件系统上。

在Greenplum数据库中，表空间必须建立在文件空间之上，在GPDB中，Master和每个Instance（primary和mirror）都需要独立的存储位置（或者说目录）。

所有这些文件系统目录构成了GP系统中所谓的文件空间（filespace）。

文件空间定义之后，其可以被多个表空间使用。

如果单个表空间下的文件数量过多，会给系统维护操作，例如备份、节点恢复、扩容等操作带来不便；因此需控制表空间下的文件数量在一定范围。

建议每个表空间的文件对象数量不要超过20万；

表空间下的文件数量与数据表的数量、列存储表的数量列存表的列数、分区表的分区数量、索引数量等因素有关；可通过Altertable命令修改数据表所在表空间；

表空间的使用规范如下：

1.建议控制每个表空间下的文件数不要超过20万，如果超过20万，请将部分表迁移到其他表空间；

2.系统默认的表空间为pg_default,建议给不同数据库设置其默认表空间，以免太多的数据表都创建在pg_default上；

生产环境GP集群规划创建一个独立的filespace，命名为：

XXX_fs。

所有的表空间都创建在该文件系统上。

表空间规划建议如下：

1.共享及公共数据层分配一个独立的初始表空间：

ts_shdata_01

2.为每一个系统分配独立的初始表空间，例如：

信用卡决策支持系统表空间为ts_ccard_01。

每新增一个系统，需为其初始创建一个独立的表空间。

3.公共数据区或者每个系统相应使用的用户，都需要设置用户的默认表空间。

如：

dwshdata_trans用户的默认表空间为ts_shdata_01。

4.如果当前使用的表空间中超过15万个，则需要新创建另一个表空间，如：

ts_shdata_02，修改相应用户的默认表空间。

运维人员在日常巡检中，检查所有表空间对应的数据库目录下文件数超过15万以后，可以创建新的表空间，新建表空间名称需要符合命名规范。

表空间创建完成后，通过默认表空间参数（default_tablespace）或表空间改名等方法启用新的表空间。

获取每个实例，每个表空间对应的目录的方法：

1、查询系统初始默认表空间（pg_default）:

SELECT

spcnameastblspc,fsnameasfilespc,fsedbidasseg_dbid,conf.hostnameasseghost,

fselocation||'/base/'||（selectoidfrompg_databasewheredatname='bigdata'）:

:

text

asdatadir

FROMpg_tablespacepgts,pg_filespacepgfs,pg_filespace_entrypgfse,gp_segment_configurationconf

WHEREpgts.spcfsoid=pgfse.fsefsoidANDpgfse.fsefsoid=pgfs.oid

ANDpgfse.fsefsoid=3052ANDconf.dbid=pgfse.fsedbidandspcname='pg_default'

ORDERBYseg_dbid,tblspc;

2、查询自定义的表空间：

SELECT

spcnameastblspc,fsnameasfilespc,fsedbidasseg_dbid,conf.hostnameasseghost,

fselocation||'/'||pgts.oid:

:

text||'/'||（selectoidfrompg_databasewheredatname='bigdata'）:

:

text

asdatadir

FROMpg_tablespacepgts,pg_filespacepgfs,pg_filespace_entrypgfse,gp_segment_configurationconf

WHEREpgts.spcfsoid=pgfse.fsefsoidANDpgfse.fsefsoid=pgfs.oid

ANDpgfse.fsefsoid>3052ANDconf.dbid=pgfse.fsedbid

ORDERBYseg_dbid,tblspc;

查询到对应的主机和路径之后，到相应的路径下，执行：

find.|wc-l就可以统计出数据文件的数量

4.3资源队列设计

GPDB需要规划资源队列，以限定不用类型的数据库操作的可用资源阈值。

资源队列规划原则是按照数据库操作类型来规划：

资源开销大响应时间短、资源消耗大响应时间长、资源消耗小响应时间短、资源消耗小响应时间长（并发度很高）。

对于生产环境，我们建议至少会规划两个队列：

（1）que_batch：

所有后台跑批用户（*_trans）归属该资源队列。

应用特点是资源消耗大响应时间长。

createresourcequeueque_batchwith（active_statements=30,priority=medium,memory_limit='16GB'）;

（2）que_front：

用于所有前端即时查询用户（*user）的资源队列。

应用特点是随时可能运行一些资源消耗较高的SQL，白天要求优先级较高。

createresourcequeueque_frontwith（active_statements=5,priority=max,memory_limit='10GB',max_cost=400000000）;

（3）que_front_pri：

用于普通查询用户（dwituser）的资源队列。

应用特点是前端用户可能较多，但要求优先级不高

createresourcequeueque_front_priwith（active_statements=10,priority=medium,max_cost=400000000）;

关于资源队列的调整：

由于GP系统白天和晚上所执行的任务特点有所不同，白天需要兼顾前端查询和后端批量任务，晚上则完全是批量任务优先。

因此，通过两个脚本在一天的两个时间段按需调整白天和晚上的资源队列的相关配置。

检查各个角色对应的资源队列的方法：

selectrolname,rsqname

frompg_rolesaa,pg_resqueuebb

whereaa.rolresqueue=bb.oidorderby2;

4.4系统级的维护工作

系统级的维护工作主要是表的统计信息收集（analyze）以及表空间膨胀维护（vacuum）。

4.4.1系统表的维护工作

系统表的维护工作有master上的vacuum_analyze.sh脚本负责处理，存放路径是两台master服务器的/home/gpadmin/gpshell/。

由自动化平台调用每天执行。

执行语句：

sh/home/gpadmin/gpshell/vacuum_analyze.shbigdatapg_catalog

4.4.2各种库表的维护工作

日常各种库表的维护工作有三类：

1、每日analyze任务：

针对非分区表，每个跑批日期对应的子分区表收集统计信息。

2、每周analyze任务：

针对所有分区表的根分区进行统计信息收集。

3、每周vacuum任务：

针对膨胀超过50%的表进行vacuum。

每个子系统都应该配置有上述三个维护操作的尾任务。

4.4.3投产前统一收集统计信息

对于新投产的子系统，在初始化的数据后需要进行统一的analyze操作，可使用master服务器上的/home/gpadmin/gpdbanalyze/目录下的脚本进行操作：

1、对整个schema进行analyze操作，可使用analyze_for_schema.pl脚本，举例：

perlanalyze_for_schema.plbigdatadwmart_fis12

2、如果针对某些表进行analyze操作，可以使用gpdbanalyze.sh脚本，举例：

先把待处理的表名放到一个文件中，如tablelist.txt，每一行一个表名

shgpdbanalyze.shtablelist.txt

5命名规范

1、对象名称只能使用字母、数字、下划线；不允许使用特殊字符；

2、Schema总长度不得超过30个字符；

3、原则上表/视图名称长度不超过30个字符，外部表不超过34个字符；

4、TableSpace长度不超过60字符。

5、关于表的子分区（partitionname）命名需要统一规范，按日期分区的建议采用字母“p”加上日期的方式命名，如：

p20151201、p201512、p2015。

6数据库对象设计规范

6.1数据库对象数据量

1.数据库对象类型包括数据表、视图、函数、序列、索引等等，在Greenplum数据库中，系统元数据保存在Master服务器上，过多的数据库对象会造成系统元数据的膨胀，而过多的系统元数据造成系统运行偏慢，以及数据库的备份、恢复、扩容等操作都造成困难，因此，单个数据库的对象数量，应控制在一定范围之内，应尽量减少数据库对象数量，单个数据库的对象数量建议控制在10万以内；

对象包括：

表、视图、索引、分区子表、外部表。

2.如果数据表的数量太多，建议按应用域进行分库，尽量将单个数据库的表数量控制在10万以内（备注：

在Greenplum数据库中，一张分区表，在数据库中存储为一张父表、一张默认分区的子表、与分区数一样多的分区子表，例如：

一张按月进行分区的存储一年数据的表，在Greenplum中为14张表），可以在一个集群中创建多个数据库；

6.2表创建规范

为了避免数据库表数量太多，避免单个数据表的数据量过大，给系统的运行和使用带来困难，在Greenplum数据库中需遵循如下的表创建规范：

1.在数据仓库模型设计时，通过允许一定数量的数据冗余，降低数据模型的范式要求，对相关数据表进行合并，减少数据表数据量；

2.单个数据库的数据表数量建议不要超过10万张；

3.禁止使用二级分区表，因为二级分区表会造成表对象数量的急剧膨胀；

4.每个表空间目录下的数据文件数量，不允许超过20万，因为过多的数据文件会给系统维护带来困难，如果数据文件数量过多，建议增加多个表空间，把数据表均匀分布到不同的表空间；

表空间数据文件数量估算方法为：

行存表数量+行存表的分区子表数量+行存表的索引数量+行存表的分区子表的索引数量+列存表的字段总数+列存表的分区子表的字段总数+列存表的索引数量+列存表的分区子表的索引数量。

举例说明：

假设表空间下保存如下几张表：

TableA:

按行存储，无分区，索引数量为5；

TableB：

按行存储，按机构分区，40个子分区，索引数量为5；

TableC:

按列存储，20个字段，无分区，索引数量为5；

TableD:

按列存储，20个字段，按机构分区，40个子分区，索引数量为5

那么总的数据文件数量估算为：

行存表数量：

2（TableA,TableB）

行存表的分区子表数量：

40（TableB的子分区总数）

行存表的索引数量：

10（TableA5个索引+TableB5个索引）

行存表的分区子表索引数量：

40*5=200（40个分区，每分区5个索引）

列存表的字段总数：

20+20=40；

列存表的分区子表的字段总数：

40*20=800；

列存表的索引数量：

5+5=10

列存表的分区子表的索引数量：

40*5=200；

所以，数据文件总数估计为：

2+40+10+200+40+800+10+200=1302；

5.创建数据表的时候，必须使用tablespace子句指定用于存储表的表空间,而不是把所有表都存储在默认表空间；例如：

Createtableemployee（idint,namevarchar）

TA