Share via

Öğretici: PostgreSQL için Azure Cosmos DB kullanarak gerçek zamanlı analiz panosu 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 veri oluşturma
  • Toplamaları gerçekleştirme
  • Ham ve toplanmış verileri sorgulama
  • Verilerin süresi dolduğunda

Ö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 öğreticide, web analizinden trafik verilerini alma ve ardından bu verileri temel alan gerçek zamanlı panolar sağlamak için verileri dağıtma adımlarında size yol gösterilir.

Şimdi tüm ham web trafiği verilerimizi kullanacak bir tablo oluşturalım. psql terminalinde aşağıdaki komutları çalıştırın:

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);

Ayrıca dakika başına toplamalarımızı barındıracak bir tablo ve son toplamamızın konumunu koruyan bir tablo oluşturacağız. Psql'de de aşağıdaki komutları çalıştırın:

CREATE TABLE http_request_1min (
  site_id INT,
  ingest_time TIMESTAMPTZ, -- which minute this row represents

  error_count INT,
  success_count INT,
  request_count INT,
  average_response_time_msec INT,
  CHECK (request_count = error_count + success_count),
  CHECK (ingest_time = date_trunc('minute', ingest_time))
);

CREATE INDEX http_request_1min_idx ON http_request_1min (site_id, ingest_time);

CREATE TABLE latest_rollup (
  minute timestamptz PRIMARY KEY,

  CHECK (minute = date_trunc('minute', minute))
);

Yeni oluşturulan tabloları şu psql komutuyla tablo listesinde görebilirsiniz:

\dt

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", düğümler arasında verilerin nasıl parçalandığına işaret eder.

Şimdi dağıtım sütununu parça anahtarı olan site_id olarak ayarlayalım. psql'de şu işlevleri çalıştırın:

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_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 veri oluşturma

Artık kümemiz bazı verileri almaya hazır olmalıdır. Verileri sürekli eklemek için aşağıdakini bağlantımızdan psql yerel olarak çalıştırabiliriz.

DO $$
  BEGIN LOOP
    INSERT INTO http_request (
      site_id, ingest_time, url, request_country,
      ip_address, status_code, response_time_msec
    ) VALUES (
      trunc(random()*32), clock_timestamp(),
      concat('http://example.com/', md5(random()::text)),
      ('{China,India,USA,Indonesia}'::text[])[ceil(random()*4)],
      concat(
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2)
      )::inet,
      ('{200,404}'::int[])[ceil(random()*2)],
      5+trunc(random()*150)
    );
    COMMIT;
    PERFORM pg_sleep(random() * 0.25);
  END LOOP;
END $$;

Sorgu her saniye yaklaşık sekiz satır ekler. Satırlar, dağıtım sütunu site_idtarafından yönlendirilen farklı çalışan düğümlerinde depolanır.

Dekont

Veri oluşturma sorgusunu çalışır durumda bırakın ve bu öğreticideki kalan komutlar için ikinci bir psql bağlantısı açın.

Sorgu

PostgreSQL için Azure Cosmos DB, birden çok düğümün hız için sorguları paralel olarak işlemesine olanak tanır. Örneğin veritabanı, çalışan düğümlerinde TOPLA ve BAĞ_DEĞ_SAY gibi toplamları hesaplar ve sonuçları son bir yanıt olarak birleştirir.

Aşağıda birkaç istatistikle birlikte dakika başına web isteklerini saymak için bir sorgu bulabilirsiniz. Psql'de çalıştırmayı deneyin ve sonuçları gözlemleyin.

SELECT
  site_id,
  date_trunc('minute', ingest_time) as minute,
  COUNT(1) AS request_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
  SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
FROM http_request
WHERE date_trunc('minute', ingest_time) > now() - '5 minutes'::interval
GROUP BY site_id, minute
ORDER BY minute ASC;

Verileri taşıma

Önceki sorgu ilk aşamalarda düzgün çalışır, ancak verileriniz ölçeklendikçe performansı düşer. Dağıtılmış işlemede bile verileri önceden hesaplamak, tekrar tekrar yeniden hesaplamaktan daha hızlıdır.

Ham verileri düzenli olarak bir toplama tablosuna alarak panomuzun hızlı kalmasını sağlayabiliriz. Toplama süresiyle denemeler yapabilirsiniz. Dakika başına toplama tablosu kullandık, ancak bunun yerine verileri 5, 15 veya 60 dakikaya bölebilirsiniz.

Bu dağıtımı daha kolay çalıştırmak için plpgsql işlevine yerleştireceğiz. İşlevi oluşturmak rollup_http_request için psql'de bu komutları çalıştırın.

-- initialize to a time long ago
INSERT INTO latest_rollup VALUES ('10-10-1901');

-- function to do the rollup
CREATE OR REPLACE FUNCTION rollup_http_request() RETURNS void AS $$
DECLARE
  curr_rollup_time timestamptz := date_trunc('minute', now());
  last_rollup_time timestamptz := minute from latest_rollup;
BEGIN
  INSERT INTO http_request_1min (
    site_id, ingest_time, request_count,
    success_count, error_count, average_response_time_msec
  ) SELECT
    site_id,
    date_trunc('minute', ingest_time),
    COUNT(1) as request_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
  FROM http_request
  -- roll up only data new since last_rollup_time
  WHERE date_trunc('minute', ingest_time) <@
          tstzrange(last_rollup_time, curr_rollup_time, '(]')
  GROUP BY 1, 2;

  -- update the value in latest_rollup so that next time we run the
  -- rollup it will operate on data newer than curr_rollup_time
  UPDATE latest_rollup SET minute = curr_rollup_time;
END;
$$ LANGUAGE plpgsql;

İşlevimiz hazır olduğunda verileri almak için bu işlevi yürütür:

SELECT rollup_http_request();

Verilerimiz önceden toplanmış bir formda olduğundan, öncekiyle aynı raporu almak için toplama tablosunu sorgulayabiliriz. Aşağıdaki sorguyu çalıştırın:

SELECT site_id, ingest_time as minute, request_count,
       success_count, error_count, average_response_time_msec
  FROM http_request_1min
 WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

Süresi dolan eski veriler

Toplamalar sorguları daha hızlı hale getirir, ancak yine de sınırsız depolama maliyetinden kaçınmak için eski verilerin süresinin dolması gerekir. Her ayrıntı düzeyi için verileri ne kadar süreyle saklamak istediğinize karar verin ve süresi dolan verileri silmek için standart sorgular kullanın. Aşağıdaki örnekte ham verileri bir gün ve dakika başına toplamaları bir ay boyunca tutmaya karar verdik:

DELETE FROM http_request WHERE ingest_time < now() - interval '1 day';
DELETE FROM http_request_1min WHERE ingest_time < now() - interval '1 month';

Üretimde, bu sorguları bir işleve sarmalayabilir ve bir cron işinde dakikada bir çağırabilirsiniz.

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üğmesine basın. Bir açılır sayfada sorulduğunda, kümenin adını onaylayın ve son Sil düğmesine tıklayın.

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. Ham formdaki verileri sorgulamayı, bu verileri düzenli olarak toplamayı, toplanan tabloları sorgulamayı ve eski verilerin süresinin dolduğunu öğrendiniz.