1. Giới thiệu
Trong lớp học lập trình này, bạn sẽ tìm hiểu cách triển khai AlloyDB Omni và sử dụng Công cụ dạng cột để cải thiện hiệu suất cho các truy vấn.
Điều kiện tiên quyết
- Có kiến thức cơ bản về Google Cloud Console
- Kỹ năng cơ bản về giao diện dòng lệnh và google shell
Kiến thức bạn sẽ học được
- Cách triển khai AlloyDB Omni trên máy ảo GCE trong Google Cloud
- Cách kết nối với AlloyDB Omni
- Cách tải dữ liệu vào AlloyDB Omni
- Cách bật Công cụ dạng cột
- Cách kiểm tra Công cụ dạng cột ở chế độ Tự động
- Cách điền sẵn Kho cột theo cách thủ công
Bạn cần có
- Tài khoản Google Cloud và dự án trên Google Cloud
- Một trình duyệt web như Chrome
2. Cách thiết lập và các yêu cầu
Thiết lập môi trường theo tốc độ của riêng bạn
- Đăng nhập vào Google Cloud Console rồi tạo một dự án mới hoặc sử dụng lại một dự án hiện có. Nếu chưa có tài khoản Gmail hoặc Google Workspace, bạn phải tạo một tài khoản.
- Tên dự án là tên hiển thị cho người tham gia dự án này. Đây là một chuỗi ký tự không được API của Google sử dụng. Bạn luôn có thể cập nhật thông tin này.
- Mã dự án là duy nhất trên tất cả các dự án Google Cloud và không thể thay đổi (không thể thay đổi sau khi đặt). Cloud Console sẽ tự động tạo một chuỗi duy nhất; thường thì bạn không cần quan tâm đến chuỗi này. Trong hầu hết các lớp học lập trình, bạn sẽ cần tham chiếu đến Mã dự án (thường được xác định là
PROJECT_ID). Nếu không thích mã được tạo, bạn có thể tạo một mã ngẫu nhiên khác. Ngoài ra, bạn có thể thử dùng email của riêng mình để xem có thể sử dụng hay không. Bạn không thể thay đổi thông tin này sau bước này và thông tin này sẽ được giữ nguyên trong suốt thời gian diễn ra dự án. - Xin lưu ý rằng có một giá trị thứ ba là Mã dự án mà một số API sử dụng. Tìm hiểu thêm về cả ba giá trị này trong tài liệu.
- Tiếp theo, bạn cần bật tính năng thanh toán trong Cloud Console để sử dụng các tài nguyên/API trên Cloud. Việc tham gia lớp học lập trình này sẽ không tốn kém nhiều chi phí, nếu có. Để tắt các tài nguyên nhằm tránh bị tính phí sau khi hoàn tất hướng dẫn này, bạn có thể xoá các tài nguyên đã tạo hoặc xoá dự án. Người dùng mới của Google Cloud đủ điều kiện tham gia chương trình Dùng thử miễn phí 300 USD.
Khởi động Cloud Shell
Mặc dù có thể điều khiển Google Cloud từ xa trên máy tính xách tay, nhưng trong lớp học lập trình này, bạn sẽ sử dụng Google Cloud Shell, một môi trường dòng lệnh chạy trên đám mây.
Trong Bảng điều khiển Google Cloud, hãy nhấp vào biểu tượng Cloud Shell trên thanh công cụ trên cùng bên phải:
Quá trình cấp phép và kết nối với môi trường sẽ chỉ mất vài phút. Khi quá trình này hoàn tất, bạn sẽ thấy như sau:
Máy ảo này được tải sẵn tất cả các công cụ phát triển mà bạn cần. Ứng dụng này cung cấp một thư mục gốc 5 GB ổn định và chạy trên Google Cloud, giúp nâng cao đáng kể hiệu suất mạng và xác thực. Bạn có thể thực hiện mọi thao tác trong lớp học lập trình này trong trình duyệt. Bạn không cần cài đặt gì cả.
3. Trước khi bắt đầu
Bật API
Kết quả:
Trong Cloud Shell, hãy đảm bảo bạn đã thiết lập mã dự án:
PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID
Nếu không được xác định trong cấu hình shell trên đám mây, hãy thiết lập bằng các lệnh sau
export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID
Bật tất cả các dịch vụ cần thiết:
gcloud services enable compute.googleapis.com
Kết quả dự kiến
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. Triển khai AlloyDB Omni trên GCE
Để triển khai AlloyDB Omni trên GCE, chúng ta cần chuẩn bị một máy ảo có cấu hình và phần mềm tương thích. Dưới đây là ví dụ về cách triển khai AlloyDB Omni trên máy ảo dựa trên Debian.
Tạo máy ảo GCE
Chúng ta cần triển khai một máy ảo có cấu hình chấp nhận được cho CPU, bộ nhớ và bộ nhớ. Chúng ta sẽ sử dụng hình ảnh Debian mặc định với kích thước ổ đĩa hệ thống tăng lên 20 Gb để chứa các tệp cơ sở dữ liệu AlloyDB Omni.
Chúng ta có thể sử dụng shell trên đám mây đã khởi động hoặc một thiết bị đầu cuối đã cài đặt SDK trên đám mây
Tất cả các bước cũng được mô tả trong phần bắt đầu nhanh cho AlloyDB Omni.
Thiết lập các biến môi trường cho quá trình triển khai.
export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
Sau đó, chúng ta sử dụng gcloud để tạo máy ảo GCE.
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
Kết quả đầu ra dự kiến trên bảng điều khiển:
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)$
Cài đặt AlloyDB Omni
Kết nối với máy ảo đã tạo:
gcloud compute ssh omni01 --zone $ZONE
Kết quả đầu ra dự kiến trên bảng điều khiển:
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:~$
Chạy lệnh sau trong thiết bị đầu cuối đã kết nối.
Cài đặt docker trên máy ảo:
sudo apt update
sudo apt-get -y install docker.io
Kết quả đầu ra dự kiến trên bảng điều khiển(đã loại bỏ):
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:~$
Xác định mật khẩu cho người dùng postgres:
export PGPASSWORD=<your password>
Tạo thư mục cho dữ liệu AlloyDB Omni. Đây là phương pháp không bắt buộc nhưng nên dùng. Theo mặc định, dữ liệu sẽ được tạo bằng cách sử dụng lớp hệ thống tệp tạm thời của docker và mọi thứ sẽ bị huỷ bỏ khi xoá vùng chứa docker. Việc tách riêng cho phép bạn quản lý các vùng chứa độc lập với dữ liệu và tuỳ ý đặt vùng chứa vào bộ nhớ có đặc điểm IO tốt hơn
Dưới đây là lệnh tạo thư mục trong thư mục gốc của người dùng, nơi tất cả dữ liệu sẽ được đặt:
mkdir -p $HOME/alloydb-data
Triển khai vùng chứa 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
Kết quả đầu ra dự kiến trên bảng điều khiển(đã loại bỏ):
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:~$
Cài đặt phần mềm ứng dụng PostgreSQL vào máy ảo (không bắt buộc – dự kiến là đã được cài đặt):
sudo apt install -y postgresql-client
Kết quả đầu ra dự kiến trên bảng điều khiển:
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.
Kết nối với AlloyDB Omni:
psql -h localhost -U postgres
Kết quả đầu ra dự kiến trên bảng điều khiển:
gleb@omni01:~$ psql -h localhost -U postgres psql (15.6 (Debian 15.6-0+deb12u1), server 15.5) Type "help" for help. postgres=#
Ngắt kết nối khỏi AlloyDB Omni:
exit
Kết quả đầu ra dự kiến trên bảng điều khiển:
postgres=# exit gleb@omni01:~$
5. Chuẩn bị cơ sở dữ liệu kiểm thử
Để kiểm thử Công cụ dạng cột, chúng ta cần tạo một cơ sở dữ liệu và điền một số dữ liệu kiểm thử vào đó.
Tạo cơ sở dữ liệu
Kết nối với máy ảo AlloyDB Omni và tạo cơ sở dữ liệu
Trong phiên Cloud Shell, hãy thực thi:
gcloud config set project $(gcloud config get-value project)
Kết nối với máy ảo AlloyDB Omni:
ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Kết quả đầu ra dự kiến trên bảng điều khiển:
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:~$
Trong phiên SSH đã thiết lập, hãy thực thi:
export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
Kết quả đầu ra dự kiến trên bảng điều khiển:
student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db" CREATE DATABASE student@omni01:~$
Tạo bảng có dữ liệu mẫu
Đối với các thử nghiệm của mình, chúng ta sẽ sử dụng dữ liệu công khai về Nhà sản xuất bảo hiểm được cấp phép ở Iowa. Bạn có thể tìm thấy tập dữ liệu này trên trang web của chính phủ Iowa – https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .
Trước tiên, chúng ta cần tạo một bảng.
Trong máy ảo GCE, hãy thực thi:
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
);"
Kết quả đầu ra dự kiến trên bảng điều khiển:
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:~$
Tải dữ liệu vào bảng.
Trong máy ảo GCE, hãy thực thi:
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"
Kết quả đầu ra dự kiến trên bảng điều khiển:
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:~$
Chúng tôi đã tải 210898 bản ghi về nhà sản xuất bảo hiểm vào cơ sở dữ liệu và có thể tiến hành kiểm thử.
Chạy truy vấn kiểm thử
Kết nối với quickstart_db bằng psql và bật tính năng tính thời gian để đo lường thời gian thực thi cho các truy vấn của chúng ta.
Trong máy ảo GCE, hãy thực thi:
psql -h localhost -U postgres -d quickstart_db
Kết quả đầu ra dự kiến trên bảng điều khiển:
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=#
Trong phiên PSQL, hãy thực thi:
\timing
Kết quả đầu ra dự kiến trên bảng điều khiển:
quickstart_db=# \timing Timing is on. quickstart_db=#
Hãy tìm 5 thành phố hàng đầu theo số lượng nhà sản xuất bảo hiểm bán bảo hiểm Tai nạn và Bảo hiểm y tế, đồng thời có giấy phép hợp lệ ít nhất trong 6 tháng tới.
Trong phiên PSQL, hãy thực thi:
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;
Kết quả đầu ra dự kiến trên bảng điều khiển:
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
Bạn nên chạy truy vấn kiểm thử nhiều lần để có được thời gian thực thi đáng tin cậy.Chúng ta có thể thấy rằng thời gian trung bình để trả về kết quả là khoảng 94 mili giây. Trong các bước sau, chúng ta sẽ bật Công cụ cột AlloyDB và xem liệu công cụ này có thể cải thiện hiệu suất hay không.
Thoát khỏi phiên psql:
exit
6. Bật Công cụ dạng cột
Bây giờ, chúng ta cần bật Công cụ dạng cột trên AlloyDB Omni.
Cập nhật tham số AlloyDB Omni
Chúng ta cần chuyển tham số thực thể "google_columnar_engine.enabled" thành "on" (bật) cho AlloyDB Omni và cần khởi động lại.
Cập nhật postgresql.conf trong thư mục /var/alloydb/config và khởi động lại thực thể.
Trong máy ảo GCE, hãy thực thi:
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
Kết quả đầu ra dự kiến trên bảng điều khiển:
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:~$
Xác minh Công cụ dạng cột
Kết nối với cơ sở dữ liệu bằng psql và xác minh công cụ dạng cột.
Kết nối với cơ sở dữ liệu AlloyDB Omni
Trong phiên SSH của máy ảo, hãy kết nối với cơ sở dữ liệu:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
Lệnh này sẽ hiển thị công cụ dạng cột đã bật.
Kết quả đầu ra dự kiến trên bảng điều khiển:
student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled" google_columnar_engine.enabled -------------------------------- on (1 row)
7. So sánh hiệu suất
Bây giờ, chúng ta có thể điền sẵn kho công cụ dạng cột và xác minh hiệu suất.
Tự động điền kho cột
Theo mặc định, công việc điền sẵn cửa hàng sẽ chạy mỗi giờ. Chúng tôi sẽ giảm thời gian này xuống còn 10 phút để tránh tình trạng chờ đợi.
Trong máy ảo GCE, hãy thực thi:
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
Và đây là kết quả dự kiến:
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:~$
Xác minh chế độ cài đặt
Trong máy ảo GCE, hãy thực thi:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
Kết quả đầu ra dự kiến:
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:~$
Kiểm tra các đối tượng trong Kho cột. Bạn phải để trống trường này.
Trong máy ảo GCE, hãy thực thi:
psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
Kết quả đầu ra dự kiến:
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:~$
Kết nối với cơ sở dữ liệu và chạy nhiều lần cùng một truy vấn mà chúng ta đã thực thi trước đó.
Trong máy ảo GCE, hãy thực thi:
psql -h localhost -U postgres -d quickstart_db
Trong phiên PSQL.
Bật tính năng tính thời gian
\timing
Chạy truy vấn vài lần:
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;
Kết quả đầu ra dự kiến:
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=#
Chờ 10 phút rồi kiểm tra xem các cột của bảng insurance_producers_licensed_in_iowa đã được điền vào kho cột hay chưa.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Kết quả đầu ra dự kiến:
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
Bây giờ, chúng ta có thể chạy lại truy vấn cho bảng insurance_producers_licensed_in_iowa và xem hiệu suất có được cải thiện hay không.
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;
Kết quả đầu ra dự kiến:
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
Thời gian thực thi đã giảm từ 94 mili giây xuống còn 14 mili giây. Nếu không thấy sự cải thiện nào, bạn có thể kiểm tra xem các cột đã được điền thành công vào kho cột hay chưa bằng cách kiểm tra chế độ xem g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Kết quả đầu ra dự kiến:
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)
Bây giờ, chúng ta có thể xác minh xem kế hoạch thực thi truy vấn có sử dụng Công cụ dạng cột hay không.
Trong phiên PSQL, hãy thực thi:
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;
Kết quả đầu ra dự kiến:
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
Và chúng ta có thể thấy rằng thao tác "Seq Scan" (Quét tuần tự) trên phân đoạn bảng business_licenses chưa bao giờ được thực thi và thay vào đó là "Custom Scan (columnar scan)" (Quét tuỳ chỉnh (quét theo cột)). Điều đó giúp chúng tôi cải thiện thời gian phản hồi từ 94 xuống còn 12 mili giây.
Nếu muốn xoá nội dung được điền tự động khỏi công cụ dạng cột, chúng ta có thể thực hiện việc này bằng cách sử dụng hàm SQL google_columnar_engine_reset_recommendation.
Trong phiên PSQL, hãy thực thi:
SELECT google_columnar_engine_reset_recommendation(drop_columns => true);
Thao tác này sẽ xoá các cột đã điền sẵn và bạn có thể xác minh thao tác này trong các thành phần hiển thị g_columnar_columns và g_columnar_recommended_columns như đã hiển thị trước đó.
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;
Kết quả đầu ra dự kiến:
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=#
Tự động điền kho cột
Chúng ta có thể thêm cột vào Kho công cụ dạng cột theo cách thủ công bằng các hàm SQL hoặc chỉ định các thực thể bắt buộc trong cờ thực thể để tự động tải các thực thể đó khi thực thể khởi động.
Hãy thêm các cột giống như trước bằng cách sử dụng hàm SQL google_columnar_engine_add.
Trong phiên PSQL, hãy thực thi:
SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');
Và chúng ta có thể xác minh kết quả bằng cách sử dụng cùng một thành phần hiển thị g_columnar_columns:
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Kết quả đầu ra dự kiến:
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=#
Bạn có thể xác minh rằng kho cột được sử dụng bằng cách chạy cùng một truy vấn như trước và kiểm tra kế hoạch thực thi:
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;
Thoát khỏi phiên psql:
exit
Nếu khởi động lại vùng chứa AlloyDB Omni, chúng ta có thể thấy rằng tất cả thông tin dạng cột đều bị mất.
Trong phiên shell, hãy thực thi:
sudo docker stop my-omni
sudo docker start my-omni
Chờ 5 đến 10 giây rồi chạy:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Kết quả đầu ra dự kiến:
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)
Để tự động điền lại các cột trong quá trình khởi động lại, chúng ta có thể thêm các cột đó dưới dạng cờ cơ sở dữ liệu vào các tham số AlloyDB Omni. Chúng ta sẽ thêm cờ google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' và khởi động lại vùng chứa.
Trong phiên shell, hãy thực thi:
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
Sau đó, chúng ta có thể thấy các cột đã được thêm vào Kho cột một cách tự động sau khi khởi động.
Chờ 5 đến 10 giây rồi chạy:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Kết quả đầu ra dự kiến:
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. Dọn dẹp môi trường
Bây giờ, chúng ta có thể huỷ bỏ máy ảo AlloyDB Omni
Xoá máy ảo GCE
Trong Cloud Shell, hãy thực thi:
export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Kết quả đầu ra dự kiến trên bảng điều khiển:
student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Deleted
9. Xin chúc mừng
Chúc mừng bạn đã hoàn thành lớp học lập trình này.
Nội dung đã đề cập
- Cách triển khai AlloyDB Omni trên máy ảo GCE trong Google Cloud
- Cách kết nối với AlloyDB Omni
- Cách tải dữ liệu vào AlloyDB Omni
- Cách bật Công cụ dạng cột
- Cách kiểm tra Công cụ dạng cột ở chế độ Tự động
- Cách điền sẵn Kho cột theo cách thủ công
Bạn có thể đọc thêm về cách làm việc với Công cụ dạng cột trong tài liệu.
10. Khảo sát
Kết quả: