Hive 任务调优实践总结

set mapreduce.input.fileinputformat.split.minsize=4096000000;
set mapreduce.input.fileinputformat.split.maxsize=4096000000;
set mapred.reduce.tasks=1000;    
set hive.exec.reducers.max=1000;
SELECT user_id, county FROM
(
SELECT u as user_id, 
  bi_warehouse_dwd.ip_parse(ip).gb_id as county, 
    row_number() over(partition by u order by dm desc) as rank
FROM udw.tmp_play
WHERE
  dt >= '2022-03-14'
    and bi_warehouse_dwd.ip_parse(ip).gb_id is not null
) a
WHERE rank = 1

set mapreduce.input.fileinputformat.split.minsize=16384000000;
set mapreduce.input.fileinputformat.split.maxsize=16384000000;
set mapred.reduce.tasks=1000;    
set hive.exec.reducers.max=1000;
set mapreduce.reduce.shuffle.memory.limit.percent=0.1;
set mapreduce.reduce.shuffle.input.buffer.percent=0.2;
select a.u,a.county
from (SELECT u, 
  dm,
    bi_warehouse_dwd.ip_parse(ip).gb_id as county
FROM udw.tmp_play
WHERE 
    dt >= '2022-03-14'
  and bi_warehouse_dwd.ip_parse(ip).gb_id is not null) a
  join (
SELECT u, 
    max(dm) as dm
FROM udw.tmp_play
WHERE 
    dt >= '2022-03-14' group by u) b on a.u=b.u and a.dm=b.dm;