الدرس: تصميم قاعدة بيانات متعددة المستأجرين مع مجموعات مرنة

في هذا الدرس، تستخدم Azure Database ل PostgreSQL مع العناقيد المرنة لتتعلم كيفية تصميم تطبيق متعدد المستأجرين يستفيد من التكبير الأفقي.

• المتطلبات الأساسية
• استخدام الأداة المساعدة psql لإنشاء مخطط
• تقطيع الجداول عبر العقد
• استيعاب بيانات العينة
• الاستعلام عن بيانات المستأجر
• مشاركة البيانات بين المستأجرين
• تخصيص المخطط لكل مستأجر

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

قم بإنشاء مجموعة مرنة بإحدى الطرق التالية:

• إنشاء نظام مجموعة مرن باستخدام المدخل
• إنشاء عنقود مرن باستخدام عضلة البايسيب
• إنشاء مجموعة مرنة باستخدام قالب ARM

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

بعد الاتصال بعنقود المرن باستخدام 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)
);

يمكنك الآن رؤية الجداول الجديدة التي تم إنشاؤها من psql من خلال تشغيل:

\dt

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

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

قم بإعداد عمود التوزيع ليكون 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');

إشعار

للاستفادة من المجموعات المرنة مع ميزات أداء Azure Database for PostgreSQL، تحتاج إلى توزيع الجداول. ما لم توزع جداولك ومخططاتك، فإن عقد العنقود لا تشارك في أي استعلامات أو عمليات موزعة.

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

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

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-elastic-clusters/main/multi-tenant/${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');

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

\copy geo_ips from 'geo_ips.csv' with csv

عبارات SQL التي تربط الجدول المحدد ب geo_ips أصبحت الآن فعالة على جميع العقد. لاحظ هذا الانضمام للعثور على مواقع كل عنوان IP تم اختياره في الإعلان 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. يمكن لشركة (مثلا الشركة 5) استخدام هذا العمود لتتبع المعلومات حول ما إذا كان المستخدم يستخدم جهاز محمول.

إليك استعلام يمكن لشركة 5 استخدامه لمعرفة من يختار المزيد: الزوار عبر الهاتف المحمول أم التقليديون.

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;

يتضمن PostgreSQL ميزة قوية تتيح لك فهرسة جزء أو مجموعة محددة من بياناتك. يمكنك تحسين استفسارك للشركة 5 بشكل أكبر عن طريق إنشاء مؤشر جزئي.

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

بالإضافة إلى ذلك، هناك طريقة أخرى لتحسين الأداء عبر الاستعلامات التي تتضمن عمود JSONB الخاص بك وهي إنشاء مؤشر GIN لكل مفتاح وقيمة أساسية داخل عمود JSONB الخاص بك.

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;

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

تعرف على المزيد حول المجموعات المرنة