チュートリアル: Azure Database for PostgreSQL - Hyperscale (Citus) を使用して、マルチテナント データベースを設計するTutorial: design a multi-tenant database 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
  • サンプル データを取り込むIngest sample data
  • テナント データをクエリするQuery tenant data
  • テナント間でデータを共有するShare data between tenants
  • テナントごとにスキーマをカスタマイズするCustomize the schema per-tenant

前提条件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 creating a web app that allows advertisers to track their campaigns.

複数の会社でこのアプリを使用できます。それでは、会社を保持するテーブルと会社のキャンペーンを保持する別のテーブルを作成しましょう。Multiple companies can use the app, so let's create a table to hold companies and another for their campaigns. psql コンソールで、次のコマンドを実行します。In the psql console, run these commands:

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,
  blacklisted_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

各キャンペーンでは、広告の掲載は有料になります。Each campaign will pay to run ads. 上記のコードの後に psql で次のコードを実行して、広告用のテーブルも追加します。Add a table for ads too, by running the following code in psql after the code above:

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

最後に、各広告のクリック数とインプレッション数に関する統計情報を追跡します。Finally, we'll track statistics about clicks and impressions for each ad:

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 のテーブルの一覧に新しく作成されたテーブルが表示されます。You can see the newly created tables in the list of tables now in psql by running:

\dt

マルチ テナント アプリケーションではテナントごとにのみ一意性を適用することができます。そのため、すべての主キーと外部キーには会社 ID が含まれています。Multi-tenant applications can enforce uniqueness only per tenant, which is why all primary and foreign keys include the company ID.

ノード全体でテーブルをシャード化する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 which tenant owns which rows.

ディストリビューション列がテナント識別子である company_id になるように設定しましょう。Let's set the distribution column to be company_id, the tenant identifier. psql で、次の関数を実行します。In psql, run these functions:

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

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

ここで、psql 外にある通常のコマンド ラインで、サンプル データ セットをダウンロードします。Outside of psql now, in the normal command line, download sample data sets:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

psql 内に戻って、データを一括読み込みします。Back inside psql, bulk load the data. psql は必ず、データ ファイルをダウンロードしたディレクトリと同じディレクトリで実行してください。Be sure to run psql in the same directory where you downloaded the data files.

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

これで、このデータは、ワーカー ノードにまたがって分散するようになります。This data will now be spread across worker nodes.

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

アプリケーションでシングル テナントのデータが要求されると、データベースでは単一のワーカー ノードでクエリを実行できます。When the application requests data for a single tenant, the database can execute the query on a single worker node. シングルテナント クエリでは、単一のテナント ID でフィルター処理が行われます。Single-tenant queries filter by a single tenant ID. たとえば、次のクエリでは広告とインプレッションに対して company_id = 5 がフィルターされます。For example, the following query filters company_id = 5 for ads and impressions. 結果を確認するには、psql で実行してみてください。Try running it in psql to see the results.

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;

テナント間でデータを共有するShare data between tenants

これまで、すべてのテーブルは company_id によって分散されていましたが、一部のデータは特定のテナントに自然に "属している" のではなく、共有することができます。Until now all tables have been distributed by company_id, but some data doesn't naturally "belong" to any tenant in particular, and can be shared. たとえば、サンプルの広告プラットフォームのすべての会社は、IP アドレスに基づいて対象ユーザーの地理情報を取得する必要があるかもしれません。For instance, all companies in the example ad platform might want to get geographical information for their audience based on IP addresses.

共有されている地理情報を保持するためのテーブルを作成します。Create a table to hold shared geographic information. 次のコマンドを psql で実行します。Run the following commands in 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 を "参照テーブル" にして、すべてのワーカー ノードにテーブルのコピーを保存します。Next make geo_ips a "reference table" to store a copy of the table on every worker node.

SELECT create_reference_table('geo_ips');

サンプル データで読み込みます。Load it with example data. このコマンドは必ず、データセットをダウンロードしたディレクトリ内の psql で実行してください。Remember to run this command in psql from inside the directory where you downloaded the dataset.

\copy geo_ips from 'geo_ips.csv' with csv

クリック数テーブルを geo_ips で結合すると、すべてのノードで効率的に作業できます。Joining the clicks table with geo_ips is efficient on all nodes. 広告をクリックしたすべてのユーザーの場所を検索するための結合を、以下に示します。Here is a join to find the locations of everyone who clicked on ad 290. psql でクエリを実行してみてください。Try running the query in 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;

テナントごとにスキーマをカスタマイズするCustomize the schema per-tenant

場合によっては、各テナントに他のテナントが必要としない特別な情報を保存する必要があります。Each tenant may need to store special information not needed by others. ただし、すべてのテナントは同一のデータベース スキーマを持つ共通インフラストラクチャを共有しています。However, all tenants share a common infrastructure with an identical database schema. 追加データはどこに移動できますか?Where can the extra data go?

コツは、PostgreSQL の JSONB のような制約のない列タイプを使用することです。One trick is to use an open-ended column type like PostgreSQL's JSONB. スキーマには、clicksuser_data という JSONB フィールドがあります。Our schema has a JSONB field in clicks called user_data. 会社 (例: 会社 5) は、この列を使用して、ユーザーがモバイル デバイス上にいるかどうかを追跡できます。A company (say company five), can use the column to track whether the user is on a mobile device.

クリックを多く行った実行元が、デバイスであるか従来の訪問者であるかを検索するためのクエリを、以下に示します。Here's a query to find who clicks more: mobile, or traditional visitors.

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;

部分インデックスを作成することで、単一の会社に対してこのクエリを最適化することができます。We can optimize this query for a single company by creating a partial index.

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

一般的には、列内のすべてのキーと値に GIN インデックスを作成できます。More generally, we can create a GIN indices on every key and value within the column.

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;

リソースのクリーンアップ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 both within and between tenants, and to customize the schema per tenant.

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