Azure Cosmos DB for PostgreSQL を使用して、マルチテナント データベースを設計する

適用対象: Azure Cosmos DB for PostgreSQL (PostgreSQL の Citus データベース拡張機能を利用)

このチュートリアルでは、Azure Cosmos DB for PostgreSQL を使用して、次の方法を学習します。

前提条件

Azure サブスクリプションをお持ちでない場合は、開始する前に無料アカウントを作成してください。

クラスターの作成

Azure portal にサインインし、次の手順を実行して、Azure Cosmos DB for PostgreSQL クラスターを作成します。

[Azure Cosmos DB for PostgreSQL クラスターの作成] フォームで、次を実行します。

1. [基本] タブに情報を入力します。

   

   ほとんどのオプションは文字どおりのわかりやすいものですが、次の点に注意してください。

   • <node-qualifier>-<clustername>.<uniqueID>.postgres.cosmos.azure.com という形式で、クラスター名によって、アプリケーションで接続に使用する DNS 名が決まります。
   • 15 などの主要な PostgreSQL バージョンを選択できます。 Azure Cosmos DB for PostgreSQL では、選択したメジャー Postgres バージョンの最新の Citus バージョンが常にサポートされます。
   • 管理者ユーザー名は、citus という値にする必要があります。
   • データベース名は既定値 'citus' のままにするか、データベース名のみを定義できます。 クラスターのプロビジョニング後にデータベースの名前を変更することはできません。

2. 画面の下部にある [次へ: ネットワーク] を選択します。

3. [ネットワーク] 画面で、[Azure 内の Azure サービスおよびリソースにこのクラスターへのパブリック アクセスを許可する] を選択します。

   

4. [確認および作成] を選択し、検証で問題がなければ、[作成] を選択してクラスターを作成します。

5. プロビジョニングには数分かかります。 デプロイを監視するために、ページがリダイレクトされます。 状態が [デプロイが進行中です] から [デプロイが完了しました] に変わったら、[リソースに移動] を選択します。

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

マルチ テナント アプリケーションではテナントごとにのみ一意性を適用することができます。そのため、すべての主キーと外部キーには会社 ID が含まれています。

ノード全体でテーブルをシャード化する

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

サンプル データを取り込む

ここで、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

これで、このデータは、ワーカー ノードにまたがって分散するようになります。

テナント データをクエリする

アプリケーションでシングル テナントのデータが要求されると、データベースでは単一のワーカー ノードでクエリを実行できます。 シングルテナント クエリでは、単一のテナント ID でフィルター処理が行われます。 たとえば、次のクエリでは広告とインプレッションに対して 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 で結合すると、すべてのノードで効率的に作業できます。 広告 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 フィールドがあります。 会社 (例: 会社 5) は、この列を使用して、ユーザーがモバイル デバイス上にいるかどうかを追跡できます。

より多くクリックしたのはモバイルであるか従来の訪問者であるかを検索するためのクエリを、以下に示します。

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 で接続し、スキーマを作成して、データを分散しました。 テナント内とテナント間の両方でデータをクエリしたり、テナントごとにスキーマをカスタマイズしたりすることを学習しました。

• クラスターのノードの種類について学習する
• クラスターに最適な初期サイズを決定する