mysql数据仓库（数据仓库）

「起始篇」

大家在做数据开发（数仓）的过程中有没有思考过下面两个问题：

一、为什么要做数据仓库？

二、做数据仓库能为公司带来什么价值？

在聊之前先跟说一下在数仓行业能一些术语，以便后续大家在阅读过程中容易理解。也同时工作当中同事闲聊提专业术语尴尬场面（联想起刚刚入数仓行业面试官提起各种术语，但有些是面试官自己的理解）。

缩写	英文全称	中文全称	备注
EDW	Enterprise Data Warehouse	企业级数据仓库	数仓
DW	Data Warehouse	数据仓库	数仓
BI	Business Intelligence	商业智能
OLAP	On-Line Analysis Processing	在线分析处理
ROLAP	Relational On-Line Analysis Processing	关系在线分析处理
MOLAP	Multidimensional On-Line Analysis Processing	多维在线分析处理
HOLAP	Hybrid On-Line Analysis Processing	混合在线分析处理
ETL	Extract-Transform-Load	抽取、转换清洗、装载
ODS	Operational Data Store	操作数据存储
DWD	Data Warehouse Detail	数据仓库明细
DWS	Data Warehouse Summary	数据仓库汇总
ADS	Application Data Store	应用数据存储
DIM	Dimension	维度
DM	Data Mart	数据集市
SCD	Slow Changing Dimensions	缓慢变化维

总结一句：叫什么有的时候并不重要，重要的是通俗易懂，见字见意即可。

一、为什么要做数据仓库？

（图1）

大家看了（图1）发现理想很丰满，现实很骨感。上图反应其实是非常普遍的一种现象。从数据的角度来看就是各个部门数据不能互通、相互独立，形成数据孤堡（各自为政）。久而久之就会让需要办理业务（需求方）的人哀声道怨（俗称：“跑断腿”）。

（图2）

为了彻底解决“数据孤堡”问题，数据仓库解决方案应运而生。一站式解决“跑断腿”问题（但想要彻底解决是一个漫长的过程）。

二、做数据仓库能为公司带来什么价值？

数据仓库价值可分为两大类来看，一类是业务侧，一类是技术侧（数据将是公司未来最核心的资产，得数据者得）。

1、业务侧

1.1、数据获取、运算成本，需要同步不同系统数据和数据运算。

1.2、避免查看数据，需要在不同系统之间来回登录查看。

1.3、数据驱动业务，如：标签平台、营销平台、推荐系统、AB实验平台、用户画像中心、AB实验平台、数据挖掘等等。

2、技术侧

2.1、数据资产统一管理，避免企业内部各个烟囱式开发（各部门都成立数据组）。大大降低企业数据资产成本，如：开发人员、服务器等。

2.2、数据统一形成规范化、标准化、服务化；为企业降本增效。

2.3、统一数据产品，提供面向不同对象的数据产品，如：Ad-Hoc（即席查询、报表平台、分析平台）

三、让数据产生价值才是王道

（图3）

数据部门存在的意义就是让数据产生价值，让数据数据价值最大化。

「方法论篇」

构建数据仓库目前在业界已经有非常成熟的方法论，方法论可分为两大派系。分别是：

比尔·恩门（Bill Inmon）：自上而下（DWDM）方式

拉尔夫·金博尔（Ralph Kimball）：自下而上（DMDW）的方式

为了更好的理解这两套方法论，本文从实际业务场景实践进行讨论。

• 数据仓库特征

1、主题性

主题是一个抽象的概念，是在较高层次将企业信息系统的数据综合、归类并进行分析利用的抽象。每一个主题对应一个宏观的分析领域

2、集成性

数据仓库通常是结合多个异种数据源构成，异种数据源可能包含数据库、文本、Excel等

3、时变性

数据仓库大多数是保留历史数据。都含有时间属性，反应数据历史变化的

4、易失性

数据仓库数据通常只有两种操作：数据载入和数据查询，在任意时间点查看数据是保持没有变化

• 自上而下构建数仓

Bill Inmon的模型从流程上看是自上而下的，即从分散异构的数据源->数据仓库->数据集市。Bill Inmon流程类似瀑布式开发。需要对源数据进行数据探查来确定数据是否符合预期。其次，明确数据清理规则通过ETL中的Stage将数据转化到DW层。

（图1）

实践场景：在实际工作中当你对业务不太熟悉的情况下，可以对业务进行分析了解，根据业务按3NF方式来进行实施。

• 自下而上构建数仓

Ralph Kimball的模型从流程是自下而上的，即从数据集市->数据仓库-> 分散异构的数据源。Ralph Kimball流程快速交付、敏捷迭代，不会对数据仓库架构做过多复杂的设计，在变换莫测的互联网行业，这种架构方式逐渐成为一种主流范式。

（图2）

实践场景：在实际工作中，当你需要快速构建数据仓库，但又对业务不太熟悉的情况下，可以根据业务需求按维度建模方式来进行实施。

• 2345数仓构建实践

根据公司现状情况，想采用基于自下而上方式（需求应用）构建数据仓库。但是考虑到没有足够的人力、时间且避免反复性的同步数据问题。就直接采用混合模式来构建数据仓库（注：最开始是没有DWS层）。

（图3）

实践场景：两种方法论各取所长且根据公司现状来进行分析考虑。（有时间可以单独开一篇来聊一聊2345数仓演进史）

• 总结

两种方法论各有所长，但是具体实践应用需要根据企业的现状来进行实施。比如：

互联网行业采用自下而上构建比较符合公司发展需要，因为有些APP就是需要快迭代试水，一方面是想根据数据分析是否符合市场需要，另一方面是想基于数据迭代优化产品（业务流程、APP使用习惯）。因为有很多APP还没上线几个月就下线了。

传统行业已经有非常成熟的数据模型（金融行业），比较适合采用自上而下的方式。因为已经有成熟的模型，只需要对数据进行探查然后构建数仓。

思考：构建数仓的本质都是解决供需关系，只有很好的理解公司战略、产品发展、业务需求才构建更好的数据仓库。

「分层篇」

数仓分层是数仓架构中是非常重要的一部分，为什么说是非常重要的一个部分呢？

主要原因如下几点：

• 清晰明确分层职能边界：每层都有对应的职能和边界，数据应用时方便理解和定位。
• 减少重复开发：合理的数据分层，能够避免数据应用（表）重复开发、运算、存储等。
• 统一数据出口：通过分层明确数据统一服务（输出），避免数据出现二议问题。
• 复杂问题简单化：将复杂的任务分解成多个步骤来完成，每一层解决特定的问题。

1、数仓模型分层

（图1）

数仓分层整体可分为三层，分别是：ODS、DW、DM层，只是在实施过程中有些分层不是核心部分则省略了。如：BUFFER、TEMP层，业务有些层只是临时过渡的数据层，不对外提供数据服务或功能说明。

注：有时候不必过度注重层命名，只有明确层的职能边界即可，无需注重叫什么，为什么大家都喜欢参考阿里分层命名？答案是因为分层职能达成共识、减少沟通过程中分层理解、沟通成本。

2、数仓分层定义

2.1、数据缓冲阶段层（Data Buffer Stage Layer）

描述： 源数据采集临时过渡，存储业务表数据有更新、删除操作 和 用户行为日志非结构化数据。为增加数据识别度分存储策略、更新周期、数据来源。

2.2、操作数据存储层（Operational Data Store Layer）

描述： 存放企业接入的最原始的数据，是其他下游层数据的源数据。为增加数据识别度分存储策略、更新周期、数据来源。

2.3、公共维度数据层（Public Dimension Data Layer）

描述： 用于各种维度模型的存储，基于维度建模构建全域一致性维度，采用宽表设计的原则。

2.4、数据仓库明细层（Data Warehouse Detail Layer ）

描述： 明细数据层是按贴源的建模方式，分数据域存储最明细的数据，以适应业务快速发展和变化带来的横向扩展，并保障数据的可回溯。统一的数据存储、命名规则、有限的数据扩展，并按规则要求进行清洗。允许数据的扩展清洗，为使用方便从次层开始提供敏感数据的加密、脱敏、MD5三列。

2.5、数据仓库汇总层（Data Warehouse Summary Layer）

描述： 基于分析的主题对象作为建模驱动，分业务主题域的数据宽表处理及轻度汇总，减少业务使用上的关联，为重度汇总层或应用服务提供相对稳定的数据模型。也通过对通用需求得沉淀，建立易用的数据模型，提升开发效率，节约计算资源。

2.6、应用数据存储层（Application Data Store Layer）

描述： 根据数据应用需求，跨主题域集成、汇总、扩展衍生及根据需要的多粒度的重度汇总数据，建立准确、易用、统一指标体系与口径。

2.7、临时数据处理层（Temporary Data Processing Layer）

描述： 存放数仓各层数据运算处理临时会话表数据，具体临时表数据根据数据处理需要决定。方便统一管理临时表数据的生命周期。

总结

数仓分层设计，在一定的程度上需要通过表命名来体现，本文的核心在于讲解数据分层职能和边界，后面会有单独的文章来分享数仓模型设计和数仓命名规范。

「命名篇」

数仓中为什么要在数据开发过程中强调遵守数仓开发命名规范呢？

主要原因如下几点：

a、养成良好的编程习惯

b、写出清楚、易懂、易维护的程序代码

c、提高代码质量与生产率

d、减少编码中的不必要的错误

1、库命名规范

库命名	库描述	数仓层命名	命名备注
buf	数据缓冲层	buf	buf_开头
ods	数据操作存储层	ods	ods_开头
dwd	数据明细层	dwd	dwd_开头
dws	数据汇总层	dws	dws_开头
ads	数据应用层	ads	ads_开头
dim	统一维度层	dim	dim_开头
temp	临时数据处理层	temp	temp_开头

2、表命名规范

• 命名全部采用小写字母和数字构成，只能以字母开头，并且尽量避免使用数字。
• 命名应采用能够准确反映其中文含义的英文单词或英文单词缩写构成，避免出现英文单词和汉语拼音混用的局面。
• 命名长度尽量控制在30个字符以内，考虑可读性、易懂性、规范性；如果超过30个字符，尽量把长单词转换成缩略词。
• 名称的各部分之间以"_"(下划线)拼接。
• 数据域、主题域命名统一管理
• 缩略词请统一参考【字典库】
• 【禁止】禁止缩写英文单词的首字母的元音

3、离线模型表

3.1、BUF层表命名规范

表名规范：buf_来源类型_[业务|系统]编码_业务表名_装载策略_装载周期 表名示例：buf.buf_db_xxx_user_info_i_d 规范说明： - 存储库名：buf - 来源类型：区分不同来源及系统，含结构化、半结构及非结构化数据。 -- 类型说明：DataBase（db）、Http（api）、Rsync Log（web|h5|app）、MQ（topicName）。 - 业务编码：参考业务对应的编码对照库，注：一般指业务系统简称编码 - 业务表名：与数据来源系统一致，以避免造成其二义性。有分表则去除分表规则，目标添加source_table字段区分来源表名。 - 装载策略：增量（i）、全量（f）、快照（s） - 装载周期：根据实际装载周期确定。实时（rt）、分钟（mi）、小时（h）、天（d）、周（w）、月（m）、季（q）、年（y）、一次性任务（o）、无周期（n）

3.2、ODS层表命名规范

表名规范：ods_来源类型[业务|系统]_业务表名_装载策略_装载周期 表名示例：ods.ods_db_logs_gold_logs_i_d 规范说明： - 存储库名：ods - 来源类型：区分不同来源及系统，含结构化、半结构及非结构化数据。 -- 类型分类：DataBase（db）、Http（api）、Rsync Log（rsync）、MQ（topicName）、hive（layerName）。 - 项目编码：参考业务对应的编码对照库，注：一般指业务系统简称编码 - 业务表名：与数据来源系统一致，以避免造成其二义性。有分表则去除分表规则，目标添加source_table字段区分来源表名。 - 装载策略：增量（i）、全量（f）、快照（s）、 拉链（h）、 - 装载周期：根据实际装载周期确定。实时（rt）、小时（h）、天（d）、周（w）、月（m）、季（q）、年（y）、一次性任务（o）、无周期（n）

3.3、DWD层表命名规范

表名规范：dwd_一级数据域_二级数据域[_业务过程]_业务描述_装载策略_装载周期 表名示例：dwd.dwd_log_app_click_info_i_d 规范说明： - 存储库名：dwd - 一级数据域：用户域、内容域、日志域、财务域、互动域、服务域等等 - 二级数据域：移动端、Web端、会员、金币等等，统一定义 - 业务过程：曝光、浏览、点击、注册、登录、注销等等，统一定义 - 业务描述：描述业务内容 - 装载策略：增量（i）、全量（f）、快照（s）、 拉链（h） - 装载周期：根据实际装载周期确定。实时（rt）、小时（h）、天（d）、周（w）、月（m）、季（q）、年（y）、一次性任务（o）、无周期（n）

3.4、DWS层表命名规范

表名规范：dws_一级数据域_二级数据域_数据粒度_业务描述_统计周期 表名示例：dws.dws_log_mbr_event_info_1d 规范说明： - 存储库名：dws - 一级数据域：用户域、内容域、日志域、财务域、互动域、服务域等等 - 二级数据域：流量、渠道、会员、留存、事件等等 - 数据粒度：描述业务数据粒度 - 业务描述：描述业务内容 - 统计周期：统计实际周期范围，缺省情况下，离线计算应该包括最近一天（_1[h|d|w|m|q|y]），最近N天（_n[h|d|w|m|q|y]）和历史截至当天（_t[h|d|w|m|q|y]）三个表。小时(h)、天（d）、周(w)、月（m）、季（q）、年（y）。

3.5、ADS层表命名规范

表名规范：ads_应用类型_业务主题_业务描述_统计周期_装载周期 表名示例：ads.ads_rpt_channel_user_1d_d 规范说明： - 存储库名：ads - 应用类型：固定报表、分析报表、标签系统、用户画像、数据接口 - 业务主题：看板、驾驶仓、ROI、渠道分析、漏斗分析、留存分析、活跃分析等等 - 业务描述：描述业务内容 - 统计周期：统计实际周期范围，缺省情况下，离线计算应该包括最近一天（_1[h|d|w|m|q|y]），最近N天（_n[h|d|w|m|q|y]）和历史截至当天（_t[h|d|w|m|q|y]）三个表。小时(h)、天（d）、周(w)、月（m）、季（q）、年（y）。 - 装载周期：根据实际装载周期确定。实时（rt）、小时（h）、天（d）、周（w）、月（m）、季（q）、年（y）、一次性任务（o）、无周期（n）

3.6、DIM层表命名规范

表名规范：dim_应用类型_业务主题_业务描述_[层级_装载策略_装载周期] 表名示例：dim.dim_pub_city_lvl、dim_pub_chl_i_h 规范说明： - 存储库名：dim - 应用类型：公共、自定义 - 业务主题：渠道、版本、产品、城市等等 - 业务描述：描述业务内容 - 层级 ：层级（lvl） - 装载策略：增量（i）、全量（f）、快照（s）、 拉链（h） - 装载周期：根据实际装载周期确定。实时（rt）、小时（h）、天（d）、周（w）、月（m）、季（q）、年（y）、一次性任务（o）、无周期（n）

3.7、TEMP层表命名规范

表名规范：temp_目标表名_((数据日期[_数据小时])|(开始日期_结束日期)) 表名示例：temp.temp_dwd_log_app_click_info_i_d_20210311（会话表）、temp.temp_username_test_20210311_20210321 （临时表） 规范说明： - 存储库名：temp - 目标表名： 会话表：目标表名 临时表：业务描述 - 数据日期：ETL跑批日期 、ETL数据处理日期 - 数据小时：ETL跑批小时 、ETL数据处理小时 - 开始日期：临时表有效开始日期 - 结束日期：临时表有效结束日期

4、字段命名规范

通用规范

• 命名全部采用小写、字母和数字构成，且只能以字母开头，并且尽量避免使用数字；不允许使用除数字、字母、下划线之外的特殊字符
• 命名应采用能够准确反映其中文含义的英文单词或英文单词缩写构成，避免出现英文单词和汉语拼音混用的局面，尽量达到见字知意效果。
• 命名长度尽量控制在30个字符以内，特殊字段除外
• 名称的各部分之间以"_"(下划线)连接
• 约定俗成的业务缩略词，统一参考【字典库】
• 实体名称作为前缀
• 字段属性的名称尽量保留实体的名称作为前缀，比如"channel_id/渠道编号"
• 【禁止】除ods层，不能使用"id/name/title"的无实体的名称；无实体含义的自增id除外
• 实体编号/名称带标识/名称(id/name)为强制规范，如country_id,country_name; 不能以country命名 对于编号做为标识符的属性/列，一般统一命名为“××编号”的属性/列，后缀应是id，如"渠道编号/channel_id"等；另外有些已经习惯了的叫法，比如uid，我们不用user_id，但这种情况不多，如果遇到需要单独提出来交组内统一批准;
• 正例：city_id, city_name, country_id, country_name, province_id, province_name, province_short_name, city_level(公共城市维度表)
• 反例：id,name,country(公共城市维度表)

常用字段规范

• 取值只有“是/否”的属性/列，中文名必须以“是否”开头，英文名前缀应是is_，并且取值为string类型且必须满足“是（1）/否（0）”；
• 正例：is_new_device(是否新增设备)
• 反例：device_flag, device_type ...
• 日期类型的属性/列，后缀应是dt，如"注册日期/reg_dt"等（register简写）；时间类型后缀应是time，如"事件发生时间/event_time"等；遇到时间戳类型称为"时间标签"，用ts后缀，建议尽量不要使用，如有需要提交组内批准
• 一般的分类中文名用"类型"，英文名用"type"；大类中文名用"类别"，英文名用"categ"（category简写）；如果遇到特殊的分类情况可以建立单独的分类关系实体，中文名用"分类"，英文名用"class"

其他

• 【禁止】禁止使用关键字，如desc、from、select、left、join、time等 等，请参考Hive官方保留字。
• 【禁止】禁止缩写英文单词的首字母的元音
• 【强制】表名、字段名必须使用小写字母或数字，必须以字母开头，禁止使用除数字、字母、下划线之外的特殊字符，禁止两个下划线中间只出现数字。
• 正例：create_time，create_name
• 反例：ctime，cname

5、字段类型

说明： 长度一般不能超过25个字符长度。如无特殊需要，只允许使用以下几种字段类型。

类型	释义	场景举例	备注
string	字符串类型	名字、评论、地址
bigint	整形类型	人数、PV、UV
double	浮点型类型	比率、比例
decimal	高精度浮点型类型	金额	推荐精度(18, 4)
复合类型		map、array

6、分区

分区分类	分区数据类型	分区字段名	格式	示例	示例说明
小时	string	p_hr	HH	01	24小时制
日	string	p_dt	yyyy-MM-dd	2020-11-02	2020年11月02日
周	string	p_wk	yyyy-MM-dd	2020-11-02	2020年11月02日，每周的第一天
月	string	p_mt	yyyy-MM-dd	2020-11-01	2020年11月01日，每月的第一天
季度	string	p_qt	yyyy-MM-dd	2020-10-01	每季的第一天，2020年Q4
半年	string	p_hy	yyyy-MM-dd	2020-07-01	每半年的第一天，2020年上半年
年	string	p_ys	yyyy-MM-dd	2020-01-01	年的第一天，2020年
类型	string	p_tp
产品	string	p_pd
项目	string	p_pj

总结

数仓命名规范统一降低数仓模型理解成本，避免字段二议性问题。后续将命名规范平台化，提升开发效率、Code Review效率等。

「HQL规范篇」

如何编写优雅可度性高的SQL？好的开发规范是必不可少的。

1. 规范目的

• 养成良好的编程习惯
• 写出清楚、易懂、易维护的程序代码
• 提高代码质量与生产率
• 减少软件编码中的不必要的错误

2. 开发规约

• 所有查询必须指定具体字段
• 统一采用小写字母编码(不包含参数、注解)，遵循SQL 92语法标准
• 所有查询表名必须添加数据库名 表名
• HQL文件一定要设置任务名名称
• HQL【会话临时表】必须要在末尾删除
• 别名一定要加AS关键字,多表关联必须要AS别名
• 字符串用单引号包括
• 不能使用
• SQL 必须经过验证，更新数仓再执行
• SQL代码中严格禁止使用硬编码，排除脏数据清洗编码、默认值。

3.1. 注释

此注释格式适用于SQL。

3.1.1. 头部注释

行号	注解名称	注释说明	是否选
第一行	注释分割线		必选
第二行	业务范围	此脚本所属业务范围	必选
第三行	创建人	填写公司邮箱@之前部分	必选
第四行	创建日期	脚本创建日期	必选
第五行	脚本用途	描述此脚本的作用或预期目标	必选
第六行	执行引擎	如：MySQL、Hive、Spark等	必选
第七行	执行周期	如：时、天、周、月	必选
第八行	对应报表	对应报表名称	可选
第九行	质量监控	对应数据质量校验的名称	可选
第十行	变更记录	变更记录	必选
第十一行	变更格式	变更日期、变更人、变更描述	必选
末行	注释分割线		必选

注释样例–-----------------------------------------------------------------------– 业务范围: 常规任务– 创建人: username– 创建日期: 2020-01-01– 脚本用途: 这是一个测试样例– 执行引擎: hive– 执行周期: 天– 对应报表: 这是一个测试报表– 质量监控: 这是一个测试监控– 变更记录:– 变更日期 变更人 变更描述– 2020-02-02 test 注释相应表–-----------------------------------------------------------------------

3.1.2. 脚本注释

脚本注释可分为：分段注释、临时表注释、语句块注释

• 注释样例

-- 以下属于临时处理IP地址库(分段注释) -- 此表用来临时存放常用IP相关数据(临时表注释) drop table if exists temp_db.temp_tableName_yyyyMMdd; create table temp_db.temp_tableName_yyyyMMdd as select ip ,city_name -- 为空时则使用xxx当默认值(语句块注释) ,nvl(city_id,'xxx') as city_id from temp_db.temp_tableName where p_dt='2019-04-15' -- IP地址必须以数字开头(语句块注释) and ip rlike '^[\\d] '

3.2. DQL

3.2.1. 查询

特别说明，查询表全部字段，可以使用select * 且在代码上方备注"查询全部字段"，否则需要使用select 具体字段。

– 示例 – 查询全部字段 select * from db.table_name

常规查询 - SELECT关键字后换行 - 查询时必须指定具体字段 - 多个字段通过换行进行分割 - 首个字段前面空一空，非首个字段前面则写逗号(以便排查) - FROM后面必须接具体表名

– 示例 select pk_column ,column1 ,column2 ,column3 from db.table_name

子查询 - 查询嵌套时子查询前面必须要有两个空格 - 子查询必须取别名，不能使用匿名方式 - 子查询与括号不能在出现在同一行。

– 示例 select m.column1 ,m.column2 ,m.column3 from ( select m.column1 ,m.column2 ,m.column3 from db.table_name_a as m ) as m

3.2.2. 关联

• 多表关联查询时必须使用[别名 字段]，不能直接写字段名
• 表关联时关键字必须全写，不能简写，如：INNER JOIN写成JOIN
• 表关联时必须先换行再写INNER JOIN关键字
• 表关联时换行后空两格ON关键字后必须紧接着关联条件
• 如果是子查询则直接写ON关键字后必须紧接着关联条件
• 如果关联条件超过两个必须换行，每行各一个关联条件且前面空两格，见示例3、示例4

-- 示例1 select m.pk_column ,m.column1 ,m.column2 ,m.column3 ,a.column4 from db.table_name as m inner join db.table_name_a as a on m.pk_column=a.id and m.column1=a.id -- 示例2 select m.pk_column ,m.column1 ,m.column2 ,m.column3 ,a.column4 from db.table_name as m inner join ( select id ,name from db.table_name_a ) as a on m.pk_column=a.id -- 示例3 select m.pk_column ,m.column1 ,m.column2 ,m.column3 ,a.column4 from db.table_name as m inner join db.table_name_a as a on m.pk_column=a.id and m.pk_column=a.id and m.pk_column=a.id -- 示例4 select m.pk_column ,m.column1 ,m.column2 ,m.column3 ,a.column4 from db.table_name as m inner join ( select id ,name from db.table_name_a ) as a on m.pk_column=a.id and m.pk_column=a.id and m.pk_column=a.id

3.2.3. 条件

• 优先将数据过滤最多的写在第一位
• 先换行再写关键字，然后再写过滤条件，多个过滤条件必须先换行再写关键字 条件

-- 示例 select m.pk_column ,m.column1 ,m.column2 ,m.column3 ,a.column4 from db.table_name as m inner join db.table_name_a as a on m.pk_column=a.id where a.column7='xxxx' and a.column8='xxxx'

3.2.4. 分组

• 先换行再写关键字

• 第一个分组字段先空格再写字段名，非首个字段前面则写逗号(以便排查)

-- 示例 select m.column1 ,m.column2 ,m.column3 ,count(1) as cnt from db.table_name as m group by m.column1 ,m.column2 ,m.column3

3.2.5. 合并

• 多结果集合并时，两结果集之间必须空一行再写关键字

-- 示例 select m.column1 ,m.column2 ,m.column3 from db.table_name_a as m union all select m.column1 ,m.column2 ,m.column3 from db.table_name_b as m

3.2.6. 聚合筛选

• 脚本尽量不用

-- 示例 select m.column1 ,m.column2 ,m.column3 ,count(*) as pv from db.table_name as m group by m.column1 ,m.column2 ,m.column3 having count(*)>1

3.2.7. 排序

• 先换行再写关键字。
• 第一个排序字段先空格再写字段名，非首个字段前面则写逗号(以便排查)
• 必须明确指定排序关键字(DESC|ASC)

-- 示例 select m.column1 ,m.column2 ,m.column3 from db.table_name as m order by m.column1 desc ,m.column2 asc

3.3. DML

Hive只做INSERT操作，MySQL可做INSERT、UPDATE、DELETE操作

3.3.1. INSERT

• hive

语法1： insert overwrite table table_name [partition(column)] select column1 ,column2 from table_name ; 语法2： insert into table table_name(column[...]) select column1 ,column2 from table_name ; 语法3： insert into table table_name(column[...]) values(column[...]) ;

• MySQL

INSERT INTO [ 资料表或可更新检视表物件名称] ( 栏位1 , 栏位2 , 栏位3 , ...) VALUES ( 值1 , 值2 , 值3 , ...) --只需要根据指定的栏位来设定插入值。 INSERT INTO [ 资料表或可更新检视表物件名称] VALUES ( 值1 , 值2 , 值3 , ...) 　--必须要指定所有栏位的插入值。

3.3.2. UPDATE

UPDATE [ 资料表或可更新检视表物件名称] SET 栏1 = 值1 , 栏2 = 值2 , 栏3 = 值3 , ... WHERE [ 指定条件]

3.3.3. DELETE

DELETE FROM [资料表或可更新检视表名称] WHERE [给定条件] DELETE * FROM [资料表或可更新检视表名称] WHERE [给定条件]

3.4. DDL

3.4.1. 常规表

• 表必须指定存储类型、字段分割符、换行符、表描述
• 首个字段空一格，其余换行使用逗号 字段
• 字段必须有字段描述，字符必须是英文字符，编码类字段描述格式为：1:是,2:否
• 字段类型必须与源字段类型一致
• 首先必须注释表删除语法(防止表名重复)
• 如果是对应有固定报表、数据分析平台的dm表，是维度字段的字段描述后面要增加'维度'二字
• 其余未描述规范与示例保持一致

-- 示例1 -- drop table if exists 库名.表名; create table 库名.表名 ( column1 string comment 'column1' ) comment 'XXXXXXXX' partitioned by (p_dt string comment '日期(yyyy-MM-dd)') row format delimited fields terminated by '\t' collection items terminated by '\n' stored as orc ;

3.4.2. 临时表

会话临时表

• 先删除后创建原则

-- 示例 drop table if exists temp_db.temp_ods_userinfo_20190201; create table temp_db.temp_ods_userinfo_20190201 as select column1 ,column2 ,column3 ,column4 from ods.ods_userinfo

常规临时表

• 参考常规建表规范

3.5. DCL

目前

只针对MySQL数据库操作，目前此操作由运维管理。后续在开发过程中会不断迭代与修改，最终将规范形成平台化。

「ETL篇」

a、什么场景下会使用到ETL？

b、ETL与ELT的区别是什么？

c、如何快速了解ETL开发流程？

图1

1、ETL介绍

ETL：是英文Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。

ETL：概念通常用在数据仓库，但其对象并不限于数据仓库，什么意思呢？就是只要有涉及到数据搬运都是源到目标的过程，如：数据库迁移(Oracle迁移到MySQL)等。

使用ETL技术可以解决以下相关场景：

1、数据分散在各个系统中需要对数据进行整合，如：（图1）非常形象。

2、数据迁移到不同的环境，如：数据库、操作系统等

3、数据处理效率非常低下，如：有的用Python处理、Java处理等

2、ETL VS ELT

ETL与ELT的最核心区别是：抽取源数据是否需要【转换】后再加载到目标

设计时需要思考的问题如：

1、数据的可追溯性

2、ETL服务器资源

3、数据库服务器资源

4、程序职责边界划分

目前业界互联网80%都是基于ELT开发，传统大型数仓都是基于ETL开发。如下：

1、基于Hadoop体系构建的数据仓库，都是基于开源工具来做数据同步（如：DataX、Sqoop、Kettle等），然后基于同步数据来做相应ETL操作。

2、基于RMDB（Teradata\PostgreSQL\Oracle\DB2\MySQL\SQL Server）构建数据仓库，都是基于ETL工具来开发设计，如：Informatica、DataStage、SSIS、Kettle等。

3、ETL开发流程

图2

数仓ETL开发流程分别为：流程设计、概要设计、逻辑设计、代码实现、任务调度、数据质量

• 流程设计：是对数据整体链路流程描述说明
• 概要设计：明确数据各环节处理过程
• 逻辑设计：源到目标映射处理关系
• 代码实现：是逻辑代码的具体实施
• 调度任务：任务之间依赖关系与处理流程控制
• 数据质量：监控源与目标是否准确无误

产出：ETL开发设计文档、单元测试用例

3.1、 流程设计

涉及ETL整体流程链路，明确源到目标之间过程描述

• 数据来源信息

明确数据源相关详细信息，数据库需说明相关信息，如：IP、端口、用户名、密码、表名称；日志需说明相关信息，如：服务器信息、目录、文件名称等

• 数据处理过程

指源数据处理存储逻辑概念描述，而非指具体处理过程。如：数仓、DataX等

• 数据目标输出

指数据最终输出目标，目标可能是：Hive、MySQL、ClickHouse、API、File等

3.2、概要设计

基于源数据进行分析，确定数据抽取、装载、问题环节相关策略。

• 数据探查

数据来源分两类：一类业务数据（DB）;一类行为数据（日志）。两类数据都需要对结构、字段、字段值、字段解释进行探查。

• 表、日志结构

表结构或日志结构实际是否与文档描述一致，如：业务方说某字段在表中是唯一，但实际表结构并未设置唯一键问题；防止发生少表问题。

• 字段

实际是否与文档描述一致，防止发生缺少字段、新加字段问题。

• 字段值

实际检查枚举值、状态值等是否与文档描述一致，防止与业务方描述不一样问题。

• 解释

表结构、日志结构、字段都应当有具体文档解释说明。

• 抽取策略

确定数据抽取口径（全量、增量）；明确数据提取频次（如：时、天、周、月）

• 装载策略

确定源数据到目标表装载策略类型（如：增量、Merge、全量）

• 问题处理

问题处理方式确定，如：执行时间过长，配置超时告警。执行出失败，邮件告警（默认）。关键核心数据配置【监控中心】电话通知告警。

3.3、逻辑设计

源到目标过程具体字段处理逻辑说明

• 命名规范

参见：《数仓命名规范》

• 映射关系

源数据到目标表的字段映射关系

• 转换规则

源数据到目标表字段处理逻辑

3.4、代码实现

基于逻辑设计编写程序实现具体功能

• 开发规范

参见：《数仓开发规范》

• 脚本性能

程序实现需要考虑具体性能问题，性能要求如下：

层名称	性能要求	执行时间段
buf	0~1200s	0:01 ~ 1:00
ods	0~1200s	0:01 ~ 1:00
dim	0~1800s	1:01 ~ 3:00
dwd	0~1800s	1:01 ~ 3:00
dww	0~1800s	3:01 ~ 4:00
ads	0~600s	4:01 ~ 6:00

未能达到性能要求，不能上线生产环境（特殊情况需向研发负责人说明）

• 引擎策略

根据具体业务要求采使用不同执行引擎，如：MR、Presto、Spark等

• 单元测试

参见：《脚本测试》

3.5、调度任务

脚本开发完成后在调度系统中指定相应时间让任务运行

• 命名规范

参见：《调度命名规范》

• 依赖配置

配置脚本涉及到的所有前置任务依赖

• 资源控制

合理控制任务并发数让资源合理使用，避免任务与任务之间出现资源争抢。

• 流程控制

根据具体业务情况控制任务优先级、项目与任务之间关联关系

• 单元测试

参见：《调度任务测试》

3.6、数据质量

确保数据从源到目标加工过程的准确性、完整性、一致性

• 命名规范

参见：《数据质量命名规范》

• 源与目标校验

校验源到目标数据，如：记录数、数值汇总、分维度 记录数 数值汇总校验

• 数据交叉校验

校验源到多目标、多源到目标、相同指标跨表等数据，如：记录数、数值汇总、分维度 记录数 数值汇总校验

• 数据趋势校验

校验目标表数据历史趋势情况，如：日、周、月、年周期环比、同比校验

• 单元测试

参见：《数据质量测试》

4、ETL工具选型

ETL工具调研选型结合项目实际情况，主要考虑如下：

1、ETL工具是否收费（只考虑免费）

2、团队整体技术栈（比如：Java）

3、ETL工具熟练程度

4、工具社区活跃情况

5、工具文档完善程序

通过以上几点考虑，第1点就过滤了一大批收费工具，再经过筛选只剩下两款工具，分别是：DataX与Sqoop两款。它们之间的功能对比如下

功能	DataX	Sqoop
运行模式	单进程多线程	MapReduce
MySQL读写	单机压力大；读写粒度容易控制	MapReduce 模式重，写出错处理麻烦
Hive读写	单机压力大	扩展性好
文件格式	orc支持	orc不支持，可添加
分布式	不支持，可以通过调度系统规避	支持
统计信息	已有一些统计，上报需定制	没有，分布式的数据收集不方便
数据校验	在core部分有校验功能	没有，分布式的数据收集不方便
监控	无	无
社区	社区不活跃	核心部分变动很少

DataX 主要的缺点在于单机运行，而这个问题我们通过分布试调度系统规避，其他方面的功能均优于 Sqoop，最终我们选择了基于 DataX 开发。

「维度篇」

维度是维度建模的基础和灵魂。需要知道如下几点：

a）维度的基本概念

b）维度的设计流程

c）维度变化有哪几种

1、维度基本概念

在维度建模中，将度量称为“事实”将环境描述为“维度”，维度是用于分析事实所需要的多样环境。例如：在分析会员过程时，可以通过地域、渠道、年龄和注册时间等维度描述会员注册的环境。

维度所包含的表示维度的列（字段），称为维度属性。维度属性是查询约束条件、分组和报表、用户标签生成的基本来源，是数据易用性、易理解的关键。例如：在查询请求中，获取某地域的会员、注销的会员等，是通过约束地域属性和注册状态属性来实现的；统计不同地域每天注册会员数，是通过会员的地域属性、注册日期进行分组的；我们在报表中看到的地域、性别、渠道等，都是维度属性。所以维度的作用一般是查询约束、分类汇总以及排序等。

维度使用主键标识其唯一性，主键也是确保与之相连（SQL关联）的任何事实表 之间存在关联完事性为基础。主键有两种：代理键和自然键，它们都是用于标识某维度的具体值。但代理键是不具有业务含义的键，一般用于处理【缓慢变化维】或涉及到某个OLAP引擎只支持数字【精度去重】时；自然键是具有业务含义的键。

2、维度设计流程

维度的设计过程就是确定维度属性的过程，如何生成维度属性，以及所生成的维度属性的优劣，决定了维度使用的方便性，成为数据仓库易用性的关键。正如 Kimball 所说的，数据仓库的能力直接与维度属性的质量和深度成正比。

下面以2345的会员维度为例，对维度设计方法进行详细说明。

第一步：选择维度或新建维度（动态与静态维度）。作为维度建模的核心，在企业级数 据仓库中必须保证维度的唯一性（非常重要）。以2345维度为例，有且只允许有 一个维度定义（避免后续派生指标统计口径二议性问题）。

第二步：确定主维表。此处的主维表一般是 ODS 表，直接与业务系统同步。以2345会员为例， t_user_info_[0~255] 是与后台会员系统同步的会员表，此表即是主维表。

第三步：确定相关维表。数据仓库是业务源系统的数据集成（整合），不同业务系统或者同业务系统中的表之间存在关联性。根据对业务的梳理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。以2345会员维度为例，根据对业务逻辑的梳理，可以得到会员与支付宝信息、微信信息、扩展信息等维度存在关联关系。

第四步 ：确定维度属性。此步骤主要包括两个阶段，其中第一个阶段是从主维表中选择维度属性或生成新的维度属性；第2个阶段是从相关维表中选择维度属性或生成新的维度属性。以2345会员维度为例，从主维表 （t_user_info_[0~255] ）和支付宝信息、微信信息、扩展信息等相关维表中选择维度属性或生成新的维度属性。

确定维度属性需要注意的几点：

a）尽可能生成丰富的维度属性

如：2345会员维度是将相关信息进行整合，为下游的数据应用（统计、分析、 探查）提供了良好的基础。

b）尽可能多的给出包括一些富有意义的文字性描述

如：属性不应该是编码，而应该是真正的文字。正常情况编码与编码描述都是1对1关系存在。因为编码是用于ETL计算逻辑关联 或 报表、标签条件查询过滤。

c）尽量沉淀出通用的维度属性

如：有些维度属性获取需要进行比较复杂的逻辑加工处理，

1、有些需要通过多表关联得到

2、通过单表的不同宇段混合处理得到

3、通过对单表 的某个字段进行解析得到

这种情况就需要将尽可能通用的维度属性进行沉淀。一方面可以提高下游使用的方便性，减少复杂度；另一方面可以避免下游使用解析时由于各自逻辑不同而导致口径二议性。

d）区分数值型属性和事实

数值型宇段是作为事实还是维度属性，具体需要根据业务情况决定。如果通常用于查询约束条件或分组统计，则是作为维度属性；如果通常用于参与度量的计算，则是作为事实。

3、维度的层次结构

维度中的一些描述属性以层次方式或一对多的方式相互关联，可以被理解为包含连续主从关系的属性层次。层次的最底层代表维度中描述最低级别的详细信息，最高层代表最高级别的概要信息。维度常常有多个这样的嵌入式层次结构。比如机构维度，有员工、组、部门等。 其中员工是最低级别是叶子属性，组属于二级类目，二级类目属于一级类目。

在属性的层次结构中进行钻取是数据钻取的常见方法之一。有兴趣的可以去了解：OLAP相关的上钻、下钻、切片、旋转等。

以下是以日期层级举例：

以最高年份层级查看注册会员数，如（图1）

图1

以季度层级查看注册会员数，如（图2）

图2

以月度层级查看注册会员数，如（图3）

图3

以最细粒度天层级查看注册会员数，如（图4）

图4

4、规范化和反规范化

当属性层次被实例化为一系列维度，而不是单一的维度时，被称为花模式。大多数联机事务处理系统（ OLTP ）的底层数据结构在设计时采用此种规范化技术，通过规范化处理将重复属性移至其自身所属的表中，删除冗余数据。 这种方法用在 OLTP系统中可以有效避免数据冗余导致的不一致性。

图5

采用雪花模式，除了可以节约部分存储外，对于OLAP 系统来说没有其他效用而现阶段存储的成本非常低。出于易用性和性能的考虑，维表一般是很不规范化的在实际应用中（星型模型），几乎总是使用维表的空间来取简明性和询性能。后续文章会讲解。

图6

5、一致性维度和交叉探查

构建企业级数据仓库不可能一蹦而就，一般采用迭代的方式构建。而单独构建存在的问题是形成独立型数据集市，导致严重的不一致性。 Kimball 的数据仓库总线架构提供了一种分解企级数据仓库规划任务的合理方法，通过构建企业范围内一致性维度和事实来构建总线架构。

数据仓库总线架构的重要基石之一就是一致性维度。在针对不同数据域进行迭代构建或并行构建时，存在很多需求是对于不同数据域的业务过程或者同一数据域的不同业务过程合并在一起观察。比如对于日志数据域，统计了设备维度的最近1天的 PV 与UV；对于交易数据域，统计了会员维度的最近一天的下单金额 。现在将不同数据域的会员或设备的事实合并在一起进行数据探 ，如计算转化率等，称为交叉探查。

如果不同数据域的计算过程使用的维度不一致，就会导致交叉探查存在问题。当存在重复的维度，但维度属性或维度属性的值不一致时，会导交叉探查无法进行或交叉探查结果错误。如：假设对于日志数据域，统计性别维度登录会员数，性别包含1和2，对于交易数据域统计性别维度交易金额，性别包含W和M，则无法进行交叉查看与分析。或者日期有的是数字有的是yyyy-MM-dd HH:mm:ss或yyyy-MM-dd等各种不同的形式。就不一一举例了。但总体可分为两类：格式不一致与内容不一致。

6、维度设计

6.1、维度整合

数据仓库的定义：数据仓库是一个面向主题的、集成的、 非易失的且随时间变化的数据集合，用来支持管理人员的决策。其中集成是数据仓库的四个特性中最重要的一个。

图7

数据仓库的重要数据来源是大量的、分散（图7）的面向应用的操作型环境。不同的应用在设计过程中，可以自由决策 ，主要满足本应用的需求，很少会考虑和其他系统进行数据集成。应用之间的差异具体表现在如下几个方面：

a）应用系统在编码、命名习惯、度量单位等方面会存在很大的差异。如：上面所说的性别，A系统1（男）、2（女），B系统W（女）、M（男）；A系统创建时间为create_at，B系统创建时间为create_time；A系统金币1000=1元，B系统金币10000=1元等问题。

b）应用系统出于性能和扩展性的考虑，或者随技术架构的演变，以及业务的发展，采用不同的物理实现。如：分库、分表（t_user_info_[0~255]）,分不同的存储：MySQL、HBase、Mongo等。

所以数据由面向应用的操作型环境进入数据仓库后，需要进行数据集成。将面向应用的数据转换为面向主题的数据仓库数据，本身就是一 种集成。具体体现在如下几个方面：

a）命名规范的统一。表名、字段名等统一（参考：手撕数据仓库之「命名篇」）。

b）字段类型的统一。 相同和相似字段的字段类型统一。

c）公共代码及代码值的统一。公共代码及标志性宇段的数据类型、 命名方式等统一。

d）业务含义相同的表的统一 。主要依据高内聚、低稠合的理念，在物理实现中，将业务关系大、源系统影响差异小的表进行整合： 将业务关系小、游、系统影响差异大的表进行分而置之。

6.2、水平拆分

维度通常可以按照类别或类型进行细分。主要有两种解决方案：方案1是将维度的不同分类拆分为不同的维度表；方案2将公共的属性进行整合成公共维度表。

但在具体的应用场景中如何选择呢？在数据模型设计过程中需要考虑的因素有很多，基本不可能满足各个特性指标的最优化。在设计过程中需要重点考虑以下三个原则。

a）扩展性：当源系统、业务逻辑变化时，能通过较少的成本快速扩展模型，保持核心模型的相对稳定性。软件工程中的高内聚、低耦合的思想是重要的指导方针之一。

b）效能：在性能和成本方面取得平衡。通过牺牲一定的存储成本， 达到性能和逻辑的优化。

c）易用性：模型可理解性高、访问复杂度低。用户能够方便地从模型中找到对应的数据表，并能够方便地查询和分析。

6.3、垂直拆分

垂直拆分在实际的业务场景应用中是相对比较少，尽量避免垂直拆分，除非业务的特殊情况必须这样做。如：涉及到某些数据来源特别晚但业务要使用到的数据必须要几点钟要出的情况，则需要考虑将部分维度进行垂直拆分。由于应用较少就不再展开细说。

7、维度变化

7.1、缓慢变化维

数据仓库的重要特点之一是反映历史变化，所以如何处理维度的变化是维度设计的重要工作之一。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化与数据增长较为快速的事实表相比，维度变化相对缓慢。

第一种处理方式 ：重写维度值。采用此种方式，不保留历史数据， 始终取最新数据。比如：员工所对应的组或部门调整，始终都使用最新的组织层级。

图8

第二种处理方式：插人新的维度行。采用此种方式，保留历史数据， 维度值变化前的事实和过去的维度值关联，维度值变化后的事实和当前的维度值关联。同上面的例子，采用第二种处理方式。

图9

第三种处理方式：添加维度列。采用第二种处理方式不能将变化前后记录的事实归为变化前的维度或者归一为变化后的维度。比如根据业务需求，需要查看历史组变化前后数据，采用第二种处理方式无法实现。针对此问题，采用第三种处理方式，保留历史数据，可以使用任何一个属性列。同上面的例子

图10

对于选择哪种方式处理缓慢变化维，并没有一个完全正确的答案，具体需要根据业务需求来进行选择。

7.2、快照维表

结合目前维度表的设计都需要通过一定的加工处理形成相应的维度表，如：生成相应的代理键与增加ETL复杂性，且维度成本相对较高问题。

可以通过处理缓慢变化维的方法是采用快照方式。数据仓库的计算周期一般是每天1次，基于此周期，处理维度变化的方式就是每天保留一份全量快照数据。此方法既有优 ，也有弊端。

优点：

a）简单而有效，开发和维护成本低。

b）使用方便，理解性好。数据使用方只需要限定日期，即可获取到 当天的快照数据。任意一天的事实快照和维度快照通过维度的自然键进行关联即可。

缺点：

a）存储的极大浪费

b）定期维护处理数据生命周期

7.3、拉链维表

历史拉链存储是指利用维度模型中缓慢变化维的第二种处理方式。这种处理方式是通过新增两个时间戳字段 (start_dt、end_dt ），将所有以天为粒度的变更数据都记录下来。通常 分区字段也是时间戳字段。

图11

这样下游应用可以通过限制时间戳字段来获取历史数据。例如，用 访问 日的数据，只需要限制【开始日期】<=2021-03-01 and 【结束日期】> 2021-03-01即可。

但是这种存储方式对于下游应用方存在一定的理解，特别是数据分析师、业务开发人员，会有很高的解释成本。特别是对于Hive表设计复杂度与性能会极度下降。

具体大家可以根据业务需求场景来确定是否使用。

7.4、其它维表

其它维度表比如业务库比较常见的递归表，多值维度表等等。由于不常用就不细说。

总结：目前业界互联网90%都是基于维度建模来构建数据仓库，所以掌握好维度设计就是掌握了维度建模核心之一。

,