Projektowanie wielodostępnej bazy danych przy użyciu usługi Azure Cosmos DB for PostgreSQL

DOTYCZY: Usługa Azure Cosmos DB for PostgreSQL (obsługiwana przez rozszerzenie bazy danych Citus do bazy danych PostgreSQL)

W tym samouczku użyjesz usługi Azure Cosmos DB for PostgreSQL, aby dowiedzieć się, jak wykonywać następujące czynności:

• Tworzenie klastra
• Tworzenie schematu za pomocą narzędzia psql
• Tabele fragmentów między węzłami
• Pozyskiwanie przykładowych danych
• Wykonywanie zapytań dotyczących danych dzierżawy
• Udostępnianie danych między dzierżawami
• Dostosowywanie schematu dla dzierżawy

Wymagania wstępne

Jeśli nie masz subskrypcji platformy Azure, przed rozpoczęciem utwórz bezpłatne konto.

Tworzenie klastra

Zaloguj się do witryny Azure Portal i wykonaj następujące kroki, aby utworzyć klaster usługi Azure Cosmos DB for PostgreSQL:

Link bezpośredni

Przejdź do pozycji Utwórz klaster usługi Azure Cosmos DB for PostgreSQL w witrynie Azure Portal.

Wyszukiwanie w portalu

1. W witrynie Azure Portal wyszukaj pozycję cosmosdb i wybierz pozycję Azure Cosmos DB z wyników.

   

2. Na ekranie usługi Azure Cosmos DB wybierz pozycję Utwórz.

   

3. Na ekranie Wybierz interfejs API wybierz pozycję Utwórz na kafelku PostgreSQL .

   

W formularzu Tworzenie klastra usługi Azure Cosmos DB for PostgreSQL:

1. Wypełnij informacje na karcie Podstawowe informacje.

   

   Większość opcji nie wymaga wyjaśnień, ale należy pamiętać o następujących kwestiach:

   • Nazwa klastra określa nazwę DNS używaną przez aplikacje do nawiązywania połączenia w postaci <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com.
   • Możesz wybrać główną wersję bazy danych PostgreSQL, taką jak 15. Usługa Azure Cosmos DB for PostgreSQL zawsze obsługuje najnowszą wersję citus dla wybranej głównej wersji bazy danych Postgres.
   • Nazwa użytkownika administratora musi być wartością citus.
   • Nazwę bazy danych można pozostawić na wartości domyślnej "citus" lub zdefiniować tylko nazwę bazy danych. Nie można zmienić nazwy bazy danych po aprowizacji klastra.

2. Wybierz pozycję Dalej: Sieć w dolnej części ekranu.

3. Na ekranie Sieć wybierz pozycję Zezwalaj na dostęp publiczny z usług i zasobów platformy Azure w ramach platformy Azure do tego klastra.

   

4. Wybierz pozycję Przeglądanie i tworzenie, a po zakończeniu walidacji wybierz pozycję Utwórz, aby utworzyć klaster.

5. Aprowizacja zajmuje kilka minut. Strona przekierowuje do monitorowania wdrożenia. Gdy stan zmieni się z Wdrażanie jest w toku na Wdrożenie zostało ukończone, wybierz pozycję Przejdź do zasobu.

Tworzenie schematu za pomocą narzędzia psql

Po nawiązaniu połączenia z usługą Azure Cosmos DB for PostgreSQL przy użyciu narzędzia psql możesz wykonać kilka podstawowych zadań. Ten samouczek przeprowadzi Cię przez proces tworzenia aplikacji internetowej, która umożliwia reklamodawcom śledzenie ich kampanii.

Wiele firm może korzystać z aplikacji, więc utwórzmy tabelę do przechowywania firm i innych dla swoich kampanii. W konsoli programu psql uruchom następujące polecenia:

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

Każda kampania będzie płacić za uruchamianie reklam. Dodaj też tabelę dla reklam, uruchamiając następujący kod w pliku psql po powyższym kodzie:

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

Na koniec prześledzimy statystyki dotyczące kliknięć i wyświetleń dla każdej reklamy:

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

Nowo utworzone tabele można wyświetlić na liście tabel teraz w narzędziu psql, uruchamiając polecenie :

\dt

Aplikacje wielodostępne mogą wymuszać unikatowość tylko dla dzierżawy, dlatego wszystkie klucze podstawowe i obce zawierają identyfikator firmy.

Tabele fragmentów między węzłami

Wdrożenie usługi Azure Cosmos DB for PostgreSQL przechowuje wiersze tabeli na różnych węzłach na podstawie wartości kolumny wyznaczonej przez użytkownika. Ta "kolumna dystrybucji" oznacza dzierżawę będącą właścicielem wierszy.

Ustawmy kolumnę dystrybucji na company_id identyfikator dzierżawy. W narzędziu psql uruchom następujące funkcje:

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

Ważne

Rozpowszechnianie tabel lub używanie fragmentowania opartego na schemacie jest niezbędne do korzystania z funkcji wydajności usługi Azure Cosmos DB for PostgreSQL. Jeśli nie dystrybuujesz tabel ani schematów, węzły procesu roboczego nie mogą pomóc w uruchamianiu zapytań dotyczących ich danych.

Pozyskiwanie przykładowych danych

Poza narzędziem psql teraz w normalnym wierszu polecenia pobierz przykładowe zestawy danych:

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

Z powrotem wewnątrz narzędzia psql załaduj zbiorczo dane. Pamiętaj, aby uruchomić narzędzie psql w tym samym katalogu, w którym pobrano pliki danych.

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

Te dane będą teraz rozłożone na węzły robocze.

Wykonywanie zapytań dotyczących danych dzierżawy

Gdy aplikacja żąda danych dla pojedynczej dzierżawy, baza danych może wykonać zapytanie w jednym węźle roboczym. Zapytania z jedną dzierżawą filtruje według jednego identyfikatora dzierżawy. Na przykład następujące filtry company_id = 5 zapytań dla reklam i wyświetleń. Spróbuj uruchomić go w narzędziu psql, aby wyświetlić wyniki.

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;

Udostępnianie danych między dzierżawami

Do tej pory wszystkie tabele były dystrybuowane przez company_idprogram . Jednak niektóre dane nie należą naturalnie "" do żadnej dzierżawy w szczególności i mogą być udostępniane. Na przykład wszystkie firmy na przykładowej platformie reklamowej mogą chcieć uzyskać informacje geograficzne dla swoich odbiorców na podstawie adresów IP.

Utwórz tabelę do przechowywania udostępnionych informacji geograficznych. Uruchom następujące polecenia w narzędziu 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);

Następnie utwórz geo_ips "tabelę referencyjną", aby przechowywać kopię tabeli w każdym węźle roboczym.

SELECT create_reference_table('geo_ips');

Załaduj je przy użyciu przykładowych danych. Pamiętaj, aby uruchomić to polecenie w narzędziu psql z poziomu katalogu, w którym pobrano zestaw danych.

\copy geo_ips from 'geo_ips.csv' with csv

Łączenie tabeli kliknięć z geo_ips jest wydajne we wszystkich węzłach. Oto sprzężenie, aby znaleźć lokalizacje wszystkich osób, które kliknął reklamy 290. Spróbuj uruchomić zapytanie w narzędziu 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;

Dostosowywanie schematu dla dzierżawy

Każda dzierżawa może wymagać przechowywania specjalnych informacji, które nie są potrzebne innym. Jednak wszyscy dzierżawcy mają wspólną infrastrukturę z identycznym schematem bazy danych. Gdzie można przejść dodatkowe dane?

Jedną z wskazówek jest użycie typu kolumny typu open-ended, takiego jak JSONB bazy danych PostgreSQL. Nasz schemat zawiera pole JSONB o clicks nazwie user_data. Firma (np. pięć firm) może używać kolumny do śledzenia, czy użytkownik jest na urządzeniu przenośnym.

Oto zapytanie, aby dowiedzieć się, kto klika więcej: mobilnych lub tradycyjnych odwiedzających.

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;

Możemy zoptymalizować to zapytanie dla jednej firmy, tworząc indeks częściowy.

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

Ogólnie rzecz biorąc, możemy utworzyć indeksy GIN dla każdego klucza i wartości w kolumnie.

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;

Czyszczenie zasobów

W poprzednich krokach utworzono zasoby platformy Azure w klastrze. Jeśli nie spodziewasz się, że te zasoby będą potrzebne w przyszłości, usuń klaster. Wybierz przycisk Usuń na stronie Przegląd klastra. Po wyświetleniu monitu na wyskakującym okienku potwierdź nazwę klastra i wybierz końcowy przycisk Usuń .

Następne kroki

W tym samouczku przedstawiono sposób aprowizacji klastra. Nawiązano z nim połączenie za pomocą narzędzia psql, utworzono schemat i rozproszone dane. Wiesz już, jak wykonywać zapytania dotyczące danych zarówno w ramach dzierżaw, jak i między nimi, oraz dostosować schemat dla każdej dzierżawy.

• Dowiedz się więcej o typach węzłów klastra
• Określanie najlepszego rozmiaru początkowego klastra