تسريع طلبات البحث التحليلية باستخدام محرّك الأعمدة في AlloyDB Omni

1. مقدمة

في هذا الدرس التطبيقي حول الترميز، ستتعرّف على كيفية نشر AlloyDB Omni واستخدام "محرك البيانات العمودي" لتحسين أداء الاستعلامات.

المتطلبات الأساسية

فهم أساسي لـ Google Cloud وConsole
مهارات أساسية في واجهة سطر الأوامر وGoogle Shell

ما ستتعلمه

كيفية نشر AlloyDB Omni على جهاز GCE الافتراضي في Google Cloud
كيفية الاتصال بـ AlloyDB Omni
كيفية تحميل البيانات إلى AlloyDB Omni
كيفية تفعيل "محرك البيانات العمودي"
كيفية التحقّق من "محرك البيانات الجدولية" في "الوضع التلقائي"
كيفية ملء "مستودع البيانات العمودي" يدويًا

المتطلبات

حساب Google Cloud ومشروع Google Cloud
متصفّح ويب، مثل Chrome

2. الإعداد والمتطلبات

إعداد البيئة بالسرعة التي تناسبك

سجِّل الدخول إلى Google Cloud Console وأنشِئ مشروعًا جديدًا أو أعِد استخدام مشروع حالي. إذا لم يكن لديك حساب على Gmail أو Google Workspace، عليك إنشاء حساب.

اسم المشروع هو الاسم المعروض للمشاركين في هذا المشروع. وهي سلسلة أحرف لا تستخدمها Google APIs. ويمكنك تعديلها في أي وقت.
رقم تعريف المشروع هو معرّف فريد في جميع مشاريع Google Cloud ولا يمكن تغييره بعد ضبطه. تنشئ Cloud Console تلقائيًا سلسلة فريدة، ولا يهمّك عادةً ما هي. في معظم دروس البرمجة، عليك الرجوع إلى رقم تعريف مشروعك (يُشار إليه عادةً باسم PROJECT_ID). إذا لم يعجبك رقم التعريف الذي تم إنشاؤه، يمكنك إنشاء رقم تعريف عشوائي آخر. يمكنك بدلاً من ذلك تجربة اسم من اختيارك ومعرفة ما إذا كان متاحًا. لا يمكن تغيير هذا الخيار بعد هذه الخطوة وسيظل ساريًا طوال مدة المشروع.
للعلم، هناك قيمة ثالثة، وهي رقم المشروع، تستخدمها بعض واجهات برمجة التطبيقات. يمكنك الاطّلاع على مزيد من المعلومات حول هذه القيم الثلاث في المستندات.

بعد ذلك، عليك تفعيل الفوترة في Cloud Console لاستخدام موارد/واجهات برمجة تطبيقات Cloud. لن تكلفك تجربة هذا الدرس التطبيقي حول الترميز الكثير، إن وُجدت أي تكلفة على الإطلاق. لإيقاف الموارد وتجنُّب تحمّل تكاليف فوترة تتجاوز هذا البرنامج التعليمي، يمكنك حذف الموارد التي أنشأتها أو حذف المشروع. يمكن لمستخدمي Google Cloud الجدد الاستفادة من برنامج الفترة التجريبية المجانية بقيمة 300 دولار أمريكي.

بدء Cloud Shell

على الرغم من إمكانية تشغيل Google Cloud عن بُعد من الكمبيوتر المحمول، ستستخدم في هذا الدرس التطبيقي حول الترميز Google Cloud Shell، وهي بيئة سطر أوامر تعمل في السحابة الإلكترونية.

من Google Cloud Console، انقر على رمز Cloud Shell في شريط الأدوات أعلى يسار الصفحة:

لن يستغرق توفير البيئة والاتصال بها سوى بضع لحظات. عند الانتهاء، من المفترض أن يظهر لك ما يلي:

يتم تحميل هذه الآلة الافتراضية مزوّدة بكل أدوات التطوير التي ستحتاج إليها. توفّر هذه الخدمة دليلًا منزليًا ثابتًا بسعة 5 غيغابايت، وتعمل على Google Cloud، ما يؤدي إلى تحسين أداء الشبكة والمصادقة بشكل كبير. يمكن إكمال جميع المهام في هذا الدرس العملي ضمن المتصفّح. لست بحاجة إلى تثبيت أي تطبيق.

3- قبل البدء

تفعيل واجهة برمجة التطبيقات

إخراج:

داخل Cloud Shell، تأكَّد من إعداد رقم تعريف مشروعك:

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. نشر AlloyDB Omni على GCE

لنشر AlloyDB Omni على GCE، علينا إعداد جهاز افتراضي يتضمّن إعدادات وبرامج متوافقة. في ما يلي مثال على كيفية نشر AlloyDB Omni على جهاز افتراضي يستند إلى Debian.

إنشاء جهاز افتراضي على GCE

علينا نشر جهاز افتراضي بإعدادات مقبولة لوحدة المعالجة المركزية والذاكرة والتخزين. سنستخدم صورة Debian التلقائية مع زيادة حجم قرص النظام إلى 20 غيغابايت لاستيعاب ملفات قاعدة بيانات 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.

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

اتّصِل بالجهاز الافتراضي الذي تم إنشاؤه:

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:~$

نفِّذ الأمر التالي في الوحدة الطرفية المتصلة.

ثبِّت 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. ويتيح لك الاحتفاظ بها بشكل منفصل إدارة الحاويات بشكل مستقل عن بياناتك، ويمكنك اختياريًا وضعها في مساحة تخزين ذات خصائص إدخال وإخراج أفضل.

في ما يلي أمر لإنشاء دليل في الدليل الرئيسي للمستخدم حيث سيتم وضع جميع البيانات:

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:~$

ثبِّت برنامج عميل 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- إعداد قاعدة بيانات اختبار

لاختبار Columnar Engine، علينا إنشاء قاعدة بيانات وملؤها ببعض بيانات الاختبار.

إنشاء قاعدة بيانات

الاتصال بالجهاز الظاهري AlloyDB Omni وإنشاء قاعدة بيانات

في جلسة Cloud Shell، نفِّذ ما يلي:

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

اتّبِع الخطوات التالية للاتصال بالجهاز الظاهري AlloyDB Omni:

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 الافتراضي، نفِّذ ما يلي:

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 الافتراضي، نفِّذ ما يلي:

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 سجلاً حول منتجي التأمين إلى قاعدة البيانات الخاصة بنا ويمكننا إجراء بعض الاختبارات.

تنفيذ طلبات بحث اختبارية

اربط قاعدة بيانات quickstart_db باستخدام psql وفعِّل التوقيت لقياس وقت التنفيذ لطلبات البحث.

في جهاز GCE الافتراضي، نفِّذ ما يلي:

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=#

لنعثر على أفضل 5 مدن حسب عدد منتجي التأمين الذين يبيعون تأمينات الحوادث والصحة والذين يكون ترخيصهم صالحًا لمدة 6 أشهر على الأقل.

في جلسة 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 Columnar Engine ونرى ما إذا كان بإمكانه تحسين الأداء.

للخروج من جلسة psql، اتّبِع الخطوات التالية:

exit

6. تفعيل "محرك البيانات العمودية"

الآن، علينا تفعيل "محرك الأعمدة" على AlloyDB Omni.

تعديل مَعلمات AlloyDB Omni

علينا تبديل مَعلمة المثيل "google_columnar_engine.enabled" إلى "on" في AlloyDB Omni، ويتطلّب ذلك إعادة التشغيل.

عدِّل ملف postgresql.conf في الدليل /var/alloydb/config وأعِد تشغيل المثيل.

في جهاز GCE الافتراضي، نفِّذ ما يلي:

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:~$

التحقّق من Columnar Engine

اتّصِل بقاعدة البيانات باستخدام psql وتحقّق من محرك الأعمدة.

الربط بقاعدة بيانات AlloyDB Omni

في جلسة بروتوكول النقل الآمن (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 الافتراضي، نفِّذ ما يلي:

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 الافتراضي، نفِّذ ما يلي:

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:~$

تحقَّق من العناصر في Columnar Store. يجب أن يكون فارغًا.

في جهاز GCE الافتراضي، نفِّذ ما يلي:

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 الافتراضي، نفِّذ ما يلي:

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)

يمكننا الآن التحقّق مما إذا كانت خطة تنفيذ الاستعلام تستخدم Columnar Engine.

في جلسة 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 مطلقًا، وتم استخدام "الفحص المخصّص (الفحص العمودي)" بدلاً من ذلك. وقد ساعدنا ذلك في تحسين وقت الاستجابة من 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 أو تحديد الكيانات المطلوبة في علامات المثيل لتحميلها تلقائيًا عند بدء تشغيل المثيل.

لنضِف الأعمدة نفسها كما في السابق باستخدام دالة SQL google_columnar_engine_add.

في جلسة 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، سنلاحظ أنّه تم فقدان جميع المعلومات العمودية.

في جلسة shell، نفِّذ ما يلي:

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)' ونعيد تشغيل الحاوية.

في جلسة shell، نفِّذ ما يلي:

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

وبعد ذلك، يمكننا أن نرى أنّه تمت إضافة الأعمدة إلى Columnar Store تلقائيًا بعد بدء التشغيل.

انتظِر لمدة تتراوح بين 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. تنظيف البيئة

الآن يمكننا إيقاف الجهاز الافتراضي (VM) الخاص بـ AlloyDB Omni

حذف جهاز GCE الافتراضي

في 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- تهانينا

تهانينا على إكمال تجربة البرمجة.

المواضيع التي تناولناها

كيفية نشر AlloyDB Omni على جهاز GCE الافتراضي في Google Cloud
كيفية الاتصال بـ AlloyDB Omni
كيفية تحميل البيانات إلى AlloyDB Omni
كيفية تفعيل "محرك البيانات العمودي"
كيفية التحقّق من "محرك البيانات الجدولية" في "الوضع التلقائي"
كيفية ملء "مستودع البيانات العمودي" يدويًا

يمكنك الاطّلاع على مزيد من المعلومات حول استخدام Columnar Engine في المستندات.

10. استطلاع

إخراج:

كيف ستستخدم هذا البرنامج التعليمي؟

قراءة المحتوى فقط

قراءة المحتوى وإكمال التمارين

تسريع طلبات البحث التحليلية باستخدام محرّك الأعمدة في AlloyDB Omni تنظيم صفحاتك في مجموعات يمكنك حفظ المحتوى وتصنيفه حسب إعداداتك المفضّلة.

1. مقدمة

المتطلبات الأساسية

ما ستتعلمه

المتطلبات

2. الإعداد والمتطلبات

إعداد البيئة بالسرعة التي تناسبك

بدء Cloud Shell

3- قبل البدء

تفعيل واجهة برمجة التطبيقات

4. نشر AlloyDB Omni على GCE

إنشاء جهاز افتراضي على GCE

تثبيت AlloyDB Omni

5- إعداد قاعدة بيانات اختبار

إنشاء قاعدة بيانات

إنشاء جدول باستخدام بيانات نموذجية

تنفيذ طلبات بحث اختبارية

6. تفعيل "محرك البيانات العمودية"

تعديل مَعلمات AlloyDB Omni

التحقّق من Columnar Engine

7. مقارنة الأداء

تعبئة "مخزن البيانات العمودي" تلقائيًا

تعبئة "مستودع البيانات العمودي" يدويًا

8. تنظيف البيئة

حذف جهاز GCE الافتراضي

9- تهانينا

المواضيع التي تناولناها

10. استطلاع

كيف ستستخدم هذا البرنامج التعليمي؟

تسريع طلبات البحث التحليلية باستخدام محرّك الأعمدة في AlloyDB Omni