Samouczek: projektowanie pulpitu nawigacyjnego analizy w czasie rzeczywistym za pomocą klastrów elastycznych

W tym samouczku użyjesz klastrów elastycznych usługi Azure Database for PostgreSQL, by nauczyć się, jak zaprojektować pulpit nawigacyjny w czasie rzeczywistym i zrównoleglać zapytania.

• Wymagania wstępne
• Tworzenie schematu za pomocą narzędzia psql
• Fragmentacja tabel po węzłach
• Generowanie danych przykładowych
• Wykonywanie zestawień
• Wykonywanie zapytań dotyczących danych pierwotnych i zagregowanych
• Wygasanie danych

Wymagania wstępne

Utwórz klaster elastyczny na jeden z następujących sposobów:

• Tworzenie klastra elastycznego przy użyciu portalu
• Tworzenie klastra elastycznego przy użyciu Bicep
• Tworzenie klastra elastycznego przy użyciu szablonu ARM

Tworzenie schematu za pomocą narzędzia psql

Po nawiązaniu połączenia z klastrem elastycznym przy użyciu narzędzia psql można skonfigurować klaster elastyczny. W tym samouczku przedstawiono proces pozyskiwania danych ruchu z analizy internetowej, a następnie zwijania danych w celu zapewnienia pulpitów nawigacyjnych w czasie rzeczywistym na podstawie tych danych.

Utwórzmy tabelę, która używa wszystkich naszych nieprzetworzonych danych dotyczących ruchu internetowego. Uruchom następujące polecenia w terminalu programu psql:

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

Utworzymy również tabelę, która przechowuje nasze agregacje na minutę, oraz tabelę, która utrzymuje pozycję naszego ostatniego zestawienia. Uruchom również następujące polecenia w narzędziu psql:

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

Nowo utworzone tabele są teraz widoczne na liście tabel za pomocą tego polecenia psql:

\dt

Fragmentacja tabel po węzłach

Wdrożenie klastra elastycznego przechowuje wiersze tabeli na różnych węzłach na podstawie wartości kolumny wyznaczonej przez użytkownika. Kolumna rozdzielająca oznacza, jak dane są rozdysponowane pomiędzy węzłami.

Ustawmy kolumnę dystrybucji (klucz fragmentu) na site_id. W narzędziu psql uruchom następujące funkcje:

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

Uwaga / Notatka

Rozpowszechnianie tabel lub używanie fragmentowania opartego na schemacie jest niezbędne do korzystania z elastycznych klastrów funkcji wydajności usługi Azure Database for PostgreSQL. Dopóki tabele lub schematy nie będą dystrybuowane, węzły klastra nie będą uruchamiać rozproszonych zapytań dotyczących ich danych.

Generowanie danych przykładowych

Teraz nasz klaster powinien być gotowy do pozyskiwania niektórych danych. Możemy uruchomić następujące polecenie lokalnie z naszego psql połączenia, aby stale wstawiać dane.

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

Zapytanie wstawia około 8 wierszy każdą sekundę. Wiersze są przechowywane w różnych węzłach roboczych na podstawie kolumny dystrybucji site_id.

Uwaga / Notatka

Pozostaw uruchomione zapytanie generowania danych i otwórz drugie połączenie psql dla pozostałych poleceń w tym samouczku.

Query

Elastyczne klastry usługi Azure Database for PostgreSQL umożliwiają wielu węzłom równoległe przetwarzanie zapytań w celu uzyskania szybkości. Na przykład baza danych oblicza agregacje, takie jak SUM i COUNT w węzłach roboczych, i łączy wyniki w ostateczną odpowiedź.

Oto zapytanie do zliczywania żądań internetowych na minutę wraz z kilkoma statystykami. Spróbuj uruchomić go w narzędziu psql i obserwuj wyniki.

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;

Zwijanie danych

Poprzednie zapytanie działa prawidłowo we wczesnych etapach, ale jego wydajność spada w miarę skalowania danych. Nawet w przypadku przetwarzania rozproszonego szybsze jest wstępne obliczenie danych niż ponowne obliczenie ich wielokrotnie.

Możemy zapewnić, że nasz pulpit nawigacyjny pozostaje szybki, regularnie przetwarzając nieprzetworzone dane do tabeli zagregowanej. Możesz eksperymentować z czasem trwania agregacji. Użyliśmy tabeli agregacji na minutę, ale zamiast tego można podzielić dane na 5, 15 lub 60 minut.

Aby łatwiej wykonać tę zbiorczą operację, umieścimy ją w funkcji plpgsql. Wykonaj te polecenia w psql, aby utworzyć funkcję 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;

Po wdrożeniu funkcji wykonaj ją, aby zwijać dane:

SELECT rollup_http_request();

Dzięki naszym danym w formularzu wstępnie zagregowanym możemy wysłać zapytanie do tabeli zestawień, aby uzyskać ten sam raport co wcześniej. Uruchom następujące zapytanie:

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;

Wygasające stare dane

Dane sumaryczne przyspieszają wykonywanie zapytań, ale nadal musimy usuwać stare dane, aby uniknąć nieograniczonych kosztów przechowywania. Zdecyduj, jak długo chcesz przechowywać dane dla każdego stopnia szczegółowości, i użyj standardowych zapytań, aby usunąć wygasłe dane. W poniższym przykładzie postanowiliśmy zachować nieprzetworzone dane przez jeden dzień i agregacje na minutę przez jeden miesiąc:

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

W środowisku produkcyjnym można opakowować te zapytania w funkcji i wywoływać je co minutę w zadaniu cron.

Następny krok

W tym samouczku nauczyłeś się, jak stworzyć elastyczny klaster. Podłączyłeś się do niego za pomocą narzędzia psql, utworzyłeś schemat i rozprowadziłeś dane. Wiesz już, jak wykonywać zapytania dotyczące danych w postaci pierwotnej, regularnie agregować te dane, wykonywać zapytania względem zagregowanych tabel i wygasać stare dane.

Dowiedz się więcej o klastrach elastycznych