数仓知识梳理

一、千里之行始于足下-数据同步

1.同步方式：

• 1>.直连同步，jdbc方式适合操作形业务系统数据同步。但是这种方式对数据库的性能影响较大，一般可以采取主备策略，从备库抽取数据。sqoop、datax
• 2>.数据文件同步，通过约定好的编码格式、大小等从源系统生成txt文件，通过ftp传输等方式加载到目标数据库。为保证文件同步的安全性和准确性，可以考虑源系统增加压缩和加密到目标系统解密和解压缩的方式。
• 3>.日志binlog同步，实现实时同步。性能好，效率高。但是需要部署实时同步系统。

datax 和 sqoop以及kettle的区别
转自sqoop与datax的区别
sqoop和datax的区别：
1、sqoop采用map-reduce计算框架进行导入导出，而datax仅仅在运行datax的单台机器上进行数据的抽取和加载，速度比sqoop慢了许多；
2、sqoop只可以在关系型数据库和hadoop组件之间进行数据迁移，而在hadoop相关组件之间，比如hive和hbase之间就无法使用sqoop互相导入导出数据，
同时在关系型数据库之间，比如mysql和oracle之间也无法通过sqoop导入导出数据。与之相反，datax能够分别实现关系型数据库和hadoop组件之间、关系型数据库
之间、hadoop组件之间的数据迁移；
3、sqoop是专门为hadoop而生，对hadoop支持度好，而datax可能会出现不支持高版本hadoop的现象；
4、sqoop只支持官方提供的指定几种关系型数据库和hadoop组件之间的数据交换，而在datax中，用户只需根据自身需求修改文件，生成相应rpm包，自行安装之后就可以使用自己定制的插件

2.数据同步后如何同步业务库的DML操作？

可以通过DBA系统对接获得业务数据库的dml语句，响应的对数仓数据库和同步脚本进行调整，

3.增量数据的合并

每天同步增量的表，并与前一天的全量数据进行合并。合并方式 full outer join 或者 union all 后根据更新时间戳做去重，去重时使用distribute by sortby 全局排序的方法。至于是哪个比较快。我觉得是union all排序的方式，因为join需要内循环。

4.数据漂移 如何避免或者处理

• 1.限制modify_time<=当日零点。这个是最普遍和方便用的方式。缺点：业务手工处理不更新或者恰好在0点以后更新数据字段时会拉取不到。
• 2.多取前后15分钟的数据，根据多个时间戳字段进行限制，取相对准确的时间。感觉这个很难，因为有些源系统没有记载那么多的时间。

5.数据清洗

• 1.非结构化数据尽量清洗为结构化数据，方便后续计算
• 2.根据业务需求，必要时处理缺失数据，比如将大量空值处理成0值或者其他值，避免因为空值引起数据倾斜。
• 3.处理脏数据，有时候在业务系统存储中会存在一些手工输入的数据，可能存在特殊字符、空格、引号等为后续计算留坑，在数据清洗层做必要的转换。
• 4.将ods层的字段命名为数仓规定的规范化命名。

二、没有规矩，不成方圆

1.数仓层级划分

• 1.数仓层级如何划分，为什么要这样划分；分为ods数据原始层，dwd：数据基础层和数据中间宽表层，dws层：轻度汇总数据层，ads层：数据应用层。
• 2.划分层级的优点：1>空间换时间,取数效率提高 2>、容易定位问题，可以做到上层无感知。3>可以回溯历史 4>安全性

2.主题域划分

• 1.数据仓库的作用之一是集成，为了方便上层分析，将关系较为密切的数据主题的集合划分为主题域。
• 2.具体主题域的划分由数仓开发人员、分析师同学、业务人员一起根据公司的业务和分析对象进行划分。主题域要尽可能包含存量业务需求，如有新业务无法包含时，需扩展主题域

3.表和字段的命名规范

规范化的命名对于构建一致性维度和抽取公共层数据等有着非常重要的作用。通常由数据架构师定义共有通用的名词。

• 表命名：数据层级_主题域_业务域_表名_更新周期+增量全量
• 字段命名：优先考虑公共通用的名词，如是业务特有的字段尽量从ods层开始命名保持一致，方便回溯问题
• 字段类型：与ods层业务数据保持一致，如果是小数避免丢失精度。

三、充分准备、避免试错

数据建模的思路往往决定了模型的质量，前期充分的调研和涉及往往会为后期开发的速度和质量打下坚实的基础。进行必要的文档输出和技术评审，合理的规避因为个人思路或者考虑不周等原因引起的试错。

1.模型方法论的选择

工欲善其事必先利其器，选择好的方法论是数仓建模的基础

• 1.ER模型：实体与关系图
• 2.datavalut 模型
• 3.anchor模型
• 4.维度模型：
  • 1.星形模型：反规范化数据，构建一致性维度，整合常用逻辑，减少关联，方便分析
  • 2.雪花模型：规范化数据，消除冗余，节省空间
    因为发展中企业的业务飞速发展和变更，所以我们选择维度模型方便分析。

2. 充分的理解业务和业务数据结构调研。

• 1.充分理解业务并梳理业务过程和涉及到的维度以及根据需求的优先级判断优先开发项
• 2.调研业务数据结构，将指标需求涉及的表最好详细到字段是否可取以及取数路径列出来
• 3.考虑是否是公用表，是否需要沉淀到公共层

3.确定一致性维度和业务过程，构建总线矩阵

-1. 将2中确定好的维度，与业务过程进行梳理后构建总线矩阵，为后续事实表中添加维度属性打好基础
-2.软件设计遵从高内聚低耦合的设计思想，联系数据结构探查预判是否有较多不同业务的私有维度属性。例如：订单中心的订单包含各个系统的所有共有属性，当我们接到需求调研得知主数据来自订单中心，其他数据取自业务系统，这个时候可以将总线矩阵的维度属性和业务过程区分为属性和私有属性，将订单中心表作为基础模型，构建共有属性，在订单中心表的基础上抽取需求涉及业务的订单作为面向某个业务的订单模型，加入私有属性。避免过度耦合，模型混乱。

4.模型设计

<1>.维表设计

• 1.维度设计

  • i. 通过需求分析选择维度或者新建维度。维度在数据仓库中必须保持唯一性

  • ii.确定主维表，此处的主维表一般是从ods直接同步的表

  • iii.确定相关维表

  • iv.确定维度属性

• 2.注意事项
  1.尽可能的生成丰富的属性。尽可能的多给出一些富有意义的文字性描述。区分数值型属性和事实。尽量沉淀出通用的维度属性。
  2.因为维度有层次属性，比如店铺的省市区，所以报表支持向下钻取。
  3.在loap系统中，数据基本是反规范化数据。将维度的属性层次合并到单个维表中称之为范规范化数据。

• 3.一致性维度与交叉探查。

  将同一维度的不同域的度量放在一起进行数据探查称之为交叉探查。比如：需要知道某个店铺的日活以及该店铺的gmv涉及到店铺维度的日志域探查和交易域探查 称之为交叉探查

• 4、维度整合
  1.垂直整合：将同一来源包含不同数据集的数据整合到一起。 eg:商品sku主表之外还有extend表、model表等其他扩展表，我们在做整合的时候需要考虑垂直整合。

  2.垂直拆分：将某些热度较高、产出较早较稳定易用的属性统一在主维度中中，将热度较低产出时间较晚的数据放在扩展维度中。假设主维度是在每日1：00产出，扩展维度是在每日3：00产出，主从维度表的涉及极大的提前了下游数据产出的时间。

  3.水平整合与拆分： 将个业务之间通用的维度水平进行整合，比如弹个车客户和新零售客户。

• 5.缓慢变化维的处理：

  • 1>.直接覆盖。如果不需要回溯历史属性，则直接覆盖
  • 2>.插入新的维度行，在事实表中使用心的维度行进行关联。
  • 3>.插入新的维度列。
  • 4>.历史拉链表:

  注意
  1.这里历史拉链如何存储分区。有些资料上说以end_date 和start_date 作为分区，事实操作 起来非常困难，一方面一旦查询需要全表扫描跨分区查询查询效率低，另一方面维护困难，如果某一日数据错误，比较难更正，所以存分区是有必要的。
  2.考虑到极限存储的一个的作用是节省空间，每日存分区就失去了历史拉链的意义，同时为了避免全表扫描，可以考虑按月分区，即每个月存储一份当时的全量历史拉链。并合理进行归并。大数据之路还建议了另一种存储方案，每个月存end_date=当月的数据，感觉这样也避免不了全表扫描带来的性能问题，不过也可以考虑。

• 6、递归层次

  • 1.层次结构扁平化
    类似商品类目表，父节点有子节点，依次类推，称之为递归节点。这种情况下可以考虑层次结构扁平化。但是前提是有直达层次的深度。
  • 2.桥接表
    记录所有父节点与子节点的关系以及层间之间的间隔。但是桥接表不容易维护，也不直观，还会因为类目多对一的问题引起数据计算问题，所以具体需求具体讨论，一般情况下使用层次结构扁平化

<2> 事实表设计

• 1.事实表的设计原则

  • 尽可能包含所有业务过程相关的事实，可以选择一个或多个业务过程。
  • 只选择与业务过程相关的事实
  • 分解不可加性事实为可加的组件：eg:比如订单转化率需要拆分为：创建订单量、完成订单量。
  • 在选择维度和事实之前必须先声明粒度
  • 在同一个事实表中不能有多种不同粒度的度量。比如订单金额与明细订单金额不可放在同一个订单事实表中。
  • 事实的单位要保持一致
  • 对事实的null值要处理。建议填充为0.
  • 使用退化维提高事实表的易用性。

• 2、事实表设计的步骤

  • 选定业务过程
  • 声明粒度
  • 确定维度
  • 确定事实
  • 冗余维度

• 3、事实表类型与设计方案
  1>事物事实表

  • 单事物事实表：每个业务过程一张事实表
  • 多事物事实表：多个业务过程合并为一张事实表
  • 在多个事实粒度/维度一致时可以选择多事物事实表。事物粒度不一致、维度差异较大时可以选择单事物事实表。

  2>周期快照事实表,一般来说是汇总表
  每个周期一条记录，比如日，月，年的时间周期内的gmv是多少。确定时间周期
  3> 累计快照事实表，汇总表
  没有时间周期，历史到当前的下单金额是多少。
  累计快照表的物理实现
  1.每天的分区都存储全量数据
  2.如果可以预测业务过程的最大生命周期，每个分区存储最大生命周期的数据
  3.按照业务实体的结束时间作为分区。

  4>无事实的事实表
  如日志表，埋点表，或者记录维度与维度之间多对多的关系

  5>聚集性事实表
  将使用较为频繁的指标进行沉淀称为公共汇总层
  1.确定聚集维度 2.确定一致性上钻 3.确定聚集事实

5.技术方案评审

• 输出模型设计文档，文档内容可以包括：需求、源系统数据情况、数据同步方式、数据存储方式、数据模型结构与源系统数据映射、数据测试方案以及不确定性和疑难点。
• 组织相关人员召开技术方案评审。

四.ETL开发

1.数据存储格式

• 如果拥有参数化的分隔符可以选择textfile
• 如果文件大小小于默认的文件块大小（128M）则可以选择sequencefile格式
• 如果想执行数据分析并高效的存储数据，可以选择rcfile
• 如果希望减少数据分析所需的存储空间并提升性能，则可以选择orcfile格式

2.优化

1.判断数据量，数据量小查询较慢，可以考虑是否因为小文件过多，可以考虑将文件数合并成一个。
2.单表查询慢，比如上层报表慢

• 如果是小表，数据量较小但是还是慢，大概率是跨分区查询，或者小文件过多引起的。
  方案：1.如果小文件过多，引起启动的map数过多，则合并小文件，尽量保持256m每一个数据块，如果没有小文件是跨分区查询，可以考虑预先在应用层做一个无分区表，将需要查询的分区插入这个表，会提升报表查询速度。
  2.有可能是其他大数据量的查询占了内存，导致小数据查询出不来，这个可以设置sql超时时间，超过查询时间就断开。
  合并小文件方法：spark：在sql 末尾加上distribute by rand() 或者select查询加/+ REPARTITION(1) /
  hive：set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件
  set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件
  set hive.merge.size.per.task = 25610001000 ##合并文件的大小
  set mapred.max.split.size=256000000; ##每个 Map 最大分割大小
  set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值
  set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行 Map 前进行小文件合并
• 如果是大表：
  1.尽可能裁剪分区和列，只查询自己需要的结果集，必要时加limit
  2.合并小文件，尽量保持一个数据块256M，减少map的个数
  3.过滤空值或者给空值赋随机值
  4.如果有热点值引起长尾，可以考虑将热点值和非热点值分开查询。
  5.如果一段语句有多个count(distinct )操作会导致数据分发多次，引起查询慢，可以考虑先group by再count 最后关联起来的方式

2.join查询慢
1.join会有内循环的时间，一般优化方案是减少内循环次数，将小表作为驱动表，一次性载入内存中。
2.尽量使用相同的key值连接,链接的key类型保持一致
3.可以在进入查询前预先聚合减少数据量
4.用disrtibute by sort by 代替order by
5.shuffle阶段会网络传输，如果是小表关联大表情况并且数据量不大，内存充足时可以考虑使用map join 舍去shuffle阶段。
6.如果有热点值或者空值引起长尾，可以热点值单独处理，或者给空值赋予关联值之外的随机值。
7.group by代替distinct
8.空值较多的字段作关联条件时,需要使用函数rand()进行打散,比如 when key is null then concat(‘rand-‘,rand()) else key end

3.join的原理

• NLJ 循环嵌套算法：循环外层表是驱动表，循环内层表是被驱动表，驱动表驱动被驱动表进行连接，从第一条记录去逐一去匹配与驱动表记录匹配的记录，生成结果集，循环匹配直到驱动表记录处理完毕

DISTRIBUTE BY HASH(product_id)是一种数据分布方式，用于将数据根据哈希算法分散到多个节点上。具体地，该语句会根据product_id列的数值对数据进行哈希运算，并将哈希值作为分区的依据，将不同的哈希值分配到不同的节点上。这样，相同哈希值的数据就可以被分配到同一个节点上，从而实现了数据的分布式存储。

哈希算法是一种通过对数据进行哈希运算，将数据映射为一个固定长度的哈希值的算法。哈希值具有不可逆性、唯一性和均匀性等特点，因此可以被用来作为数据分布的依据。在数据仓库中，如果某个表的某个列的值分布不均衡，可以通过哈希分区的方式将数据均匀地分散到多个节点上，从而提高系统的负载均衡和查询性能。

总之，DISTRIBUTE BY HASH()语句是数据仓库中用来实现数据分布式存储的一种方法，通过哈希算法将数据分配到不同的节点上，从而实现数据的分散存储。

SELECT o.order_no, o.sales_quantity, o.sales_amount, p.product_name
FROM sales_order_fact o
JOIN (
  SELECT product_id, product_name, ...
  FROM product_dim
  DISTRIBUTE BY HASH(product_id)
) p ON o.product_id = p.product_id
WHERE p.product_id = 'HotProduct'
  AND date BETWEEN '2021-01-01' AND '2021-01-31';
在上述SQL中，通过JOIN操作将订单事实表和产品维度表进行关联。在产品维度表中，通过SELECT子句对产品ID进行哈希分区，并将分区后的数据存储在不同的节点上。在订单事实表中，通过WHERE子句筛选出需要查询的热点产品，然后通过相同的哈希算法将产品ID映射到对应的产品维度表分区中，从而避免了数据倾斜问题。

foreach row1 from t1
        foreach row2 from t2
            if row2 match row1 //row2与row1匹配，满足连接条件
                join row1 and row2 into result //连接row1和row2加入结果集

• BNLJ 块嵌套算法：是NLJ 算法的优化算法。驱动表一次加载多条数据，被驱动表每次记录拿出来以后跟驱动表加载的多条数据去对比。优化思路就是减少内循环次数提高表连接效率。驱动表加载多条到缓冲区，从而也减少了io吞吐。
• 优化思路：
  • 1.减少内循环次数：小表m驱动大表n，假设小表只需要加载一次，那大表需要加载<=n次。大表驱动小表，假设大表加载一次，小表需要加载n次。结论：小表驱动大表性能较好； 如果使用BNLJ算法可以通过设置合理的缓存区减少驱动表加载次数
  • 2.如果join并且要排序，用驱动表的key进行排序，这样的话会先排序再去join，比join后的结果集排序要快
  • 3.如果是mysql排序，给被驱动表加上索引，会更快。

五、实用性保障

1.数据测试

数据质量保障的第一环节就是上线前进行大量的数据测试。根据在方案评审阶段输出的数据测试文档以及后期开发环节补充到的点进行数据测试，一般来说数据测试需要注意到的点有：

• 1.完整性比对：数据条数是否与底层表或者计算逻辑保持一致。不出现重复数据或者数据缺失的情况。
• 2.精确性比对：度量值与实际逻辑或者底层表保持一致。在前期调研和技术方案评审时可以着重对业务要求的度量值和属性值字段输出测试方案，开发完成后对这些字段单独测试。
• 3.产出及时性：上线前观察脚本在无障碍情况下的平均产出时间是否满足业务要求输出的时间
• 4.测试方案留档：在后期发生变更时，进行必要的测试。

2.上线后数据质量保障

分层级保障。

3.元数据

六、 hadoop组件

• hadoop基础功能
  • hdfs：一个分布式文件系统
  • yarn一个作业调度和资源管理框架
  • MapReduce：一个基于yarn的大数据计算框架
• hadoop生态圈内的其他组件
  • 计算框架：spark、flink
  • 数据库：hive、hbase
  • 查询工具：hue zeppline
  • bi分析工具：metabase、达芬奇、tablu
  • 其他：sqoop datax

七、mapreduce原理

转自链接

• 输入分片：在进行Map计算之前，MapReduce会根据输入文件计算输入分片。每个输入分片对应一个Map任务，输入分片存储的并非数据本身，而是一个分片长度和一个记录数据的位置的数组。输入分片往往和HDFS和block（块）
• map阶段：mapinstance读取文件块，分别调用map函数，输出键值对。
• combiner阶段（可配置）：对于每个map的键值对进行合并，比如map1的键值对为 a:1 b:1 a:1 c:1 则在本map任务内执行combiner时合并为a:2 b:1 c:1 （Combiner使用一定得慎重，适用于输入key/value和输出key/value类型完全一致，而且不影响最终的结果）
• shuffle 阶段：一般理解为数据从Map Task输出到ReduceTask输入的过程，它决定了Map Task的输出如何且高效第传输给Reduce Task
  • map阶段的shuffle：写入之前先进行分区，将分完区的结果<key,value,partition>开始序列化成字节数组，开始写入缓冲区。缓冲区的默认大小是100M,当缓冲区大小达到阀值（默认是80%），开始启动溢写线程，每次溢写都会生成小文件，最终一个map任务的所有小文件合并为一个文件
  • Reduce-Shuffle ：当MapTask完成任务数超过总数的5%后，开始调度执行ReduceTask任务，然后ReduceTask默认启动5个copy线程到完成的MapTask任务节点上分别copy一份属于自己的数据（使用Http的方式）。这些拷贝的数据会首先保存到内存缓冲区中，当达到一定的阀值的时候，开始启动内存到磁盘的Merge，也就是溢写过程，一致运行直到map端没有数据生成，最后启动磁盘到磁盘的Merge方式生成最终的那个文件。在溢写过程中，然后锁定80M的数据，然后在延续Sort过程，然后记性group(分组）将相同的key放到一个集合中，然后在进行Merge然后就开始reduceTask就会将这个文件交给reduced（）方法进行处理，执行相应的业务逻辑
• reduce阶段：
  经过Map和Reduce阶段的Shuffle过程后，Reduce任务的输入的准备完毕，相关的数据已经被合并和汇总，Reduce任务只需要调用Reduce函数即可，对于本例即对每个键，调用sum逻辑合并value并输出到HDFS即可

分区和分桶的区别：转自

从表现形式上：
分区表是一个目录，分桶表是文件

从创建语句上：
分区表使用partitioned by 子句指定，指定字段为伪列，需要指定字段类型
分桶表由clustered by 子句指定，指定字段为真实字段，需要指定桶的个数

从数量上：
分区表的分区个数可以增长，分桶表一旦指定，不能再增长

从作用上：
分区避免全表扫描，根据分区列查询指定目录提高查询速度
分桶保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列）。
分桶表数据进行抽样和JOIN时可以提高MR程序效率

mr的二次排序详解

mr的二次排序 写的很详细很好

hadoop生态圈