透過 AlloyDB Omni 中的資料欄引擎加快數據分析查詢速度。

1. 簡介

在本程式碼研究室中，您將瞭解如何部署 AlloyDB Omni，並使用資料欄引擎改善查詢效能。

7da257ba067ed1b1.png

必要條件

  • 具備 Google Cloud 控制台的基本知識
  • 指令列介面和 Google Shell 的基本技能

課程內容

  • 如何在 Google Cloud 的 GCE VM 上部署 AlloyDB Omni
  • 如何連線至 AlloyDB Omni
  • 如何將資料載入 AlloyDB Omni
  • 如何啟用資料欄引擎
  • 如何在自動模式下檢查資料欄引擎
  • 如何手動填入資料欄式儲存庫

軟硬體需求

  • Google Cloud 帳戶和 Google Cloud 專案
  • 網路瀏覽器，例如 Chrome

2. 設定和需求

自助式環境設定

  1. 登入 Google Cloud 控制台，然後建立新專案或重複使用現有專案。如果您還沒有 Gmail 或 Google Workspace 帳戶，請務必建立帳戶。

fbef9caa1602edd0.png

a99b7ace416376c4.png

5e3ff691252acf41.png

  • 「Project name」是這個專案參與者的顯示名稱。這是 Google API 不會使用的字元字串。您隨時可以更新。
  • 專案 ID 在所有 Google Cloud 專案中都是不重複的值，且無法變更 (設定後即無法變更)。Cloud 控制台會自動產生專屬字串，您通常不需要特別留意。在大多數程式碼研究室中，您都需要參照專案 ID (通常會以 PROJECT_ID 表示)。如果您不喜歡系統產生的 ID，可以隨機產生另一個 ID。或者，您也可以自行嘗試，看看是否可用。在這個步驟後就無法變更，且會在專案期間維持不變。
  • 提醒您，有些 API 會使用第三個值「專案編號」。如要進一步瞭解這三個值，請參閱說明文件。
  1. 接下來，您需要在 Cloud 控制台中啟用帳單功能，才能使用 Cloud 資源/API。執行這個程式碼研究室不會產生太多費用，甚至可能完全不會產生費用。如要關閉資源，避免在本教學課程結束後繼續產生費用，您可以刪除建立的資源或專案。Google Cloud 新使用者可享有價值 $300 美元的免費試用期。

啟動 Cloud Shell

雖然 Google Cloud 可透過筆記型電腦遠端操作，但在本程式碼研究室中，您將使用 Google Cloud Shell，這是在雲端運作的指令列環境。

在 Google Cloud 控制台中，按一下右上方工具列的 Cloud Shell 圖示：

55efc1aaa7a4d3ad.png

佈建並連線至環境的作業需要一些時間才能完成。完成後，畫面應如下所示：

7ffe5cbb04455448.png

這個虛擬機器會載入您需要的所有開發工具。提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，可大幅提升網路效能和驗證功能。您可以在瀏覽器中完成本程式碼研究室的所有工作。您不需要安裝任何東西。

3. 事前準備

啟用 API

輸出：

在 Cloud Shell 中，確認專案 ID 已設定完畢：

PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID

如果 Cloud Shell 設定中未定義，請使用下列指令進行設定

export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID

啟用所有必要服務：

gcloud services enable compute.googleapis.com

預期的輸出內容：

student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project)
Your active configuration is: [cloudshell-14650]
student@cloudshell:~ (test-project-001-402417)$ gcloud config set project test-project-001-402417
Updated property [core/project].
student@cloudshell:~ (test-project-001-402417)$ gcloud services enable compute.googleapis.com
Operation "operations/acat.p2-4470404856-1f44ebd8-894e-4356-bea7-b84165a57442" finished successfully.

4. 在 GCE 上部署 AlloyDB Omni

如要在 GCE 上部署 AlloyDB Omni，我們需要準備虛擬機器，並安裝相容的設定和軟體。以下是如何在以 Debian 為基礎的 VM 上部署 AlloyDB Omni 的範例。

建立 GCE VM

我們需要部署 CPU、記憶體和儲存空間的 VM，並設定可接受的值。我們將使用預設的 Debian 映像檔，並將系統磁碟大小增加到 20 GB，以便容納 AlloyDB Omni 資料庫檔案。

我們可以使用已啟動的 Cloud Shell，或是已安裝 Cloud SDK 的終端機

所有步驟也都列在 AlloyDB Omni 的快速入門 中。

設定部署作業的環境變數。

export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01

接著，我們使用 gcloud 建立 GCE VM。

gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd

預期的控制台輸出內容：

gleb@cloudshell:~ (gleb-test-short-001-415614)$ export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd
Created [https://www.googleapis.com/compute/v1/projects/gleb-test-short-001-415614/zones/us-central1-a/instances/omni01].
WARNING: Some requests generated warnings:
 - Disk size: '20 GB' is larger than image size: '10 GB'. You might need to resize the root repartition manually if the operating system does not support automatic resizing. See https://cloud.google.com/compute/docs/disks/add-persistent-disk#resize_pd for details.

NAME: omni01
ZONE: us-central1-a
MACHINE_TYPE: n2-highmem-2
PREEMPTIBLE: 
INTERNAL_IP: 10.128.0.3
EXTERNAL_IP: 35.232.157.123
STATUS: RUNNING
gleb@cloudshell:~ (gleb-test-short-001-415614)$

安裝 AlloyDB Omni

連線至已建立的 VM：

gcloud compute ssh omni01 --zone $ZONE

預期的控制台輸出內容：

gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute ssh omni01 --zone $ZONE
Warning: Permanently added 'compute.5615760774496706107' (ECDSA) to the list of known hosts.
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
gleb@omni01:~$

在已連線的終端機中執行下列指令。

在 VM 上安裝 Docker：

sudo apt update
sudo apt-get -y install docker.io

預期的控制台輸出內容(經過遮蓋)：

gleb@omni01:~$ sudo apt update
sudo apt-get -y install docker.io
Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [30 B]
Get:5 file:/etc/apt/mirrors/debian-security.list Mirrorlist [39 B]
Get:7 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable InRelease [5146 B]
Get:8 https://packages.cloud.google.com/apt cloud-sdk-bookworm InRelease [6406 B]        
Get:9 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable/main amd64 Packages [1916 B]
Get:2 https://deb.debian.org/debian bookworm InRelease [151 kB]
...
Setting up binutils (2.40-2) ...
Setting up needrestart (3.6-4+deb12u1) ...
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for libc-bin (2.36-9+deb12u4) ...
gleb@omni01:~$

為 postgres 使用者定義密碼：

export PGPASSWORD=<your password>

建立 AlloyDB Omni 資料的目錄。這是選用做法，但建議採用。根據預設，系統會使用 Docker 暫時性檔案系統層建立資料，並在 Docker 容器刪除時刪除所有內容。將兩者分開可讓您獨立管理容器和資料，並視需要將容器放置至具有更佳 I/O 特性的儲存空間

以下是指令，可在使用者主目錄中建立目錄，並將所有資料放入該目錄：

mkdir -p $HOME/alloydb-data

部署 AlloyDB Omni 容器：

sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni

預期的控制台輸出內容(經過遮蓋)：

gleb@omni01:~$ export PGPASSWORD=StrongPassword
gleb@omni01:~$ sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni
Unable to find image 'google/alloydbomni:latest' locally
latest: Pulling from google/alloydbomni
71215d55680c: Pull complete 
...
2e0ec3fe1804: Pull complete 
Digest: sha256:d6b155ea4c7363ef99bf45a9dc988ce5467df5ae8cd3c0f269ae9652dd1982a6
Status: Downloaded newer image for google/alloydbomni:latest
56de4ae0018314093c8b048f69a1e9efe67c6c8117f44c8e1dc829a2d4666cd2
gleb@omni01:~$

在 VM 中安裝 PostgreSQL 用戶端軟體 (選用，應已安裝)：

sudo apt install -y  postgresql-client

預期的控制台輸出內容：

gleb@omni01:~$ sudo apt install -y  postgresql-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
postgresql-client is already the newest version (15+248).
0 upgraded, 0 newly installed, 0 to remove and 4 not upgraded.

連線至 AlloyDB Omni：

psql -h localhost -U postgres

預期的控制台輸出內容：

gleb@omni01:~$ psql -h localhost -U postgres
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
Type "help" for help.

postgres=#

中斷與 AlloyDB Omni 的連線：

exit

預期的控制台輸出內容：

postgres=# exit
gleb@omni01:~$

5. 準備測試資料庫

如要測試資料庫引擎，我們需要建立資料庫，並填入一些測試資料。

建立資料庫

連線至 AlloyDB Omni VM 並建立資料庫

在 Cloud Shell 工作階段中執行：

gcloud config set project $(gcloud config get-value project)

連線至 AlloyDB Omni VM：

ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE

預期的控制台輸出內容：

student@cloudshell:~ (gleb-test-short-001-416213)$ gcloud config set project $(gcloud config get-value project)
Updated property [core/project].
student@cloudshell:~ (gleb-test-short-001-416213)$ ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Mon Mar  4 18:17:55 2024 from 35.237.87.44
student@omni01:~$

在建立的 SSH 工作階段中執行以下指令：

export PGPASSWORD=<your password>

psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"

預期的控制台輸出內容：

student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
CREATE DATABASE
student@omni01:~$

建立包含樣本資料的表格

我們將使用愛荷華州核准的保險業務員公開資料進行測試。您可以在愛荷華州政府網站上找到這個資料集：https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data。

首先，我們需要建立資料表。

在 GCE VM 中執行：

psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"

預期的控制台輸出內容：

otochkin@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"
NOTICE:  table "insurance_producers_licensed_in_iowa" does not exist, skipping
DROP TABLE
CREATE TABLE
otochkin@omni01:~$

將資料載入資料表。

在 GCE VM 中執行：

curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"

預期的控制台輸出內容：

otochkin@omni01:~$ curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 39.3M    0 39.3M    0     0  1004k      0 --:--:--  0:00:40 --:--:-- 1028k
COPY 210898
otochkin@omni01:~$

我們已將 210898 筆保險業務員記錄載入資料庫，並可進行測試。

執行測試查詢

使用 psql 連線至 quickstart_db，並啟用計時功能，以便測量查詢的執行時間。

在 GCE VM 中執行：

psql -h localhost -U postgres -d quickstart_db

預期的控制台輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db
psql (13.14 (Debian 13.14-0+deb11u1), server 15.5
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

quickstart_db=#

在 PSQL 工作階段中執行：

\timing

預期的控制台輸出內容：

quickstart_db=# \timing
Timing is on.
quickstart_db=#

讓我們根據銷售意外險和健康險的保險業務員人數，找出未來 6 個月內有效執照人數前 5 名的城市。

在 PSQL 工作階段中執行：

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

預期的控制台輸出內容：

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.965 ms

建議您多次執行測試查詢，以便取得可靠的執行時間。我們可以看到，傳回結果的平均時間約為 94 毫秒。在後續步驟中，我們將啟用 AlloyDB 欄式引擎，看看是否能提升效能。

結束 psql 工作階段：

exit

6. 啟用資料欄引擎

接下來，我們需要在 AlloyDB Omni 上啟用資料欄引擎。

更新 AlloyDB Omni 參數

我們需要將 AlloyDB Omni 的執行個體參數「google_columnar_engine.enabled」切換為「on」，並重新啟動。

更新 /var/alloydb/config 目錄中的 postgresql.conf，然後重新啟動執行個體。

在 GCE VM 中執行：

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

預期的控制台輸出內容：

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

驗證資料欄引擎

使用 psql 連線至資料庫，並驗證資料庫引擎。

連線至 AlloyDB Omni 資料庫

在 VM SSH 工作階段中連線至資料庫：

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"

這個指令應會顯示已啟用的柱狀引擎。

預期的控制台輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
 google_columnar_engine.enabled 
--------------------------------
 on
(1 row)

7. 成效比較

我們現在可以填入資料欄引擎儲存庫，並驗證效能。

自動欄儲存體填充

根據預設，填入商店的工作會每小時執行一次。我們將縮短這段時間至 10 分鐘，以免造成等候情形。

在 GCE VM 中執行：

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 10 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

以下是預期的輸出內容：

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 5 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

確認設定

在 GCE VM 中執行：

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"

預期輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
 google_columnar_engine.auto_columnarization_schedule 
------------------------------------------------------
 EVERY 10 MINUTES
(1 row)

student@omni01:~$

檢查資料欄儲存庫中的物件。應為空白。

在 GCE VM 中執行：

psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"

預期輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
 database_name | schema_name | relation_name | column_name 
---------------+-------------+---------------+-------------
(0 rows)

student@omni01:~$

連線至資料庫，並執行先前多次執行過的相同查詢。

在 GCE VM 中執行：

psql -h localhost -U postgres -d quickstart_db

在 PSQL 工作階段中。

啟用時間

\timing

執行查詢幾次：

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

預期輸出內容：

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.289 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.608 ms
quickstart_db=#

請等候 10 分鐘，然後檢查 insurance_producers_licensed_in_iowa 資料表的資料欄是否已填入資料欄儲存庫。

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

預期輸出內容：

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name |            relation_name             | column_name
---------------+-------------+--------------------------------------+-------------
 quickstart_db | public      | insurance_producers_licensed_in_iowa | city
 quickstart_db | public      | insurance_producers_licensed_in_iowa | expirydate
 quickstart_db | public      | insurance_producers_licensed_in_iowa | loa_has_ah
(3 rows)

Time: 0.643 ms

我們現在可以再次針對 insurance_producers_licensed_in_iowa 表格執行查詢，看看效能是否有所提升。

SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

預期輸出內容：

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 14.380 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 13.279 ms

執行時間從 94 毫秒降至 14 毫秒。如果您沒有看到任何改善，可以查看 g_columnar_columns 檢視畫面，確認資料欄是否已成功填入資料欄式儲存庫。

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

預期輸出內容：

quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

我們現在可以驗證查詢執行計畫是否使用資料欄引擎。

在 PSQL 工作階段中執行：

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

預期輸出內容：

quickstart_db=# EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2279.72..2279.73 rows=5 width=17) (actual time=12.248..12.252 rows=5 loops=1)
   ->  Sort  (cost=2279.72..2292.91 rows=5277 width=17) (actual time=12.246..12.248 rows=5 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=2139.30..2192.07 rows=5277 width=17) (actual time=10.235..11.250 rows=7555 loops=1)
               Group Key: city
               Batches: 1  Memory Usage: 1169kB
               ->  Append  (cost=20.00..1669.24 rows=94012 width=9) (actual time=10.231..10.233 rows=94286 loops=1)
                     ->  Custom Scan (columnar scan) on insurance_producers_licensed_in_iowa  (cost=20.00..1665.22 rows=94011 width=9) (actual time=10.229..10.231 rows=94286 loops=1)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
                           Rows Removed by Columnar Filter: 116612
                           Rows Aggregated by Columnar Scan: 94286
                           Columnar cache search mode: native
                     ->  Seq Scan on insurance_producers_licensed_in_iowa  (cost=0.00..4.02 rows=1 width=9) (never executed)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
 Planning Time: 0.216 ms
 Execution Time: 12.353 ms

我們可以看到，系統從未執行 business_licenses 資料表區段的「Seq Scan」作業，而是改用「Custom Scan (列式掃描)」。這有助於我們將回應時間從 94 毫秒縮短至 12 毫秒。

如果想清除自動填入的內容，可以使用 SQL 函式 google_columnar_engine_reset_recommendation 來清除。

在 PSQL 工作階段中執行：

SELECT google_columnar_engine_reset_recommendation(drop_columns => true);

這會清除已填入資料的欄，您可以在 g_columnar_columns 和 g_columnar_recommended_columns 檢視中確認這項操作，如前文所述。

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

預期輸出內容：

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name | relation_name | column_name
---------------+-------------+---------------+-------------
(0 rows)

Time: 0.447 ms
quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

Time: 0.556 ms
quickstart_db=#

手動直欄式商店填充

我們可以使用 SQL 函式手動將資料欄新增至 Columnar Engine 儲存庫，或是在執行個體標記中指定必要實體，以便在執行個體啟動時自動載入這些實體。

讓我們使用 google_columnar_engine_add SQL 函式新增與先前相同的欄。

在 PSQL 工作階段中執行：

SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');

我們可以使用相同的 g_columnar_columns 檢視畫面來驗證結果：

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

預期輸出內容：

quickstart_db=# SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

Time: 0.692 ms
quickstart_db=#

您可以執行與先前相同的查詢，並查看執行計畫，確認是否使用了資料庫儲存格式：

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

結束 psql 工作階段：

exit

如果重新啟動 AlloyDB Omni 容器，您會發現所有資料欄資訊都遺失了。

在殼層工作階段中執行：

sudo docker stop my-omni
sudo docker start my-omni

請等候 5 到 10 秒，然後執行以下指令：

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

預期輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

如要在重新啟動期間自動重新填入資料欄，可以將資料欄新增為資料庫標記，並加入 AlloyDB Omni 參數。我們會新增標記 google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)'，並重新啟動容器。

在殼層工作階段中執行：

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.relations=\'quickstart_db.public.insurance_producers_licensed_in_iowa\(city,expirydate,loa_has_ah\)\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

之後，我們可以看到系統在啟動後自動將資料欄新增至資料欄式儲存庫。

請等候 5 到 10 秒，然後執行以下指令：

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

預期輸出內容：

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

8. 清理環境

我們現在可以刪除 AlloyDB Omni VM

刪除 GCE VM

在 Cloud Shell 中執行：

export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet

預期的控制台輸出內容：

student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet
Deleted

9. 恭喜

恭喜您完成程式碼研究室！

涵蓋內容

  • 如何在 Google Cloud 的 GCE VM 上部署 AlloyDB Omni
  • 如何連線至 AlloyDB Omni
  • 如何將資料載入 AlloyDB Omni
  • 如何啟用資料欄引擎
  • 如何在自動模式下檢查資料欄引擎
  • 如何手動填入資料欄式儲存庫

如要進一步瞭解如何使用 Columnar Engine，請參閱說明文件。

10. 問卷調查

輸出：

您要如何使用這個教學課程？

只閱讀內容 閱讀內容並完成練習