2020大厂面试题-数仓篇

1、手写sql问题：连续活跃。。。

-- 第一种解决方案，使用lag(向前)或者lead(向后)
select
  *
from 
(
  select 
    user_id,
    date_id,
    lead(date_id) over(partition by user_id order by date_id) as last_date_id
  from 
  (
    select 
      user_id,
      date_id
    from wedw_dw.log_beacon_pv_base_info_da
    where date_id>='2020-08-10'
      and user_id is not null  
      and length(user_id)>0
    group by user_id,date_id
    order by user_id,date_id
  )t
)t1
where datediff(last_date_id,date_id)=1
-- 第二种解决方案,使用row_number
select 
  user_id,
  min(date_id),
  max(date_id),
  count(1)
from 
(
  select
  t1.user_id
  ,t1.date_id
  ,date_sub(t1.date_id,rn) as dis
  from 
  (
    select 
      user_id,
      date_id,
      row_number() over(partition by user_id order by date_id asc) rn
    from 
    (
      select 
        user_id,
        date_id
      from wedw_dw.log_beacon_pv_base_info_da
      where date_id>='2020-08-10'
        and user_id is not null  
        and length(user_id)>0
      group by user_id,date_id
      order by user_id,date_id
    )t
  )t1
)t2
group by user_id,his 
having count(1)>2

2、left semi join和left jion区别；

left semi join 左半连接 ：

in(keySet)，相当于在右表中查询左表的key， left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过；当右表不存在的时候，左表数据不会显示; 相当于SQL的in语句，比如测试的语句相当于“select * from table1 where table1.student_no in (table2.student_no)”，注意，结果中是没有B表的字段的

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。
Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

left join:

当右表不存在的时候，则会显示NULL，

select * from table1 left semi join table2 on(table1.student_no=table2.student_no);
结果：
1 name1
2 name2
3 name3
4 name4
5 name5

select * from table1 left outer join table2 on(table1.student_no=table2.student_no);
结果：

1 name1 1 11
1 name1 1 12
1 name1 1 13
2 name2 2 11
2 name2 2 14
3 name3 3 15
3 name3 3 12
4 name4 4 13
4 name4 4 12
5 name5 5 14
5 name5 5 16
6 name6 NULL NULL

3、维度建模和范式建模的区别；

模型设计的流程：

1. 概念模型:将业务划分几个主题
2. 逻辑模型：定义各种实体、属性和关系
3. 物理模型：设计数据对象的物理实现，比如表字段类型、命名等

范式建模

目的：降低数据冗余，保障数据一致性；

缺点：获取数据关联逻辑复杂

范式类型：

1. 原子性，即数据不可分割
2. 基于1NF的基础上，实体属性完全依赖于主键，不能存在仅依赖主关键字一部分属性。即不能存在部分依赖
3. 在2NF的基础上，任何非主属性不依赖于其他非主属性。即消除传递依赖

Inmon提出的模型使用了范式建模，且周期较长， 主要应用于OLTP系统中 ；

维度建模

维度建模是面向分析场景；重点关注快速，灵活，能够提供大规模数据快速响应

模型类型:

1. 星型模型： 由一个事实表和一组维度表组成，每个维表都有一个维度作为主键，事实表居中，多个维表呈辐射状分布于四周，并与事实表连接，形成一个星型结构
2. 雪花模型： 在星型模型的基础上，基于范式理论进一步层次化，将某些维表扩展成事实表，最终形成雪花状结构
3. 星系模型： 基于多个事实表 ， 共享一些维度表

4、埋点的码表如何设计；

5、数据倾斜；

6、group by为什么要排序；

7、集市层和公共层的区别；

8、缓慢变化维的处理方式；

10、说说印象最深的一次优化场景，hive常见的优化思路；

11、 数据质量，元数据管理，指标体系建设，数据驱动

12、Hive调优

13、 如何保证数据质量；

14、 如何保证指标一致性

15、 数据漂移如何解决

数据漂移的概念： 通常是指ODS表的同一个业务日期数据中包含前一天或后一天凌晨附近的数据或者丢失当天变更数据

解决方案：

1、多获取后一天的数据
2、通过多个时间戳字段限制时间来获取相对准确的数据

<<大数据之路>>中对该类问题的定义解决为:

通常，时间戳字段分为四类：

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

理论上这几个时间应该是一致的，但往往会出现差异，造成的原因可能为：

1. 数据抽取需要一定的时间，extract_time往往晚于前三个时间
2. 业务系统手动改动数据并未更新modfied_time
3. 网络或系统压力问题，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. 多获取前，后一天的数据，保障数据只多不少。

2. 通过多个时间戳字段限制时间

   2.1 首先通过log_time多同步前一天最后15分钟和后一天凌晨开始15分钟的数据，然后用modified_time过滤非当天的数据，这样确保数据不会因为系统问题被遗漏

   2.2 然后根据log_time获取后一天15分钟的数据，基于这部分数据，按照主键根据log_time做升序排序，那么第一条数据也就是最接近当天记录变化的

   2.3 最后将前两步的数据做全外连接，通过限制业务时间proc_time来获取想要的数据

16、 拉链表如何设计，拉链表出现数据回滚的需求怎么解决

拉链表使用的场景：

1. 数据量大，且表中部分字段会更新，比如用户地址、产品描述信息、订单状态等等
2. 需要查看某一个时间段的历史快照信息
3. 变化比例和频率不是很大

--拉链表实现
--原始数据
CREATE TABLE wedw_tmp.tmp_orders (
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING
) stored AS textfile;

--拉链表
CREATE TABLE wedw_tmp.tmp_orders_dz(
    orderid int,
    createtime STRING,
    modifiedtime STRING,
    status STRING,
    link_start_date string,
    link_end_date string
) stored AS textfile;

--更新表
CREATE TABLE wedw_tmp.tmp_orders_update(
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING
) stored AS textfile;


--插入原始数据
insert overwrite table  wedw_tmp.tmp_orders
select 1,"2015-08-18","2015-08-18","创建"
union all 
select 2,"2015-08-18","2015-08-18","创建"
union all
select 3,"2015-08-19","2015-08-21","支付"
union all
select 4,"2015-08-19","2015-08-21","完成"
union all 
select 5,"2015-08-19","2015-08-20","支付"
union all 
select 6,"2015-08-20","2015-08-20","创建"
union all 
select 7,"2015-08-20","2015-08-21","支付"

--拉链表初始化
insert into  wedw_tmp.tmp_orders_dz
select *,createtime,'9999-12-31' from  wedw_tmp.tmp_orders 

--增量数据
insert into  wedw_tmp.tmp_orders_update
select 3,"2015-08-19","2015-08-21","支付"
union all 
select 4,"2015-08-19","2015-08-21","完成"
union all 
select 7,"2015-08-20","2015-08-21","支付"
union all 
select 8,"2015-08-21","2015-08-21","创建" 

--更新拉链表
insert overwrite table  wedw_tmp.tmp_orders_dz
select 
    t1.orderid,
    t1.createtime,
    t1.modifiedtime,
    t1.status,
    t1.link_start_date,
    case when t1.link_end_date='9999-12-31' and t2.orderid is not null then '2015-08-20'
     else t1.link_end_date
    end as link_end_date
from  wedw_tmp.tmp_orders_dz t1
left join wedw_tmp.tmp_orders_update t2
on t1.orderid = t2.orderid
union all 
select 
  orderid,
  createtime,
  modifiedtime,
  status,
  '2015-08-21' as link_start_date,
  '9999-12-31' as link_end_date
from wedw_tmp.tmp_orders_update

--拉链表回滚，比如在插入2015-08-22的数据后，回滚2015-08-21的数据，使拉链表与2015-08-20的一致，具体操作过程如下

select 
  orderid,
  createtime,
  modifiedtime,
  status,
  link_start_date,
  link_end_date
from wedw_tmp.tmp_orders_dz
where link_end_date<'2015-08-20'

union all 
select 
  orderid,
  createtime,
  modifiedtime,
  status,
  link_start_date,
  '9999-12-31'
from wedw_tmp.tmp_orders_dz
where link_end_date='2015-08-20'

union all 
select 
  orderid,
  createtime,
  modifiedtime,
  status,
  link_start_date,
  '9999-12-31'
from wedw_tmp.tmp_orders_dz
where link_start_date<'2020-08-21' and  link_end_date>='2015-08-21'

17、 交叉维度的解决方案？

18、 sql里面on和where有区别吗

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表

以 LEFT JOIN 为例：在使用 LEFT JOIN 时，ON 和 WHERE 过滤条件的区别如下：

on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录

where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

19、 公共层和数据集市层的区别和特点

公共维度模型层（CDM）：存放明细事实数据、维表数据及公共指 标汇总数据 ， 其中明细事实数据、维表数据一般根据 ODS 层数据加工 生成 ：公共指标汇总数据一般根据维表数据和明细事实数据加工生成。

CDM 层又细分为 DWD 层和 DWS 层，分别是明细数据层和汇总数 据层，采用维度模型方法作为理论基础 ，更多地采用一些维度退化手法， 将维度退化至事实表中，减少事实表和维表的关联 ，提高明细数据表的易用性 ：同时在汇总数据层， 加强指标的维度退化， 采取更多的宽表化 手段构建公共指标数据层，提升公共指标的复用性，减少重复加工。其主要功能如下。

1. 组合相关和相似数据：采用明细宽表，复用关联计算，减少数据 扫描。
2. 公共指标统一加工：基于 OneData 体系构建命名规范、口径一致 和算法统一的统计指标，为上层数据产品、应用和服务提供公共 指标 建立逻辑汇总宽表
3. 建立一致性维度：建立一致的数据分析维表，降低数据计算口径、 算法不统一的风险。 应用数据层（ ADS）：存放数据产品个性化的统计指标数据，根据 CDM 层与 ODS 层加工生成。

数据集市(Data Mart) ，也叫数据市场，数据集市就是满足特定的部门或者用户的需求，按照多维的方式进行存储，包括定义维度、需要计算的指标、维度的层次等，生成面向决策分析需求的数据立方体

20、 从原理上说一下mpp和mr的区别

原理的角度出发,map reduce其实就是二分查找的一个逆过程,不过因为计算节点有限,所以map和reduce前都预先有一个分区的步骤.

二分查找要求数据是排序好的,所以Map Reduce之间会有一个shuffle的过程对Map的结果排序. Reduce的输入是排好序的

MPP 为并行数据库 ，它的思路简单粗暴,把数据分块,交给不同节点储存, 查询的时候各块的节点有独立的计算资源分别处理,然后汇总到一个leader node(又叫control node),具体的优化和传统的关系型数据库很相似,涉及到了索引,统计信息等概念. MPP有shared everything /Disk / Nothing之别.

Shared Everything:一般是针对单个主机，完全透明共享CPU/MEMORY/IO，并行处理能力是最差的，典型的代表SQLServer

Shared Disk：各个处理单元使用自己的私有 CPU和Memory，共享磁盘系统。典型的代表Oracle Rac，它是数据共享，可通过增加节点来提高并行处理的能力，扩展能力较好。其类似于SMP（对称多处理）模式，但是当存储器接口达到饱和的时候，增加节点并不能获得更高的性能 。

Shared Nothing：各个处理单元都有自己私有的CPU/内存/硬盘等，不存在共享资源，类似于MPP（大规模并行处理）模式，各处理单元之间通过协议通信，并行处理和扩展能力更好。典型代表DB2 DPF和hadoop ，各节点相互独立，各自处理自己的数据，处理后的结果可能向上层汇总或在节点间流转。

MR 或者 Spark胜过MPP的地方在于非结构化的数据处理上

区别：

1. 底层数据库： MPP跑的是SQL,而Hadoop底层处理是MapReduce程序
2. 扩展程度： MPP虽然是宣称可以横向扩展Scale OUT，但是这种扩展一般是扩展到100左右，而Hadoop一般可以扩展1000+ ；因为MPP始终还是DB,一定要考虑到C(Consistency),其次考虑A(Availability),最后才在可能的情况下尽量做好P(Partition-tolerance)。而Hadoop就是为了并行处理和存储设计的，所以数据都是以文件存储，所以有限考虑的是P,然后是A,最后再考虑C.所以后者的可靠型当然好于前者

本质mpp还是数据库，需要优先考虑C(数据一致性)，而mr首先考虑的是P(分区容错性)

21、 Kimball和Inmon的相同和不同

概念性描述

Inmon模型

流程： 自顶向下， 即从分散异构的数据源 -> 数据仓库 -> 数据集市 ；是一种瀑布流开发方法。模型偏向于3NF

数据源往往是异构的，比如爬虫；数据源是根据最终目标自行定制的。

这里主要的处理工作集中在对异构数据进行清洗，否则无法从stage层直接输出到dm层，必须先通过etl将数据进行清洗后放入dw层。

Inmon模式下，不强调事实表和维度表的概念，因为数据源变化可能性较大，更加强调的是数据的清洗工作，从中抽取实体-关系

Inmon是以数据源头为导向，具体流程如下：

1. 首先探索获取尽量符合预期的数据，尝试将数据按照预期划分不同的表需求
2. 明确数据清洗规则后将各个任务通过etl由stage层转化到dm层，这里dm层通常涉及到较多的UDF开发，将数据抽象为实体-关系模型
3. 完成dm数据治理后，可以将数据输出到数据集市中做基本数据组合，最后输出到BI系统辅助具体业务
   

Kimball模型

流程：自下向上， 即从数据集市-> 数据仓库 -> 分散异构的数据源 ，相当于是以最终任务为导向的；模型使用星型、雪花

1. 首先得到数据后需要先做数据的探索，尝试将数据按照目标拆分出不同的表需求。
2. 明确数据依赖后将各个任务再通过etl由stage层转化到DM层。DM层由若干事实表和维度表组成
3. 完成DM层的事实表和维度表拆分后，数据集市一侧可以直接向BI环节输出数据

Kimball往往意味着快速交付，敏捷交付，不会对数仓架构做过多复杂的设计

特征对比

特性对比

特性	Kimball	Inmon
数据摄取	yes	yes
stage	yes	yes
etl	yes	yes
数据集市	yes	yes
商业需求	yes	yes
数据时间属性	yes	yes
数据仓库优先	no	yes
事实维度拆分	yes	no
关系表维护	no	yes
处理导向	yes	no
数据模型泛化	no	yes
精心设计	no	yes
缓慢变化维	yes	no
连续变化维	no	yes

优劣比较

特性	Kimball	Inmon
时间	快速交付	持续推进
开发难度	小	大
维护难度	大	小
技能要求	入门级	专家级
数据要求	特定业务	企业级

22、 数据质量管理、数据治理有什么好的方案？知识库管理有什么好的思路？血缘关系图。

1. 数据治理

为何难以推动

治理工作涉及众多的职能线与部门，角色不同，立场不同，治理投入度也不同 ; 即使集群资源达到一定规模，不得不治理时，各组织仍会以开发业务为核心，治理工作对他们来说优先级并不高，这也直接影响着治理效果。

如何推动开展？

治理工作需要从组织保障和治理工具两方面协同推进。公司的支持至关重要，有助于建设统一的数据文化，推进成立数据治理委员会，明确各组织的职责，制定治理制度、标准和流程等，以专职的治理团队负责治理工具建设和整体运营推进。

大数据集群资源治理聚焦于计算资源和存储资源的缩容，在保障平台性能和稳定性的同时，还需要考量数据资产管理的赋能。

治理工作需要结合公司现状，优先治理紧急的、投入产出比高的治理项。

对于紧急的治理项，前期可以手动线下的方式治理。对于非紧急治理项，涉及部门和用户较厂，并且需要长期治理的，则采用线上工具辅助治理，以此来减少人力投入成本。

治理的四个步骤：线下手工治理、半工具化治理、工具化治理和自驱动治理

治理场景和解决方案

从计算资源、存储资源、性能和稳定性治理三方面来进行治理。

1.计算资源

1. 任务复算治理

   数仓建设过程中，往往存在着事实表和维度表多次关联，事实表和事实表多次关联的现象，造成数据重复计算。

   任务复算治理，通过对表与表关联的union,join，子查询复杂关联等语法进行解析，识别重复计算的任务以及读取的关联表数据，并以此推动公共模型建设，减少重复计算

2. 任务异常治理

   任务出错率是衡量任务是否需要治理的重要指标，出错率过高意味着这个任务是没有价值的，一般可以被清除。如果任务确实是需要使用的，则必须进行优化。

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMIC1zan-1599523474605)(D:\GitCode\learnNote\learnNote\面试真经\assets\任务出错率.jpg)]

   另外当任务的目标表在一个或多个调度周期内未更新，可以认定为该任务未产出数据，任务清除下线的可能性很大。

3. 任务削峰平谷治理

任务执行会有明显的忙闲时之分，大部分的忙时主要集中在凌晨0点至8点，其余时间段相对闲时，这就造成了忙时计算资源严重紧缺。

比如如果任务在早上8点跑完，其写入的目标表在中午12点才被读取，是否可以将该任务避开忙时执行。

4. 任务资源配置治理

   这里主要说一下sparkstreaming实时任务资源治理， “微批”处理一个个切分后的文件，往往会存在资源分配过多的现象，这很容易被识别 。通过缩减任务资源或多个任务合并成一个任务的方式来治理，可以提升资源利用率

5. 计算框架优化

   计算框架越来越多，也越来越成熟完善，选择适合自己的计算框架是关键。比如，由 Hive 任务迁移至 SparkSQL 任务、Storm 任务迁移至 Flink 任务，会带来性能上的明显提升。

   但是，在海量数据任务的前提下，任务迁移绝非易事，需要综合考虑迁移的方案以及涉及的成本和风险

2.存储资源

1. 生命周期

   根据表生命周期对表进行清理删除，是最常见有效的存储治理方式。为降低数据丢失风险，可以先对表进行 rename 或通过 ranger 禁止表读写权限（相当于逻辑删除），7 天观察期过后删除至回收站，回收站默认保留 3 天后进行最终删除。

   如果表的生命周期设置不合理（过长），也可以根据表的类型、业务情况进行稽核整改。

2. 数据压缩治理

   数据压缩治理是最简单有效的存储治理方式。数据压缩的好处显而易见，可以直接节省磁盘空间，提升磁盘利用率，并且加速网络传输

   但同时数据的压缩和解压，需要消耗计算资源。如果集群计算资源紧缺，并且数据经常被读，则建议根据实际场景选择合适的数据压缩方式

   在不同的存储格式和压缩算法下，简单查询、大宽表查询和复杂查询的执行表现均有差异，具体需结合实际场景选择使用。

3. 数据复存治理

   比较简单的方式是通过解析 Hive 任务、SparkSQL 任务的代码逻辑，分析代码中的读表、写表、条件、字段函数，识别读表和写表是否重复存储。

   另外，也可以通过表名、字段名的相似度进行识别，并结合某些周期产出数据，抽样进行相似度对比分析和识别。

   如果表数据出现重复存储，还需要根据链路血缘关系找出上游任务，对整个链路上的表及上游任务实施“一锅端”治理。

4. 数据价值治理

   梳理当前业务价值，从数据应用层（包括报表、指标、标签）源头分析投入产出比，对整体链路资源进行“从上至下”的价值治理。

   如果表长时间未作更新（如 32 天）或未被读取，往往表明这张表价值很低，甚至没有价值，则可对表进行清理删除，这时可以优先考虑治理大表、分区表、高成本表。

3.性能和稳定性

1. 小文件治理

   HDFS 虽然支持水平扩展，但是不适合大量小文件的存储。因为 NameNode 将文件系统的元数据存放在内存中，导致存储的文件数目受限于 NameNode 内存大小。当集群到了一定规模，NameNode 内存就会成为瓶颈。

   小文件治理需要根据当前集群的文件数量，定义合适的小文件大小，比如小于 1M。

   治理方式需要考虑从源头控制，在任务中配置文件合并参数，在 HDFS 存储之前进行小文件合并，但这又会延长任务执行时间。 所以，可选择在闲时进行周期性的小文件合并。另外，也可以设置小文件占比阈值，根据阈值触发小文件合并。

2. 数据倾斜治理

   很多时候，我们在用 Hive 或 Spark 任务取数，只是跑了一个简单的 join 语句，却跑了很长时间，往往会觉得这是集群资源不够导致的，但是很大情况下，是出现了“数据倾斜”的情况。

   数据倾斜，在 MapReduce 编程模型中十分常见，大量的相同 key 被 partition 分配到一个分区里，造成了“某些任务累死，还拖了后腿，其他任务闲死”的情况，这并不利于资源最大化的有效利用。

治理需要具备的能力

面向治理责任人、项目主管、公司领导以及治理运营人员，集团构建了统一的集群资源治理平台，全局把控集群计算资源，存储资源，性能和稳定性的整体情况，通过平台“识别通知，治理优化，监督考核”的支撑能力，实现一站式治理服务，降低治理投入的工作量，提升治理成效。

2.数据质量

3.知识库

4.血缘关系图

23、MOLAP ROLAP HOLAP的区别和联系

MOLAP：多维联机分析处理，预计算

ROLAP：关系型联机分析处理， 依赖于操作存储在关系型数据库中的数据 。 本质上，每个slicing或dicing功能和SQL语句中"WHERE"子句的功能是一样的。

HOLAP：混合型联机分析处理（指的是MOLAP和ROLAP的结合）