Convert mysql schema to bigquery schema in bash

README.md

how to use this

Just run

./convert.sh path/to/schema.sql

Maybe pipe it through jq to get prettier output:

./convert.sh path/to/schema.sql | jq

convert.sh

#!/usr/bin/env bash

set -e -u -o pipefail

grep -v '^/' "$1" \
    | xargs \
    | sed 's/`//g; s/^CREATE TABLE [^ ]+ [(]//; s/, PRIMARY KEY .+$//' \
    | tr ',' '\0' \
    | xargs -0 -n1 ./convert_field.sh \
    | sed '1 s/^/[/; $ s/,$/]/'

convert_field.sh

#!/usr/bin/env bash

set -e -u -o pipefail

NAME="$(echo "$1" | awk '{print $1}')"
SOURCE_TYPE="$(cut -d'(' -f1 <<<"$1" | awk '{print $2}')"
TARGET_TYPE="$(grep "^${SOURCE_TYPE}:" types.map | cut -d':' -f2)"
MODE="NULLABLE"

if [[ "$1" == *"NOT NULL"* ]]; then
    MODE="REQURIED"
fi

printf '{"name": "%s", "type": "%s", "mode": "%s"},' "${NAME}" "${TARGET_TYPE}" "${MODE}"

types.map

varchar:STRING
char:STRING
text:STRING
datetime:STRING
timestamp:TIMESTAMP
date:STRING
time:STRING
enum:STRING
set:STRING
int:INTEGER
tinyint:INTEGER
mediumint:INTEGER
bigint:INTEGER
float:FLOAT
double:FLOAT