AnalyticDB for PostgreSQL では、OSS の外部テーブル (gpossext 機能) を使って、OSS へのデータのインポート、および OSS からのエクスポートを同時に行うことができます。また、OSS の外部テーブルファイルを gzip で圧縮することで、ストレージスペースとコストを削減します。
gpossext関数は、text / csvファイルまたはtext / csvファイルをgzip形式で読み書きできます。
OSS 外部テーブルの拡張子を作成する (oss_ext)
OSS 外部テーブルを使用する前に、最初に oss_ext 拡張子 (デ ータベースの oss_ext) を作成する必要があります。
- oss_ext を作成するには、次のコマンドを実行します。
CREATE EXTENSION IF NOT EXISTS oss_ext;
- oss_ext を削除するには、コマンド
DROP EXTENSION IF EXISTS oss_ext;
を実行します。
データを同時にインポートする
データをインポートするには、次の手順を実行します。
複数の OSS ファイルにデータを均等に分散して保存します。ファイル数は、 AnalyticDB for PostgreSQL データノード数 (セグメント数) の整数倍であることが望ましいです。
AnalyticDB for PostgreSQL データベースに、READABLE 外部テーブルを作成します。
次のコマンドを実行して、データを同時にインポートします。
INSERT INTO <target table> SELECT * FROM <external table>
データを同時にエクスポートする
データをエクスポートするには、次の手順を実行します。
AnalyticDB for PostgreSQL データベースに WRITABLE 外部テーブルを作成します。
次のコマンドを実行して、OSS にデータを同時にエクスポートします。
INSERT INTO <external table> SELECT * FROM <source table>
OSS 外部テーブルの構文を作成する
次のコマンドを実行して、OSS 外部テーブルの構文を作成します。
CREATE [READABLE] EXTERNAL TABLE tablename
( columnname datatype [, ...] | LIKE othertable )
LOCATION ('ossprotocol')
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'escape']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
[ ENCODING 'encoding' ]
[ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
[ROWS | PERCENT] ]
CREATE WRITABLE EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('ossprotocol')
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] ]
[ESCAPE [AS] 'escape'] )]
[ ENCODING 'encoding' ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
ossprotocol:
oss://oss_endpoint prefix=prefix_name
id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
oss://oss_endpoint dir=[folder/[folder/]...]/file_name
id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
oss://oss_endpoint filepath=[folder/[folder/]...]/file_name
id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
パラメータの説明
一般的なパラメータ
プロトコルとエンドポイント:フォーマットは
protocol name: //oss_endpoint
です。「protocol name」は OSS で、「oss_endpoint」は対応する OSS リージョンのドメイン名です。注:アクセスリクエストが Alibaba Cloud ホストからのものである場合は、イントラネットドメイン名 (つまり、ドメイン名に「internal」) を使用して、パブリックデータの使用を回避します。
id: OSS アカウント ID です。
key: OSS アカウントキーです。
bucket: データファイルがあるバケットを指定します。OSS で事前に作成する必要があります。
prefix: データファイルの対応するパス名のプレフィックスを指定します。正規表現をサポートしていないため、マッチングプレフィックスのみです。また、filepath および dir と相互に排他的です。つまり、同時に指定することはできません。
データインポート用の READABLE 外部テーブルを作成すると、このプレフィックスを持つすべての OSS ファイルがインポートされます。
prefix = test / filename
を指定した場合は、次のファイルがすべてインポートされます。- test/filename
- test/filenamexxx
- test/filename/aa
- test/filenameyyy/aa
- test/filenameyyy/bb/aa
prefix = test / filename /
を指定した場合、次のファイルだけがインポートされます。 (先にリストしたファイルはインポートされません)- test/filename/aa
データエクスポートのための WRITABLE 外部テーブルを作成すると、エクスポートしたファイルに名前を付けるためのプレフィックスに基づき、一意のファイル名が自動的に生成されます。
注:複数のファイルをエクスポートすると、すべてのデータノードが 1 つ以上のファイルをエクスポートします。エクスポートするファイル名の形式は
prefix_tablename_uuid.x
です。具体的に言うと、uuid
は生成された int64 整数値 (マイクロ秒単位のタイムスタンプ) であり、x
はノード ID です。AnalyticDB for PostgreSQL は、同じ外部テーブルを使用して複数のエクスポート操作をサポートします。すべてのエクスポート操作からエクスポートされたファイルは UUID によって区別され、同じエクスポート操作でエクスポートされたファイルは同じ UUID を共有します。
dir: OSS 内の仮想フォルダのパスです。プレフィックスとファイルパスは相互に排他的です。
フォルダのパスは「/」で終わります。たとえば、
test/mydir/
です。データのインポート中にこのパラメータを使用して外部テーブルを作成すると、指定した仮想ディレクトリの下にあるすべてのファイル (サブディレクトリおよびサブディレクトリ下のファイルを除く) がインポートされます。ファイルパスとは異なり、dir ディレクトリ下にあるファイルの命名要件はありません。
データのエクスポート中にこのパラメータを使用して外部テーブルを作成すると、すべてのデータがこのディレクトリの下にある複数のファイルにエクスポートされます。出力ファイル名は
filename.x
の形式に従います。具体的には、x
は数字ですが、不連続でもかまいません。
filepath: OSS のパスを含むファイル名です。プレフィックスと dir とは相互排他的です。READABLE 外部テーブルの作成時にのみ指定することができます。(つまり、データのインポート時のみ使用可能です)
ファイル名にはファイルパスが含まれていますが、バケット名は含んでいません。
ファイルの命名規則は、データのインポート中は
filename
またはfilename.x
に従う必要があります。x
は 1 から始まり、連続する必要があります。たとえば、filepath = filename
を指定し、OSS に以下のファイルが含まれている場合、インポートされるファイルには filename、filename.1、および filename.2 が含まれます。filename.3 は存在しないため、filename.4 はインポートされません。filename
filename.1
filename.2
filename.4,
インポートモードのパラメータ
async: 非同期モードでデータを読み込むかどうかを指定します。
- ワーカースレッドを有効にして OSS からデータを読み込むと、インポートのパフォーマンスが向上します。
- 非同期モードはデフォルトで有効になっており、通常モードよりも多くハードウェアリソースを消費します。
async = false
またはasync = f
を使って無効にすることができます。
compressiontype: インポートしたファイルの圧縮形式です。
- none (デフォルト値) に指定すると、インポートしたファイルは圧縮されません。
- gzip に指定すると、インポートした形式は gzip になります。gzip 圧縮形式のみがサポートされています。
エクスポートモードのパラメータ
oss_flush_block_size:OSS にフラッシュされる単一データのバッファサイズです。デフォルトは、32 MB です。値の範囲は 1 MB ~ 128 MB です。
oss_file_max_size:OSS に書き込まれるファイルの最大サイズ。この制限を超えると、エクスポートは別のファイルに切り替わり、データの書き込みを続行します。デフォルト値は、 1,024 MB で、範囲は 8 MB から 4,000 MB です。
また、エクスポートモードでは次の項目に注意してください。
WRITABLE は、エクスポートモードの外部テーブルのキーワードです。外部テーブルを作成するときは、明示的に指定する必要があります。
エクスポートモードでは、プレフィックスおよび dir パラメーターモードのみがサポートされ、ファイルパスはサポートされません。
エクスポートモードの DISTRIBUTED BY 節を使用すると、データノード (セグメント) は、指定された分散キーで OSS にデータを書き込むことができます。
その他の一般的なパラメータ
インポートモードとエクスポートモードには、次のフォールトトレラントなパラメータもあります。
oss_connect_timeout:接続のタイムアウトを設定します。単位は秒で、デフォルト値は 10 秒です。
oss_dns_cache_timeout:DNS タイムアウトを設定します。単位は秒で、デフォルト値は 60 秒です。
oss_speed_limit:最小許容レートを制御します。デフォルト値は、1,024 (つまり 1 K) です。
oss_speed_time:最大許容時間を制御します。デフォルト値は、15 秒です。
上記のすべてのパラメータをデフォルトとして設定すると、連続する 15 秒間に、伝送速度が 1 K より遅い場合にタイムアウトが発生します。詳細は、OSS SDK エラー処理 をご参照ください。
例
# Create an external table for OSS import
create readable external table ossexample
(date text, time text, open float, high float,
low float, volume int)
location('oss://oss-cn-hangzhou.aliyuncs.com
prefix=osstest/example id=XXX
key=XXX bucket=testbucket' compressiontype=gzip)
FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
ENCODING 'utf8'
LOG ERRORS INTO my_error_rows SEGMENT REJECT LIMIT 5;
create readable external table ossexample
(date text, time text, open float, high float,
low float, volume int)
location('oss://oss-cn-hangzhou.aliyuncs.com
dir=osstest/ id=XXX
key=XXX bucket=testbucket')
FORMAT 'csv'
LOG ERRORS SEGMENT REJECT LIMIT 5;
create readable external table ossexample
(date text, time text, open float, high float,
low float, volume int)
location('oss://oss-cn-hangzhou.aliyuncs.com
filepath=osstest/example.csv id=XXX
key=XXX bucket=testbucket')
FORMAT 'csv'
LOG ERRORS SEGMENT REJECT LIMIT 5;
# Create an external table for OSS export
create WRITABLE external table ossexample_exp
(date text, time text, open float, high float,
low float, volume int)
location('oss://oss-cn-hangzhou.aliyuncs.com
prefix=osstest/exp/outfromhdb id=XXX
key=XXX bucket=testbucket') FORMAT 'csv'
DISTRIBUTED BY (date);
create WRITABLE external table ossexample_exp
(date text, time text, open float, high float,
low float, volume int)
location('oss://oss-cn-hangzhou.aliyuncs.com
dir=osstest/exp/ id=XXX
key=XXX bucket=testbucket') FORMAT 'csv'
DISTRIBUTED BY (date);
# Create a heap table to load data
create table example
(date text, time text, open float,
high float, low float, volume int)
DISTRIBUTED BY (date);
# Load data from ossexample to example in parallel
insert into example select * from ossexample;
# Export data from example to OSS in parallel
insert into ossexample_exp select * from example;
# We can see from the following query plan that every segment participates in the work.
# They pull data from the OSS in parallel and then use the Redistribute Motion node to distribute the hashed data to corresponding segments. Data-receiving segments store the data to the database through the Insert node.
explain insert into example select * from ossexample;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Insert (slice0; segments: 4) (rows=250000 width=92)
-> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..11000.00 rows=250000 width=92)
Hash Key: ossexample.date
-> External Scan on ossexample (cost=0.00..11000.00 rows=250000 width=92)
(4 rows)
# We can see from the following query plan that the segment exports local data directly to the OSS without data redistribution.
explain insert into ossexample_exp select * from example;
QUERY PLAN
---------------------------------------------------------------
Insert (slice0; segments: 3) (rows=1 width=92)
-> Seq Scan on example (cost=0.00..0.00 rows=1 width=92)
(2 rows)
注意
ロケーション関連のパラメータとは別に、外部テーブルの作成と使用に関する構文の残りの部分は、Greenplum の構文と一貫性があります。
データのインポートパフォーマンスは、 AnalyticDB for PostgreSQL クラスタリソース (CPU、IO、メモリ、ネットワークなど) および OSS と関連しています。最適なインポートパフォーマンスを実現するために、テーブルを作成するときは、圧縮列ストアを使用することをお勧めします。たとえば、
WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)
という節を指定できます。
- ossendpoint リージョンは、データのインポートのパフォーマンスを保証するために、 AnalyticDB for PostgreSQL リージョンと一致する必要があります。最適なパフォーマンスを得るには、同じリージョン内の OSS および AnalyticDB for PostgreSQL インスタンスを設定することをお勧めします。関連情報については、OSS エンドポイント情報 をご参照ください。
TEXT / CSV形式の説明
外部表のDDLパラメータに次のパラメータを指定して、OSSの読取り/書込み操作のファイル形式を指定できます。
- TEXT / CSVの行区切り文字は改行文字である '\ n'です。
- DELIMITERは、列を定義するために使用される区切り文字です。
- DELIMITERがユーザーデータに含まれている場合は、QUOTEパラメーターが必要です。*推奨される列区切り文字は '、'、 '\ t'、 '|'またはまれな文字です。
- QUOTEは、特殊文字を含むユーザー・データを列にラップするために使用されます。特殊文字を含む文字列は、ユーザデータを制御文字と区別するためにQUOTEで囲まれています。 パフォーマンス最適化の考慮事項(たとえば、整数の場合)のために、QUOTEでデータをラップする必要がないことがあります。
- QUOTEはDELIMITERと同じにすることはできません。デフォルトのQUOTEは二重引用符です。*ユーザーデータにQUOTE文字が含まれている場合は、エスケープ文字ESCAPEを使用してユーザーデータを区別する必要があります。
- ESCAPEは特殊文字用のエスケープ文字です*エスケープ文字は、特殊文字の前に表示され、特殊文字でないことを示します。
- ESCAPEはデフォルトでQUOTEと同じですが、二重引用符で囲みます。
- '\'(MySQLのデフォルトのエスケープ文字)またはその他の文字で指定することもできます。
典型的なデフォルトのTEXT / CSV制御文字
制御文字\書式 | テキスト | CSV |
---|---|---|
DELIMITER(列区切り文字) | \ t(タブ) | 、 (コンマ) |
クォート(引用) | “(二重引用符) | “(二重引用符) |
エスケープ(エスケープ) | (該当なし) | QUOTEと同じ |
NULL(NULL) | \ N(バックスラッシュ-N) | (引用符のない空文字列) |
すべての制御文字は1バイト文字でなければなりません。
SDK エラー処理
データのインポートおよびエクスポート中の操作エラーの場合、エラーログには次の情報が表示されます。
code:誤ったリクエストの HTTP ステータスコードです。
error_code:OSS のエラーコードです。
error_msg:OSS のエラーメッセージです。
req_id:リクエストの UUID です。問題を解決できない場合は、req_id を使用して OSS 開発エンジニアにヘルプを依頼してください。
詳細は、OSS API エラー応答 をご参照ください。タイムアウト関連のエラーは、oss_ext 関連のパラメーターを使用して処理できます。
よくある質問
インポートが遅い場合は、前述の 注意点 セクションのインポートパフォーマンスの説明をご参照ください。