bus_mysql_to_hdfs.sh 3.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115
  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. SAGA_DATABASE=sagacloud_customization
  11. url=$SAGA_URL_PRE$SAGA_DATABASE$SAGA_URL_SUF
  12. username=$SAGA_USER
  13. password=$SAGA_PASSWORD
  14. echo "$do_date"
  15. mysql_to_hdfs_lzo() {
  16. sqoop import \
  17. -D mapred.job.queue.name=default \
  18. --connect "$1" \
  19. --username "$2" \
  20. --password "$3" \
  21. --target-dir "$4""$7"/"$do_date" \
  22. --delete-target-dir \
  23. --columns "$5" \
  24. --query "$6 and \$CONDITIONS" \
  25. --num-mappers 1 \
  26. --hive-drop-import-delims \
  27. --fields-terminated-by '\001' \
  28. --compress \
  29. --compression-codec lzo \
  30. --hive-import \
  31. --hive-database saga_dw \
  32. --hive-table "$7" \
  33. --hive-overwrite \
  34. --hive-partition-key dt \
  35. --hive-partition-value "$do_date" \
  36. --null-string '\\N' \
  37. --null-non-string '\\N'
  38. }
  39. mysql_to_hdfs_lzo_no_partition() {
  40. sqoop import \
  41. -D mapred.job.queue.name=default \
  42. --connect "$1" \
  43. --username "$2" \
  44. --password "$3" \
  45. --target-dir "$4""$7" \
  46. --delete-target-dir \
  47. --columns "$5" \
  48. --query "$6 and \$CONDITIONS" \
  49. --num-mappers 1 \
  50. --hive-drop-import-delims \
  51. --fields-terminated-by '\001' \
  52. --compress \
  53. --compression-codec lzo \
  54. --hive-import \
  55. --hive-database saga_dw \
  56. --hive-table "$7" \
  57. --hive-overwrite \
  58. --null-string '\\N' \
  59. --null-non-string '\\N'
  60. }
  61. ## 空调操作记录表
  62. ods_feedback(){
  63. mysql_to_hdfs_lzo "$url" "$username" "$password" /warehouse/saga_dw/ods/tmp/ \
  64. "id, project_id, object_id, source_type, user_id, user_phone, user_name, value_type, item_id, value, create_time, next_open_time, model, duration_type, custom_plan, curr_temp, nick_name, result, exe_result, fb_temp, remark" \
  65. "select id, project_id, object_id, source_type, user_id, user_phone, user_name, value_type, item_id, value, create_time, next_open_time, model, duration_type, custom_plan, curr_temp, nick_name, result, exe_result, fb_temp, remark from feedback where date_format(create_time, '%Y-%m-%d') = '$do_date' and 1 = 1 " \
  66. ods_bus_feedback
  67. }
  68. ods_feedback_tmp(){
  69. mysql_to_hdfs_lzo_no_partition "$url" "$username" "$password" /warehouse/saga_dw/ods/tmp/ \
  70. "id, project_id, object_id, source_type, user_id, user_phone, user_name, value_type, item_id, value, create_time, next_open_time, model, duration_type, custom_plan, curr_temp, nick_name, result, exe_result, fb_temp, remark" \
  71. "select id, project_id, object_id, source_type, user_id, user_phone, user_name, value_type, item_id, value, create_time, next_open_time, model, duration_type, custom_plan, curr_temp, nick_name, result, exe_result, fb_temp, remark from feedback where 1 = 1 " \
  72. ods_bus_feedback_tmp
  73. }
  74. # 照明操作记录
  75. ods_light_switch(){
  76. mysql_to_hdfs_lzo "$url" "$username" "$password" /warehouse/saga_dw/ods/tmp/ \
  77. "id, project_id, object_id, source_type, user_id, user_phone, user_name, on_off, result, create_time, exe_result" \
  78. "select id, project_id, object_id, source_type, user_id, user_phone, user_name, on_off, result, create_time, exe_result from light_switch where date_format(create_time, '%Y-%m-%d') = '$do_date' and 1 = 1 " \
  79. ods_bus_light_switch
  80. }
  81. ods_light_switch_tmp(){
  82. mysql_to_hdfs_lzo_no_partition "$url" "$username" "$password" /warehouse/saga_dw/ods/tmp/ \
  83. "id, project_id, object_id, source_type, user_id, user_phone, user_name, on_off, result, create_time, exe_result" \
  84. "select id, project_id, object_id, source_type, user_id, user_phone, user_name, on_off, result, create_time, exe_result from light_switch where 1 = 1 " \
  85. ods_bus_light_switch_tmp
  86. }
  87. case $1 in
  88. "all")
  89. ods_feedback
  90. ods_light_switch
  91. ;;
  92. "ods_feedback")
  93. ods_feedback
  94. ;;
  95. "ods_feedback_tmp")
  96. ods_feedback_tmp
  97. ;;
  98. "ods_light_switch")
  99. ods_light_switch
  100. ;;
  101. "ods_light_switch_tmp")
  102. ods_light_switch_tmp
  103. ;;
  104. esac