hive传参

SET start_day=case when '${start_day}'='null' then '$[time(yyyy-MM-dd,-3d)]' else '${start_day}' end;    --3天前的
SET end_day=case when '${end_day}'='null' then '$[time(yyyy-MM-dd,-1d)]' else '${end_day}' end;    
SET start_par=regexp_replace(${hiveconf:start_day}, '-', '');     -- 可以调用hive函数。也可以调平台的参数'$[time(yyyyMMdd,-3d)]'免掉去-横杠字符
SET end_par=regexp_replace(${hiveconf:end_day}, '-', '');
SET v_last_day=regexp_replace(date_add(substr(current_timestamp(),1,10),-1),'-',''); --昨天的日期

 
select ${hiveconf:start_day} as start_day,
     ${hiveconf:end_day} as end_day,
     ${hiveconf:start_par} as start_par,
     ${hiveconf:end_par} as end_par,
     ${hiveconf:v_last_day} as v_last_day
     ;

 

SET v_start_dt=case when '${v_start_dt}'='null' then '$[time(yyyy-MM-dd,-1d)]' else '${v_start_dt}' end;    --滚动刷新1天前的
SET v_end_dt=case when '${v_end_dt}'='null' then '$[time(yyyy-MM-dd,+1d)]' else '${v_end_dt}' end;      --调度时间当天+1天，inc_day也为调度当天-1天。 2018-0827调度：v_end_dt为2018-08-26
SET v_start_inc=regexp_replace(${hiveconf:v_start_dt}, '-', '');
SET v_end_inc=regexp_replace(${hiveconf:v_end_dt}, '-', '');
SET v_last_day=regexp_replace(date_add(substr(current_timestamp(),1,10),-1),'-',''); --昨天的日期

 
select ${hiveconf:v_start_dt} as v_start_dt,
     ${hiveconf:v_end_dt} as v_end_dt,
     ${hiveconf:v_start_inc} as v_start_inc,
     ${hiveconf:v_end_inc} as v_end_inc,
     ${hiveconf:v_last_day} as v_last_day
     ;


--中转班次明细表
DROP TABLE IF EXISTS tmp_dm_op.pass_zhc_7188_his_peak_sum_transfer_batch_info_tmp1;
CREATE TABLE tmp_dm_op.pass_zhc_7188_his_peak_sum_transfer_batch_info_tmp1  stored as parquet as
SELECT
    report_dt,            --班次日期    YYYY-MM-DD        
    operate_zone_code,    --操作网点            
    batch_index,        --序号            
    batch_code,            --班次编码
    batch_cost_s/3600 as batch_h,        --班次时长 h
    case when batch_cost_s/60 >=0 and batch_cost_s/60 < 30 then (batch_cost_s/60-5)/60
         when batch_cost_s/60 >=30 and batch_cost_s/60 < 90 then (batch_cost_s/60-25)/60
         when batch_cost_s/60 >=90 and batch_cost_s/60 < 180 then (batch_cost_s/60-30)/60
         when batch_cost_s/60 >=180  then (batch_cost_s/60-40)/60
        end as transfer_h,                 --中转时长 h
    fm_tm,                --班次开始时间(YYYY-MM-DD HH24:MI:SS)            
    end_arrive_tm,        --最晚到达时间(YYYY-MM-DD HH24:MI:SS)            
    to_tm                --班次结束时间(YYYY-MM-DD HH24:MI:SS)
FROM
    (SELECT
        report_dt,            --班次日期    YYYY-MM-DD        
        operate_zone_code,    --操作网点            
        batch_index,        --序号            
        batch_code,            --班次编码
        (unix_timestamp(to_tm) - unix_timestamp(fm_tm)) as batch_cost_s,        --班次耗时 s秒
        fm_tm,                --班次开始时间(YYYY-MM-DD HH24:MI:SS)            
        end_arrive_tm,        --最晚到达时间(YYYY-MM-DD HH24:MI:SS)            
        to_tm                --班次结束时间(YYYY-MM-DD HH24:MI:SS)
    FROM dm_oxx.dw_transxxxx_info  t 
    WHERE inc_day>=regexp_replace(date_add(${hiveconf:v_start_dt}, -2), '-', '')         --T-3
        and inc_day<=regexp_replace(date_add(${hiveconf:v_end_dt}, 0), '-', '')            --T+1
    ) t1
;