最近感觉好长一段时间只是机械化重复的做着工作，自身没有什么太多的提高，有些基本的东西已经快忘了，所以现在整理一下知识体系，换一分新的工作，为之后的面试做好充足的准备。

数仓相关

基本概念

星型模型和雪花模型

星型模型：是由事实表和维度表共同组成，维度表均以事实表为中心呈放射状分布，它们通过数据库的主键和外键相互连接，整个图就像一个星星，故称之为星型模型。
雪花模型：在星型模型的基础上，对维度表进一步规范化为子维度表，这些子维度表并没有直接与事实表连接，而是通过其它维度表连接到事实表上，看起来就像一片雪花，顾称雪花型模型。
星型模型和雪花型模型的比较

	星型模型	雪花型模型
数据总量	多	少
查询速度	快	慢
可读性	容易	差
表个数	少	多
冗余度	高	低
扩展性	差	好

数据仓库和数据集市

数据仓库 是企业级的数据中心，从企业级各个系统获取数据，可以为整个企业各个部门提供数据以及决策支持。数据仓库来整合企业级的数据，然后分门别类提供给数据服务。
数据集市 是某个部门内部使用的数据集，可以称之为部门级的数据仓库。数据集市的数据可以直接从各个系统获取，也可以从企业级的数据仓库中获取，如果企业级数据仓库搭建的比较完整，还是应该从数据仓库中获取数据的。数据集市用来解决特定人群的特定问题，所需要的数据相比数据仓库来说少而集中。
总的来说：在面对企业级决策需求的数据支撑时，我们希望系统可以集成不同的数据源的数据、数据稳定、结构统一、保存历史数据，可以满足不同部门的不断变化的数据系统，因此产生了数据仓库；对于不同的部门来讲，进行决策时如果直接访问数据仓库，得到信息需要多张表进行关联，访问压力大，且主题繁多不易于管理，因此需要建立数据集市，从数据仓库中直接取数，对数据进行汇总整理以满足特定部门的需求。

度量

度量（Metric，也称作指标） 是人们最想关注的最终结果数据。例如总收入。

维度

维度（Demension） 是人们对度量的最终值的拆解方式。例如，总收入的数据可以分地域、分季度、分用户类别来拆借，其中的地域、季节、客户类别就是维度。

Cube 多维数据立方体

将度量值按照多个维度进行拆借，就形成了多维数据。比如总收入按照地域、季度、客户类别进行拆分，就可以形成一个三维的立方体，因此多维数据也叫多为数据立方体（Cube）。提到 Cube 就难免想起 Kylin 里的 Cube ，它会对度量的多个查询维度进行预计算，可以对数据方便的进行 OLAP 多维分析操作。这篇博客 对多维数据立方体以及 OLAP 分析介绍的比较全面。

OLAP 分析

OLAP（On-line Analytical Processing，联机分析处理） 是一类软件技术，它们使用户（业务分析师、经理和执行官）能够以交互形式快速、一致地探查数据，用户看到的是经过转换后的原始数据的各种信息视图，它们可以反映业务的真实维数。它是呈现集成性决策信息的方法,是在基于数据仓库多维模型的基础上实现的面向分析的各类操作的集合。常用的 OLAP 多维分析操作包括：钻取（Drill-down）、上卷（Roll-up）、切片（Slice）、切块（Dice）以及旋转（Pivot）等，多用于决策支持系统、商务智能或数据仓库。

元数据

元数据就是定义数据的数据。狭义的解释是用来描述数据的数据广义的来看，除了业务逻辑直接读写处理的那些业务数据，所有其它用来维持整个系统运转所需的信息、数据都可以叫作元数据

缓慢变化维度

事实表

可叠加事实，不可叠加事实，半可叠加事实。

维度表

缓慢变化维度退化维度等等等

退化维度

数仓设计方法

四步骤设计

hive 相关

日期相关操作

上月初

  
select trunc(add_months('2019-02-20', -1), 'MM');
select add_months(date_sub('2019-02-20', dayofmonth('2019-02-20') - 1), -1)

本月初

  
select trunc('2019-02-20', 'MM');
select date_sub('2019-02-20', dayofmonth('2019-02-20') - 1)

复杂 SQL 练习

题目一

有一个股票交易系统日志表（share_logs），求每只股票最大净值的时间点。

交易时间	股票代码	价格
2019-01-07 10:00:00	BIDU	168
2019-01-07 10:01:00	BIDU	167
2019-01-07 10:02:00	BIDU	158
2019-01-07 10:03:00	BIDU	163
2019-01-07 10:04:00	BIDU	164
2019-01-07 10:00:00	BABA	170
2019-01-07 10:01:00	BABA	172
2019-01-07 10:02:00	BABA	178
2019-01-07 10:03:00	BABA	176
2019-01-07 10:04:00	BABA	177
2019-01-07 10:00:00	00700	341
2019-01-07 10:01:00	00700	342
2019-01-07 10:02:00	00700	345
2019-01-07 10:03:00	00700	342
2019-01-07 10:04:00	00700	340

  
SELECT
   交易时间,
   股票代码,
   价格,
   ROW_NUMBER() OVER(PARTITION BY TO_DATE(交易时间), 股票代码 ORDER BY 价格 DESC) AS rn
FROM share_logs
WHERE rn = 1
;

题目二

用一条 SQL 查询出成绩表(grade)中每门课程都大于 80 分的学生姓名。

name	subject	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

  
SELECT
    name
FROM SC
GROUP BY
    name
HAVING MIN(score) > 80

题目三

来一个基本的行转列的，将题目二中的原始数据转换成

name	chinese	math	english
张三	81	75	0
李四	76	90	0
王五	81	100	90

  
SELECT
    name,
    SUM(CASE WHEN(subject = '语文' THEN score else 0 end)) as chinese,
    SUM(CASE WHEN(subject = '数学' THEN score else 0 end)) as math,
    SUM(CASE WHEN(subject = '英语' THEN score else 0 end)) as english
FROM SC
GROUP BY
    name
;

题目四

编写Hive的HQL语句求出每个店铺的当月销售额和累计到当月的总销售额

  
use class;
create table t_store(
name string,
months int,
money int
)
row format delimited fields terminated by ",";



with store_1 as(
    SELECT
        name,
        months,
        SUM(money) AS money
    FROM t_store
    GROUP BY
        name,
        months
),

store_2 AS(
    SELECT
        a.name as aname,
        a.months as amonths,
        a.money as amoney,
        b.name as bname,
        b.months as bmonths,
        b.money as bmoney
    FROM store_1 a
    LEFT JOIN store_1 b ON a.name = b.name
    ORDER BY
        aname,
        amonths
)

SELECT
    aname,
    amonths,
    amoney,
    SUM(bmoney) AS total
FROM store_2
WHERE amonths >= bmonths
GROUP BY
    aname,
    amonths,
    amoney
;

题目五

和题目四比较类似的一个题目有一个球队表，里面有四支球队的名称，用一个SQL求出他们可以组成的所有比赛情况。 3.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合. 你先按你自己的想法做一下，看结果有我的这个简单吗？

  
SELECT
    a.name,
    b.name
FROM team a, team b
WHERE a.name < b.name

order by,distribute by，sortby的区别

hql 调优基本方法

应尽早过滤数据
单个 SQL 所起的 JOB 个数尽量控制在 5 个以下
慎重使用 mapjoin, 一般行数小于 2000 行，大小小于 1M (扩容后可以适当放大)的表才能使用,小表要注意放在 join 的左边。否则会引起磁盘和内存的大量消耗
写SQL要先了解数据本身的特点，如果有join ,group操作的话，要注意是否会有数据倾斜
如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，这样会提升执行的速度。

数据倾斜

调整 hive 设置参数

调整 map 和 reduce 的个数

通常作业会通过 input 目录产生一个或多个 map 任务，默认的 map 的个数通常与以下因素相关：

文件总个数
单个文件大小
集群设置的文件块大小

每个文件会产生一个 map 任务，如果单个文件的大小大于集群设置的文件块大小(128M)，则会按照文件块大小来切分文件为多个 map 任务。

减少 map 数量

当输入路径的文件是多个小文件的时候，会产生大量的 map 任务，而且每个 map 任务执行时间很短，大量时间都消耗在创建以及销毁 map 任务上。所以这个时候要合并输入的文件个数以减少 map 任务的数量来优化执行效率。

  
-- 每个 map 处理的最大的文件大小，单位为B。
set mapred.max.split.size=100000000;

-- 节点中可以处理的最小的文件大小
set mapred.min.split.size.per.node=100000000;

-- 机架中可以处理的最小的文件大小
set mapred.min.split.size.per.rack=100000000;

-- 这个参数表示执行前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

我对上面参数的理解是，如果文件大于集群设置的文件块大小（128M），则按照集群设置的文件块大小（128M）来切分文件；如果文件小于 128M 大于 100M（上面参数设置的值），则按照 100M 来切分文件；剩下的小于 100M 的文件（包括小文件和分隔大文件剩下的）则会通过合并之后再按照 100M 来切分文件来减少 map 的数量。如果想尽量减少 map 的数量，则需要相应加大上面参数设置的值。需要注意的是三个参数的关系:

mapred.max.split.size <= mapred.min.split.size.per.node <= mapred.min.split.size.per.rack

增加 map 数量

当输入路径的文件是一个大文件并且有很少列的时候，每个 map 任务中会有上千万条的记录，如果用一个 map 去完成这个任务肯定会比较耗时的。这种情况下，我们要去考虑将这一个文件合理的拆分成多个小文件，这样就可以用多个 map 任务去完成这个任务。

  
-- 设置 map 的个数
set mapred.map.tasks = 100

设置 reduce 数量

-- 设置 reduce 运行的最大数量
set hive.exec.reducers.max=5000;

-- 设置每个 reduce 的数据量
set hive.exec.reducers.bytes.per.reducer=50000000;

-- 强制设施 reduce 个数
set mapred.reduce.tasks = 100;

根据实际情况，map 和 reduce 的数量要注意以下事项：

使大数据量利用合适的map数；
使单个map任务处理合适的数据量；
使大数据量利用合适的reduce数；
使单个reduce任务处理合适的数据量。

开启 mapjoin

mapjoin 会在两个表进行连接的时候将较小的表加载到内存中，从而省去了 suffer 阶段，大大提升了查询效率。mapjoin 可以提升大表连接小表的查询，可以控制小表大小的阙值来判断是否使用 mapjoin 来优化查询。

  
-- 开启 mapjoin
set hive.auto.convert.join=true;

-- mapjoin 优化表的最大大小，小于这个值会执行优化
set hive.mapjoin.smalltable.filesize=25000000;

在 map 端聚合

  
-- map端部分聚合，相当于Combiner
set hive.map.aggr=true;、

set hive.groupby.mapaggr.checkinterva =100000;
set hive.map.aggr.hash.min.reduction=0.5;

后面的两个参数的作用是预先取 100000 条数据聚合,如果聚合后的条数/100000 > 0.5，则不再聚合。

万金油

set hive.groupby.skewindata=true; 有数据倾斜时，查询计划生成两个mr job，第一个job先进行key随机分配处理，先缩小数据量。第二个job再进行真正的group by key处理。

第一通过 hive.groupby.skewindata=true控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce做次预汇总,减少某些key值条数过多某些key条数过小造成的数据倾斜问题

set hive.groupby.mapaggr.checkinterval=100000 ;–这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置

set hive.optimize.skewjoin=true;–如果是join 过程出现倾斜应该设置为true

JVM 重用

  
set mapred.job.reuse.jvm.num.tasks=10;

只有一个 reduce 的情况

在查询中存在没有 group by 的汇总
在查询中用到了 order by
在查询中有笛卡尔乘积

test

spark 相关

stage 划分

fff

driver 作用

ddd

三个 group by 的区别

fff

数据倾斜

由于大数据量导致的数据倾斜

ddd

由于复杂计算导致的数据倾斜

fff

原文链接:数仓面试准备

转载请指明出处

数仓面试准备

数仓相关

基本概念

星型模型和雪花模型

数据仓库和数据集市

度量

维度

Cube 多维数据立方体

OLAP 分析

元数据

缓慢变化维度

事实表

维度表

退化维度

数仓设计方法

hive 相关

日期相关操作

上月初

本月初

复杂 SQL 练习

题目一

题目二

题目三

题目四

题目五

order by,distribute by，sortby的区别

hql 调优基本方法

数据倾斜

调整 hive 设置参数

调整 map 和 reduce 的个数

开启 mapjoin

在 map 端聚合

万金油

JVM 重用

只有一个 reduce 的情况

test

spark 相关

stage 划分

driver 作用

三个 group by 的区别

数据倾斜

由于大数据量导致的数据倾斜

由于复杂计算导致的数据倾斜

相关文章

数仓面试题汇总

hive面试题汇总

各公司数仓分享