文档

使用OSS Foreign Table分区表

更新时间:

AnalyticDB PostgreSQL版OSS Foreign Table(简称OSS FDW)支持分区表功能,当分区列出现在查询语句的WHERE条件中时,可以有效减少从OSS上拉取的数据量,从而提升查询的执行效率。

注意事项

OSS FDW分区表功能对数据文件的目录结构有一定要求,OSS文件目录格式需要为oss://bucket/partcol1=partval1/partcol2=partval2/,其中partcol1partcol2为分区列,partval1partval2为该分区对应的分区列值。

例如,分区表需要按照year列划分一级分区,再按照month划分二级分区,其中某个一级分区为year=2022、二级分区为month=07,那么属于这个分区的数据文件必须要放在oss://bucket/year=2022/month=07的目录下。

创建OSS Server和创建OSS User Mapping

使用OSS FDW前您需要提前创建OSS Server以及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_partable

    • 为分区表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');

    分区架构变化如下:

    ossfdw_parttable_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的介绍,请参见什么是日志服务

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