hive SQL优化之distribute by和sort by

现在的位置: 首页 > 数据库 > 正文

hive SQL优化之distribute by和sort by

2019年05月08日 ⁄ 数据库 ⁄ 共 3453字 ⁄ 字号小中大 ⁄ 评论关闭

转自：http://yaoyinjie.blog.51cto.com/3189782/703873

最近在优化hiveSQL，

下面是一段排序，分组后取每组第一行记录的SQL

INSERT OVERWRITE TABLE t_wa_funnel_distinct_temp PARTITION (pt='${SRCTIME}') 
SELECT 
    bussiness_id, 
    cookie_id, 
    session_id, 
    funnel_id, 
    group_first(funnel_name) funnel_name, 
    step_id, 
    group_first(step_name) step_name, 
    group_first(log_type) log_type, 
    group_first(url_pattern) url_pattern, 
    group_first(url) url, 
    group_first(refer) refer, 
    group_first(log_time) log_time, 
    group_first(is_new_visitor) is_new_visitor, 
    group_first(is_mobile_traffic) is_mobile_traffic, 
    group_first(is_bounce) is_bounce, 
    group_first(campaign_name) campaign_name, 
    group_first(group_name) group_name, 
    group_first(slot_name) slot_name, 
    group_first(source_type) source_type, 
    group_first(next_page) next_page, 
    group_first(continent) continent, 
    group_first(sub_continent_region) sub_continent_region, 
    group_first(country) country, 
    group_first(region) region, 
    group_first(city) city, 
    group_first(language) language, 
    group_first(browser) browser, 
    group_first(os) os, 
    group_first(screen_color) screen_color, 
    group_first(screen_resolution) screen_resolution, 
    group_first(flash_version) flash_version, 
    group_first(java) java, 
    group_first(host) host 
FROM 
(   SELECT * 
    FROM r_wa_funnel  
    WHERE pt='${SRCTIME}' 
    ORDER BY bussiness_id, cookie_id, session_id, funnel_id, step_id, log_time ASC 
) t1 
GROUP BY pt, bussiness_id, cookie_id, session_id, funnel_id, step_id;

group_first: 自定义函数，用户取每组第一个字段
${SRCTIME}: 由外部oozie调度传入, 作为时间分区，精确到小时. eg: 2011.11.01.21

下面在hive上以SRCTIME =
2011.11.01.21 执行以上SQL.
2011.11.01.21 小时分区记录数有10435486

执行时间:

从上面可以看出，reduce阶段只有一个reduce，这是因为ORDER BY是全局排序，hive只能通过一个reduce进行排序

从业务需求来看，只要按bussiness_id, cookie_id, session_id, funnel_id, step_id分组，组内按
log_time升序排序即可.

OK, 这样可以采用hive提供的distribute by 和 sort by, 这样可以充分利用hadoop资源，在多个

reduce中局部按log_time 排序

优化有的hive代码:

INSERT OVERWRITE TABLE t_wa_funnel_distinct PARTITION (pt='2011.11.01.21') 
SELECT 
    bussiness_id, 
    cookie_id, 
    session_id, 
    funnel_id, 
    group_first(funnel_name) funnel_name, 
    step_id, 
    group_first(step_name) step_name, 
    group_first(log_type) log_type, 
    group_first(url_pattern) url_pattern, 
    group_first(url) url, 
    group_first(refer) refer, 
    group_first(log_time) log_time, 
    group_first(is_new_visitor) is_new_visitor, 
    group_first(is_mobile_traffic) is_mobile_traffic, 
    group_first(is_bounce) is_bounce, 
    group_first(campaign_name) campaign_name, 
    group_first(group_name) group_name, 
    group_first(slot_name) slot_name, 
    group_first(source_type) source_type, 
    group_first(next_page) next_page, 
    group_first(continent) continent, 
    group_first(sub_continent_region) sub_continent_region, 
    group_first(country) country, 
    group_first(region) region, 
    group_first(city) city, 
    group_first(language) language, 
    group_first(browser) browser, 
    group_first(os) os, 
    group_first(screen_color) screen_color, 
    group_first(screen_resolution) screen_resolution, 
    group_first(flash_version) flash_version, 
    group_first(java) java, 
    group_first(host) host 
FROM 
(   SELECT * 
    FROM r_wa_funnel  
    WHERE pt='2011.11.01.21' 
    distribute by bussiness_id, cookie_id, session_id, funnel_id, step_id sort by log_time ASC 
) t1 
GROUP BY bussiness_id, cookie_id, session_id, funnel_id, step_id;