https://learn.microsoft.com/ja-jp/azure/cosmos-db/postgresql/quickstart-create-portal?tabs=direct
https://learn.microsoft.com/ja-jp/cli/azure/cosmosdb?view=azure-cli-latest
1 node, no high availability (HA)
Burstable, 1 vCore(s) / 2 GiB RAM, 3,153.54 JPY / month
※macのAzure CLIからクラスター作成はコマンドが認識されないので、Azure Cloud Shellから実行
-- 1. 前作業
az account show
az version
az configure --list-defaults
az configure --defaults location=japaneast
az configure --list-defaults
az group create \
--name rg9999999 \
--location japaneast
az group list
az upgrade
-- 2. Azure Cosmos DB for PostgreSQL クラスターの作成
az cosmosdb postgres cluster create \
--cluster-name cluster01 \
--resource-group rg9999999 \
--administrator-login-password 'password' \
--coord-public-ip-access true \
--coord-server-edition BurstableMemoryOptimized \
--coordinator-storage 32768 \
--coordinator-v-cores 1 \
--enable-ha no \
--enable-shards-on-coord yes \
--no-wait no \
--node-count 0 \
--node-enable-public-ip-access false \
--node-server-edition MemoryOptimized \
--node-storage 524288 \
--node-v-cores 2 \
--postgresql-version 15
az cosmosdb postgres cluster list
az cosmosdb postgres cluster server list \
--cluster-name cluster01 \
--resource-group rg9999999
az cosmosdb postgres role list \
--cluster-name cluster01 \
--resource-group rg9999999
az cosmosdb postgres firewall-rule create \
--cluster-name cluster01 \
--resource-group rg9999999 \
--firewall-rule-name fw01 \
--start-ip-address 192.0.2.1 \
--end-ip-address 192.0.2.1 \
--no-wait no
az cosmosdb postgres firewall-rule list \
--cluster-name cluster01 \
--resource-group rg9999999
-- 3. 接続する
PGPASSWORD='password' psql -h c-cluster01.xxxxxxxxxxxxxx.postgres.cosmos.azure.com -p 5432 -U citus -d citus
SHOW server_version;
-- 4. テーブルの作成
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);
-- 5. テーブルの分散
SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');
-- 6. 分散テーブルへのデータの読み込み
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';
SELECT * FROM citus_tables;
-- 7. 分散クエリ
-- count all rows (across shards)
SELECT count(*) FROM github_users;
-- Find all events for a single user.
-- (A common transactional/operational query)
SELECT created_at, event_type, repo->>'name' AS repo_name
FROM github_events
WHERE user_id = 3861633;
-- 8. より複雑なクエリ
-- Querying JSONB type. Query is parallelized across nodes.
-- Find the number of commits on the default branch per hour
SELECT date_trunc('hour', created_at) AS hour,
sum((payload->>'distinct_size')::int) AS num_commits
FROM github_events
WHERE event_type = 'PushEvent' AND
payload @> '{"ref":"refs/heads/master"}'
GROUP BY hour
ORDER BY hour;
-- DDL commands that are also parallelized
ALTER TABLE github_users ADD COLUMN dummy_column integer;
-- 9. クリーンアップ
az group list
az group delete \
--name rg9999999 \
--yes
