Självstudie: Utforma en instrumentpanel för realtidsanalys med hjälp av Azure Cosmos DB for PostgreSQL

GÄLLER FÖR: Azure Cosmos DB for PostgreSQL (drivs av Citus-databastillägget till PostgreSQL)

I den här självstudien använder du Azure Cosmos DB for PostgreSQL för att lära dig hur du:

• Skapa ett kluster
• Använda psql-verktyget för att skapa ett schema
• Fragmentera tabeller mellan noder
• Generera exempeldata
• Utföra sammanslagningar
• Fråga rådata och aggregerade data
• Förfalla data

Förutsättningar

Om du inte har någon Azure-prenumeration skapar du ett kostnadsfritt konto innan du börjar.

Skapa ett kluster

Logga in på Azure-portalen och följ dessa steg för att skapa ett Azure Cosmos DB for PostgreSQL-kluster:

Direktlänk

Gå till Skapa ett Azure Cosmos DB for PostgreSQL-kluster i Azure-portalen.

Portalsökning

1. I Azure-portalen söker du efter cosmosdb och väljer Azure Cosmos DB i resultatet.

   

2. På skärmen Azure Cosmos DB väljer du Skapa.

   

3. På skärmen Välj API-alternativ väljer du Skapa på PostgreSQL-panelen.

   

I klusterformuläret Skapa en Azure Cosmos DB for PostgreSQL-kluster :

1. Fyll i informationen på fliken Basics (Grunder).

   

   De flesta alternativ är självförklarande, men tänk på följande:

   • Klusternamnet bestämmer det DNS-namn som dina program använder för att ansluta, i formuläret <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
   • Du kan välja en större PostgreSQL-version, till exempel 15. Azure Cosmos DB for PostgreSQL stöder alltid den senaste Citus-versionen för den valda större Postgres-versionen.
   • Administratörsanvändarnamnet måste ha värdet citus.
   • Du kan lämna databasnamnet som standardvärde "citus" eller definiera ditt enda databasnamn. Du kan inte byta namn på databasen efter klusteretablering.

2. Välj Nästa: Nätverk längst ned på skärmen.

3. På skärmen Nätverk väljer du Tillåt offentlig åtkomst från Azure-tjänster och resurser i Azure till det här klustret.

   

4. Välj Granska + skapa, och när valideringen är godkänd väljer du Skapa för att skapa klustret.

5. Etableringen tar några minuter. Sidan omdirigeras till övervakning av distribution. När statusen ändras från Distribution pågår till Distributionen är klar väljer du Gå till resurs.

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

När du har anslutit till Azure Cosmos DB for PostgreSQL med psql kan du utföra några grundläggande uppgifter. Den här självstudien vägleder dig genom att mata in trafikdata från webbanalys och sedan rulla upp data för att tillhandahålla instrumentpaneler i realtid baserat på dessa data.

Nu ska vi skapa en tabell som förbrukar alla våra råa webbtrafikdata. 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 aggregeringar per minut och en tabell som behåller positionen för vår senaste sammanslagning. Kör även följande kommandon i psql:

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 över tabeller nu med det här psql-kommandot:

\dt

Fragmentera tabeller mellan noder

En Azure Cosmos DB for PostgreSQL-distribution lagrar tabellrader på olika noder baserat på värdet för en användarutnämnd kolumn. Den här "distributionskolumnen" markerar hur data fragmenteras mellan noder.

Nu ska vi ange att distributionskolumnen ska vara site_id, shardnyckeln. Kör följande funktioner i psql:

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

Viktigt!

Det är nödvändigt att distribuera tabeller eller använda schemabaserad horisontell partitionering för att dra nytta av prestandafunktionerna i Azure Cosmos DB for PostgreSQL. Om du inte distribuerar tabeller eller scheman kan arbetsnoder inte hjälpa till att köra frågor som rör deras data.

Generera exempeldata

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

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 cirka åtta rader varje sekund. Raderna lagras på olika arbetsnoder enligt distributionskolumnen . site_id

Kommentar

Låt datagenereringsfrågan köras och öppna en andra psql-anslutning för de återstående kommandona i den här självstudien.

Fråga

Med Azure Cosmos DB for PostgreSQL kan flera noder bearbeta frågor parallellt för hastighet. Databasen beräknar till exempel aggregeringar som SUM och COUNT på arbetsnoder och kombinerar resultatet till ett slutligt svar.

Här är en fråga för att räkna webbbegäranden per minut tillsammans med några få statistik. Prova att köra den 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;

Löpande data

Den tidigare frågan fungerar bra i ett tidigt skede, men dess prestanda försämras när dina data skalar. Även med distribuerad bearbetning går det snabbare att förberäkna data än att beräkna om dem upprepade gånger.

Vi kan se till att instrumentpanelen förblir snabb genom att regelbundet rulla upp rådata till en aggregeringstabell. Du kan experimentera med aggregeringstiden. Vi använde en sammansättningstabell per minut, men du kan dela upp data i 5, 15 eller 60 minuter i stället.

För att köra den här sammanslagning enklare ska vi placera 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 kör du den för att samla in data:

SELECT rollup_http_request();

Och med våra data i ett föraggregerat formulär kan vi fråga sammanslagningstabellen för att få 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 upphör att gälla

Sammanslagningarna gör frågor snabbare, men vi måste fortfarande förfalla gamla data för att undvika obundna lagringskostnader. Bestäm hur länge du vill behålla data för varje kornighet och använd standardfrågor för att ta bort utgångna data. I följande exempel bestämde vi oss för att behålla rådata för en dag och aggregeringar 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 omsluta dessa frågor i en funktion och anropa den varje minut i ett cron-jobb.

Rensa resurser

I föregående steg skapade du Azure-resurser i ett kluster. Om du inte förväntar dig att behöva dessa resurser i framtiden tar du bort klustret. Tryck på knappen Ta bort på översiktssidan för klustret. När du uppmanas till det på en popup-sida bekräftar du namnet på klustret och klickar på den sista knappen Ta bort .

Nästa steg

I den här självstudien har du lärt dig hur du etablerar ett kluster. Du anslöt till den med psql, skapade ett schema och distribuerade data. Du har lärt dig att köra frågor mot data i rådataform, regelbundet aggregera dessa data, köra frågor mot de aggregerade tabellerna och förfalla gamla data.

• Läs mer om klusternodtyper
• Fastställa den bästa initiala storleken för klustret