Öğretici: PostgreSQL için Azure veritabanı – hiper ölçek (Citus) kullanarak gerçek zamanlı analiz panosu 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 öğretici, Web analizinden trafik verilerini geri alma ve bu verileri temel alan gerçek zamanlı panolar sağlamak üzere verileri toplama konusunda size kılavuzluk eder.

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 toplaımuzu ve son toplaımızın konumunu tutan bir tabloyu barındıracak bir tablo oluşturacağız. Psql 'de aşağıdaki komutları da ç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 birlikte Tablo listesinde görebilirsiniz:

\dt

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

Bir hiper ölçek dağıtımı, tablo satırlarını Kullanıcı tarafından belirlenen bir sütunun değerine göre farklı düğümlere depolar. Bu "dağıtım sütunu", verilerin düğümler arasında nasıl parçalanmış olduğunu işaretler.

Dağıtım sütununu site kimliği, parça anahtarı olacak şekilde ayarlayalim _ . 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');

Örnek veri oluşturma

Artık sunucu grubumuz bazı verileri almaya hazır olmalıdır. psqlSürekli olarak veri eklemek için bağlantımızdan aşağıdakileri 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ütunuyla yönlendirildiği şekilde farklı çalışan düğümlerinde depolanır site_id .

Sorgu

Hiper ölçek barındırma seçeneği, birden çok düğümün hız için paralel olarak sorguları işlemesini sağlar. Örneğin, veritabanı, çalışan düğümlerinde SUM ve say gibi toplamları hesaplar ve sonuçları nihai bir yanıt olarak birleştirir.

İşte birkaç istatistikle birlikte, dakikada Web isteklerini saymak için bir sorgu. 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 toplama

Önceki sorgu erken aşamalarda ince çalışıyor, ancak verileriniz ölçeklenmesi halinde performansı düşürür. Dağıtılmış işleme dahil olmak üzere, verilerin tekrar tekrar hesaplanmasını yerine önceden hesaplamanız daha hızlıdır.

Ham verileri düzenli olarak toplam bir tabloya aktararak panonuzun hızlı bir şekilde devam etmemesini sağlayabilirsiniz. Toplama süresi ile denemeler yapabilirsiniz. Dakika başına toplama tablosu kullandık, ancak bunun yerine verileri 5, 15 veya 60 dakikaya kesebilirsiniz.

Bu toplaması daha kolay bir şekilde çalıştırmak için bunu bir plpgsql işlevine koyacağız. İşlevi oluşturmak için psql 'de bu komutları çalıştırın rollup_http_request .

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

İşlevimizde, verileri toplamak için yürütün:

SELECT rollup_http_request();

Önceden toplanmış bir formdaki verilerimizde, daha önce de 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 sınırsız depolama maliyetlerinden kaçınmak için eski verilerin süresini de sona ermemiz gerekir. Her ayrıntı düzeyi için verileri ne kadar süreyle saklamak istediğinize karar verin ve zaman aşımına uğramış verileri silmek için standart sorguları kullanın. Aşağıdaki örnekte, ham verileri bir gün ve dakika başına toplamalar için bir ay 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 kaydırabilirsiniz ve bir cron işinde her dakika çağırabilirsiniz.

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. Ham formdaki verileri sorgulamayı, verileri düzenli olarak toplamasını, toplanmış tabloları sorgulamayı ve eski verilerin zaman dolmasını öğrendiniz.

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