daily pastebin goal
51%
SHARE
TWEET

Untitled

a guest Feb 17th, 2019 47 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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}/*"
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top