Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/bin/bash
- set -eo pipefail
- [ -n "${TRACE}" ] && set -x
- function setup() {
- echo "Setting up GCP"
- echo "${GCP_JSON}" | base64 -d >/key.json
- # Setup gcloud service account
- gcloud auth activate-service-account --key-file=/key.json
- }
- if [ -n "${DEPLOYED}" ]; then
- setup
- else
- # Poor man's dotenv
- eval "$(grep -v '^#' .env | xargs)"
- fi
- # Get list of tables into an array
- while IFS=$'\n' read -r line; do TABLES+=("${line}"); done < <(mysql \
- -Bs \
- -u "${DB_USER}" \
- -p"${DB_PASS}" \
- --port=3306 \
- -D "${DB}" \
- -e "SHOW TABLES;" 2>/dev/null)
- mkdir -p schema
- for TABLE in "${TABLES[@]}"; do
- FILE="${FILEBASE}/${TABLE}"
- GCS_REF="gs://${BUCKET}/${FILE}.csv"
- SCHEMA_JSON="schema/${TABLE}.json"
- # Skip unwanted tables
- case $TABLE in
- migration | migrations)
- continue
- ;;
- esac
- # This line is horrible, would love to move it to a file but it needs to
- # be parameterized
- 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}'; "
- # Create our schema file
- echo '[' >"${SCHEMA_JSON}"
- mysql \
- -Bs \
- -u "${DB_USER}" \
- -p"${DB_PASS}" \
- --port=3306 \
- -e "$QUERY" 2>/dev/null |
- sed '$s/,$//' >>"${SCHEMA_JSON}"
- echo ']' >>"${SCHEMA_JSON}"
- # Create CSV file on GCS from mysql, also trim out any CRs.
- # The generated file is really a TSV
- mysql \
- -Bs \
- -u "${DB_USER}" \
- -p"${DB_PASS}" \
- --port=3306 \
- -e "select * from ${DB}.${TABLE}" 2>/dev/null |
- tr -d '\r' |
- gsutil cp - "${GCS_REF}"
- # Load file from the bucket into BigQuery
- bq --location="US" load \
- --field_delimiter "\t" \
- --null_marker "NULL" \
- --quote="" \
- --replace \
- --source_format=CSV \
- "${BQ_DATASET}"."${TABLE}" \
- "${GCS_REF}" \
- ./"${SCHEMA_JSON}"
- done
- # Cleanup our CSV files
- gsutil -m rm "gs://${BUCKET}/${FILEBASE}/*"
Add Comment
Please, Sign In to add comment