PgSQL · 最佳实践 · 从 MaxCompute (ODPS) 迁移数据到 HybridDB

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: title: PgSQL · 最佳实践 · 从 ODPS 迁移数据到 HybridDB author: 义从 背景 最近,不少用户在尝试使用 HybridDB 的过程中,询问我们如何把之前在 ODPS 中的数据迁移到 HybridDB。

title: PgSQL · 最佳实践 · 从 ODPS 迁移数据到 HybridDB

author: 曾文旌(义从)

背景

最近,不少用户在尝试使用 HybridDB 的过程中,询问我们如何把之前在 ODPS 中的数据迁移到 HybridDB。今天就跟大家介绍一种效率较高的方法。

一:原理

ODPS 和 HybridDB 都是多数据节点组合成的集群架构,这样的架构如果要做到效率较高的数据吞吐,需要驱动数据节点主动推送数据。幸运的是 ODPS 和 HybridDB 都支持用该方式向 OSS 读写数据。于是解决方案来了。

要在 OSS 交换数据,约定数据格式是必要的。调查发现,ODPS 支持向 ODPS 写文本格式的数据(TEXT/CSV),HybridDB 也支持读取文本格式的数据。

二:解决方案

下面通过一个简单的 demo 描述整体方案

1. ODPS OSS 外表

首先,我们需要创建一个和 ODPS 数据表相同结构的外部表,用于打通和 OSS 的数据通道。

 CREATE external TABLE `demo_oss_ext` (
    id string COMMENT 'id',
    data1 string COMMENT 'data1',
    data2 string COMMENT 'data2'
  ) 
partitioned by (ds string)
STORED BY 'com.aliyun.odps.TextStorageHandler'
WITH SERDEPROPERTIES ('odps.text.option.delimiter'='\t')
LOCATION 'oss://id:key@endpoint/bucketname/oss_dir/';

关键参数:

  • 1. com.aliyun.odps.TextStorageHandler 定义了数据存储到 OSS 的数据格式

    • TextStorageHandler 由 JAVA 开发,是缺省的数据投递选择
    • 缺省的 TextStorageHandler 不支持完整的 TEXT/CSV 协议,如果要支持,则建议和开源的 JAVA CSV 格式实现对接。有开发工作量。
  • 2. TextStorageHandler 支持两个自定义参数

    • odps.text.option.delimiter 用于指定列分割符。
    • odps.text.option.use.quote 定义引用字符。
    • 对于 NULL 列值,默认是 N 且不可更改。
    • 不支持转义特殊字符,只能通过自定义 Handler 实现。
  • 3. LOCATION 指定投递到 OSS 上的具体账号和位置,包含了 id, key, endpoint, bucket, 和具体位置。

2. 把数据通过外表迁移到 OSS

使用下面的 SQL 把 ODPS 中的数据转移到 OSS

insert into demo_oss_ext select * from t_data;

注意:

  • 1. 该操作是并行的进行的,默认每 256MB 数据开启一个并发。

    • 可以通 set odps.sql.mapper.split.size=xxx; 改小值增大并发。
  • 2. ODPS 到 OSS 数据的转移会受到 OSS 流控的影响,理论上单个并发到 OSS 的网络带宽是 100MB/S。
  • 3. 如果想进一步提高带宽,需要联系 OSS 的相关同学放开限制。

3. HybridDB 外部表

HybridDB 外部表 oss_ext

CREATE READABLE EXTERNAL TABLE user_data_oss_ext (
    id int64,
    data1 text,
    data2 text
)
location('oss://endpoint 
    dir=data_oss_dir
    id=ossid 
    key=osskey 
    bucket=bucketname') 
FORMAT 'TEXT' (DELIMITER '\t' )
LOG ERRORS INTO error_track_table SEGMENT REJECT LIMIT 10;

关键参数:

  • 1. location 指定了所有和 oss 相关的参数
  • 2. 文件的格式需要和 ODPS 的外表匹配 FORMAT 'TEXT' (DELIMITER 't' )
  • 3. 设置跳过错误的行

    • 异构数据的迁移难免会碰到校验不过的数据,可能是特殊字符,也可能是不合法的编码。
    • LOG ERRORS INTO error_track_table 会把出错的数据写到一张表中。
    • SEGMENT REJECT LIMIT X 设置支持单个 SEGMENT 允许错误的行,也可以设置成允许错误的百分比。
  • 4. HybridDB 的导入也是并行的,并行程度和计算节点数一致。
  • 5. 导入 gzip 格式的 text/csv 数据会提高一倍以上的性能,但前提是 ODPS 支持输出压缩格式的文件。

4. HybridDB 列压缩本地表

CREATE TABLE t_ao(
    id int64,
    data1 text,
    data2 text
)
with (
APPENDONLY=true, COMPRESSTYPE=zlib,
 ,BLOCKSIZE=2097152,
ORIENTATION=COLUMN,CHECKSUM=true,
OIDS=false) 
DISTRIBUTED BY (id);

关键参数:

  • 1. 如果导入到 HybridDB 不需要大量的修改数据,那么使用 append only 以列组织再加上压缩是最好的方案

    • 对应下列几个参数 APPENDONLY=true COMPRESSTYPE=zlib COMPRESSLEVEL=5 ORIENTATION=COLUMN BLOCKSIZE=2097152
    • HybridDB 是按列组织的压缩,压缩比会比按行压缩高很多,COMPRESSLEVEL=5 压缩比很容易到之前的 20%。
  • 2. DISTRIBUTED BY (column) 用于把数据均匀打散到 HybridDB 的各计算节点,数据尽量的均匀分布是选择分布列的关键。

5. 从 OSS 导入数据到 HybridDB

使用下面的 SQL 把 OSS 中的数据导入到 HybridDB

insert into t_ao select * from user_data_oss_ext;

HybridDB 和 PostgreSQL 都支持从 OSS 读写数据

OSS 和 AWS 的 S3 一致,是云上廉价的存储服务,它打通了几乎所有的云产品。我们推荐的云上数据通道。

目前,云上的 PostgreSQL 和 HybridDB 都支持 OSS 数据源的读写。

  • PostgreSQL + OSS 读写外部数据源 [oss_fdw] [5]
  • HybridDB for PostgreSQL + OSS 并行的导入导出数据 [oss_ext] [3]

参考资料

  1. PostgreSQL + OSS oss_fdw
  2. HybridDB for PostgreSQL + OSS oss_ext
  3. SLS 支持投递 CSV 格式的数据到 OSS
  4. 开源的 JAVA 数据格式化实现
  5. ODPS 导出数据到 OSS
  6. ODPS 上如何访问OSS
相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
10天前
|
分布式计算 DataWorks IDE
MaxCompute数据问题之忽略脏数据如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
23 0
|
10天前
|
SQL 存储 分布式计算
MaxCompute问题之下载数据如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
26 0
|
10天前
|
分布式计算 关系型数据库 MySQL
MaxCompute问题之数据归属分区如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
21 0
|
10天前
|
分布式计算 DataWorks BI
MaxCompute数据问题之运行报错如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
22 1
|
10天前
|
分布式计算 Cloud Native MaxCompute
MaxCompute数据问题之没有访问权限如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
21 0
|
10天前
|
JSON 分布式计算 MaxCompute
MaxCompute问题之创建数据集失败如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
16 0
|
10天前
|
存储 分布式计算 DataWorks
MaxCompute数据问题之数据不一致如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
22 0
|
10天前
|
存储 分布式计算 DataWorks
MaxCompute数据之数据不一致如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
16 0
|
10天前
|
SQL 分布式计算 DataWorks
MaxCompute数据之禁止数据流出如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
23 0
|
10天前
|
分布式计算 关系型数据库 MySQL
MaxCompute数据问题之创建数据集失败如何解决
MaxCompute数据包含存储在MaxCompute服务中的表、分区以及其他数据结构;本合集将提供MaxCompute数据的管理和优化指南,以及数据操作中的常见问题和解决策略。
22 2

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB