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

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

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

https://learn.microsoft.com/ja-jp/azure/cosmos-db/try-free?tabs=postgresql

# CosmosDB試用版アカウント作成
- 以下CosmosDB試用版ホームページにアクセスする。

https://cosmos.azure.com/try/

- Azure CosmosDB for PostgreSQLの「Create」をクリックする。
![図27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/0d3feb0b-afd1-a836-b5ae-30569d9ab683.png)

- 「Sign in」をクリックする。
![図28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/15de7974-5565-08bc-a24c-4ac1b922b9c9.png)

- MicroSoftアカウントでサインインする。

- 「Open in portal」をクリックする。
![図29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/05cf0c4a-3688-615c-a8ec-30503c63405b.png)

- 確認画面が表示されるので「承諾」をクリックする。
![図30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/a7880436-d55b-9dcf-d6f8-342dbe0857a9.png)

- 試用版アカウント画面が表示される。
  - この時点でPostgreSQLクラスタが作成されている。 
![図31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/1adb40c2-03e8-80ca-f5b5-d54b64a42566.png)

- 「Important～」の「here」をクリックしてPostgreSQLのパスワードを変更し、「保存」をクリックする。
![図32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/e65a9226-8b3d-b591-8e93-1fd827b35b07.png)

# PostgreSQL接続準備
- 接続環境
    - Windows11
    - psql

- 以下のサイトよりPostgreSQLのWindowsインストーラをダウンロードする。
https://www.postgresql.jp/download

  - WindowsのURLをクリックする。
![図33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/613fe03a-99fd-3df5-1d30-0098d3c8fb70.png)

  - Download the installerをクリックする。
![図34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/6a77b217-e30a-b6cb-9031-003d9b36a1e1.png)

  - Windows x86-64のダウンロードアイコンをクリックする。
![図35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/3e37746d-51e5-053a-f8be-3000d20a65f6.png)

- ダウンロードしたインストーラ「postgresql-15.1-1-windows-x64.exe」を実行する。

- デフォルトのままNextで進めていく。
![図36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/634df4af-5612-a45f-fd6b-4e8e8d69d4f9.png)
![図37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/b0c1f83c-29ef-790d-b40a-34cd142c24b6.png)

- Command Line Toolsのみにチェックを入れてNextをクリックする。
![図38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/ceb41f62-bd34-8462-dc32-c493c6427931.png)

- Nextをクリックする。
![図39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/907a30a3-5fad-480e-b865-3065bfc3e2fb.png)
![図40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/c45d8f5f-6454-a563-1788-c1c812c7539c.png)

- Finishをクリックする。
![図41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/d885a0c5-8ae9-8a6b-9a29-64ad7a075ab2.png)

- Pathを通す。
  - Windowsの検索ボックスに「path」と入力し、システム環境変数の編集画面を開く。
![図42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/bcfce1d0-7ea8-f418-a067-5a1b540d4c21.png)

  - 「環境変数」をクリックする。
![図43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/a6bb5a40-c202-9233-0a6c-58fcc596129a.png)

  - ユーザー環境変数の「編集」をクリックする。
![図44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/fbf3aa6c-d859-b2f8-1ef2-22c83a9d8572.png)

  - 「新規」をクリックし、最終行にPostgreSQLのインストールフォルダのパス「C:\Program Files\PostgreSQL\15\bin」を追記して「OK」をクリックする。
![図45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/a439e73b-2956-175b-84ef-4c68b18796e9.png)

  - PowerShellを開いて「psql --version」の結果が返ることを確認する。
![図46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/f0f8f488-e181-7c49-f3ea-9729d57d1f6b.png)

# PostgreSQL接続
- Azureポータル画面の左ペインより「接続文字列」を選択し、psqlの文字列をコピーする。
![図47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2957750/1c01f9b8-796c-e83b-4024-ed79b57e0cf1.png)

- 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=>
```
:::note info
コピーした文字列の「{your_password}」の箇所は上記で変更したパスワードに置換する。
:::

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

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

https://learn.microsoft.com/ja-jp/azure/cosmos-db/postgresql/quickstart-distribute-tables

- テーブルの作成
```
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';
```

:::note warn
-- 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
:::

https://kenpg.bitbucket.io/blog/201601/12.html

- 上記サイトを参考に文字コードを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)
```
:::note info
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
```

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