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

適用於： 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 叢集。

入口網站搜尋

1. 在 Azure 入口網站中，搜尋 cosmosdb 並從結果中選取 [Azure Cosmos DB]。

   

2. 在 [Azure Cosmos DB] 畫面上，選取 [建立]。

   

3. 在 [選取 API 選項] 畫面上，選取 [PostgreSQL] 圖格上的 [建立]。

   

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

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

   

   大多數選項都一目了然，但請記住：

   • 叢集名稱會決定應用程式用來連線的 DNS 名稱，其格式為 <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com。
   • 您可以選擇主要 PostgreSQL 版本，例如 15。 Azure Cosmos DB for PostgreSQL 一律支援所選取主要 Postgres 版本的最新 Citus 版本。
   • 管理使用者名稱的值必須是 citus。
   • 您可以將資料庫名稱保留為其預設值 'citus'，或定義您唯一的資料庫名稱。 您無法在叢集佈建之後重新命名資料庫。

2. 選取畫面底部的 [下一步: 網路]。

3. 在 [網路] 畫面上，選取 [允許從 Azure 服務及 Azure 內部資源對此叢集進行公用存取]。

   

4. 選取 [檢閱 + 建立]，並在驗證通過時選取 [建立] 以建立叢集。

5. 佈建需要幾分鐘的時間。 此頁面會重新導向以監視部署。 當狀態從 [正在部署] 變更為 [您的部署已完成] 時，選取 [移至資源]。

使用 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 連線到該群組、建立結構描述，並散發資料。 您已了解如何查詢未經處理格式的資料、定期彙總該資料、查詢彙總的資料表，以及使舊資料過期。

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