PgSQL · 最佳实践 · 从 MaxCompute (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=2097152HybridDB 是按列组织的压缩，压缩比会比按行压缩高很多，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]

参考资料

PostgreSQL + OSS oss_fdwHybridDB for PostgreSQL + OSS oss_extSLS 支持投递 CSV 格式的数据到 OSS开源的 JAVA 数据格式化实现ODPS 导出数据到 OSS ODPS 上如何访问OSS