Cloud Spanner - Loading Data and Performing Backups
Solution for Cloud Spanner - Loading Data and Performing Backups. 1 lab: GSP1049. Fast copy-paste commands for Google Cloud.
GSP1049 — Cloud Spanner - Loading Data and Performing Backups
Estimated time: 25 minutes
# 🗄️ Cloud Spanner - Loading Data and Performing Backups > ⚠️ **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 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 wi
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 — GSP1049 — PART 1 OF 3 ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${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
export INSTANCE_NAME="banking-instance"
export DATABASE_NAME="banking-db"
echo -e "✅ Project: ${GREEN}$PROJECT_ID${RESET}"
echo -e "✅ Instance: ${GREEN}$INSTANCE_NAME${RESET}"
echo -e "✅ Database: ${GREEN}$DATABASE_NAME${RESET}"
echo -e "\n1️⃣ ${BOLD}Task 1 — Confirming the pre-provisioned Customer table is empty...${RESET}"
gcloud spanner databases execute-sql $DATABASE_NAME --instance=$INSTANCE_NAME --sql="SELECT * FROM Customer"
echo -e "\n2️⃣ ${BOLD}Task 2 — Inserting a single row via DML...${RESET}"
gcloud spanner databases execute-sql $DATABASE_NAME --instance=$INSTANCE_NAME \
--sql="INSERT INTO Customer (CustomerId, Name, Location) VALUES ('bdaaaa97-1b4b-4e58-b4ad-84030de92235', 'Richard Nelson', 'Ada Ohio')"
echo -e "\n3️⃣ ${BOLD}Task 3 — Inserting a row via the Python client library...${RESET}"
echo -e "${YELLOW}Installing google-cloud-spanner...${RESET}"
pip3 install --user --break-system-packages --quiet google-cloud-spanner
cat > insert.py <<'PYEOF'
from google.cloud import spanner
from google.cloud.spanner_v1 import param_types
INSTANCE_ID = "banking-instance"
DATABASE_ID = "banking-db"
spanner_client = spanner.Client()
instance = spanner_client.instance(INSTANCE_ID)
database = instance.database(DATABASE_ID)
def insert_customer(transaction):
row_ct = transaction.execute_update(
"INSERT INTO Customer (CustomerId, Name, Location)"
"VALUES ('b2b4002d-7813-4551-b83b-366ef95f9273', 'Shana Underwood', 'Ely Iowa')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_customer)
PYEOF
python3 insert.py
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 — GSP1049 — PART 2 OF 3 ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "4️⃣ ${BOLD}Task 4 — Batch inserting rows via the Python client library...${RESET}"
cat > batch_insert.py <<'PYEOF'
from google.cloud import spanner
from google.cloud.spanner_v1 import param_types
INSTANCE_ID = "banking-instance"
DATABASE_ID = "banking-db"
spanner_client = spanner.Client()
instance = spanner_client.instance(INSTANCE_ID)
database = instance.database(DATABASE_ID)
with database.batch() as batch:
batch.insert(
table="Customer",
columns=("CustomerId", "Name", "Location"),
values=[
('edfc683f-bd87-4bab-9423-01d1b2307c0d', 'John Elkins', 'Roy Utah'),
('1f3842ca-4529-40ff-acdd-88e8a87eb404', 'Martin Madrid', 'Ames Iowa'),
('3320d98e-6437-4515-9e83-137f105f7fbc', 'Theresa Henderson', 'Anna Texas'),
('6b2b2774-add9-4881-8702-d179af0518d8', 'Norma Carter', 'Bend Oregon'),
],
)
print("Rows inserted")
PYEOF
python3 batch_insert.pyecho -e "\n5️⃣ ${BOLD}Task 5 — Loading ~150,000 rows via Dataflow...${RESET}"
echo -e "${YELLOW}Auto-fetching Region...${RESET}"
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 Regional endpoint shown in Task 5 (e.g., us-east4): "${RESET})" REGION
export REGION
else
export REGION=${ZONE%-*}
fi
echo -e "✅ Region: ${GREEN}$REGION${RESET}"
echo -e "${YELLOW}Ensuring the staging bucket exists in $REGION...${RESET}"
BUCKET_LOCATION=$(gcloud storage buckets describe gs://$PROJECT_ID --format="value(location)" 2>/dev/null)
if [[ -z "$BUCKET_LOCATION" ]]; then
gcloud storage buckets create gs://$PROJECT_ID --location=$REGION
elif [[ "${BUCKET_LOCATION,,}" != "${REGION,,}" ]]; then
echo -e "${YELLOW}Bucket exists in a different region — rebuilding in $REGION...${RESET}"
gcloud storage rm --recursive gs://$PROJECT_ID 2>/dev/null
gcloud storage buckets delete gs://$PROJECT_ID --quiet 2>/dev/null
gcloud storage buckets create gs://$PROJECT_ID --location=$REGION
else
echo -e "${GREEN}Bucket already correct — skipping.${RESET}"
fi
echo -e "${YELLOW}Creating the placeholder file the Task 5 check verifies...${RESET}"
touch emptyfile
gcloud storage cp emptyfile gs://$PROJECT_ID/tmp/emptyfile
echo -e "${YELLOW}Resetting the Dataflow API (required by this lab)...${RESET}"
gcloud services disable dataflow.googleapis.com --force
gcloud services enable dataflow.googleapis.com
sleep 60
echo -e "${YELLOW}Launching the 'Text Files on Cloud Storage to Cloud Spanner' template job...${RESET}"
gcloud dataflow jobs run spanner-load \
--gcs-location="gs://dataflow-templates-$REGION/latest/GCS_Text_to_Cloud_Spanner" \
--region=$REGION \
--staging-location="gs://$PROJECT_ID/tmp" \
--parameters="instanceId=$INSTANCE_NAME,databaseId=$DATABASE_NAME,importManifest=gs://spls/gsp1049/manifest.json" \
--worker-machine-type=e2-medium
# Note (per the lab): if this fails with a worker-provisioning error, re-run with a different US region.
echo -e "${YELLOW}Polling job status every 30s (this job takes ~12-16 minutes)...${RESET}"
JOB_ID=$(gcloud dataflow jobs list --region=$REGION --filter="name:spanner-load" --format="value(id)" --limit=1)
if [[ -z "$JOB_ID" ]]; then
echo -e "${YELLOW}Could not auto-detect the Job ID — check Dataflow > Jobs in the Console.${RESET}"
else
echo -e "Job ID: ${GREEN}$JOB_ID${RESET}"
while true; do
STATE=$(gcloud dataflow jobs describe $JOB_ID --region=$REGION --format="value(currentState)" 2>/dev/null)
echo " $(date +%H:%M:%S) — $STATE"
if [[ "$STATE" == "JOB_STATE_DONE" ]]; then
echo -e "${GREEN}${BOLD}Dataflow job succeeded.${RESET}"
break
fi
if [[ "$STATE" == "JOB_STATE_FAILED" || "$STATE" == "JOB_STATE_CANCELLED" ]]; then
echo -e "${YELLOW}${BOLD}Job did not succeed (state: $STATE) — check the Console.${RESET}"
break
fi
sleep 30
done
fi
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/videos