ods_to_dwd.sh 2.5 KB

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