Zelfstudie: Een realtime analysedashboard ontwerpen met behulp van Azure Cosmos DB for PostgreSQL

  • Artikel

VAN TOEPASSING OP: Azure Cosmos DB for PostgreSQL (mogelijk gemaakt door de Citus-database-extensie naar PostgreSQL)

In deze zelfstudie gebruikt u Azure Cosmos DB for PostgreSQL voor meer informatie over het volgende:

  • Een cluster maken
  • Het hulpprogramma psql gebruiken om een schema te maken
  • Sharding toepassen op tabellen tussen knooppunten
  • Voorbeeldgegevens genereren
  • Rollups uitvoeren
  • Query's uitvoeren op onbewerkte en geaggregeerde gegevens
  • Gegevens laten verlopen

Vereisten

Als u geen Azure-abonnement hebt, maakt u een gratis account voordat u begint.

Een cluster maken

Meld u aan bij Azure Portal en volg deze stappen om een Azure Cosmos DB for PostgreSQL-cluster te maken:

Ga naar Een Azure Cosmos DB for PostgreSQL-cluster maken in de Azure-portal.

In het formulier Een Azure Cosmos DB for PostgreSQL-cluster maken:

  1. Vul de gegevens in op het tabblad Basis.

    Screenshot showing the Basics tab of the Create screen.

    De meeste opties spreken voor zich, maar onthoud:

    • De clusternaam bepaalt de DNS-naam die uw toepassingen gebruiken om verbinding te maken, in de vorm <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
    • U kunt een primaire PostgreSQL-versie, zoals 15, kiezen. Azure Cosmos DB for PostgreSQL ondersteunt altijd de nieuwste Citus-versie voor de geselecteerde primaire Postgres-versie.
    • De gebruikersnaam van de beheerder moet de waarde citus zijn.
    • U kunt de databasenaam op de standaardwaarde citus laten staan of de enige databasenaam definiëren. U kunt de naam van de database niet wijzigen nadat het cluster is ingericht.

  2. Selecteer Volgende: Netwerken onderaan het scherm.

  3. Selecteer in het scherm Netwerken de optie Openbare toegang toestaan vanuit Azure-services en -resources binnen Azure naar dit cluster.

    Screenshot showing the Networking tab of the Create screen.

  4. Selecteer Beoordelen en maken en selecteer vervolgens, wanneer de validatie is geslaagd, Maken om het cluster te maken.

  5. De inrichting duurt een paar minuten. De pagina wordt omgeleid om de implementatie te bewaken. Wanneer de status verandert van Implementatie wordt uitgevoerd naar Uw implementatie is voltooid, selecteer dan Naar resource.

Het hulpprogramma psql gebruiken om een schema te maken

Nadat u met behulp van psql verbinding hebt gemaakt met Azure Cosmos DB for PostgreSQL, kunt u enkele basistaken uitvoeren. In deze zelfstudie wordt uitgelegd hoe u verkeersgegevens opneemt vanuit Web Analytics en vervolgens de gegevens samenvoegt om dashboards in realtime te bieden op basis van die gegevens.

We gaan een tabel maken waarin al onze onbewerkte webverkeersgegevens worden gebruikt. Voer de volgende opdrachten uit in de psql-terminal:

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

U gaat ook een tabel maken waarin onze aggregaties per minuut worden bewaard, en een tabel die de positie van onze laatste rollup behoudt. Voer de volgende opdrachten ook in psql uit:

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

U kunt de zojuist gemaakte tabellen nu in de lijst met tabellen in psql zien met deze psql-opdracht:

\dt

Sharding toepassen op tabellen tussen knooppunten

In een Azure Cosmos DB for PostgreSQL-implementatie worden tabelrijen op verschillende knooppunten opgeslagen op basis van de waarde van een door de gebruiker aangewezen kolom. Deze 'distributiekolom' markeert hoe sharding op deze gegevens worden toegepast tussen knooppunten.

Laten we de distributiekolom instellen op site_id, de shardsleutel. Voer in psql deze functies uit:

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

Belangrijk

Het distribueren van tabellen of het gebruik van sharding op basis van schema's is nodig om te profiteren van de prestatiefuncties van Azure Cosmos DB for PostgreSQL. Als u geen tabellen of schema's distribueert, kunnen werkknooppunten u niet helpen bij het uitvoeren van query's met betrekking tot hun gegevens.

Voorbeeldgegevens genereren

Nu moet ons cluster klaar zijn om enkele gegevens op te nemen. We kunnen het volgende lokaal uitvoeren vanaf onze verbinding psql om continu gegevens in te voegen.

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

De query voegt elke seconde ongeveer acht rijen in. De rijen worden op verschillende werkknooppunten opgeslagen, aangestuurd door het distributiekolom, site_id.

Notitie

Laat de query voor het genereren van gegevens lopen en open een tweede psql-verbinding voor de resterende opdrachten in deze zelfstudie.

Query’s uitvoeren

Met Azure Cosmos DB for PostgreSQL kunnen meerdere knooppunten query's parallel verwerken voor snelheid. De database berekent bijvoorbeeld aggregaties zoals SUM en COUNT op werkknooppunten en combineert de resultaten in een eindantwoord.

Hier volgt een query voor het tellen van webaanvragen per minuut, samen met een aantal statistieken. Probeer de query in psql uit te voeren en bekijk de resultaten.

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 van gegevens

De vorige query werkt in de vroege stadia prima, maar de prestaties verslechteren naarmate uw gegevens worden geschaald. Zelfs bij gedistribueerde verwerking is het sneller om de gegevens vooraf te berekenen dan om deze herhaaldelijk opnieuw te berekenen.

We kunnen zorgen het dashboard snel blijft werken door de onbewerkte gegevens regelmatig in een aggregatietabel te verdelen. U kunt experimenteren met de aggregatieduur. We hebben een aggregatietabel per minuut gebruikt, maar u kunt gegevens in plaats daarvan in vijf, 15 of 60 minuten indelen.

Als u deze rollup eenvoudiger wilt uitvoeren, gaan we deze in een plpgsql-functie plaatsen. Voer deze opdrachten uit in psql om de functie rollup_http_request te maken.

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

Als onze functie is geïmplementeerd, voert u deze uit om de gegevens samen te tellen:

SELECT rollup_http_request();

En met onze gegevens in een vooraf geaggregeerd formulier kunnen we een query uitvoeren op het rolluptabel om hetzelfde rapport te verkrijgen als eerder. Voer de volgende query uit.

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;

Oude gegevens laten verlopen

De rollups maken query's sneller, maar we moeten nog steeds oude gegevens laten verlopen om overmatige opslagkosten te voorkomen. Bepaal hoelang u gegevens voor elke granulariteit wilt behouden en gebruik standaardquery's om verlopen gegevens te verwijderen. In het volgende voorbeeld hebben we besloten om onbewerkte gegevens voor één dag en aggregaties per minuut gedurende één maand te houden:

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

Tijdens de productie kunt u deze query's in een functie laten teruglopen en deze elke minuut aanroepen in een cron-taak.

Resources opschonen

In de voorgaande stappen hebt u Azure-resources in een cluster gemaakt. Als u deze resources in de toekomst niet meer nodig hebt, verwijdert u het cluster. Druk op de knop Verwijderen op de pagina Overzicht voor uw cluster. Wanneer u hierom wordt gevraagd op een pop-uppagina, bevestigt u de naam van het cluster en klikt u op de laatste knop Verwijderen .

Volgende stappen

In deze zelfstudie hebt u geleerd hoe u een cluster inricht. U hebt psql gebruikt om hiermee verbinding te maken, u hebt een schema gemaakt en u hebt gegevens gedistribueerd. U hebt geleerd hoe u gegevens in onbewerkte vorm opvraagt, regelmatig de gegevens aggregeert, query's uitvoert op de geaggregeerde tabellen en oude gegevens laat verlopen.

  • Meer informatie over clusterknooppunttypen
  • Bepaal de beste initiële grootte voor uw cluster