共用方式為


教學課程:使用 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 叢集

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