727      魔獸

PostgreSQLWriter__Writer插件_使用手冊_數據集成-阿裏雲

1 快速介紹

PostgreSQLWriter 插件實現了寫入數據到 PostgreSQL 主庫的目的表的功能。在底層實現上， PostgreSQLReader 通過 JDBC 連接遠程 PostgreSQL 數據庫，並執行相應的 insert into ...的 sql 語句將數據寫入 PostgreSQL，內部會分批次提交入庫。

PostgreSQLWriter 麵向ETL開發工程師，他們使用 PostgreSQLWriter 從數倉導入數據到 PostgreSQL。同時 PostgreSQLWriter 亦可以作為數據遷移工具為DBA等用戶提供服務。

2 實現原理

PostgreSQLWriter 通過 CDP 框架獲取 Reader 生成的協議數據，生成 insert into...(當主鍵/唯一性索引衝突時會寫不進去衝突的行)，性能考慮，采用了 PreparedStatement + Batch，並且設置了：rewriteBatchedStatements=true，將數據緩衝到線程上下文 Buffer 中，當 Buffer 累計到預定閾值時，才發起寫入請求。

注意：目的表所在數據庫必須是主庫才能寫入數據；整個任務至少需要具備 insert into...的權限，是否需要其他權限，取決於你任務配置中在 preSql 和 postSql 中指定的語句。

3 功能說明

3.1 配置樣例

• 使用RDS的Instance配置一個寫入PostgreSQL的作業。

{
    "type": "job",
    "traceId": "您可以在這裏填寫您作業的追蹤ID，建議使用業務名+您的作業ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "sqlserver",
            "parameter": {
                "instanceName": "rds.aliyun.com",
                "username": "username",
                "password": "password",
                "table": "table",
                "column": [
                    "*"
                ],
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "postgresql",
            "parameter": {
                "instanceName": "instanceName",
                "database": "database",
                "username": "username",
                "password": "password",
                "table": "table",
                "column": [
                    "*"
                ],
                "preSql": [
                    "delete from XXX;"
                ]
            }
        }
    }
}

• 使用JDBC配置一個向RDS寫入數據的作業

{
    "type": "job",
    "traceId": "您可以在這裏填寫您作業的追蹤ID，建議使用業務名+您的作業ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "sqlserver",
            "parameter": {
                "instanceName": "rds.aliyun.com",
                "username": "username",
                "password": "password",
                "table": "table",
                "column": [
                    "*"
                ],
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "PostgreSQL",
            "parameter": {
                "jdbcUrl": "jdbc:postgresql://ip:port/database",
                "username": "username",
                "password": "password",
                "table": "table",
                "column": [
                    "*"
                ],
                "preSql": [
                    "delete from XXX;"
                ]
            }
        }
    }
}

3.2 參數說明

• instanceName

  • 描述: 阿裏雲RDS實例名稱(Instance名稱)。用戶使用該配置指定RDS的Instance名稱，CDP將翻譯為底層執行的jdbc連接串連接。

    instanceName指定的是RDS實例，類似PostgreSQL實例，需要和database配合使用。

  • 必選：是

  • 默認值：無

• jdbcUrl

  • 描述：對於CDP部分私有雲場景，數據庫遷移場景等，其本身數據源是普通PostgreSQL數據庫不是RDS，對於這類場景，用戶可以指定jdbc信息直連。

    jdbcUrl和instanceName/database兩類信息概念上是等同的，因此隻能配置其一。如果兩者均配置，CDP默認將使用jdbc信息。

  • 必選：是

  • 默認值：無

• database

  • 描述: 阿裏雲RDS數據庫名稱。
  • 必選：是
  • 默認值：無

• username

  • 描述：數據源的用戶名
  • 必選：是
  • 默認值：無

• password

  • 描述：數據源指定用戶名的密碼
  • 必選：是
  • 默認值：無

• table

  • 描述：所選取的需要同步的表。
  • 必選：是
  • 默認值：無

• column

  • 描述：所配置的表中需要同步的列名集合。以英文逗號（,）進行分隔。我們強烈不推薦用戶使用默認列情況
  • 必選：是
  • 默認值：無

• preSql

  • 描述：執行數據同步任務之前率先執行的sql語句，目前隻允許執行一條SQL語句，例如清除舊數據。
  • 必選：否
  • 默認值：無

• postSql

  • 描述：執行數據同步任務之後執行的sql語句，目前隻允許執行一條SQL語句，例如加上某一個時間戳。
  • 必選：否
  • 默認值：無

• batchSize

  • 描述：一次性批量提交的記錄數大小，該值可以極大減少CDP與PostgreSQL的網絡交互次數，並提升整體吞吐量。但是該值設置過大可能會造成CDP運行進程OOM情況。
  • 必選：否
  • 默認值：512

3.3 類型轉換

目前 PostgresqlWriter支持大部分 PostgreSQL類型，但也存在部分沒有支持的情況，請注意檢查你的類型。

下麵列出 PostgresqlWriter針對 PostgreSQL類型轉換列表:

4 性能報告

4.1 環境準備

4.1.1 數據特征

建表語句：

 create table pref_test(
 id serial, 
 a_bigint bigint, 
 a_bit bit(10), 
 a_boolean boolean, 
 a_char character(5), 
 a_date date, 
 a_double double precision, 
 a_integer integer, 
 a_money money, 
 a_num numeric(10,2),
 a_real real, 
 a_smallint smallint, 
 a_text text, 
 a_time time, 
 a_timestamp timestamp
)

4.1.2 機器參數

• 執行CDP的機器參數為:

  1. cpu: 16核 Intel(R) Xeon(R) CPU E5620  @ 2.40GHz
  2. mem: MemTotal: 24676836kB    MemFree: 6365080kB
  3. net: 百兆雙網卡
  

• PostgreSQL數據庫機器參數為:

  D12 24邏輯核  192G內存 12*480G SSD 陣列
  

4.2 測試報告

4.2.1 單表測試報告

4.2.2 性能測試小結

1. channel數對性能影響很大
2. 通常不建議寫入數據庫時，通道個數 > 32

5 FAQ

Q: PostgreSQLWriter 執行 postSql 語句報錯，那麼數據導入到目標數據庫了嗎?

A: CDP 導入過程存在三塊邏輯，pre 操作、導入操作、post 操作，其中任意一環報錯，CDP 作業報錯。由於 CDP 不能保證在同一個事務完成上述幾個操作，因此有可能數據已經落入到目標端。

Q: 按照上述說法，那麼有部分髒數據導入數據庫，如果影響到線上數據庫怎麼辦?

A: 目前有兩種解法，第一種配置 pre 語句，該 sql 可以清理當天導入數據， CDP 每次導入時候可以把上次清理幹淨並導入完整數據。第二種，向臨時表導入數據，完成後再 rename 到線上表。

Q: 上麵第二種方法可以避免對線上數據造成影響，那我具體怎樣操作?

A: 可以配置臨時表導入

最後更新：2016-11-23 16:03:59

  上一篇： SQLServerWriter__Writer插件_使用手冊_數據集成-阿裏雲

  下一篇： ODPSWriter__Writer插件_使用手冊_數據集成-阿裏雲