mysql_to_hdfs.sh 3.0 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798
  1. #!/bin/bash
  2. . /usr/local/service/sagaApps/bi_saga/dwh_saga/task_day/config.sh
  3. if [ -n "$2" ] ;then
  4. do_date=$2
  5. else
  6. echo "====没有输入数据的日期,取当前时间的前一天===="
  7. do_date=$(date -d yesterday +"%Y-%m-%d")
  8. fi
  9. url=$MYSQL_URL
  10. username=$MYSQL_USER
  11. password=$MYSQL_PASSWORD
  12. mysql_to_hdfs_lzo() {
  13. /usr/local/service/sqoop/bin/sqoop import \
  14. -D mapred.job.queue.name=default \
  15. --connect $1 \
  16. --username $2 \
  17. --password $3 \
  18. --target-dir $4$7/$do_date \
  19. --delete-target-dir \
  20. --columns "$5" \
  21. --query "$6 and \$CONDITIONS" \
  22. --num-mappers 1 \
  23. --hive-drop-import-delims \
  24. --fields-terminated-by '\001' \
  25. --compress \
  26. --compression-codec lzo \
  27. --hive-import \
  28. --hive-database saga_dw \
  29. --hive-table $7 \
  30. --hive-overwrite \
  31. --hive-partition-key dt \
  32. --hive-partition-value $do_date \
  33. --null-string '\\N' \
  34. --null-non-string '\\N'
  35. }
  36. ## 能源 15 分钟差值
  37. ods_energy_15_min(){
  38. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, from_unixtime(unix_timestamp(data_time) + 28800) as data_time, data_value from energy_15_min where 1 = 1 " ods_energy_15_min
  39. }
  40. ## CO2 15 分钟分精度
  41. ods_co2_15_min(){
  42. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, data_time, data_value from co2_15_min where dt = '$do_date'" ods_co2_15_min
  43. }
  44. ## PM2.5 15 分钟分精度
  45. ods_pm25_15_min(){
  46. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, data_time, data_value from pm25_15_min where dt = '$do_date'" ods_pm25_15_min
  47. }
  48. ## 甲醛 15 分钟分精度
  49. ods_hcho_15_min(){
  50. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, data_time, data_value from hcho_15_min where dt = '$do_date'" ods_hcho_15_min
  51. }
  52. ## 温度 15 分钟分精度
  53. ods_temperature_15_min(){
  54. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, data_time, data_value from temperature_15_min where dt = '$do_date'" ods_temperature_15_min
  55. }
  56. ## 湿度 15 分钟分精度
  57. ods_humidity_15_min(){
  58. mysql_to_hdfs_lzo "$url" "$username" "$password" /saga/saga_dw/ods/tmp/ "building, func_id, meter, data_time, data_value" "select building, func_id, meter, data_time, data_value from humidity_15_min where dt = '$do_date'" ods_humidity_15_min
  59. }
  60. case $1 in
  61. "all")
  62. ods_energy_15_min
  63. ods_co2_15_min
  64. ods_pm25_15_min
  65. ods_hcho_15_min
  66. ods_temperature_15_min
  67. ods_humidity_15_min
  68. ;;
  69. "ods_energy_15_min")
  70. ods_energy_15_min
  71. ;;
  72. "ods_co2_15_min")
  73. ods_co2_15_min
  74. ;;
  75. "ods_pm25_15_min")
  76. ods_pm25_15_min
  77. ;;
  78. "ods_hcho_15_min")
  79. ods_hcho_15_min
  80. ;;
  81. "ods_temperature_15_min")
  82. ods_temperature_15_min
  83. ;;
  84. "ods_humidity_15_min")
  85. ods_humidity_15_min
  86. ;;
  87. esac