ods_to_dwd.sh 2.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103
  1. #!/bin/bash
  2. . /opt/app/bi-app/energy/dayTask/config.sh
  3. if [ -n "$2" ] ;then
  4. echo "如果是输入的日期按照取输入日期"
  5. do_date=$2
  6. else
  7. echo "====没有输入数据的日期,取当前时间的前一天===="
  8. do_date=`date -d yesterday +"%Y-%m-%d"`
  9. fi
  10. echo $do_date
  11. env_config="
  12. use saga_dw;
  13. SET mapreduce.job.queuename=default;
  14. "
  15. dwd_energy_15_min_hour="
  16. insert overwrite table saga_dw.dwd_energy_15_min_hour partition (dt = '$do_date')
  17. select building,
  18. func_id,
  19. parent_code,
  20. branch_type,
  21. branch_type_name,
  22. use_range_type,
  23. cast(value_sum as decimal(30, 15)) as value_sum ,
  24. hour_ as current_hour,
  25. current_year,
  26. year_mouth,
  27. week_of_year
  28. from (
  29. select building,
  30. func_id,
  31. parent_code,
  32. branch_type,
  33. branch_type_name,
  34. use_range_type,
  35. dt,
  36. t1.hour_,
  37. (ap_sum - al_sum) value_sum
  38. from (
  39. select building,
  40. func_id,
  41. 'AP1' parent_code,
  42. 'ALU' as branch_type,
  43. '其他' as branch_type_name,
  44. 'OTHER' use_range_type,
  45. sum(if((branch_type == 'AP' and use_range_type == '1'), data_value, 0)) ap_sum,
  46. sum(if((branch_type == 'AL' and use_range_type in (2, 3, 4, 6, 7)), data_value, 0)) al_sum,
  47. dt,
  48. o15mh.hour_
  49. from (
  50. select building,
  51. func_id,
  52. meter,
  53. data_time,
  54. substr(data_time, 0, 13) hour_,
  55. data_value,
  56. dt
  57. from ods_energy_15_min oe15m
  58. where dt = '$do_date'
  59. ) o15mh
  60. left join dim_office_meter dom on o15mh.meter = dom.meter
  61. where dt = '$do_date' and branch_type in ('AP', 'AL')
  62. group by building, func_id, dt, o15mh.hour_ ) t1
  63. union
  64. select building,
  65. func_id,
  66. parent_code,
  67. branch_type,
  68. branch_type_name,
  69. use_range_type,
  70. dt,
  71. o15mh.hour_,
  72. sum(data_value) value_sum
  73. from (
  74. select building,
  75. func_id,
  76. meter,
  77. data_time,
  78. substr(data_time, 0, 13) hour_,
  79. data_value,
  80. dt
  81. from ods_energy_15_min oe15m
  82. where dt = '$do_date'
  83. ) o15mh
  84. left join dim_office_meter dom2 on o15mh.meter = dom2.meter
  85. group by building, func_id, parent_code, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_
  86. ) teh
  87. left join dim_date_day ddd on dt = date_id
  88. order by current_hour;"
  89. insert_into_table(){
  90. case $1 in
  91. "all")
  92. hive -e "$env_config$dwd_energy_15_min_hour"
  93. ;;
  94. esac
  95. }
  96. insert_into_table $1