数据仓库工作问题总结

1. ODS 层采用什么压缩方式和存储格式？

压缩采用 Snappy ，存储采用 orc ，压缩比是 100g 数据压缩完 10g 左右。

2. DWD 层做了哪些事？

1.、数据清洗

• 空值去除
• 过滤核心字段无意义的数据，比如订单表中订单 id 为 null，支付表中支付id 为空
• 对手机号、身份证号等敏感数据脱敏
• 对业务数据传过来的表进行维度退化和降维。
• 将用户行为宽表和业务表进行数据一致性处理
  2、 清洗的手段
  Sql、mr、rdd、kettle、Python（项目中采用 sql 进行清除）

3.、DWS 层做了哪些事？

1.、DWS 层有 3-5 张宽表（处理 100-200 个指标 70%以上的需求）
具体宽表名称：用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车、宽表，物流宽表、登录注册、售后等。
2.、哪个宽表最宽？大概有多少个字段？
最宽的是用户行为宽表。大概有60-100 个字段

4、数据漂移如何解决?

1. 什么是数据漂移及情况
   通常是指ods表的同⼀个业务⽇期数据中包含了前⼀天或后⼀天凌晨附近的数据或者丢失当天变更的数据，这种现象就叫做漂移，
2. 解决数据漂移方法

两种方案：
1.、多获取一点后⼀天的数据，保障数据只多不少
2、第⼀种⽅案⾥，时间戳字段分为四类：

1. 数据库表中⽤来标识数据记录更新时间的时间戳字段（假设这类字段叫modified time ）。
2. 数据库⽇志中⽤来标识数据记录更新时间的时间戳字段·（假设这类宇段叫 log_time）。
3. 数据库表中⽤来记录具体业务过程发⽣时间的时间戳字段 （假设这类字段叫 proc_time）。
4. 标识数据记录被抽取到时间的时间戳字段（假设这类字段 extract time）。

理论上这⼏个时间应该是⼀致的，但往往会出现差异，造成的原因可能为：
5. 数据抽取需要⼀定的时间，extract_time 往往晚于前三个时间。
6. 业务系统⼿动改动数据并未更新 modfied_time。
7. ⽹络或系统压⼒问题，log_time 或 modified_time 晚于 proc_time。
通常都是根据以上的某⼏个字段来切分 ODS 表，这就产⽣了数据漂移。具体场
景如下：

1. 根据 extract_time 进⾏同步。
2. 根据 modified_time 进⾏限制同步， 在实际⽣产中这种情况最常⻅，但是往往会发⽣不更新 modified time⽽导致的数据遗漏，或者凌晨时间产⽣的数据记录漂移到后天 。由于⽹络或者系统压⼒问题， log_time 会晚proc_time ，从⽽导致凌晨时间产⽣的数据记录漂移到后⼀天。
3. 根据 proc_time 来限制，会违背 ods 和业务库保持⼀致的原则，因为仅仅根据 proc_time 来限制，会遗漏很多其他过程的变化。

第⼆种解决⽅案：

1. ⾸先通过 log_time多同步前⼀天最后 15 分钟和后⼀天凌晨开始 15 分钟的数据，然后⽤modified_time 过滤⾮当天的数据，这样确保数据不会因为系统问题被遗漏。
2. 然后根据 log_time 获取后⼀天 15 分钟的数据，基于这部分数据，按照主键根据 log_time 做升序排序，那么第⼀条数据也就是最接近当天记录变化的。
3. 最后将前两步的数据做全外连接，通过限制业务时间 proc_time 来获取想要的数据。

优化的场景
1、Hive 处理 100 亿+数据之性能优化
项目背景
有个关于bds项目，日增数据约6.5T，约100亿左右数据。通过清洗，输出不同5大维度，8 种粒度的依赖视图，以及相关的报表汇总统计。过程遇到了不少坑。在一些数据量大的场景下，很容易把一些潜在的问题就容易暴露出来，现总结如下
get_json_object()
描述：get_json_object 函数第一个参数填写 json 对象变量，第二个参数使用$表示json变量标识，然后用.或[]读取对象或数组；定义：该方法只能接受两个参数，如果想要查看多个值，只能多写几个示例：selectge{t}_{j}so{n}_{o}bject{(}^{\prime }{"name":"linda","server":"www.baidu.com"}^{\prime }{,}^{\prime }$.server’)
输出结果：
www.baidu.com
json_tuple()
定义：当使用 json_tuple 对象时，可以显著提高效率，一次获取多个对象并且可以被组
合使用
示例：
select
json_tuple(’{“name”:“linda”,“server”:“www.baidu.com”}’,‘server’,
‘name’)
输出结果：
linda www.baidu.com

通常，我们通过一下两种函数进行解析 get_json_object()或 json_tuple()。假如要从一张stg层表中将json字符串解析成对相应的字符串，假设有10个字段，那么get_json_object()方法相当于一条记录使用 10 次函数，而 json_tuple()方法只是使用了一次，进行了批量解析，这种方式明显更高（脑补下 JVM 的知识点）。另外，确认是否所有的字段都有必要解析？解析的字段越多，意味着序列化和反序列化，以及解析的工作量，这都是很消耗 CPU。而 CPU 核数越多，意味着并行处理的 task 的能力越强。通过这种优化思路，生产环境解析 json 这张表的时间从 4.5 小时缩减到 0.5 小时。
3.大表和大表 join
比如单表 100 亿，设计到父子关系，需要 join 自己，尽管通过 where 语句缩小了数据量，还是将近有 50 亿之间的 join，并在此之上进行汇总计算。我这里用了 3 中解决方案。
（1）临时表 ：创建临时表，将 join 结果方法临时表，再从临时表取数据计算，若失败重试（默认 3 次）再从临时表取数据，跑完数据，删除临时表。
（2）动态分区：默认是按天分区，可以根据 join 的关键 ID hash 到不同分区中去（如10 个）按分区 join，再合并结果。
（3）分桶： 跟动态分区有点像，根据 cluster(xxId)

基于 hive 的大数据量的优化处理实例
是关于大数据量的数据，具体为一个 1000 亿的轻度汇总数据，去关联一个 7 亿左右的
另一个轻度汇总数据。
主要遇到的问题有：
1，1000 亿的轻度汇总数据读写困难，耗费时间、资源大；
2，两表进行关联处理数据计算慢。
背景：（完全假设来说）
A 表是以用户、城市维度的出现次数数据，字段主要有 user_id、city_id，cnt
B 表是以用户、城市维度的出现次数标准数据，字段主要有 user_id、city_id，city_cnt
主要是求用户是否在某个省份达到出现次数标准的进度，比如说小a在石家庄出现2次，在保定出现 5 次。但是在石家庄出现的标准次数应该为 4 次，在保定出现的标准次数为 4 次。所以在河北出现的标准次数为8次，而小a达标的次数分别为2次和4次（超过标准按照标准统计），所以小a在河北出现的次数进度为67%。针对以上数据量，目前想到的方案有以下三种：
方案一：拆分数据、分散资源，建立多个计算 task
（1）由于目的是对用户进行统计，所以对 A、B 表以及目标表根据 user_id 首位数字设
置分区，将数据量拆分；首先需要对 user_id 首位数进行数据量查询，然后按照数据量的大小进行合理分配分区。
（2）根据分区建多个 task，跑入对应目标表的分区；
对相同分区下的用户数据进行数据计算，然后建立多个 task 进行跑数，分散资源。
方案二：维度进行高粒度汇总，减小数据量
（1）维护一张城市和省份的维度表，在 A 表的计算过程中进行聚合，直接输出用户到省份维度，省略用户对城市的维度（A 表)。这样的话，就获取不到 A 表，得不到用户对城市维度的明细数据，对与数仓建设和后期数据维护不友好，但是对于本次项目的统计确实性能得到很好的提升。如果效果不好的话可以再重复方案一在用户对省份维度上建立分区。
（2）再与其用户到省份维度的表进行关联。
方案三：维度直接汇总到最高用户维度，建立数组存储省份信息，最大粒度减小数据量。
（1）涉及到的底层表聚合到用户维度，其余对应的城市和次数字段做成数组进行存储。建立以 user_id 作为主键，其余字段作为数据 map 的表，最大限度的满足当前需求，但是不利于数仓基础表的维护，再做新需求利用到同样数据源还需要进行同样的操作
（2）解析数组，再进行关联，输入到目标表。
对所有汇总到用户维度的底层表进行解析数组，然后进行关联再计算。针对以上方案，如果既需要满足本需求，又有利于数仓建设的话，推荐方案一，如果是敏捷开发，满足此需求的话推荐方案三，对于数据集市建设，如果没必要细致到用户到城市维度的话，建议方案二，三种方案各有利弊，具体选择需要根据实际情况定夺。