ods_to_dwd.sh 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495
  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. branch_type,
  20. branch_type_name,
  21. cast(value_sum as decimal(30, 15)) as value_sum ,
  22. hour_ as current_hour,
  23. current_year,
  24. year_mouth,
  25. week_of_year
  26. from (
  27. select building,
  28. func_id,
  29. branch_type,
  30. branch_type_name,
  31. dt,
  32. t1.hour_,
  33. (ap_sum - al_sum) value_sum
  34. from (
  35. select building,
  36. func_id,
  37. 'ALU' as branch_type,
  38. '其他' as branch_type_name,
  39. sum(if((branch_type == 'AP' and use_range_type == '1'), data_value, 0)) ap_sum,
  40. sum(if((branch_type == 'AL' and use_range_type in (2, 3, 4, 6, 7)), data_value, 0)) al_sum,
  41. dt,
  42. o15mh.hour_
  43. from (
  44. select building,
  45. func_id,
  46. meter,
  47. data_time,
  48. substr(data_time, 0, 13) hour_,
  49. data_value,
  50. dt
  51. from ods_energy_15_min oe15m
  52. where dt = '$do_date'
  53. ) o15mh
  54. left join dim_office_meter dom on o15mh.meter = dom.meter
  55. where dt = '$do_date' and branch_type in ('AP', 'AL')
  56. group by building, func_id, dt, o15mh.hour_ ) t1
  57. union
  58. select building,
  59. func_id,
  60. concat_ws('-', branch_type, use_range_type),
  61. branch_type_name,
  62. dt,
  63. o15mh.hour_,
  64. sum(data_value) value_sum
  65. from (
  66. select building,
  67. func_id,
  68. meter,
  69. data_time,
  70. substr(data_time, 0, 13) hour_,
  71. data_value,
  72. dt
  73. from ods_energy_15_min oe15m
  74. where dt = '$do_date'
  75. ) o15mh
  76. left join dim_office_meter dom2 on o15mh.meter = dom2.meter
  77. group by building, func_id, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_
  78. ) teh
  79. left join dim_date_day ddd on dt = date_id
  80. order by current_hour;"
  81. insert_into_table(){
  82. case $1 in
  83. "all")
  84. hive -e "$env_config$dwd_energy_15_min_hour"
  85. ;;
  86. esac
  87. }
  88. insert_into_table $1