SQL真香

create table bucketed_user(
    id int,
    name string
)
clustered by (id) into 4 buckets

a.高效采样，数据是hash过的，从一个桶取跟从所有数据取几乎一毛一样

b.获得更好的查询处理效率，比如，join时候知道key对应到哪个桶，就能直接取

1.Map数量不是单纯由用户设置的，是由InputFormat接口的getSplits方法决定；  
每个map处理一个fileSplit，即 map数=fileSplit数；
  
逻辑为：

（源码贴了会看？直接上干货!）

分片大小：splitSize = max (minSize, min(goalSize, dfs.block.size))

其中：

goalSize = totalSize / mapred.map.tasks
minSize = max {mapred.min.split.size, minSplitSize}

totalSize：一个JOB的所有map总的输入大小
mapred.map.tasks：默认2，期望map数
minSplitSize：默认为1，可复写（特殊情况）
dfs.block.size：1.X默认64M，2.X默认128M

Map数量：
文件大小/splitSize>1.1，创建一个split，即一个map task；
文件剩余大小/splitSize<1.1，剩余的部分作为一个split；

结论：
通过调节 mapred.map.tasks（期望map数）和 mapred.min.split.size（最小文件分割大小）
可实际实现 map数量 的控制；

实际应用：
想增加map个数，设置 mapred.map.tasks 为一个较大的值，如map长尾问题；
想减小map个数，设置 mapred.min.split.size 为一个较大的值，如小文件过多；

2.Hive小文件合并（设置参数即可）

map-only的任务结束时合并小文件：
sethive.merge.mapfiles = true
  
在Map-Reduce的任务结束时合并小文件：
sethive.merge.mapredfiles= true
  
当输出文件的平均大小小于1GB时，启动一个独立的map-reduce任务进行文件merge：
sethive.merge.smallfiles.avgsize=1024000000

3.Spark小文件合并

之前写过干货：https://wyk2011fj.github.io/2018/11/spark-small-file/

4.distribute by、sort by

distribute by a 表面map端的结果会按照a字段分发到reduce(默认为hash)；
sort by 在每个reducer端都会做排序，最后做归并排序（order by只用一个reduce总排序）；

1.Common join

Map阶段
读取源表的数据，Map输出时候以Join on条件中的列为key；
Map输出的value(含所属表信息)为join之后所关心的(select或者where中需要用到的)列；
按照key进行排序；

Shuffle阶段
根据key的值进行hash,并将key/value按照hash值推送至不同的reduce中；
（每个map都可能有所有的key）        

Reduce阶段
根据key的值完成join操作。

2.MapJoin

原理：
把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，即完成join操作；

相关设置：
小表自动选择Mapjoin: hive.auto.convert.join=true
小表阈值:hive.mapjoin.smalltable.filesize (默认25M)
(下面两个不常用)
mapjoin做group by时可以使用的最大内存占比：
hive.mapjoin.followby.gby.localtask.max.memory.usage （默认0.55）
本地mapjoin可以使用内存占比：hive.mapjoin.localtask.max.memory.usage （默认0.9）

1.hive coalesce函数

非空查找函数；

COALESCE(T v1, T v2,…)，返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL；

demo:COALESCE(null,100,50)=100,COALESCE(null,null,50)=50

insert table hot_products_tmp
select
      distinct p_id
from
    (
        select
            p_id,
            count(1) as pv
        from log
        group by p_id
    ) a
where pv >= 10000

select /*+mapjoin(b)*/ a.p_id,a.pv
from (
    select p_id
        ,  count(1) as pv
    from log
) a
left outer join (
    select p_id
    from hot_products_tmp
) b
on a.p_id = b.p_id	    
and b.p_id is null

select /*+mapjoin(d)*/ c.p_id,c.pv
from (
    select p_id
        ,  count(1) as pv
    from log
) c
join (
    select p_id
    from hot_products_tmp
) d
on c.p_id = d.p_id

select d1
    ,  d2
    ,  count(distinct case 
       when a is not null then b
       end) as b_distinct_cnt
from xxx
group by d1,d2

create table tmp1
as
select d1,d2,b,
count( case when a is not null then b end ) as b_cnt
from xxx
group by d1, d1, b

select d1,d2,
sum(case when b_cnt > 0 then 1 else 0 end) as b_distinct_cnt
from tmp1
group by d1,d2