PgSQL最佳實踐 - 從 MaxCompute (ODPS) 遷移資料到 HybridDB

摘要： 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'='	')

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 '	' )

LOG ERRORS INTO error_track_table SEGMENT REJECT LIMIT 10;

關鍵參數：

• 1. location指定了所有和 oss相關的參數
• 2.檔案的格式需要和 ODPS的外表配對 FORMAT 'TEXT' (DELIMITER ' ' )
• 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

參考資料

1. PostgreSQL + OSS oss_fdw
2. HybridDB for PostgreSQL + OSS oss_ext
3. SLS 支援投遞 CSV 格式的資料到 OSS
4. 開源的 JAVA 資料格式化實現
5. ODPS 匯出資料到 OSS
6. ODPS 上如何存取OSS

相關產品：

1. 雲資料庫RDS