AnalyticDB PostgreSQL基于DMS数据ETL链路开发

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: PostgreSQL数据库目前被广泛应用于企业的在线业务,这款数据库以其高度的稳定性和完善的产品能力被业界高度赞誉和广泛接受。 本文介绍了两款PostgreSQL引擎的数据库是如何完成一套标准的数据链路同步,开发并让企业可以同时享受PostgreSQL在OLTP & OLAP的场景下的全面能力。

PostgreSQL数据库目前被广泛应用于企业的在线业务,这款数据库以其高度的稳定性和完善的产品能力被业界高度赞誉和广泛接受。 本文介绍了两款PostgreSQL引擎的数据库是如何完成一套标准的数据链路同步,开发并让企业可以同时享受PostgreSQL在OLTP & OLAP的场景下的全面能力。

背景

PostgreSQL数据库目前被广泛应用于企业的在线业务,这款数据库以其高度的稳定性和完善的产品能力被业界高度赞誉和广泛接受。 本文介绍了两款PostgreSQL引擎的数据库是如何完成一套标准的数据链路同步,开发并让企业可以同时享受PostgreSQL在OLTP & OLAP的场景下的全面能力。

本文使用了dms来实现了整个链路的调度, 使用了OSS介质作为中间态的存储,将数据从PostgreSQL 加载到OSS后,落入ADB PG Serverless版本中,实现T+1的数据分析。

优势:

  1. 数据基于OSS低成本存储实现归档,永不删除

  2. 数据从RDS(可以是PG也可以是MySQL),T+1加载到ADB PG Serverless版本中做高性能分析

  3. 基于DMS配置自动调度框架,白屏化,低代码。

本文案例的相关约束:RDS源表中的数据,需要能够通过条件来增量归档。下面的例子是通过t_src表中的c date列,实现按天归档。

方案框架:

image.png

step by step流程 (大约20~30分钟可完成)

资源准备

注意:本方案对 RDS 的pg14版本暂不支持; 支持pg9.4 到13的版本;

  1. 需要一台开通的rds pg; (确认您的版本在pg9~pg13之间):

  2. 开通adb pg serverless版本,若无adbpg,可点击购买; 在创建好实例之后,请先进行初始设置,需要设置实例账号;

image.png
  1. 开通OSS,并建好对应的bucket,有对应的access key,secret可以。

    1. 若您没有OSS,可免费开启该服务: https://www.aliyun.com/product/oss

    2. OSS服务已开启,可登录OSS控制台 : https://oss.console.aliyun.com/bucket

    3. 需要创建一个的OSS bucket,需要保证该bucket和RDS PG 和 ADBPG在同一个region内;

image.png

  1. 建立bucket以后: 点击进入bucket,记录你的bucket信息;包括 bucket_host, bucket_name,获取方式如下:

image.png

  1. 怎么获取access_key 和 secret_key,在控制台右上角点击自己的头像,去到AccessKey管理

进入后可查看secret,记录好自己的两个关键key;

image.png

执行流程

第一步:数据和服务准备

理论上用户在RDS PG上已经存在着业务数据,本文为了方便理解,创建了样本数据来供测试流程使用;

  1. 登录rds pg,进入选择数据库,进入SQL编辑页面, 创建oss_fdw插件。

  • 通过dms登录rds pg,运行SQL:

create extension if not exists oss_fdw; 
  • 创建一个表作为源表,运行SQL:

   
    create table t_src (a int, b int, c date);
    insert into t_src select generate_series(1, 1000), 1, now();
    select * from t_src;
  • 为RDS PG创建一个外表写入

-- 导入外表插件
create extension if not exists oss_fdw;  

-- 创建外表服务;
create  SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host '[bucket_host]' , id '[access_key]', key '[secret_key]',bucket '[bucket_name]');

  1. 开通adb pg serverless版本,进入数据库, 在SQL编辑页面进行创建oss_fdw插件,创建目标表,并配置oss 外表。登录adb pg,运行SQL:

--添加外表访问插件
create extension if not exists oss_fdw;  

-- 和rdspg侧保持表的定义相同,创建一张表; 注意serverless版本暂不支持主键;
create table t_target (a int, b int, c date);
-- 导入外表访问插件
create extension if not exists oss_fdw;  

-- 创建外表访问链接服务
create server oss_serv
    foreign data wrapper oss_fdw
    options (
        endpoint '[bucket_host]',
        bucket '[bucket_name]');

create user mapping for public
    server oss_serv
    options (
      id '[access_key]',
      key '[secret_key]');

  1. 接下来,我们会通过dms配置自动归档和加载任务流;

第二步: 配置ETL任务;

RDS PG 抽取任务

  1. 新建任务编排。 在DMS页面上,集成与开发(DTS)”-> 任务编排->新建任务流 , 设置任务流名称“rdspg数据导入OSS

image.png
  1. 新建RDS PG归档流程: 选择新建任务流,数据加工 -> 单实例SQL,拖入面板

image.png
  • 命名该实例,点击image.png,改名为’rds数据抽取‘

  • 点击实例设置按钮image.png进入配置,并将如下SQL贴入,此段为将数据从RDS PG数据写入OSS存储介质中;

drop FOREIGN TABLE if exists oss_${mydate};

CREATE FOREIGN TABLE if not exists oss_${mydate}
    (a int,
     b int,
     c date) 
     SERVER ossserver 
     OPTIONS ( dir 'rds/t3/${mydate}/', delimiter '|' ,
         format 'csv', encoding 'utf8');
         

insert into oss_${mydate} select * from t_src where c >= '${mydate}';

image.png
  1. 填入该任务关联的数据库名称; (注意搜索方法为,回到SQL窗口,切换到需要的数据库,在头部有数据库对应的已public开头的库名,点击右侧进行复制; )

image.png
  1. 返回任务配置页,将刚刚复制的库名放入搜索框后,选中结果

image.png

  1. 点击“变量设置”一栏,配置日期变量mydate, 时间格式变为 yyyyMMdd; 注意:这个变量在后面的SQL中要用到,用来根据日期做增量归档。

image.png

配置ADB PG的加载任务

  1. 新建ADB PG加载任务,数据加供 -> 单实例SQL,将其拖拽至面板,改名为“ADBPG数据加载”

image.png
  1. 点击内容设置image.png,首先选取对应的ADBPG数据库实例(方法如上)

image.png
  1. 编辑对应SQL,在ADBPG侧进行数据从OSS抽取

CREATE FOREIGN TABLE if not EXISTS  oss_${mydate}(  
     a int ,
     b int ,
     c date
) server oss_serv
    options (
        dir 'rds/t3/${mydate}/',
        format 'csv',
        delimiter '|',
        encoding 'utf8');


insert into t_target select * from oss_${mydate};
  1. 配置日期变量mydate,作用同上。

image.png

配置任务调度和执行时间配置

  1. 配置调度流程,先跑RDS PG任务,再跑ADB PG,选中RDS PG框下面圆点,拖到ADB PG上。

image.png
  1. 找到调度配置,开启调度, 并配置调度策略。配置调度页。选择你希望的任务调度周期,每次调度会对数据进行rdspg侧的导出和adbpg的导入,保证数据的同步;

image.png image.png

测试和发布

点击“试运行”,测试无误后,点击“发布”

总结

该产品能力支持用户快速完成一套免费的数据ETL链路,支持用户将现有的RDS PG的数据落入OSS中,并加载至ADBPG中进行分析; 也可以落在OSS上后,以外表的形式进行联邦分析;

用法: 对于海量分区数据的分析,在ADBPG中有明显的加速效果; 对于TB~PB级的数据,可做到秒级查询,汇总并对接主流的BI产品,目前AnalyticDB PostgreSQL被广泛应用在企业的数字化转型,实现云端数据平台部署;

参考文档

【1】dms案例,https://www.alibabacloud.com/help/zh/data-management-service/latest/regularly-back-up-data-from-relational-databases-to-oss-in-dms

【2】rds pg外表使用说明 https://help.aliyun.com/document_detail/164815.html

【3】adb pg外表使用说明 https://help.aliyun.com/document_detail/164815.html

相关文章
|
15天前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
402 1
|
10月前
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
815 1
|
15天前
|
SQL 运维 关系型数据库
PolarDB产品使用合集之PolarDB 2.3.0 版本的 CDC 功能支持 Polardb-X 到 Polardb-X 的数据同步吗
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
8月前
|
存储 SQL 关系型数据库
AnalyticDB PostgreSQL构建一站式实时数仓实践
本文介绍通过 AnalyticDB PostgreSQL 版基于实时物化视图,构建流批一体的一站式实时数仓解决方案,实现一套系统、一份数据、一次写入,即可在数仓内完成实时数据源头导入到实时分析全流程。
1914 5
AnalyticDB PostgreSQL构建一站式实时数仓实践
|
10月前
|
存储 SQL 自然语言处理
如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
本文从阿里云用户使用云原生数据仓库AnalyticDB PostgreSQL版(以下简称ADB PG)的实际体验出发,介绍ADB PG如何实现“一站式全文检索”业务,并详细阐述ADB PG使用的优势技术,最后提供对应业务案例分析。
32173 33
|
15天前
|
Cloud Native 关系型数据库 OLAP
从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发
在传统数仓中,往往采用资源预购的方式,缺少面向业务的资源调整灵活性。 在数据分析这种存在明显业务波峰波谷或分时请求的场景下,实例无法按需使用,造成了大量成本浪费。云原生数仓AnalyticDB PostgreSQL产品自2022年2月正式发布了Serverless版之后,依托于内核强大的资源管理能力...
|
存储 关系型数据库 数据管理
高性价比的数据归档解决方案(DMS + AnalyitcDB PostgreSQL)
发布全新数据归档方案,依托DMS + AnalyticDB PostgreSQL Serverless版本,帮助客户用低价格实现海量数据的持久化,还可以对归档数据进行完善管理、高效寻回、查看并进行分析
高性价比的数据归档解决方案(DMS + AnalyitcDB PostgreSQL)
|
存储 关系型数据库 数据管理
【最佳实践】高性价比的数据归档解决方案(DMS + AnalyticDB PostgreSQL)
发布全新数据归档方案,依托DMS + AnalyticDB PostgreSQL Serverless版本,帮助客户用低价格实现海量数据的持久化,还可以对归档数据进行完善管理、高效寻回、查看并进行分析
【最佳实践】高性价比的数据归档解决方案(DMS + AnalyticDB PostgreSQL)
|
SQL 关系型数据库 Serverless
【实操系列】 从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发
本文以RDSPG 到 ADBPG 的数据链路作为案例,介绍了如何从0~1,基于DMS进行ETL数据链路开发
【实操系列】 从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发
|
SQL 数据采集 关系型数据库
基于AnalyticDB PostgreSQL + OSS + SLS构建面向应用内行为数据的分析全链路
AnalyticDB PostgreSQL助力某互联网企业完成数仓建设和面向用户行为的全链路分析。通过Serverless版本的性能助力,轻松实现了10+的性价比提升。
532 0
基于AnalyticDB PostgreSQL + OSS + SLS构建面向应用内行为数据的分析全链路