Självstudie: utforma en instrument panel med real tids analys med Azure Database for PostgreSQL – storskalig (citus)

I den här självstudien använder du Azure Database for PostgreSQL-storskalig skalning (citus) för att lära dig att:

Förutsättningar

Skapa en Hyperscale-servergrupp (Citus)

Om du inte har en Azure-prenumeration kan du skapa ett kostnadsfritt konto innan du börjar.

Logga in på Azure Portal

Logga in på Azure-portalen.

Följ de här stegen för att skapa en Azure Database för PostgreSQL-server:

1. Klicka på Skapa en resurs i det övre vänstra hörnet av Azure Portal.

2. Välj databaser från sidan Nytt och välj Azure Database för PostgreSQL från sidan databaser.

3. För distributionsalternativet klickar du på knappen Skapa under Hyperskala (Citus) servergrupp.

4. Fyll i formuläret om den nya servern och uppge följande information:

   • Resursgrupp: Klicka på länken Skapa ny under textrutan för det här fältet. Ange ett namn, till exempel myresourcegroup.
   • Namn på servergrupp: Ange ett unikt namn för den nya servergruppen, som även ska användas för en serverunderdomän.
   • Administratörsanvändarnamn: måste för närvarande vara värdet citus och kan inte ändras.
   • Lösenord: måste vara minst åtta tecken långt och innehålla tecken från tre av följande kategorier – engelska versala bokstäver, engelska gemener, siffror (0–9) och icke-alfanumeriska tecken (!, $, #, %, osv.)
   • Plats: Använd den plats som är närmast dina användare för att ge dem den snabbaste åtkomsten till data.

   Viktigt

   Det serveradministratörslösenord som du anger här krävs för att logga in på servern och dess databaser. Kom ihåg eller skriv ned den här informationen så att du kan använda den senare.

5. Klicka på Konfigurera servergruppen. Lämna inställningarna i det avsnittet oförändrade och klicka på Spara.

6. Klicka på Nästa: > nätverk längst ned på skärmen.

7. På fliken Nätverk väljer du Tillåt offentlig åtkomst från Azure-tjänster och -resurser i Azure till den här servergruppen. Välj sedan + Lägg till aktuell klient-IP-adress.

   Anteckning

   Azure PostgreSQL-servern kommunicerar via port 5432. Om du försöker ansluta inifrån ett företagsnätverk, kan utgående trafik via port 5432 bli nekad av nätverkets brandvägg. I så fall kan du inte ansluta till Hyperskala (Citus) kluster om inte IT-avdelningen öppnar port 5432.

8. Klicka på Granska + skapa och sedan på Skapa för att etablera servern. Etableringen tar några minuter.

9. Sidan omdirigeras för att övervaka distributionen. När livestatusen ändras från Distributionen är på gång till Distributionen är klar klickar du på menyalternativet Utdata till vänster på sidan.

10. Utdatasidan innehåller ett värdnamn för koordinatorn med en knapp bredvid sig för att kopiera värdet till Urklipp. Registrera den här informationen för senare användning.

Anslut till databasen med psql

När du skapar Azure Database for PostgreSQL server skapas en standarddatabas med namnet citus. Om du vill ansluta till databasservern behöver du en anslutningssträng och administratörslösenordet.

1. Hämta anslutningssträngen. På servergruppsidan klickar du på menyalternativet Anslutningssträngar. (Den är under Inställningar.) Hitta strängen som är markerad som psql. Den kommer att ha följande format:

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

   Kopiera strängen. Du måste ersätta "{ditt _ lösenord}" med det administrativa lösenord som du valde tidigare. Systemet lagrar inte lösenordet i klartext och kan därför inte visa det åt dig i anslutningssträngen.

2. Öppna ett terminalfönster på den lokala datorn.

3. I kommandotolken ansluter du till Azure Database for PostgreSQL-servern med psql-verktyget. Skicka anslutningssträngen inom citattecken och kontrollera att den innehåller ditt lösenord:

   psql "host=..."
   

   Följande kommando ansluter till exempel till koordinatornoden för servergruppen mydemoserver:

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

Använd psql-verktyget för att skapa ett schema

När du har anslutit till Azure Database for PostgreSQL skala (citus) med psql kan du utföra några grundläggande uppgifter. Den här självstudien vägleder dig genom att mata in trafik data från Web Analytics och sedan samla in data för att tillhandahålla instrument paneler i real tid baserat på dessa data.

Nu ska vi skapa en tabell som använder all vår rå data för webb trafik. Kör följande kommandon i psql-terminalen:

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

Vi ska också skapa en tabell som innehåller våra samlingarna per minut och en tabell som behåller positionen för den senaste sammanslagningen. Kör följande kommandon i psql också:

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

Du kan se de nyligen skapade tabellerna i listan med tabeller nu med detta psql-kommando:

\dt

Shard-tabeller över noder

En storskalig distribution lagrar tabell rader på olika noder baserat på värdet för en användardefinierad kolumn. Denna "distributions kolumn" markerar hur data ska shardade mellan noder.

Låt oss ställa in distributions kolumnen som plats _ -ID, nyckeln Shard. I psql kör du följande funktioner:

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

Generera exempeldata

Nu bör vår server grupp vara redo att mata in vissa data. Vi kan köra följande lokalt från vår psql anslutning för att infoga data kontinuerligt.

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

Frågan infogar ungefär åtta rader varje sekund. Raderna lagras på olika arbetsnoder enligt anvisningarna i kolumnen distribution site_id .

Fråga

Med alternativet för skalnings värd kan flera noder bearbeta frågor parallellt för hastighet. Databasen beräknar till exempel agg regeringar som SUM och COUNT på arbetsnoder och kombinerar resultaten till ett slutligt svar.

Här är en fråga om att räkna webb förfrågningar per minut tillsammans med få statistik. Prova att köra det i psql och observera resultatet.

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;

Rensar data

Den föregående frågan fungerar fint i tidiga faser, men dess prestanda försämras i takt med att dina data skalas. Även om distribuerad bearbetning är det snabbare att förberäkna data än att beräkna om dem upprepade gånger.

Vi kan se till att vår instrument panel hålls fast genom att regelbundet rensa rå data i en sammanslagen tabell. Du kan experimentera med agg regerings tiden. Vi använde en agg regerings tabell per minut, men du kan dela upp data i 5, 15 eller 60 minuter i stället.

För att kunna köra den här sammanfattningen är det enklare att publicera den i en plpgsql-funktion. Kör dessa kommandon i psql för att skapa rollup_http_request funktionen.

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

Med vår funktion på plats kan du köra den för att samla in data:

SELECT rollup_http_request();

Och med våra data i ett församlat formulär kan vi skicka en fråga till sammanslagnings tabellen för att hämta samma rapport som tidigare. Kör följande fråga:

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;

Gamla data förfaller

Sammanslagningarna gör frågor snabbare, men vi måste fortfarande förfalla gamla data för att undvika obegränsade lagrings kostnader. Bestäm hur länge du vill behålla data för varje kornig het och Använd standard frågor för att ta bort inaktuella data. I följande exempel beslutade vi att behålla rå data i en dag och agg regeringar per minut i en månad:

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

I produktion kan du figursätta dessa frågor i en funktion och anropa dem varje minut i ett cron-jobb.

Rensa resurser

I föregående steg skapade du Azure-resurser i en Server grupp. Om du inte tror att du behöver dessa resurser i framtiden tar du bort Server gruppen. Tryck på knappen ta bort på sidan Översikt för Server gruppen. När du uppmanas till ett popup-fönster bekräftar du namnet på Server gruppen och klickar på knappen slutlig borttagning .

Nästa steg

I den här självstudien har du lärt dig hur du etablerar en Server grupp för storskaliga (citus). Du är ansluten till den med psql, skapat ett schema och distribuerade data. Du har lärt dig att fråga data i rå data, samla in data, fråga de sammanställda tabellerna och förfalla gamla data.

• Lär dig mer om Node-typer för Server grupper
• Fastställ bästa initial storlek för Server gruppen