تصميم قاعدة بيانات متعددة المستأجرين باستخدام Azure Cosmos DB ل PostgreSQL

ينطبق على: Azure Cosmos DB ل PostgreSQL (مدعوم بملحق قاعدة بيانات Citus إلى PostgreSQL)

في هذا البرنامج التعليمي، يمكنك استخدام Azure Cosmos DB ل PostgreSQL لمعرفة كيفية:

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

في حال لم يكن لديك اشتراك Azure، فأنشئ حساباً مجانيّاً قبل البدء.

إنشاء نظام مجموعة

سجل الدخول إلى مدخل Microsoft Azure واتبع هذه الخطوات لإنشاء نظام مجموعة Azure Cosmos DB ل PostgreSQL:

في نموذج إنشاء Azure Cosmos DB لنظام مجموعة PostgreSQL:

1. املأ المعلومات في علامة التبويب الأساسيات .

   

   معظم الخيارات تفسر بعضها بعضًا، لكن ضع في اعتبارك:

   • يحدد اسم نظام المجموعة اسم DNS الذي تستخدمه التطبيقات للاتصال، في النموذج <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
   • يمكنك اختيار إصدار PostgreSQL رئيسي مثل 15. يدعم Azure Cosmos DB ل PostgreSQL دائما أحدث إصدار من Citus لإصدار Postgres الرئيسي المحدد.
   • يجب أن يكون اسم المستخدم المسؤول هو القيمة citus.
   • يمكنك ترك اسم قاعدة البيانات بقيمته الافتراضية "citus" أو تعريف اسم قاعدة البيانات الوحيد. لا يمكنك إعادة تسمية قاعدة البيانات بعد توفير نظام المجموعة.

2. حدد Next : Networking أسفل الشاشة.

3. في شاشة Networking ، حدد Allow public access from Azure services and resources within Azure to this cluster.

   

4. حدد Review + create، وعند اجتياز التحقق من الصحة، حدد Create لإنشاء نظام المجموعة.

5. يستغرق التزويد بضع دقائق. تعيد الصفحة توجيهها لمراقبة التوزيع. عندما تتغير الحالة من النشر قيد التقدم إلى اكتمال النشر الخاص بك، حدد Go to resource.

استخدام الأداة المساعدة psql لإنشاء مخطط

بمجرد الاتصال ب Azure Cosmos DB ل PostgreSQL باستخدام psql، يمكنك إكمال بعض المهام الأساسية. يرشدك هذا البرنامج التعليمي من خلال إنشاء تطبيق ويب يسمح للمعلنين بتتبع حملاتهم.

يمكن لشركات متعددة استخدام هذا التطبيق، لذلك دعونا نقُم بإنشاء جدول للشركات وآخر لحملاتهم. في وحدة تحكم psql، قم بتشغيل هذه الأوامر:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blocked_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

ستدفع كل حملة مقابل عرض الإعلانات. إضافة جدول للإعلانات أيضًا، عن طريق تشغيل التعليمة البرمجية التالية في psql بعد التعليمة البرمجية أعلاه:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

وأخيرًا، سنتتبع الإحصاءات المتعلقة بالنقرات ومرات الظهور لكل إعلان:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

يمكنك رؤية الجدول الذي تم إنشاؤه حديثًا في قائمة الجداول الآن عن طريق تشغيل:

\dt

يمكن للتطبيقات متعددة المستأجرين فرض التفرد لكل مستأجر فقط، وهذا هو السبب في أن جميع المفاتيح الأساسية والأجنبية تشمل معرف الشركة.

تقطيع الجداول عبر العقد

يخزن Azure Cosmos DB لتوزيع PostgreSQL صفوف الجدول على عقد مختلفة استنادا إلى قيمة عمود معين من قبل المستخدم. يضع "عمود التوزيع" هذا علامات تبين نوع المستأجر ونوع الصفوف التي يملكها.

لنقم بتعيين عمود التوزيع ليكون company_id، معرّف المستأجر. في psql، شغّل هذه الدوال:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

استيعاب بيانات العينة

خارج psql الآن، في سطر الأوامر العادي، قم بتحميل عينة من مجموعات البيانات :

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

مرة أخرى داخل psql، قم بتحميل الجزء الأكبر من البيانات. تأكد من تشغيل psql في نفس الدليل حيث قمت بتنزيل ملفات البيانات.

SET client_encoding TO 'UTF8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

سيتم الآن نشر هذه البيانات عبر العقد العاملة.

الاستعلام عن بيانات المستأجر

عندما يطلب التطبيق بيانات لمستأجر واحد، يمكن لقاعدة بيانات تنفيذ الاستعلام على عقدة عامل واحد. يتم تصفية استعلامات المستأجر المفرد بواسطة معرف مستأجر واحد. على سبيل المثال، يقوم الاستعلام التالي بتصفية company_id = 5 الإعلانات ومرات الظهور. حاول تشغيله في psql لرؤية النتائج.

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) desc
       ), count(*) as n_impressions, a.id
  FROM ads as a
  JOIN impressions as i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions desc;

مشاركة البيانات بين المستأجرين

حتى الآن تم توزيع كافة الجداول بواسطة company_id. ومع ذلك، فإن بعض البيانات لا تنتمي بشكل طبيعي إلى أي مستأجر على وجه الخصوص، ويمكن مشاركتها. على سبيل المثال، قد ترغب جميع الشركات في النظام الأساسي التوضيحي للإعلان في الحصول على معلومات جغرافية لجمهورها استنادًا إلى عناوين IP.

إنشاء جدول للاحتفاظ بالمعلومات الجغرافية المشتركة. قم بتشغيل الأوامر التالية في psql:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

بعد ذلك، قم بإنشاء geo_ips "جدول مرجعي" لتخزين نسخة من الجدول على كل عقدة عاملة.

SELECT create_reference_table('geo_ips');

قم بتحميل ذلك مع البيانات التوضيحية. تذكر تشغيل هذا الأمر في psql من داخل الدليل الذي قمت بتحميل مجموعة البيانات فيه.

\copy geo_ips from 'geo_ips.csv' with csv

يعد الانضمام إلى جدول النقرات باستخدام geo_ips فعالاً في جميع العقد. إليك صلة للعثور على مواقع كل من نقر على الإعلان 290. حاول تشغيل الاستعلام في psql.

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

تخصيص المخطط لكل مستأجر

قد يحتاج كل مستأجر إلى تخزين معلومات خاصة لا يحتاجها الآخرون. ومع ذلك، يشترك كافة المستأجرين في بنية أساسية مشتركة مع مخطط قاعدة بيانات متطابقة. أين يمكن أن تذهب البيانات الإضافية؟

تتمثل إحدى الحيل في استخدام نوع عمود مفتوح مثل JSONB PostgreSQL. يحتوي مخططنا على حقل JSONB في clicks يسمى user_data. يمكن للشركة (لنقل company five) استخدام العمود لتتبع ما إذا كان المستخدم على جهاز محمول.

إليك استعلامًا للعثور على الأشخاص الذين ينقرون أكثر: الجوال أو الزوار التقليديين.

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

يمكننا تحسين هذا الاستعلام لشركة واحدة عن طريق إنشاء فهرس جزئي.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

بشكل عام، يمكننا إنشاء فهارس GIN على كل مفتاح وقيمة داخل العمود.

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have
-- the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

تنظيف الموارد

في الخطوات السابقة، قمت بإنشاء موارد Azure في نظام مجموعة. إذا كنت لا تتوقع أن تحتاج إلى هذه الموارد في المستقبل، فاحذف نظام المجموعة. حدد الزر Delete في صفحة Overview للمجموعة الخاصة بك. عند مطالبتك في صفحة منبثقة، قم بتأكيد اسم نظام المجموعة وحدد زر الحذف النهائي.

الخطوات التالية

في هذا البرنامج التعليمي، تعلمت كيفية توفير نظام مجموعة. كنت متصلاً معها بواسطة psql، وأنشأت مخططًا، ووزعت البيانات. تعلمت الاستعلام عن البيانات داخل المستأجرين وفيما بينهم، وتخصيص مخطط لكل مستأجر.

• تعرف على أنواع عقد نظام المجموعة
• تحديد أفضل حجم أولي لنظام المجموعة