PolarDB:oss_fdwを使用した外部データファイルの読み書き

oss_fdw パラメーター

oss_fdwプラグインは、OSSに保存されている外部データをカプセル化するために、他の外部データラッパー (FDW) インターフェイスで使用されているのと同じ方法を使用します。 oss_fdwを使用してOSSのデータを読み取ることができます。 このプロセスは、データテーブルの読み取りに似ています。 oss_fdwは、OSSのファイルデータに接続して解析するための一意のパラメーターを提供します。

CREATE SERVER パラメーター

• ossendpoint: 内部ネットワークを介してOSSにアクセスするために使用されるエンドポイント。 このパラメーターは、ホストとも呼ばれます。
• id oss: アカウントのID。
• key oss: アカウントのAccessKeyペア。
• bucket: アクセスしたいデータが保存されているOSSバケット。 このパラメーターを指定する前に、OSSアカウントを作成する必要があります。

次のフォールトトレランスパラメーターは、データのインポートとエクスポートに使用します。 ネットワークの状態が悪い場合は、これらのパラメーターを調整して、インポートとエクスポートを成功させることができます。

• oss_connect_timeout: 接続タイムアウト期間を示します。 デフォルト値は 10 です。 単位は秒です。
• oss_dns_cache_timeout: DNS タイムアウト期間を示します。 デフォルト値: 60。 単位は秒です。
• oss_speed_limit: 最小データ転送速度を示します。 デフォルト値: 1024。 単位: byte/s。
• oss_speed_time: データ伝送速度が最小値より低い最大待機期間。 デフォルト値は、15 秒です。

oss_speed_limitとoss_speed_timeのデフォルト値を使用すると、伝送レートが15秒間連続で1,024バイト /秒を下回るとタイムアウトエラーが発生します。

CREATE FOREIGN TABLE パラメーター

• filepath: ファイルパスを含むOSSファイル名。
  • ファイル名にはファイルパスが含まれますが、バケット名は含まれません。
  • このパラメーターは、指定されたOSSパスの複数のファイルと一致します。 データベースに複数のファイルをロードすることができます。
  • 名前がfilepathまたはfilepath.x形式に従うファイルをデータベースにインポートできます。 x の値は、1 から始まる連続した数値である必要があります。 たとえば、filepath、filepath.1、filepath.2、filepath.3、filepath.5という名前のファイルの中で、最初の4つのファイルが一致してインポートされますが、filepath.5ファイルはインポートされません。
• dir: OSSの仮想ファイルディレクトリ。
  • 指定されたディレクトリはスラッシュ (/) で終わる必要があります。
  • dirで指定された仮想ファイルディレクトリ内のすべてのファイル (サブフォルダおよびサブフォルダ内のファイルを除く) が照合され、データベースにインポートされます。
• prefix: データファイルに対応するパス名の接頭語。 正規表現はサポートされていません。 prefix、filepath、dirパラメーターは相互に排他的です。 一度に指定できるのは1つだけです。
• format: ファイル形式は csv のみが可能です。
• encoding: エンコード形式。 UTF-8 などの一般的な PostgreSQL エンコーディングフォーマットがサポートされています。
• parse_errors: フォールトトレラント解析モードが使用されます。 解析プロセス中に行にエラーが発生した場合、行全体のエラーは無視されます。
• delimiter: 列の区切り文字。
• quote: ファイルの引用文字。
• escape: ファイルのエスケープ文字。
• null: 指定された文字列に一致する列の値をnullに設定します。 たとえば、null 'test' は、値が 'test' である列を特定し、値をnullに設定するために使用されます。
• force_not_null: 指定された列の値がnullになることを許可しません。 たとえば、force_not_null 'id' は、空のID列セルの値がnull値ではなく空の文字列であるというルールを設定するために使用されます。
• compressiontype: OSS で読み書きするファイルの形式を指定します。
  • none: ファイルは圧縮されていません。 デフォルト値です。
  • gzip: ファイルはgzipを使用して圧縮されます。
• compressionlevel: OSSに書き込まれたデータファイルの圧縮度を指定します。 有効値: 1～9。 デフォルト値: 6。

CREATE FOREIGN TABLE のエクスポートモードパラメーター

• oss_flush_block_size: 一度にOSSに書き込まれるデータのバッファサイズ。 デフォルト値: 32 MB。 有効な値: 1～128 MB。

• oss_file_max_size: OSSに書き込まれるデータファイルの最大サイズ。 データファイルが最大サイズに達すると、残りのデータは別のデータファイルに書き込まれます。 デフォルト値: 1024。 有効値: 8 ~ 4000 単位：MB。

• num_parallel_worker: OSSに書き込まれたデータを圧縮するために並列に実行できるスレッドの最大数。 有効値: 1～8。 デフォルト値：3

補助関数

FUNCTION oss_fdw_list_file (relname text, schema text DEFAULT 'public')

• この関数は、外部テーブルが一致するOSSファイルの名前とサイズを取得します。

• ファイルサイズはバイト単位で測定されます。

select * from oss_fdw_list_file('t_oss');
              name              |   size    
--------------------------------+-----------
 oss_test/test.gz.1  | 739698350
 oss_test/test.gz.2  | 739413041
 oss_test/test.gz.3  | 739562048
(3 rows)

補助機能

oss_fdw.rds_read_one_file: 読み取りモードでは、この機能を使用して、外部テーブルと一致するファイルを指定します。 外部テーブルは、データインポート中に指定されたファイルのみに一致します。

例: set oss_fdw.rds_read_one_file = 'oss_test/example16.csv.1';

set oss_fdw.rds_read_one_file = 'oss_test/test.gz.2';
select * from oss_fdw_list_file('t_oss');
              name              |   size    
--------------------------------+-----------
  oss_test/test.gz.2  | 739413041
(1 rows)

oss_fdwの例

# Create a plug-in.
create extension oss_fdw; 
# Create a server. 
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou.aliyuncs.com', id 'xxx', key 'xxx', bucket 'mybucket');
# Create an OSS foreign table.
CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS ( filepath 'osstest/example.csv', delimiter ',' ,
         format 'csv', encoding 'utf8', PARSE_ERRORS '100');
# Create a table to which you want to load data.
create table example
        (date text, time text, open float,
         high float, low float, volume int);
# Load data from the ossexample table to the example table.
insert into example select * from ossexample;
# Result
# oss_fdw estimates the file size in OSS and formulates a query plan.
explain insert into example select * from ossexample;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Insert on example  (cost=0.00..1.60 rows=6 width=92)
   ->  Foreign Scan on ossexample  (cost=0.00..1.60 rows=6 width=92)
         Foreign OssFile: osstest/example.csv.0
         Foreign OssFile Size: 728
(4 rows)
# Write data from the example table to OSS.
insert into ossexample select * from example;
explain insert into ossexample select * from example;
                           QUERY PLAN
-----------------------------------------------------------------
 Insert on ossexample  (cost=0.00..16.60 rows=660 width=92)
   ->  Seq Scan on example  (cost=0.00..16.60 rows=660 width=92)
(2 rows)

oss_fdw使用法ノート

• oss_fdwは、PostgreSQL foreign tableフレームワークに基づいて開発された外部テーブルプラグインです。

• データインポートパフォーマンスは、PolarDBクラスターリソース (CPU、I/O、メモリ、ネットワークリソース) とOSSパフォーマンスの影響を受けます。

• データインポートのパフォーマンスを確保するには、PolarDBクラスターがOSSバケットと同じリージョンにあることを確認します。 詳細については、「OSS のエンドポイント」をご参照ください。

• 外部テーブルからSQL文を読み取るときにエラーerror: oss endpoint userendpoint not in aliyun white listが報告された場合は、パブリックエンドポイントを使用することを推奨します。

  詳細については、「Alibaba Cloudゾーンのパブリックエンドポイント」をご参照ください。 問題が解決しない場合は、お問い合わせください。

エラー処理

インポートまたはエクスポートエラーが生じた場合には、エラーログには次の情報が含まれています。

• code: 失敗したリクエストのHTTPステータスコード。

• error_code: OSS から返されたエラーコード。

• error_msg: OSS から返されたエラーメッセージ。

• req_id: リクエストの汎用一意識別子 (UUID) 。 問題の解決に支援が必要な場合は、失敗したリクエストのreq_idを含むチケットをOSSエンジニアに送信できます。

エラーの詳細については、次のリファレンスを参照してください。 タイムアウトエラーは、oss_extパラメーターを使用して処理できます。

• OSSエラー処理

• OSSエラー応答

AccessKey IDとAccessKey secretの暗号化

CREATE SERVERのidパラメーターとkeyパラメーターが暗号化されていない場合、他のユーザーはselect * from pg_foreign_serverステートメントを実行することで、AccessKeyペアをプレーンテキストで取得できます。 対称暗号化を使用して、AccessKey IDとAccessKeyシークレットを非表示にできます。 インスタンスごとに異なるAccessKeyペアを使用して、情報をさらに保護します。 ただし、以前のバージョンとの非互換性を防ぐために、Greenplumのようにデータ型を追加しないでください。

暗号化された情報

postgres=# select * from pg_foreign_server ;
  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                              srvoptions
-----------+----------+--------+---------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 ossserver |       10 |  16390 |         |            |        | {host=oss-cn-hangzhou-zmf.aliyuncs.com,id=MD5xxxxxxxx,key=MD5xxxxxxxx,bucket=067862}

暗号化された値はMD5文字列で始まります。 全長を 8 で割った余りは 3 です。 したがって、エクスポートされたデータがインポートされた後、データは再び暗号化されません。 ただし、MD5文字列で始まるAccessKey IDまたはAccessKeyシークレットは作成できません。