使用 Azure Cosmos DB for PostgreSQL 設計多租用戶資料庫

發行項
03/12/2024

適用於： Azure Cosmos DB for PostgreSQL (由 PostgreSQL 的 Citus 資料庫延伸模組提供)

在此教學課程中，您會使用 Azure Cosmos DB for PostgreSQL 來瞭解如何：

建立叢集
使用 psql 公用程式建立結構描述
進行跨節點的資料表分區化
內嵌範例資料
查詢租用戶資料
在租用戶之間共用資料
自訂每一租用戶的結構描述

Prerequisites

如果您沒有 Azure 訂用帳戶，請在開始前建立免費帳戶。

建立叢集

登入 Azure 入口網站，並遵循下列步驟來建立 Azure Cosmos DB for PostgreSQL 叢集：

直接連結
入口網站搜尋

移至 Azure 入口網站中的建立 Azure Cosmos DB for PostgreSQL 叢集。

在 [建立 Azure Cosmos DB for PostgreSQL 叢集] 表單上：

填寫 [基本資料] 索引標籤上的資訊。

大多數選項都一目了然，但請記住：
- 叢集名稱會決定應用程式用來連線的 DNS 名稱，其格式為 <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com。
- 您可以選擇主要 PostgreSQL 版本，例如 15。 Azure Cosmos DB for PostgreSQL 一律支援所選取主要 Postgres 版本的最新 Citus 版本。
- 管理使用者名稱的值必須是 citus。
- 您可以將資料庫名稱保留為其預設值 'citus'，或定義您唯一的資料庫名稱。您無法在叢集佈建之後重新命名資料庫。
選取畫面底部的 [下一步: 網路]。
在 [網路] 畫面上，選取 [允許從 Azure 服務及 Azure 內部資源對此叢集進行公用存取]。
選取 [檢閱 + 建立]，並在驗證通過時選取 [建立] 以建立叢集。
佈建需要幾分鐘的時間。此頁面會重新導向以監視部署。當狀態從 [正在部署] 變更為 [您的部署已完成] 時，選取 [移至資源]。

使用 psql 公用程式建立結構描述

使用 psql 連線到 Azure Cosmos DB for PostgreSQL 後，您可以完成一些基本工作。本教學課程會引導您建立可讓廣告商追蹤其行銷活動的 Web 應用程式。

此應用程式可供多家公司使用，因此我們將建立一個資料表來保存公司，並且為其行銷活動建立另一個資料表。在 psql 主控台中，執行下列命令：

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

每個行銷活動都會付費以執行廣告。完成上述程式碼後也請在 psql 中執行下列程式碼，以新增廣告的資料表：

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

最後，我們會追蹤每個廣告的點擊率和廣告曝光數的相關統計資料：

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

現在，您可以在 psql 中執行下列命令，以查看在資料表清單中新建的資料表：

\dt

多租用戶應用程式只能就個別的租用戶強制執行唯一性，這正是所有主要和外部索引鍵都包含公司識別碼的原因。

進行跨節點的資料表分區化

Azure Cosmos DB for PostgreSQL 部署會根據使用者指定的資料行值，在不同的節點上儲存資料表資料列。這個「散發資料行」標記會標示哪個租用戶擁有哪些資料列。

我們要將散發資料行設定為 company_id，即租用戶識別碼。在 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');

重要

您必須散發資料表或使用結構描述型分區化，才能利用 Azure Cosmos DB for PostgreSQL 效能功能。如果您未散發資料表或結構描述，則背景工作角色節點就無法協助執行涉及其資料的查詢。

內嵌範例資料

現在，在 psql 外部的一般命令列中，下載範例資料集：

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

回到 psql 內，進行資料的大量載入。請務必在您下載資料檔案的相同目錄中執行 psql。

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

這項資料現在會分散至背景工作節點。

查詢租用戶資料

當應用程式要求單一租用戶的資料時，資料庫可在單一背景工作節點上執行查詢。單一租用戶查詢會依單一租用戶識別碼進行篩選。例如，下列查詢會篩選 company_id = 5 的廣告和曝光數。請嘗試在 psql 中執行該查詢，並查看結果。

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;

到目前為止，所有資料表皆由 company_id 散發。但某些資料並非原本就特別「屬於」任何租用戶，且可共用。例如，範例廣告平台中的所有公司可能都會想要根據 IP 位址取得其對象的地理資訊。

請建立資料表來保存共用的地理資訊。在 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);

接著，使 geo_ips 成為「參考資料表」，用以儲存每個背景工作節點上的資料表複本。

SELECT create_reference_table('geo_ips');

使用範例資料加以載入。請務必從您下載資料集的目錄內執行 psql 的此命令。

\copy geo_ips from 'geo_ips.csv' with csv

將點擊次數資料表與 geo_ips 相聯結，在所有節點上都是有效的。以下聯結可尋找已點擊 ad 290 的每個人員位置。請嘗試在 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;

自訂每一租用戶的結構描述

每個租用戶都可能需要儲存其他人不需要的特殊資訊。不過，所有租用戶會共用具有相同資料庫結構描述的通用基礎結構。其他資料的去處為何？

方法之一是使用開放式資料行類型，例如 PostgreSQL 的 JSONB。我們的結構描述在 clicks 中有名為 user_data 的 JSONB 欄位。一家公司 (例如公司五) 可使用資料行來追蹤使用者是否位於行動裝置上。

以下查詢可找出哪些人點擊了更多項目：行動裝置或傳統訪客。

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;

我們也可以建立部分索引，為單一公司最佳化此查詢。

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

在更廣泛的層面，我們可以建立資料行中每個索引鍵和值的 GIN 索引。

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;

清除資源

在前述步驟中，您在叢集中建立了 Azure 資源。如果您認為未來不需要這些資源，請刪除叢集。在叢集的 [概觀] 頁面中，選取 [刪除] 按鈕。當快顯頁面上出現提示時，請確認叢集的名稱，並選取最後的 [刪除] 按鈕。

下一步

在本教學課程中，您已了解如何佈建叢集。您已使用 psql 連線到該群組、建立結構描述，並散發資料。您已了解如何在租用戶內部和之間查詢資料，以及如何自訂每個租用戶的結構描述。

了解叢集節點類型
判斷叢集的最佳初始大小