iT邦幫忙

2026 iThome 鐵人賽
DAY 9
0
Build on Google AI

將考國際證照的應用程式變成開源系列 第 11 篇

Cloud Spanner - 資料庫基礎知識

18th鐵人賽
41 瀏覽

  • 分享至 

  • xImage
    •  

Cloud Spanner - Database Fundamentals

https://www.skills.google/games/7399/labs/45431

這份 Cloud Spanner - Database Fundamentals 實驗原本包含了許多網頁圖形介面 (GUI) 的操作。為了能「全程使用 CLI (命令列介面)」完成，我將原本需要點擊 GUI 的步驟（Task 1 到 Task 4）全部轉換為對應的 gcloud 指令，並結合實驗原有的 CLI 步驟（Task 5 到 Task 7）。

請在 Google Cloud Console 右上角點擊 Activate Cloud Shell (啟動 Cloud Shell) 後，依序執行以下指令：

Task 1: 建立 Cloud Spanner 執行個體

原本需要透過控制台點擊建立，現在我們直接用 gcloud 指令來創建一個名為 banking-instance 的執行個體。

gcloud spanner instances create banking-instance \
    --config=regional-us-west1 \
    --description="banking-instance" \
    --nodes=1
  • 詳細說明：
  • instances create: 建立新的執行個體。
  • --config=regional-us-west1: 指定機房區域配置為美國西岸 (us-west1)。
  • --description: 執行個體的顯示名稱。
  • --nodes=1: 配置 1 個運算節點 (Node)。

Task 2: 建立資料庫

在剛建立的 banking-instance 執行個體中，建立名為 banking-db 的資料庫。

gcloud spanner databases create banking-db \
    --instance=banking-instance
  • 詳細說明：
  • databases create: 建立資料庫。
  • --instance: 指定該資料庫要建立在哪個執行個體底下。

Task 3: 在資料庫中建立資料表 (Schema)

使用 Data Definition Language (DDL) 建立一個名為 Customer 的資料表。

gcloud spanner databases ddl update banking-db \
    --instance=banking-instance \
    --ddl="CREATE TABLE Customer (
      CustomerId STRING(36) NOT NULL,
      Name STRING(MAX) NOT NULL,
      Location STRING(MAX) NOT NULL,
    ) PRIMARY KEY (CustomerId);"
  • 詳細說明：
  • ddl update: 更新資料庫的結構定義 (Schema)。
  • --ddl: 帶入標準 SQL DDL 語法。這裡定義了三個欄位，並將 CustomerId 設為主鍵 (Primary Key)。

Task 4: 新增與修改資料 (Insert and Query)

透過 execute-sql 指令來執行 SQL 語法，將實驗要求的第一筆及第二筆客戶資料寫入，並進行查詢。

1. 寫入第一筆資料 (Richard Nelson)：

gcloud spanner databases execute-sql banking-db \
    --instance=banking-instance \
    --sql="INSERT INTO Customer (CustomerId, Name, Location) VALUES ('bdaaaa97-1b4b-4e58-b4ad-84030de92235', 'Richard Nelson', 'Ada Ohio');"

2. 寫入第二筆資料 (Shana Underwood)：

gcloud spanner databases execute-sql banking-db \
    --instance=banking-instance \
    --sql="INSERT INTO Customer (CustomerId, Name, Location) VALUES ('b2b4002d-7813-4551-b83b-366ef95f9273', 'Shana Underwood', 'Ely Iowa');"

3. 查詢資料表：

gcloud spanner databases execute-sql banking-db \
    --instance=banking-instance \
    --sql="SELECT * FROM Customer;"
  • 詳細說明：
  • execute-sql: 讓您能夠直接對指定的 Spanner 資料庫執行標準 SQL 的 INSERT (新增) 與 SELECT (查詢) 語法。

Task 5: 實驗內建的 CLI 進階練習

實驗這部分要求建立第二個執行個體與資料庫，並修改節點數量。

1. 建立第二個執行個體 (2個節點)：

gcloud spanner instances create banking-instance-2 \
    --config=regional-us-west1 \
    --description="Banking Instance 2" \
    --nodes=2

2. 列出專案中所有的 Spanner 執行個體：

gcloud spanner instances list

3. 為第二個執行個體建立資料庫：

gcloud spanner databases create banking-db-2 \
    --instance=banking-instance-2

4. 將第二個執行個體的節點數從 2 降為 1 (節省資源)：

gcloud spanner instances update banking-instance-2 --nodes=1

Task 6: 使用自動化工具 (Terraform)

這段需要在 Cloud Shell 環境中安裝 Terraform，並透過腳本來部署第三個執行個體。

1. 安裝 Terraform：

cat << 'EOF' > ~/.customize_environment
# Set up HashiCorp repository and install Terraform
wget -O- https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(grep -oP '(?<=UBUNTU_CODENAME=).*' /etc/os-release || lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list
sudo apt update && sudo apt install -y terraform
EOF
bash ~/.customize_environment
  • 詳細說明：此腳本會匯入 HashiCorp 的 GPG 金鑰，將其加入 Ubuntu 的套件來源，並透過 apt install 自動安裝 Terraform。

2. 建立 Terraform 設定檔 (spanner.tf)：
可以直接用以下指令快速生成檔案，取代手動進入 Nano 編輯器：

cat << 'EOF' > spanner.tf
resource "google_spanner_instance" "banking-instance-3" {
  name         = "banking-instance-3"
  config       = "regional-us-west1"
  display_name = "Banking Instance 3"
  num_nodes    = 2
  labels       = {}
}
EOF

3. 初始化並部署：

terraform init
terraform plan
terraform apply -auto-approve
  • 詳細說明：
  • init: 初始化 Terraform 工作環境與下載 Spanner 供應商外掛。
  • plan: 預覽即將建立的資源。
  • apply -auto-approve: 實際執行部署，建立 banking-instance-3 (加上 -auto-approve 可以略過輸入 yes 的確認步驟)。

Task 7: 刪除執行個體

最後，清理環境，將第二個執行個體刪除。

gcloud spanner instances delete banking-instance-2 --quiet
  • 詳細說明：delete 用於刪除不再需要的執行個體。加上 --quiet 參數可以直接略過 Y/N 的確認提示，達成全自動化。刪除後可以再次執行 gcloud spanner instances list 來確認是否成功移除。

以下是整個操作的過程詳細內容:

To run a command as administrator (user "root"), use "sudo ".
See "man sudo_root" for details.

Welcome to Cloud Shell! Type "help" to get started.
Your Cloud Platform project in this session is set to qwiklabs-gcp-04-74a806f920b4.
Use gcloud config set project [PROJECT_ID] to change to a different project.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud auth list
Credentialed Accounts

ACTIVE: *
ACCOUNT: student-02-6b7122af38a0@qwiklabs.net

To set the active account, run:
$ gcloud config set account ACCOUNT

student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud config list project
[core]
project = qwiklabs-gcp-04-74a806f920b4

Your active configuration is: [cloudshell-1390]
[environment: untagged] Read more to tag: g.co/cloud/project-env-tag.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner instances create banking-instance
--config=regional-us-west1
--description="banking-instance"
--nodes=1
Creating instance...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases create banking-db
--instance=banking-instance
Creating database...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases ddl update banking-db
--instance=banking-instance
--ddl="CREATE TABLE Customer (
CustomerId STRING(36) NOT NULL,
Name STRING(MAX) NOT NULL,
Location STRING(MAX) NOT NULL,
) PRIMARY KEY (CustomerId);"
Schema updating...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases execute-sql banking-db
--instance=banking-instance
--sql="INSERT INTO Customer (CustomerId, Name, Location) VALUES ('bdaaaa97-1b4b-4e58-b4ad-84030de92235', 'Richard Nelson', 'Ada Ohio');"
Statement modified 1 row
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases execute-sql banking-db
--instance=banking-instance
--sql="INSERT INTO Customer (CustomerId, Name, Location) VALUES ('b2b4002d-7813-4551-b83b-366ef95f9273', 'Shana Underwood', 'Ely Iowa');"
Statement modified 1 row
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases execute-sql banking-db
--instance=banking-instance
--sql="SELECT * FROM Customer;"
CustomerId: b2b4002d-7813-4551-b83b-366ef95f9273
Name: Shana Underwood
Location: Ely Iowa

CustomerId: bdaaaa97-1b4b-4e58-b4ad-84030de92235
Name: Richard Nelson
Location: Ada Ohio
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner instances create banking-instance-2
--config=regional-us-west1
--description="Banking Instance 2"
--nodes=2
Creating instance...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner instances list
NAME: banking-instance
DISPLAY_NAME: banking-instance
CONFIG: regional-us-west1
NODE_COUNT: 1
PROCESSING_UNITS: 1000
STATE: READY
INSTANCE_TYPE: PROVISIONED

NAME: banking-instance-2
DISPLAY_NAME: Banking Instance 2
CONFIG: regional-us-west1
NODE_COUNT: 2
PROCESSING_UNITS: 2000
STATE: READY
INSTANCE_TYPE: PROVISIONED
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner databases create banking-db-2
--instance=banking-instance-2
Creating database...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner instances update banking-instance-2 --nodes=1
Updating instance...done.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ cat << 'EOF' > ~/.customize_environment

Set up HashiCorp repository and install Terraform

wget -O- https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(grep -oP '(?<=UBUNTU_CODENAME=).*' /etc/os-release || lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list
sudo apt update && sudo apt install -y terraform
EOF
bash ~/.customize_environment
--2026-08-10 12:55:47-- https://apt.releases.hashicorp.com/gpg
Resolving apt.releases.hashicorp.com (apt.releases.hashicorp.com)... 54.192.248.17, 54.192.248.67, 54.192.248.59, ...
Connecting to apt.releases.hashicorp.com (apt.releases.hashicorp.com)|54.192.248.17|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3980 (3.9K) [binary/octet-stream]
Saving to: ‘STDOUT’

  •                           100%[=====================================================>]   3.89K  --.-KB/s    in 0s

2026-08-10 12:55:47 (773 MB/s) - written to stdout [3980/3980]

deb [arch=amd64 signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com noble main
Hit:1 https://cli.github.com/packages stable InRelease
Get:2 https://packages.cloud.google.com/apt gcsfuse-noble InRelease [1,227 B]
Hit:3 https://repo.mongodb.org/apt/ubuntu noble/mongodb-org/8.0 InRelease
Get:4 https://packages.cloud.google.com/apt cloud-sdk InRelease [1,620 B]
Get:5 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease [189 kB]
Get:6 http://security.ubuntu.com/ubuntu noble-security InRelease [126 kB]
Hit:7 http://archive.ubuntu.com/ubuntu noble InRelease
Get:8 http://archive.ubuntu.com/ubuntu noble-updates InRelease [126 kB]
Get:9 https://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 Packages [1,068 kB]
Get:10 https://packages.cloud.google.com/apt cloud-sdk/main all Packages [2,082 kB]
Get:11 https://packages.cloud.google.com/apt cloud-sdk/main amd64 Packages [4,961 kB]
Get:12 http://security.ubuntu.com/ubuntu noble-security/main amd64 Packages [1,145 kB]
Get:13 http://archive.ubuntu.com/ubuntu noble-backports InRelease [126 kB]
Get:14 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 Packages [1,473 kB]
Get:15 http://security.ubuntu.com/ubuntu noble-security/universe amd64 Packages [1,522 kB]
Get:16 http://security.ubuntu.com/ubuntu noble-security/restricted amd64 Packages [1,643 kB]
Get:17 http://archive.ubuntu.com/ubuntu noble-updates/universe amd64 Packages [2,140 kB]
Get:18 http://archive.ubuntu.com/ubuntu noble-updates/restricted amd64 Packages [1,758 kB]
Get:19 https://download.docker.com/linux/ubuntu noble InRelease [48.5 kB]
Get:20 https://apt.releases.hashicorp.com noble InRelease [12.9 kB]
Get:21 https://download.docker.com/linux/ubuntu noble/stable amd64 Packages [76.9 kB]
Get:22 https://apt.releases.hashicorp.com noble/main amd64 Packages [305 kB]
Fetched 18.8 MB in 10s (1,835 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
31 packages can be upgraded. Run 'apt list --upgradable' to see them.
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
terraform
0 upgraded, 1 newly installed, 0 to remove and 31 not upgraded.
Need to get 34.8 MB of archives.
After this operation, 117 MB of additional disk space will be used.
Get:1 https://apt.releases.hashicorp.com noble/main amd64 terraform amd64 1.15.8-1 [34.8 MB]
Fetched 34.8 MB in 0s (141 MB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package terraform.
(Reading database ... 116250 files and directories currently installed.)
Preparing to unpack .../terraform_1.15.8-1_amd64.deb ...
Unpacking terraform (1.15.8-1) ...
Setting up terraform (1.15.8-1) ...
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ cat << 'EOF' > spanner.tf
resource "google_spanner_instance" "banking-instance-3" {
name = "banking-instance-3"
config = "regional-us-west1"
display_name = "Banking Instance 3"
num_nodes = 2
labels = {}
}
EOF
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ terraform init
terraform plan
terraform apply -auto-approve
Initializing the backend...

Initializing provider plugins...

  • Finding latest version of hashicorp/google...
  • Installing hashicorp/google v7.43.0...
  • Installed hashicorp/google v7.43.0 (signed by HashiCorp)

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the
following symbols:

  • create

Terraform will perform the following actions:

google_spanner_instance.banking-instance-3 will be created

  • resource "google_spanner_instance" "banking-instance-3" {
    • config = "regional-us-west1"
    • default_backup_schedule_type = (known after apply)
    • deletion_policy = "DELETE"
    • display_name = "Banking Instance 3"
    • edition = (known after apply)
    • effective_labels = {
      • "goog-terraform-provisioned" = "true"
        }
    • force_destroy = false
    • id = (known after apply)
    • instance_type = (known after apply)
    • name = "banking-instance-3"
    • num_nodes = 2
    • processing_units = (known after apply)
    • project = "qwiklabs-gcp-04-74a806f920b4"
    • state = (known after apply)
    • terraform_labels = {
      • "goog-terraform-provisioned" = "true"
        }
        }

Plan: 1 to add, 0 to change, 0 to destroy.

─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Note: You didn't use the -out option to save this plan, so Terraform can't guarantee to take exactly these actions if you run
"terraform apply" now.

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the
following symbols:

  • create

Terraform will perform the following actions:

google_spanner_instance.banking-instance-3 will be created

  • resource "google_spanner_instance" "banking-instance-3" {
    • config = "regional-us-west1"
    • default_backup_schedule_type = (known after apply)
    • deletion_policy = "DELETE"
    • display_name = "Banking Instance 3"
    • edition = (known after apply)
    • effective_labels = {
      • "goog-terraform-provisioned" = "true"
        }
    • force_destroy = false
    • id = (known after apply)
    • instance_type = (known after apply)
    • name = "banking-instance-3"
    • num_nodes = 2
    • processing_units = (known after apply)
    • project = "qwiklabs-gcp-04-74a806f920b4"
    • state = (known after apply)
    • terraform_labels = {
      • "goog-terraform-provisioned" = "true"
        }
        }

Plan: 1 to add, 0 to change, 0 to destroy.
google_spanner_instance.banking-instance-3: Creating...
google_spanner_instance.banking-instance-3: Still creating... [00m10s elapsed]
google_spanner_instance.banking-instance-3: Creation complete after 16s [id=qwiklabs-gcp-04-74a806f920b4/banking-instance-3]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$ gcloud spanner instances delete banking-instance-2 --quiet
student_02_6b7122af38a0@cloudshell:~ (qwiklabs-gcp-04-74a806f920b4)$


上一篇
admin-panel.feature , ai-tutor.feature , auth.feature , exam-practice.feature , localization.feature
下一篇
Cloud Spanner - 載入資料並執行備份
系列文
將考國際證照的應用程式變成開源 共 12 篇
  1. 8
    使用 Vector Search，以 Firestore 為代理建立基準
  2. 9
    ui-components , automation-scripts
  3. 10
    admin-panel.feature , ai-tutor.feature , auth.feature , exam-practice.feature , localization.feature
  4. 11
    Cloud Spanner - 資料庫基礎知識
  5. 12
    Cloud Spanner - 載入資料並執行備份
完整目錄
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
166 組
團體組數
5 組
累計文章數
897 篇
最後報名日
9/15
iT+看影片追技術 看影片追技術 看更多
熱門tag
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 17th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# linux windows server css react
熱門問題
熱門回答
熱門文章
IT邦幫忙