如何使用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 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。

说明

目前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时使用符合条件的目录结构，即可在定义外表时使用分区表。

更多关于日志服务SLS的介绍，请参见什么是日志服务。

创建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目录下拉取数据，需要拉取数据量的越少则查询的执行效率越高。