AnalyticDB:パーティション分割されたOSS外部テーブルの使用

注意事項

パーティション分割されたOSS外部テーブル機能では、OSS 内のオブジェクトが特定のディレクトリ構造に従う必要があります。ディレクトリパスは oss://bucket/partcol1=partval1/partcol2=partval2/ の形式である必要があります。ここで、partcol1 と partcol2 はパーティション列であり、partval1 と partval2 はパーティションに対応する値です。

たとえば、パーティションテーブルが year 列でパーティション分割され、month 列でサブパーティション分割されている場合、year=2022 や month=07 などの特定のパーティションのオブジェクトは、oss://bucket/year=2022/month=07/ ディレクトリに格納する必要があります。

OSSサーバー、OSSユーザーマッピング、OSS FDWの作成

OSS FDW を使用する前に、事前に OSS サーバー、OSS ユーザーマッピング、および OSS FDW を作成する必要があります。

• OSSサーバーの作成方法については、「OSSサーバーの作成」をご参照ください。

• OSSユーザーマッピングの作成方法については、「OSSユーザーマッピングの作成」をご参照ください。

• OSS FDWの作成方法については、「OSS外部テーブルの作成」をご参照ください。

パーティションテーブルの作成

CREATE FOREIGN TABLE ステートメントを使用して、パーティション分割されたOSS外部テーブルを作成できます。構文は、標準のパーティションテーブルを作成する場合と同じです。詳細については、「パーティションテーブルの定義」をご参照ください。

CREATE FOREIGN TABLE の構文の詳細については、「OSS外部テーブルの作成」をご参照ください。

• パーティションテンプレートを使用して、ossfdw_parttable という名前のパーティションテーブルを作成します。

  CREATE FOREIGN TABLE ossfdw_parttable( 
    key text,
    value bigint,
    pt text, -- パーティションキー
    region text -- サブパーティションキー
  ) 
  SERVER oss_serv
  OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
  PARTITION BY LIST (pt) -- pt 列でテーブルをパーティション分割します。
  SUBPARTITION BY LIST (region) -- region 列でテーブルをサブパーティション分割します。
      SUBPARTITION TEMPLATE ( -- サブパーティションテンプレート
         SUBPARTITION hangzhou VALUES ('hangzhou'),
         SUBPARTITION shanghai VALUES ('shanghai')
      )
  ( PARTITION "20170601" VALUES ('20170601'), 
    PARTITION "20170602" VALUES ('20170602'));

• パーティションテンプレートを使用せずに、ossfdw_parttable1 という名前のパーティションテーブルを作成します。

  CREATE FOREIGN TABLE ossfdw_parttable1( 
    key text,
    value bigint,
    pt text, -- パーティションキー
    region text -- サブパーティションキー
  ) 
  SERVER oss_serv
  OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
  PARTITION BY LIST (pt) -- pt 列でテーブルをパーティション分割します。
  SUBPARTITION BY LIST (region) -- region 列でテーブルをサブパーティション分割します。
  (
      -- 以下の 2 つのパーティションには、異なるサブパーティションを含めることができます。
      VALUES('20181218')
      (
          VALUES('hangzhou'),
          VALUES('shanghai') 
      ),
      VALUES('20181219')
      (
          VALUES('nantong'),
          VALUES('anhui') 
      ) 
  );

パーティション分割されたOSS外部テーブルの変更

ALTER TABLE ステートメントを使用して、パーティション分割されたOSS外部テーブルを変更します。AnalyticDB for PostgreSQL は、パーティション分割されたOSS外部テーブルでのパーティションの追加と削除をサポートしています。

パーティションの追加

• パーティションの追加

  • ossfdw_parttable テーブルにパーティションを追加するには、次のステートメントを実行します。このテーブルはパーティションテンプレートを使用しているため、システムは対応するサブパーティションを自動的に作成します。

    ALTER TABLE ossfdw_parttable ADD PARTITION VALUES ('20170603');

    スキーマは次のように更新されます。

    

  • ossfdw_parttable1 テーブルにパーティションを追加するには、次のステートメントを実行します。このテーブルはパーティションテンプレートを使用していないため、サブパーティションを明示的に指定する必要があります。

    ALTER TABLE ossfdw_parttable1 ADD PARTITION VALUES ('20181220')
    (
        VALUES('hefei'),
        VALUES('guangzhou') 
    );

• サブパーティションの追加

  ossfdw_parttable テーブルの 20170603 パーティションにサブパーティションを追加するには、次のステートメントを実行します。

  ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170603') ADD PARTITION VALUES('nanjing');

  スキーマは次のように更新されます。

  

パーティションの削除

• パーティションを削除するには、次のステートメントを実行します。

  ALTER TABLE ossfdw_parttable DROP PARTITION FOR ('20170601');

• サブパーティションを削除するには、次のステートメントを実行します。

  ALTER TABLE ossfdw_parttable ALTER PARTITION FOR ('20170602') DROP PARTITION FOR ('hangzhou');

パーティションテーブルの削除

DROP FOREIGN TABLE ステートメントを使用して、パーティションテーブルを削除します。

例：

DROP FOREIGN TABLE ossfdw_parttable;

OSS外部テーブルによるSLSデータへのアクセス

Log Service (SLS) から転送されたデータへのアクセスは、パーティション分割されたOSS外部テーブルの一般的なユースケースです。SLS が互換性のあるディレクトリ構造を使用して OSS にデータを書き込む場合、そのデータをクエリするためにパーティションテーブルを定義できます。

Log Service (SLS) の詳細については、「Log Service とは」をご参照ください。

1. OSS転送タスクの作成 (旧バージョン)

   [OSS LogShipper] パネルで転送タスクを設定する際、Partition format を date=%Y%m/userlogin に設定することを推奨します。生成される OSS ディレクトリ構造の例を次に示します。

   oss://testBucketName/adbpgossfdw
   ├── date=202002
   │ ├── userlogin_158561762910654****_647504382.csv
   │ └── userlogin_158561784923220****_647507440.csv
   └── date=202003
       └── userlogin_158561794424704****_647508762.csv

2. SLS からのログデータと一致するスキーマでパーティション分割されたOSS外部テーブルを作成します。

   CREATE FOREIGN TABLE userlogin (
           uid integer,
           name character varying,
           source integer,
           logindate timestamp without time zone,
           "date" int
   ) SERVER oss_serv OPTIONS (
       dir 'adbpgossfdw/',
       format 'text'
   )
   PARTITION BY LIST ("date")
   (
           VALUES ('202002'), 
           VALUES ('202003') 
   )

3. ログデータに対するクエリの実行計画を表示します。

   たとえば、2020 年 2 月のユーザーログイン数を分析するには、次のステートメントを実行します。

   EXPLAIN SELECT uid, count(uid) FROM userlogin WHERE "date" = 202002 GROUP BY uid;

   次の出力が返されます。

    QUERY PLAN 
   -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Gather Motion 3:1 (slice2; segments: 3) (cost=5135.10..5145.10 rows=1000 width=12)
      -> HashAggregate (cost=5135.10..5145.10 rows=334 width=12)
            Group Key: userlogin_1_prt_1.uid
            -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=5100.10..5120.10 rows=334 width=12)
                  Hash Key: userlogin_1_prt_1.uid
                  -> HashAggregate (cost=5100.10..5100.10 rows=334 width=12)
                        Group Key: userlogin_1_prt_1.uid
                        -> Append (cost=0.00..100.10 rows=333334 width=4)
                              -> Foreign Scan on userlogin_1_prt_1 (cost=0.00..100.10 rows=333334 width=4)
                                    Filter: (date = 202002)
                                    Oss Url: endpoint=oss-cn-hangzhou-zmf-internal.aliyuncs.com bucket=adbpg-regress dir=adbpgossfdw/date=202002/ filetype=plain|text
                                    Oss Parallel (Max 4) Get: total 0 file(s) with 0 bytes byte(s).
    Optimizer: Postgres query optimizer
   (13 rows)

   実行計画は、クエリが OSS の date=202002 ディレクトリにあるオブジェクトのみをスキャンすることを示しています。スキャンするデータが少ないほど、クエリのパフォーマンスは大幅に向上します。