Guest User

Untitled

a guest
Feb 17th, 2019
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.32 KB | None | 0 0
  1. #!/bin/bash
  2.  
  3. set -eo pipefail
  4. [ -n "${TRACE}" ] && set -x
  5.  
  6. function setup() {
  7. echo "Setting up GCP"
  8. echo "${GCP_JSON}" | base64 -d >/key.json
  9.  
  10. # Setup gcloud service account
  11. gcloud auth activate-service-account --key-file=/key.json
  12. }
  13.  
  14. if [ -n "${DEPLOYED}" ]; then
  15. setup
  16. else
  17. # Poor man's dotenv
  18. eval "$(grep -v '^#' .env | xargs)"
  19. fi
  20.  
  21. # Get list of tables into an array
  22. while IFS=$'\n' read -r line; do TABLES+=("${line}"); done < <(mysql \
  23. -Bs \
  24. -u "${DB_USER}" \
  25. -p"${DB_PASS}" \
  26. --port=3306 \
  27. -D "${DB}" \
  28. -e "SHOW TABLES;" 2>/dev/null)
  29.  
  30. mkdir -p schema
  31.  
  32. for TABLE in "${TABLES[@]}"; do
  33. FILE="${FILEBASE}/${TABLE}"
  34. GCS_REF="gs://${BUCKET}/${FILE}.csv"
  35. SCHEMA_JSON="schema/${TABLE}.json"
  36.  
  37. # Skip unwanted tables
  38. case $TABLE in
  39. migration | migrations)
  40. continue
  41. ;;
  42. esac
  43.  
  44. # This line is horrible, would love to move it to a file but it needs to
  45. # be parameterized
  46. QUERY="SELECT CONCAT('{ \"name\": \"', COLUMN_NAME, '\", \"type\": \"', CASE WHEN DATA_TYPE LIKE '%int%' THEN \"INTEGER\" WHEN DATA_TYPE LIKE 'decimal' THEN \"FLOAT\" WHEN DATA_TYPE LIKE 'datetime' THEN \"DATETIME\" WHEN DATA_TYPE LIKE 'date' THEN \"DATE\" WHEN DATA_TYPE LIKE 'float' THEN \"FLOAT\" ELSE \"STRING\" END, '\"},') AS json FROM information_schema.columns WHERE TABLE_SCHEMA = '${DB}' AND TABLE_NAME = '${TABLE}'; "
  47.  
  48. # Create our schema file
  49. echo '[' >"${SCHEMA_JSON}"
  50. mysql \
  51. -Bs \
  52. -u "${DB_USER}" \
  53. -p"${DB_PASS}" \
  54. --port=3306 \
  55. -e "$QUERY" 2>/dev/null |
  56. sed '$s/,$//' >>"${SCHEMA_JSON}"
  57. echo ']' >>"${SCHEMA_JSON}"
  58.  
  59. # Create CSV file on GCS from mysql, also trim out any CRs.
  60. # The generated file is really a TSV
  61. mysql \
  62. -Bs \
  63. -u "${DB_USER}" \
  64. -p"${DB_PASS}" \
  65. --port=3306 \
  66. -e "select * from ${DB}.${TABLE}" 2>/dev/null |
  67. tr -d '\r' |
  68. gsutil cp - "${GCS_REF}"
  69.  
  70. # Load file from the bucket into BigQuery
  71. bq --location="US" load \
  72. --field_delimiter "\t" \
  73. --null_marker "NULL" \
  74. --quote="" \
  75. --replace \
  76. --source_format=CSV \
  77. "${BQ_DATASET}"."${TABLE}" \
  78. "${GCS_REF}" \
  79. ./"${SCHEMA_JSON}"
  80. done
  81.  
  82. # Cleanup our CSV files
  83. gsutil -m rm "gs://${BUCKET}/${FILEBASE}/*"
Add Comment
Please, Sign In to add comment