dwh_saga/task_day/ods_to_dwd.sh

#!/bin/bash

. /usr/local/service/sagaApps/bi_saga/dwh_saga/task_day/config.sh

if [ -n "$2" ] ;then
    do_date=$2
else
    echo "====没有输入数据的日期，取当前时间的前一天===="
    do_date=$(date -d yesterday +"%Y-%m-%d")
fi
echo "$do_date"

env_config="
use saga_dw;
SET mapreduce.job.queuename=default;
"

dwd_energy_15_min_hour="
insert overwrite table saga_dw.dwd_energy_15_min_hour partition (dt = '$do_date')
select building,
       func_id,
       parent_code,
       branch_type,
       branch_type_name,
       use_range_type,
       cast(value_sum as decimal(30, 15)) as value_sum ,
       hour_ as current_hour,
       current_year,
       year_mouth,
       week_of_year
from (
    select building,
           func_id,
           parent_code,
           branch_type,
           branch_type_name,
           use_range_type,
           dt,
           t1.hour_,
           (ap_sum - al_sum) value_sum
    from (
      select building,
             func_id,
             'AP1' parent_code,
             'ALU' as branch_type,
             '其他' as branch_type_name,
             'OTHER' use_range_type,
             sum(if((branch_type == 'AP' and use_range_type == '1'), data_value, 0)) ap_sum,
             sum(if((branch_type == 'AL' and use_range_type in (2, 3, 4, 6, 7)), data_value, 0)) al_sum,
             dt,
             o15mh.hour_
      from (
          select building,
               func_id,
               meter,
               data_time,
               substr(data_time, 12, 2) hour_,
               data_value,
               dt
        from ods_energy_15_min oe15m
        where dt = '$do_date'
           ) o15mh
          left join dim_office_meter dom on o15mh.meter = dom.meter
      where dt = '$do_date' and  branch_type in ('AP', 'AL')
      group by building, func_id, dt, o15mh.hour_ ) t1
    union
    select building,
           func_id,
           parent_code,
           branch_type,
           branch_type_name,
           use_range_type,
           dt,
           o15mh.hour_,
           sum(data_value) value_sum
    from (
        select building,
               func_id,
               meter,
               data_time,
               substr(data_time, 12, 2) hour_,
               data_value,
               dt
        from ods_energy_15_min oe15m
        where dt = '$do_date'
         ) o15mh
        left join dim_office_meter dom2 on o15mh.meter = dom2.meter
    group by building, func_id, parent_code, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_
     ) teh
    left join dim_date_day ddd on dt = date_id
order by current_hour;"


insert_into_table(){
    case $1 in
    "all")
      hive -e "$env_config$dwd_energy_15_min_hour"
      ;;
    esac
}

insert_into_table "$1"