当前位置:首页 » 编程语言 » 必然数据倾斜的sql如何优化
扩展阅读
webinf下怎么引入js 2023-08-31 21:54:13
堡垒机怎么打开web 2023-08-31 21:54:11

必然数据倾斜的sql如何优化

发布时间: 2023-01-14 08:14:36

A. hive中sql运算出现倾斜的情况,主要原因有哪些

1. Mapjoin是一种避免避免数据倾斜的手段

允许在map阶段进行join操作,MapJoin把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配,由于在map是进行了join操作,省去了rece运行的效率也会高很多

在《hive:join遇到问题》有具体操作

在对多个表join连接操作时,将小表放在join的左边,大表放在Jion的右边,

在执行这样的join连接时小表中的数据会被缓存到内存当中,这样可以有效减少发生内存溢出错误的几率

2. 设置参数

hive.map.aggr = true

hive.groupby.skewindata=true 还有其他参数

3.SQL语言调节

比如: group by维度过小时:采用sum() group by的方式来替换count(distinct)完成计算

4.StreamTable

将在recer中进行join操作时的小table放入内存,而大table通过stream方式读取

B. 数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

大家好呀,这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据,运行过程由于通常要经过 MapRece 的过程,因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的,因此为了减少等待的时间,提高服务器的运行效率,我们需要在 HiveSQL 的语句上进行一些优化。

本节课的主要内容

引言
1、技巧一:列裁剪和分区裁剪
(1)列裁剪
(2)分区裁剪
2、技巧二:排序技巧——sort by代替order by
3、技巧三:去重技巧——用group by来替换distinct
4、技巧四:聚合技巧——grouping sets、cube、rollup
(1)grouping sets
(2)cube
(3)rollup
5、技巧五:换个思路解题
6、技巧六:union all时可以开启并发执行
7、技巧七:表连接优化
8、技巧八:遵循严格模式

Hive 作为大数据领域常用的数据仓库组件,在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大,而是数据倾斜、数据冗余、job 或 I/O 过多、MapRece 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化,也包含 Hive 配置项和 MR 方面的调整。

列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时,如果select 所有的列或者不指定分区,导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp ,默认是 true 。

分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hive.optimize.pruner ,默认是 true 。

HiveSQL中的 order by 与其他 SQL 语言中的功能一样,就是将结果按某个字段全局排序,这会导致所有map端数据都进入一个 rece 中,在数据量大时可能会长时间计算不完。

如果使用 sort by ,那么就会视情况启动多个 recer 进行排序,并且保证每个 recer 内局部有序。为了控制 map 端数据分配到 rece 的 key,往往还要配合 distribute by 一同使用。如果不加 distribute by 的话,map 端数据就会随机分配给 recer。

这里需要解释一下, distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。

假如我们要对一张很大的用户信息表按照年龄进行分组,优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候, sort by 后面还是 age 这个排序字段, distribute by 后面选择一个没有重复值的均匀字段,比如 user_id 。

这样做的原因是,通常用户的年龄分布是不均匀的,比如20岁以下和50岁以上的人非常少,中间几个年龄段的人又非常多,在 Map 阶段就会造成有些任务很大,有些任务很小。那通过 distribute by 一个均匀字段,就可以让系统均匀地进行“分桶”,对每个桶进行排序,最后再组合,这样就能从整体上提升 MapRece 的效率。

取出 user_trade 表中全部支付用户:

原有写法的执行时长:

优化写法的执行时长:

考虑对之前的案例进行优化:

注意: 在极大的数据量(且很多重复值)时,可以先 group by 去重,再 count() 计数,效率高于直接 count(distinct **) 。

如果我们想知道用户的性别分布、城市分布、等级分布,你会怎么写?

通常写法:

缺点 :要分别写三次SQL,需要执行三次,重复工作,且费时。

那该怎么优化呢?

注意 :这个聚合结果相当于纵向地堆在一起了(Union all),分类字段用不同列来进行区分,也就是每一行数据都包含 4 列,前三列是分类字段,最后一列是聚合计算的结果。

GROUPING SETS() :在 group by 查询中,根据不同的维度组合进行聚合,等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。

如果我们想知道用户的性别分布以及每个性别的城市分布,你会怎么写?

那该怎么优化呢?

注意: 第二列为NULL的,就是性别的用户分布,其余有城市的均为每个性别的城市分布。

cube:根据 group by 维度的所有组合进行聚合

注意 :跑完数据后,整理很关键!!!

rollup:以最左侧的维度为主,进行层级聚合,是cube的子集。

如果我想同时计算出,每个月的支付金额,以及每年的总支付金额,该怎么办?

那应该如何优化呢?

条条大路通罗马,写SQL亦是如此,能达到同样效果的SQL有很多种,要学会思路转换,灵活应用。

来看一个我们之前做过的案例:

有没有别的写法呢?

Hive 中互相没有依赖关系的 job 间是可以并行执行的,最典型的就是
多个子查询union all。在集群资源相对充足的情况下,可以开启并
行执行。参数设置: set hive.exec.parallel=true;

时间对比:

所谓严格模式,就是强制不允许用户执行3种有风险的 HiveSQL 语句,一旦执行会直接报错。

要开启严格模式,需要将参数 hive.mapred.mode 设为 strict 。

好啦,这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句,不断改进,就能掌握最优的写法。

C. 关于SQL数据库优化

具体要注意的:
1.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
3.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
4.in 和 not in 也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
5.尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子:
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。
6.必要时强制查询优化器使用某个索引,如在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num
7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2
SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’
SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
即:任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
11.很多时候用 exists是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select top 1 from b where num=a.num)
SELECT SUM(T1.C1)FROM T1 WHERE(
(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
SELECT SUM(T1.C1) FROM T1WHERE EXISTS(
SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成:
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果集中没有的记录,如:
SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
SELECT a.hdr_key FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL
SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
三种写法都可以得到同样正确的结果,但是效率依次降低。
12.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
13.避免频繁创建和删除临时表,以减少系统表资源的消耗。
14.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
15.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
16.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

17.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
18.尽量避免大事务操作,提高系统并发能力。
19.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

20. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary > 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。
21.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。
例:
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。
22、使用视图加速查询
把表的一个子集进行排序并创建视图,有时能加速查询。它有助于避免多重排序 操作,而且在其他方面还能简化优化器的工作。例如:
SELECT cust.name,rcvbles.balance,……other columns
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个视图中,并按客户的名字进行排序:
CREATE VIEW DBO.V_CUST_RCVLBES
AS
SELECT cust.name,rcvbles.balance,……other columns
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
然后以下面的方式在视图中查询:
SELECT * FROM V_CUST_RCVLBES
WHERE postcode>“98000”
视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
23、能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为:
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
24.能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源

35.尽量不要用SELECT INTO语句。
SELECT INOT 语句会导致表锁定,阻止其他用户访问该表。
上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试,看实现相同功能的SQL语句哪个执行时间最少,但是数据库中如果数据量很少,是比较不出来的,这时可以用查看执行计划,即:把实现相同功能的多条SQL语句考到查询分析器,按CTRL+L看查所利用的索引,表扫描次数(这两个对性能影响最大),总体上看询成本百分比即可。

今天在itput上看了一篇文章,是讨论一个语句的优化:
原贴地址: http://www.itpub.net/viewthread.php?tid=1015964&extra=&page=1
一,发现问题 优化的语句:
请问以下语句如何优化:
CREATE TABLE aa_001
( ip VARCHAR2(28),
name VARCHAR2(10),
password VARCHAR2(30) )

select * from aa_001 where ip in (1,2,3) order by name desc;
--目前表中记录有一千多万条左右,而且in中的值个数是不确定的。
以上就是优化的需要优化的语句和情况。

不少人在后面跟帖:有的说没办法优化,有的说将IN该为EXISTS,有的说在ip上建立索引复合索引(ip,name)等等。
二,提出问题 那这样的情况,能优化吗,如何优化?今天就来讨论这个问题。
三,分析问题 1,数据量1千万多条。
2,in中的值个数是不确定
3.1 分析数据分布 这里作者没有提到ip列的数据的分布情况,目前ip列的数据分布可能有以下几种:
1,ip列(数据唯一,或者数据重复的概率很小)
2,ip列 (数据不均匀,可能有些数据重复多,有些重复少)
3,ip列 (数据分布比较均匀,数据大量重复,主要就是一些同样的数据(可能只有上万级别不同的ip数据等)

解决问题:
1,对于第一种数据分布情况,只要在ip列建立一个索引即可。这时不管表有多少行, in个数是不确定的情况下,都很快。
2,对应第二中数据分布情况,在ip列建立索引,效果不好。因为数据分布不均匀,可能有些快,有些慢
3,对应第三种数据分布情况,在ip列建立索引,速度肯定慢。
注意:这里的 order by name desc 是在取出数据后再排序的。而不是取数据前排序

对于2,3两个情况,因为都是可能需要取出大量的数据,优化器就采用表扫描(table scan),而不是索引查找(index seek) ,速度很慢,因为这时表扫描效率要优于索引查找,特别是高并发情况下,效率很低。

那对应2,3中情况,如何处理。是将in改成exists。其实在sql server 2005和oracle里的优化器在in后面数据少时,效率是一样的。这时采用一般的索引效率很低。这时如果在ip列上建立聚集索引,效率会比较高。我们在SQL server 2005中做个测试。

表:[dbo].[[zping.com]]]中有约200万条数据。包含列Userid, id, Ruleid等列。按照上面的情况查询一下类似语句:
select * from [dbo].[[zping.com]]] where
userid in ('',''
,'') order by Ruleid desc

我们先看userid的数据分布情况,执行下面语句:
select userid,count(*) from [dbo].[[zping.com]]] group by userid order by 2
这时我们看看数据分布:总共有379条数据,数据两从1到15万都有,数据分布倾斜严重。下图是其中一部分。

这时如果在ip上建立非聚集索引,效率很低,而且就是强行索引扫描,效率也很低,会发现IO次数比表扫描还高。这时只能在ip上建立聚集索引。这时看看结果。

这时发现,搜索采用了(clustered index seek)聚集搜索扫描。
在看看查询返回的结果:
(156603 行受影响)
表 '[zping.com]'。扫描计数 8,逻辑读取 5877 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
返回15万行,才不到6千次IO。效率较高,因为这15万行要排序,查询成本里排序占了51%。当然可以建立(userid,Ruleid)复合聚集索引,提高性能,但这样做DML维护成本较高。建议不采用。

从上面的测试例子可以看出, 优化的解决办法:
数据分布为1:建立ip索引即可
数据分布为2,3:在ip列建立聚集索引。

D. Hive SQL处理数据倾斜的方法有哪些

可以在查询的结尾加上diistribute by rand(),回答如有帮助请采纳,谢谢!

E. 一条sql执行过长的时间,你如何优化,从哪些方面

1、查看sql是否涉及多表的联表或者子查询,如果有,看是否能进行业务拆分,相关字段冗余或者合并成临时表(业务和算法的优化)

2、涉及链表的查询,是否能进行分表查询,单表查询之后的结果进行字段整合

3、如果以上两种都不能操作,非要链表查询,那么考虑对相对应的查询条件做索引。加快查询速度

4、针对数量大的表进行历史表分离(如交易流水表)

5、数据库主从分离,读写分离,降低读写针对同一表同时的压力,至于主从同步,mysql有自带的binlog实现 主从同步

6、explain分析sql语句,查看执行计划,分析索引是否用上,分析扫描行数等等

7、查看mysql执行日志,看看是否有其他方面的问题

个人理解:从根本上来说,查询慢是占用mysql内存比较多,那么可以从这方面去酌手考虑

F. 如何进行SQL性能优化

这里分享下mysql优化的几种方法。

1、首先在打开的软件中,需要分别为每一个表创建 InnoDB FILE的文件。

G. hive数据倾斜及处理

火山日常啰嗦
学习了一些大数据的相关框架后,发现应用层的东西确实不难,真正难的都是底层原理,所以我查看了很多资料,借鉴了前人的方法再加上自己的理解,写下了这篇文章。

数据倾斜的直白概念:
数据倾斜就是数据的分布不平衡,某些地方特别多,某些地方又特别少,导致的在处理数据的时候,有些很快就处理完了,而有些又迟迟未能处理完,导致整体任务最终迟迟无法完成,这种现象就是数据倾斜。

针对maprece的过程来说就是,有多个rece,其中有一个或者若干个rece要处理的数据量特别大,而其他的rece处理的数据量则比较小,那么这些数据量小的rece很快就可以完成,而数据量大的则需要很多时间,导致整个任务一直在等它而迟迟无法完成。
跑mr任务时常见的rece的进度总是卡在99%,这种现象很大可能就是数据倾斜造成的。

产生数据倾斜的原因:
1) key的分布不均匀或者说某些key太集中。
上面就说过,rece的数据量大小差异过大,而rece的数据是分区的结果,分区是对key求hash值,根据hash值决定该key被分到某个分区,进而进入到某个rece,而如果key很集中或者相同,那么计算得到它们的hash值可能一样,那么就会被分配到同一个rece,就会造成这个rece所要处理的数据量过大。
2) 业务数据自身的特性。
比如某些业务数据作为key的字段本就很集中,那么结果肯定会导致数据倾斜啊。
还有其他的一些原因,但是,根本原因还是key的分布不均匀,而其他的原因就是会造成key不均匀,进而导致数据倾斜的后果,所以说根本原因是key的分布不均匀。

既然有数据倾斜这种现象,就必须要有数据倾斜对应的处理方案啊。
简单地说数据倾斜这种现象导致的任务迟迟不能完成,耗费了太多时间,极大地影响了性能,所以我们数据倾斜的解决方案设计思路就是往如何提高性能,即如何缩短任务的处理时间这方面考虑的,而要提高性能,就要让key分布相对均衡,所以我们的终极目标就是考虑如何预处理数据才能够使得它的key分布均匀。

常见的数据倾斜处理方案:
1 设置参数
1)设置hive.map.aggr=true //开启map端部分聚合功能,就是将key相同的归到一起,减少数据量,这样就可以相对地减少进入rece的数据量,在一定程度上可以提高性能,当然,如果数据的减少量微乎其微,那对性能的影响几乎没啥变化。
2)设置hive.groupby.skewindata=true //如果发生了数据倾斜就可以通过它来进行负载均衡。当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Rece 中,每个 Rece 做部分聚合操作,并输出结果,这样处理的结果是相同的Key 有可能被分发到不同的 Rece 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照Key 分布到 Rece 中(这个过程是按照key的hash值进行分区的,不同于mr job1的随机分配,这次可以保证相同的Key 被分布到同一个 Rece 中),最后完成最终的聚合操作。所以它主要就是先通过第一个mr job将key随机分配到rece,使得会造成数据倾斜的key可能被分配到不同的rece上,从而达到负载均衡的目的。到第二个mr job中,因为第一个mr job已经在rece中对这些数据进行了部分聚合(就像单词统计的例子,a这个字母在不同的rece中,已经算出它在每个rece中的个数,但是最终的总的个数还没算出来,那么就将它传到第二个mr job,这样就可以得到总的单词个数),所以这里直接进行最后的聚合就可以了。
3)hive.exec.recers.bytes.per.recer=1000000000 (单位是字节)
每个rece能够处理的数据量大小,默认是1G
4)hive.exec.recers.max=999
最大可以开启的rece个数,默认是999个
在只配了hive.exec.recers.bytes.per.recer以及hive.exec.recers.max的情况下,实际的rece个数会根据实际的数据总量/每个rece处理的数据量来决定。
5)mapred.rece.tasks=-1
实际运行的rece个数,默认是-1,可以认为指定,但是如果认为在此指定了,那么就不会通过实际的总数据量/hive.exec.recers.bytes.per.recer来决定rece的个数了。

2 sql语句优化
给几个具体的场景以及在这些场景下的处理方案:
1)进行表的join这种业务操作时,经常会产生数据倾斜。
原因就是这些业务数据本就存在key会分布不均匀的风险,所以我们join时不能使用普通的join(rece端join)或者可以使用普通join,但是是优化后的。

但是这种操作有个前提条件就是仅适用于小表join大表,而小表怎么定义它的大小,多小的表才算小表,这里有个参数可以确定的(但是这个参数名我暂时忘记了),如果小表的数据大小小于这个值,就可以使用map join,而是在这种情况下是自动使用map join这种方案的。所以如果是大小表join,直接用map join,避免数据倾斜。

方法1:(普通join)
select * from log a join users b on (a.user_id is not null and a.user_id = b.user_id );
这是属于表的内连接的,两张表不满足条件的记录都不保留。

方法2:检测到user_id是null时给它赋予一个新值(这个新值由一个字符串(比如我自己给它定一个 hive)加上一个随机数组成),这样就可以将原来集中的key分散开来,也避免了数据倾斜的风险。而且因为这些数据本来就是无效数据,根本不会出现在结果表中,所以,这样处理user_id(由一个字符串(比如我自己给它定一个 hive)加上一个随机数),它也无法关联的,因为有效的数据的user_id没有这种形式的,所以就算这些无效数据出现在不同的rece中还是不会影响结果的,我这样处理只是为了将它们分散开而已,所以用这种方法处理,结果表中也不会出现null这些无效数据,跟过滤处理方案得到的结果是一样的。(普通join)
select *
from log a
join users b
on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;
但是这两种方案只是适用于大表join大表的内连接,两张表的无效数据都不保留。
但是如果对于左外连接或者右外连接这种情况,即使驱动表中某些记录在另一张表中没有数据与它对应,但我们是依然需要保留驱动表的这些数据的,那该怎么办呢?其实很简单,只需要将上述方法得到的结果再与驱动表的这些无数据取并集就可以了。
如下:
select * from log a
left outer join users b
on a.user_id is not null
and a.user_id = b.user_id
union all
select * from log a
where a.user_id is null;
2)虽然都是大表,但是呢对于某些业务数据而言,其有用的部分只占它所在表的很少一部分,那么我们就可以将它们先取出来,得到的结果应该是一张小表,那么就可以使用map join来避免数据倾斜了。

场景:用户表中user_id字段为int,log表中user_id字段既有string类型也有int类型。
当按照user_id进行两个表的Join操作时,因为我们在连接时要进行user_id的比较,所以需要user_id的类型都相同,如果我们选择将log表中的String类型转换为int类型,那么就可能会出现这种情况:String类型转换为int类型得到的都是null值(这就是类型转换的问题了,String类型数据转换为int类型会失败,数据丢失,就会赋null值),如果所有的String类型的user_id都变成了null,那么就又出现了集中的key,分区后就又会导致数据倾斜。所以我们进行类型转换时不能选择将String类型转换为int,而应该将int类型转换为String,因为int转换为String不会出问题,int类型原来的值是什么,转换为String后对应的字符串就会是什么,形式没变,只是类型变了而已。
解决方法:把int类型转换成字符串类型
select * from users a
join logs b
on (a.usr_id = cast(b.user_id as string));

比如有一份日志,要你从日志中统计某天有多少个用户访问网站,即统计有多少个不同的user_id;但是呢这个网站却又恰巧遭到攻击,日志中大部分都是同一个user_id的记录,其他的user_id属于正常访问,访问量不会很大,在这种情况下,当你直接使用count(distinct user_id)时,这也是要跑mr任务的啊,这时这些大量的相同的user_id就是集中的key了,结果就是通过分区它们都被分到一个rece中,就会造成这个rece处理的数据特别大,而其中的rece处理的数据都很小,所以就会造成数据倾斜。
那么要怎么优化呢?
方法1:可以先找出这个user_id是什么,过滤掉它,然后通过count(distinct user_id)计算出剩余的那些user_id的个数,最后再加1(这1个就是那个被过滤掉的user_id,虽然它有大量的记录,但是ser_id相同的都是同一个用户,而我们要计算的就是用户数)
sql语句展示:
分组求和后降序排序,就可以得到这个数据量最大的user_id是什么,然后我们下一步操作时就过滤它,等计算完其他的再加上它这一个。
select user_id,count(user_id) from log group by user_id desc limit 2;
select count(distinct user_id)+1 as sum from log;
sum就是最终的结果--用户数
方法2:我们可以先通过group by分组,然后再在分组得到的结果的基础之上进行count
sql语句展示:
select count(*) from (select user_id from log group by user_id) new_log;

总的来说就是,数据倾斜的根源是key分布不均匀,所以应对方案要么是从源头解决(不让数据分区,直接在map端搞定),要么就是在分区时将这些集中却无效的key过滤(清洗)掉,或者是想办法将这些key打乱,让它们进入到不同的rece中。

性能调优是指通过调整使得机器处理任务的速度更快,所花的时间更少,而数据倾斜的处理是hive性能调优的一部分,通过处理能够大大减少任务的运行时间。

除了数据倾斜的处理之外,hive的优化还有其他方面的,例如where子句优化:
select * from a left outer join b on (a.key=b.key) where a.date='2017-07-11' and b.date='2017-07-11';
这是一个左外连接。
这个sql语句执行的结果是:得到的结果是表a与表b的连接表,且表中的记录的date都是'2017-07-11'。
而这个sql语句的执行过程是:逐条获取到a表的记录,然后扫描b表,寻找字段key值为a.key的记录,找到后将b表的这条记录连接到a表上,然后判断连接后的这条记录是否满足条件a.date='2017-07-11' and b.date='2017-07-11',如果满足,则显示,否则,丢弃。
因为这是一个左外连接,且a为驱动表,连接时在a中发现key而在b中没有发现与之相等的key时,b中的列将置为null,包括列date,一个不为null,一个为null,这样后边的where就没有用了。
简答的说这个方案的做法就是先按连接条件进行连接,连接后再看where条件,如果不满足就丢弃,那之前连接所做的那些功夫就浪费了,白白耗费了资源(cpu等),增加了运行的总时间,如果有一种方案可以在未进行连接之前就直接判断出不满足最终的条件,那么就可以直接丢弃它,这样对于这样的记录就不要浪费资源以及时间去连接了,这样也是能提升性能的,下面就看看这种方案:
sql语句:
将刚才的where限制条件直接放到on里面,那么就变成了满足这三个条件才会进行连接,不满足的直接过滤掉,就像上面所说的,少了无效连接那一步,就相对地节约了时间,如果这样的无效连接的记录很多的话,那么采用这种改进版的方案无疑能够较大程度地提高性能。
select * from a left outer join b on (a.key=b.key and a.date='2017-07-11' and b.date='2017-07-11');

不管怎么说,我们在运行任务时,总是希望能加快运行速度,缩短运行时间,更快地得到结果,即提升性能,这是我们的目的,这就是我们所谓的性能调优。

关于小表join大表的补充:
表join时的操作是这样的:
当操作到驱动表的某条记录时,就会全局扫描另一张表,寻找满足条件的记录,而当扫描它时,为了读取速度更快,一般都选先将它加载到内存,而内存的大小是有限的,为了不占据过多的内存或者避免内存溢出,加载进入内存的表一般是小表,即数据量比较小,map join就是这样做的。
即驱动表不放进内存,而另一张表(即要连接到驱动表的那一张表)就要先加载进内存,为了扫描速度更快,提高性能。
比如select * from a left outer join b on (a.key=b.key);
左外连接,驱动表是a,表b的记录是要被连接到表a的,所以每在a上连接一条记录就要被全局扫描一次的表是b,所以表b应先加载到内存(前提是表b是小表,如果是大表的话,估计会产生oom异常--out of memory内存溢出异常)。

select * from aa right outer join bb on (a.key=b.key);
右外连接,驱动表是bb,aa应先加载到内存(前提是小表)。

ps:希望我的分享能帮助到有需要的伙伴哦。我不是大神的哦,如果文中有误,还请大家不吝赐教,帮忙指正,谢谢了!!!

H. Hive-3.1.2(五)解决数据倾斜问题和优化

再 hive 里输入如下:

或在 hive-site.xml 里添加如下:

hive.groupby.skewindata=true:数据倾斜时负载均衡,当选项设定为true,生成的查询计划会有两个MRJob:

第一个MRJob中,Map的输出结果集合会随机分布到Rece中,每个 Rece做部分聚合操作,并输出结果,这样处理的结果是相同的 GroupBy Key 有可能被分到不同的Rece中,从而达到负载均衡的目的;

第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Rece中(这个过程可以保证相同的GroupBy Key被分到统一个Rece中),最后完成最终的聚合操作。

由上面可以看出,起到至关重要的作用其实时第二个参数的设置,它使计算变成了两个 MapRece,现在第一个中再 shuffle 过程 partition 时随鸡给 key 打标机,使每个 key 随机均匀分布到各个 rece 上计算,但是这样只能完成部分计算,因为相同key 没有分配到相同 rece 上,所以需要第二次的 MapRece,这次就回归正常 shuffle,但是数据分布不均匀的问题再第一次有很大的改善,因此基本解决数据倾斜。

再 hive 里输入如下:

或在 hive-site.xml 里添加如下:

当使用以上参数设置时,需要注意sql写的时候,小表一定要在前面(最左侧)。

mapjoin 的主要意思就是,当连接两个表是一个比较小的表和一个特大的表的时候,我们把比较小的table直接放到内存中去,然后再对比较大的表格进行map操作。join就发生在map操作的时候,每当扫描一个大的table中的数据,就要去查看小表的数据,哪条与之相符,继而进行连接。这里的join并不会设计rece操作。map端join的优势就是在于没有shuffle。

还有种sql优化的方式,就是有条件的话,先对大表进行条件过滤,后对他join。

优化前

优化后

优化前的sql 会启用一个 Rece 任务,优化后的会启用两个 Rece 任务。优化后的 Rece 必须设置 mapred.rece.tasks 数量才有效,否则默认还是1个。而count一定之启用1个Rece。

日常统计场景中,我们经常会对一段十七内的字段进行消重并统计数量,SQL语句类似于 优化前 的语句,这条语句是从一个表的符合where条件的记录中统计不重复的id的总数。该语句转化为 MapRece 任务后执行示意图如下:

由于引入了 distinct,因此在Map阶段无法利用 combine 对输出结果消重,必须将id作为key输出,在Rece阶段再对来自于不同 Map 相同 key 的结果进行消重,计入最终统计值。

我们看到任务运行时Rece个数为1,对于统计大量数据量时,这会导致最终Map的全部输出由单个Reuce处理,这唯一的Rece需要shuffle大量的数据,并且进行排序聚合等处理,这使得它称为整个任务的IO和运算瓶颈。

经过上述分析后,我们可以尝试显示的增大 Rece 任务个数来提高Rece阶段的并发,使每一个Rece的数据量控制在我们预想的范围。

但这个调正不会影响 count 这种"全聚合(full aggregates)"的任务,它会忽略用户指定的

我们利用hive对嵌套语句的支持,将原来一个 MapRece 任务转为两个任务,在第一阶段选出全部的非重复id,在第二阶段再对这些已消重的id进行统计。这样在第一阶段我们可以同过增大Rece的并发数,并发处理Map输出。在第二阶段,由于id已经消重,因此count(*) 操作Map阶段不需要输出原id数据,只输出一个合并后的计数即可。这样即使第二阶段Hive强制指定一个Rece任务,极少量的Map输出数据也不会使单一的Rece任务成为瓶颈,改进后的视图如下:

Hive底层自动对小文件做了优化,用了 CombineTextInputFormat,将多个小文件切片合成一片,对应也就只会启动一个 Map 任务。
合并完成的切片大小,如果 > mapred.max.split.size 的大小,就启动一个新的切片任务。默认 mapred.max.split.size=134217728(128MB)

JVM 重用是hadoop调优参数内容,对hive的性能具有非常大的影响,特别是对于很难避免小文件的场景或者task特别多的场景,这类场景大多数执行事件都很短。这时jvm的启动过程可能会造成相当大的开销,尤其时执行的Job包含由成千上万个task任务的情况。

JVM重用可以使得一个JVM进程在同一个JOB中重新使用N次后才会销毁。

在hive里面可以通过严格模式防止用户执行那些可能产生意想不到的不好的效果的查询,从而保护hive的集群。

用户可以通过以下来设置严格模式,改成 unstrict 则为非严格模式。

在严格模式下,用户在运行如下查询的时候会报错:

因为在测试环境下我们都把应用程序跑通了,如果还加上推测执行,如果有一个数据分片本来就会发生数据倾斜,执行事时间就是比其他的时间长,那么hive就会把这个执行时间长的job当作运行失败,继而又产生一个相同的job去运行,后果可想而知,可以通过如下设置关闭推测执行机制。该机制默认时开启的。

I. Hive数仓-数据倾斜优化

首先倾斜一般发生在rece阶段

-- 解决方案

Hive默认启动Map join 优化

如果是查询的语句列如(group, join)可以直接在yarn kill掉
如果是 insert ,,, select 就要权衡 等他跑完

结束后 对sql语句发生数据倾斜的地方进行定位、给出解决方案、测试、变更

可以要对进行join 或者group by 的字段采样
列如 订单表1000w条数据 商品表100w条数据
a.pid = b.pid

select pid ,count(*) from tb_order group by pid;
查看一下订单表中 商品key的个数
列如商品1 900w条 那么这个数据一定会发生数据倾斜

textfile 文本文件 hdfs 默认存储
SequenceFile 二进制文件 使用二进制字节码存储 存储方式为行存储,其具有使用方便、可分割、
可压缩的特点
RCfile 、
ORCfile 列式存储
行式存储 方便插入 列式存储 方便查询
:相比TEXTFILE和SEQUENCEFILE,RCFILE由于列式存储方式,数据加载时性能消耗较大,但是具有较好
的压缩比和查询响应。
数据仓库的特点是一次写入、多次读取,因此,整体来看,RCFILE相比其余两种格式具有较明显的优势