Azure Cosmos DB for PostgreSQL で pg_azure_storage を使用してデータを取り込む方法

[アーティクル]
06/16/2023

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

この記事では、pg_azure_storage PostgreSQL 拡張機能を使用して Azure Blob Storage (ABS) からデータを操作し、Azure Cosmos DB for PostgreSQL に直接読み込む方法について説明します。 ABS は、クラウドネイティブのスケーラブルで永続的で安全なストレージサービスです。これらの特性により、既存のデータをクラウドに格納して移動するのに適しています。

データベースと BLOB ストレージを準備する

Azure Blob Storage からデータを読み込むには、データベースに pg_azure_storage PostgreSQL 拡張機能をインストールします。

SELECT * FROM create_extension('azure_storage');

重要

pg_azure_storage 拡張機能は、PostgreSQL 13 以降を実行する Azure Cosmos DB for PostgreSQL クラスター上でのみ使用できます。

この記事用の公開デモデータセットを準備しました。独自のデータセットを使用するには、オンプレミスデータのクラウドストレージへの移行に関する記事を確認し、データセットを Azure Blob Storage に効率的に取り込む方法を学習します。

Note

[コンテナー (コンテナーと BLOB の匿名読み取りアクセス)] を選択すると、パブリック URL を使用して Azure Blob Storage からファイルを取り込んで、pg_azure_storage でアカウントキーを構成することなくコンテナーの内容を列挙できます。アクセスレベル "プライベート (匿名アクセスなし)" または "BLOB (BLOB 専用の匿名読み取りアクセスのみ)" に設定されたコンテナーには、アクセスキーが必要です。

コンテナーの内容を一覧表示する

この攻略ガイド用に事前に作成された Azure Blob Storage アカウントおよびコンテナーのデモがあります。コンテナーの名前は github で、pgquickstart アカウント内にあります。 azure_storage.blob_list(account, container) 関数を使用することによって、コンテナー内に存在するファイルを簡単に確認できます。

SELECT path, bytes, pg_size_pretty(bytes), content_type
  FROM azure_storage.blob_list('pgquickstart','github');

-[ RECORD 1 ]--+-------------------
path           | events.csv.gz
bytes          | 41691786
pg_size_pretty | 40 MB
content_type   | application/x-gzip
-[ RECORD 2 ]--+-------------------
path           | users.csv.gz
bytes          | 5382831
pg_size_pretty | 5257 kB
content_type   | application/x-gzip

出力は、通常の SQL WHERE 句を使用するか、blob_list UDF の prefix パラメーターを使用することでフィルター処理できます。後者は、Azure Blob Storage 側で返された行をフィルター処理します。

Note

コンテナーの内容を一覧表示するには、アカウントとアクセスキー、または匿名アクセスが有効なコンテナーが必要です。

SELECT * FROM azure_storage.blob_list('pgquickstart','github','e');

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

SELECT *
  FROM azure_storage.blob_list('pgquickstart','github')
 WHERE path LIKE 'e%';

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

ABS からデータを読み込む

COPY コマンドを使用してデータを読み込む

最初に、サンプルスキーマを作成します。

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

テーブルへのデータの読み込みは、COPY コマンドを呼び出すのと同じくらい簡単になります。

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

コピーコマンドに指定された URL が Azure Blob Storage からのものであることを拡張機能がどのように認識したかを確認してください。指定したファイルは gzip 圧縮され、自動的に処理されます。

COPY コマンドでは、より多くのパラメーターと形式がサポートされています。上記の例では、ファイル拡張子に基づいて形式と圧縮が自動選択されています。ただし、通常の COPY コマンドと同様の形式を直接指定できます。

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');

現在、この拡張機能は次のファイル形式をサポートしています。

format	description
csv	PostgreSQL COPY で使用されるコンマ区切りの値の形式
tsv	タブ区切りの値 (PostgreSQL COPY の既定形式)
binary	バイナリ PostgreSQL COPY 形式
text	1 つのテキスト値を含むファイル (大きな JSON や XML など)

blob_get() を使用してデータを読み込む

COPY コマンドは便利ですが、柔軟性に限界があります。 COPY は内部的に blob_get 関数を使用します。これは、より複雑なシナリオでデータを直接操作するために使用できます。

SELECT *
  FROM azure_storage.blob_get(
         'pgquickstart', 'github',
         'users.csv.gz', NULL::github_users
       )
 LIMIT 3;

-[ RECORD 1 ]-+--------------------------------------------
user_id       | 21
url           | https://api.github.com/users/technoweenie
login         | technoweenie
avatar_url    | https://avatars.githubusercontent.com/u/21?
gravatar_id   |
display_login | technoweenie
-[ RECORD 2 ]-+--------------------------------------------
user_id       | 22
url           | https://api.github.com/users/macournoyer
login         | macournoyer
avatar_url    | https://avatars.githubusercontent.com/u/22?
gravatar_id   |
display_login | macournoyer
-[ RECORD 3 ]-+--------------------------------------------
user_id       | 38
url           | https://api.github.com/users/atmos
login         | atmos
avatar_url    | https://avatars.githubusercontent.com/u/38?
gravatar_id   |
display_login | atmos

注意

上記のクエリでは、ファイルは、LIMIT 3 が適用される前に完全にフェッチされます。

この関数を使用すると、複雑なクエリで素早くデータを操作し、INSERT FROM SELECT のようにインポートを実施できます。

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users)
      WHERE gravatar_id IS NOT NULL;

INSERT 0 264308

上記のコマンドでは、gravatar_id が存在するアカウントにデータをフィルター処理し、そのログオンを即座に大文字にしました。

blob_get() のオプション

状況によっては、blob_get、decoder、compression、options パラメーターを使用して実施する内容を正確に制御する必要がある場合があります。

デコーダーは、auto (既定値) または、次の値のいずれかに設定できます。

format	description
csv	PostgreSQL COPY で使用されるコンマ区切りの値の形式
tsv	タブ区切りの値 (PostgreSQL COPY の既定形式)
binary	バイナリ PostgreSQL COPY 形式
text	1 つのテキスト値を含むファイル (大きな JSON や XML など)

compression には、auto (既定値)、none または gzip を指定できます。

最後に、options パラメーターは jsonb 型です。値の構築に役立つ 4 つのユーティリティ関数があります。各ユーティリティ関数は、その名前に一致するデコーダーに指定されます。

デコーダー	オプション関数
csv	`options_csv_get`
tsv	`options_tsv`
binary	`options_binary`
text	`options_copy`

関数定義を調べることで、どのデコーダーでどのパラメーターがサポートされているかを確認できます。

options_csv_get - delimiter、null_string、header、quote、escape、force_not_null、force_null、content_encoding options_tsv - delimiter、null_string、content_encoding options_copy - delimiter、null_string、header、quote、escape、force_quote、force_not_null、force_null、content_encoding。 options_binary - content_encoding

上記を把握することで、解析中に null gravatar_id を含むレコーディングを破棄できます。

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users,
                                    options := azure_storage.options_csv_get(force_not_null := ARRAY['gravatar_id']));

INSERT 0 264308

プライベートストレージにアクセスする

アカウント名とアクセスキーを取得する

アクセスキーがない場合、プライベートまたは BLOB アクセスレベルに設定されているコンテナーを一覧表示することはできません。
```
SELECT * FROM azure_storage.blob_list('mystorageaccount','privdatasets');
```
```
ERROR:  azure_storage: missing account access key
HINT:  Use SELECT azure_storage.account_add('<account name>', '<access key>')
```
ストレージアカウントで、アクセスキーを開きます。 [ストレージアカウント名] をコピーし、[key1] セクションから [キー] をコピーします (最初にキーの横にある [表示] を選択する必要があります)。
pg_azure_storage にアカウントを追加する
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
これで、そのストレージのプライベートと BLOB のアクセスレベルに設定されたコンテナーを一覧表示できますが、これは azure_storage_admin ロールが付与されている citus ユーザーとしてのみ可能です。 support という名前の新しいユーザーを作成した場合、既定ではコンテナーの内容へのアクセスは許可されません。
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
support ユーザーが特定の Azure Blob Storage アカウントを使用できるようにする

アクセス許可の付与は、account_user_add を呼び出すのと同じくらい簡単です。
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
account_list の出力で許可されているユーザーを確認できます。これには、アクセスキーが定義されているすべてのアカウントが表示されます。
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
決定した場合、ユーザーはアクセスできなくなります。 account_user_remove を呼び出すだけです。
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

次のステップ

これで、Azure Blob Storage から Azure Cosmos DB for PostgreSQL に直接データを読み込む方法を学習しました。

Azure Cosmos DB for PostgreSQL を使用して、リアルタイムダッシュボードを作成する方法について学習します。
pg_azure_storage の詳細を確認します。
Postgres COPY のサポートについて説明します。

次の方法で共有

Azure Cosmos DB for PostgreSQL で pg_azure_storage を使用してデータを取り込む方法

データベースと BLOB ストレージを準備する

コンテナーの内容を一覧表示する

ABS からデータを読み込む

COPY コマンドを使用してデータを読み込む

blob_get() を使用してデータを読み込む

blob_get() のオプション

プライベートストレージにアクセスする

次のステップ

フィードバック

フィードバック

その他のリソース

次の方法で共有

Azure Cosmos DB for PostgreSQL で pg_azure_storage を使用してデータを取り込む方法

データベースと BLOB ストレージを準備する

コンテナーの内容を一覧表示する

ABS からデータを読み込む

COPY コマンドを使用してデータを読み込む

blob_get() を使用してデータを読み込む

blob_get() のオプション

プライベート ストレージにアクセスする

次のステップ

フィードバック

フィードバック

その他のリソース

プライベートストレージにアクセスする