はじめに

Azure CosmosDB for PostgreSQLの試用版を試してみたので備忘のため記事を投稿します。

Azure CosmosDB試用版とは

CosmosDBを30日間無料で試すことができる試用版アカウントのこと。
Azureの無料アカウントを持っている場合でも別アカウントが作成される。(ややこしい・・)
クレジットカードの登録不要でアカウントを作成できる。
有効期限が切れた場合は新しい試用版アカウントを作成できる。
MicroSoftまたはGitHubのアカウントが必要。

CosmosDB試用版アカウント作成

以下CosmosDB試用版ホームページにアクセスする。

Azure CosmosDB for PostgreSQLの「Create」をクリックする。
「Sign in」をクリックする。
MicroSoftアカウントでサインインする。
「Open in portal」をクリックする。
確認画面が表示されるので「承諾」をクリックする。
試用版アカウント画面が表示される。
- この時点でPostgreSQLクラスタが作成されている。
「Important～」の「here」をクリックしてPostgreSQLのパスワードを変更し、「保存」をクリックする。

PostgreSQL接続準備

接続環境
- Windows11
- psql
以下のサイトよりPostgreSQLのWindowsインストーラをダウンロードする。
https://www.postgresql.jp/download
- WindowsのURLをクリックする。
- Download the installerをクリックする。
- Windows x86-64のダウンロードアイコンをクリックする。
ダウンロードしたインストーラ「postgresql-15.1-1-windows-x64.exe」を実行する。
デフォルトのままNextで進めていく。
Command Line Toolsのみにチェックを入れてNextをクリックする。
Nextをクリックする。
Finishをクリックする。
Pathを通す。
- Windowsの検索ボックスに「path」と入力し、システム環境変数の編集画面を開く。
- 「環境変数」をクリックする。
- ユーザー環境変数の「編集」をクリックする。
- 「新規」をクリックし、最終行にPostgreSQLのインストールフォルダのパス「C:\Program Files\PostgreSQL\15\bin」を追記して「OK」をクリックする。
- PowerShellを開いて「psql --version」の結果が返ることを確認する。

PostgreSQL接続

Azureポータル画面の左ペインより「接続文字列」を選択し、psqlの文字列をコピーする。
PowerShellでコピーしたpsqlコマンドでPostgreSQLに接続する。

PS > psql "host=～.postgres.database.azure.com port=5432 dbname=citus user=citus password=【変更したパスワード】 sslmode=require"
psql (15.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

citus=>

コピーした文字列の「{your_password}」の箇所は上記で変更したパスワードに置換する。

citus=>と表示されたら接続できています。

公式サイトのクイックスタートを実行してみる

テーブルの作成

CREATE TABLE github_users
(
	user_id bigint,
	url text,
	login text,
	avatar_url text,
	gravatar_id text,
	display_login text
);

CREATE TABLE github_events
(
	event_id bigint,
	event_type text,
	event_public boolean,
	repo_id bigint,
	payload jsonb,
	repo jsonb,
	user_id bigint,
	org jsonb,
	created_at timestamp
);

CREATE INDEX event_type_index ON github_events (event_type);
CREATE INDEX payload_index ON github_events USING GIN (payload jsonb_path_ops);

テーブルの分散

SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');

分散テーブルへのデータ読み込み

SELECT * FROM create_extension('azure_storage');

-- download users and store in table
COPY github_users FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz';

-- download events and store in table
COPY github_events FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';

-- download events and store in tableのSQLを実行した際に以下のエラーになりました。
以下サイトによるとCOPYコマンドを使う際によくある文字コード変換失敗のエラーのようです。

citus=> COPY github_events FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';
ERROR: character with byte sequence 0x85 0xa5 in encoding "SJIS" has no equivalent in encoding "UTF8"
CONTEXT: COPY , line 26

上記サイトを参考に文字コードをUTF8にして再実行する。

citus=> select current_setting('client_encoding');
 current_setting
-----------------
 SJIS
(1 row)


citus=>
citus=> set client_encoding to 'utf8';
SET
citus=>
citus=> COPY github_events FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';
COPY 126245
citus=>

成功しました！

分散テーブルを確認する。

citus=> SELECT * FROM citus_tables;
  table_name   | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method
---------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------
 github_events | distributed      | user_id             |             1 | 389 MB     |          32 | citus       | heap
 github_users  | distributed      | user_id             |             1 | 39 MB      |          32 | citus       | heap
(2 rows)

分散クエリを実行する。

citus=> SELECT count(*) FROM github_users;
 count
--------
 264308
(1 row)

github_usersテーブルは分散テーブルなので、データが複数のシャードに分割されているが、自動的にすべてのシャード上で同時にカウントを実行し、結果を合算して表示している。

その他のクエリの例

-- Find all events for a single user.
-- (A common transactional/operational query)

citus=> SELECT created_at, event_type, repo->>'name' AS repo_name
citus->   FROM github_events
citus->  WHERE user_id = 3861633;
     created_at      |  event_type  |              repo_name
---------------------+--------------+--------------------------------------
 2016-12-01 06:28:44 | PushEvent    | sczhengyabin/Google-Image-Downloader
 2016-12-01 06:29:27 | CreateEvent  | sczhengyabin/Google-Image-Downloader
 2016-12-01 06:36:47 | ReleaseEvent | sczhengyabin/Google-Image-Downloader
 2016-12-01 06:42:35 | WatchEvent   | sczhengyabin/Google-Image-Downloader
 2016-12-01 07:45:58 | IssuesEvent  | sczhengyabin/Google-Image-Downloader
(5 rows)

より複雑なクエリの例

-- Querying JSONB type. Query is parallelized across nodes.
-- Find the number of commits on the default branch per hour

citus=> SELECT date_trunc('hour', created_at) AS hour,
citus->        sum((payload->>'distinct_size')::int) AS num_commits
citus-> FROM   github_events
citus-> WHERE  event_type = 'PushEvent' AND
citus->        payload @> '{"ref":"refs/heads/master"}'
citus-> GROUP BY hour
citus-> ORDER BY hour;
        hour         | num_commits
---------------------+-------------
 2016-12-01 05:00:00 |       13051
 2016-12-01 06:00:00 |       43480
 2016-12-01 07:00:00 |       34254
 2016-12-01 08:00:00 |       29307
(4 rows)

citus=> ALTER TABLE github_users ADD COLUMN dummy_column integer;
ALTER TABLE

おわりに

試用アカウントは簡単に作成できて便利だなと思いました。
今後も学習に使用していきたいです！

【Azure】CosmosDB for PostgreSQLを試してみました