Trick-or-Metric | GSP1083

GSP1083 — AlloyDB - Database Fundamentals

Estimated time: 35 minutes

# 🚀 AlloyDB - Database Fundamentals (GSP1083) > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created for educational purposes, hands-on practice, and Google Cloud certification preparation. This guide is designed to help learners understand AlloyDB, database clusters, primary instances, SQL data loading, VPC networking, and the `gcloud` CLI through hands-on practice. Always attempt the lab yourself first and follow Google Cloud Skills Boost / Qwiklabs Terms of Service.

clear
CYAN='\e[1;36m'
BOLD='\e[1m'
RESET='\e[0m'
echo -e "${CYAN}${BOLD}"
echo "    ____        _     _ _             __    ___            "
echo "   / __ \      | |   (_) |           / _|  / _ \           "
echo "  | |  | |_ __| |__  _| |_   ___  | |_ | | | |_ __  ___  "
echo "  | |  | | '__| '_ \| | __| / _ \ |  _|| | | | '_ \/ __| "
echo "  | |__| | |  | |_) | | |_ | (_) || |  | |_| | |_) \__ \ "
echo "   \____/|_|  |_.__/|_|\__| \___/ |_|   \___/| .__/|___/ "
echo "                                            | |        "
echo "                                            |_|        "
echo -e "${RESET}"
echo -e "${CYAN}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${CYAN}${BOLD}║   COMMAND 1 OF 4: PRIMARY ALLOYDB CLUSTER (HA)             ║${RESET}"
echo -e "${CYAN}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"

export PROJECT_ID=$(gcloud config get-value project)
export ZONE=$(gcloud compute instances list --filter="name=alloydb-client" --format="value(zone)" 2>/dev/null)
export REGION=${ZONE%-*}

gcloud alloydb clusters create lab-cluster --password=Change3Me --network=peering-network --region=$REGION --project=$PROJECT_ID --quiet

gcloud alloydb instances create lab-instance --instance-type=PRIMARY --cpu-count=2 --availability-type=REGIONAL --region=$REGION --cluster=lab-cluster --project=$PROJECT_ID --quiet

CYAN='\e[1;36m'
BOLD='\e[1m'
RESET='\e[0m'
echo -e "\n${CYAN}${BOLD}### COMMAND 2 OF 4: DATABASE TABLES & DATA LOADING ###${RESET}\n"

export PROJECT_ID=$(gcloud config get-value project)
export ZONE=$(gcloud compute instances list --filter="name=alloydb-client" --format="value(zone)" 2>/dev/null)
export REGION=${ZONE%-*}
export ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --format="value(ipAddress)")

gcloud storage cp gs://spls/gsp1083/hrm_load.sql .

cat << 'EOF' > schema.sql
CREATE TABLE regions (region_id bigint NOT NULL, region_name varchar(25));
ALTER TABLE regions ADD PRIMARY KEY (region_id);
INSERT INTO regions VALUES (1, 'Europe'), (2, 'Americas'), (3, 'Asia'), (4, 'Middle East and Africa');
EOF

gcloud compute ssh alloydb-client --zone=$ZONE --quiet --command="echo $ALLOYDB_IP > alloydbip.txt"
gcloud compute scp schema.sql hrm_load.sql alloydb-client:~ --zone=$ZONE --quiet
gcloud compute ssh alloydb-client --zone=$ZONE --quiet --command="PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -f schema.sql && PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -f hrm_load.sql"

CYAN='\e[1;36m'
BOLD='\e[1m'
RESET='\e[0m'
echo -e "\n${CYAN}${BOLD}### COMMAND 3 OF 4: GCLOUD CLI CLUSTER CREATION ###${RESET}\n"

export PROJECT_ID=$(gcloud config get-value project)
export ZONE=$(gcloud compute instances list --filter="name=alloydb-client" --format="value(zone)" 2>/dev/null)
export REGION=${ZONE%-*}

gcloud alloydb clusters create gcloud-lab-cluster --password=Change3Me --network=peering-network --region=$REGION --project=$PROJECT_ID --quiet

gcloud alloydb instances create gcloud-lab-instance --instance-type=PRIMARY --cpu-count=2 --region=$REGION --cluster=gcloud-lab-cluster --project=$PROJECT_ID --quiet