Hive SQL經(jīng)典優(yōu)化案例

SELECT?dt?as?DATA_DATE,STRATEGY,AB_GROUP,SOURCE,    count(distinct case when lower(event) not like '%push%' and event!='corner_mark_show' then udid else null end) as DAU,    count(case when event='client_show' then 1 else null end) as TOTAL_VSHOW,    count(distinct case when event='client_show' then vid else null end) as TOTAL_VIDEO_VSHOW,    count(case when event='video_play' then 1 else null end) as TOTAL_VV_VP,    count(distinct case when event='video_play' then udid else null end) as TOTAL_USERS_VP,    count(case when event='effective_play' then 1 else null end) as TOTAL_VV_EP,    count(distinct case when event='effective_play' then udid else null end) as TOTAL_USERS_EP,    sum(case when event='video_over' then duration else 0 end) as TOTAL_DURATION,    count(case when event='video_over' then 1 else null end) as TOTAL_VOVER,    sum(case when event='video_over' then play_cnts else 0 end) as TOTAL_VOVER_PCNTS,    count(case when event='push_video_clk' then 1 else null end) as TOTAL_PUSH_VC,    count(distinct case when event='app_start' and body_source = 'push' then udid else null end) as TOTAL_PUSH_START,    count(case when event='post_comment' then 1 else null end) as TOTAL_REPLY,    count(distinct case when event='post_comment' then udid else null end) as TOTAL_USERS_REPLY    FROM dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zklgroup?by?dt,strategy,ab_group,source;

jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> select count(*) from dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl;

jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> select?count(distinct?dt)?as?dis_dt,?count(distinct?strategy)?as?dis_strategy,?count(distinct?ab_group)?as?dis_ab_group,?count(distinct?source)?as?dis_sourcefrom dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl;

[hue@ks-hdp-client-v02 10:55:08 /usr/local/hue]$ pythonPython 2.7.12 (default, Dec  4 2017, 14:50:18)[GCC 5.4.0 20160609] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> 2*14*722016-- 2016 個分區(qū)還可以接受。

show create table dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl;

jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> show create table dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl;

create external table `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_lym`(  event string,  udid string,  vid string,  duration string,  body_source string,  play_cnts string)PARTITIONED BY (  dt string,  source string,  strategy string,  ab_group string);

insert into `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_lym` partition(dt,source,strategy,ab_group)select event,udid,vid,duration,body_source,play_cnts,dt,source,strategy,ab_groupfrom `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl`;

create?table?bi_tmp.aloha_UserLoyalty_190301_190303?as?    select aid, imei, idfa, udid, event, duration, dt, time_local, hour, source,         first_value(time_local) over(partition by udid, event order by time_local) as first_time,        last_value(time_local) over(partition by udid, event order by time_local) as last_time,        count(time_local) over(partition by udid, event, dt) as event_count_per_day,        sum(duration) over(partition by udid, event, dt) as event_duration_each_day    from dwb_v8sp.event_column_info_new_hour    where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment',         'comment_success')????????and?dt?>=?'2019-03-01'?and?dt?<=?'2019-03-03';
select count(*) from dwb_v8sp.event_column_info_new_hourwhere event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success')and dt >= '2019-03-01' and dt <= '2019-03-03';

select count(distinct event) as dis_eventfrom dwb_v8sp.event_column_info_new_hourwhere event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success')and dt >= '2019-03-01' and dt <= '2019-03-03';

-- drop table if exists bi_tmp.zyt1;create table bi_tmp.zyt1 partitioned by(event)asselect udid,        min(time_local) as first_time,       max(time_local) as last_time,       eventfrom dwb_v8sp.event_column_info_new_hourwhere event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success')and dt >= '2019-03-01' and dt <= '2019-03-03'group by udid, event;
-- drop table if exists bi_tmp.zyt2 purge;create table bi_tmp.zyt2 partitioned by(dt,event)asselect udid,        count(time_local) as event_count_per_day,       sum(duration) as event_duration_each_day,       dt,       eventfrom dwb_v8sp.event_column_info_new_hourwhere event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success')and dt >= '2019-03-01' and dt <= '2019-03-03'group by udid, dt, event;
create table bi_tmp.zyt3 partitioned by(dt,event)as select aid, imei, idfa, udid, duration, time_local, hour, source, dt, eventfrom dwb_v8sp.event_column_info_new_hour t3    where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment',         'comment_success')        and dt >= '2019-03-01' and dt <= '2019-03-03';
-- 插入目標(biāo)表：create table bi_tmp.aloha_UserLoyalty_190301_190303 as     select t3.aid, t3.imei, t3.idfa, t3.udid, t3.event, t3.duration, t3.dt, t3.time_local, t3.hour, t3.source,         t1.first_time,        t1.last_time,        t2.event_count_per_day,        t2.event_duration_each_day    from bi_tmp.zyt1 t1 join bi_tmp.zyt2 t2 on t1.event=t2.event and t1.udid=t2.udid    join bi_tmp.zyt3 t3 on t2.dt=t3.dt and t2.event= t3.event and t2.udid=t3.udid;
-- 驗證數(shù)據(jù)：（與上面的查詢記錄行數(shù)對的上)