Kurz: Návrh řídicího panelu analýzy v reálném čase pomocí Azure Database for PostgreSQL – škálovatelné (Citus)Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus)

V tomto kurzu se naučíte, jak používat Azure Database for PostgreSQL-Citus (škálování na více procesorů):In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) to learn how to:

  • Vytvoření skupiny serverů Hyperscale (Citus)Create a Hyperscale (Citus) server group
  • Vytvoření schématu pomocí nástroje psqlUse psql utility to create a schema
  • Tabulky horizontálních oddílů napříč uzlyShard tables across nodes
  • Generování ukázkových datGenerate sample data
  • Provést souhrnyPerform rollups
  • Dotazování na hrubá a agregovaná dataQuery raw and aggregated data
  • Vypršení platnosti datExpire data

PředpokladyPrerequisites

Pokud ještě nemáte předplatné Azure, vytvořte si bezplatný účet před tím, než začnete.If you don't have an Azure subscription, create a free account before you begin.

Přihlášení k webu Azure PortalSign in to the Azure portal

Přihlaste se na web Azure Portal .Sign in to the Azure portal.

Vytvoření Azure Database for PostgreSQL – Citus (škálování)Create an Azure Database for PostgreSQL - Hyperscale (Citus)

Server Azure Database for PostgreSQL vytvoříte pomocí tohoto postupu:Follow these steps to create an Azure Database for PostgreSQL server:

  1. Klikněte na Vytvořit prostředek v levém horním rohu webu Azure Portal.Click Create a resource in the upper left-hand corner of the Azure portal.

  2. Na stránce Nový vyberte Databáze a na stránce Databáze vyberte Azure Database for PostgreSQL.Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. U možnosti nasazení klikněte na tlačítko vytvořit pod skupinou serverů Citus (škálování na úrovni).For the deployment option, click the Create button under Hyperscale (Citus) server group.

  4. Do formuláře podrobností o novém serveru zadejte následující informace:Fill out the new server details form with the following information:

    • Skupina prostředků: klikněte na odkaz vytvořit nový pod textovým polem pro toto pole.Resource group: click the Create new link below the text box for this field. Zadejte název, například myresourcegroup.Enter a name such as myresourcegroup.
    • Název skupiny serverů: Zadejte jedinečný název pro novou skupinu serverů, která se bude používat i pro subdoménu serveru.Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • Uživatelské jméno správce: v tuto chvíli se musí jednat o hodnotu citusa nedá se změnit.Admin username: currently required to be the value citus, and can't be changed.
    • Heslo: musí mít aspoň osm znaků a musí obsahovat znaky ze tří z následujících kategorií – velká písmena anglické abecedy, malá písmena anglické abecedy, číslice (0-9) a jiné než alfanumerické znaky (!, $, #,% a tak dále).Password: must be at least eight characters long and contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on.)
    • Umístění: použijte umístění, které je nejblíže vašim uživatelům, a poskytněte jim tak nejrychlejší přístup k datům.Location: use the location that is closest to your users to give them the fastest access to the data.

    Důležité

    Heslo správce serveru, které tady zadáte, se vyžaduje pro přihlášení k serveru a jeho databázím.The server admin password that you specify here is required to log in to the server and its databases. Tyto informace si zapamatujte nebo poznamenejte pro pozdější použití.Remember or record this information for later use.

  5. Klikněte na Konfigurovat skupinu serverů.Click Configure server group. Nastavení v této části ponechte beze změny a klikněte na Uložit.Leave the settings in that section unchanged and click Save.

  6. Klikněte na Další: sítě > v dolní části obrazovky.Click Next : Networking > at the bottom of the screen.

  7. Na kartě síť klikněte na přepínač veřejný koncový bod .In the Networking tab, click the Public endpoint radio button. vybraný veřejný koncový bodPublic endpoint selected

  8. Klikněte na odkaz + Přidat aktuální IP adresu klienta.Click the link + Add current client IP address. přidaných klientských IPAdded client IP

    Poznámka

    Server Azure PostgreSQL komunikuje přes port 5432.Azure PostgreSQL server communicates over port 5432. Pokud se pokoušíte připojit z podnikové sítě, nemusí být odchozí provoz přes port 5432 bránou firewall vaší sítě povolený.If you are trying to connect from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. Pokud je to tak, nebudete se moct připojit k serveru služby Azure SQL Database, dokud vaše IT oddělení neotevře port 5432.If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 5432.

  9. Kliknutím na tlačítko zkontrolovat + vytvořit a vytvořit Server zřiďte.Click Review + create and then Create to provision the server. Zřizování trvá několik minut.Provisioning takes a few minutes.

  10. Stránka se přesměruje na monitorování nasazení.The page will redirect to monitor deployment. Až se stav živé změny z vašeho nasazení probíhají po nasazení, klikněte na položku nabídky výstupy na levé straně stránky.When the live status changes from Your deployment is underway to Your deployment is complete, click the Outputs menu item on the left of the page.

  11. Stránka s výstupy bude obsahovat název hostitele koordinátora s tlačítkem vedle něj pro zkopírování hodnoty do schránky.The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. Tyto informace si poznamenejte pro pozdější použití.Record this information for later use.

Připojení k databázi pomocí psqlConnect to the database using psql

Při vytváření serveru Azure Database for PostgreSQL se vytvoří výchozí databáze s názvem citus .When you create your Azure Database for PostgreSQL server, a default database named citus is created. Abyste se mohli připojit k databázovému serveru, potřebujete připojovací řetězec a heslo správce.To connect to your database server, you need a connection string and the admin password.

  1. Získejte připojovací řetězec.Obtain the connection string. Na stránce skupiny serverů klikněte na položku nabídky připojovací řetězce .In the server group page click the Connection strings menu item. (Jedná se o Nastavení.) Vyhledejte řetězec označený C++ (libpq) .(It's under Settings.) Find the string marked C++ (libpq). Bude mít formu:It will be of the form:

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

    Zkopírujte řetězec.Copy the string. Heslo pro správu, které jste si zvolili dříve, budete muset nahradit {_heslo}.You will need to replace "{your_password}" with the administrative password you chose earlier. Systém neuloží vaše heslo ve formátu prostého textu, a proto ho nemůže zobrazit v připojovacím řetězci.The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. Otevřete okno terminálu na místním počítači.Open a terminal window on your local computer.

  3. Na příkazovém řádku se připojte k serveru Azure Database for PostgreSQL pomocí nástroje psql .At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. Předá připojovací řetězec v uvozovkách, ujistěte se, že obsahuje vaše heslo:Pass your connection string in quotes, being sure it contains your password:

    psql "{connection_string}"
    

    Například následující příkaz se připojí k uzlu koordinátora skupiny serveru mydemoserver:For example, the following command connects to the coordinator node of the server group mydemoserver:

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

Vytvoření schématu pomocí nástroje psqlUse psql utility to create a schema

Po připojení k Azure Database for PostgreSQL – Citus () pomocí psql můžete dokončit některé základní úlohy.Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) using psql, you can complete some basic tasks. Tento kurz vás provede příjmem dat přenosů z webové analýzy a následným shrnutím dat a poskytnutím řídicích panelů v reálném čase na základě těchto dat.This tutorial walks you through ingesting traffic data from web analytics, then rolling up the data to provide real-time dashboards based on that data.

Pojďme vytvořit tabulku, která bude využívat všechna nezpracované data webového provozu.Let's create a table that will consume all of our raw web traffic data. V terminálu psql spusťte následující příkazy:Run the following commands in the 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
);

Také se vytvoří tabulka, která bude obsahovat naši agregaci za minutu, a tabulku, která bude uchovávat pozici poslední souhrn.We're also going to create a table that will hold our per-minute aggregates, and a table that maintains the position of our last rollup. V psql také spusťte následující příkazy:Run the following commands in psql as well:

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

Nově vytvořené tabulky můžete zobrazit v seznamu tabulek nyní pomocí tohoto příkazu psql:You can see the newly created tables in the list of tables now with this psql command:

\dt

Tabulky horizontálních oddílů napříč uzlyShard tables across nodes

Nasazení v rámci škálování ukládá řádky tabulky na různých uzlech na základě hodnoty uživatelem označeného sloupce.A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. Tento "distribuční sloupec" označuje způsob, jakým se data horizontálně dělené napříč uzly.This "distribution column" marks how data is sharded across nodes.

Nastavíme distribuční sloupec na ID_ID webu, klíč horizontálních oddílů.Let's set the distribution column to be site_id, the shard key. V psql spusťte tyto funkce:In psql, run these functions:

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

Generování ukázkových datGenerate sample data

Naše skupina serverů by teď měla být připravená na ingestování některých dat.Now our server group should be ready to ingest some data. Z našeho psqlho připojení můžeme pro průběžné vkládání dat spustit následující místně.We can run the following locally from our psql connection to continuously insert 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 $$;

Dotaz vkládá přibližně osm řádků každou sekundu.The query inserts approximately eight rows every second. Řádky jsou uloženy na různých pracovních uzlech, které jsou směrovány pomocí distribučního sloupce site_id.The rows are stored on different worker nodes as directed by the distribution column, site_id.

Poznámka

Ponechte dotaz na generování dat spuštěný a otevřete druhé připojení psql pro zbývající příkazy v tomto kurzu.Leave the data generation query running, and open a second psql connection for the remaining commands in this tutorial.

DotazQuery

Možnost hostování v rámci škálování umožňuje více uzlům zpracovávat paralelně dotazy pro rychlost.The hyperscale hosting option allows multiple nodes to process queries in parallel for speed. Databáze například vypočítá agregované hodnoty, jako je součet a počet na pracovních uzlech, a zkombinuje výsledky do konečné odpovědi.For instance, the database calculates aggregates like SUM and COUNT on worker nodes, and combines the results into a final answer.

Tady je dotaz, jak počítat webové žádosti za minutu spolu s několika statistikami.Here's a query to count web requests per minute along with a few statistics. Zkuste ho spustit v psql a sledujte výsledky.Try running it in psql and observe the results.

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;

Shrnutí datRolling up data

Předchozí dotaz funguje v počátečních fázích dobře, ale jeho výkon se sníží, protože se škálují data.The previous query works fine in the early stages, but its performance degrades as your data scales. I u distribuovaného zpracování je rychlejší vypočítávat data, než je znovu přepočítat.Even with distributed processing, it's faster to pre-compute the data than to recalculate it repeatedly.

V rámci pravidelného Shrnutí nezpracovaných dat do agregované tabulky můžeme zajistit, aby byl náš řídicí panel stále rychlý.We can ensure our dashboard stays fast by regularly rolling up the raw data into an aggregate table. Můžete experimentovat s trváním agregace.You can experiment with the aggregation duration. Použili jsme tabulku agregace po minutách, ale místo ní můžete data přerušit na 5, 15 nebo 60 minut.We used a per-minute aggregation table, but you could break data into 5, 15, or 60 minutes instead.

Aby bylo možné tento souhrn snadněji spustit, přidáme ho do plpgsql funkce.To run this roll-up more easily, we're going to put it into a plpgsql function. Spusťte tyto příkazy v psql a vytvořte funkci rollup_http_request.Run these commands in psql to create the rollup_http_request function.

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

Když je naše funkce na místě, spusťte ji a zaveďte data:With our function in place, execute it to roll up the data:

SELECT rollup_http_request();

A s našimi daty v předem agregované podobě se můžeme dotazovat na souhrnnou tabulku a získat tak stejnou sestavu jako dříve.And with our data in a pre-aggregated form we can query the rollup table to get the same report as earlier. Spusťte následující dotaz:Run the following query:

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;

Vypršení starých datExpiring old data

Souhrny urychlují dotazy, ale pořád potřebujeme vypršet stará data, aby nedocházelo k nevázaným nákladům na úložiště.The rollups make queries faster, but we still need to expire old data to avoid unbounded storage costs. Určete, jak dlouho chcete uchovat data pro každou členitost, a pomocí standardních dotazů odstraňte data s vypršenou platností.Decide how long you’d like to keep data for each granularity, and use standard queries to delete expired data. V následujícím příkladu jsme se rozhodli uchovávat nezpracovaná data za jeden den a agregace po minutách po dobu jednoho měsíce:In the following example, we decided to keep raw data for one day, and per-minute aggregations for one month:

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

V produkčním prostředí byste tyto dotazy mohli zabalit do funkce a volat je každou minutu v rámci úlohy cron.In production, you could wrap these queries in a function and call it every minute in a cron job.

Vyčištění prostředkůClean up resources

V předchozích krocích jste vytvořili prostředky Azure ve skupině serverů.In the preceding steps, you created Azure resources in a server group. Pokud neočekáváte, že tyto prostředky budete potřebovat v budoucnu, odstraňte skupinu serverů.If you don't expect to need these resources in the future, delete the server group. Stiskněte tlačítko Odstranit na stránce Přehled pro skupinu serverů.Press the Delete button in the Overview page for your server group. Po zobrazení výzvy na místní stránce potvrďte název skupiny serverů a klikněte na tlačítko poslední Odstranit .When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

Další krokyNext steps

V tomto kurzu jste zjistili, jak zřídit skupinu serverů (Citus).In this tutorial, you learned how to provision a Hyperscale (Citus) server group. K němu jste se připojili pomocí psql, vytvořili schéma a distribuovaná data.You connected to it with psql, created a schema, and distributed data. Seznámili jste se s dotazem na data v nezpracovaném formuláři, pravidelně agreguje tato data, dotazování na agregované tabulky a vypršení platnosti starých dat.You learned to query data both in the raw form, regularly aggregate that data, query the aggregated tables, and expire old data.

V dalším kroku se dozvíte o konceptech škálování.Next, learn about the concepts of hyperscale.