チュートリアル:Azure Database for PostgreSQL - Hyperscale (Citus) を使用したリアルタイム分析ダッシュボードの設計Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus)

このチュートリアルでは、Azure Database for PostgreSQL - Hyperscale (Citus) を使用して、次の作業を実行する方法を説明します。In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) to learn how to:

  • Hyperscale (Citus) サーバー グループを作成するCreate a Hyperscale (Citus) server group
  • psql ユーティリティを使用してスキーマを作成するUse psql utility to create a schema
  • ノード全体でテーブルをシャード化するShard tables across nodes
  • サンプル データを作成するGenerate sample data
  • ロールアップを実行するPerform rollups
  • 生データと集計データのクエリを実行するQuery raw and aggregated data
  • データを期限切れにするExpire data

前提条件Prerequisites

Azure サブスクリプションをお持ちでない場合は、開始する前に無料アカウントを作成してください。If you don't have an Azure subscription, create a free account before you begin.

Azure portal にサインインするSign in to the Azure portal

Azure portal にサインインします。Sign in to the Azure portal.

Azure Database for PostgreSQL - Hyperscale (Citus) の作成Create an Azure Database for PostgreSQL - Hyperscale (Citus)

Azure Database for PostgreSQL サーバーを作成するには、次の手順に従います。Follow these steps to create an Azure Database for PostgreSQL server:

  1. Azure Portal の左上隅にある [リソースの作成] をクリックします。Click Create a resource in the upper left-hand corner of the Azure portal.

  2. [新規] ページで [データベース] を選択し、 [データベース] ページで [Azure Database for PostgreSQL] を選択します。Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. デプロイ オプションについては、 [Hyperscale (Citus) server group] (Hyperscale (Citus) サーバー グループ) の下にある [作成] ボタンをクリックします。For the deployment option, click the Create button under Hyperscale (Citus) server group.

  4. 新しいサーバーの詳細フォームには次の情報を入力してください。Fill out the new server details form with the following information:

    • リソース グループ: このフィールドのテキスト ボックスの下の [新規作成] リンクをクリックします。Resource group: click the Create new link below the text box for this field. myresourcegroup などの名前を入力します。Enter a name such as myresourcegroup.
    • サーバー グループ名: 新しいサーバー グループの一意の名前を入力します。これはサーバー サブドメインにも使用されます。Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • 管理者ユーザー名: 現在、値 citus である必要があり、変更することはできません。Admin username: currently required to be the value citus, and can't be changed.
    • パスワード: 少なくとも 8 文字で、次のカテゴリのうち 3 つのカテゴリの文字が含まれている必要があります。英字大文字、英字小文字、数字 (0 から 9)、英数字以外の文字 (!、$、#、% など)。Password: must be at least eight characters long and contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on.)
    • 場所: データに最速でアクセスできるよう、お客様のユーザーに最も近い場所を使用します。Location: use the location that is closest to your users to give them the fastest access to the data.

    重要

    ここで指定するサーバー管理者のパスワードは、サーバーとそのデータベースにログインするために必要です。The server admin password that you specify here is required to log in to the server and its databases. 後で使用するために、この情報を覚えておくか、記録しておきます。Remember or record this information for later use.

  5. [サーバー グループの構成] をクリックします。Click Configure server group. そのセクションの設定を変更しないで、 [保存] をクリックします。Leave the settings in that section unchanged and click Save.

  6. ページの下部の [次へ:ネットワーク] をクリックします。Click Next : Networking > at the bottom of the screen.

  7. [ネットワーク] タブで、 [パブリック エンドポイント] ラジオ ボタンをクリックします。In the Networking tab, click the Public endpoint radio button. 選択されたパブリック エンドポイントPublic endpoint selected

  8. [+ 現在のクライアント IP アドレスを追加する] リンクをクリックします。Click the link + Add current client IP address. 追加されたクライアント IPAdded client IP

    注意

    Azure PostgreSQL サーバーはポート 5432 を介して通信します。Azure PostgreSQL server communicates over port 5432. 企業ネットワーク内から接続しようとしても、ポート 5432 での送信トラフィックがネットワークのファイアウォールで禁止されている場合があります。If you are trying to connect from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. その場合、会社の IT 部門によってポート 5432 が開放されない限り、Hyperscale (Citus) クラスターに接続することはできません。If so, you cannot connect to your Hyperscale (Citus) cluster unless your IT department opens port 5432.

  9. [確認と作成][作成] の順にクリックして、サーバーをプロビジョニングします。Click Review + create and then Create to provision the server. プロビジョニングには数分かかります。Provisioning takes a few minutes.

  10. ページは、デプロイを監視するためにリダイレクトされます。The page will redirect to monitor deployment. ライブ状態が [デプロイが進行中です] から [デプロイが完了しました] に変わったら、ページの左側にある [出力] メニュー項目をクリックします。When the live status changes from Your deployment is underway to Your deployment is complete, click the Outputs menu item on the left of the page.

  11. 出力ページには、コーディネーターのホスト名が含まれており、値をクリップボードにコピーするためのボタンが横に付いています。The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. 後で使用するために、この情報を記録しておきます。Record this information for later use.

psql を使用したデータベースへの接続Connect to the database using psql

Azure Database for PostgreSQL サーバーを作成したときに、citus という名前の既定のデータベースも作成されています。When you create your Azure Database for PostgreSQL server, a default database named citus is created. データベース サーバーに接続するには、接続文字列と管理者パスワードが必要です。To connect to your database server, you need a connection string and the admin password.

  1. 接続文字列を取得します。Obtain the connection string. サーバー グループ ページで、 [接続文字列] メニュー項目をクリックします。In the server group page click the Connection strings menu item. ( [設定] の下にあります)。psql のマークが付けられている文字列を見つけます。(It's under Settings.) Find the string marked psql. 次のような形式になります。It will be of the form:

    psql "host=hostname.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
    

    文字列をコピーします。Copy the string. "{your_password}" を以前に選択した管理者パスワードに置き換える必要があります。You will need to replace "{your_password}" with the administrative password you chose earlier. システムは、プレーンテキストのパスワードを保存しないため、接続文字列に表示できません。The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. ローカル コンピューターでターミナル ウィンドウを開きます。Open a terminal window on your local computer.

  3. プロンプトで、psql ユーティリティを使用して Azure Database for PostgreSQL サーバーに接続します。At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. パスワードが含まれていることを確認して、接続文字列を引用符で囲んで渡します。Pass your connection string in quotes, being sure it contains your password:

    psql "host=..."
    

    たとえば、次のコマンドは、サーバー グループ mydemoserver のコーディネーター ノードに接続します。For example, the following command connects to the coordinator node of the server group mydemoserver:

    psql "host=mydemoserver-c.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
    

psql ユーティリティを使用してスキーマを作成するUse psql utility to create a schema

psql を使用して Azure Database for PostgreSQL - Hyperscale (Citus) に接続すると、いくつかの基本的なタスクを完了することができます。Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) using psql, you can complete some basic tasks. このチュートリアルでは、Web 分析からトラフィック データを取り込んでから、データをロールアップし、そのデータに基づいてリアルタイムのダッシュボードを提供する方法について説明します。This tutorial walks you through ingesting traffic data from web analytics, then rolling up the data to provide real-time dashboards based on that data.

生の Web トラフィック データをすべて使用するテーブルを作成しましょう。Let's create a table that will consume all of our raw web traffic data. psql ターミナルで次のコマンドを実行します。Run the following commands in the psql terminal:

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

また、1 分あたりの集計を保持するテーブルと、前回のロールアップの位置を維持するテーブルを作成します。We're also going to create a table that will hold our per-minute aggregates, and a table that maintains the position of our last rollup. psql で次のコマンドも実行します。Run the following commands in psql as well:

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 コマンドを使用すると、テーブルの一覧に新しく作成されたテーブルが表示されます。You can see the newly created tables in the list of tables now with this psql command:

\dt

ノード全体でテーブルをシャード化するShard tables across nodes

ハイパースケールのデプロイでは、テーブルの行はユーザー指定の列の値に基づいて異なるノードに保存されます。A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. この "ディストリビューション列" には、ノード全体でデータがどのようにシャード化されているかが示されます。This "distribution column" marks how data is sharded across nodes.

ディストリビューション列がシャード キーである site_id になるように設定しましょう。Let's set the distribution column to be site_id, the shard key. psql で、次の関数を実行します。In psql, run these functions:

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

サンプル データを作成するGenerate sample data

これで、サーバー グループがデータを取り込む準備が整いました。Now our server group should be ready to ingest some data. psql 接続からローカルで以下を実行して、継続的にデータを挿入することができます。We can run the following locally from our psql connection to continuously insert data.

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

クエリは、毎秒約 8 行を挿入します。The query inserts approximately eight rows every second. 行は、ディストリビューション列 site_id の指示に従って、さまざまなワーカー ノードに格納されます。The rows are stored on different worker nodes as directed by the distribution column, site_id.

注意

データ生成クエリを実行したままにして、このチュートリアルのその他のコマンド用に 2 つ目の psql 接続を開きます。Leave the data generation query running, and open a second psql connection for the remaining commands in this tutorial.

クエリQuery

ハイパースケール ホスティング オプションを使用すると、複数のノードでクエリを並行処理して速度を上げることができます。The hyperscale hosting option allows multiple nodes to process queries in parallel for speed. たとえば、データベースではワーカー ノード上で SUM や COUNT などの集計が計算され、その結果が最終的な回答にまとめられます。For instance, the database calculates aggregates like SUM and COUNT on worker nodes, and combines the results into a final answer.

1 分あたりの Web 要求といくつかの統計情報をカウントするクエリを次に示します。Here's a query to count web requests per minute along with a few statistics. psql で実行して結果を確認してみてください。Try running it in psql and observe the results.

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;

データのロールアップRolling up data

上記のクエリは早い段階では問題なく動作しますが、データの規模が大きくなるにつれてパフォーマンスは低下します。The previous query works fine in the early stages, but its performance degrades as your data scales. 分散処理でも、繰り返し再計算するよりも、このデータを事前に計算する方が高速です。Even with distributed processing, it's faster to pre-compute the data than to recalculate it repeatedly.

生データを集計テーブルに定期的にロールアップすることで、ダッシュボードを確実に高速に維持させることができます。We can ensure our dashboard stays fast by regularly rolling up the raw data into an aggregate table. 集計期間で実験できます。You can experiment with the aggregation duration. 分単位の集計テーブルを使用しましたが、代わりにデータを 5、15、または 60 分に分割できます。We used a per-minute aggregation table, but you could break data into 5, 15, or 60 minutes instead.

このロールアップをより簡単に実行するために、これを plpgsql 関数で処理します。To run this roll-up more easily, we're going to put it into a plpgsql function. psql でこれらのコマンドを実行して rollup_http_request 関数を作成します。Run these commands in psql to create the rollup_http_request function.

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

関数を用意できたら、それを実行してデータをロールアップします。With our function in place, execute it to roll up the data:

SELECT rollup_http_request();

また、事前に集計されたフォームのデータを使用して、ロールアップ テーブルのクエリを実行し、前と同じレポートを取得することもできます。And with our data in a pre-aggregated form we can query the rollup table to get the same report as earlier. 次のクエリを実行します。Run the following query:

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;

古いデータを期限切れにするExpiring old data

ロールアップによってクエリは高速になりますが、無制限のストレージ コストを回避するために古いデータを期限切れにする必要があります。The rollups make queries faster, but we still need to expire old data to avoid unbounded storage costs. 細分性ごとにデータを保持する期間を決定し、標準のクエリを使用して期限切れのデータを削除します。Decide how long you’d like to keep data for each granularity, and use standard queries to delete expired data. 次の例では、生データは 1 日、分ごとの集計は 1 か月間保持することにしました。In the following example, we decided to keep raw data for one day, and per-minute aggregations for one month:

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

運用環境では、これらのクエリを関数にラップして cron ジョブで毎分呼び出すことができます。In production, you could wrap these queries in a function and call it every minute in a cron job.

リソースをクリーンアップするClean up resources

前の手順では、サーバー グループ内に Azure リソースを作成しました。In the preceding steps, you created Azure resources in a server group. これらのリソースが将来不要であると思われる場合は、サーバー グループを削除します。If you don't expect to need these resources in the future, delete the server group. サーバー グループの [概要] ページで、 [削除] ボタンを押します。Press the Delete button in the Overview page for your server group. ポップアップ ページでメッセージが表示されたら、サーバー グループの名前を確認し、最後の [削除] ボタンをクリックします。When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

次のステップNext steps

このチュートリアルでは、Hyperscale (Citus) サーバー グループのプロビジョニング方法を学習しました。In this tutorial, you learned how to provision a Hyperscale (Citus) server group. そのサーバー グループに psql で接続し、スキーマを作成して、データを分散しました。You connected to it with psql, created a schema, and distributed data. 生のフォームでデータのクエリを実行すること、そのデータを定期的に集計すること、集計されたテーブルのクエリを実行すること、古いデータを期限切れにすることを学習しました。You learned to query data in the raw form, regularly aggregate that data, query the aggregated tables, and expire old data.

次は、ハイパースケールの概念について学習します。Next, learn about the concepts of hyperscale.