Tutorial: Entwerfen eines Dashboards zur Echtzeitanalyse mithilfe von Azure Cosmos DB for PostgreSQL

  • Artikel

GILT FÜR: Azure Cosmos DB for PostgreSQL (unterstützt von der Citus-Datenbankerweiterung auf PostgreSQL)

In diesem Tutorial verwenden Sie Azure Cosmos DB for PostgreSQL, um Folgendes zu lernen:

  • Erstellen eines Clusters
  • 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

Wenn Sie kein Azure-Abonnement besitzen, können Sie ein kostenloses Konto erstellen, bevor Sie beginnen.

Erstellen eines Clusters

Melden Sie sich beim Azure-Portal an, und führen Sie die folgenden Schritte aus, um einen Azure Cosmos DB for PostgreSQL-Cluster zu erstellen:

Rufen Sie im Azure-Portal Azure Cosmos DB for PostgreSQL-Cluster erstellen auf.

Gehen Sie im Formular Azure Cosmos DB for PostgreSQL-Cluster erstellen wie folgt vor:

  1. Geben Sie die Informationen auf der Registerkarte Grundlagen ein.

    Screenshot showing the Basics tab of the Create screen.

    Die meisten Optionen sind selbsterklärend, denken Sie jedoch an Folgendes:

    • Der Clustername bestimmt den DNS-Namen, den Ihre Anwendungen zum Herstellen einer Verbindung verwenden. Geben Sie den Namen im Format <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com an.
    • Sie können eine höhere PostgreSQL-Version wie 15 auswählen. Azure Cosmos DB für PostgreSQL unterstützt immer die neueste Citus-Version für die ausgewählte Postgres-Hauptversion.
    • Der Administratorbenutzername muss den Wert citus aufweisen.
    • Sie können den Datenbanknamen auf dem Standardwert „citus“ belassen oder einen eigenen Datenbanknamen festlegen. Nach der Clusterbereitstellung können Sie den Namen der Datenbank nicht mehr ändern.

  2. Wählen Sie am unteren Rand des Bildschirms Weiter: Netzwerk aus.

  3. Wählen Sie auf dem Bildschirm Netzwerk die Option Von Azure-Diensten und -Ressourcen in Azure aus öffentlichen Zugriff auf diesen Cluster gestatten aus.

    Screenshot showing the Networking tab of the Create screen.

  4. Wählen Sie Überprüfen und erstellen und nach bestandener Überprüfung Erstellen aus, um den Cluster zu erstellen.

  5. Die Bereitstellung dauert einige Minuten. Die Seite leitet Sie zur Überwachung der Bereitstellung weiter. Wählen Sie Zu Ressource wechseln aus, wenn sich der Status von Bereitstellung wird ausgeführt in Ihre Bereitstellung wurde abgeschlossen. ändert.

Verwenden des psql-Hilfsprogramms zum Erstellen eines Schemas

Nachdem Sie mithilfe von psql eine Verbindung mit Azure Cosmos DB for PostgreSQL hergestellt haben, können Sie einige einfache Aufgaben ausführen. 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.

Erstellen wir eine Tabelle, die unsere gesamten Webdatenverkehrs-Rohdaten nutzt. 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
);

Wir erstellen darüber hinaus eine Tabelle, die unsere minütlichen Aggregationen aufnimmt und eine Tabelle, die die Position unseres 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 Azure Cosmos DB for PostgreSQL-Bereitstellung speichert Tabellenzeilen basierend auf dem Wert einer vom Benutzer zugeordneten Spalte auf verschiedenen Knoten. Die „Verteilungsspalte“ gibt an, wie die Daten auf die Knoten verteilt sind.

Legen Sie die Verteilungsspalte auf „site_id“ fest, den Shardschlüssel. 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');

Wichtig

Die Verteilung von Tabellen oder die Verwendung von schemabasiertem Sharding ist erforderlich, um die Vorteile der Leistungsfeatures von Azure Cosmos DB for PostgreSQL zu nutzen. Wenn Sie keine Tabellen oder Schemas verteilen, können Workerknoten nicht beim Ausführen von Abfragen helfen, die ihre Daten betreffen.

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 verschiedenen Workerknoten gespeichert, gemäß Anweisung durch die Verteilungsspalte site_id.

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

Azure Cosmos DB for PostgreSQL ermöglicht es aus Geschwindigkeitsgründen, dass Abfragen von mehreren Knoten parallel 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();

Und wenn unsere Daten in voraggregierter Form vorliegen, können wir 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.

Bereinigen von Ressourcen

In den vorherigen Schritten haben Sie Azure-Ressourcen in einem Cluster erstellt. Wenn Sie diese Ressourcen nicht mehr benötigen, löschen Sie den Cluster. Klicken Sie auf der Seite Übersicht für Ihr Cluster auf die Schaltfläche Löschen. Bestätigen Sie den Namen des Clusters, wenn Sie auf einer Popupseite dazu aufgefordert werden, und klicken Sie abschließend auf die Schaltfläche Löschen.

Nächste Schritte

In diesem Tutorial haben Sie erfahren, wie Sie einen Cluster bereitstellen. 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.