Zelfstudie: Zelfstudie: een database met realtime analyses ontwerpen met behulp van Azure Database for PostgreSQL – Hyperscale (Citus)

In deze zelfstudie gebruikt u Azure Database for PostgreSQL - Hyperscale (Citus) om te leren hoe u de volgende bewerkingen uitvoert:

Vereisten

Een Hyperscale (Citus)-servergroep maken

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

Aanmelden bij Azure Portal

Meld u aan bij de Azure-portal.

Volg deze stappen voor het maken van een Azure Database voor PostgreSQL-server:

1. Klik in de linkerbovenhoek van Azure Portal op Een resource maken.

2. Selecteer Databases op de pagina Nieuw en selecteer Azure Database voor PostgreSQL op de pagina Databases.

3. Klik voor de implementatieoptie op de knop Maken onder Servergroep voor Hyperscale (Citus) .

4. Vul het formulier voor de gegevens van de nieuwe server als volgt in:

   • Resourcegroep: klik op de koppeling Nieuwe maken onder het tekstvak voor dit veld. Voer een naam in, bijvoorbeeld mijnresourcegroep.
   • Naam van servergroep: voer een unieke naam in voor de nieuwe servergroep, die ook wordt gebruikt voor een subdomein van de server.
   • Gebruikersnaam van beheerder: momenteel is de waarde citus vereist en kan dit niet worden gewijzigd.
   • Wachtwoord: Uw wachtwoord moet minimaal 8 tekens lang zijn en tekens bevatten uit drie van de volgende categorieën: Nederlandse hoofdletters, Nederlandse kleine letters, cijfers (0-9) en niet-alfanumerieke tekens (!, $, #, %, enzovoort).
   • Locatie: gebruik de locatie die zich het dichtst bij uw gebruikers bevindt, zodat ze de snelst mogelijke toegang tot de gegevens hebben.

   Belangrijk

   Het wachtwoord van de serverbeheerder die u hier opgeeft, is vereist voor aanmelding bij de server en de bijbehorende databases. Onthoud of noteer deze informatie voor later gebruik.

5. Klik op Servergroep configureren. Laat de instellingen in die sectie ongewijzigd en klik op Opslaan.

6. Klik op Volgende: Netwerken > onderaan het scherm.

7. Selecteer op het tabblad Netwerken de optie Openbare toegang toestaan vanuit Azure-services en -resources in Azure voor deze servergroep. Selecteer vervolgens + Huidig IP-adres van client toevoegen.

   Notitie

   De Azure PostgreSQL-server communiceert via poort 5432. Als u verbinding probeert te maken vanuit een bedrijfsnetwerk, wordt uitgaand verkeer via poort 5432 mogelijk niet toegestaan door de firewall van uw netwerk. In dat geval kunt u alleen verbinding maken met uw Hyperscale (Citus)-cluster als uw IT-afdeling poort 5432 openstelt.

8. Klik op Beoordelen en maken en vervolgens op Maken om de server in te richten. De inrichting duurt een paar minuten.

9. De pagina wordt omgeleid om de implementatie te bewaken. Wanneer de Live-status wordt gewijzigd van Uw implementatie wordt uitgevoerd in Uw implementatie is voltooid, klikt u op de menuopdracht Uitvoer aan de linkerkant van de pagina.

10. De pagina Uitvoer bevat een coördinatorhostnaam met een knop ernaast om de waarde naar het klembord te kopiëren. Noteer deze informatie voor later gebruik.

Verbinding maken met de database met behulp van psql

Wanneer u de Azure Database for PostgreSQL-server maakt, wordt een standaarddatabase met de naam citus gemaakt. Als u verbinding wilt maken met uw databaseserver, hebt u een verbindingsreeks en het beheerderswachtwoord nodig.

1. Haal de verbindingsreeks op. Klik op de pagina Servergroep op de menuopdracht Verbindingsreeksen. (Deze bevindt zich onder Instellingen.) Zoek de tekenreeks met de markering psql. Deze heeft de volgende vorm:

   psql "host=hostname.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
   

   Kopieer de tekenreeks. Vervang "{your_password}" door het beheerderswachtwoord dat u eerder hebt gekozen. Uw leesbare wachtwoord wordt niet opgeslagen in het systeem en kan dus niet worden weergegeven in de verbindingsreeks.

2. Open een terminalvenster op de lokale computer.

3. In de prompt maakt u verbinding met uw Azure Database for PostgreSQL-server via het psql-hulpprogramma. Geef uw verbindingsreeks tussen haakjes door, en zorg ervoor dat het uw wachtwoord bevat:

   psql "host=..."
   

   Met de volgende opdracht maakt u bijvoorbeeld verbinding met het coördinatorknooppunt van de servergroep mydemoserver:

   psql "host=mydemoserver-c.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
   

Het hulpprogramma psql gebruiken om een schema te maken

Zodra u verbinding hebt gemaakt met de Azure Database for PostgreSQL - Hyperscale (Citus) met behulp van psql, kunt u een aantal basistaken voltooien. 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 hyperscale-implementatie worden tabelrijen op verschillende knooppunten opgeslagen op basis van de waarde van een door de gebruiker opgegeven kolom. Deze 'distributiekolom' markeert hoe sharding op deze gegevens worden toegepast tussen knooppunten.

We gaan de distributiekolom instellen als 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');

Voorbeeldgegevens genereren

Nu moet onze servergroep gereed zijn om 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.

Query’s uitvoeren

Met de optie voor hyperscale hosten kunnen meerdere knooppunten gelijktijdig query's verwerken om het proces sneller te laten verlopen. 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 resourcegroep gemaakt. Als u deze resources in de toekomst niet nodig denkt te hebben, kunt u de servergroep verwijderen. Druk op de knop Verwijderen op de pagina Overzicht voor uw servergroep. Wanneer u daarom op een pop-uppagina wordt gevraagd, bevestigt u de naam van de servergroep en klikt u op de laatste knop Verwijderen.

Volgende stappen

In deze zelfstudie hebt u geleerd hoe u een Hyperscale (Citus)-servergroep 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 knooppunttypen voor servergroepen
• Bepaal de beste begingrootte voor uw servergroep