Kurz: návrh databáze s více klienty pomocí Azure Database for PostgreSQL – Citus (škálování)

V tomto kurzu se naučíte, jak používat Azure Database for PostgreSQL-Citus (škálování na více procesorů):

Požadavky

Vytvoření skupiny serverů Hyperscale (Citus)

Pokud ještě nemáte předplatné Azure, vytvořte si bezplatný účet před tím, než začnete.

Přihlášení k webu Azure Portal

Přihlaste se k webu Azure Portal.

Server Azure Database for PostgreSQL vytvoříte pomocí tohoto postupu:

1. Klikněte na Vytvořit prostředek v levém horním rohu Azure Portal.

2. Na stránce Nový vyberte Databáze a na stránce Databáze vyberte Azure Database for PostgreSQL.

3. U možnosti nasazení klikněte na tlačítko Vytvořit v části Hyperscale (Citus) serveru.

4. Do formuláře podrobností o novém serveru zadejte následující informace:

   • Skupina prostředků: Klikněte na odkaz Vytvořit nový pod textovým polem pro toto pole. Zadejte název, například myresourcegroup.
   • Název skupiny serverů: Zadejte jedinečný název nové skupiny serverů, který se použije také pro subdoménu serveru.
   • Uživatelské jméno správce: V současné době musí být hodnota citus a nelze ji změnit.
   • Heslo: Musí mít alespoň 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 (!, $, #, % atd.).
   • Umístění: Použijte umístění, které je vašim uživatelům nejblíže, a podejte jim nejrychlejší přístup k datům.

   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. Tyto informace si zapamatujte nebo poznamenejte pro pozdější použití.

5. Klikněte na Konfigurovat skupinu serverů. Nastavení v této části ponechte beze změny a klikněte na Uložit.

6. Klikněte na Další: > v dolní části obrazovky.

7. Na kartě Sítě vyberte Povolit veřejný přístup ze služeb a prostředků Azure k této skupině serverů. Pak vyberte + Přidat aktuální IP adresu klienta.

   Poznámka

   Server Azure PostgreSQL komunikuje přes 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ý. Pokud ano, nemůžete se připojit ke svému clusteru Hyperscale (Citus), dokud vaše IT oddělení otevře port 5432.

8. Klikněte na Zkontrolovat a vytvořit a pak na Vytvořit, aby se server zř vytvořil. Zřizování trvá několik minut.

9. Stránka se přesměruje na monitorování nasazení. Když se živý stav změní z Vaše nasazení probíhá na Vaše nasazení je dokončeno, klikněte na položku nabídky Výstupy na levé straně stránky.

10. Stránka výstupů bude obsahovat název hostitele koordinátora s tlačítkem vedle něj pro zkopírování hodnoty do schránky. Tyto informace si zaznamenate pro pozdější použití.

Připojení k databázi pomocí nástroje psql

Při vytváření vašeho Azure Database for PostgreSQL se vytvoří výchozí databáze s názvem citus. Pro připojení k databázovému serveru potřebujete připojovací řetězec a heslo správce.

1. Získejte připojovací řetězec. Na stránce skupiny serverů klikněte na položku nabídky Připojovací řetězce. (Je pod Nastavení.) Vyhledejte řetězec označený jako psql. Bude ve tvaru :

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

   Zkopírujte řetězec . {vaše heslo} budete muset nahradit heslem správce, _ které jste zvolili dříve. Systém neukládá heslo ve formátu prostého textu, takže ho za vás nemůže zobrazit v připojovacím řetězci.

2. Otevřete okno terminálu na místním počítači.

3. Po zobrazení výzvy se připojte k Azure Database for PostgreSQL serveru pomocí nástroje psql. Předejte připojovací řetězec do uvozovek a ujistěte se, že obsahuje vaše heslo:

   psql "host=..."
   

   Například následující příkaz se připojí k koordinátorovi uzlu skupiny serverů 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 psql

Po připojení k Azure Database for PostgreSQL – Citus () pomocí psql můžete dokončit některé základní úlohy. Tento kurz vás provede vytvořením webové aplikace, která umožňuje inzerentům sledovat své kampaně.

Aplikace může používat více společností, takže vytvoříme tabulku, která bude uchovávat společnosti a jinou pro své kampaně. V konzole psql spusťte tyto příkazy:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blacklisted_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

Každá kampaň bude platit pro spuštění reklam. Přidejte tabulku pro reklamu, a to spuštěním následujícího kódu v psql po výše uvedeném kódu:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

Nakonec budeme sledovat statistiku kliknutí a potlačení pro každou reklamu:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

Nově vytvořené tabulky můžete zobrazit v seznamu tabulek nyní v psql spuštěním:

\dt

Aplikace s více tenanty mohou vynucovat jedinečnost pouze pro jednoho tenanta, což je důvod, proč všechny primární a cizí klíče zahrnují ID společnosti.

Tabulky horizontálních oddílů napříč uzly

Nasazení hyperšmaku ukládá řádky tabulky na různých uzlech na základě hodnoty sloupce určeného uživatelem. Tento "distribuční sloupec" označuje, který tenant vlastní které řádky.

Pojďme nastavit distribuční sloupec na _ ID společnosti, identifikátor tenanta. V nástroji psql spusťte tyto funkce:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

Ingestace ukázkových dat

Mimo psql si teď na normálním příkazovém řádku stáhněte ukázkové datové sady:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

Zpět v nástroji psql hromadně načtěte data. Nezapomeňte spustit psql ve stejném adresáři, kam jste stáhli datové soubory.

SET CLIENT_ENCODING TO 'utf8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

Tato data budou teď rozložená mezi pracovní uzly.

Dotazování dat tenanta

Když aplikace požaduje data pro jednoho tenanta, může databáze spustit dotaz na jednom pracovním uzlu. Dotazy s jedním tenantem filtruje podle jednoho ID tenanta. Například následující dotaz filtruje reklamy company_id = 5 a imprese. Zkuste ho spustit v psql a podívejte se na výsledky.

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) desc
       ), count(*) as n_impressions, a.id
  FROM ads as a
  JOIN impressions as i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions desc;

Sdílení dat mezi tenanty

Až dosud byly všechny tabulky distribuovány pomocí , ale některá data přirozeně company_id "nepatří" konkrétně do žádného tenanta a je možné je sdílet. Například všechny společnosti v platformě pro příklady reklam můžou chtít získat zeměpisné informace pro cílovou skupinu na základě IP adres.

Vytvořte tabulku, která bude obsahovat sdílené geografické informace. V nástroji psql spusťte následující příkazy:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

Dále vytvořte geo_ips odkazovou tabulku pro uložení kopie tabulky v každém pracovním uzlu.

SELECT create_reference_table('geo_ips');

Načtěte ho s ukázkovými daty. Nezapomeňte spustit tento příkaz v psql zevnitř v adresáři, do kterého jste stáhli datovou sadu.

\copy geo_ips from 'geo_ips.csv' with csv

Spojování tabulky kliknutí s geografickými _ IP adresami je efektivní na všech uzlech. Tady je spojení, kde najdete umístění všech uživatelů, kteří na reklamu klikli. 290. Zkuste spustit dotaz v psql.

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

Přizpůsobení schématu pro jednotlivé klienty

Každý tenant může potřebovat ukládat speciální informace, které jiné nevyžadují. Všichni klienti ale sdílejí společnou infrastrukturu se stejným schématem databáze. Kde může další data přejít?

Jedním z zdvihů je použití typu otevřeného a koncového sloupce, jako je PostgreSQL JSONB. Naše schéma má volané pole JSONB clicks user_data . Společnost (vyslovit pět firem) může pomocí sloupce sledovat, jestli je uživatel v mobilním zařízení.

Tady je dotaz, který zjistí, kdo klikne na další: mobilní nebo tradiční Návštěvníci.

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

Tento dotaz pro jednu společnost můžeme optimalizovat vytvořením částečného indexu.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

Obecněji můžeme vytvořit gin indexy pro každý klíč a hodnotu ve sloupci.

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have
-- the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

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

V předchozích krocích jste vytvořili prostředky Azure ve skupině serverů. Pokud neočekáváte, že tyto prostředky budete potřebovat v budoucnu, odstraňte skupinu serverů. Stiskněte tlačítko Odstranit na stránce Přehled pro skupinu serverů. Po zobrazení výzvy na místní stránce potvrďte název skupiny serverů a klikněte na tlačítko poslední Odstranit .

Další kroky

V tomto kurzu jste zjistili, jak zřídit skupinu Hyperscale (Citus) serveru. Připojili jste se k ní pomocí psql, vytvořili jste schéma a distribuovaná data. Naučili jste se dotazovat data v rámci tenantů i mezi nimi a přizpůsobit schéma pro tenanta.

• Informace o typech uzlů skupiny serverů
• Určení nejlepší počáteční velikosti skupiny serverů