Több-bérlős adatbázis tervezése az Azure Cosmos DB for PostgreSQL használatával

A KÖVETKEZŐKRE VONATKOZIK: Azure Cosmos DB for PostgreSQL (a Citus adatbázisbővítménye a PostgreSQL-re)

Ebben az oktatóanyagban az Azure Cosmos DB for PostgreSQL használatával megtudhatja, hogyan:

• Fürt létrehozása
• Séma létrehozása a psql segédprogrammal
• Csomópontok közötti szegmenstáblák
• Mintaadatok betöltése
• Bérlői adatok lekérdezése
• Adatok megosztása bérlők között
• A séma testreszabása bérlőnként

Előfeltételek

Ha még nincs Azure-előfizetése, kezdés előtt hozzon létre egy ingyenes fiókot.

Fürt létrehozása

Jelentkezzen be az Azure Portalra , és kövesse az alábbi lépéseket egy Azure Cosmos DB for PostgreSQL-fürt létrehozásához:

Közvetlen hivatkozás

Az Azure Portalon lépjen az Azure Cosmos DB for PostgreSQL-fürt létrehozása elemre.

Portálkeresés

1. Az Azure Portalon keressen rá a cosmosdb-ra, és válassza ki az Azure Cosmos DB-t az eredmények közül.

   

2. Az Azure Cosmos DB képernyőjén válassza a Létrehozás lehetőséget.

   

3. Az API kiválasztása beállítási képernyőn válassza a Létrehozás lehetőséget a PostgreSQL csempén.

   

Az Azure Cosmos DB for PostgreSQL-fürt létrehozása űrlapon:

1. Adja meg az Alapvető beállítások lapon kért adatokat.

   

   A legtöbb beállítás egyértelmű, de tartsa szem előtt a következőket:

   • A fürt neve határozza meg az alkalmazások által a csatlakozáshoz használt DNS-nevet az űrlapon <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
   • Választhat egy fő PostgreSQL-verziót, például a 15-öt. Az Azure Cosmos DB for PostgreSQL mindig a legújabb Citus-verziót támogatja a kiválasztott fő Postgres-verzióhoz.
   • A rendszergazdai felhasználónév legyen citus.
   • Meghagyhatja az adatbázis nevét az alapértelmezett "citus" értéknél, vagy megadhatja az egyetlen adatbázisnevet. A fürt kiépítése után nem nevezheti át az adatbázist.

2. Válassza a Tovább elemet : Hálózatkezelés a képernyő alján.

3. A Hálózatkezelés képernyőn válassza a Nyilvános hozzáférés engedélyezése az Azure-szolgáltatásokból és az Azure-beli erőforrásokból a fürthöz lehetőséget.

   

4. Válassza a Felülvizsgálat + létrehozás elemet, majd az ellenőrzés elvégzése után kattintson a Létrehozás gombra a fürt létrehozásához.

5. Az üzembe helyezés eltarthat néhány percig. Az oldal átirányítja az üzembe helyezés monitorozásához. Amikor az állapot az Üzembe helyezés folyamatban után az Az üzembe helyezés befejeződött értékre vált, válassza az Ugrás az erőforrásra lehetőséget.

Séma létrehozása a psql segédprogrammal

Miután a psql használatával csatlakozott az Azure Cosmos DB for PostgreSQL-hez, elvégezhet néhány alapvető feladatot. Ez az oktatóanyag bemutatja, hogyan hozhat létre olyan webalkalmazást, amellyel a hirdetők nyomon követhetik kampányaikat.

Több vállalat is használhatja az alkalmazást, ezért hozzunk létre egy táblázatot, amely a vállalatokat és egy másikat a kampányaikhoz tart. A psql-konzolon futtassa az alábbi parancsokat:

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,
  blocked_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

Minden kampány fizet a hirdetések futtatásáért. Adjon hozzá egy táblát a hirdetésekhez is, ha a fenti kód után a következő kódot futtatja a psql-ben:

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

Végül nyomon követjük az egyes hirdetésekkel kapcsolatos kattintásokra és megjelenítésekre vonatkozó statisztikákat:

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

Az újonnan létrehozott táblák a psql-ben lévő táblák listájában a következő futtatásával láthatók:

\dt

A több-bérlős alkalmazások csak bérlőnként kényszeríthetik ki az egyediséget, ezért az összes elsődleges és idegen kulcs tartalmazza a cégazonosítót.

Csomópontok közötti szegmenstáblák

Az Azure Cosmos DB for PostgreSQL üzembe helyezése különböző csomópontokon tárolja a táblasorokat a felhasználó által kijelölt oszlop értéke alapján. Ez a "terjesztési oszlop" jelzi, hogy melyik bérlő melyik sorokat birtokolja.

Állítsuk be a terjesztési oszlopot company_id, a bérlőazonosítót. A psql-ben futtassa az alábbi függvényeket:

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

Fontos

Táblák elosztása vagy sémaalapú horizontális skálázás szükséges az Azure Cosmos DB for PostgreSQL teljesítményfunkcióinak kihasználásához. Ha nem terjeszt táblákat vagy sémákat, akkor a feldolgozó csomópontok nem tudnak az adataikkal kapcsolatos lekérdezéseket futtatni.

Mintaadatok betöltése

A psql-en kívül most a normál parancssorban töltse le a mintaadatkészleteket:

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

Vissza a psql-be, tömegesen töltse be az adatokat. Mindenképpen futtassa a psql-t ugyanabban a könyvtárban, ahol az adatfájlokat letöltötte.

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

Ezek az adatok mostantól el lesznek osztva a feldolgozó csomópontokon.

Bérlői adatok lekérdezése

Amikor az alkalmazás adatokat kér egy bérlőhöz, az adatbázis egyetlen feldolgozó csomóponton hajthatja végre a lekérdezést. Az egybérlős lekérdezések egyetlen bérlőazonosító alapján szűrnek. A következő lekérdezés például a hirdetéseket és a megjelenítéseket szűri company_id = 5 . Próbálja meg futtatni a psql-ben az eredmények megtekintéséhez.

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;

Adatok megosztása bérlők között

Eddig az összes táblát elosztotta a company_id. Bizonyos adatok azonban természetesen nem tartoznak egyik bérlőhöz sem, és megoszthatóak. Előfordulhat például, hogy a példában szereplő hirdetésplatform összes vállalata az IP-címek alapján szeretné lekérni a célközönség földrajzi adatait.

Hozzon létre egy táblát a megosztott földrajzi adatok tárolásához. Futtassa a következő parancsokat a psql-ben:

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

Ezután készítsen geo_ips egy "referenciatáblát" a tábla másolatának tárolásához minden munkavégző csomóponton.

SELECT create_reference_table('geo_ips');

Töltse be példaadatokkal. Ne felejtse el futtatni ezt a parancsot a psql-ben abban a könyvtárban, ahol az adathalmazt letöltötte.

\copy geo_ips from 'geo_ips.csv' with csv

A kattintások táblázatának geo_ips való összekapcsolása minden csomóponton hatékony. Az alábbi illesztésben megtalálhatja a 290-et a hirdetésre kattintó összes felhasználó helyét. Próbálja meg futtatni a lekérdezést a psql-ben.

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;

A séma testreszabása bérlőnként

Előfordulhat, hogy minden bérlőnek olyan különleges információkat kell tárolnia, amelyekre másoknak nincs szükségük. Azonban minden bérlő azonos adatbázissémával rendelkező közös infrastruktúrával rendelkezik. Hová kerülnek a felesleges adatok?

Az egyik trükk egy nyílt végű oszloptípus használata, például a PostgreSQL JSONB-ja. A sémánkban van egy JSONB-mező a hívott user_datahelyenclicks. Egy vállalat (például öt vállalat) az oszlop segítségével nyomon követheti, hogy a felhasználó mobileszközön van-e.

Az alábbiakban egy lekérdezést talál, amelyből megtudhatja, hogy ki kattint többet: mobil vagy hagyományos látogatókra.

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;

Ezt a lekérdezést egyetlen vállalatra optimalizálhatjuk egy részleges index létrehozásával.

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

Általánosabban létrehozhatunk egy GIN-indexet az oszlop minden kulcsán és értékén.

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;

Clean up resources

Az előző lépésekben azure-erőforrásokat hozott létre egy fürtben. Ha a jövőben nem lesz szüksége ezekre az erőforrásokra, törölje a fürtöt. Válassza a Törlés gombot a fürt Áttekintés lapján. Amikor a rendszer egy előugró lapon kéri, erősítse meg a fürt nevét, és válassza a végleges Törlés gombot.

Következő lépések

Ebben az oktatóanyagban megtanulta, hogyan építhet ki fürtöt. Csatlakoztatta a psql-hez, létrehozott egy sémát és elosztott adatokat. Megtanulta a bérlőken belüli és a bérlők közötti adatok lekérdezését, valamint a séma bérlőnkénti testreszabását.

• Tudnivalók a fürtcsomópont-típusokról
• A fürt optimális kezdeti méretének meghatározása