数据倾斜、数据漂移及hiveSQL优化

一流觞

已于 2023-02-26 15:06:25 修改

阅读量1.3k

点赞数 2

分类专栏：大数据文章标签：数据仓库数据库

于 2023-02-23 20:33:10 首次发布

本文链接：https://blog.csdn.net/mklmlkj/article/details/129189133

版权

大数据专栏收录该内容

16 篇文章 0 订阅

订阅专栏

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、数据漂移

4.1、什么是数据漂移及情况

通常是指ods表的同⼀个业务⽇期数据中包含了前⼀天或后⼀天凌晨附近的数据或者丢失当天变更的数据，这种现象就叫做漂移，

4.2、解决数据漂移思路

两种方法：
**方法一：**多获取一点后⼀天的数据，保障数据只多不少（简单粗暴）
**方法二：**第⼀种⽅案⾥，时间戳字段分为四类：

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

理论上这⼏个时间应该是⼀致的，但往往会出现差异，造成的原因可能为：
数据抽取需要⼀定的时间，extract_time 往往晚于前三个时间。
业务系统⼿动改动数据并未更新 modfied_time。
⽹络或系统压⼒问题，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）hadoop中的数据倾斜表现：
有一个多几个Reduce卡住，卡在99.99%，一直不能结束。
各种container报错OOM
异常的Reducer读写的数据量极大，至少远远超过其它正常的Reducer
伴随着数据倾斜，会出现任务被kill等各种诡异的表现。
2）hive中数据倾斜
一般都发生在Sql中group by和join on上，而且和数据逻辑绑定比较深。
3）Spark中的数据倾斜
Spark中的数据倾斜，包括Spark Streaming和Spark Sql，表现主要有下面几种：
Executor lost，OOM，Shuffle过程出错；
Driver OOM；
单个Executor执行时间特别久，整体任务卡在某个阶段不能结束；
正常运行的任务突然失败；

数据倾斜产生原因

以Spark和Hive的使用场景为例。
他们在做数据运算的时候会涉及到，count distinct、group by、join on等操作，这些都会触发Shuffle动作。一旦触发Shuffle，所有相同key的值就会被拉到一个或几个Reducer节点上，容易发生单点计算问题，导致数据倾斜。
一般来说，数据倾斜原因有以下几方面：
1）key分布不均匀
2）建表时考虑不周
举一个例子，就说数据默认值的设计吧，假设我们有两张表：
user（用户信息表）：userid，register_ip
ip（IP表）：ip，register_user_cnt
这可能是两个不同的人开发的数据表。如果我们的数据规范不太完善的话，会出现一种情况：
user表中的register_ip字段，如果获取不到这个信息，我们默认为null；
但是在ip表中，我们在统计这个值的时候，为了方便，我们把获取不到ip的用户，统一认为他们的ip为0。
两边其实都没有错的，但是一旦我们做关联了，这个任务会在做关联的阶段，也就是sql的on的阶段卡死。
3）业务数据激增
比如订单场景，我们在某一天在北京和上海两个城市多了强力的推广，结果可能是这两个城市的订单量增长了10000%，其余城市的数据量不变。
然后我们要统计不同城市的订单情况，这样，一做group操作，可能直接就数据倾斜了。

解决数据倾斜思路

很多数据倾斜的问题，都可以用和平台无关的方式解决，比如更好的数据预处理，异常值的过滤等。因此，解决数据倾斜的重点在于对数据设计和业务的理解，这两个搞清楚了，数据倾斜就解决了大部分了。
1）业务逻辑
我们从业务逻辑的层面上来优化数据倾斜，比如上面的两个城市做推广活动导致那两个城市数据量激增的例子，我们可以单独对这两个城市来做count，单独做时可用两次MR，第一次打散计算，第二次再最终聚合计算。完成后和其它城市做整合。
2）程序层面
比如说在Hive中，经常遇到count(distinct)操作，这样会导致最终只有一个Reduce任务。我们可以先group by，再在外面包一层count，就可以了。比如计算按用户名去重后的总用户量：
（1）优化前
只有一个reduce，先去重再count负担比较大： select name,count(distinct name)from user;
（2）优化后
// 设置该任务的每个job的reducer个数为3个。Hive默认-1，自动推断。
set mapred.reduce.tasks=3;
// 启动两个job，一个负责子查询(可以有多个reduce)，另一个负责count(1)：
select count(1) from (select name from user group by name) tmp;
3）调参方面
Hadoop和Spark都自带了很多的参数和机制来调节数据倾斜，合理利用它们就能解决大部分问题。
详见：
1、hive 参数设置大全：https://blog.csdn.net/weixin_39032019/article/details/111912916
2、spark-submit 参数设置：https://blog.csdn.net/weixin_39032019/article/details/103371674
3、kudu 参数设置：https://blog.csdn.net/weixin_39032019/article/details/110534549
4）从业务和数据上解决数据倾斜
很多数据倾斜都是在数据的使用上造成的。我们举几个场景，并分别给出它们的解决方案。
数据有损的方法：找到异常数据，比如ip为0的数据，过滤掉
数据无损的方法：对分布不均匀的数据，单独计算
hash法：先对key做一层hash，先将数据随机打散让它的并行度变大，再汇聚
数据预处理：就是先做一层数据质量处理，类似于数据仓库维度建模时，底层先处理数据质量

问题二

Hive 处理 100 亿及数据之性能优化
项目背景
sdb项目，日增数据约6.5T，约100亿左右数据。通过清洗，输出不同5大维度，8 种粒度的依赖视图，以及相关的报表汇总统计。过程遇到了不少坑。在一些数据量大的场景下，很容易把一些潜在的问题就容易暴露出来，现总结如下
get_json_object()
描述：get_json_object 函数第一个参数填写 json 对象变量，第二个参数使用 $表示json变量标识，然后用.或[]读取对象或数组；定义：该方法只能接受两个参数，如果想要查看多个值，只能多写几个示例：select get_json_object('{"name":"linda","server":"www.baidu.com"}','$ .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()方法只是使用了一次，进行了批量解析，这种方式明显更高。另外，确认是否所有的字段都有必要解析？解析的字段越多，意味着序列化和反序列化，以及解析的工作量，这都是很消耗 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）解析数组，再进行关联，输入到目标表。
对所有汇总到用户维度的底层表进行解析数组，然后进行关联再计算。针对以上方案，如果既需要满足本需求，又有利于数仓建设的话，推荐方案一，如果是敏捷开发，满足此需求的话推荐方案三，对于数据集市建设，如果没必要细致到用户到城市维度的话，建议方案二，三种方案各有利弊，具体选择需要根据实际情况定夺。

问题四

Hive 一种通用的上亿级别的去重方法
背景:需要把申请人信息表中的所有渠道端用户的 user_id 进行去重的然后计数的操作。数据量去完重复大概 2 个亿，去之前大概将近 3 亿。
做法一：最原始的做法使用的是 count(distinct user_id)这个需要大概跑 3 个小时的任务。
做法二：使用 group by 去重，效果依然不好。
做法三：使用 row_number() over(partition by user_id order by user_id desc) as rn ，然后取rn=1，这样也不行。

#通用做法：将任务分成 5 份，即 user_id%5=0,1,2,3,4 这几个任务去跑，然后进行 union all和并即可。任务从三小时降到0.5小时。
#代码：开启 5 个以下任务，uid%5=0,1,2,3,4 五种情况
写到 temp.ods_lc_appl_all_1，2，3，4，5
	select 
		user_id,
		sum(uv)
	from
	(
		select * from temp.ods_lc_appl_all_1
		UNION ALL
		select * from temp.ods_lc_appl_all_2
		UNION ALL
		select * from temp.ods_lc_appl_all_3
		UNION ALL
		select * from temp.ods_lc_appl_all_4
		UNION ALL
		select * from temp.ods_lc_appl_all_5
)a
group by user_id;

遇到的问题
列裁剪
Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其它列。例如，若有以下查询：
select a,b from q where e<10;
在实施此项查询中，Q 表有 5 列（a，b，c，d，e），Hive 只读取查询逻辑中真实需要的 3 列 a、b、e，而忽略列 c，d；这样做节省了读取开销，中间表存储开销和数据整合开销。裁剪所对应的参数项为：hive.optimize.cp=true（默认值为真）

补充：在我实习的操作过程中，也有用到这个道理，也就是多次 join 的时候，考虑到只需要的指标，而不是为了省事使用 select * 作为子查询

分区裁剪
可以在查询的过程中减少不必要的分区。例如，若有以下查询：

SELECT
* 
FROM
	(
		SELECTT
			a1,
			COUNT(1) 
		FROM 
			T 
		GROUP BY a1
	)subq # 建议贴边写，这样容易检查是否是中文括号！WHERE subq.prtn=100; #（多余分区）

SELECT
* FROM
T1 JOIN
(
SELECT
*
FROM T2
)subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;

查询语句若将“subq.prtn=100”条件放入子查询中更为高效，可以减少读入的分区数目。 Hive 自动执行这种裁剪优化。分区参数为：hive.optimize.pruner=true（默认值为真）

补充：实际集群操作过程中，加分区是重中之重，不加分区的后果非常可能把整个队列资源占满，而导致 io 读写异常，无法登陆服务器及 hive！切记切记分区操作和 limit 操作

JOIN 原则
在使用写有 Join 操作的查询语句时有一条原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生 OOM 错误的几率。对于一条语句中有多个 Join的情况，如果 Join 的条件相同，一句话就是小表在左边比如查询：

MAP JOIN 操作（没有用到）
如果你有一张表非常非常小，而另一张关联的表非常非常大的时候，你可以使用 mapjoin 此 Join 操作在 Map 阶段完成，不再需要 Reduce，也就不需要经过 Shuffle 过程，从而能在一定程度上节省资源提高 JOIN 效率前提条件是需要的数据在 Map 的过程中可以访问到。比如查询：

GROUP BY 操作

进行 GROUP BY 操作时需要注意一下几点：
Map 端部分聚合
事实上并不是所有的聚合操作都需要在 reduce 部分进行，很多聚合操作都可以先在 Map 端进行部分聚合，然后 reduce端得出最终结果。
这里需要修改的参数为：
hive.map.aggr=true（用于设定是否在 map 端进行聚合，默认值为真）
hive.groupby.mapaggr.checkinterval=100000（用于设定 map 端进行聚合操作的条目数）
有数据倾斜时进行负载均衡
此处需要设定 hive.groupby.skewindata，当选项设定为true 是，生成的查询计划有两个 MapReduce 任务。
合并小文件
我们知道文件数目小，容易在文件存储端造成瓶颈，给 HDFS 带来压力，影响处理效率。对此，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响。
是否合并 Map 输出文件：hive.merge.mapfiles=true（默认值为真）
是否合并 Reduce 端输出文件：hive.merge.mapredfiles=false（默认值为假）
合并文件的大小：hive.merge.size.per.task=25610001000（默认值为256000000）
实际集群操作过程中，join 时候的小表在前的原则是比较先接触到的，这点在查阅一些资料和问过同事之后觉得是最快优化 join 操作的，而mapjoin 则几乎没有用到，可能接触到的小表量级也是比较大的，而且公司的 hive 貌似是 0.12 的了，应该是自动优化的把

问题五

无效 ID 在关联时的数据倾斜问题
问题：日志中常会出现信息丢失，比如每日约为 20 亿的全网日志，其中的 user_id 为主键，在日志收集过程中会丢失，出现主键为 null 的情况，如果取其中的user_id 和 bmw_users 关联，就会碰到数据倾斜的问题。原因是 Hive 中，主键为 null 值的项会被当做相同的Key 而分配进同一个计算 Map。
解决方法 1：user_id 为空的不参与关联，子查询过滤 null

	SELECT
	* 
	FROM 
		log a 
		JOIN bmw_users b ON a.user_id IS NOT NULL AND a.user_id=b.user
	UNION All 
	SELECT
		* 
	FROM 
		log a 
		WHERE a.user_id IS NULL


# 解决方法 2 如下所示：函数过滤 null
SELECT
	* 
FROM 
	log a 
LEFT OUTER JOIN bmw_users b ON 
CASE WHEN a.user_id IS NULL THEN CONCAT('dp_hive',RAND()) 
ELSE a.user_id END =b.user_id; // 这句话写的好骚气啊，还有这种操作，我没有试过

调优结果：原先由于数据倾斜导致运行时长超过 1 小时，解决方法 1 运行每日平均时长 25 分钟，解决方法 2 运行的每日平均时长在 20 分钟左右。优化效果很明显。
我在工作中总结出：解决方法2比解决方法1效果更好，不但IO少了，而且作业数也少了。解决方法 1 中 log 读取两次，job 数为2。解决方法 2 中 job 数是 1。这个优化适合无效 id（比如-99、‘’，null 等）产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的 Reduce上，从而解决数据倾斜问题。因为空值不参与关联，即使分到不同的Reduce上，也不会影响最终的结果。附上Hadoop通用关联的实现方法是：关联通过二次排序实现的，关联的列为 partion key，关联的列和表的tag组成排序的group key，根据 pariton key分配 Reduce。同一Reduce内根据group key 排序。

问题七

不同数据类型关联产生的倾斜问题
问题：不同数据类型 id 的关联会产生数据倾斜问题。一张表 s8 的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。 s8 的日志中有 32 为字符串商品 id，也有数值商品 id，日志中类型是 string 的，但商品中的数值 id 是 bigint的。猜想问题的原因是把 s8 的商品 id 转成数值 id 做 hash 来分配 Reduce，所以字符串 id 的 s8 日志，都到一个 Reduce 上了，解决的方法验证了这个猜测。
解决方法：把数据类型转换成字符串类型

数据质量

事前：制定每份数据的数据质量监控规则
事中：监控和影响数据生产过程，不符合质量要求的数据不算产出数据
事后：数据质量情况分析和打分，推动数据质量提升；
保障数据质量，可以从事前、事中、事后入手。事前，我们可以通过制定每份数据的数据质量监控规则，越重要的数据对应的监控规则应该越多；事中，通过监控和影响数据生产过程，对不符合质量要求的数据进行干预，使其不影响下流数据的质量；事后，通过对数据质量情况进行分析和打分，将一些不足和改进反馈数据监控体系，推动整体的数据质量提升。

数据生命周期管理

1、出于成本等因素的考虑，在大数据平台上我们依然需要对数据生命周期进行管理。根据使用频率将数据分为冰、冷、温、热四类。一个合理的数据生命周期管理要保证温热数据占整个数据体系大部分；同时为了保障数据资产的完整性，对于重要的基础数据会长久保留。
2、对于数据中间计算过程数据，在保障满足绝大部分应用访问历史数据需要的前提下，缩短数据保留周期，有助于降低存储成本；最后一点值得注意的是，冷备已经成为历史，在大数据平台下不需要单独的冷备设备。

一流觞

关注

2
点赞
踩
16

收藏

觉得还不错? 一键收藏
2
评论
数据倾斜、数据漂移及hiveSQL优化

假如要从一张stg层表中将json字符串解析成对相应的字符串，假设有10个字段，那么get_json_object()方法相当于一条记录使用 10 次函数，而 json_tuple()方法只是使用了一次，进行了批量解析，这种方式明显更高（脑补下 JVM 的知识点）。针对以上方案，如果既需要满足本需求，又有利于数仓建设的话，推荐方案一，如果是敏捷开发，满足此需求的话推荐方案三，对于数据集市建设，如果没必要细致到用户到城市维度的话，建议方案二，三种方案各有利弊，具体选择需要根据实际情况定夺。
复制链接

扫一扫

专栏目录