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

テーブル分割機能は、Object Storage Service (OSS) 外部テーブルでサポートされています。クエリステートメントのWHERE句にパーティション列が含まれている場合、OSSからスキャンするデータを少なくすることでクエリ効率を向上させることができます。

注意事項

OSS外部テーブルのパーティション分割機能を使用するには、オブジェクトディレクトリの形式がoss:// bucket/partcol1=partval1/partcol2=partval2/ であることを確認します。このディレクトリでは、partcol1とpartcol2はパーティション列を示し、partval1とpartval2は前のパーティション列の値を示します。

たとえば、テーブルが年列に基づいてパーティション分割され、月列に基づいてサブパーティション分割されているとします。 year列の値が2022で、month列の値が07の場合、関連付けられたパーティションはoss:// bucket/year=2022/month=07ディレクトリに格納されます。

OSSサーバー、OSSサーバーへのユーザーマッピング、およびOSS外部テーブルの作成

OSS外部テーブルを使用する前に、OSSサーバー、OSSサーバーへのユーザーマッピング、およびOSS外部テーブルを作成する必要があります。

OSSサーバーの作成方法については、「データレイク分析のためのOSS外部テーブルの使用」トピックの「OSSサーバーの作成」セクションをご参照ください。
OSSサーバーへのユーザーマッピングの作成方法については、「データレイク分析にOSS外部テーブルを使用する」トピックの「OSSサーバーへのユーザーマッピングの作成」をご参照ください。
OSS外部テーブルの作成方法については、「データレイク分析にOSS外部テーブルを使用する」トピックの「OSS外部テーブルの作成」セクションをご参照ください。

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

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

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

説明

OSS外部テーブルはリストパーティションのみをサポートします。

ossfdw_parttableという名前のパーティションテーブルを作成し、パーティションテンプレートを指定します。例：

CREATE FOREIGN TABLE ossfdw_parttable(            
  key text,
  value bigint,
  pt text,                                        -- Define a partition key.
  region text                                     -- Define a subpartition key.
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt)                            -- Use the pt field to partition the table.
SUBPARTITION BY LIST (region)                     -- Use the region field to subpartition the table.
    SUBPARTITION TEMPLATE (                       -- Specify a 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,                                        -- Define a partition key.
  region text                                     -- Define a subpartition key.
) 
SERVER oss_serv
OPTIONS (dir 'PartationDataDirInOss/', format 'jsonline')
PARTITION BY LIST (pt)                            -- Use the pt field to partition the table.
SUBPARTITION BY LIST (region)                     -- Use the region field to subpartition the table.
(
    -- The following partitions contain different subpartitions: 
    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外部テーブルを使用してlog Serviceによって出荷されたログデータにアクセスする

log Serviceによって出荷されたログデータにアクセスするときに、パーティション化されたOSS外部テーブルを使用できます。 Log ServiceからOSSにデータを書き込むときに適切なオブジェクトパスを使用する場合は、パーティション化されたOSS外部テーブルを作成できます。

Log Serviceの詳細については、Simple Log Serviceとは

Simple log ServiceからOSSにログデータを送信します。

OSS LogShipperパネルで、Shard 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

log Serviceによって出荷されたログデータのキーフィールドに基づいて、パーティション化された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')       
)

log Serviceによって出荷されたログデータに対するSELECTステートメントの実行計画を照会および分析します。

たとえば、2月2022日のユーザーログオンの総数を照会するとします。

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
                     ->t;  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)

返された情報は、date=202002ディレクトリ内のデータのみをスキャンする必要があることを示しています。スキャンされたデータが少ないほど、クエリ効率が高くなります。