如何使用OSSForeignTable分區表 - AnalyticDB

AnalyticDB PostgreSQL版OSS Foreign Table（簡稱OSS FDW）支援分區表功能，當分區列出現在查詢語句的WHERE條件中時，可以有效減少從OSS上拉取的資料量，從而提升查詢的執行效率。

注意事項

OSS FDW分區表功能對資料檔案的目錄結構有一定要求，OSS檔案目錄格式需要為oss://bucket/partcol1=partval1/partcol2=partval2/，其中partcol1、partcol2為分區列，partval1、partval2為該分區對應的分區列值。

例如，分區表需要按照year列劃分一級分區，再按照month劃分二級分區，其中某個一級分區為year=2022、二級分區為month=07，那麼屬於這個分區的資料檔案必須要放在oss://bucket/year=2022/month=07的目錄下。

建立OSS Server、OSS User Mapping和OSS FDW

使用OSS FDW前您需要提前建立OSS Server、OSS User Mapping和OSS FDW。

建立OSS Server的具體方法，請參見建立OSS Server。
建立OSS User Mapping的具體方法，請參見建立OSS User Mapping。
建立OSS FDW的具體方法，請參見建立OSS FDW。

建立分區表

您可以通過CREATE FOREIGN TABLE語句建立OSS FDW分區表。OSS FDW分區表建表文法與建立普通分區表時的文法完全一致。具體資訊，請參見表分區定義。

更多關於CREATE FOREIGN TABLE文法的介紹，請參見建立OSS FDW。

說明

目前OSS Foreign Table僅支援值（LIST）分區。

建立分區表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"欄位為分區鍵
(
    -- 如下兩個一級分區下面可以掛著不同的二級分區。
    VALUES('20181218')
    (
        VALUES('hangzhou'),
        VALUES('shanghai') 
    ),
    VALUES('20181219')
    (
        VALUES('nantong'),
        VALUES('anhui') 
    )    
);

調整分區外表結構

您可以通過ALTER TABLE語句調整已有分區外表的表結構。目前AnalyticDB PostgreSQL版OSS FDW支援增加分區和刪除原有分區的操作。

新增分區

新增一個一級分區
- 為分區表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 Foreign Table訪問SLS投遞資料

OSS FDW訪問SLS投遞資料是OSS FDW分區表的一種典型應用情境。SLS寫入資料到OSS時使用合格目錄結構，即可在定義外表時使用分區表。

更多關於Log ServiceSLS的介紹，請參見什麼是Log Service。

建立OSS投遞任務（舊版）。

在OSS投遞功能面板中配置投遞資訊時，分区格式建議設定為date=%Y%m/userlogin，產生的OSS目錄格式樣本如下：

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

根據SLS投遞的記錄檔的關鍵字段建立OSS FDW分區表，樣本語句如下：

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')       
)

查詢分析業務資料語句的執行計畫。

例如分析2022年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
                     ->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)

根據返回樣本可以看出，OSS FDW僅需要從OSS的date=202002目錄下拉取資料，需要拉取資料量的越少則查詢的執行效率越高。