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

1、Greenplum数据库设计开发规范参考Greenplum数据库设计开发规范参考Greenplum数据库设计开发规范参考文档2016年7月1 前言1.1 文档目的随着Greenplum 数据库仓库平台应用逐步上线，为了保证Greenplum 数据仓库系统平台的平稳运行，保证系统的可靠性、稳定性、可维护性和高性能，特制定本开发规范，以规范基于Greenplum平台的应用开发，提高开发质量。1.2 文档范围本规范主要包含Greenplum 数据仓库平台应用开发的设计开发规范要求；适合于本行所有基于Greenplum 数据仓库平台的应用开发。1.3 预期读者Greenplum数据仓库平台应用的设计与

2、开发人员；Greenplum 数据仓库平台的系统管理人员和数据库管理员；Greenplum 数据仓库平台的运行维护人员；1.4 参考资料GPDB43AdminGuide.pdfGPDB43BestPractices.pdf2 开发规范检查项本规范主要用于指导Greenplum数据库平台的开发，通过规范要求提升开发质量。本规范所提出的观点都是基于Greenplum数据库产品的最佳实践。同样，作为系统或者项目的管理者，也可以通过该规范对开发质量进行审查和监督。本章节的检查列表，是帮助系统管理人员审查开发质量，关注重点检查项。检查项目列表：序号分类检查项描述1系统级是否有按照开发规范创建数据库角色：

3、1、创建子系统专用的用户2、非超级用户3、ETL跑批用户与前端用户区分开2资源队列检查：数据库角色归属的资源队列是否符合规范，不允许使用默认队列pg_default3tablespace检查：1、是否安装规范要求创建独立的tablespace。2、表是否按照要求创建到该tablespace中。3、检查相应的用户是否有配置默认tablespace4表属主检查：检查表的属主（owner）是否按照规范，表属主都应该是子系统的用户，一般属主应该是跑批用户（*_trans）。属主不允许是超级用户5库表设计检查子系统的中表数量6检查分区表设计是否符合规范1、如果表太大需要按天划分分区，只在半年内保留内的天

4、分区；2、按月分区只在5年内保留月分区；3、五年前的历史分区都采用年分区；4、拉链表会有特殊的分区，如：p3*、p0*5、单个分区表，子分区数量不要超过300个。6、检查是否有没用的分区。是否有没用的子分区则需要结合具体的业务需求来定7检查是否需要设置为分区表，分区粒度是否合适。按照生产环境判断分区粒度的规则：1、表的总记录数超过3亿，单表容量超过50GB，需要把表设计为分区表2、该表在每个实例上记录数小于50万的表，无需进行分区，根据生产环境上实例数计算表总记录数小于XXX条记录，不需要设置为分区表3、单个子分区的记录数小于1000万，说明分区粒度太细8检查默认分区是否有过多的数据记录9检查

5、表压缩设计，统计各种压缩表的数量。如果表的记录数小于1000万，该表不需要设计为压缩表。10倾斜率检查11ETL任务automation中是否有配置子系统对应的vacuum和analyze任务1、每日analyze任务2、每周analyze任务3、每周vacuum任务12automation中是否有配置相应的子系统的增加分区的操作3 GP与TD的差异关注点经过信用卡集市的项目实施，总结的了一些编程和数据核对方面的GP数据库产品与TD数据库产品的特性差异。1、中文字排序：TD的中文字排序是按照汉语拼音的顺序进行排列。而GP数据库的中文字排序由于其内码处理的关系，并不是按照中文拼音的顺序排列。如需

6、要达到按照拼音顺序排列的结果，需要对中文字符串进行处理。目前已经封装了一个函数string_convert，用于转换后进行排序。举例:select name,string_convert(name) from tableA order by 2;注意：string_convert函数的结果不能用于展示，只可用于排序（order by）。2、char、varchar字段中汉字的长度差异在目前TD库中，所有char、varchar字段定义中都把字符集设置为了LATIN,LATIN编码就代表在TD中是按照单字节的格式保存。而经测试TD中中文字编码实际上是GBK。这样一个汉字存储TD中长度为2。在GP

7、库中，所有数据存储编码统一为UTF8，即使一个汉字实际有3个字节，在数据库存储中一个汉字长度为1。3、char、varchar字段中字母忽略大小写的差异在目前TD库中，所有char和varchar字段的定义中都有NOT CASESPECIFIC关键字，指明字符的处理会忽略大小写。在GP数据库中，所有存入数据库的都是统一按照原样大小写存储，并且处理时不会改变其属性，也不会忽略大小写。如果需要忽略大小写，只能够采用upper或者lower等方法。注意：目前部分程序中已经采用upper操作实现忽略大小写的处理。但upper或者lower操作必须要在GP库内部处理。TD数据是按照LATIN单字节存储，

8、不能在TD输出时就操作upper或者lower。否则存储内容很可能会被破坏掉。4、四舍五入算法的差异GP数据库所采用的四舍五入算法是传统的四舍五入算法，如：10.12345 - 10.123510.12355 - 10.123610.12346 - 10.1235TD数据库所采用的四舍五入的算法比较特殊，主要是遇到5进位时的差异：10.12345 - 10.123410.12355 - 10.123610.12365 - 10.123610.12375 - 10.123810.12346 - 10.1235(这个与GP相同)4 系统级设计系统级对象是指整个GP集群级别的对象，并不属于某个数据库

9、（Database）。包括：系统用户（Role）、表空间（Tablespace）、资源队列（Resource queue）等。4.1 用户设计数据库中规划几类用户（DB Role）：超级用户，公共查询用户，公共数据区用户以及各个集市系统的用户。GP数据库用户信息列举如下：序号归属区域用户类型用户名允许登录权限描述1系统级SYSgpadmin是超级用户，用于运维2FRONTdwituser是公共查询用户3公共数据区BATCHdwshdata_trans是公共数据区(sdata/pdata/ods)跑批用户4BACKENDdwshdata_qry否公共数据层(sdata/pdata/ods)只读权

10、限，用于其他用户继承5信用卡集市BATCHdwccd_trans是ccard子系统跑批用户，对象属主，公共数据select权限6FRONTdwccarduser是前端查询，拥有建表权限，公共数据select权限7BATCHdwbip_trans是bip子系统跑批用户，对象属主，公共数据select权限8FRONTdwbipuser是前端查询，拥有建表权限，公共数据select权限9BATCHdwcsp_trans是csp子系统跑批用户，对象属主，公共数据select权限10FRONTdwcspuser是前端查询，拥有建表权限，公共数据select权限11BACKENDdwccard_qry否查

11、询权限继承：grant dwccard_qry to .12市场风险集市BATCHdwmrdm_trans是mrdm子系统跑批用户，ccard属主，公共数据select权限13FRONTdwmrdmuser是前端查询，拥有建表权限，公共数据select权限XX集市每种用户详细描述参加如下章节。4.1.1 超级用户GP集群初始化好之后，默认的超级用户是gpadmin。gpadmin用户既是操作系统用户也是数据库的超级用户。应用于数据库的日常维护和故障处理，如：数据库启停，数据库状态监控，数据库恢复等操作。4.1.2 公共查询用户GP数据库中创建一个独立的公共查询用户dwituser。该用于拥有数

12、据库中所有表的查询权限，用于前台业务查询应用。因此，所有表创建好后都需要给dwituser赋予select权限。4.1.3 公共数据区用户公共数据区（sdata/pdata）主要用于接收从TD及其他系统导入的数据，并且对数据进行初步加工。该区域的数据是其他集市的基础数据源，数据会供给上层所有集市系统。该区主要是后台跑批任务，创建一个独立的后台跑批用户dwshdata_trans。该用户是公共数据区库表的数据，拥有所有权限。另外上层集市系统的用户都拥有公共数据区的select权限。4.1.4 集市系统用户对于每一个集市系统参考以前TD的用户管理模式，都创建2类用户：（1）一类是后台跑批用户，命名

13、为*_trans，是归属子系统（schema）下表的属主（即owner），并且拥有其他子系统（schema）下表的查询权限；上线投产操作时，使用该用户创建表和其他对象，并且负责给其他用户赋权；（2）另一类是前台查询用户，命名为*user，拥有其归属子系统（schema）下表的增删改查权限，并且拥有其他子系统（schema）下表的查询权限。以信用卡决策分析系统集市为例，其下有三个子系统：ccd、bip、csp。各个子系统分别创建一个后台跑批用户：dwccd_trans、dwbip_trans、dwcsp_trans；分别创建一个前台查询用户：dwccarduser、dwbipuser、dwcsp

14、user。所有用户拥有公共数据区的select权限。如果一个集市系统下面的子系统不止一个，为了方面于彼此之间授予select权限，可以创建一个专用于继承select权限的的用户（该用户不可登陆数据库）。如：信用卡集市dwccard_qry用户，信用卡集市的所有表、公共数据区的所有表都给该用户授予select权限。然后dwccard_qry用户再把自身权限授予集市的所有其他用户，其他用户都拥有了查询权限。以后，新增的库表也只需要把select权限授予dwccard_qry用户间授权方法：grant role dwccard_qry to dwccd_trans, dwbip_trans, dwc

15、sp_trans, dwccarduser, dwbipuser, dwcspuser所有集市系统都参考信用卡集市系统的用户创建自己的用户。4.2 数据库表空间设计表空间(tablespace)允许DB管理员使用多个文件系统来存储数据库对象，从而可以决定如何更好的利用他们的物理储存设备。使用表空间，允许用户使用不同性能的磁盘来存储访问频度不同的数据库对象。例如，将经常使用的表放在高性能磁盘的文件系统上(比如SSD固态盘)，而将其他表放在普通硬盘的文件系统上。 在Greenplum数据库中，表空间必须建立在文件空间之上，在GPDB中，Master和每个Instance(primary和mirro

16、r)都需要独立的存储位置(或者说目录)。所有这些文件系统目录构成了GP系统中所谓的文件空间(filespace)。文件空间定义之后，其可以被多个表空间使用。如果单个表空间下的文件数量过多，会给系统维护操作，例如备份、节点恢复、扩容等操作带来不便；因此需控制表空间下的文件数量在一定范围。建议每个表空间的文件对象数量不要超过20万；表空间下的文件数量与数据表的数量、列存储表的数量列存表的列数、分区表的分区数量、索引数量等因素有关；可通过Alter table 命令修改数据表所在表空间；表空间的使用规范如下：1. 建议控制每个表空间下的文件数不要超过20万，如果超过20万，请将部分表迁移到其他表空间

17、；2. 系统默认的表空间为pg_default,建议给不同数据库设置其默认表空间，以免太多的数据表都创建在pg_default 上；生产环境GP集群规划创建一个独立的filespace，命名为：XXX_fs。所有的表空间都创建在该文件系统上。表空间规划建议如下：1.共享及公共数据层分配一个独立的初始表空间：ts_shdata_012.为每一个系统分配独立的初始表空间，例如：信用卡决策支持系统表空间为ts_ccard_01。每新增一个系统，需为其初始创建一个独立的表空间。3.公共数据区或者每个系统相应使用的用户，都需要设置用户的默认表空间。如：dwshdata_trans用户的默认表空间为ts_

18、shdata_01。4.如果当前使用的表空间中超过15万个，则需要新创建另一个表空间，如：ts_shdata_02，修改相应用户的默认表空间。运维人员在日常巡检中，检查所有表空间对应的数据库目录下文件数超过15万以后，可以创建新的表空间，新建表空间名称需要符合命名规范。表空间创建完成后，通过默认表空间参数（default_tablespace）或表空间改名等方法启用新的表空间。获取每个实例，每个表空间对应的目录的方法：1、查询系统初始默认表空间（pg_default）:SELECT spcname as tblspc, fsname as filespc, fsedbid as seg_dbi

19、d, conf.hostname as seghost,fselocation|/base/|(select oid from pg_database where datname=bigdata):textas datadirFROM pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfse, gp_segment_configuration confWHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid AND pgfse.fsefsoid=3052 A

20、ND conf.dbid=pgfse.fsedbid and spcname=pg_defaultORDER BY seg_dbid,tblspc;2、查询自定义的表空间：SELECT spcname as tblspc, fsname as filespc, fsedbid as seg_dbid, conf.hostname as seghost,fselocation|/|pgts.oid:text|/|(select oid from pg_database where datname=bigdata):textas datadirFROM pg_tablespace pgts, pg

21、_filespace pgfs, pg_filespace_entry pgfse, gp_segment_configuration confWHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid AND pgfse.fsefsoid3052 AND conf.dbid=pgfse.fsedbidORDER BY seg_dbid,tblspc;查询到对应的主机和路径之后，到相应的路径下，执行：find . |wc -l就可以统计出数据文件的数量4.3 资源队列设计GPDB需要规划资源队列，以限定不用类型的数据库操作的可用

22、资源阈值。资源队列规划原则是按照数据库操作类型来规划：资源开销大响应时间短、资源消耗大响应时间长、资源消耗小响应时间短、资源消耗小响应时间长（并发度很高）。对于生产环境，我们建议至少会规划两个队列：（1）que_batch：所有后台跑批用户（*_trans）归属该资源队列。应用特点是资源消耗大响应时间长。create resource queue que_batch with (active_statements=30,priority=medium,memory_limit=16GB);（2）que_front：用于所有前端即时查询用户（*user）的资源队列。应用特点是随时可能运行一些资源

23、消耗较高的SQL，白天要求优先级较高。create resource queue que_front with (active_statements=5,priority=max,memory_limit=10GB,max_cost=400000000);（3）que_front_pri：用于普通查询用户（dwituser）的资源队列。应用特点是前端用户可能较多，但要求优先级不高create resource queue que_front_pri with (active_statements=10,priority=medium,max_cost=400000000);关于资源队列的调整：

24、由于GP系统白天和晚上所执行的任务特点有所不同，白天需要兼顾前端查询和后端批量任务，晚上则完全是批量任务优先。因此，通过两个脚本在一天的两个时间段按需调整白天和晚上的资源队列的相关配置。检查各个角色对应的资源队列的方法：select rolname,rsqname from pg_roles aa,pg_resqueue bbwhere aa.rolresqueue=bb.oid order by 2;4.4 系统级的维护工作系统级的维护工作主要是表的统计信息收集（analyze）以及表空间膨胀维护（vacuum）。4.4.1 系统表的维护工作系统表的维护工作有master上的vacuum_a

25、nalyze.sh脚本负责处理，存放路径是两台master服务器的/home/gpadmin/gpshell/。由自动化平台调用每天执行。执行语句：sh /home/gpadmin/gpshell/vacuum_analyze.sh bigdata pg_catalog4.4.2 各种库表的维护工作日常各种库表的维护工作有三类：1、每日analyze任务：针对非分区表，每个跑批日期对应的子分区表收集统计信息。2、每周analyze任务：针对所有分区表的根分区进行统计信息收集。3、每周vacuum任务：针对膨胀超过50%的表进行vacuum。每个子系统都应该配置有上述三个维护操作的尾任务。4.4

26、.3 投产前统一收集统计信息对于新投产的子系统，在初始化的数据后需要进行统一的analyze操作，可使用master服务器上的/home/gpadmin/gpdbanalyze/目录下的脚本进行操作：1、对整个schema进行analyze操作，可使用analyze_for_schema.pl脚本，举例：perl analyze_for_schema.pl bigdata dwmart_fis 122、如果针对某些表进行analyze操作，可以使用gpdbanalyze.sh脚本，举例：先把待处理的表名放到一个文件中，如tablelist.txt，每一行一个表名sh gpdbanalyze.s

27、h tablelist.txt5 命名规范1、对象名称只能使用字母、数字、下划线；不允许使用特殊字符；2、Schema总长度不得超过30个字符；3、原则上表/视图名称长度不超过30个字符，外部表不超过34个字符；4、TableSpace 长度不超过60字符。5、关于表的子分区（partitionname）命名需要统一规范，按日期分区的建议采用字母“p”加上日期的方式命名，如：p20151201、p201512、p2015。6 数据库对象设计规范6.1 数据库对象数据量1. 数据库对象类型包括数据表、视图、函数、序列、索引等等，在Greenplum数据库中，系统元数据保存在Master 服务器上

28、，过多的数据库对象会造成系统元数据的膨胀，而过多的系统元数据造成系统运行偏慢，以及数据库的备份、恢复、扩容等操作都造成困难，因此，单个数据库的对象数量，应控制在一定范围之内，应尽量减少数据库对象数量，单个数据库的对象数量建议控制在10万以内；对象包括：表、视图、索引、分区子表、外部表。2. 如果数据表的数量太多，建议按应用域进行分库，尽量将单个数据库的表数量控制在10万以内（备注：在Greenplum数据库中，一张分区表，在数据库中存储为一张父表、一张默认分区的子表、与分区数一样多的分区子表，例如：一张按月进行分区的存储一年数据的表，在Greenplum中为14张表），可以在一个集群中创建多个

29、数据库；6.2 表创建规范为了避免数据库表数量太多，避免单个数据表的数据量过大，给系统的运行和使用带来困难，在Greenplum数据库中需遵循如下的表创建规范：1. 在数据仓库模型设计时，通过允许一定数量的数据冗余，降低数据模型的范式要求，对相关数据表进行合并，减少数据表数据量；2. 单个数据库的数据表数量建议不要超过10万张；3. 禁止使用二级分区表，因为二级分区表会造成表对象数量的急剧膨胀；4. 每个表空间目录下的数据文件数量，不允许超过20万，因为过多的数据文件会给系统维护带来困难，如果数据文件数量过多，建议增加多个表空间，把数据表均匀分布到不同的表空间；表空间数据文件数量估算方法为：行

30、存表数量+行存表的分区子表数量+行存表的索引数量+行存表的分区子表的索引数量+列存表的字段总数+列存表的分区子表的字段总数+列存表的索引数量+列存表的分区子表的索引数量。举例说明：假设表空间下保存如下几张表：TableA: 按行存储，无分区，索引数量为5；TableB：按行存储，按机构分区，40个子分区，索引数量为5；TableC: 按列存储，20个字段，无分区，索引数量为5；TableD: 按列存储，20个字段，按机构分区，40个子分区，索引数量为5那么总的数据文件数量估算为：行存表数量：2 （TableA ,TableB） 行存表的分区子表数量：40 （TableB的子分区总数）行存表的索

31、引数量：10 （TableA 5个索引+ TableB 5个索引）行存表的分区子表索引数量：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 子句指定用于存储表的表空间,而不是把所有表都存储在默认表空间；例如：Create table employee ( id int,name varchar) TA