数据仓库面试题整理（一）

最新推荐文章于 2024-05-24 15:21:14 发布

无语梦醒

最新推荐文章于 2024-05-24 15:21:14 发布

阅读量1.6k

点赞数

分类专栏：数据仓库 Hive学习

本文链接：https://blog.csdn.net/qq_32648939/article/details/118856816

版权

数据仓库同时被 2 个专栏收录

8 篇文章 1 订阅

订阅专栏

Hive学习

5 篇文章 0 订阅

订阅专栏

一、数据仓库基础

1.范式建模和维度建模

a.范式建模

ER建模是数仓之父Bill Inmon推崇的一种“自上而下”的模型设计方式，及无需考虑具体数据支持应用是哪些，而是从整个企业的数据入手，分析业务场景应该有什么样的数据，从而构建唯一的数据中心。

b.维度建模

维度建模是数仓专家Ralph Kimball推崇的一种“自下而上”的模型设计方式，及根据具体需求加载所需的数据，从业务实际需求入手进行数据建模。
如上所述，维度建模会将数据仓库的表划分为事实表（A与B等实际发生的业务流程）和维度表（单一主键下对实体的描述）两种，如交易行为表和用户维度表。
a)星型模型
    由一个事实表与一组维度表组成，维度表中存在该维度下所有数据，所以会存在重复，如城市维中的各区县所属的城市，实际上是会重复使用和定义的。建模流程上会以明确业务过程为起始，其次是明确粒度与维度，事实（按照顺序进行梳理），关系明确后进行数据建模。
b)雪花模型
    由一个事实表与一个或多个维度表组成，但是雪花模型是对星型模型的拓展，将星型模型中的维度表层次化，每个维度可关联多个子维度，以子维度的方式减少数据重复的问题。
c)星型模型与雪花模型的关联与区别
    关联：雪花模型在一定程度上是星型模型的拓展，将星型模型中的不满足规范化设计的维度表规范化。
    区别：雪花模型符合业务逻辑与范式设计，避免了数据冗余，但是由于关联表的增多会降低性能；星型模型将维度聚合，属于反范式设计，以存储空间换取较少的关联表带来的性能提升。

2.主题域划分

a.主题域和业务过程

通过拆解和归并业务过程可以明确主题域，对业务的核心理解会左右主题的定义。

b.主题域和主题

主题是一个抽象概念，是对企业的信息数据进行综合、归类汇总用以分析使用的数据的抽象，每一个主题对应一个宏观的分析领域。
主题域是联系较为紧密的数据主题的集合。在处理一个业务中，实际上会是多个主题的交叉，比如用户和客户交叉产生的订单域，可以根据业务的关注点，将这些数据主题划分到不同的主题域。

3.数据仓库分层优点

数仓分层优点：

1.解耦数据开发过程，专人专事，降低出问题的风险的同时方便问题定位。
2.用空间换时间，用多人多步操作换取使用数据的高效性。
3.数据流向规范，避免循环依赖的发生，也是方便问题定位的方式。
4.统计计算口径，减少重复开发。

4.事实表分类

5.缓慢变化维

解决办法：

1.新数据覆盖旧数据（较为粗暴，适用于不关注数据变化的维度）
2.保存多条记录，并添加字段加以区分
3.保存多个字段，不同字段保存不同值（适用于变化不超过两次的维度）
拉链表是一种为了保留历史数据，又节省空间而设计的数据存储模式，可以使用拉链表辅助解决

6.数据输出SLA保障

7.大表JOIN大表优化

a.间接map join

通过拆分数据间接完成map join（限制行或列，特殊数据处理）

b.负载均衡

二、Hive基础

1.HIVE SQL优化

 思想：
 1.尽量尽早地过滤数据，减少每个阶段的数据量
 2.尽量原子化操作，尽量避免一个SQL包含复杂逻辑, 可以使用中间表或CTE来完成复杂的逻辑
 3.小表要注意放在join的左边，否则会引起磁盘和内存的大量消耗
 4.如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%
 5.了解数据特点，如果有join ,group by操作的话，要注意是否会有数据倾斜
 6.控制Map和Reduce数量
 方式：
      1.列裁剪
      2.分区裁剪
      3.left semi join替换in，exists
      4.mapjoin
      5.小文件合并
      6.负载均衡

2.Hive Join类型

a.Reduce Join

 reduce join（shuffle join,common join）
 join输入为Map的输出，在Map端输出时用on关键字作为key，对其关联key进行hash后推送至不同的reduce以确保相同key在同一个reduce中（在该步骤中可以对输入key的来源进行打tag标记以标识数据来源）

b.Map Join

 map join即将小表放在内存中，那么Hive可以在map端执行一次连接操作。从而省略常规所需的reduce过程（在shuffle过程中会有极大的消耗，跨机器的数据传输量非常大）

3.Hive Map和Reduce个数

map数量：map的数量应该由输入的文件数量与大小决定
1）如果一个任务有很多小文件（远小于块默认大小128MB），则每个小文件都会被当做一个块，此时我们就应该减少map数量。 
2）如果一个任务的大小为接近128MB，但是字段信息数量很大，此时我们就应该增加map数量。
可以通过将文件进行拆分的方式增加map的数量。

reduce数量：reduce的数量由map的输出文件大小决定，不指定reduce个数的前提下，hive会计算出一个reduce的个数。
1）reduce个数调整方式
每个reduce任务处理的数据量：hive.exec.reducers.bytes.per.reducer（默认为1000^3=1G）
每个任务最大的reduce数：hive.exec.reducers.max（默认为999）
计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1)
调整reduce个数
2）reduce的个数在进行全局操作的时候只有一个reduce，并且无法被增加，如笛卡尔积，order by，没有group by的汇总操作。

4.Hive Map和Reduce的Shuffle过程

a.Map端Shuffle

1.写缓存:环形内存缓冲区执行溢出写
每个map任务对应的唤醒内存缓冲区和额定大小，如果缓冲内容达到阈值则创建溢出写文件
2.分区：partition
3.排序：sort
4.聚合：combiner
5.生成溢出写文件
6.合并：merge

b.Map端Shuffle

1.复制Map输出：copy
2.排序：sort
3.合并：merge

5.Hive JOIN,GROUPBY过程

1.JOIN

Map阶段
将关联字段作为Key，Value Table标记，并对Table进行标记
Shuffle阶段
该阶段会根据key进行sort排序，然后分配给不同的reduce去执行下一步操作
Reduce阶段
根据Shuffle出的不同key，可知会分配给两个Reduce，每个reduce会分遍历数据，将table标记不同的记录两两组合

2.GROUP BY

Map阶段
将分组字段作为Key，Value为该分组下的记录数量
Shuffle阶段
该阶段会根据key进行sort排序（一般是字典排序），然后分配给不同的reduce去执行下一步操作
Reduce阶段
根据Shuffle出的不同key分配

6.Hive SQL2MR

1.Antlr是一门解析语言，由其定义SQL的语法规则，完成SQL词法，语法解析，将SQL转化为抽象语法树（AST Tree）
2.遍历抽象语法树（AST Tree），抽象出查询的基本组成单元QueryBlock（QueryBlock包括三个部分：输入源，计算过程，输出，可以将QueryBlock简单理解为一个子查询），即将SQL进一步抽象与结构化，方便转化为MapReduce，将QueryBlock转换为Calcite的RelNode，进行Calcite优化（HIVE提供元数据，Calcite有默认的Rules）
3.遍历QueryBlock，翻译为执行操作树（Operator Tree，其基本操作符包括TableScanOperator，SelectOperator，FilterOperator，JoinOperator，GroupByOperator，ReduceSinkOperator）
4.逻辑层优化器进行执行操作树（Operator Tree）变换，合并不必要的ReduceSinkOperator，减少shuffle数据量
5.遍历OperatorTree，翻译为MapReduce任务
6.物理层优化器进行MapReduce任务的变换，生成最终的执行计划

7.HIVE 分区和分桶

1.分区

分区是表的部分列的集合，可以为频繁使用的数据建立分区，是对“分区列”（partition column）的值进行粗略划分的机制。
Hive中每个分区对应着表很多的子目录，将所有的数据按照分区列放入到不同的子目录中去。庞大的数据集可能需要耗费大量的时间去处理。
在许多场景下，可以通过分区的方法减少每一次扫描总数据量，这种做法可以显著地改善性能。
指定MapReduce任务在HDFS中指定的子目录下完成扫描的工作。

2.分桶

分桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。
在分区数量过于庞大以至于可能导致文件系统崩溃时，我们就需要使用分桶来解决。
分区中的数据可以被进一步拆分成桶，不同于分区对列直接进行拆分，桶往往使用列的哈希值对数据打散，并分发到各个不同的桶中从而完成数据的分桶过程，桶中的数据条数不一定相等。
哈希函数的选择依赖于桶操作所针对的列的数据类型。除了数据采样，桶操作也可以用来实现高效的Map端连接操作。

三、常见Hive SQL面试题

1.连续登陆

整体思路就是将不同的日期变为相同的日期便于对所谓的连续进行数据统计

1.原始数据构建方式与原貌如下：

//基础数据构建：
select
	stack(13,
	'A','2020-12-30'
	,'A','2020-12-31'
	,'A','2021-01-01'
	,'B','2021-01-05'
	,'B','2021-02-06'
	,'B','2021-02-07'
	,'B','2021-02-08'
	,'C','2021-02-10'
	,'C','2021-02-14'
	,'C','2021-02-16'
	,'D','2021-02-11'
	,'D','2021-02-12'
	,'D','2021-02-13'
) as (user_name,login_date)

基础数据样貌：

user_name	login_date
A	2020-12-30
A	2020-12-31
A	2021-01-01
B	2021-01-05
B	2021-02-06
B	2021-02-07
B	2021-02-08
C	2021-02-10
C	2021-02-14
C	2021-02-16
D	2021-02-11
D	2021-02-12
D	2021-02-13

2.初步处理如下：

SELECT 
	user_name
	,login_date
	, row_number() OVER (PARTITION BY user_name ORDER BY login_date) as sub_date
	, date_sub(login_date, row_number() OVER (PARTITION BY user_name ORDER BY login_date)) AS flag_date
FROM (
	select
		stack(13,
		'A','2020-12-30'
		,'A','2020-12-31'
		,'A','2021-01-01'
		,'B','2021-01-05'
		,'B','2021-02-06'
		,'B','2021-02-07'
		,'B','2021-02-08'
		,'C','2021-02-10'
		,'C','2021-02-14'
		,'C','2021-02-16'
		,'D','2021-02-11'
		,'D','2021-02-12'
		,'D','2021-02-13'
	) as (user_name,login_date)
) t

处理结果：

user_name	login_date	sub_date	flag_date
A	2020-12-30	1	2020-12-29
A	2020-12-31	2	2020-12-29
A	2021-01-01	3	2020-12-29
B	2021-01-05	1	2021-01-04
B	2021-02-06	2	2021-02-04
B	2021-02-07	3	2021-02-04
B	2021-02-08	4	2021-02-04
C	2021-02-10	1	2021-02-09
C	2021-02-14	2	2021-02-12
C	2021-02-16	3	2021-02-13
D	2021-02-11	1	2021-02-10
D	2021-02-12	2	2021-02-10
D	2021-02-13	3	2021-02-10

3.最终步骤：

SELECT 
	user_name
	, COUNT(*) AS continuous_days
FROM (
	SELECT 
		user_name
		,login_date
		, row_number() OVER (PARTITION BY user_name ORDER BY login_date) as sub_date
		, date_sub(login_date, row_number() OVER (PARTITION BY user_name ORDER BY login_date)) AS flag_date
	FROM (
		select
			stack(13,
			'A','2020-12-30'
			,'A','2020-12-31'
			,'A','2021-01-01'
			,'B','2021-01-05'
			,'B','2021-02-06'
			,'B','2021-02-07'
			,'B','2021-02-08'
			,'C','2021-02-10'
			,'C','2021-02-14'
			,'C','2021-02-16'
			,'D','2021-02-11'
			,'D','2021-02-12'
			,'D','2021-02-13'
		) as (user_name,login_date)
	) t
) t
GROUP BY user_name, flag_date
HAVING COUNT(*) >= 3

处理结果：

user_name	continuous_days
A	3
B	3
D	3

2.N日留存

整体思路就是通过自关联获取以左表为第一天的对应右表中的记录的条目数量

1.原始数据构建方式如下：

//基础数据构建：
select
	stack(15,
	'A','2020-12-01'
	,'B','2020-12-01'
	,'C','2020-12-01'
	,'D','2020-12-01'
	,'A','2020-12-02'
	,'B','2020-12-02'
	,'C','2020-12-02'
	,'B','2020-12-03'
	,'C','2021-02-03'
	,'E','2021-02-03'
	,'F','2021-02-03'
	,'D','2021-02-03'
	,'A','2021-02-04'
	,'B','2021-02-04'
	,'G','2021-02-05'
) as (uid,first_date)

2.处理如下：

SELECT t0.first_date AS day, datediff(t1.next_date, t0.first_date) AS gap
	, COUNT(DISTINCT t0.uid) AS users
FROM (
		select
			stack(15,
			'A','2020-12-01'
			,'B','2020-12-01'
			,'C','2020-12-01'
			,'D','2020-12-01'
			,'A','2020-12-02'
			,'B','2020-12-02'
			,'C','2020-12-02'
			,'B','2020-12-03'
			,'C','2021-02-03'
			,'E','2021-02-03'
			,'F','2021-02-03'
			,'D','2021-02-03'
			,'A','2021-02-04'
			,'B','2021-02-04'
			,'G','2021-02-05'
		) as (uid,first_date)
) t0
	LEFT JOIN (
		select
			stack(15,
			'A','2020-12-01'
			,'B','2020-12-01'
			,'C','2020-12-01'
			,'D','2020-12-01'
			,'A','2020-12-02'
			,'B','2020-12-02'
			,'C','2020-12-02'
			,'B','2020-12-03'
			,'C','2021-02-03'
			,'E','2021-02-03'
			,'F','2021-02-03'
			,'D','2021-02-03'
			,'A','2021-02-04'
			,'B','2021-02-04'
			,'G','2021-02-05'
		) as (uid,next_date)
	) t1
	ON t0.uid = t1.uid
WHERE t1.next_date >= t0.first_date
	AND datediff(t1.next_date, t0.first_date) < 15
GROUP BY t0.first_date, t1.next_date

处理结果：

day	gap	users
2020-12-01	0	4
2020-12-02	0	3
2020-12-03	0	1
2021-02-03	0	4
2021-02-04	0	2
2021-02-05	0	1
2020-12-01	1	3
2020-12-02	1	1
2020-12-01	2	1

3.共同好友

整体思路就是通过对共同好友进行整合考虑，反向思考
如下所述如果A，B，C作为本题的调查对象，那么定义他们为主角，第二列即为主角的朋友（通过lateral view后打散朋友们）
反向思考，那么朋友的共同主角就是拥有该朋友的主角群体
而后再把这个主角群体的对应朋友们聚合起来就是拥有共同好友的主角群体的共同好友了

1.原始数据构建方式如下：

//背景介绍：A有BCD三个好友，B有AC好友，C有ABD好友，统计共同好友个数
//基础数据构建：
select
	stack(3,
	'A','B,C,D'
	,'B','A,C'
	,'C','A,B,D'
) as (people,friends)

2.处理如下：

a.方式一

select
    people2
	,concat_ws(',',collect_list(friend)) as common_friends
from(
	--friends_tmp_01自连接之后过滤，得到“好友，用户1，用户2”
	select 
		a.friend
		,concat(a.people,'-',b.people) as people2
	from (
		SELECT
			people
			,friend
		FROM(
			select
				stack(3,
				'A','B,C,D'
				,'B','A,C'
				,'C','A,B,D'
			) as (people,friends)
		) t
		lateral view explode(split(friends,',')) tmp as friend
	) a
	inner join (
		SELECT
			people
			,friend
		FROM(
			select
				stack(3,
				'A','B,C,D'
				,'B','A,C'
				,'C','A,B,D'
			) as (people,friends)
		) t
		lateral view explode(split(friends,',')) tmp as friend
	) b
	on a.friend = b.friend
	where a.people < b.people
) x
group by people2
order by people2

a.方式二

select
	peoples
	,concat_ws(',',collect_set(friend)) as friends
	,length(concat_ws(',',collect_set(friend))) as friends_num
from(
	select
		friend
		,concat_ws(',',collect_set(people)) as peoples
	from(
			SELECT
				people
				,friend
			FROM(
				select
					stack(3,
					'A','B,C,D'
					,'B','A,C'
					,'C','A,B,D'
				) as (people,friends)
			) t
			lateral view explode(split(friends,',')) tmp as friend
	) t
	group by friend
) t
group by peoples

处理结果

people	friends	friends_num
B,A	C	1
C,B	A	1
C,A	B,D	3

4.中位数

整体思路

1.原始数据构建方式如下：
2.处理如下：

select
	user_class
	,avg(score) as middle_score
from(
	select
		*
		,count(1) over(partition by user_class) as cnt
		,row_number() over(partition by user_class order by score) as nk
	from(
		SELECT 
			stack(19
			,'A',9
			,'A',9
			,'A',9
			,'A',2
			,'A',2
			,'A',3
			,'A',9
			,'A',9
			,'A',9
			,'B',7
			,'B',7
			,'B',7
			,'B',7
			,'B',7
			,'B',7
			,'B',7
			,'B',7
			,'B',9
			,'B',10
		) as (user_class,score)
	) t
) t
where if(cnt%2=0,nk in(cnt/2,cnt/2+1),nk=(cnt+1)/2)
group by user_class

5.波峰波谷

整体思路

1.原始数据构建方式如下：
2.处理如下：

a.方式一

select
	stock_name,stats_date,stock_price
	,case when stock_price > lag_price and stock_price > lead_price then '波峰'
	      when stock_price < lag_price and stock_price < lead_price then '波谷'
	      else '其他' end as price_type
from(
	select
		stock_name,stats_date
		,stock_price,lag(stock_price,1) over(partition by stock_name order by stats_date) as lag_price
		,lead(stock_price,1) over(partition by stock_name order by stats_date) as lead_price
	from(
		select
		 stack(10
			  , 'A','20210621','13'
			  , 'A','20210622','11'
			  , 'A','20210623','17'
			  , 'A','20210624','12'
			  , 'A','20210625','14'
			  , 'B','20210621','16'
			  , 'B','20210622','12'
			  , 'B','20210623','12'
			  , 'B','20210624','17'
			  , 'B','20210625','13'
		) as (stock_name,stats_date,stock_price)
	) t
) t

无语梦醒

关注

0
点赞
踩
35

收藏

觉得还不错? 一键收藏
打赏
2
评论
数据仓库面试题整理（一）

面试题整理数据仓库基础范式建模和维度建模主题域划分数据仓库分层优点事实表分类缓慢变化维数据输出SLAHive基础Hive Join类型Hive Map和Reduce个数常见Hive SQL面试题连续登陆N日留存共同好友数据仓库基础范式建模和维度建模1.范式建模2.维度建模主题域划分1.主题域和业务过程2.主题域和主题数据仓库分层优点数仓分层优点：1.解耦数据开发过程，专人专事，降低出问题的风险的同时方便问题定位。2.用空间换时间，用多人多步操作换取使用数据的高效性。3.数据流向规范，
复制链接

扫一扫