杭州杭州网站建设,做营销的网站,马来西亚网站建设,国外网站建设素材文末下载PDF
文章很长#xff0c;前言一定要看
拥有本篇文章#xff0c;意味着你拥有一本完善的书籍#xff0c;本篇文章整理了数据仓库领域#xff0c;几乎所有的知识点#xff0c;文章内容主要来源于以下几个方面#xff1a;
源于「数据仓库交流群」资深数据仓库工程… 文末下载PDF
文章很长前言一定要看
拥有本篇文章意味着你拥有一本完善的书籍本篇文章整理了数据仓库领域几乎所有的知识点文章内容主要来源于以下几个方面
源于「数据仓库交流群」资深数据仓库工程师的交流讨论如《sql行转列的千种写法》。源于群友面试大厂遇到的面试真题整理投稿给我形成《面试题库》。源于笔者在系统学习过程中整理的笔记和一点理解。源于技术网站的优质文章和高赞答案。本篇文章尤其适合初级程序员准备面试以及作为工作中的指导手册对资深程序员来说也可夯实基础。
当然技术学习仅仅依靠一篇文章还是不够的可加入公众号和技术交流群联系方式见文末群里有很多数据仓库领域资深大佬大家经常在群里讨论技术热点问题、互相解决工作难题、安排内推、甚至有部门leader直接发出岗位邀请。「西红柿」也会持续更新优质文章也欢迎热爱学习总结的小伙伴有偿投稿共同推动中国信息技术行业发展让我们一起加油吧
目录
一、数据仓库的8个发展阶段
1、概念阶段1978-1988
2、萌芽阶段
3、集成阶段
4、确立阶段1991
5、数据集市19941996
6、争吵与混乱1996-1997
7、合并19982001
8、未来
二、四种常见数据模型
1、为什么要进行数据仓库建模
2、四种常见模型
2.1 维度模型
2.2 范式模型
2.3 Data Vault模型
2.4 Anchor模型
3、数据模型的评价标准
三、三种事实表
1、三种事实表概述
1.1 事务事实表
1.2 周期快照事实表
1.3 累积快照事实
2、三种事实表对比
3、事实表设计 8 大原则
4、事实表设计方法
第一步选择业务过程及确定事实表类型
第二步声明粒度
第三步确定维度
第四步确定事实
四、多维体系结构
1、总线架构
2、一致性维度
3、一致性事实
五、数据仓库规范设计
1、为什么要进行规范设计
2、设计规范 - 指标
3、命名规范 - 表命名
3.1 常规表
3.2 中间表
3.3 临时表
3.4 维度表
4、开发规范
5、流程规范
六、元数据管理
1、业务元数据
2、技术元数据
数据源元数据
ETL 元数据
数据仓库元数据
BI 元数据
3、管理元数据
4、小编有话
七、维度表
1、什么是维度表
2、维度表设计原则
缓慢变化维
3、维度表设计方法
八、三范式与反范式
1、第一范式
2、第二范式
3、第三范式
4、反范式化
5、范式化设计和反范式化设计的优缺点
5.1 范式化 时间换空间
5.2 反范式化空间换时间
6、OLAP和OLTP中范式设计
九、数据仓库架构-Lambda和Kappa
1、Lambda架构原理
2、Lambda架构的缺点
3、Kappa架构原理
4、Lambda架构和Kappa架构优缺点对比
5、数据架构评价标准
6、小编有话
十、数据治理目的、方法、流程
1、什么是数据治理
2、数据治理的目的
3、数据治理的方法
4、数据质量8个衡量标准
5、数据治理流程
十一、ETL
1、什么是ETL
2、ETL ELT
3、常用的ETL工具
3.1 sqoop
3.2 DataX
3.3 Kettle
3.4 canal
3.5 StreamSets
4、ETL加载策略
4.1 增量
4.2 全量
4.3 流式
5、小编有话
十二、数据应用-OLAP
1、olap和oltp的区别
2、OLAP分类
3、OLAP基本操作
4、OLAP选型
4.1 druid
4.2 kylin
十三、数据倾斜
1、数据倾斜表现
2、数据倾斜产生原因
3、解决数据倾斜思路 一、数据仓库的8个发展阶段
1、概念阶段1978-1988
数据仓库最早的概念可以追溯到20世纪70年代MIT的一项研究该研究致力于开发一种优化的技术架构并提出这些架构的指导性意见。
第一次MIT的研究员将业务系统和分析系统分开将业务处理和分析处理分成不同的层次并采用单独的数据存储和完全不同的设计准则。同时MIT的研究成果与80年代提出的信息中心InformationCenter相吻合即把那些新出现的、不可以预测的、但是大量存在的分析型的负载从业务处理系统中剥离出来。
但是限于当时的信息处理和数据存储能力该研究只是确立了一个论点这两种信息处理的方式差别如此之大以至于它们只能采用完全不同的架构和设计方法。
2、萌芽阶段
在80年代中后期,作为当时技术最先进的公司,DEC已经开始采用分布式网络架构来支持其业务应用并且DEC公司首先将业务系统移植到其自身的RDBMS产品RdB。并且DEC公司从工程部、销售部、财务部以及信息技术部抽调了不同的人员组建了新的小组不仅研究新的分析系统架构并要求将其应用到其全球的财务系统中。该小组结合MIT的研究结论建立了TA2TechnicalArchitecture2规范该规范定义了分析系统的四个组成部分
数据获取数据访问目录用户服务
其中的数据获取和数据访问目前大家都很清楚而目录服务是用于帮助用户在网络中找到他们想要的信息类似于业务元数据管理用户服务用以支持对数据的直接交互包含了其他服务的所有人机交互界面这是系统架构的一个非常大的转变第一次将交互界面作为单独的组件提出来。
3、集成阶段
全企业集成EnterpriseIntergration1988同时IBM也在处理信息管理不同方面的问题其最烦人的问题是不断增加的信息孤岛IBM的很多客户要面对很多分立系统的数据集成问题而这些系统有不同的编码方式和数据格式。
1988年为解决全企业集成问题IBM爱尔兰公司的BarryDevlin和PaulMurphy第一次提出了“信息仓库InformationWarehouse”的概念将其定义为“一个结构化的环境能支持最终用户管理其全部的业务并支持信息技术部门保证数据质量”并在1991年在DECTA2的基础上把信息仓库的概念包含进去并称之为VITAL规范将PC、图形化界面、面向对象的组件以及局域网都包含在VITAL里并定义了85种信息仓库的组件包括数据抽取、转换、有效性验证、加载、Cube开发和图形化查询工具等。但是IBM只是将这种领先的概念用于市场宣传而没有付诸实际的架构设计。这是IBM有一个领域上创新后停止不前导致丧失其领先地位。因此在90年代初期数据仓库的基本原理、框架架构以及分析系统的主要原则都已经确定主要的技术包括关系型数据存取、网络、C/S架构和图形化界面均已具备只欠东风了。
同时在1988年1991年一些前沿的公司已经开始建立数据仓库。
4、确立阶段1991
企业级数据仓库EDW19911991年BillInmon出版了其有关数据仓库的第一本书这本书不仅仅说明为什么要建数据仓库、数据仓库能给你带来什么更重要的是Inmon第一次提供了如何建设数据仓库的指导性意见该书定义了数据仓库非常具体的原则包括数据仓库是面向主题的Subject-Oriented、集成的Integrated、包含历史的Time-variant、相对稳定的Nonvolatile、面向决策支持的DecisionSupport面向全企业的EnterpriseScope最明细的数据存AtomicDetail数据快照式的数据获取SnapShotCapture这些原则到现在仍然是指导数据仓库建设的最基本原则虽然中间的一些原则引发一些争论并导致一些分歧和数据仓库变体的产生。
BillInmon凭借其这本书奠定了其在数据仓库建设的位置被称之为“数据仓库之父”。
5、数据集市19941996
数据仓库发展的第一明显分歧是数据集市概念的产生。由于企业级数据仓库的设计、实施很困难使得最早吃数据仓库螃蟹的公司遭到大面积的失败因此数据仓库的建设者和分析师开始考虑只建设企业级数据仓库的一部分然后再逐步添加但是这有背于BillInmon的原则各个实施部分的数据抽取、清洗、转换和加载是独立导致了数据的混乱与不一致性。而且部分实施的项目也有很多失败除了常见的业务需求定义不清、项目执行不力之外很重要的原因是因为其数据模型设计在企业级数据仓库中Inmon推荐采用3范式进行数据建模但是不排除其他的方法但是Inmon的追随者固守OLTP系统的3范式设计从而无法支持DSS系统的性能和数据易访问性的要求。
这时Ralph Kimball出现了他的第一本书“TheDataWarehouseToolkit”掀起了数据集市的狂潮这本书提供了如何为分析进行数据模型优化详细指导意见从DimensionalModeling大行其道也为传统的关系型数据模型和多维OLAP之间建立了很好的桥梁。从此数据集市在很多地方冒了出来并获得很大成功而企业级数据仓库已逐渐被人所淡忘。
6、争吵与混乱1996-1997
企业级数据仓库还是部门级数据集市关系型还是多维BillInmon和RalphKimball一开始就争论不休其各自的追随者也唇舌相向形成相对立的两派Inmon派和Kimball派有点象少林和武当呵呵。
在初期数据集市的快速实施和较高的成功率让Kimball派占了上风但是很快他们也发现自己陷入了某种困境企业中存在67个不同的数据集市分别有不同的ETL相互之间的数据也不完全一致。同时各个项目实施中也任意侵犯了Inmon开始定下的准则把数据集市当成众多OLTP系统之后的有一个系统而不是一个基础性的集成性的东西为保证数据的准确性和实时性有的甚至可以由OLTP系统直接修改数据集市里面的数据为了保证系统的性能有的数据集市删除了历史数据。等等不一而足。
当然这导致了一些新的应用的出现例如ODS但是人们对DataWarehouse、DataMart、ODS的概念非常的模糊经常混为一谈。有人说OLAP就是数据仓库也有人说我要ODS和DataMart不要Datawarehouse也有人说我DataMart建多了自然就有DataWarehouse了。但是BillInmon一直很旗帜鲜明“你可以打到几万吨的小鱼小虾但是这些小鱼小虾加起来不是大鲸鱼”。
7、合并19982001
经过多翻争吵证明one-size-fits-all是不可能的你需要不同的BI架构来满足不同的业务需求。BillInmon也推出了新的BI架构CIFCorporationinformationfactory把Kimball的数据集市也包容进来了第一次Kimball承认了Inmon但是仍然还有很多人在争论是自顶向下还是自底向上。
8、未来
未来几个方向时效性方向的实时数仓数据质量方向的数据治理数据中台、数据湖欢迎留言讨论
推荐阅读
数据库、数据仓库、数据平台、数据中台、数据湖到底是什么 二、四种常见数据模型 大数据时代维度建模已成为各大厂的主流方式。
维度建模从分析决策的需求出发构建模型为分析需求服务。重点关注用户如何快速的完成数据分析可以直观的反应业务模型中的业务问题需要大量的数据预处理、数据冗余有较好的大规模复杂查询的响应性能。 1、为什么要进行数据仓库建模
性能良好的模型能帮我们快速查询需要的数据减少数据的IO吞吐成本减少数据冗余、计算结果复用、从而降低存储和计算成本效率改善用户使用数据的体验提高使用数据的效率改善统计口径的不一致性减少数据计算错误的可能性
2、四种常见模型
2.1 维度模型
维度建模按数据组织类型划分可分为星型模型、雪花模型、星座模型。Kimball老爷爷维度建模四部曲
选择业务处理过程 定义粒度 选择维度 确定事实 2.1.1 星型模型
星型模型主要是维表和事实表以事实表为中心所有维度直接关联在事实表上呈星型分布。 2.1.2 雪花模型
雪花模型在星型模型的基础上维度表上又关联了其他维度表。这种模型维护成本高性能方面也较差所以一般不建议使用。尤其是基于hadoop体系构建数仓减少join就是减少shuffle性能差距会很大。 星型模型可以理解为一个事实表关联多个维度表雪花模型可以理解为一个事实表关联多个维度表维度表再关联维度表。 2.1.3 星座模型
星座模型是对星型模型的扩展延伸多张事实表共享维度表。 星座模型是很多数据仓库的常态因为很多数据仓库都是多个事实表的。所以星座模型只反映是否有多个事实表他们之间是否共享一些维度表。 2.2 范式模型
即实体关系ER模型数据仓库之父Immon提出的从全企业的高度设计一个3NF模型用实体加关系描述的数据模型描述企业业务架构在范式理论上符合3NF。此建模方法对建模人员的能力要求非常高。 特点设计思路自上而下适合上游基础数据存储同一份数据只存储一份没有数据冗余方便解耦易维护缺点是开发周期一般比较长维护成本高。
详见后文三范式与反范式 2.3 Data Vault模型
DataVault由Hub关键核心业务实体、Link关系、Satellite实体属性 三部分组成 是Dan Linstedt发起创建的一种模型方法论它是在ER关系模型上的衍生同时设计的出发点也是为了实现数据的整合并非为数据决策分析直接使用。 2.4 Anchor模型
高度可扩展的模型所有的扩展只是添加而不是修改因此它将模型规范到6NF基本变成了K-V结构模型。企业很少使用。
信息技术智库关注领取技术资料、面试真题、简历模板和PPT模板一起交流工作难题、面试套路、职场经验、内推直通车公众号
3、数据模型的评价标准
数据模型建设的怎么样极度依赖规范设计如果代码风格是“千人千面”那么恐怕半年下来业务系统就没法看了。没有什么比“数据系统”更看重“法制”了规范体系不仅能保障数据建设的一致性也能够应对业务交接的情况更能够为自动化奠定基础。 业务过程清晰ODS就是原始信息不修改DWD面向基础业务过程DIM描述维度信息DWS针对最小场景做指标计算ADS也要分层面向跨域的建设和面向应用的建设指标可理解按照一定业务事务过程进行业务划分明细层粒度明确、历史数据可获取汇总层维度和指标同名同义能客观反映业务不同角度下的量化程度核心模型相对稳定如果业务过程运行的比较久过程相对固定就要尽快下沉到公共层形成可复用的核心模型高内聚低耦合各主题内数据模型要业务高内聚避免在一个模型耦合其他业务的指标造成该模型主题不清晰和性价比低。
小编有话
在传统企业数仓中业务相对稳定以范式建模为主。如电信、金融行业等在互联网公司业务变化快需求来来回回的改计算和存储也不是问题我们更关心快速便捷的满足业务需求所以以维度建模为主。三、三种事实表 事实表作为数据仓库维度建模的核心紧紧围绕着业务过程来设 计通过获取描述业务过程的度量来表达业务过程包含了引用的维度 和与业务过程有关的度量。
1、三种事实表概述
事实表有三种类型 : 事务事实表、周期快照事实表和累积快照事实表。 1.1 事务事实表
也称原子事实表描述业务过程跟踪控件或时间上某点的度量事件保存的是最原子的数据
个人理解类似于mysql binlog日志每一次相关的 change 都记录下来生成一行新的数据 1.2 周期快照事实表
以一个周期为时间间隔来记录事实一般周期可以是每天、每周、每月、每年等
个人理解只看某个业务过程比如订单收货数据按订单收货时间来切分周期可以为每天、每月等。 1.3 累积快照事实
用来描述过程开始和结束之间的关键步骤事件覆盖过程的整个生命周期通常具有多个日期字段来记录关键时间点当过程随着生命周期不断变化时记录也会随着过程的变化而被修改
个人理解要看整个生命周期的多个业务过程比如创建订单 → 买家付款 → 卖家发货 → 买家确认收货。粒度是一个订单一行数据创建订单时间付款时间发货时间收货时间分别作为一个字段便于计算不同业务过程的时间间隔。
2、三种事实表对比 事务事实表 周期快照事实表 累积快照事实表 时期/时间 离散事务时间点 以有规律的、可预测的 用于时间跨度不确定的不断变化的工作流 日期维度 事务日期 快照日期 相关业务过程涉及的多个日期 粒度 每行代表实体的一个事务 每行代表某时间周期的一个实体 每行代表一个实体的生命周期 事实 事务事实 累积事实 相关业务过程事实和时间间隔事实 事实表加载 插入 插入 插入与更新 事实表更新 不更新 不更新 业务过程变更时更新
3、事实表设计 8 大原则
原则 1尽可能包含所有与业务过程相关的事实 分析哪些事实与业务过程相关是设计过程中非常重要的关注点在事实表中尽量包含所有与业务过程相关的事实即使存在冗余由于事实通常是数字型存储开销不会太大原则 2只选择与业务过程相关的事实 如订单的下单这个业务过程事实表中不应该存在支付金额这个表示支付业务过程的事实原则 3分解不可加性事实为可加的组件 如订单的优惠率应分解为订单原价金额与订单优惠金额两个事实存储在事实表中原则 4在选择维度和事实之前必须先声明粒度 因为原子粒度提供了最大限度的灵活性可以支持无法预期的各种细节层次的用户需求粒度用于确定事实表中一行所表示业务的细节层次决定了维度模型的扩展性每个维度和事实必须与所定义的粒度保持一致设计事实表时粒度定义越细越好一般从最低级别的原子粒度开始原则 5在同一个事实表中不能有多种不同粒度的事实 粒度为票一级实际业务中一个订单可以同时支付多张票票支付金额和票折扣金额两个事实的粒度为 “票级”与定义的粒度一致订单支付金额和订单票数两个事实的粒度为 “订单级”属于上一层订单级数据与 “票级” 事实表的粒度不一致且不能进行汇总如果以订单金额和订单票数这两个维度汇总总金额和总票数会造成大量的重复计算疑问怎么判断不同事实的粒度是否相同原则 6事实的单位要保持一致 如订单金额、订单优惠金额、订单运费这 3 个事实应该采用统一的计量单位统一为元或者分以方便使用原则 7对事实的 null 值要处理 原因在数据库中null 值对常用数字型字段的 SQL 过滤条件都不生效如大于、小于、等于、大于或等于、小于或等于处理用 0 代替 null 原则 8使用退化维度提高事实表的易用性 易用性对事实表更较少关联操作、过滤查询、控制聚合层次、排序数据、定义主从关系等事实表中存储各种类型的常用维度信息较少下游用户使用时关联多个表的操作通过退化维度可以实现对事实表的过滤查询、控制聚合层次、排序数据、定义主从关系等
在 Kimball 的维度建模中通常按照星形模型的方式设计通过事实表的外键关联专门的维表这种方式来获取维度谨慎使用退化维表这与大数据领域的事实表设计不一样 思路通过增加冗余存储减少计算开销提高使用效率4、事实表设计方法
Kimball 的维度模型设计 4 步法选择业务过程、声明粒度、确定维度、确定事实
当前的互联网大数据环境维度模型的设计是基于 Kimball 的四步维度建模方法进行了更进一步的改进 第一步选择业务过程及确定事实表类型 如淘宝的一个交易订单选择 “买家付款” 这个业务过程则事实表类型应为只包含买家付款这一个业务过程的 “单事务事实表”如选择了所有 4 个业务过程并且需要分享各业务过程的时间间隔则事实表类型应为包含了所有 4 个业务过程的 “累积快照事实表”如是选择 “买家付款” 这个业务过程还是选择 “创建订单” 和 “买家付款” 这两个业务过程具体根据业务情况来定思路详细分析需求对业务的整个生命周期进行分析明确关键的业务步骤从而选择与需求有关的业务过程以实例说明如何选择业务过程如何确定事实表类型分析业务的生命周期业务过程通常使用行为动词表示业务执行的活动明确关键的业务步骤该订单流转的业务过程有 4 个创建订单 → 买家付款 → 卖家发货 → 买家确认收货根据业务需求选择与维度建模有关的业务过程根据所选的业务过程确定事实表类型第二步声明粒度 粒度的作用粒度的选择尽量选择最细级别的原子粒度以确保事实表的应用具有最大的灵活性灵活性支持无法预期的各种细节层次的用户需求对于订单级别粒度可以定义为最细的订单级别如父子订单事实表的粒度可以定 “子订单级别” 粒度的声明意味着精确定义事实表的每一行所表示的业务含义明确的粒度能够确保对实表中行的意思的理解不会产生混淆保证所有的事实按照同样的细节层次记录第三步确定维度 如淘宝订单 “付款事务事实表” 中粒度为 “子订单”相关的维度有买家、卖家、商品、收货人信息、业务类型、订单时间等完成了粒度声明就意味着确定了主键对应的维度组合以及相关的维度字段也可以确定了选择维度的原则应该选择能够描述清楚业务过程所处的环境的维度信息第四步确定事实 确定原则选择与业务过程有关的所有事实且事实的粒度要与所声明的事实表的粒度一致思路可以通过回答 “过程的度量是什么” 来确定注意将不可加性事实分解为可加的组件分解的原则可以通过分解后的可加的属性值计算得到不可加性事实四、多维体系结构 在Kimball的维度建模的数据仓库中关于多维体系结构MD有三个关键性概念总线架构Bus Architecture一致性维度Conformed Dimension和一致性事实Conformed Fact。
1、总线架构
多维体系结构(总线架构) 数据仓库领域里有一种构建数据仓库的架构叫Multidimensional ArchitectureMD中文一般翻译为“多维体系结构”也称为“总线架构”Bus Architecture。 多维体系结构的创始人是数据仓库领域中最有实践经验的Kimball博士。多维体系结构主要包括后台Back Room和前台Front Room两部分。后台也称为数据准备区Staging Area是MD架构的最为核心的部件。在后台是一致性维度的产生、保存和分发的场所。同时代理键也在后台产生。前台是MD架构对外的接口包括两种主要的数据集市一种是原子数据集市另一种是聚集数据集市。 原子数据集市保存着最低粒度的细节数据数据以星型结构来进行数据存储。聚集数据集市的粒度通常比原子数据集市要高和原子数据集市一样聚集数据集市也是以星型结构来进行数据存储。前台还包括像查询管理、活动监控等为了提供数据仓库的性能和质量的服务。在多维体系结构中所有的这些基于星型机构来建立的数据集市可以在物理上存在于一个数据库实例中也可以分散在不同的机器上而所有这些数据集市的集合组成的分布式的数据仓库。 2、一致性维度
在多维体系结构中没有物理上的数据仓库由物理上的数据集市组合成逻辑上的数据仓库。而且数据集市的建立是可以逐步完成的最终组合在一起成为一个数据仓库。如果分步建立数据集市的过程出现了问题数据集市就会变成孤立的集市不能组合成数据仓库而一致性维度的提出正式为了解决这个问题。 一致性维度的范围是总线架构中的维度即可能会在多个数据集市中都存在的维度这个范围的选取需要架构师来决定。一致性维度的内容和普通维度并没有本质上区别都是经过数据清洗和整合后的结果。 一致性维度建立的地点是多维体系结构的后台Back Room即数据准备区。 在多维体系结构的数据仓库项目组内需要有专门的维度设计师他的职责就是建立维度和维护维度的一致性。在后台建立好的维度同步复制到各个数据集市。这样所有数据集市的这部分维度都是完全相同的。建立新的数据集市时需要在后台进行一致性维度处理根据情况来决定是否新增和修改一致性维度然后同步复制到各个数据集市。这是不同数据集市维度保持一致的要点。 在同一个集市内一致性维度的意思是两个维度如果有关系要么就是完全一样的要么就是一个维度在数学意义上是另一个维度的子集。 例如如果建立月维度话月维度的各种描述必须与日期维度中的完全一致最常用的做法就是在日期维度上建立视图生成月维度。这样月维度就可以是日期维度的子集在后续钻取等操作时可以保持一致。如果维度表中的数据量较大出于效率的考虑应该建立物化视图或者实际的物理表。这样维度保持一致后事实就可以保存在各个数据集市中。虽然在物理上是独立的但在逻辑上由一致性维度使所有的数据集市是联系在一起随时可以进行交叉探察等操作也就组成了数据仓库。
3、一致性事实
在建立多个数据集市时完成一致性维度的工作就已经完成了一致性的80%90%的工作量。余下的工作就是建立一致性事实。一致性事实和一致性维度有些不同一致性维度是由专人维护在后台Back Room发生修改时同步复制到每个数据集市而事实表一般不会在多个数据集市间复制。需要查询多个数据集市中的事实时一般通过交叉探查drill across来实现。 为了能在多个数据集市间进行交叉探查一致性事实主要需要保证两点第一个是KPI的定义及计算方法要一致第二个是事实的单位要一致性。如果业务要求或事实上就不能保持一致的话建议不同单位的事实分开建立字段保存。 这样一致性维度将多个数据集市结合在一起一致性事实保证不同数据集市间的事实数据可以交叉探查一个分布式的数据仓库就建成了。 小遍有话
总线矩阵业务过程和维度的交点一致性维度同一集市的维度表内容相同或包含一致性事实不同集市的同一事实需保证口径一致单位统一。追求一致性必然会增加开发工作量但长期来说使用方便、运维简单一致性和性能之间需要平衡。
五、数据仓库规范设计 1、为什么要进行规范设计
无规矩、不方圆。规范设计是在具体开发工作之前制定的过程中不断进行完善。目的在于约束 N 个人对齐认知按照一个标准或流程进行开发以保证数据一致性流程清晰且稳定。 一个良好的规范设计应当起到以下作用提高开发效率提升质量降低沟通对齐成本降低运维成本等。 下面西红柿将带领大家盘一盘数据仓库有哪些规范从中挑选几个重点细说
设计规范逻辑架构、技术架构、分层设计、主题划分、方法论 命名规范各层级命名、任务命名、表命名、字段命名、指标命名等
模型规范建模方法、建模工具、血缘关系、维度退化、一致性维度、元数据管理
开发规范脚本注释、字段别名、编码规范、脚本格式、数据类型、缩写规范
流程规范需求流程、工程流程、上线流程、调度流、调度和表生命周期管理 2、设计规范 - 指标
Step1面向主题域管理
为了提高指标管理的效率你需要按照业务线、主题域和业务过程三级目录方式管理指标。
Step2划分原子指标和派生指标
原子指标 原子指标 派生指标
Step3进行指标命名规范
需要遵循两个原则易懂与统一
易懂就是看到指标的名称就可以基本判断这个指标归属于哪个业务过程统一就是要确保派生指标和它继承的原子指标命名是一致的。对于原子指标标名称适合用“动作 度量”的命名方式比如注册用户数、购买用户数 对于派生指标应该严格遵循“时间周期 统计粒度 修饰词 原子指标”的命名方式。比如30天内黑卡会员购买用户数 Step4分级管理
指标确实是多如果一视同仁去管理其实很难所以可以按照下面的原则进行等级划分
一级指标数据中台直接产出核心指标提供给公司高层看的、原子指标以及跨部门的派生指标。二级指标基于中台提供的原子指标业务部门创建的派生指标。3、命名规范 - 表命名
3.1 常规表
常规表是我们需要固化的表是正式使用的表是目前一段时间内需要去维护去完善的表。 规范分层前缀[dwd|dws|ads|bi]_业务域_主题域_XXX_更新频率|全量/增量。 业务域、主题域我们都可以用词根的方式枚举清楚不断完善粒度也是同样的主要的是时间粒度、日、月、年、周等使用词根定义好简称。
建议格式 dwd_xxx_xxx_da
di 每日增量da每日全量mi每月增量ma每月全量3.2 中间表
中间表一般出现在Job中是Job中临时存储的中间数据的表中间表的作用域只限于当前Job执行过程中Job一旦执行完成该中间表的使命就完成了是可以删除的按照自己公司的场景自由选择以前公司会保留几天的中间表数据用来排查问题。 建议格式mid_table_name_[0~9] table_name是我们任务中目标表的名字通常来说一个任务只有一个目标表。这里加上表名是为了防止自由发挥的时候表名冲突而末尾大家可以选择自由发挥起一些有意义的名字或者简单粗暴使用数字代替各有优劣吧谨慎选择。 3.3 临时表
临时表是临时测试的表是临时使用一次的表就是暂时保存下数据看看后续一般不再使用的表是可以随时删除的表。 建议格式tmp_xxx
只要加上tmp开头即可其他名字随意注意tmp开头的表不要用来实际使用只是测试验证而已。 3.4 维度表
维度表是基于底层数据抽象出来的描述类的表。维度表可以自动从底层表抽象出来也可以手工来维护。 建议格式dim_xxx
维度表统一以dim开头后面加上对该指标的描述可以自由发挥。 4、开发规范 1 表和列的注释释是否有缺失复杂计算逻辑是否有注释释 2 任务是否支持多次重跑而输出不变不能有insert into语句 3 分区表是否使用分区键过滤并且有有效裁剪 4 外连接的过逑条件是否使用正确例如在左连接的where语句存在右表的过滤条件 5 关联小表是否使用/* map join * / hint 6 不允许引用别的计算任务临时表 7 原则上不允许存在一个任务更新多个目标表 8 是否存在笞、迪卡尔积 9 禁止在代码里面使用drop 111ble、creat它111ble、renaiue 111ble、chan零column等ddl语句 10 使用动态分区时有没有检查分区键值为NULL的情况 11 DQC质量监控规则是否配置严禁棵奔 12 代码中有没有进行适当的规避数据倾斜语句 13 Where条件中is null语句有没有进行空字符串处理 5、流程规范
根据阿里流程规范本文将数据仓库研发流程抽象为如下几点
需求阶段数据产品经理应如何应对不断变化的业务需求。设计阶段数据产品经理、数据开发者应如何综合性能、成本、效率、质量等因素更好地组织与存储数据。开发阶段数据研发者如何高效、规范地进行编码工作。测试阶段测试人员应如何准确地暴露代码问题与项目风险提升产出质量。发布阶段如何将具备发布条件的程序平稳地发布到线上稳定产出。运维阶段运维人员应如何保障数据产出的时效性和稳定性。六、元数据管理
1、业务元数据
描述 ”数据”背后的业务含义主题定义每段 ETL、表背后的归属业务主题。业务描述每段代码实现的具体业务逻辑。标准指标类似于 BI 中的语义层、数仓中的一致性事实将分析中的指标进行规范化。标准维度同标准指标对分析的各维度定义实现规范化、标准化。不断的进行维护且与业务方进行沟通确认。2、技术元数据 数据源元数据 例如数据源的 IP、端口、数据库类型数据获取的方式数据存储的结构原数据各列的定义及 key 指对应的值。ETL 元数据 根据 ETL 目的的不同可以分为两类数据清洗元数据数据处理元数据。数据清洗主要目的是为了解决掉脏数据及规范数据格式因此此处元数据主要为各表各列的正确数据规则默认数据类型的正确规则。数据处理例如常见的表输入表输出非结构化数据结构化特殊字段的拆分等。源数据到数仓、数据集市层的各类规则。比如内容、清理、数据刷新规则。数据仓库元数据 数据仓库结构的描述包括仓库模式、视图、维、层次结构及数据集市的位置和内容业务系统、数据仓库和数据集市的体系结构和模式等。BI 元数据 汇总用的算法、包括各类度量和维度定义算法。数据粒度、主题领域、聚集、汇总、预定义的查询与报告。
3、管理元数据
管理领域相关包括管理流程、人员组织、角色职责等。
4、小编有话
在日常工作中元数据的管理主要体现在元数据的采集、存储、查询、应用几个方面。原则上应从规范化到脚本化到工具化的方向进行建设。 采集元数据采集时尽可能详细真实可通过工具生成或者勾选避免手动录入带来不规范等问题存储存储元数据要做到不失真元数据变更时及时同步查询通过网页或库表等方式方便快捷的看到元数据辅助进行开发应用数据血缘、优化调度依赖、数据治理等七、维度表 1、什么是维度表
维度是维度建模的基础和灵魂。在维度建模中将度量称为“事实” 将环境描述为“维度”。 维度表包含了事实表中指定属性的相关详细信息最常用的维度表有日期维度、城市维度等。 日期维表 num 字段名 字段中文名 描述 数据类型 1 date 日期 日期 yyyMMdd格式 bigint 2 week 星期数字型 星期数字型 0-6 bigint 3 week_cn 星期中文名 星期中文名 星期一…… string 4 year_weeks 一年中的第几周 一年中的第几周 1 2 3…… bigint 5 mon_dt 本周周一日期 本周周一日期 bigint 6 sun_dt 本周周日日期 本周周日日期 bigint 7 month 年月 年月yyyyMM格式 bigint 8 month_short 月份简写 月份简写MM格式1~12 bigint 9 month_cn 月份中文名 月份中文名 一月…… string 10 quarter 季度 季度yyyyQ1\2\3\4 string 11 quarter_short 季度 数字型 季度 数字型 1-4 bigint 12 quarter_cn 季度中文名 季度中文名 第一季度…… string 13 year 年份 年份yyyy格式 bigint
2、维度表设计原则
维度的作用一般是查询约束、分类汇总以及排序等我们在进行维度表设计时应当提前考虑 1维度属性尽量丰富为数据使用打下基础
比如淘宝商品维度有近百个维度属性为下游的数据统计、分析、探查提供了良好的基础。 2给出详实的、富有意义的文字描述
属性不应该是编码而应该是真正的文字。在间里巴巴维度建模中 一般是编码和文字同时存在比如商品维度中的商品 ID 和商品标题、 类目 ID 和 类目名称等。ID 一 般用于不同表之间的关联而名称一般用 于报表标签 3区分数值型属性和事实
数值型宇段是作为事实还是维度属性可以参考字段的一般用途。如果通常用于查询约束条件或分组统计则是作为维度属性;如果通常 用于参与度量的计算 则是作为事实。比如商品价格可以用于查询约 束条件或统计价格区间 的商品数量此时是作为维度属性使用的;也可 以用于统计某类目 下商品的平均价格此时是作为事实使用的。另外 如果数值型字段是离散值则作为维度属性存在的可能性较大;如果数 值型宇段是连续值 则作为度量存在的可能性较大但并不绝对需要 同时参考宇段的具体用途。 4沉淀出通用的维度属性为建立一致性维度做好铺垫
有些维度属性获取需要进行比较复杂的逻辑处理有些需要通过多表关联得到或者通过单表 的不同宇段混合处理得到或者通过对单表 的某个字段进行解析得到。此时需要将尽可能多的通用的维度属性进 行沉淀。一方 面可以提高下游使用的方便性减少复杂度;另一方面可以避免下游使用解析时由于各自逻辑不同而导致口径不 一致。 5退化维度Degenerate Dimension
在维度类型中有一种重要的维度称作为退化维度。这种维度指的是直接把一些简单的维度放在事实表中。退化维度是维度建模领域中的一个非常重要的概念它对理解维度建模有着非常重要的作用退化维度一般在分析中可以用来做分组使用。 6缓慢变化维Slowly Changing Dimensions
维度的属性并不是始终不变的它会随着时间的流逝发生缓慢的变化这种随时间发生变化的维度我们一般称之为缓慢变化维SCD缓慢变化维一般使用代理健作为维度表的主健。 推荐阅读缓慢变化维度的10种处理方式 缓慢变化维
① TYPE1 直接覆盖原值
适用于不看历史数据简单粗暴 ② TYPE2 拉链表
需要在维度行再增加三列有效日期、截止日期、行标识可选。
在旧的一行数据增加关链时间end_date新的一行数据增加开链时间和关链时间多条数据加起来是一个完整的时间周期。 ③ TYPE3 增加属性列 3、维度表设计方法
第一步选择维度或新建维度。作为维度建模的核心在企业级数 据仓库中必须保证维度的唯一性。以淘宝商品维度为例有且只允许有 一个维度定义。第二步确定主维表。此处的主维表一般是 ODS 表直接与业务 系统同步。以淘宝商品维度为例 s_auction_auctions 是与前台商品中心 系统同步的商品表此表即是主维表。第三步确定相关维表。数据仓库是业务源系统的数据整合不同业务系统或者同 一业务系统中的表之间存在 关联性。根据对业务的梳 理确定哪些表和主维表存在关联关系并选择其中的某些表用于生成维度属性。第四步 确定维度属性 。本步骤主要 包括两个阶段其中第 一 个阶 段是从主维表 中选择维度属性或生成新的维度属性;第 二个阶段是从相 关维表中选择维度属性或生成新 的维度属性。以淘宝商品维度为例从 主维表 (s_auction_auctions)和类目、 SPU、卖家、店铺等相关维表中 选择维度属性或生成新 的维度属性。八、三范式与反范式 范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中这种规则就是范式。 关系数据库中的关系必须满足一定的要求即满足不同的范式。大数据生态中各类强大的查询引擎层出不穷相对廉价的磁盘和分布式技术也让数据冗余变得可接受甚至更加方便。 在创建一个数据库的过程中范化是将其转化为一些表的过程这种方法可以使从数据库得到的结果更加明确。这样可能使数据库产生重复数据从而导致创建多余的表。范化是在识别数据库中的数据元素、关系以及定义所需的表和各表中的项目等这些初始工作之后的一个细化的过程。 1、第一范式
1NF要求属性具有原子性即列不可再分解
表字段1、 字段2(字段2.1、字段2.2)、字段3 ......
如学生学号姓名性别出生年月日 有些钢筋可能要问西红柿了姓名可以拆成姓、名两列 “出生年月日” 也可以拆成年、月、日三个字段。所以就不满足第一范式了这里再强调一下原子性原子性是根据使用方便来自定义的最小单位。中国人一般姓名一起用美国就习惯姓名分别存两字段。 2、第二范式
2NF要求记录有惟一标识即不存在部分依赖
简单来说就是拆表以人为粒度做一张明细表以课程号为粒度做一张维度表两表关联使用消除了数据冗余
表学号、课程号、姓名、学分;
这个表明显说明了两个事务:学生信息, 课程信息;由于非主键字段必须依赖主键这里学分依赖课程号姓名依赖与学号所以不符合二范式。 可能会存在问题
数据冗余每条记录都含有相同信息删除异常删除所有学生成绩就把课程信息全删除了插入异常学生未选课无法记录进数据库更新异常调整课程学分所有行都调整。
正确做法: 学生Student(学号, 姓名) 课程Course(课程号, 学分) 选课关系StudentCourse(学号, 课程号, 成绩)。 3、第三范式
3NF是对字段的冗余性要求任何字段不能由其他字段派生出来它要求字段没有冗余即不存在传递依赖
表: 学号, 姓名, 年龄, 学院名称, 学院电话
因为存在依赖传递: (学号) → (学生)→(所在学院) → (学院电话) 。 可能会存在问题
数据冗余:有重复值更新异常有重复的冗余信息修改时需要同时修改多条记录否则会出现数据不一致的情况 。正确做法
学生(学号, 姓名, 年龄, 所在学院)
学院(学院, 电话)。 4、反范式化
一般说来数据库只需满足第三范式3NF就行了。 没有冗余的数据库设计可以做到。但是没有冗余的数据库未必是最好的数据库有时为了提高运行效率就必须降低范式标准适当保留冗余数据。具体做法是在概念数据模型设计时遵守第三范式降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段允许冗余达到以空间换时间的目的。 〖例〗有一张存放商品的基本表如表1所示。“金额”这个字段的存在表明该表的设计不满足第三范式因为“金额”可以由“单价”乘以“数量”得到说明“金额”是冗余字段。但是增加“金额”这个冗余字段可以提高查询统计的速度这就是以空间换时间的作法。 在Rose 2002中规定列有两种类型数据列和计算列。“金额”这样的列被称为“计算列”而“单价”和“数量”这样的列被称为“数据列”。 5、范式化设计和反范式化设计的优缺点
5.1 范式化 时间换空间
优点
范式化的表减少了数据冗余数据表更新操作快、占用存储空间少。
缺点
查询时需要对多个表进行关联查询性能降低。 更难进行索引优化5.2 反范式化空间换时间
反范式的过程就是通过冗余数据来提高查询性能但冗余数据会牺牲数据一致性
优点
可以减少表关联可以更好进行索引优化
缺点
存在大量冗余数据数据维护成本更高删除异常插入异常更新异常6、OLAP和OLTP中范式设计
OLAP 一般冗余比较多以查询分析为主这种一般都是采用反范式设计以提高查询效率。更新一般是定时大批量数据插入。
OLTP 则是尽可能消除冗余以提高变更的效率。因为这种应用无时无刻不在频繁变化。
九、数据仓库架构-Lambda和Kappa 随着数据量的暴增和数据实时性要求越来越高以及大数据技术的发展驱动企业不断升级迭代数据仓库架构方面也在不断演进分别经历了以下过程早期经典数仓架构 离线大数据架构 Lambda Kappa 混合架构。 架构 组成 特点 经典数仓架构 关系型数据库mysql、oracle为主 数据量小实时性要求低 离线大数据架构 hivespark为主 数据量大实时性要求低 Lambda hivespark负责存量strom/Flink负责实时计算 数据量大实时性要求高 Kappa kafka、strom、Flink 多业务多数据源事件型数据源 混合架构
ps.西红柿的举例若有不当欢迎指正 1、Lambda架构原理
Lambda架构的核心思想是把大数据系统拆分成三层Batch LayerSpeed Layer和Serving Layer。其中Batch Layer负责数据集存储以及全量数据集的预查询。 Speed Layer主要负责对增量数据进行计算生成Realtime Views。Serving Layer用于响应用户的查询请求它将Batch Views和Realtime Views的结果进行合并得到最后的结果返回给用户如下图 2、Lambda架构的缺点
Lambda架构解决了大数据量下实时计算的问题但架构本身也存在一定缺点。
实时与批量计算结果不一致引起的数据口径问题因为批量和实时计算走的是两个计算框架和计算程序算出的结果往往不同经常看到一个数字当天看是一个数据第二天看昨天的数据反而发生了变化。批量计算在计算窗口内无法完成在IOT时代数据量级越来越大经常发现夜间只有4、5个小时的时间窗口已经无法完成白天20多个小时累计的数据保证早上上班前准时出数据已成为每个大数据团队头疼的问题。开发和维护的复杂性问题Lambda 架构需要在两个不同的 APIapplication programming interface应用程序编程接口中对同样的业务逻辑进行两次编程一次为批量计算的ETL系统一次为流式计算的Streaming系统。针对同一个业务问题产生了两个代码库各有不同的漏洞。这种系统实际上非常难维护服务器存储大数据仓库的典型设计会产生大量的中间结果表造成数据急速膨胀加大服务器存储压力。3、Kappa架构原理
Kappa架构的核心思想包括以下三点
用Kafka或者类似的分布式队列系统保存数据你需要几天的数据量就保存几天。当需要全量重新计算时重新起一个流计算实例从头开始读取数据进行处理并输出到一个新的结果存储中。当新的实例做完后停止老的流计算实例并把老的一些结果删除。在Kappa架构下只有在有必要的时候才会对历史数据进行重复计算并且实时计算和批处理过程使用的是同一份代码。
4、Lambda架构和Kappa架构优缺点对比 项目 Lambda Kappa 数据处理能力 可以处理超大规模的历史数据 历史数据处理的能力有限 机器开销 批处理和实时计算需一直运行机器开销大 必要时进行全量计算机器开销相对较小 存储开销 只需要保存一份查询结果存储开销较小 需要存储新老实例结果存储开销相对较大 开发、测试难度 实现两套代码开发、测试难度较大 只需面对一个框架开发、测试难度相对较小 运维成本 维护两套系统运维成本大 只需维护一个框架运维成本小 5、数据架构评价标准
响应速度数据架构的主要场景包括业务开发、数据产品、运营分析三大类不论是那种场景数据架构均应该在尽可能短的时间内响应需求可复用性只有复用能力上来了响应速度才能提上来体现在下游依赖、调用次数、核心字段覆盖率等指标上稳定性除了日常任务不出问题以外一旦发现了问题能在多短的时间内定位和恢复问题就非常重要健壮性除了电商等已经耕耘多年的领域外绝大多数业务模型都会快速的变化如何适应这种变化就非常考验架构功底。
6、小编有话
Lambda 将全量历史数据和实时增量数据合并输出。Kappa 两个流协作输出queries每次使用最新一个流处理结果
目前很多准实时增量批处理方案也能满足实时性需求从稳定性和运维成本上也表现更佳。
比如kudu存储impala计算准实时方案可以实现千万级数据的增量更新和olap查询性能优异。
十、数据治理目的、方法、流程 1、什么是数据治理
数据治理Data Governance是组织中涉及数据使用的一整套管理行为。由企业数据治理部门发起并推行关于如何制定和实施针对整个企业内部数据的商业应用和技术管理的一系列政策和流程。 数据的质量直接影响着数据的价值并且直接影响着数据分析的结果以及我们以此做出的决策的质量。 我们常说用数据说话用数据支撑决策管理但低质量的数据、甚至存在错误的数据必然会说假话数据治理即提高数据的质量发挥数据资产价值。 2、数据治理的目的
降低风险建立数据使用内部规则实施合规要求改善内部和外部沟通增加数据价值方便数据管理降低成本通过风险管理和优化来帮助确保公司的持续生存3、数据治理的方法
从技术实施角度看数据治理包含“理”“采”“存”“管”“用”这五个步骤即业务和数据资源梳理、数据采集清洗、数据库设计和存储、数据管理、数据使用。 数据资源梳理数据治理的第一个步骤是从业务的视角厘清组织的数据资源环境和数据资源清单包含组织机构、业务事项、信息系统以及以数据库、网页、文件和 API 接口形式存在的数据项资源本步骤的输出物为分门别类的数据资源清单。 数据采集清洗通过可视化的 ETL 工具例如阿里的 DataXPentaho Data Integration将数据从来源端经过抽取 (extract)、转换 (transform)、加载 (load) 至目的端的过程目的是将散落和零乱的数据集中存储起来。 基础库主题库建设一般情况下可以将数据分为基础数据、业务主题数据和分析数据。基础数据一般指的是核心实体数据或称主数据例如智慧城市中的人口、法人、地理信息、信用、电子证照等数据。主题数据一般指的是某个业务主题数据例如市场监督管理局的食品监管、质量监督检查、企业综合监管等数据。而分析数据指的是基于业务主题数据综合分析而得的分析结果数据例如市场监督管理局的企业综合评价、产业区域分布、高危企业分布等。那么基础库和主题库的建设就是在对业务理解的基础上基于易存储、易管理、易使用的原则抽像数据存储结构说白了就是基于一定的原则设计数据库表结构然后再根据数据资源清单设计数据采集清洗流程将整洁干净的数据存储到数据库或数据仓库中。 元数据管理元数据管理是对基础库和主题库中的数据项属性的管理同时将数据项的业务含义与数据项进行了关联便于业务人员也能够理解数据库中的数据字段含义并且元数据是后面提到的自动化数据共享、数据交换和商业智能BI的基础。需要注意的是元数据管理一般是对基础库和主题库中即核心数据资产的数据项属性的管理而数据资源清单是对各类数据来源的数据项的管理。 血缘追踪数据被业务场景使用时发现数据错误数据治理团队需要快速定位数据来源修复数据错误。那么数据治理团队需要知道业务团队的数据来自于哪个核心库核心库的数据又来自于哪个数据源头。我们的实践是在元数据和数据资源清单之间建立关联关系且业务团队使用的数据项由元数据组合配置而来这样就建立了数据使用场景与数据源头之间的血缘关系。数据资源目录数据资源目录一般应用于数据共享的场景例如政府部门之间的数据共享数据资源目录是基于业务场景和行业规范而创建同时依托于元数据和基础库主题而实现自动化的数据申请和使用。 质量管理数据价值的成功发掘必须依托于高质量的数据唯有准确、完整、一致的数据才有使用价值。因此需要从多维度来分析数据的质量例如偏移量、非空检查、值域检查、规范性检查、重复性检查、关联关系检查、离群值检查、波动检查等等。需要注意的是优秀的数据质量模型的设计必须依赖于对业务的深刻理解在技术上也推荐使用大数据相关技术来保障检测性能和降低对业务系统的性能影响例如 HadoopMapReduceHBase 等。 商业智能BI数据治理的目的是使用对于一个大型的数据仓库来说数据使用的场景和需求是多变的那么可以使用 BI 类的产品快速获取需要的数据并分析形成报表比较知名的产品有 Microsoft Power BIQlikViewTableau帆软等。 数据共享交换数据共享包括组织内部和组织之间的数据共享共享方式也分为库表、文件和 API 接口三种共享方式库表共享比较直接粗暴文件共享方式通过 ETL 工具做一个反向的数据交换也就可以实现。我们比较推荐的是 API 接口共享方式在这种方式下能够让中心数据仓库保留数据所有权把数据使用权通过 API 接口的形式进行了转移。API 接口共享可以使用 API 网关实现常见的功能是自动化的接口生成、申请审核、限流、限并发、多用户隔离、调用统计、调用审计、黑白名单、调用监控、质量监控等等。 4、数据质量8个衡量标准
数据的准确性
数据采集值或者观测值和真实值之间的接近程度也叫做误差值误差越大准确度越低。
数据的精确性
指对同一对象的观测数据在重复测量时所得到不同数据间的接近程度。
数据的真实性数据的及时性
数据能否在需要的时候得到保证比如月初的财务对账能不能在月初就完成
数据的即时性
指数据采集时间节点和数据传输的时间节点一个数据在数据源头采集后立即存储并立即加工呈现就是即时数据而经过一段时间之后再传输到信息系统中则数据即时性就稍差。
数据的完整性
应采集和实际采集到数据之间的比例。
数据的全面性
完整性衡量的是应采集和实际采集的差异。而全面性指的是数据采集点的遗漏情况。
数据的关联性
指各个数据集之间的关联关系。比如员工工资数据和员工绩效考核数据是通过员工这个资源关联在一起来的。 信息技术智库关注领取技术资料、面试真题、简历模板和PPT模板一起交流工作难题、面试套路、职场经验、内推直通车公众号
5、数据治理流程 基本流程发现数据质量问题 定义数据质量规则 质量控制 质量评估 质量优化
十一、ETL 1、什么是ETL
ETL是英文Extract-Transform-Load的缩写用来描述将数据从来源端经过抽取extract、转换transform、加载load至目的端的过程是数据仓库的生命线。 抽取Extract主要是针对各个业务系统及不同服务器的分散数据充分理解数据定义后规划需要的数据源及数据定义制定可操作的数据源制定增量抽取和缓慢渐变的规则。 转换transform主要是针对数据仓库建立的模型通过一系列的转换来实现将数据从业务模型到分析模型通过ETL工具可视化拖拽操作可以直接使用标准的内置代码片段功能、自定义脚本、函数、存储过程以及其他的扩展方式实现了各种复杂的转换并且支持自动分析日志清楚的监控数据转换的状态并优化分析模型。
装载Load主要是将经过转换的数据装载到数据仓库里面可以通过直连数据库的方式来进行数据装载可以充分体现高效性。在应用的时候可以随时调整数据抽取工作的运行方式可以灵活的集成到其他管理系统中。
2、ETL ELT
伴随着数据仓库的发展传送门数据仓库的八个发展阶段数据量从小到大数据实时性从T1到准实时、实时ETL也在不断演进。
在传统数仓中数据量小计算逻辑相对简单我们可以直接用ETL工具实现数据转换T转换之后再加载到目标库即Extract-Transform-Load。但在大数据场景下数据量越大越大计算逻辑愈发复杂数据清洗需放在运算能力更强的分布式计算引擎中完成ETL也就变成了ELTExtract-Load-Transform。即Extract-Transform-Load Extract-Load-Transform
通常我们所说的ETL已经泛指数据同步、数据清洗全过程而不仅限于数据的抽取-转换-加载。 3、常用的ETL工具
下面小编将介绍几类ETL工具sqoopDataXKettlecanalStreamSets。 3.1 sqoop
是Apache开源的一款在Hadoop和关系数据库服务器之间传输数据的工具。可以将一个关系型数据库MySQL ,Oracle等中的数据导入到Hadoop的HDFS中也可以将HDFS的数据导出到关系型数据库中。sqoop命令的本质是转化为MapReduce程序。sqoop分为导入import和导出export策略分为table和query模式分为增量和全量。3.2 DataX
DataX 是阿里巴巴集团内被广泛使用的离线数据同步工具/平台实现包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各种异构数据源之间高效的数据同步功能。3.3 Kettle
一款国外免费开源的、可视化的、功能强大的ETL工具纯java编写可以在Windows、Linux、Unix上运行数据抽取高效稳定。3.4 canal
canal是阿里巴巴旗下的一款开源项目纯Java开发。基于数据库增量日志解析提供增量数据实时订阅和消费目前主要支持了MySQL也支持mariaDB。3.5 StreamSets
是大数据实时采集ETL工具可以实现不写一行代码完成数据的采集和流转。通过拖拽式的可视化界面实现数据管道(Pipelines)的设计和定时任务调度。创建一个Pipelines管道需要配置数据源(Origins)、操作(Processors)、目的地(Destinations)三部分。
4、ETL加载策略
4.1 增量
有些表巨大我们需要选择增量策略新增delta数据需要和存量数据merge合并。
只有新增(full join。能拿更新表就拿更新表) 新增删除 history-table Left join delet-table where delect-table.value is null 表a表a full join update-table (能拿update就拿update)4.2 全量
每天一个全量表也可一个hive天分区一个全量。 4.3 流式
使用kafka消费mysql binlog日志到目标库源表和目标库是11的镜像。
5、小编有话
无论是全量还是增量的方式都会浪费多余的存储或通过计算去重得到最新的全量数据。为解决这一问题西红柿墙裂建议kafka的数据同步方案源表变化一条目标表消费一条目标表数据始终是一份最新全量数据且为实时同步的。 ps.极端情况下可能会丢数需要写几个监控脚本详见上文数据质量部分和补数脚本即可~
十二、数据应用-OLAP 1、olap和oltp的区别 OLTP OLAP 对象 业务开发人员 分析决策人员 功能 日常事务处理 面向分析决策 模型 关系模型 多维模型 数据量 几条或几十条记录 百万于万条记录 操作类型 增、删、查、改(CRUD) 查询为主 总体概括 联机事务处理 在线分析处理
2、OLAP分类
MOLAP基于多维数组的存储模型也是OLAP最初的形态特点是对数据进行预计算以空间换效率明细和聚合数据都保存在cube中。但生成cube需要大量时间和空间。ROLAP基于关系模型进行存储数据不需要预计算按需即时查询。明细和汇总数据都保存在关系型数据库事实表中。其特点是与事务实体对应关系清晰但一般需要较为复杂的数据准备。在响应前端需求时一般较快但取决于计算引擎能力。HOLAP混合模型细节数据以ROLAP存放聚合数据以MOLAP存放。这种方式相对灵活且更加高效。可按企业业务场景和数据粒度进行取舍没有最好只有最适合。3、OLAP基本操作
★钻取维的层次变化从粗粒度到细粒度汇总数据下钻到明细数据。如通过季度销售数据钻取每个月的销售数据。★上卷钻取的逆向上钻取。从细粒度到粗粒度细粒度数据到不同维层级的汇总。eg. 通过每个月的销售数据汇总季度、年销售数据。★切片特定维数据剩余维两个。eg. 只选电子产品销售数据。★切块维区间数据剩余维三个。eg. 第一季度到第二季度销售数据。★旋转维位置互换数据行列互换通过旋转可以得到不同视角的数据。4、OLAP选型
4.1 druid
实时查询和分析的高容错、高性能开源分布式系统用于解决如何在大规模数据集下进行快速的、交互式的查询和分析。实时的数据消费真正做到数据摄入实时、查询结果实时。扩展性强支持 PB 级数据极高的高可用保障支持滚动升级。druid属于时间存储删除操作比较繁琐且不支持查询条件删除数据只能根据时间范围删除数据。Druid能接受的数据的格式相对简单比如不能处理嵌套结构的数据。4.2 kylin
可扩展超快olap引擎Hadoop/Spark上百亿数据规模提供 Hadoop ANSI SQL 接口交互式查询能力用户可以与Hadoop数据进行亚秒级交互百亿以上数据集构建多维立方体MOLAP CUBE与BI工具无缝整合如TableauPowerBI/ExcelMSTRQlikSenseHue和SuperSet十三、数据倾斜 1、数据倾斜表现
1.1 hadoop中的数据倾斜表现
有一个多几个Reduce卡住卡在99.99%一直不能结束。各种container报错OOM异常的Reducer读写的数据量极大至少远远超过其它正常的Reducer伴随着数据倾斜会出现任务被kill等各种诡异的表现。1.2 hive中数据倾斜
一般都发生在Sql中group by和join on上而且和数据逻辑绑定比较深。
1.3 Spark中的数据倾斜
Spark中的数据倾斜包括Spark Streaming和Spark Sql表现主要有下面几种
Executor lostOOMShuffle过程出错Driver OOM单个Executor执行时间特别久整体任务卡在某个阶段不能结束正常运行的任务突然失败
2、数据倾斜产生原因
我们以Spark和Hive的使用场景为例。 在做数据运算的时候会涉及到count distinct、group by、join on等操作这些都会触发Shuffle动作。一旦触发Shuffle所有相同key的值就会被拉到一个或几个Reducer节点上容易发生单点计算问题导致数据倾斜。
一般来说数据倾斜原因有以下几方面
1key分布不均匀 2建表时考虑不周
举一个例子就说数据默认值的设计吧假设我们有两张表 user用户信息表useridregister_ip ipIP表ipregister_user_cnt 这可能是两个不同的人开发的数据表。如果我们的数据规范不太完善的话会出现一种情况
user表中的register_ip字段如果获取不到这个信息我们默认为null
但是在ip表中我们在统计这个值的时候为了方便我们把获取不到ip的用户统一认为他们的ip为0。
两边其实都没有错的但是一旦我们做关联了这个任务会在做关联的阶段也就是sql的on的阶段卡死。
3业务数据激增
比如订单场景我们在某一天在北京和上海两个城市多了强力的推广结果可能是这两个城市的订单量增长了10000%其余城市的数据量不变。
然后我们要统计不同城市的订单情况这样一做group操作可能直接就数据倾斜了。
3、解决数据倾斜思路
很多数据倾斜的问题都可以用和平台无关的方式解决比如更好的数据预处理异常值的过滤等。因此解决数据倾斜的重点在于对数据设计和业务的理解这两个搞清楚了数据倾斜就解决了大部分了。
1业务逻辑
我们从业务逻辑的层面上来优化数据倾斜比如上面的两个城市做推广活动导致那两个城市数据量激增的例子我们可以单独对这两个城市来做count单独做时可用两次MR第一次打散计算第二次再最终聚合计算。完成后和其它城市做整合。
2程序层面
比如说在Hive中经常遇到count(distinct)操作这样会导致最终只有一个Reduce任务。
我们可以先group by再在外面包一层count就可以了。比如计算按用户名去重后的总用户量
1优化前
只有一个reduce先去重再count负担比较大
select name,count(distinct name)from user; 2优化后
// 设置该任务的每个job的reducer个数为3个。Hive默认-1自动推断。
set mapred.reduce.tasks3;
// 启动两个job一个负责子查询(可以有多个reduce)另一个负责count(1)
select count(1) from (select name from user group by name) tmp;
3调参方面
Hadoop和Spark都自带了很多的参数和机制来调节数据倾斜合理利用它们就能解决大部分问题。
4从业务和数据上解决数据倾斜
很多数据倾斜都是在数据的使用上造成的。我们举几个场景并分别给出它们的解决方案。
一个原则尽早过滤每个阶段的数据量。
数据有损的方法找到异常数据比如ip为0的数据过滤掉。数据无损的方法对分布不均匀的数据单独计算。hash法先对key做一层hash先将数据随机打散让它的并行度变大再汇聚。数据预处理就是先做一层数据质量处理类似于数据仓库维度建模时底层先处理数据质量。添加公众号「信息技术智库」 硬核资料20G8大类资料关注即可领取PPT模板、简历模板、技术资料 技术互助技术群大佬指点迷津你的问题可能不是问题求资源在群里喊一声。 面试题库由各个技术群小伙伴们共同投稿热乎的大厂面试真题持续更新中。 知识体系含编程语言、算法、大数据生态圈组件Mysql、Hive、Spark、Flink、数据仓库、前端等。 送书抽奖丨技术互助丨粉丝福利
