AnalyticDB:外部テーブルを使用して OSS から AnalyticDB for PostgreSQL にデータをインポートする方法

仕組み

gpossext 機能は、oss:// プロトコルハンドラで AnalyticDB for PostgreSQL を拡張します。oss_ext 拡張機能をインストールした後、OSS オブジェクトにマッピングされる読み取り可能な外部テーブルを定義します。INSERT INTO ... SELECT * FROM を実行すると、各コンピュートノードはポーリングメカニズムを介して OSS オブジェクトのサブセットを並列で読み取り、その後、行を正しい宛先パーティションに再配布します。

gpossext は、GZIP 圧縮の有無にかかわらず、TEXT および CSV ファイルを読み取ります。

外部テーブルを作成および使用するための構文は、location 関連のパラメーターの構文を除き、Greenplum Database の構文と同じです。

前提条件

開始する前に、以下が準備できていることを確認してください。

• AnalyticDB for PostgreSQL V6.0 インスタンス

• ご利用のインスタンスと同じリージョンにある OSS バケット

• バケットへの読み取りアクセス権を持つ AccessKey ID と AccessKey Secret

• TEXT または CSV ファイル (プレーンまたは GZIP 圧縮) として OSS にアップロード済みのソースデータ

OSS からのデータインポート

CREATE EXTENSION IF NOT EXISTS oss_ext;

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] ]
ossprotocol:
    oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name|filepath=[folder/[folder/]...]/file_name]
     id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

-- 行のサンプルを確認
SELECT * FROM <External table> LIMIT 10;

-- クエリプランを検査して並列読み取りを確認
EXPLAIN INSERT INTO <Destination table> SELECT * FROM <External table>;

INSERT INTO <Destination table> SELECT * FROM <External table>;

例

この例では、OSS から株価データを example という名前のテーブルにインポートします。

1. 拡張機能をインストールします。

CREATE EXTENSION IF NOT EXISTS oss_ext;

2. 宛先テーブルを作成します。

CREATE TABLE example
    (date text, time text, open float,
     high float, low float, volume int)
    DISTRIBUTED BY (date);

インポートパフォーマンスを最大化するには、圧縮を使用した列指向ストレージを使用します。たとえば、次の句を指定できます：WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)。詳細については、「CREATE TABLE」をご参照ください。

3. 外部テーブルを作成します。OSS でのファイルの整理方法に一致するパスパラメーターを選択します。

prefix を使用 (一致するパスプレフィックスを持つすべてのオブジェクトをインポート、GZIP をサポート)：

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 SEGMENT REJECT LIMIT 5;

dir を使用 (ディレクトリ内のすべてのオブジェクトを直接インポート、GZIP なし)：

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;

filepath を使用 (指定されたオブジェクトをインポート)：

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;

4. データをインポートします。

INSERT INTO example SELECT * FROM ossexample;

クエリプランは、4 つすべてのコンピュートノードが OSS から並列で読み取ることを確認します。

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)

TEXT および CSV 形式のリファレンス

以下のパラメーターは、gpossext が TEXT および CSV ファイルを解析する方法を制御します。これらは CREATE EXTERNAL TABLE 文の FORMAT 句で指定します。

• \n：行区切り文字 (改行文字)

• DELIMITER：列区切り文字です。指定した場合、QUOTE も指定する必要があります。一般的な選択肢：,、|、\t。

• QUOTE：特殊文字を含む列値を囲みます。DELIMITER とは異なる必要があります。デフォルト："。QUOTE 文字を含む値は ESCAPE も使用する必要があります。

• ESCAPE：リテラルとして扱う必要がある文字の前に置かれます。デフォルト：QUOTE と同じです。バックスラッシュ (\) も一般的に使用されます。

すべての制御文字はシングルバイトである必要があります。

デフォルトの制御文字

トラブルシューティング

SELECT * FROM gp_read_error_log('external_table_name');

SELECT gp_truncate_error_log('external_table_name');

次のステップ

• OSS ドメイン名 — リージョンごとの正しいエンドポイントを検索

• OSS ドキュメント — バケットとオブジェクトの管理

• CREATE TABLE — テーブルストレージオプションの完全な構文

• エラー応答 — OSS エラーコードリファレンス

• エラー処理エラー処理 ー タイムアウトとエラー処理のガイダンス