【实操系列】 从0~1,基于DMS面向AnalyticDB PostgreSQL的数据ETL链路开发

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 本文以RDSPG 到 ADBPG 的数据链路作为案例,介绍了如何从0~1,基于DMS进行ETL数据链路开发


背景

PostgreSQL数据库目前被广泛应用于企业的在线业务,这款数据库也被业界誉为“最先进的开源数据库”。 本文介绍了AnalyticDB PostgreSQL如何能够使用DMS进行业务调度,完成面向RDS PG的实现定时调度的数据EL链路,开发并让企业可以同时享受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配置自动调度框架,白屏化,低代码。


约束:

  1. RDS源表中的数据,需要能够通过条件来增量归档。下面的例子是通过t_src表中的c date列,实现按天归档。


方案框架:


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

资源准备

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

  1. 已有一台开通的rds pg; (确认您的版本在pg9~pg13之间)若无RDS PG,可购买创建
  2. 开通adb pg serverless版本,(疫情期间,我们提供了疫情礼包,包含计算引擎); 在创建好实例之后,请先进行初始设置,需要设置实例账号;
  3. 开通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内;


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

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

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


执行流程


第一步:数据和服务准备

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

  1. 登录rds pg,进入选择数据库,进入SQL编辑页面, 创建oss_fdw插件。
  1. 通过dms登录rds pg,运行SQL: create extension if not exists oss_fdw;  
  2. 创建一个表作为源表,运行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;


  1. 为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任务;

1. RDS PG 抽取任务配置

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

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

  1. 命名该实例,点击,改名为’rds数据抽取‘
  2. 点击实例设置按钮进入配置,并将如下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}';


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

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



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

2. 配置ADB PG的加载任务

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

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

  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,作用同上。

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

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

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

4. 测试和发布

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

总结

该产品能力支持用户快速完成一套免费的数据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

相关实践学习
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
10天前
|
数据采集 存储 安全
数据治理≠数据管理!90%的企业都搞错了重点!
在数字化转型中,数据不一致、质量差、安全隐患等问题困扰企业。许多组织跳过基础的数据管理,直接进行数据治理,导致方案难以落地。数据管理涵盖数据生命周期中的采集、存储、处理等关键环节,决定了数据是否可用、可靠。本文详解数据管理的四大核心模块——数据质量、元数据、主数据与数据安全,并提供构建数据管理体系的四个阶段:评估现状、确定优先级、建立基础能力与持续改进,助力企业夯实数据基础,推动治理落地。
|
2月前
|
运维 算法 机器人
阿里云AnalyticDB具身智能方案:破解机器人仿真数据、算力与运维之困
本文将介绍阿里云瑶池旗下的云原生数据仓库AnalyticDB MySQL推出的全托管云上仿真解决方案,方案采用云原生架构,为开发者提供从开发环境、仿真计算到数据管理的全链路支持。
|
4月前
|
存储 数据管理 数据格式
数据治理 vs. 数据管理:别再傻傻分不清!
数据治理 vs. 数据管理:别再傻傻分不清!
247 10
|
23天前
|
数据采集 存储 SQL
数据管理四部曲:元数据管理、数据整合、数据治理、数据质量管控
老张带你搞定企业数据管理难题!数据找不到、看不懂、用不好?关键在于打好元数据管理、数据整合、数据治理和数据质量管控四大基础。四部曲环环相扣,助你打通数据孤岛,提升数据价值,实现精准决策与业务增长。
数据管理四部曲:元数据管理、数据整合、数据治理、数据质量管控
|
2月前
|
数据采集 人工智能 监控
企业数据来源杂、质量差,如何通过主数据管理解决?如何确保数据可信、一致和可用?
本文三桥君系统介绍了主数据管理(MDM)在企业数字化转型中的关键作用。产品专家三桥君从数据清洗、治理、处理到流转四个维度,详细阐述了如何通过标准化流程将数据转化为企业核心资产。重点包括:数据清洗的方法与工具应用;数据治理的组织保障与制度设计;数据处理的三大核心动作;以及数据流转的三种模式与安全控制。专家三桥君强调主数据管理能够推动企业从"经验决策"转向"数据驱动",并提出构建统一数据服务网关、"数据血缘图谱"等实战建议,为企业数字化转型提供系统化解决方案。
113 0
|
4月前
|
SQL 存储 OLAP
数据外置提速革命:轻量级开源SPL如何用文件存储实现MPP级性能?
传统交易型数据库在分析计算中常遇性能瓶颈,将数据迁至OLAP数据仓库虽可缓解,但成本高、架构复杂。SPL通过轻量级列存文件存储历史数据,提供强大计算能力,大幅简化架构并提升性能。它优化了列式存储、数据压缩与多线程并行处理,在常规及复杂计算场景中均表现优异,甚至单机性能超越集群。实际案例中,SPL在250亿行数据的时空碰撞问题上,仅用6分钟完成ClickHouse集群30分钟的任务。
数据外置提速革命:轻量级开源SPL如何用文件存储实现MPP级性能?
|
6月前
|
SQL 分布式计算 数据挖掘
从湖仓分离到湖仓一体,四川航空基于 SelectDB 的多源数据联邦分析实践
川航选择引入 SelectDB 建设湖仓一体大数据分析引擎,取得了数据导入效率提升 3-6 倍,查询分析性能提升 10-18 倍、实时性提升至 5 秒内等收益。
462 63
从湖仓分离到湖仓一体,四川航空基于 SelectDB 的多源数据联邦分析实践
|
7月前
|
存储 数据采集 人工智能
AllData数据中台架构全览:数据时代的智慧中枢
杭州奥零数据科技有限公司成立于2023年,专注于数据中台业务,维护开源项目AllData并提供商业版解决方案。AllData提供数据集成、存储、开发、治理及BI展示等一站式服务,支持AI大模型应用,助力企业高效利用数据价值。
AllData数据中台架构全览:数据时代的智慧中枢
|
7月前
|
存储 SQL 数据挖掘
数据无界、湖仓无界, Apache Doris 湖仓一体解决方案全面解读(上篇)
湖仓一体架构融合了数据湖的低成本、高扩展性,以及数据仓库的高性能、强数据治理能力,高效应对大数据时代的挑战。为助力企业实现湖仓一体的建设,Apache Doris 提出了数据无界和湖仓无界核心理念,并结合自身特性,助力企业加速从 0 到 1 构建湖仓体系,降低转型过程中的风险和成本。本文将对湖仓一体演进及 Apache Doris 湖仓一体方案进行介绍。
590 1
数据无界、湖仓无界, Apache Doris 湖仓一体解决方案全面解读(上篇)

热门文章

最新文章

相关产品

  • 云原生数据仓库 AnalyticDB PostgreSQL版
  • 云数据库 RDS PostgreSQL 版
  • 推荐镜像

    更多