Öğretici: Elastik kümelerle gerçek zamanlı analiz panosu tasarlama

Bu öğreticide, gerçek zamanlı bir pano tasarlamayı ve sorguları paralelleştirmeyi öğrenmek için PostgreSQL için Azure Veritabanı elastik kümelerini kullanacaksınız.

• Önkoşullar
• Şema oluşturmak için psql yardımcı programını kullanma
• Düğümler arasında tabloları paylaştırmak
• Örnek veri oluşturma
• Toplamaları gerçekleştir
• Ham ve toplanmış verileri sorgulama
• Veri sona erdir

Önkoşullar

Aşağıdaki yollardan biriyle elastik küme oluşturun:

• Portalı kullanarak elastik küme oluşturma
• Bicep kullanarak elastik küme oluşturma
• ARM şablonuyla 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 elastik kümenizi yapılandırabilirsiniz. Bu öğreticide, web analizlerinden trafik verilerini alıp verileri özetleyerek bu verilere dayalı gerçek zamanlı panolar oluşturma aşamaları ele alınır.

Şimdi tüm ham web trafiği verilerimizi kullanan 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, dakikalık toplamlarımızı tutan bir tablo ve son toplulaştırma işlemimizin konumunu tutan 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 tabloları paylaştırmak

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

Şimdi dağıtım sütununu (parça anahtarı) 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');

Uyarı

PostgreSQL için Azure Veritabanı performans özelliklerinin elastik kümelerinden yararlanmak için tabloları dağıtmak veya şema tabanlı parçalama kullanmak gerekir. Tabloları veya şemaları dağıtana kadar, küme düğümleriniz verileriyle ilgili dağıtılmış sorgular çalıştırmaz.

Ö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. Dağıtım sütunu site_id'e göre satırlar farklı çalışan düğümlerde depolanır.

Uyarı

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.

Query

PostgreSQL için Azure Veritabanı elastik kümeleri, 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 (SUM) ve BAĞ_DEĞ_SAY (COUNT) gibi kümülasyonları hesaplar ve bunların sonuçlarını nihai 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 derlemek, 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;

İşlevimizi yerine koyduğumuzda, verileri derlemek için bu işlevi çalıştırın.

SELECT rollup_http_request();

Verilerimiz önceden toplanmış bir biçimde olduğundan, daha ö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 kontrolsüz büyüyen depolama maliyetlerinden kaçınmak için eski verileri kaldırmamız 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.

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

Elastik kümeler hakkında daha fazla bilgi edinin