次の方法で共有

pg_azure_storage 拡張機能

  • [アーティクル]

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

pg_azure_storage 拡張機能を使用すると、複数のファイル形式のデータを Azure BLOB ストレージから Azure Cosmos DB for PostgreSQL クラスターに直接読み込むことができます。 拡張機能を有効にすると、COPY コマンドの新機能のロック解除も行われます。 アクセス レベルが "プライベート" または "BLOB" のコンテナーでは、プライベート アクセス キーを追加する必要があります。

拡張機能は、次を実行して作成できます。

SELECT create_extension('azure_storage');

azure_storage.account_add

この関数を使用すると、ストレージ アカウントへのアクセスを追加できます。

azure_storage.account_add
        (account_name_p text
        ,account_key_p text);

引数

account_name_p

Azure BLOB ストレージ (ABS) アカウントには、すべての ABS オブジェクト (BLOB、ファイル、キュー、テーブル) が含まれます。 ストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる ABS 用の一意の名前空間が提供されます。

account_key_p

Azure BLOB ストレージ (ABS) のアクセス キーは、ストレージ アカウントの root パスワードに似ています。 アクセス キーは常に慎重に保護してください。 キーを安全に管理およびローテーションするには、Azure Key Vault を使用します。 アカウント キーは、postgres スーパーユーザー、azure_storage_admin、およびそれらの管理者アクセス許可を付与されたすべてのロールがアクセスできるテーブルに保存されます。 存在するストレージ アカウントを確認するには、関数 account_list を使用します。

azure_storage.account_remove

この関数を使用すると、ストレージ アカウントへのアカウント アクセスを取り消すことができます。

azure_storage.account_remove
        (account_name_p text);

引数

account_name_p

Azure BLOB ストレージ (ABS) アカウントには、すべての ABS オブジェクト (BLOB、ファイル、キュー、テーブル) が含まれます。 ストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる ABS 用の一意の名前空間が提供されます。

azure_storage.account_user_add

この関数を使用すると、ストレージ アカウントへのロールのアクセスを追加できます。

azure_storage.account_user_add
        ( account_name_p text
        , user_p regrole);

引数

account_name_p

Azure BLOB ストレージ (ABS) アカウントには、すべての ABS オブジェクト (BLOB、ファイル、キュー、テーブル) が含まれます。 ストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる ABS 用の一意の名前空間が提供されます。

user_p

クラスターに表示されるユーザーによって作成されたロール。

注意

account_user_add、account_add、account_remove、account_user_remove 関数には、クラスター内の個々のノードに対するアクセス許可の設定が必要です。

azure_storage.account_user_remove

この関数を使用すると、ストレージ アカウントへのロールのアクセスを削除できます。

azure_storage.account_user_remove
        (account_name_p text
        ,user_p regrole);

引数

account_name_p

Azure BLOB ストレージ (ABS) アカウントには、すべての ABS オブジェクト (BLOB、ファイル、キュー、テーブル) が含まれます。 ストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる ABS 用の一意の名前空間が提供されます。

user_p

クラスターに表示されるユーザーによって作成されたロール。

azure_storage.account_list

この関数は、Azure BLOB ストレージにアクセスできるアカウントとロールを一覧表示します。

azure_storage.account_list
        (OUT account_name text
        ,OUT allowed_users regrole[]
        )
Returns TABLE;

引数

account_name

Azure BLOB ストレージ (ABS) アカウントには、すべての ABS オブジェクト (BLOB、ファイル、キュー、テーブル) が含まれます。 ストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる ABS 用の一意の名前空間が提供されます。

allowed_users

Azure BLOB ストレージにアクセスできるユーザーが一覧表示されます。

の戻り値の型 :

TABLE

azure_storage.blob_list

この関数を使用すると、ユーザー コンテナー内で使用可能な BLOB ファイルとそのプロパティが一覧表示されます。

azure_storage.blob_list
        (account_name text
        ,container_name text
        ,prefix text DEFAULT ''::text
        ,OUT path text
        ,OUT bytes bigint
        ,OUT last_modified timestamp with time zone
        ,OUT etag text
        ,OUT content_type text
        ,OUT content_encoding text
        ,OUT content_hash text
        )
Returns SETOF record;

引数

account_name

この storage account name では、世界中のどこからでも HTTPS 経由でアクセスできる Azure Storage データ用の一意の名前空間が提供されます。

container_name

ファイル システムのディレクトリと同じように、コンテナーを使用して BLOB のセットを整理できます。 ストレージ アカウントに含めることができるコンテナーの数には制限がなく、1 つのコンテナーに格納できる BLOB の数にも制限はありません。 コンテナー名は、コンテナーまたはその BLOB をアドレス指定するために使用される一意の URI の一部になるため、有効な DNS 名である必要があります。 コンテナーに名前を付けるときは、次の規則に従います。

  • コンテナー名の長さは 3 ～ 63 文字にする必要があります。
  • コンテナー名は英文字または数字で始まり、英小文字、数字、ダッシュ (-) 文字のみを含めることができます。
  • 2 つ以上の連続するダッシュ文字は、コンテナー名には使用できません。

コンテナーの URI は次のようになります: https://myaccount.blob.core.windows.net/mycontainer

prefix

文字列のイニシャルが一致する BLOB コンテナーからファイルを返します。

path

Azure BLOB ディレクトリの完全修飾パス。

バイト

ファイル オブジェクトのサイズ (バイト単位)。

last_modified

ファイルコンテンツが最後に変更された日時。

etag

ETag プロパティは、更新時のオプティミスティック コンカレンシーに使用されます。 レコードが最後に更新された時刻を保存する Timestamp という名前の別のプロパティがあるため、タイムスタンプではありません。 たとえば、エンティティを読み込んで更新する場合、ETag は現在保存されているものと一致する必要があります。 複数のユーザーが同じ項目を編集している場合に、互いの変更を上書きしないようにするため、適切な ETag を設定することが重要です。

content_type

BLOB オブジェクトは、不変の生データのファイルに似たオブジェクトである BLOB を表します。 テキストまたはバイナリ データとして読み取ったり、ReadableStream に変換して、そのメソッドをデータの処理に使用したりできます。 BLOB は、必ずしも JavaScript ネイティブ形式ではないデータを表すことができます。

content_encoding

Azure Storage を使用すると、BLOB に Content-Encoding プロパティを定義できます。 圧縮コンテンツの場合は、プロパティを GZIP に設定できます。 ブラウザーがコンテンツにアクセスすると、コンテンツが自動的に展開されます。

content_hash

このハッシュは転送時の BLOB の整合性を確認するために使用します。 このヘッダーを指定すると、ストレージ サービスによって、到達したハッシュと送信されたハッシュが照合されます。 2 つのハッシュが一致しない場合、操作はエラー コード 400 (無効な要求) で失敗します。

の戻り値の型 :

SETOF レコード

注意

アクセス許可 これで、そのストレージのプライベートと BLOB のアクセス レベルに設定されたコンテナーを一覧表示できますが、これは azure_storage_admin ロールが付与されている citus user としてのみ可能です。 support という名前の新しいユーザーを作成した場合、既定ではコンテナーの内容へのアクセスは許可されません。

azure_storage.blob_get

この関数を使用すると、コンテナー内からファイルのコンテンツを読み込むことができます。さらに、インポート前に、データのフィルター処理または操作のサポートが追加されています。

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF record;

関数のオーバーロードされたバージョンがあり、出力形式レコードを簡単に定義できる rec パラメーターが含まれています。

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,rec anyelement
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF anyelement;

引数

account

このストレージ アカウントでは、世界中のどこからでも HTTPS 経由でアクセスできる Azure Storage データ用の一意の名前空間が提供されます。

container

ファイル システムのディレクトリと同じように、コンテナーを使用して BLOB のセットを整理できます。 ストレージ アカウントに含めることができるコンテナーの数には制限がなく、1 つのコンテナーに格納できる BLOB の数にも制限はありません。 コンテナー名は、コンテナーまたはその BLOB をアドレス指定するために使用される一意の URI の一部になるため、有効な DNS 名である必要があります。

path

コンテナー内に存在する BLOB 名。

rec

レコード出力構造を定義します。

デコーダー

BLOB 形式の指定デコーダーは、自動 (既定値) または、次の値のいずれかに設定できます

decoder description

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

compression

圧縮形式を定義します。 使用可能なオプションは、 auto、 gzip 、 noneです。 auto オプション (既定値) を使用すると、ファイル拡張子 (.gz == gzip) に基づいて圧縮が推測されます。 オプション none を指定すると、拡張機能が強制的に無視され、デコードは試行されません。 gzip は強制的に gzip デコーダーを使用します (標準以外の拡張子を持つ gzipped ファイルがある場合)。 現在、拡張機能の他の圧縮形式はサポートされていません。

options

カスタム ヘッダー、カスタム区切り記号、エスケープ文字などを処理するために、options は PostgreSQL の COPY コマンドと同様の方法で動作し、パラメーターを blob_get 関数に使用します。

の戻り値の型 :

SETOF レコード / anyelement

注意

blob_get 内のパラメーターとして呼び出される 4 つのユーティリティ関数があり、値の構築に役立ちます。 各ユーティリティ関数は、その名前に一致するデコーダーに指定されます。

azure_storage.options_csv_get

この関数は、blob_get 内のパラメーターとして呼び出されるユーティリティ関数として機能し、csv コンテンツのデコードに役立ちます。

azure_storage.options_csv_get
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

引数

delimiter

ファイルの各行内の列を区切る文字を指定します。 既定では、テキスト形式のタブ文字、CSV 形式ではコンマです。 1 バイト文字にする必要があります。

null_string

null 値を表す文字列を指定します。 既定では、テキスト形式の \N (バックスラッシュ N)、CSV 形式では引用符で囲まれていない空の文字列です。 null と空の文字列を区別しない場合には、テキスト形式でも空の文字列を使用するほうが良いかもしれません。

ファイルに、ファイル内の各列の名前を含むヘッダー行が含まれていることを指定します。 出力時に、最初の行にはテーブルの列名が含まれます。

quote

データ値を引用符で囲むときに使用する引用符文字を指定します。 既定では二重引用符です。 1 バイト文字にする必要があります。

エスケープ

QUOTE 値と一致するデータ文字の前に表示される文字を指定します。 既定では、QUOTE 値と同じです (データに引用符が表示される場合、引用符文字は 2 倍になります)。 1 バイト文字にする必要があります。

force_not_null

指定した列の値を null 文字列と一致させません。 null 文字列が空である既定のケースでは、空の値は引用符で囲まれていない場合でも、null ではなく長さ 0 の文字列として読み取られます。

force_null

引用符で囲まれていても、指定した列の値を null 文字列と照合し、一致が見つかった場合は値を NULL に設定します。 null 文字列が空である既定のケースでは、引用符で囲まれた空の文字列が NULL に変換されます。

content_encoding

ファイルが encoding_name でエンコードされることを指定します。 このオプションを省略すると、現在のクライアント エンコードが使用されます。

の戻り値の型 :

jsonb

azure_storage.options_copy

この関数は、blob_get 内のパラメーターとして呼び出されるユーティリティ関数として機能します。

azure_storage.options_copy
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_quote text[] DEFAULT NULL::text[]
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

引数

delimiter

ファイルの各行内の列を区切る文字を指定します。 既定では、テキスト形式のタブ文字、CSV 形式ではコンマです。 1 バイト文字にする必要があります。

null_string

null 値を表す文字列を指定します。 既定では、テキスト形式の \N (バックスラッシュ N)、CSV 形式では引用符で囲まれていない空の文字列です。 null と空の文字列を区別しない場合には、テキスト形式でも空の文字列を使用するほうが良いかもしれません。

header

ファイルに、ファイル内の各列の名前を含むヘッダー行が含まれていることを指定します。 出力時に、最初の行にはテーブルの列名が含まれます。

quote

データ値を引用符で囲むときに使用する引用符文字を指定します。 既定では二重引用符です。 1 バイト文字にする必要があります。

エスケープ

QUOTE 値と一致するデータ文字の前に表示される文字を指定します。 既定では、QUOTE 値と同じです (データに引用符が表示される場合、引用符文字は 2 倍になります)。 1 バイト文字にする必要があります。

force_quote

指定した各列のすべての NULL 以外の値に対して、強制的に引用符を使用します。 NULL 出力が引用符で囲まれることはありません。 * を指定すると、NULL 以外の値がすべての列で引用符で囲まれます。

force_not_null

指定した列の値を null 文字列と一致させません。 null 文字列が空である既定のケースでは、空の値は引用符で囲まれていない場合でも、null ではなく長さ 0 の文字列として読み取られます。

force_null

引用符で囲まれていても、指定した列の値を null 文字列と照合し、一致が見つかった場合は値を NULL に設定します。 null 文字列が空である既定のケースでは、引用符で囲まれた空の文字列が NULL に変換されます。

content_encoding

ファイルが encoding_name でエンコードされることを指定します。 このオプションを省略すると、現在のクライアント エンコードが使用されます。

の戻り値の型 :

jsonb

azure_storage.options_tsv

この関数は、blob_get 内のパラメーターとして呼び出されるユーティリティ関数として機能します。 tsv コンテンツをデコードする場合に便利です。

azure_storage.options_tsv
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

引数

delimiter

ファイルの各行内の列を区切る文字を指定します。 既定では、テキスト形式のタブ文字、CSV 形式ではコンマです。 1 バイト文字にする必要があります。

null_string

null 値を表す文字列を指定します。 既定では、テキスト形式の \N (バックスラッシュ N)、CSV 形式では引用符で囲まれていない空の文字列です。 null と空の文字列を区別しない場合には、テキスト形式でも空の文字列を使用するほうが良いかもしれません。

content_encoding

ファイルが encoding_name でエンコードされることを指定します。 このオプションを省略すると、現在のクライアント エンコードが使用されます。

の戻り値の型 :

jsonb

azure_storage.options_binary

この関数は、blob_get 内のパラメーターとして呼び出されるユーティリティ関数として機能します。 バイナリ コンテンツをデコードする場合に便利です。

azure_storage.options_binary
        (content_encoding text DEFAULT NULL::text)
Returns jsonb;

引数

content_encoding

ファイルが encoding_name でエンコードされることを指定します。 このオプションを省略すると、現在のクライアント エンコードが使用されます。

戻り値の型

jsonb

注意

アクセス許可 これで、そのストレージのプライベートと BLOB のアクセス レベルに設定されたコンテナーを一覧表示できますが、これは azure_storage_admin ロールが付与されている citus user としてのみ可能です。 support という名前の新しいユーザーを作成した場合、既定ではコンテナーの内容へのアクセスは許可されません。

例

使用される例では、さまざまなユース ケースの対象範囲に追加するためにアップロードされたカスタム ファイルを含むサンプル Azure ストレージ アカウント (pgquickstart) を使用します。 まず、使用する一連の例で使用されるテーブルを作成します。

CREATE TABLE IF NOT EXISTS public.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 without time zone
        );

ストレージ アカウントのアクセス キーの追加 (アクセス レベル = プライベートには必須)

この例では、Azure Cosmos DB for Postgres クラスター上のセッションからクエリを実行するためのアクセス権を取得するためのストレージ アカウントのアクセス キーの追加を示しています。

SELECT azure_storage.account_add('pgquickstart', 'SECRET_ACCESS_KEY');

ヒント

ストレージ アカウントで、アクセス キーを開きます。 [ストレージ アカウント名] をコピーし、[key1] セクションから [キー] をコピーします (最初にキーの横にある [表示] を選択する必要があります)。

Azure portal の [Azure Blob Storage] ページの [セキュリティとネットワーク] > [アクセス キー] セクションのスクリーンショット。

ストレージ アカウントのアクセス キーの削除

この例では、ストレージ アカウントのアクセス キーを削除する方法を示します。 このアクションにより、コンテナー内のプライベート バケットでホストされているファイルへのアクセスが削除されます。

SELECT azure_storage.account_remove('pgquickstart');

Azure Blob Storage へのアクセス権をロールに追加する

SELECT * FROM azure_storage.account_user_add('pgquickstart', 'support');

Azure Blob Storage へのアクセス権を持つすべてのロールを一覧表示する

SELECT * FROM azure_storage.account_list();

Azure Blob Storage へのアクセス権を持つロールを削除する

SELECT * FROM azure_storage.account_user_remove('pgquickstart', 'support');

public コンテナー内のオブジェクトを一覧表示する

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

private コンテナー内のオブジェクトを一覧表示する

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

Note

アクセス キーの追加は必須です。

パブリック コンテナー内の特定の文字列イニシャルを持つオブジェクトを一覧表示する

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

あるいは

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

コンテナー内のオブジェクトからコンテンツを読み取る

blob_get 関数は、BLOB ストレージからファイルを取得します。 blob_get がデータの解析方法を知るために、ファイルと同じ形式の値 (NULL::table_name) を渡すことができます。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv.gz'
        , NULL::events)
LIMIT 5;

または、FROM 句で列を明示的に定義することもできます。

SELECT * FROM azure_storage.blob_get('pgquickstart','publiccontainer','events.csv')
AS res (
         event_id BIGINT
        ,event_type TEXT
        ,event_public BOOLEAN
        ,repo_id BIGINT
        ,payload JSONB
        ,repo JSONB
        ,user_id BIGINT
        ,org JSONB
        ,created_at TIMESTAMP WITHOUT TIME ZONE)
LIMIT 5;

デコーダー オプションを使用する

この例では、decoder オプションの使用について示します。 通常、形式はファイルの拡張子から推論されますが、ファイル コンテンツに一致する拡張子がない場合は、デコーダー引数を渡すことができます。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events'
        , NULL::events
        , decoder := 'csv')
LIMIT 5;

デコーダー オプションで圧縮を使用する

この例では、標準の .gz 拡張子を使用せずに gzip 圧縮ファイルで gzip 圧縮を適用する方法を示します。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events-compressed'
        , NULL::events
        , decoder := 'csv'
        , compression := 'gzip')
LIMIT 5;

フィルター処理されたコンテンツをインポートし、csv 形式オブジェクトから読み込む前に変更する

この例では、SQL テーブルに読み込む前に、コンテナー内のオブジェクトからインポートされるコンテンツをフィルター処理および変更する可能性を示しています。

SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;

ヘッダー、カスタム区切り文字、エスケープ文字を使用してファイルからコンテンツを照会する

azure_storage.options_copy の結果を options 引数に渡すことで、カスタムの区切り記号とエスケープ文字を使用できます。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events_pipe.csv'
        ,NULL::events
        ,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
        );

コンテナー内のオブジェクトのコンテンツに対する集計クエリ

この方法で、データをインポートせずにクエリを実行できます。

SELECT event_type,COUNT(1) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
GROUP BY event_type
ORDER BY 2 DESC
LIMIT 5;

次の手順

COPY コマンドと組み合わせて Azure Blob Storage を使用する方法

pg_azure_storage を使用してデータを取り込む方法