前序

目前离线计算主要分为两块：hive 和 spark，该手册将围绕这两部分展开说明。随着技术不断迭代升级，结合不同业务、不同场景，手册的适用性可能发生变化，因此下面介绍的优化手段可作为参考，并不是一成不变的。

1 离线计算Hive

Hive的简单定义（来自Hive官网）：

The Apache Hive (TM) data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Built on top of Apache Hadoop (TM), it provides:
 • Tools to enable easy access to data via SQL, thus enabling data warehousing tasks such as extract/transform/load (ETL), reporting, and data analysis
 • A mechanism to impose structure on a variety of data formats
 • Access to files stored either directly in Apache HDFS (TM) or in other data storage systems such as Apache HBase (TM)
 • Query execution using Apache Hadoop MapReduce, Apache Tez or Apache Spark frameworks.

简单来说，hive本身是一种数据仓库，通过其提供的sql和访问接口，使我们能够很方便的访问读写大规模数据集，无需关注底层数据是如何分布存储的。目前hive 提供了三种计算引擎：Apache Hadoop MapReduce、Apache Tez 、Apache Spark，使用者可根据hadoop集群安装环境（CDH、阿里云EMR等等）选择使用不同的计算引擎。
具体可通过参数设置： set hive.execution.engine=spark（ mr, tez, spark ）

1.1 hive on mr

1.1.1 数据裁剪

要点

1、尽量避免全表扫描
2、多用子查询
3、分区表查询条件一定要指定分区
4、不要使用select  *

结论

1、对于Join(Inner Join)、Full outer Join，条件写在on后面，还是where后面，性能上面没有区别； 
2、对于Left outer Join，右侧的表写在on后面、左侧的表写在where后面，性能上有提高； 
3、对于Right outer Join，左侧的表写在on后面、右侧的表写在where后面，性能上有提高； 
4、当条件分散在两个表时，谓词下推可按上述结论2和3自由组合，情况如下：

用法示例

SELECT a.id
FROM test_t1 a
left outer join test_t2 b
ON (a.id = b.url);
WHERE b.day = '2020-05-10'
--正确的写法是写在ON后面：
SELECT a.id
FROM test_t1 a
left outer join test_t2 b
ON (a.id = b.url AND b.day = '2020-05-10');
--或者直接写成子查询：
SELECT a.id
FROM test_t1 a
left outer join (SELECT url FROM test_t2 WHERE day = '2020-05-10') b
ON (a.id = b.url)

1.1.2 尽量少用 count distinct

说明

数据量小的时候影响不大，数据量大的情况下，由于COUNT DISTINCT这种全量去重操作需要用一个Reduce Task来完成，这一个Task需要处理的数据量太大，就会出现OOM问题或者任务耗时很长，最终导致整个Job很难完成，建议使用GROUP BY再COUNT的方式替换

用法示例

SELECT day,
COUNT(DISTINCT id) AS uv
FROM test_t1
GROUP BY day
--可以转换成：
SELECT day,
COUNT(id) AS uv
FROM (SELECT day,id FROM test_t1 GROUP BY day,id) a
GROUP BY day;

1.1.3 group by 数据倾斜

说明

group by 分组单个key 对应的数据量特别大，在reduce端聚合，造成任务执行时间特别长

用法示例1

--在不确定倾斜值key的情况，使用hive.groupby.skewindata，会分解使用两个mr Job完成计算：
-- 正常写法
select product_id, count(1)
from priors
group by product_id
limit 10;


--改进后的写法
set  hive.groupby.skewindata = true;
select product_id, count(1)
from priors
group by product_id
limit 10;

用法示例2

--在倾斜值key确定的情况，可以使用随机函数的方式打散key：
  -- 正常写法
  select key
       , count(1) as cnt
    from tb_name
   group  by  key;

  -- 改进后写法
  select a.key
       , sum(cnt) as cnt
   from (select key
              , if(key = 'key001', rand(), 0)
              , count(1) as cnt
           from tb_name
          group by key, 
                   if(key = 'key001', rand(), 0)
         ) t
   group by t.key;

1.1.4 map-side join

说明

大表和小表join
MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。Hive0.7之前，需要使用hint提示 /*+ mapjoin(table) */才会执行MapJoin,否则执行Common Join，但在0.7版本之后，默认自动会转换Map Join，由参数hive.auto.convert.join来控制，默认为true.

用法

set hive.auto.convert.join = true; 
set hive.mapjoin.smalltable.filesize = 512000000; 

1.1.5 join 数据倾斜

说明

一种情况是join key值包含很多空值或异常值， 另一种情况是key值都是有效值

用法示例1

--join key值包含很多空值或异常值，可以对空值或异常值过滤，或者赋一个随机值来分散key
select 
  a.userid, 
  b.name
from user_info a
join (
  select 
  	case 
      when userid is null then cast(rand(23)* 100000 as int)
      else userid end 
    as userid,
    name
  from user_read_log
) b
on a.userid = b.userid

用法示例2

#key值都是有效值，没有办法判断哪个key 会产生多大的倾斜
set hive.optimize.skewjoin = true;
set hive.skewjoin.key = 100000;
set hive.exec.reducers.bytes.per.reducer = 1000000000;

skewjoin原理：

1、hive.skewjoin.key，表示join过程中同一个key对应的行数，超过该阈值时，认定该key是一个会带来数据倾斜的join key.
（Determine if we get a skew key in join. If we see more than the specified number of rows with the same key in join operator, we think the key as a skew join key.）
2、为倾斜key和非倾斜key产生两个不同的join，倾斜key执行map-side join，非倾斜key执行正常的Join
3、执行union操作合并上述步骤2中产生的两个不同join，得到join最终结果

1.1.6 合理使用union

1、union 会去重，union all不会去重
2、对同一张表的union all + insert 要比多次insert快的多

用法示例

--在大数据量的情况下，distinct + union all 性能大于 UNION 的性能
--union + count
select count(*) from(
  select order_id,user_id,order_dow from orders where order_dow='0' 
  union 
  select order_id,user_id,order_dow from orders where order_dow='1' 
  union 
  select order_id,user_id,order_dow from orders where order_dow='2'
)t;


--union all + distinct + count
select count(*) from(
  select distinct * from (
    select order_id,user_id,order_dow from orders where order_dow='0' 
    union all
    select order_id,user_id,order_dow from orders where order_dow='1' 
    union all 
    select order_id,user_id,order_dow from orders where order_dow='2'
  ) t
)t1;

1.1.7 设置并行执行

说明

并行执行可以加快任务的执行速率，但不会减少其占用的资源，应根据计算资源和任务合理设置。

用法示例

#打开任务并行执行，设置同一个sql允许最大并行度，
#对于同一个SQL产生的Job，如果不存在依赖的情况下，将会并行启动Job
set hive.exec.parallel=true; 
set hive.exec.parallel.thread.number=8; 

1.1.8 使用本地MR

说明

如果sql执行需要的数据量很小，那么使用本地mr的效率要比提交到Hadoop集群中运行快很多

用法示例

set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=50000000;
set hive.exec.mode.local.auto.tasks.max=10;

1.1.9 合理使用动态分区

说明

动态分区，是指对分区表Insert数据时候，会自动根据分区字段的值，将数据插入到相应的分区。
应根据实际情况，合理设置分区个数，分区数不易太多

用法示例

SET hive.exec.dynamic.partition=true;  
SET hive.exec.dynamic.partition.mode=nonstrict; 
SET hive.exec.max.dynamic.partitions.pernode = 1000;
SET hive.exec.max.dynamic.partitions=1000;

1.1.10 设置map 和 reduce 的内存大小

# map
set mapreduce.map.memory.mb=4096;
set mapreduce.map.java.opts=-Xmx3600m;

# reduce
set mapreduce.reduce.memory.mb=5000;
set mapreduce.reduce.java.opts=-Xmx3600m;

1.1.11 控制map个数

说明

1、map task的个数的决定因子： input的文件总个数、input的文件大小、集群设置的文件块大小(目前为128M, 可在hive中通过set dfs.block.size 查看）
2、map个数不是越多越好，要根据具体情况减少或者增加map task的个数

用法示例1

#减少map task个数
#如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。这种情况下，需要优化减少map task的个数。
--合并小文件
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;

#CombineHiveInputFormat，这个参数表示执行前进行小文件合并，
#前面三个参数确定合并文件块的大小，文件块大小大于128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），再进行合并

用法示例2

--增加map task个数
--当输入文件很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
--正常情况
Select data_desc,
count(1),
count(distinct id),
sum(case when …),
sum(case when …),
sum(…)
from a group by data_desc

--如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个---文件合理的拆分成多个，这样就可以用多个map任务去完成。

--优化后

--这里使用distribute by将数据随机分配给Reduce，这样可以使每个Reduce处理的数据大体一致
set mapred.reduce.tasks=10;
create table a_1 as
select * from a
distribute by rand(123);
--将a表的记录，随机的分散到包含10个文件的a_1表，再基于a_1表进行计算
Select data_desc,
count(1),
count(distinct id),
sum(case when …),
sum(case when …),
sum(…)
from a_1 group by data_desc

1.1.12 设置reduce个数

说明

1、reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会基于以下两点自动确定一个reduce个数：
参数1 = hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量）
参数2 = hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
计算reducer数的公式很简单N = min(参数2，总输入数据量/参数1)
2、reduce个数并不是越多越好
如果reduce太少，数据量很大时，会导致reduce异常的慢，从而导致任务运行时间长，影响依赖任务执行延迟，也可能会OOM；
如果reduce太多，产生的小文件太多，合并起来代价太高，对namenode的内存占用也会增大；
3、除非必要，或者数据量比较小，尽量避免使用单个reduce
Order by 和 笛卡尔积 会导致只有一个reduce task。

用法示例1

-- 1KB= 1,024 bytes
--每个reducer大小默认1G，输入文件为10G则会起10个reduce
set hive.exec.reducers.bytes.per.reducer=20000;
--查询sql
select user_id,count(1) as cnt from udata group by user_id order by cnt desc limit 10;
--reduce个数为99个

-- 设置这个max会影响最终的reduce的数量
set hive.exec.reducers.max = 10;
set hive.exec.reducers.bytes.per.reducer=20000;
select user_id,count(1) as cnt from udata group by user_id order by cnt desc limit 10;
--最终的输出：number of reducers: 10，reduce从99个变成最多10个

用法示例2

set mapreduce.job.reduces = 5;
select user_id,count(1) as cnt from udata group by user_id order by cnt desc limit 10;
--最终：number of reducers: 5 

1.1.13 输出自动merge小文件

set hive.merge.mapfiles = true;
--在只有map的作业结束时合并小文件

set hive.merge.mapredfiles = true; 
--在Map-Reduce的任务结束时合并小文件, 默认为False；

set hive.merge.size.per.task = 256000000; 
--合并后每个文件的大小，默认256000000

set hive.merge.smallfiles.avgsize=256000000; 
--当输出文件的平均大小小于该值时并且（mapfiles和mapredfiles为true）

1.2 hive on spark

1.2.1 注：

和hive on mr最大区别在于，任务基于spark 计算引擎执行。上述1.1.1、1.1.2、1.1.3、1.1.5、1.1.6中的优化策略在这里也适用。
此外，要通过调整spark 参数（driver 端、executor端等），对任务消耗的资源进行设置。
默认未开启动态资源分配的情况下，每个hive job能消耗的最大资源也是不变的。

1.2.2 参数设置：

set spark.executor.memory=8g;   
--Amount of memory to use per executor process：分配给每个executor的内存

set spark.executor.instances=20; 
--spark 应用启动的executor个数

set spark.executor.cores=4;
--每个executor 占用的核数，表示一个executor并行执行task的最大个数

set spark.executor.memoryOverhead=1536m;
--executor 堆外内存

set spark.driver.memory = 4g
--该参数用于设置Driver进程的内存

set spark.default.parallelism = 200
--spark core中下游reduce task任务并行度

spark.sql.shuffle.partitions = 200
--spark sql中下游reduce task任务并行度

spark.storage.memoryFraction

spark.shuffle.memoryFraction

1.3 hive on tez

暂无

2 离线计算Spark

后续补充…