AI 助理
文档备案控制台
官方文档
输入文档关键字查找
首页

使用OSS Foreign Table分区表

更新时间：
复制为 MD 格式
我的收藏

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。

创建分区表

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

上一篇：使用OSS Foreign Table进行数据湖分析下一篇：AnalyticDB PostgreSQL 7.0版使用分区外表