山西移动迁移数据库结构设计.docx

192a5deeb3bb91a7a4d1c8853e92e5ea.pdf 山西移动经营分析系统基础数据迁移项目数据库结构设计说明书12文档变更记录序号变更内容说明版本号版本日期执笔人目 录1.数据库系统设计31.1.数据库设计31.2.数据库角色设计41.3.数据库用户设计42.数据库权限设计53.数据库命名规范73.1.表类型73.2.持久类型83.3.存储类型83.4.压缩类型83.5.表的定义规范94.数据库备份设计115.数据库工作负载设计114.1.service calss114.2.workload policy111. 数据库系统设计1.1. 数据库设计安装好astercluster后，系统默认有beehive数据库。我们建立sxaster作为迁移项目的数据库。sxaster下建立若干schema，分别存放不同类型的数据，包含所有关于数据的schema、视图、sys_calendar等。其中nc_system和public是两个系统schema。nc_schema是存放关于数据库的元数据信息，包括一些只读视图、系统自带的mr函数，所有的用户都可以读写public schema，它存放开发的mr函数 。具体说明如下：create database sxaster encoding = utf8 ;schema名称说明public系统schema，存放自带的mr函数和元数据信息视图nc_system系统schema，存放开发的mr函数ptemp3系统运行时所使用的临时表在这里产生，一般在系统上线后建立临时表。pcde3存放码表数据pdata3存放除话单外的而基础数据pmid3存放中间层的数据ttemp测试的临时数据sys_calendar日历表plog加载过程中的出错信息pcdr3话单数据pcdevw3码表数据的视图pdatavw3基础数据的视图pmidvw3中间层数据的视图pcdrvw3话单数据的视图.1.2. 数据库角色设计数据库中默认的角色有db_admin、amc_admin、catalog_admin、node_admin、node_viewer、process_admin、process_runner、process_viewer8个，db_admin是管理角色、catalog_admin角色查看所有的系统表，不能访问所有的数据库角色名称描述备注db_admin管理角色，建用户、库默认存在catalog_admin可以查看所有的系统表默认存在amc_admin监控amc默认存在node_adminnode_viewerprocess_adminprocess_runnerprocess_viewerr_analysis省公司经营分析角色新建r_dev开发测试新建r_user省公司普通查询新建r_arc备份恢复新建r_cmcc地市角色新建1.3. 数据库用户设计安装好aster cluster后，系统有两个默认的用户db_superuser和beehive，其中db_superuser是管理员用户，它可以无限制地访问和管理所有的aster数据库中的对象。用户名描述备注db_superuser超级用户默认存在beehive没有管理权限默认存在aster_admin仅次于db_superuser新建dtetl加载用户新建dtmid中间层用户新建dtsyc同步到td用户新建ddsyn一经用户新建dwpar开发用户新建dspar开发用户新建省公司用户新建u01-u11地市用户新建dtexp导出用户新建2. 数据库权限设计数据库的权限会动态调整。dtetlselect insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendardtmidselect insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendardtsycselect insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendardwpar/dsparselect insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendardtsyc/dsexpselect insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendaru01-u11select insertupdatedeletecreateinstall filecreate functionusageexecuteplogptemp3pdata3pcde3pcdr3pmid3pdatavw3pcdevw3pcdrvw3pmidvw3ttempsys_calendar3. 数据库命名规范1.2.3.3.1. 表类型aster 建立的表的类型有两种：a: 事实表(fact)：它是默认的；必须有distribute by hash；推荐时间类型的字段作为分区字段；选取distribute key 的原则是单一列、非空、数据类型是int smallint bigint numeric numeric(p) numeric(p,a) text varchar varchar(n) uuid bytea 中的一种，不能使用char、date作为distribute key ； 大的dimension table 也使用distribute by hashb:维表(dimension)：一般是未压缩的，distribute by replication, 代表该维表在每个vworker上复制，未使用distribute by，则默认是默认distribute by replication。对于表的记录数大于1000行，不考虑distribute by replication。3.2. 持久类型aster 的表有三种持久类型: a:permanent 建表时默认是permanent； 结果数据都是permanent类型。 b:analytic 一些不需要永久保留的数据可以建成analytic，如加载阶段的临时表、中间过程的表； 这种表用的资源比regular table 少，存储空间也比regular table少一半 c: temp 仅仅在事务中可以创建； 事务结束，这种类型的表立即消失； 其他用户无法使用；3.3. 存储类型 aster的表有两种类型的存储：a:列存储 对于select 操作是比较好的，但是不能update、delete columnar table ;b:行存储 建表默认是行存储；3.4. 压缩类型有四种压缩类型，分别是high、medium、low、none。推荐使用low 类型。综上，可以参考下列语句进行建表：create fact table pmid3.tb_mid_bll_vc_user_day( deal_date date ,sys_type_code character(2) ,user_id numeric(14,0) . ) distribute by hash(user_id)storage rowcompress lowpartition by range(deal_date) -一级分区( partition tb_mid_bll_vc_user_day20140120 (start 2014-01-20 end 2014-01-20 inclusive compress low),partition tb_mid_bll_vc_user_day_20140209 (start 2014-02-09 end 2014-02-09 inclusive compress low). partition by list(region_code) -二级分区 ( partition tb_mid_bll_vc_user_day_region_code1 (values ( 01 ) compress low) ,partition tb_mid_bll_vc_user_day_region_code2 (values ( 02 ) compress low) . );3.5. 表的定义规范aster数据库中的表基本都是从td迁移过来的，所以命名规范基本遵循td的命名规范。所有数据库对象，包括表(table)、视图(view)均应遵循以下命名规范。prefix_subject_body_suffix，其中：1） 为前缀名，表示数据库对象类型；2） 为表隶属主题域名（三位字符缩写）；3） 为主体名，应该能够清楚地说明对象的含义；4） 是后缀名，在该对象需分表存放或以历史表方式出现时使用。名称的各部分之间以_(下划线)连接，各部分中出现的英文字母全部采用大写字母。此外，aster中数据库对象名称（包括字段名称）长度63字符，所有在部分尽量采用缩写形式，必要时做必要截取。（常用缩写可参照附录一）aster中每个表字段数限制是小于1600个。部分一般用两位字符表示数据库对象，总结如下：前缀数据库对象tb表(table)视图的名称与对应的表名一致，存于不同的schema部分命名遵循模型中主题域的划分原则，采用三位字符表示。主题域命名规则：主题域主题域缩写主题域中文描述partypar参与人主题groupgrp集团主题productpdt产品主题networknet网络主题（网管、交换机等）financefin帐务主题marketmkt市场营销主题locationloc地理区域主题billingbll计费详单域（服务使用）balancebal结算域eventevt事件域（营业受理、变更）serviceser客户服务resourceres资源域（渠道、卡、号、线）codecde代码表referrenceref参照表部分命名规则：1） 只能由字母、数字和_(下划线)构成；2） 主体名应采用能够准确反映其中文含义的英文单词或英文单词缩写构成，避免出现英文单词和汉语拼音混用的局面；3） 主体名应尽量与源系统中的命名相一致，除非其命名违反上两条规则。部分命名规则：对于数据库中的表，如因数据量庞大，需按时间分表存放时，需遵循此规则加上后缀。需按日分开存放的表，后缀为yymmdd，需按月分开存放的表，后缀为yyyymm。此外，数据库中还经常存放一些历史表，其后缀用his。4. 数据库备份设计由于备份有物理备份和逻辑备份两种，一般我们采用逻辑备份对数据库中的重要表进行备份。根据经验，一次备份的表不超过10张，恢复不超过7个。将需要备份的表写在一个文件里，如file.txt在备份管理器上执行ncluster_backup -h localhost -f file.txt执行”show backups” 命令，可以监控备份的过程。5. 数据库工作负载设计 aster 工作负载管理功能可以分配资源，使两个不同优先级的工作负载同时运行时，影响达到最小。它由两部分组成：workloads policies:可以分为备份、etl、admin、一般查询、加载等；service class:分配工作负载所占的资源，一个sql语句获得多少硬件资源。以下设计工作负载的资源分配，会随着应用的调整，动态进行调整。4.4.1. service calss目前，我们设计五种service class，namepriorityweightmemory soft limit pctmemory hard limit pctloadclasshigh606065midclasshigh404045commonclassmedium808085regionclasslow505055d