Öğretici: PostgreSQL için Azure veritabanı – hiper ölçek (Citus) kullanarak çok kiracılı bir veritabanı tasarlama

Bu öğreticide, şu şekilde nasıl yapılacağını öğrenmek için PostgreSQL için Azure veritabanı-hiper ölçek (Citus) kullanacaksınız:

Önkoşullar

Hiper Ölçek (Citus) (Citus) sunucu grubu oluşturma

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

Azure portalında oturum açın

Azure Portal oturum açın.

PostgreSQL için Azure veritabanı sunucusu oluşturmak üzere şu adımları uygulayın:

1. Uygulamanın sol üst köşesindeki Kaynak oluştur'a Azure portal.

2. Yeni sayfasından Veritabanları’nı seçin ve Veritabanları sayfasından PostgreSQL için Azure Veritabanı’nı seçin.

3. Dağıtım seçeneği için, Sunucu grubu altındaki Oluştur Hiper Ölçek (Citus) tıklayın.

4. Yeni sunucu ayrıntıları formunu aşağıdaki bilgilerle doldurun:

   • Kaynak grubu: Bu alanın metin kutusunun altındaki Yeni oluştur bağlantısına tıklayın. myresourcegroup gibi bir ad girin.
   • Sunucu grubu adı: Yeni sunucu grubu için bir sunucu alt etki alanı için de kullanılacak benzersiz bir ad girin.
   • Yönetici kullanıcı adı: şu anda citus değeri olması gerekir ve değiştirilemez.
   • Parola: En az sekiz karakter uzunluğunda olmalı ve şu kategorilerin üçünden karakterler içermelidir: İngilizce büyük harfler, İngilizce küçük harfler, sayılar (0-9) ve alfasayısal olmayan karakterler (!, $, #, %, gibi).)
   • Konum: Verilere en hızlı erişimi vermek için kullanıcılarınıza en yakın konumu kullanın.

   Önemli

   Burada belirttiğiniz sunucu yöneticisi parolası, sunucuda ve veritabanlarında oturum açmak için gereklidir. Bu bilgileri daha sonra kullanmak üzere aklınızda tutun veya kaydedin.

5. Sunucu grubunu yapılandır'a tıklayın. Bu bölümdeki ayarları değiştirmeden bırakın ve Kaydet'e tıklayın.

6. Ekranın altındaki Sonraki: > Ağ İletişimi'ne tıklayın.

7. Ağ sekmesinde Azure içindeki Azure hizmetlerinden ve kaynaklarından bu sunucu grubuna genel erişime izin ver'i seçin. Ardından + Geçerli istemci IP adresini ekle'yi seçin.

   Not

   Azure PostgreSQL sunucusu, 5432 bağlantı noktası üzerinden iletişim kurar. Kurumsal ağ içinden bağlanmaya çalışıyorsanız, ağınızın güvenlik duvarı tarafından 5432 numaralı bağlantı noktası üzerinden giden trafiğe izin verilmiyor olabilir. Bu şekilde, IT departmanınız 5432 Hiper Ölçek (Citus) açmadıkça küme kümenize bağlanamazsiniz.

8. Gözden geçir + oluştur'a ve ardından Oluştur'a tıklar ve sunucuyu sağlar. Sağlama işlemi birkaç dakika sürer.

9. Sayfa, dağıtımı izlemek için yeniden yönlendirecek. Dağıtımınız sırasındaki canlı durum Dağıtımınız tamamlandı olarak değişirse, sayfanın sol tarafından Çıkışlar menü öğesini tıklatın.

10. Çıkışlar sayfasında, değeri panoya kopyalamak için yanında bir düğme bulunan bir koordinatör ana bilgisayar adı yer ayacaktır. Bu bilgileri daha sonra kullanmak üzere kaydedebilirsiniz.

Bağlan psql kullanarak veritabanına

Bir sunucuyu PostgreSQL için Azure Veritabanı citus adlı varsayılan bir veritabanı oluşturulur. Veritabanı sunucunuza bağlanmak için bir bağlantı dizesi ve yönetici parolası gerekir.

1. Bağlantı dizesini alın. Sunucu grubu sayfasında Bağlantı dizeleri menü öğesini tıklatın. (Bu, Ayarlar.) psql olarak işaretlenmiş dizeyi bulun. Şu şekilde olur:

   psql "host=hostname.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
   

   Dizeyi kopyalayın. "{your password}" ifadesini daha önce _ seçtiğiniz yönetici parolasıyla değiştirmeniz gerekir. Sistem düz metin parolanızı depolamaz ve bu nedenle bağlantı dizesinde sizin için gösteresiniz.

2. Yerel bilgisayarınızda bir terminal penceresi açın.

3. İstendiğinde psql yardımcı PostgreSQL için Azure Veritabanı sunucunuza bağlanabilirsiniz. Bağlantı dizenizi tırnak içine alın ve parolanızı içerdiğine emin olun:

   psql "host=..."
   

   Örneğin, aşağıdaki komut mydemoserver sunucu grubunun koordinatör düğümüne bağlanır:

   psql "host=mydemoserver-c.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
   

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

Psql kullanarak PostgreSQL için Azure veritabanı-hiper ölçek (Citus) bağlantısı kurulduktan sonra bazı temel görevleri tamamlayabilirsiniz. Bu öğreticide, reklamcılarının kampanyalarını izlemelerine izin veren bir Web uygulaması oluşturma işlemi adım adım açıklanmaktadır.

Birden çok şirket uygulamayı kullanabilir, böylece şirketler için bir tablo oluşturalım 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,
  blacklisted_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 reklamları çalıştırmak için ödeme yapar. Yukarıdaki koddan sonra psql 'de aşağıdaki kodu çalıştırarak reklamları için de 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 ad için tıklama ve aksaklılar hakkında istatistikleri izliyoruz:

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ı şu şekilde psql 'de bulunan tablolar listesinde görebilirsiniz:

\dt

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

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

Hiper ölçek dağıtımı, tablo satırlarını kullanıcı tarafından belirlenen bir 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 şirket kimliği, kiracı _ tanımlayıcısı olarak ayarlay bakalı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');

Örnek verileri ekleme

Şimdi psql dışında, normal komut satırına ö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 içine geri dönüp verileri toplu olarak yükleme. Veri dosyalarını indirdiğiniz dizinde psql'yi çalıştırabilirsiniz.

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ılır.

Kiracı verilerini sorgulama

Uygulama tek bir kiracı için veri isteğinde olduğunda veritabanı sorguyu tek bir çalışan düğümünde yürütebilir. Tek kiracılı sorgular tek bir kiracı kimliğine göre filtrelemektedir. Örneğin, aşağıdaki sorgu reklam ve company_id = 5 izlenimleri filtreler. 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 dağıtılmıştır, ancak bazı veriler doğal olarak herhangi bir company_id kiracıya "ait" değildir ve paylaştırılmalarını sağlar. Örneğin, örnek reklam platformunda yer alan tüm şirketler IP adreslerine göre hedef kitleleri için coğrafi bilgi almak istiyor olabilir.

Paylaşılan coğrafi bilgileri tutmak için 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);

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

SELECT create_reference_table('geo_ips');

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

\copy geo_ips from 'geo_ips.csv' with csv

Tıklama tablosunun coğrafi _ IP 'ler ile katılması tüm düğümlerde etkilidir. Ad 'ye tıklanan herkesin konumlarını bulmak için bir JOIN aşağıda verilmiştir 290. 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, diğerlerinin gerek duymayan özel bilgileri depolaması gerekebilir. Ancak, tüm kiracılar aynı veritabanı şemasıyla ortak bir altyapı paylaşır. Fazla veri nereden gidebileceği?

Tek bir adım PostgreSQL 'in JSONB gibi bir açık uçlu sütun türü kullanmaktır. Şemanızın çağrılan bir JSONB alanı vardır clicks user_data . Şirket (Şirket beşini), kullanıcının bir mobil cihazda olup olmadığını izlemek için sütununu kullanabilir.

İşte kimin daha fazla tıklatığını bulmak için bir sorgu: mobil veya geleneksel ziyaretçiler.

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 bir dizinoluş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ütun içindeki her anahtar ve değer üzerinde bir gın dizini oluştururuz.

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

Yukarıdaki adımlarda, bir sunucu grubunda Azure kaynakları oluşturdunuz. Gelecekte bu kaynaklara ihtiyaç duymazsanız, sunucu grubunu silin. Sunucu grubunuzun genel bakış sayfasında Sil düğmesine basın. Bir açılır sayfada istendiğinde, sunucu grubunun adını onaylayın ve son Sil düğmesine tıklayın.

Sonraki adımlar

Bu öğreticide, bir hiper ölçek (Citus) sunucu grubu sağlamayı öğrendiniz. Bu ağa psql ile bağlanırsınız, bir şema oluşturdunuz ve dağıtılmış veriler. Kiracılar içindeki ve içindeki verileri sorgulamayı ve kiracı başına şemayı özelleştirmeyi öğrendiniz.

• Sunucu grubu düğüm türleri hakkında bilgi edinin
• Sunucu grubunuz için en iyi ilk boyutu belirleme