PostgreSQL için Azure Cosmos DB kullanarak çok kiracılı veritabanı tasarlama

  • Makale

ŞUNLAR IÇIN GEÇERLIDIR: PostgreSQL için Azure Cosmos DB (PostgreSQL'e citus veritabanı uzantısıyla desteklenir)

Bu öğreticide, aşağıdakilerin nasıl yapılacağını öğrenmek için PostgreSQL için Azure Cosmos DB'yi kullanacaksınız:

  • Küme oluşturma
  • Şema oluşturmak için psql yardımcı programını kullanma
  • Düğümler arasında parça tabloları
  • Örnek verileri ekleme
  • Kiracı verilerini sorgulama
  • Kiracılar arasında veri paylaşma
  • Kiracı başına şemayı özelleştirme

Ön koşullar

Azure aboneliğiniz yoksa başlamadan önce ücretsiz bir hesap oluşturun.

Küme oluşturma

PostgreSQL için Azure Cosmos DB kümesi oluşturmak için Azure portalında oturum açın ve şu adımları izleyin:

Azure portalda Azure Cosmos DB for PostgreSQL kümesi oluşturma bölümüne gidin.

PostgreSQL için Azure Cosmos DB kümesi oluşturma formunda:

  1. Temel Bilgiler sekmesindeki bilgileri doldurun.

    Screenshot showing the Basics tab of the Create screen.

    Seçeneklerin çoğu açıkça anlaşılır durumdadır ama şunları aklınızda bulundurun:

    • Küme adı, uygulamalarınızın bağlanmak için kullandığı DNS adını biçiminde <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.combelirler.
    • 15 gibi büyük bir PostgreSQL sürümü seçebilirsiniz. PostgreSQL için Azure Cosmos DB, seçilen ana Postgres sürümü için her zaman en son Citus sürümünü destekler.
    • Yönetici kullanıcı adı citus olmalıdır.
    • Veritabanı adını varsayılan 'citus' değerinde bırakabilir veya tek veritabanı adınızı tanımlayabilirsiniz. Küme sağlamadan sonra veritabanını yeniden adlandıramazsınız.

  2. Ekranın alt kısmındaki İleri: Ağ'ı seçin.

  3. Ağ ekranında Azure içindeki Azure hizmetlerinden ve kaynaklarından bu kümeye genel erişime izin ver'i seçin.

    Screenshot showing the Networking tab of the Create screen.

  4. Gözden geçir + oluştur'u seçin ve doğrulamadan geçtikten sonra Oluştur'u seçerek kümeyi oluşturun.

  5. Sağlama işlemi birkaç dakika sürer. Sayfa, izleyici dağıtımına yeniden yönlendirilir. Dağıtım devam ediyor durumu Dağıtımınız tamamlandı olarak değiştiğinde Kaynağa git'i seçin.

Şema oluşturmak için psql yardımcı programını kullanma

Psql kullanarak PostgreSQL için Azure Cosmos DB'ye bağlandıktan sonra bazı temel görevleri tamamlayabilirsiniz. Bu öğretici, reklamverenlerin kampanyalarını izlemesine olanak tanıyan bir web uygulaması oluşturma işleminde size yol gösterir.

Uygulamayı birden çok şirket kullanabilir, bu nedenle şirketleri barındıracak bir tablo ve kampanyaları için başka bir tablo oluşturalım. psql konsolunda şu komutları çalıştırın:

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)
);

Her kampanya reklam yayınlamak için ödeme yapacaktır. Yukarıdaki koddan sonra psql'de aşağıdaki kodu çalıştırarak reklamlar için de bir tablo ekleyin:

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)
);

Son olarak, her reklam için tıklamalar ve gösterimler hakkındaki istatistikleri takip edeceğiz:

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)
);

Yeni oluşturulan tabloları şimdi psql'deki tablo listesinde aşağıdakini çalıştırarak görebilirsiniz:

\dt

Çok kiracılı uygulamalar yalnızca kiracı başına benzersizliği zorunlu kılabilir, bu nedenle tüm birincil ve yabancı anahtarlar şirket kimliğini içerir.

Düğümler arasında parça tabloları

PostgreSQL için Azure Cosmos DB dağıtımı, tablo satırlarını kullanıcı tarafından belirlenen sütunun değerine göre farklı düğümlerde depolar. Bu "dağıtım sütunu", hangi kiracının hangi satırların sahibi olduğunu işaretler.

Şimdi dağıtım sütununu kiracı tanımlayıcısı olan company_id olarak ayarlayalım. psql'de şu işlevleri çalıştırın:

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');

Önemli

PostgreSQL için Azure Cosmos DB performans özelliklerinden yararlanmak için tabloları dağıtmak veya şema tabanlı parçalama kullanmak gerekir. Tabloları veya şemaları dağıtmazsanız çalışan düğümleri verileriyle ilgili sorguları çalıştırmaya yardımcı olamaz.

Örnek verileri ekleme

Psql dışında, normal komut satırında örnek veri kümelerini indirin:

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

psql'nin içine dönüp verileri toplu olarak yükleyin. psql'yi veri dosyalarını indirdiğiniz dizinde çalıştırdığınızdan emin olun.

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

Bu veriler artık çalışan düğümlerine yayılacak.

Kiracı verilerini sorgulama

Uygulama tek bir kiracı için veri istediğinde, veritabanı sorguyu tek bir çalışan düğümünde yürütebilir. Tek kiracılı sorgular tek bir kiracı kimliğine göre filtrelenir. Örneğin, aşağıdaki sorgu reklamlar ve gösterimler için filtrelenir company_id = 5 . Sonuçları görmek için psql'de çalıştırmayı deneyin.

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;

Kiracılar arasında veri paylaşma

Şimdiye kadar tüm tablolar tarafından company_iddağıtılmıştır. Ancak bazı veriler doğal olarak belirli bir kiracıya "ait" değildir ve paylaşılabilir. Örneğin, örnek reklam platformundaki tüm şirketler IP adreslerine göre hedef kitleleri için coğrafi bilgi almak isteyebilir.

Paylaşılan coğrafi bilgileri barındıracak bir tablo oluşturun. psql'de aşağıdaki komutları çalıştırın:

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);

Ardından, tablonun bir kopyasını her çalışan düğümünde depolamak için bir "başvuru tablosu" oluşturun geo_ips .

SELECT create_reference_table('geo_ips');

Örnek verilerle yükleyin. Bu komutu psql'de veri kümesini indirdiğiniz dizinin içinden çalıştırmayı unutmayın.

\copy geo_ips from 'geo_ips.csv' with csv

Tıklamalar tablosunu geo_ips ile birleştirmek tüm düğümlerde verimlidir. Reklam 290'a tıklayan herkesin konumlarını bulmak için bir birleşim aşağıdadır. Sorguyu psql'de çalıştırmayı deneyin.

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;

Kiracı başına şemayı özelleştirme

Her kiracının başkalarının ihtiyaç duymadığı özel bilgileri depolaması gerekebilir. Ancak, tüm kiracılar aynı veritabanı şemasına sahip ortak bir altyapıyı paylaşır. Ek veriler nereye gidebilir?

Bir püf noktası PostgreSQL'in JSONB'sı gibi açık uçlu bir sütun türü kullanmaktır. Şemamızda adlı user_databir JSONB alanı clicks var. Bir şirket (örneğin, şirket beş), kullanıcının mobil cihazda olup olmadığını izlemek için sütunu kullanabilir.

Mobil veya geleneksel ziyaretçiler olmak üzere kimlerin daha fazla tıklaması olduğunu bulmak için bir sorgu aşağıdadır.

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;

Kısmi dizin oluşturarak bu sorguyu tek bir şirket için iyileştirebiliriz.

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

Daha genel olarak, sütundaki her anahtar ve değer üzerinde bir GIN dizinleri oluşturabiliriz.

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;

Kaynakları temizleme

Önceki adımlarda bir kümede Azure kaynakları oluşturdunuz. Gelecekte bu kaynaklara ihtiyaç duymayı beklemiyorsanız kümeyi silin. Kümenizin Genel Bakış sayfasında Sil düğmesini seçin. Açılır sayfada sorulduğunda, kümenin adını onaylayın ve son Sil düğmesini seçin.

Sonraki adımlar

Bu öğreticide küme sağlamayı öğrendiniz. Buna psql ile bağlandınız, bir şema oluşturdunuz ve verileri dağıttınız. Kiracılar içinde ve kiracılar arasında verileri sorgulamayı ve şemayı kiracı başına özelleştirmeyi öğrendinsiniz.