AnalyticDB:データレイク分析のための OSS 外部テーブルの使用

サポートされている形式

制限事項

• AnalyticDB for PostgreSQL インスタンスと OSS バケットは、同じリージョンにある必要があります。

• filetype パラメーターは、CSV、TEXT、JSON、および JSON Lines オブジェクトにのみ適用されます。JSON および JSON Lines では Snappy 圧縮はサポートされていません。

• log_errors および segment_reject_limit パラメーターは、CSV および TEXT オブジェクトにのみ適用されます。

• OSS サーバーの endpoint パラメーターでは、内部 (VPC) エンドポイントのみがサポートされています。

前提条件

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

• 同じリージョンにある AnalyticDB for PostgreSQL インスタンスと OSS バケット

• OSS バケット名、オブジェクトパスまたはディレクトリ、および内部エンドポイント。詳細については、以下の「OSS バケット情報の取得」をご参照ください。

• OSS バケットへのアクセス権を持つ AccessKey ID と AccessKey Secret。詳細については、「AccessKey ペアの作成」をご参照ください。

• サンプルデータファイル。このトピックの例に従うには、example.csv をダウンロードしてください。

仕組み

OSS FDW の設定には、3 つの DDL ステップがあります。

1. OSS サーバーの作成 — OSS エンドポイントとバケットを登録します。

2. ユーザーマッピングの作成 — ご利用の AnalyticDB for PostgreSQL データベースユーザーを OSS AccessKey 認証情報にバインドします。

3. OSS 外部テーブルの作成 — テーブルスキーマを定義し、OSS オブジェクトパスまたはディレクトリにマッピングします。

これらのステップの後、通常の AnalyticDB for PostgreSQL テーブルと同様に、標準 SQL を使用して外部テーブルをクエリします。

ステップ 1: OSS サーバーの作成

CREATE SERVER を実行して OSS エンドポイントを登録します。PostgreSQL リファレンスの詳細については、「CREATE SERVER」をご参照ください。

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
    );

-- パラメーターの変更
ALTER SERVER oss_serv OPTIONS (SET endpoint 'oss-cn-********.aliyuncs.com');

-- パラメーターの追加
ALTER SERVER oss_serv OPTIONS (ADD connect_timeout '20');

-- パラメーターの削除
ALTER SERVER oss_serv OPTIONS (DROP connect_timeout);

ステップ 2: ユーザーマッピングの作成

CREATE USER MAPPING を実行して、データベースユーザーを OSS AccessKey 認証情報にバインドします。詳細については、「CREATE USER MAPPING」をご参照ください。

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI****************',
        key 'yourAccessKeySecret'
    );

ステップ 3: OSS 外部テーブルの作成

CREATE FOREIGN TABLE を実行して、テーブルスキーマを定義し、OSS オブジェクトにマッピングします。詳細については、「CREATE FOREIGN TABLE」をご参照ください。

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

-- 方法 1: クエリプランと一致するオブジェクトの確認
EXPLAIN VERBOSE SELECT * FROM ossexample;

-- 方法 2: 一致する OSS オブジェクトのリスト表示
SELECT * FROM get_oss_table_meta('ossexample');

OSS データのクエリ

ネイティブ AnalyticDB for PostgreSQL テーブルをクエリするのと同じ方法で、標準 SQL を使用して OSS 外部テーブルをクエリします。

列値によるフィルタリング:

SELECT * FROM ossexample WHERE volume = 5;

GROUP BY による集計:

SELECT low, sum(volume)
FROM ossexample
GROUP BY low
ORDER BY low
LIMIT 5;

一致する行数のカウント:

SELECT count(*) FROM ossexample WHERE volume = 5;

OSS 外部テーブルと AnalyticDB for PostgreSQL テーブルの結合

1. ネイティブ AnalyticDB for PostgreSQL テーブルを作成し、データを挿入します。

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. OSS 外部テーブルをネイティブテーブルと結合します。

   SELECT example.volume, min(high), max(low)
   FROM ossexample, example
   WHERE ossexample.volume = example.volume
   GROUP BY example.volume
   ORDER BY example.volume;

フォールトトレランス

OSS FDW は、log_errors および segment_reject_limit パラメーターを介したフォールトトレラントなスキャンをサポートしています。有効にすると、解析エラーのある行は、スキャン全体が失敗する代わりにログに記録されます。

フォールトトレラントな外部テーブルの作成:

CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
    SERVER oss_serv
    OPTIONS (
        log_errors 'true',          -- 失敗する代わりにエラー行をログに記録
        segment_reject_limit '10',  -- 10 行を超えるエラーがある場合、スキャンを停止
        dir 'error_sales/',
        format 'csv',
        encoding 'utf8'
    );

エラーログのクエリ:

SELECT * FROM gp_read_error_log('oss_error_sales');

エラーログのクリア:

SELECT gp_truncate_error_log('oss_error_sales');

よくある質問

OSS 外部テーブルからデータを削除すると、OSS 内のデータも削除されますか?

いいえ。OSS 外部テーブルからデータを削除しても、OSS に保存されているデータは削除されません。

次のステップ

• パーティション化された OSS 外部テーブルの使用

• OSS 外部テーブルを使用したデータのインポートとエクスポート

• OSS 外部テーブルでの操作のベストプラクティス

• OSS 外部テーブルのデータ型マッピング