ods_to_dwd.sh 2.7 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798
  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 hive.vectorized.execution.enabled=false;
  14. SET mapreduce.job.queuename=default;
  15. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
  16. SET hive.exec.dynamic.partition.mode=nonstrict;"
  17. dwd_energy_15_min_hour="
  18. insert overwrite table saga_dw.dwd_energy_15_min_hour partition (dt)
  19. select building,
  20. func_id,
  21. branch_type,
  22. branch_type_name,
  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. dt
  29. from (
  30. select building,
  31. func_id,
  32. branch_type,
  33. branch_type_name,
  34. dt,
  35. t1.hour_,
  36. (ap_sum - al_sum) value_sum
  37. from (
  38. select building,
  39. func_id,
  40. 'ALU' as branch_type,
  41. '其他' as branch_type_name,
  42. sum(if((branch_type == 'AP' and use_range_type == '1'), data_value, 0)) ap_sum,
  43. sum(if((branch_type == 'AL' and use_range_type in (2, 3, 4, 6, 7)), data_value, 0)) al_sum,
  44. dt,
  45. o15mh.hour_
  46. from (
  47. select building,
  48. func_id,
  49. meter,
  50. data_time,
  51. substr(data_time, 0, 13) hour_,
  52. data_value,
  53. dt
  54. from ods_energy_15_min oe15m
  55. where dt >= '2022-01-01'
  56. ) o15mh
  57. left join dim_office_meter dom on o15mh.meter = dom.meter
  58. where dt >= '2022-01-01' and branch_type in ('AP', 'AL')
  59. group by building, func_id, dt, o15mh.hour_ ) t1
  60. union
  61. select building,
  62. func_id,
  63. concat_ws('-', branch_type, use_range_type),
  64. branch_type_name,
  65. dt,
  66. o15mh.hour_,
  67. sum(data_value) value_sum
  68. from (
  69. select building,
  70. func_id,
  71. meter,
  72. data_time,
  73. substr(data_time, 0, 13) hour_,
  74. data_value,
  75. dt
  76. from ods_energy_15_min oe15m
  77. where dt >= '2022-01-01'
  78. ) o15mh
  79. left join dim_office_meter dom2 on o15mh.meter = dom2.meter
  80. group by building, func_id, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_
  81. ) teh
  82. left join dim_date_day ddd on dt = date_id
  83. order by current_hour;"
  84. insert_into_table(){
  85. case $1 in
  86. "all")
  87. hive -e "$env_config$dwd_energy_15_min_hour"
  88. ;;
  89. esac
  90. }
  91. insert_into_table $1