953
技術社區[雲棲]
PgSQL · 最佳實踐 · 從 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
- HybridDB for PostgreSQL + OSS oss_ext
- SLS 支持投遞 CSV 格式的數據到 OSS
- 開源的 JAVA 數據格式化實現
- ODPS 導出數據到 OSS
- ODPS 上如何訪問OSS
最後更新：2017-06-26 17:02:27