Cloud SQL, Spanner & BigQuery

Introduction to GCP Database Solutions

In the world of cloud computing, "data is the new oil," and how you store and manage that data is the key to a successful application. GCP Database Solutions offer a wide array of managed services designed to handle everything from small web blogs to global financial systems and petabyte-scale analytics. For the Associate Cloud Engineer, mastering GCP Database Solutions involves more than just knowing SQL; it's about understanding the trade-offs between consistency, availability, and scale.

Google Cloud divides its GCP Database Solutions into several categories based on the data model (Relational vs. NoSQL) and the use case (Transactional vs. Analytical). By offloading the "undifferentiated heavy lifting" of database administration—such as patching, backups, and replication—to Google, you can focus on building features while GCP Database Solutions ensure your data is safe and performant.

白話文解釋（Plain English Explanation）

To help you remember which service to use in the GCP Database Solutions portfolio, let's use these three analogies.

1. The Local Grocery Store vs. The Global Supermarket Chain (Scale)

• Cloud SQL is your local, reliable grocery store. It has everything you need for your daily cooking (standard relational data) and is easy to navigate. If it gets too busy, you might add a second checkout lane (Read Replica).
• Cloud Spanner is a massive, global supermarket chain. Every store in every country has the exact same inventory and the same prices in real-time. If you buy a loaf of bread in London, the inventory system in New York is updated instantly.

In GCP Database Solutions, Cloud SQL is for regional, traditional apps, while Spanner is for global, massive-scale consistency.

2. The Filing Cabinet vs. The Scrapbook (Structure)

• Relational Databases (SQL) are like a high-end filing cabinet. Every drawer is labeled, and every file must be in a specific folder with a specific form. If you try to put a round form in a square folder, the cabinet won't close (Schema validation).
• NoSQL Databases (Firestore/Bigtable) are like a scrapbook. You can stick in photos, ticket stubs, and handwritten notes. Each page can look different, and you don't need a strict "form" to add something new.

GCP Database Solutions provide the "filing cabinet" for structured transactions and the "scrapbook" for flexible, fast-changing data.

3. The Personal Ledger vs. The City Archives (Analytics)

• Transactional Databases (OLTP) are like your personal checkbook ledger. You record every coffee you buy (a transaction) immediately so you know exactly how much money you have right now.
• Analytical Databases (OLAP/BigQuery) are like the city archives. You don't go there to record a single coffee purchase. Instead, you go there once a year to find out "What was the average price of coffee across the whole city over the last decade?"

GCP Database Solutions distinguish between the "ledger" (Cloud SQL) for current operations and the "archive" (BigQuery) for historical insights.

Relational Databases (SQL)

Relational databases are the backbone of the GCP Database Solutions transactional lineup.

Cloud SQL: Managed MySQL, PostgreSQL, and SQL Server

Cloud SQL is a fully managed service that makes it easy to set up, maintain, and manage your relational databases. It handles backups, replication, and failover automatically.

Cloud Spanner: Globally Relational and Scalable

Spanner is a unique entry in GCP Database Solutions. It provides the consistency of a traditional SQL database with the global scale of a NoSQL database. It is "horizontally scalable," meaning you can add more nodes to increase performance without downtime.

NoSQL Databases

For high-speed, flexible data models, GCP Database Solutions offer two powerful NoSQL options.

Firestore: Real-time Document Database

Firestore is a serverless, document-oriented database that is part of the Firebase platform but also integrated into Google Cloud. It's perfect for mobile and web apps that need real-time data syncing.

Cloud Bigtable: High-Throughput Key-Value Store

Bigtable is the same "wide-column" database that powers Google Search and Gmail. It is designed for massive workloads (millions of reads/writes per second) and is a common choice in GCP Database Solutions for IoT and financial data.

Data Warehousing and Analytics

BigQuery: Serverless Data Warehouse

BigQuery is arguably the "Crown Jewel" of GCP Database Solutions. It is a serverless, multi-cloud data warehouse that allows you to query petabytes of data using standard SQL. Because it is serverless, you don't manage any infrastructure; you just pay for the data you process.

BigQuery ML: Machine Learning with SQL

A unique feature of BigQuery within GCP Database Solutions is the ability to build and run machine learning models directly within the database using SQL, without needing to export data to a separate ML platform.

Choosing the Right Database Solution

Choosing is the most important part of the GCP Database Solutions section of the ACE exam.

Decision Tree: SQL vs. NoSQL

• Do you need complex joins and strong consistency? Pick SQL.
• Do you need a flexible schema and high-speed ingestion? Pick NoSQL.

Decision Tree: Transactional vs. Analytical

• Is it for a web app handling user logins? Pick Cloud SQL/Firestore.
• Is it for a dashboard analyzing last year's sales? Pick BigQuery.

Managing Cloud SQL Instances

As an ACE, you'll spend much of your time managing Cloud SQL within the GCP Database Solutions suite.

High Availability (HA) and Read Replicas

• HA: Uses a standby instance in a different zone. If the primary fails, the standby takes over automatically.
• Read Replicas: Offload "Read" traffic from the primary instance to improve performance.

Backup and Point-in-Time Recovery (PITR)

Cloud SQL can take daily backups and keep "transaction logs" that allow you to recover your database to a specific second in the past.

Connecting to Cloud SQL (Auth Proxy)

The Cloud SQL Auth Proxy is the recommended way to securely connect to your GCP Database Solutions instances without needing to whitelist IP addresses.

Scaling with Cloud Spanner

Nodes and Processing Units

You scale Spanner by adding "Nodes." Each node provides a certain amount of storage and processing power. For smaller workloads, you can use "Processing Units" (1/10th of a node).

Sharding and Schema Design

Unlike traditional SQL, Spanner requires careful "Row Key" design to avoid "Hotspotting," where one node does all the work while others sit idle.

Developing with Firestore

Collections and Documents

Data in Firestore is stored in "Documents," which are gathered into "Collections." This hierarchical structure is very intuitive for app developers using GCP Database Solutions.

Native vs. Datastore Mode

• Native Mode: Best for mobile/web apps with real-time needs.
• Datastore Mode: Best for server-side workloads and high-throughput background processing.

Optimizing Cloud Bigtable

Bigtable is the "Ferrari" of GCP Database Solutions—it's incredibly fast but needs careful tuning.

Row Key Design for Performance

Your row key must be designed to distribute data evenly across the cluster. Never use a timestamp as the start of your row key!

Scaling Cluster Size

You can add or remove nodes from a Bigtable cluster in seconds without any downtime or data redistribution.

Querying with BigQuery

Projects, Datasets, and Tables

The hierarchy in BigQuery is Project -> Dataset -> Table. Access is usually granted at the Dataset level in the GCP Database Solutions IAM framework.

Partitioned and Clustered Tables

To save money and improve performance, you should "Partition" your tables by date and "Cluster" them by frequently filtered columns.

Database Security and IAM

1. Encryption: All GCP Database Solutions encrypt data at rest by default.
2. IAM Roles: Use specific roles like roles/cloudsql.client instead of broad project roles.
3. VPC Service Controls: Create a "security perimeter" around your databases to prevent data exfiltration.

Database Management via gcloud CLI

• gcloud sql instances create my-db --tier=db-f1-micro --region=us-central1: Creates a Cloud SQL instance.
• gcloud spanner instances list: Shows your active Spanner clusters.
• bq query --use_legacy_sql=false 'SELECT count(*) FROM my_dataset.my_table': Runs a BigQuery query from the terminal.

Troubleshooting Database Issues

Connection Failures and Timeouts

Usually caused by incorrect firewall settings or the Cloud SQL API not being enabled in the GCP Database Solutions project.

Query Performance Bottlenecks

Use the "Query Insights" tool in Cloud SQL to find slow-running queries and missing indexes.

Replication Lag

If your Read Replicas are out of sync, it's often because the primary is under too much load or the network between zones is congested.

Common Exam Scenarios for ACE

Moving an On-Prem MySQL to GCP

"You have an existing MySQL DB and want to move it to the cloud with minimal change. What do you use?" (Answer: Cloud SQL).

Handling Millions of Events per Second

"You are building an IoT platform that ingests millions of sensor readings per second. Where should you store it?" (Answer: Cloud Bigtable).

Running a Global Financial Transaction App

"You need a SQL database that can handle transactions in Tokyo, New York, and London simultaneously with strong consistency. What do you pick?" (Answer: Cloud Spanner).

FAQ

Q1: Can Cloud SQL scale horizontally for writes? A1: No. Cloud SQL scales vertically (bigger machine) for writes. For horizontal write scaling, use Cloud Spanner in the GCP Database Solutions portfolio.

Q2: Is BigQuery a database or a storage service? A2: It's both! It provides highly optimized columnar storage and a massive distributed query engine.

Q3: Does Firestore work offline? A3: Yes, the Firestore SDKs have built-in offline support, syncing data once the device regains a connection.

Q4: What is the difference between a "Region" and "Multi-region" in BigQuery? A4: "Multi-region" (like US or EU) provides higher availability and is usually where new GCP Database Solutions features are released first.

Q5: Can I run MongoDB on Google Cloud? A5: You can run it on a VM, or use the "MongoDB Atlas" partnership on Google Cloud, but Firestore is the native serverless NoSQL choice.

Summary Checklist for ACE

• Distinguish between Cloud SQL (Regional) and Cloud Spanner (Global).
• Know that BigQuery is for Analytics (OLAP) and Cloud SQL is for Transactions (OLTP).
• Understand that Bigtable is for high-throughput, "flat" NoSQL data.
• Know how to use the Cloud SQL Auth Proxy for secure connections.
• Recognize that Firestore is the best choice for mobile/web app state.
• Understand how partitioning and clustering save money in BigQuery.