Självstudie: Utforma en databas för flera innehavare med hjälp av Azure Database for PostgreSQL – Hyperskala (Citus)

I den här självstudien använder du Azure Database for PostgreSQL – Hyperskala (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ända psql-verktyget för att skapa ett schema

När du har anslutit Azure Database for PostgreSQL – Hyperskala (Citus) psql kan du utföra några grundläggande uppgifter. I den här självstudien går vi igenom hur du skapar en webbapp som gör det möjligt för användare att spåra sina kampanjer.

Flera företag kan använda appen, så vi skapar en tabell för att hålla företag och en annan för sina kampanjer. Kör följande kommandon i psql-konsolen:

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

Varje kampanj betalar för att köra annonser. Lägg även till en tabell för annonser genom att köra följande kod i psql efter koden ovan:

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

Slutligen spårar vi statistik om klick och visningar för varje annons:

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

Du kan se de nyligen skapade tabellerna i listan över tabeller nu i psql genom att köra:

\dt

Program för flera klientorganisationsprogram kan endast framtvinga unikhet per klientorganisation, vilket är anledningen till att alla primära och externa nycklar innehåller företags-ID:t.

Shardtabeller mellan noder

En distribution i hyperskala lagrar tabellrader på olika noder baserat på värdet för en kolumn som användaren har angett. Den här "distributionskolumnen" markerar vilken klientorganisation som äger vilka rader.

Nu ska vi ange distributionskolumnen till _ företags-ID, klientorganisationens ID. Kör följande funktioner i psql:

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

Mata in exempeldata

Utanför psql laddar du nu ned exempeldatauppsättningar på den normala kommandoraden:

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

Gå tillbaka till psql och massinläsning av data. Se till att köra psql i samma katalog där du laddade ned datafilerna.

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

Dessa data kommer nu att spridas över arbetsnoder.

Fråga klientorganisationsdata

När programmet begär data för en enskild klientorganisation kan databasen köra frågan på en enda arbetsnod. Frågor för en enskild klient filtreras efter ett enda klientorganisations-ID. Till exempel filtrerar följande fråga company_id = 5 efter annonser och visningar. Prova att köra den i psql för att se resultatet.

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;

Dela data mellan klienter

Fram till nu har alla tabeller distribuerats av , men vissa data "tillhör" inte någon klientorganisation i company_id synnerhet och kan delas. Alla företag i exempelplattformen kanske till exempel vill ha geografisk information för sin målgrupp baserat på IP-adresser.

Skapa en tabell för delad geografisk information. Kör följande kommandon i psql:

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

Skapa sedan geo_ips en "referenstabell" för att lagra en kopia av tabellen på varje arbetsnod.

SELECT create_reference_table('geo_ips');

Läs in den med exempeldata. Kom ihåg att köra det här kommandot i psql från katalogen där du laddade ned datauppsättningen.

\copy geo_ips from 'geo_ips.csv' with csv

Att koppla klicktabellen med _ geo-IP-adresser är effektivt på alla noder. Här är en koppling för att hitta platserna för alla som klickade på ad 290. Prova att köra frågan i 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;

Anpassa schemat per klientorganisation

Varje klient kan behöva lagra särskild information som inte behövs av andra. Alla klienter delar dock en gemensam infrastruktur med ett identiskt databasschema. Var kan extradata gå?

Ett trick är att använda en öppen kolumntyp som PostgreSQL:s JSONB. Vårt schema har ett JSONB-fält i clicks som heter user_data . Ett företag (till exempel företag fem) kan använda kolumnen för att spåra om användaren finns på en mobil enhet.

Här är en fråga för att hitta vem som klickar mer: mobil eller traditionella besökare.

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;

Vi kan optimera den här frågan för ett enda företag genom att skapa ett partiellt index.

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

Mer allmänt kan vi skapa ett FÖRDR-index för varje nyckel och värde i kolumnen.

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;

Rensa resurser

I föregående steg skapade du Azure-resurser i en servergrupp. Om du inte tror att du behöver dessa resurser i framtiden tar du bort servergruppen. Tryck på knappen Ta bort på sidan Översikt för servergruppen. När du uppmanas till det på en popup-sida bekräftar du namnet på servergruppen 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 Hyperskala (Citus) en servergrupp. Du anslöt till den med psql, skapade ett schema och distribuerade data. Du har lärt dig att fråga efter data både inom och mellan klienter och att anpassa schemat per klientorganisation.

• Läs mer om nodtyper för servergrupper
• Fastställ den bästa inledande storleken för servergruppen