Create and Manage Cloud Spanner Instances
Solution for Create and Manage Cloud Spanner Instances. 1 lab: GSP381. Fast copy-paste commands for Google Cloud.
GSP381 — Create and Manage Cloud Spanner Instances: Challenge Lab
Estimated time: 30 minutes
# 🗄️ Create and Manage Cloud Spanner Instances: Challenge Lab > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created to help you understand why each step works. Attempt the challenge yourself first. This guide is provided for educational purposes and is not intended to replace the official lab instructions or your own hands-on learning. It is not affiliated with or endorsed by Google Cloud or Google Cloud Skills Boost. Always follow the official Google Cloud and Qwikla
clear
CYAN='\e[1;36m'
BLUE='\e[1;34m'
YELLOW='\e[1;33m'
GREEN='\e[1;32m'
RESET='\e[0m'
BOLD='\e[1m'
echo -e "${CYAN}${BOLD}"
cat << "EOF"
____ _ _ _ __ ___
/ __ \ | | (_) | / _| / _ \
| | | |_ __| |__ _| |_ ___ | |_ | | | |_ __ ___
| | | | '__| '_ \| | __| / _ \ | _| | | | | '_ \/ __|
| |__| | | | |_) | | |_ | (_) || | | |_| | |_) \__ \
\____/|_| |_.__/|_|\__| \___/ |_| \___/| .__/|___/
| |
|_|
EOF
echo -e "${RESET}"
echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║ 🚀 ORBIT OF OPS — GSP381 — PART 1 OF 3 ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "${YELLOW}${BOLD}[Orbit of Ops] Auto-fetching Project and Region...${RESET}"
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
if [[ -z "$PROJECT_ID" ]]; then
export PROJECT_ID=$DEVSHELL_PROJECT_ID
fi
export ZONE=$(gcloud compute project-info describe \
--format="value(commonInstanceMetadata.items[google-compute-default-zone])" 2>/dev/null | tail -n 1)
if [[ -z "$ZONE" ]]; then
echo -e "${YELLOW}${BOLD}⚠️ Could not auto-detect a default zone for this project.${RESET}"
read -p "$(echo -e ${CYAN}${BOLD}"Enter the Region shown in the Task 1 table (e.g., us-central1): "${RESET})" REGION
export REGION
else
export REGION=${ZONE%-*}
fi
export INSTANCE_NAME="banking-ops-instance"
export DATABASE_NAME="banking-ops-db"
echo -e "✅ Project: ${GREEN}$PROJECT_ID${RESET}"
echo -e "✅ Region: ${GREEN}$REGION${RESET}"
echo -e "\n${YELLOW}Ensuring the Spanner API is enabled...${RESET}"
gcloud services enable spanner.googleapis.com --quiet 2>/dev/null
echo -e "\n1️⃣ ${BOLD}Task 1 — Creating the Cloud Spanner instance...${RESET}"
gcloud spanner instances create $INSTANCE_NAME \
--config=regional-$REGION \
--description="Banking Operations Instance" \
--nodes=1
echo -e "\n2️⃣ ${BOLD}Task 2 — Creating the Cloud Spanner database...${RESET}"
gcloud spanner databases create $DATABASE_NAME \
--instance=$INSTANCE_NAME
echo -e "\n${GREEN}${BOLD}Part 1 complete. Run Part 2 in this SAME Cloud Shell session.${RESET}"
# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videosclear
CYAN='\e[1;36m'
BLUE='\e[1;34m'
YELLOW='\e[1;33m'
GREEN='\e[1;32m'
RESET='\e[0m'
BOLD='\e[1m'
echo -e "${CYAN}${BOLD}"
cat << "EOF"
____ _ _ _ __ ___
/ __ \ | | (_) | / _| / _ \
| | | |_ __| |__ _| |_ ___ | |_ | | | |_ __ ___
| | | | '__| '_ \| | __| / _ \ | _| | | | | '_ \/ __|
| |__| | | | |_) | | |_ | (_) || | | |_| | |_) \__ \
\____/|_| |_.__/|_|\__| \___/ |_| \___/| .__/|___/
| |
|_|
EOF
echo -e "${RESET}"
echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║ 🚀 ORBIT OF OPS — GSP381 — PART 2 OF 3 ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "3️⃣ ${BOLD}Task 3 — Creating all four tables via a single DDL push...${RESET}"
cat > schema.sql <<'EOF'
CREATE TABLE Portfolio (
PortfolioId INT64 NOT NULL,
Name STRING(MAX),
ShortName STRING(MAX),
PortfolioInfo STRING(MAX)
) PRIMARY KEY (PortfolioId);
CREATE TABLE Category (
CategoryId INT64 NOT NULL,
PortfolioId INT64 NOT NULL,
CategoryName STRING(MAX),
PortfolioInfo STRING(MAX)
) PRIMARY KEY (CategoryId);
CREATE TABLE Product (
ProductId INT64 NOT NULL,
CategoryId INT64 NOT NULL,
PortfolioId INT64 NOT NULL,
ProductName STRING(MAX),
ProductAssetCode STRING(25),
ProductClass STRING(25)
) PRIMARY KEY (ProductId);
CREATE TABLE Customer (
CustomerId STRING(36) NOT NULL,
Name STRING(MAX) NOT NULL,
Location STRING(MAX) NOT NULL
) PRIMARY KEY (CustomerId);
EOF
gcloud spanner databases ddl update $DATABASE_NAME \
--instance=$INSTANCE_NAME \
--ddl-file=schema.sql
echo -e "\n4️⃣ ${BOLD}Task 4 — Loading Portfolio, Category, and Product...${RESET}"
cat > portfolio_insert.sql <<'EOF'
INSERT INTO Portfolio (PortfolioId, Name, ShortName, PortfolioInfo) VALUES
(1, "Banking", "Bnkg", "All Banking Business"),
(2, "Asset Growth", "AsstGrwth", "All Asset Focused Products"),
(3, "Insurance", "Insurance", "All Insurance Focused Products");
EOF
gcloud spanner databases execute-sql $DATABASE_NAME --instance=$INSTANCE_NAME --sql="$(cat portfolio_insert.sql)"
cat > category_insert.sql <<'EOF'
INSERT INTO Category (CategoryId, PortfolioId, CategoryName) VALUES
(1, 1, "Cash"),
(2, 2, "Investments - Short Return"),
(3, 2, "Annuities"),
(4, 3, "Life Insurance");
EOF
gcloud spanner databases execute-sql $DATABASE_NAME --instance=$INSTANCE_NAME --sql="$(cat category_insert.sql)"
cat > product_insert.sql <<'EOF'
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");
EOF
gcloud spanner databases execute-sql $DATABASE_NAME --instance=$INSTANCE_NAME --sql="$(cat product_insert.sql)"
echo -e "\n${GREEN}${BOLD}Part 2 complete. Run Part 3 in this SAME Cloud Shell session.${RESET}"
# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videosclear
CYAN='\e[1;36m'
BLUE='\e[1;34m'
YELLOW='\e[1;33m'
GREEN='\e[1;32m'
MAGENTA='\e[1;35m'
RESET='\e[0m'
BOLD='\e[1m'
echo -e "${CYAN}${BOLD}"
cat << "EOF"
____ _ _ _ __ ___
/ __ \ | | (_) | / _| / _ \
| | | |_ __| |__ _| |_ ___ | |_ | | | |_ __ ___
| | | | '__| '_ \| | __| / _ \ | _| | | | | '_ \/ __|
| |__| | | | |_) | | |_ | (_) || | | |_| | |_) \__ \
\____/|_| |_.__/|_|\__| \___/ |_| \___/| .__/|___/
| |
|_|
EOF
echo -e "${RESET}"
echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║ 🚀 ORBIT OF OPS — GSP381 — PART 3 OF 3 ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "5️⃣ ${BOLD}Task 5 — Loading the 500-row Customer dataset via batch insert...${RESET}"
gcloud storage cp gs://spls/gsp381/Customer_List_500.csv .
echo -e "${YELLOW}Installing google-cloud-spanner client library...${RESET}"
pip3 install --user --break-system-packages --quiet google-cloud-spanner
cat > load_customers.py <<PYEOF
import csv
from google.cloud import spanner
instance = spanner.Client().instance("$INSTANCE_NAME")
database = instance.database("$DATABASE_NAME")
with open("Customer_List_500.csv") as f:
rows = [tuple(row) for row in csv.reader(f) if row]
with database.batch() as batch:
batch.insert(
table="Customer",
columns=("CustomerId", "Name", "Location"),
values=rows,
)
print(f"Inserted {len(rows)} rows into Customer.")
PYEOF
python3 load_customers.py
echo -e "\n6️⃣ ${BOLD}Task 6 — Adding the MarketingBudget column to Category...${RESET}"
gcloud spanner databases ddl update $DATABASE_NAME \
--instance=$INSTANCE_NAME \
--ddl='ALTER TABLE Category ADD COLUMN MarketingBudget INT64;'
echo -e "\n${MAGENTA}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${MAGENTA}${BOLD}║ 🎉 AUTOMATION COMPLETED SUCCESSFULLY 🎉 ║${RESET}"
echo -e "${MAGENTA}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}"
# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videos