：使用OSS Foreign Table分區表

注意事項

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 Server以及OSS User Mapping。

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

建立分區表

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

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

• 建立分區表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。

1. 建立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

2. 根據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')       
   )

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

   例如分析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目錄下拉取資料，需要拉取資料量的越少則查詢的執行效率越高。