Google BigQuery - Get Table or View Schema & optionally output to a file

get-bq-schema.sh

#!/bin/bash

RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
NC='\033[0m' # No Color

echo
echo
echo -e "${GREEN}╭──────────────────────────────────────────────────────────────────────╮${NC}"
echo -e "${GREEN}├───────────── Google BigQuery - Get Table or View Schema ─────────────┤${NC}"
echo -e "${GREEN}╰──────────────────────────────────────────────────────────────────────╯${NC}"
echo

CURRENT_PROJECT_NAME=$(gcloud config list project | grep project | awk '{print $3}')


getHelp() {
    echo
    echo -e "${RED}╭───────────────────────────────────╮${NC}"
    echo -e "${RED}├───────────── HELP!!! ─────────────┤${NC}"
    echo -e "${RED}╰───────────────────────────────────╯${NC}"
    echo
    echo -e "Use this script to grab the json schema from a BigQuery table or View & optionally output it to a file."
    echo
    echo -e "Parameters are as follows:"
    echo -e "    ${YELLOW}-p${NC} or ${YELLOW}--project${NC} (${GREEN}OPTIONAL${NC}) The project to use (${GREEN}Default:${NC} the current project set by gcloud)"
    echo -e "    ${YELLOW}-d${NC} or ${YELLOW}--dataset${NC} (${GREEN}REQUIRED${NC}) The dataset to use"
    echo -e "    ${YELLOW}-t${NC} or ${YELLOW}--table${NC}   (${GREEN}REQUIRED${NC}) The table or view to use"
    echo -e "    ${YELLOW}-o${NC} or ${YELLOW}--output${NC}  (${GREEN}OPTIONAL${NC}) The file to output to (does not append a filetype, so include .json as it will always be json output)"
    echo
}


# Parse the input parameters
POSITIONAL=()
while [[ $# -gt 0 ]]
do
key="$1"

case $key in
    -t|--table)
    TABLE="$2"
    shift # past argument
    shift # past value
    ;;
    -d|--dataset)
    DATASET="$2"
    shift # past argument
    shift # past value
    ;;
    -p|--project)
    PROJECT="$2"
    shift # past argument
    shift # past value
    ;;
    -o|--output)
    OUTPUT="$2"
    shift # past argument
    shift # past value
    ;;
    *)    # unknown option
    POSITIONAL+=("$1") # save it in an array for later
    shift # past argument
    ;;
esac
done
set -- "${POSITIONAL[@]}"

# error - need to set a dataset!
if [ -z "$DATASET" ]; then
    echo
    echo -e "${RED}ERROR${NC} - no dataset specified, please use the '-d or --dataset' parameter to specify the dataset name."

    getHelp

    exit 1
fi

# error - need to set a table or view!
if [ -z "$TABLE" ]; then
    echo
    echo -e "${RED}ERROR${NC} - no table specified, please use the '-t or --table' parameter to specify the table or view name."
    
    getHelp

    exit 1
fi

# ensure the user is ok to keep going with no output
if [ -z "$OUTPUT" ]; then
    echo 
    echo -e "${YELLOW}Warning${NC}, you have chosen not to output the schema."
    echo -e "The schema will be output to the stdout if you continue."
    echo -e "Are you sure you want to continue?"
    read -p "(Y/N): "  confirm && [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]] || exit 1
fi

# check the project being used


if [ -z "$PROJECT" ]; then
    echo 
    echo -e "${YELLOW}Warning${NC}, the currently set project (via gcloud config): '${GREEN}${CURRENT_PROJECT_NAME}${NC}' will be used to reference the BQ table."
    echo -e "Do you want to continue?"
    echo -e "(${YELLOW}Note:${NC} choose 'No' to exit the script if you wish to change your gcloud configuration instead.)"
    read -p "(Y/N): "  confirm && [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]] || exit 1
else
    if [[ $PROJECT != $CURRENT_PROJECT_NAME ]]; then
        echo 
        echo -e "${YELLOW}Warning${NC}, the chosen project ${GREEN}${PROJECT}${NC} does not match the currently set project (via gcloud config): '${GREEN}${CURRENT_PROJECT_NAME}${NC}'."
        echo -e "The config settings will be updated to use the provided project '${PROJECT}'."
        echo -e "Do you want to continue?"
        echo -e "(${YELLOW}Note:${NC} choose 'No' to exit the script if you wish to change your gcloud configuration instead.)"
        read -p "(Y/N): "  confirm && [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]] || exit 1
        gcloud config set project $PROJECT
    else
        echo 
        echo -e "${YELLOW}Warning${NC}, the currently set project (via gcloud config): '${GREEN}${CURRENT_PROJECT_NAME}${NC}' will be used to reference the BQ table."
        echo -e "Do you want to continue?"
        echo -e "(${YELLOW}Note:${NC} choose 'No' to exit the script if you wish to change your gcloud configuration instead.)"
        read -p "(Y/N): "  confirm && [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]] || exit 1
    fi
fi

#################### START

FQ_TBL_NAME="${CURRENT_PROJECT_NAME}:${DATASET}.${TABLE}"

echo
echo -e "Getting the schema for the following table or view: ${GREEN}${FQ_TBL_NAME}${NC} - saving to: ${GREEN}${OUTPUT}${NC}"
echo

if [ -z "$OUTPUT" ]; then
    bq show --format=prettyjson $FQ_TBL_NAME | jq '.schema.fields'
else
    bq show --format=prettyjson $FQ_TBL_NAME | jq '.schema.fields' > "$OUTPUT"
fi

# ERROR HANDLING
if [[ $? != 0 ]]; then
    echo
    echo -e "${RED}Uh Oh!${NC} Something went wrong... check the console."
    exit 1
fi