Elastik Küme ile çok kiracılı veritabanı tasarlama

ŞUNLAR IÇIN GEÇERLIDIR: PostgreSQL için Azure Veritabanı - Esnek Sunucu

Bu öğreticide, yatay ölçeği genişletmeden yararlanmak için çok kiracılı bir uygulama tasarlamayı öğrenmek için Elastik Küme ile PostgreSQL için Azure Veritabanı kullanacaksınız.

• Önkoşullar
• Ş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

Önkoşullar

Aşağıdaki yollardan biriyle Elastik Küme oluşturun:

• ARM şablonuyla Elastik Küme oluşturma
• CLI kullanarak Elastik Küme oluşturma
• Portalı kullanarak Elastik Küme oluşturma

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

Psql kullanarak Elastik Kümeye 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 yapılır. Şirketler ve kampanyalar oluşturduktan 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 ediyoruz:

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ı

Elastik Küme 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');

Not

PostgreSQL için Azure Veritabanı performans özelliklerine sahip Elastik Kümelerden yararlanmak için tabloları dağıtmak veya şema tabanlı parçalama kullanmak gerekir. Tabloları veya şemaları dağıtmıyorsanız düğümler, 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 çalışan düğümlerine yayılır.

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 tarafından company_iddağıtılan tüm tablolar. 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;

Sonraki adım

Bu öğreticide Elastik Küme oluşturmayı öğ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.

Elastik Kümeler hakkında daha fazla bilgi edinin