Lernprogramm: Entwerfen eines Echtzeitanalysedashboards mit elastischen Clustern

In diesem Tutorial verwenden Sie elastische Cluster in der Azure-Datenbank für PostgreSQL-Elastic-Cluster, um zu erfahren, wie Sie ein Echtzeit-Dashboard entwerfen und Abfragen parallelisieren.

• Voraussetzungen
• Verwenden des psql-Hilfsprogramms zum Erstellen eines Schemas
• Knotenübergreifende Shard-Tabellen
• Generieren von Beispieldaten
• Ausführen von Rollups
• Abfragen von Rohdaten und aggregierten Daten
• Ablauf von Daten

Voraussetzungen

Erstellen Sie einen elastischen Cluster auf eine der folgenden Arten:

• Erstellen eines elastischen Clusters mithilfe des Portals
• Erstellen eines elastischen Clusters mit Bicep
• Erstellen eines elastischen Clusters mit ARM-Vorlage

Verwenden des psql-Hilfsprogramms zum Erstellen eines Schemas

Nachdem Sie mit psql mit dem elastischen Cluster verbunden sind, können Sie Ihren elastischen Cluster konfigurieren. Dieses Tutorial führt Sie schrittweise durch das Erfassen von Verkehrsdaten aus Webanalysen und den anschließenden Rollup der Daten, um Echtzeitdashboards auf der Grundlage dieser Daten bereitzustellen.

Nun erstellen Sie eine Tabelle, die sämtliche Webdatenverkehrs-Rohdaten erfasst. Führen Sie die folgenden Befehle im psql-Terminal aus:

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

Außerdem erstellen Sie eine Tabelle, die die minütlichen Aggregationen aufnimmt, und eine Tabelle, die die Position des letzten Rollups verwaltet. Führen Sie die folgenden Befehle ebenfalls in psql aus:

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

Sie können jetzt die neu erstellten Tabellen in der Liste der Tabellen mit diesem psql-Befehl anzeigen:

\dt

Knotenübergreifende Shard-Tabellen

Eine elastische Clusterbereitstellung speichert Tabellenzeilen auf unterschiedlichen Knoten basierend auf dem Wert einer vom Benutzer festgelegten Spalte. Die „Verteilungsspalte“ gibt an, wie die Daten auf die Knoten verteilt sind.

Legen Wir nun die Verteilungsspalte (Shardschlüssel) so fest, dass sie site_id wird. Führen Sie in psql diese Funktionen aus:

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

Hinweis

Das Verteilen von Tabellen oder die Verwendung schemabasierter Sharding ist erforderlich, um die Vorteile von elastischen Clustern der Azure-Datenbank für PostgreSQL-Leistungsfeatures zu nutzen. Bis Sie Tabellen oder Schemas verteilen, werden ihre Clusterknoten keine verteilten Abfragen mit ihren Daten ausführen.

Generieren von Beispieldaten

Jetzt sollte unser Cluster für die Erfassung einiger Daten bereit sein. Wir können den folgenden Code lokal aus unserer psql-Verbindung ausführen, um fortlaufend Daten einzufügen.

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

Die Abfrage fügt ca. acht Zeilen pro Sekunde ein. Die Zeilen werden auf Basis ihrer Verteilungsspalte site_id auf unterschiedlichen Arbeitsknoten gespeichert.

Hinweis

Führen Sie die Abfrage zur Datengenerierung weiter aus, und öffnen Sie für die verbleibenden Befehle in diesem Tutorial eine zweite psql-Verbindung.

Abfrage

Elastische Cluster in Azure Database for PostgreSQL ermöglichen es aus Geschwindigkeitsgründen, dass Abfragen auf mehreren Knoten verarbeitet werden. Beispielsweise berechnet die Datenbank Aggregate wie SUM und COUNT auf Workerknoten und kombiniert die Ergebnisse in einer endgültigen Antwort.

Die folgende Abfrage zählt die Webanforderungen pro Minute zusammen mit einer Reihe von Statistikangaben. Versuchen Sie, sie in psql auszuführen, und beobachten Sie die Ergebnisse.

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;

Rollup von Daten

Die vorherige Abfrage funktioniert gut in den frühen Phasen, aber mit zunehmender Datenmenge nimmt ihre Leistung ab. Selbst bei verteilter Verarbeitung ist es schneller, diese Daten vorab zu berechnen, als sie wiederholt neu zu berechnen.

Wir können sicherstellen, dass unser Dashboard schnell bleibt, indem wir regelmäßig einen Rollup der Rohdaten in eine Aggregattabelle ausführen. Sie können mit der Aggregationsdauer experimentieren. Wir haben eine minütliche Aggregationstabelle verwendet, aber Sie könnten Daten stattdessen nach 5, 15 oder 60 Minuten aufschlüsseln.

Um dieses Rollup einfacher auszuführen, bringen wir es in eine plpgsql-Funktion ein. Führen Sie diese Befehle in psql aus, um die Funktion rollup_http_request zu erstellen.

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

Wenn unsere Funktion an Ort und Stelle ist, führen Sie sie aus, um den Rollup der Daten auszuführen:

SELECT rollup_http_request();

Wenn die Daten in vorab aggregierter Form vorliegen, können Sie außerdem die Rolluptabelle abfragen, um den gleichen Bericht wie zuvor zu erhalten. Führen Sie die folgende Abfrage aus:

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;

Ablauf alter Daten

Durch die Rollups werden die Abfragen schneller, wir müssen aber trotzdem alte Daten ablaufen lassen, um aus dem Ruder laufende Speicherkosten zu vermeiden. Entscheiden Sie, wie lange Sie Daten für die einzelnen Granularitäten aufbewahren möchten, und verwenden Sie Standardabfragen, um abgelaufene Daten zu löschen. Im folgenden Beispiel haben wir beschlossen, Rohdaten einen Tag lang und minütliche Aggregationen einen Monat lang aufzubewahren:

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

In einer Produktionsumgebung könnten Sie diese Abfragen in einer Funktion verpacken und sie jede Minute in einem cron-Auftrag aufrufen.

Nächster Schritt

In diesem Lernprogramm haben Sie gelernt, wie Sie einen elastischen Cluster erstellen. Sie haben mithilfe von psql eine Verbindung mit ihr hergestellt, haben ein Schema erstellt und Daten verteilt. Sie haben gelernt, Daten in Rohform abzurufen und diese Daten regelmäßig zu aggregieren, die aggregierten Tabellen abzufragen und alte Daten ablaufen zu lassen.

Weitere Informationen zu elastischen Clustern