教學課程：使用 Azure Cosmos DB for PostgreSQL 來設計即時分析儀表板

發行項
08/15/2024

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

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

建立叢集
使用 psql 公用程式建立結構描述
進行跨節點的資料表分區化
產生範例資料
執行彙總
查詢原始和彙總資料
使資料過期

必要條件

如果您沒有 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 分析中的流量資料，然後彙總資料，以便根據該資料提供即時儀表板。

我們會建立一個資料表，該資料表將取用所有未經處理的 Web 流量資料。在 psql 終端機中執行下列命令：

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);

我們也會建立一個可保存每分鐘彙總的資料表，以及一個可維護最後一個彙總位置的資料表。也在 psql 中執行下列命令：

CREATE TABLE http_request_1min (
  site_id INT,
  ingest_time TIMESTAMPTZ, -- which minute this row represents

  error_count INT,
  success_count INT,
  request_count INT,
  average_response_time_msec INT,
  CHECK (request_count = error_count + success_count),
  CHECK (ingest_time = date_trunc('minute', ingest_time))
);

CREATE INDEX http_request_1min_idx ON http_request_1min (site_id, ingest_time);

CREATE TABLE latest_rollup (
  minute timestamptz PRIMARY KEY,

  CHECK (minute = date_trunc('minute', minute))
);

您現在可以使用此 psql 命令來查看資料表清單中新建立的資料表：

\dt

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

Azure Cosmos DB for PostgreSQL 部署會根據使用者指定的資料行值，在不同的節點上儲存資料表資料列。此「散發資料行」表示跨節點的資料分區化方式。

讓我們將散發資料行設定為 site_id，即分區索引鍵。在 psql 中，執行下列函式：

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

重要

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

產生範例資料

我們的叢集現在應已準備好擷取一些資料。我們可以在本機從我們的 psql 連線執行下列命令，以持續插入資料。

DO $$
  BEGIN LOOP
    INSERT INTO http_request (
      site_id, ingest_time, url, request_country,
      ip_address, status_code, response_time_msec
    ) VALUES (
      trunc(random()*32), clock_timestamp(),
      concat('http://example.com/', md5(random()::text)),
      ('{China,India,USA,Indonesia}'::text[])[ceil(random()*4)],
      concat(
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2)
      )::inet,
      ('{200,404}'::int[])[ceil(random()*2)],
      5+trunc(random()*150)
    );
    COMMIT;
    PERFORM pg_sleep(random() * 0.25);
  END LOOP;
END $$;

查詢會每一秒插入大約八個資料列。如散發資料行 site_id 所引導，資料列會儲存在不同的背景工作角色節點上。

注意

讓資料產生查詢保持執行，並針對本教學課程中的剩餘命令，開啟第二個 psql 連線。

查詢

Azure Cosmos DB for PostgreSQL 允許多個節點平行處理查詢以加快速度。例如，資料庫會在背景工作角色節點上計算 SUM 和 COUNT 等彙總，並將結果結合成最終的答案。

以下的查詢可計算每分鐘的 Web 要求以及一些統計資料。嘗試在 psql 中執行該查詢並觀察結果。

SELECT
  site_id,
  date_trunc('minute', ingest_time) as minute,
  COUNT(1) AS request_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
  SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
FROM http_request
WHERE date_trunc('minute', ingest_time) > now() - '5 minutes'::interval
GROUP BY site_id, minute
ORDER BY minute ASC;

彙總資料

上述查詢在早期階段可正常運作，但其效能會隨著您的資料擴充而下降。即使使用分散式處理，預先計算資料的速度會比重複重新計算還要快。

我們可以定期將未經處理的資料彙總到彙總資料表中，以確保儀表板保持快速。您可以試驗彙總持續時間。我們已使用每分鐘彙總資料表，但您可以改為將資料分成 5、15 或 60 分鐘。

為了更輕鬆地執行此彙總，我們將其放入 plpgsql 函式。在 psql 中執行下列命令以建立 rollup_http_request 函式。

-- initialize to a time long ago
INSERT INTO latest_rollup VALUES ('10-10-1901');

-- function to do the rollup
CREATE OR REPLACE FUNCTION rollup_http_request() RETURNS void AS $$
DECLARE
  curr_rollup_time timestamptz := date_trunc('minute', now());
  last_rollup_time timestamptz := minute from latest_rollup;
BEGIN
  INSERT INTO http_request_1min (
    site_id, ingest_time, request_count,
    success_count, error_count, average_response_time_msec
  ) SELECT
    site_id,
    date_trunc('minute', ingest_time),
    COUNT(1) as request_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
  FROM http_request
  -- roll up only data new since last_rollup_time
  WHERE date_trunc('minute', ingest_time) <@
          tstzrange(last_rollup_time, curr_rollup_time, '(]')
  GROUP BY 1, 2;

  -- update the value in latest_rollup so that next time we run the
  -- rollup it will operate on data newer than curr_rollup_time
  UPDATE latest_rollup SET minute = curr_rollup_time;
END;
$$ LANGUAGE plpgsql;

備妥我們的函式，加以執行來彙總資料：

SELECT rollup_http_request();

然後將資料放在預先彙總的表單中，我們即可查詢彙總資料表，以取得與稍早相同的報告。執行下列查詢：

SELECT site_id, ingest_time as minute, request_count,
       success_count, error_count, average_response_time_msec
  FROM http_request_1min
 WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

使舊資料過期

彙總讓查詢速度變快，但我們仍需要使舊資料過期，以避免不受控制的儲存體成本。決定您要針對每種細微性保留資料多久，並使用標準查詢來刪除過期的資料。在下列範例中，我們已決定將未經處理的資料保留一天，並將每分鐘的彙總保留一個月：

DELETE FROM http_request WHERE ingest_time < now() - interval '1 day';
DELETE FROM http_request_1min WHERE ingest_time < now() - interval '1 month';

在生產環境中，您可將這些查詢包裝在函式中，並且在 cron 作業中每分鐘呼叫該函式。

清除資源

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

下一步

在此教學課程中，您已了解如何佈建叢集。您已使用 psql 連線到該群組、建立結構描述，並散發資料。您已了解如何查詢未經處理格式的資料、定期彙總該資料、查詢彙總的資料表，以及使舊資料過期。

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

共用方式為