Spanner - Defining Schemas and Understanding Query Plans'

GSP1050 — Spanner - Defining Schemas and Understanding Query Plans'

Estimated time: 15 minutes

# 🗄️ Spanner - Defining Schemas and Understanding Query Plans > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created to help you understand why each step works. Attempt the lab yourself first. This guide is intended for educational purposes and hands-on learning with Google Cloud services. It is not intended to replace the official lab instructions, your own understanding, or the requirements provided by Google Cloud Skills Boost. This walkthrough is not affiliated wit

# ==============================================================================
# Color Variables & Branding
# ==============================================================================
GREEN='\e[1;32m'
CYAN='\e[1;36m'
YELLOW='\e[1;33m'
MAGENTA='\e[1;35m'
RED='\e[1;31m'
RESET='\e[0m'
BOLD='\e[1m'

clear
echo -e "${CYAN}${BOLD}"
cat << "EOF"
  ____       _     _ _            __    ___            
 / __ \     | |   (_) |          / _|  / _ \           
| |  | |_ __| |__  _| |_   ___  | |_  | | | |_ __  ___ 
| |  | | '__| '_ \| | __| / _ \ |  _| | | | | '_ \/ __|
| |__| | |  | |_) | | |_ | (_) || |   | |_| | |_) \__ \
 \____/|_|  |_.__/|_|\__| \___/ |_|    \___/| .__/|___/
                                            | |        
                                            |_|        
EOF
echo -e "${RESET}"
echo -e "${MAGENTA}${BOLD}>>> ORBIT OF OPS: GSP1050 MASTER AUTOMATION INITIALIZED <<<${RESET}\n"

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Auto-fetching Project...${RESET}"
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
if [[ -z "$PROJECT_ID" ]]; then
    export PROJECT_ID=$DEVSHELL_PROJECT_ID
fi
echo -e "✅ Project ID: ${GREEN}$PROJECT_ID${RESET}\n"

echo -e "${CYAN}${BOLD}[Orbit of Ops] Task 1: Loading data into Spanner Tables (Automating UI)...${RESET}"
gcloud spanner databases execute-sql banking-ops-db \
    --instance=banking-ops-instance \
    --sql="INSERT INTO Portfolio (PortfolioId, Name, ShortName, PortfolioInfo) VALUES (1, 'Banking', 'Bnkg', 'All Banking Business'), (2, 'Asset Growth', 'AsstGrwth', 'All Asset Focused Products'), (3, 'Insurance', 'Ins', 'All Insurance Focused Products');" || true

gcloud spanner databases execute-sql banking-ops-db \
    --instance=banking-ops-instance \
    --sql="INSERT INTO Category (CategoryId, PortfolioId, CategoryName) VALUES (1, 1, 'Cash'), (2, 2, 'Investments - Short Return'), (3, 2, 'Annuities'), (4, 3, 'Life Insurance');" || true

gcloud spanner databases execute-sql banking-ops-db \
    --instance=banking-ops-instance \
    --sql="INSERT INTO Product (ProductId, CategoryId, PortfolioId, ProductName, ProductAssetCode, ProductClass) VALUES (1, 1, 1, 'Checking Account', 'ChkAcct', 'Banking LOB'), (2, 2, 2, 'Mutual Fund Consumer Goods', 'MFundCG', 'Investment LOB'), (3, 3, 2, 'Annuity Early Retirement', 'AnnuFixed', 'Investment LOB'), (4, 4, 3, 'Term Life Insurance', 'TermLife', 'Insurance LOB'), (5, 1, 1, 'Savings Account', 'SavAcct', 'Banking LOB'), (6, 1, 1, 'Personal Loan', 'PersLn', 'Banking LOB'), (7, 1, 1, 'Auto Loan', 'AutLn', 'Banking LOB'), (8, 4, 3, 'Permanent Life Insurance', 'PermLife', 'Insurance LOB'), (9, 2, 2, 'US Savings Bonds', 'USSavBond', 'Investment LOB');" || true

echo -e "\n${GREEN}${BOLD}✅ Part 1 Complete. Check Task 1 progress in the lab window!${RESET}"

echo -e "\n${MAGENTA}${BOLD}>>> ORBIT OF OPS: GSP1050 PART 2 INITIALIZED <<<${RESET}\n"

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Task 2: Preparing Python Helper Environment...${RESET}"
mkdir python-helper 2>/dev/null || true
cd python-helper

wget https://storage.googleapis.com/cloud-training/OCBL373/requirements.txt
wget https://storage.googleapis.com/cloud-training/OCBL373/snippets.py

pip install -r requirements.txt
pip install setuptools

echo -e "${CYAN}${BOLD}[Orbit of Ops] Loading Data into Campaigns Table...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db insert_data

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Task 3: Querying Data (Campaigns Table)...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db query_data

echo -e "\n${GREEN}${BOLD}✅ Part 2 Complete. Check Tasks 2 & 3 progress in the lab window!${RESET}"

echo -e "\n${MAGENTA}${BOLD}>>> ORBIT OF OPS: GSP1050 PART 3 INITIALIZED <<<${RESET}\n"

# Ensure we are in the correct directory just in case the terminal reset
cd ~/python-helper 2>/dev/null || true

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Task 4: Updating Schema (Adding MarketingBudget Column)...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db add_column

echo -e "${CYAN}${BOLD}[Orbit of Ops] Updating Data in New Column...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db update_data

echo -e "${YELLOW}${BOLD}[Orbit of Ops] Querying Updated Category Table...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db query_data_with_new_column

echo -e "${CYAN}${BOLD}[Orbit of Ops] Task 5: Adding a Secondary Index...${RESET}"
python snippets.py banking-ops-instance --database-id banking-ops-db add_index

echo -e "\n${GREEN}${BOLD}🎉 All blocks executed successfully! Check Tasks 4 & 5 progress in the lab window!${RESET}"