PostgreSQL的學習心得和知識總結（二十）|阿里開源ETL工具dataX的使用全解

孤傲小二~阿沐發表於2020-11-09
SQL阿里

目錄結構

阿里開源ETL工具dataX的使用

文章快速說明索引

學習目標：

熟練掌握阿里開源ETL工具dataX的使用

注：這個工具功能十分強大，是支援多種對多種的異構資料庫間的離線資料同步工具。全部原始碼由Java、Python和Shell完成。

學習內容：（詳見目錄）

1、dataX的簡介說明
2、dataX的安裝使用
3、增量同步的實現（一種思路）
4、dataX的原始碼解析（這個暫時不做，原因如下）

①、能力不行，我要去學習Java
②、工程量較大，需要安排大塊時間
③、篇幅太長，合適的時間另行寫一篇部落格詳解

學習時間：

2020年11月3日02:26:30

學習產出：

1、dataX的使用學習
2、CSDN 技術部落格 1篇

下面我們所有的學習環境是Centos7+PostgreSQL12.4/13+Oracle11g+MySQL5.7：

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)

postgres=#

#-----------------------------------------------------------------------------#

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

#-----------------------------------------------------------------------------#

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.06 sec)

mysql>

DataX 是什麼東東

在應用的使用場景下，我們常常需要的資料來源分佈在不同的業務系統中，而這些系統往往是異構的（不同的資料庫源），但是我們的分析過程不能影響原有業務系統的執行。於是本文詳解的阿里的開源ETL工具dataX就應運而生，它可以把這些不同的資料彙總（即：異構間資料同步、遷移和整合等）起來。

注：下面這一小塊內容來源於這位大佬的部落格：ETL講解（很詳細！！！），點選前往 !

而所謂的ETL指的是： Extract Transform Load。ETL是將業務系統的資料經過 抽取、清洗轉換之後載入 到資料倉儲的過程。其目的是將企業中的分散、零亂、標準不統一的資料整合到一起，為企業的決策提供分析依據。 ETL是BI專案重要的一個環節。 通常情況下，在BI專案中ETL會花掉整個專案至少1/3的時間,ETL設計的好壞直接關接到BI專案的成敗。

ETL的設計分三部分：資料抽取、資料的清洗轉換、資料的載入。在設計ETL的時候我們也是從這三部分出發。

1、資料的抽取是從各個不同的資料來源抽取到ODS(Operational Data Store，操作型資料儲存)中——這個過程也可以做一些資料的清洗和轉換)，在抽取的過程中需要挑選不同的抽取方法，儘可能的提高ETL的執行效率
2、ETL三個部分中，花費時間最長的是“T”(Transform，清洗、轉換)的部分，一般情況下這部分工作量是整個ETL的2/3
3、資料的載入一般在資料清洗完了之後直接寫入DW(Data Warehousing，資料倉儲)中去

ETL的實現有多種方法，最常用的有三種：

1、藉助ETL工具(如Oracle的OWB、SQL Server 2000的DTS、SQL Server2005的SSIS服務、Informatic等)實現
2、SQL方式實現
3、另外一種是ETL工具和SQL相結合。

這三種方法的優缺點如下表所示：

實現方法技術要求靈活程度
1可以快速建立起ETL工程，遮蔽了複雜的編碼任務，提高了速度，降低了難度較差
2編碼複雜，對技術要求比較高優異
3易於實現，效率很高優異

常見的ETL工具有：

1、Kattle 也是一個開源的ETL工具，優點是開源、資料豐富和功能強大。其git倉庫為：pentaho/pentaho-kettle，點選前往 和 中文網：免費開源的、視覺化的、功能強大的ETL工具，點選前往
2、DataStage，這是IBM為其配套的DB2開發的ETL工具，也可以用於其它資料庫資料的整合
3、Informatica，這是美國的一個資料整合公司的開發的資料整合工具，有圖形介面
4、sqoop，這個是hadoop生態裡的一個資料匯入工具，但是它依賴於hadoop環境

OK，下面就開始主要介紹我們的ETL工具DataX：其git倉庫為 alibaba/DataX，點選前往

官方的解釋說明為：DataX 是阿里巴巴集團內被廣泛使用的離線資料同步工具/平臺，實現包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各種 異構 資料來源之間高效的資料 同步 功能。

以外掛的形式支援主流的RDBMS資料庫、NOSQL、大資料計算系統都已經接入，目前支援資料如下圖（當然也是可以自行擴充套件新的源）所示：
在這裡插入圖片描述
本文我們主要介紹一下在Linux（centos7）下，幾種主流RDBMS間的資料同步使用（由於這是本人的學習環境的限制，我們主要探討：MySQL PostgreSQL 和Oracle這三種主流資料庫的資料同步）。而DataX本身作為資料同步框架（如下圖所示）：將不同資料來源的同步抽象為從源頭資料來源讀取資料的Reader外掛，以及向目標端寫入資料的Writer外掛，理論上DataX框架可以支援任意資料來源型別的資料同步工作。同時DataX外掛體系作為一套生態系統，每接入一套新資料來源該新加入的資料來源即可實現和現有的資料來源互通（而正是因為這一點：我們就可以自定義新的資料來源的Reader和Writer外掛，之後即可自由實現不同型別的讀寫結合）。
在這裡插入圖片描述
如上圖所示：這幾種主流資料庫源 在結構上都是由Reader和Writer對應而成，例如：postgresqlreader和postgresqlwriter。

1、Reader實現了從資料儲存系統批量抽取資料，並轉換為DataX標準資料交換協議，DataX任意Reader能與DataX任意Writer實現無縫對接，達到任意異構資料互通之目的
2、Writer實現了從DataX標準資料交換協議，翻譯為具體的資料儲存型別並寫入目的資料儲存。DataX任意Writer能與DataX任意Reader實現無縫對接，達到任意異構資料互通之目的

DataX 的安裝使用

DataX的實現由全Java編制，其System Requirements有如下所示：

1、Linux（最好是Linux，Windows也行）
2、JDK(1.6以上，推薦1.6)
3、Python(推薦Python2.6.X)
4、Apache Maven 3.x (Compile DataX)

如下是我的環境（centos 7）：

[uxdb@local64 ~]$ javac -version
javac 1.8.0_191
[uxdb@local64 ~]$ python -V
Python 2.7.5
[uxdb@local64 ~]$ mvn -version
Apache Maven 3.5.4 (1edded0938998edf8bf061f1ceb3cfdeccf443fe; 2018-06-18T02:33:14+08:00)
Maven home: /usr/local/maven/apache-maven-3.5.4
Java version: 1.8.0_161, vendor: Oracle Corporation, runtime: /usr/local/java/jdk1.8.0_161/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "3.10.0-693.el7.x86_64", arch: "amd64", family: "unix"
[uxdb@local64 ~]$

DataX的安裝是非常簡單的，有兩種方式：

1、安裝包解壓安裝
2、原始碼自行編譯

因為自行編譯的結果也是得到一個tar包，我們這裡首先選擇安裝包的方式。（下面，當我們定製DataX後 再使用原始碼編譯方式）
第一步：下載安裝包並解壓

wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz

第二步：測試安裝是否正常

[uxdb@local64 datax]$ ls
bin  conf  job  lib  log  log_perf  plugin  script  tmp
[uxdb@local64 datax]$ 
[uxdb@local64 datax]$ ls job/
job.json
[uxdb@local64 datax]$ 
[uxdb@local64 datax]$ cd bin/
[uxdb@local64 bin]$ 
[uxdb@local64 bin]$

測試結果如下，即為安裝成功！（job目錄下的job.json，是阿里為我們提供的這麼一個校驗案例）
在這裡插入圖片描述

第三步：異構資料庫的同步

我們這裡只提供關係型資料庫間的資料同步（Oracle、MySQL和PostgreSQL）的使用案例，注：道理上都是一樣的，可以舉一反三！

一、 Oracle 到 PostgreSQL 的同步

首先資料來源端和資料目的端準備如下：（目的是將Oracle的資料同步到PostgreSQL中）

Oracle端準備如下：

SQL> show user;
USER is "SONG"
SQL> create table test1 (id int,curtime timestamp);

Table created.

SQL> insert into test1 values(1,sysdate);

1 row created.

SQL> insert into test1 values(2,sysdate);

1 row created.

SQL> select * from test1;

	ID CURTIME
---------- ---------------------------------------------------------------------------
	 1 07-NOV-20 07.42.50.000000 PM
	 2 07-NOV-20 07.42.55.000000 PM

SQL> commit;

Commit complete.

SQL>

PostgreSQL端準備如下：

postgres=# create table test1 (id int,curtime timestamp);
CREATE TABLE
postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | test1 | table | uxdb
(1 row)

postgres=# select * from test1 ;
 id | curtime 
----+---------
(0 rows)

postgres=#

開始同步（Oracle到PostgreSQL的資料同步）：

第①步：建立同步所需的配置檔案（json格式）
使用方法為：

python datax.py -r {資料來源端的readername} -w {目的地端的writername}

# 我們這裡是Oracle 到 PostgreSQL，於是命令如下：
python datax.py -r oraclereader -w postgresqlwriter > myOra2Postgres.json

清理沒有用的資訊，得到模板配置檔案如下：

[uxdb@local64 bin]$ python datax.py -r oraclereader -w postgresqlwriter > myOra2Postgres.json
[uxdb@local64 bin]$ vim myOra2Postgres.json
[uxdb@local64 bin]$ cat myOra2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "oraclereader", 
                    "parameter": {
                        "column": [], 
                        "connection": [
                            {
                                "jdbcUrl": [], 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "username": ""
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": [], 
                        "connection": [
                            {
                                "jdbcUrl": "", 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "postSql": [], 
                        "preSql": [], 
                        "username": ""
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}
[uxdb@local64 bin]$

第②步：自行按需配置同步的配置json檔案

[uxdb@local64 bin]$ cat myOra2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "oraclereader", 
                    "parameter": {
                        "column": ["id","curtime"], 
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:oracle:thin:@127.0.0.1:1521:orcl"], 
                                //"querySql": [""],
                                "table": ["test1"]
                            }
                        ], 
                        "where": "",
                        "password": "123456", 
                        "username": "SONG"
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    
					"parameter": {
                        "column": ["id","curtime"], 
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/postgres", 
                                "table": ["public.test1"]
                            }
                        ], 
                        "password": "1", 
                        "postSql": [], 
                        "preSql": [], 
                        "username": "uxdb"
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}
[uxdb@local64 bin]$

第③步：根據配置的json檔案，開始同步

python datax.py myOra2Postgres.json

使用效果如下圖所示：
在這裡插入圖片描述

同步案例結果彙報1：經測驗 如上，Oracle中的test1表中資料被同步到了PostgreSQL中同結構（可以不同名）的表中！即：兩行資料都被 全量 地同步了過來。

我們下面來看一下該命令在執行過程中的列印資訊（將更加有助於我們理解其工作原理！）

第④步：根據官方外掛文件，詳解各模組引數設定
這裡緊密相關的兩份文件為：OracleReader 外掛文件，點選前往 和 DataX PostgresqlWriter，點選前往

從上面的配置檔案myOra2Postgres.json中可以看出其佈局如下：

job

content

reader
writer

setting

下面來看一下OracleReader部分的引數說明：

壹、jdbcUrl：

  • 描述：描述的是到對端資料庫的JDBC連線資訊，使用JSON陣列描述，並支援一個庫填寫多個連線地址。之所以使用JSON陣列描述連線資訊，是因為這樣支援多個IP探測：如果配置了多個，OracleReader可以依次探測ip的可連線性，直到選擇一個合法的IP。如果全部連線失敗，OracleReader報錯。 注意，jdbcUrl必須包含在connection配置單元中。對於個人使用，JSON陣列填寫一個JDBC連線即可。jdbcUrl按照Oracle官方規範，並可以填寫連線附件控制資訊。具體請參看 Oracle官方文件，點選前往
  • 必選：是
  • 預設值：無

貳、username：

  • 描述：資料來源的使用者名稱
  • 必選：是
  • 預設值：無

叄、password：

  • 描述：資料來源指定使用者名稱的密碼
  • 必選：是
  • 預設值：無

肆、table：

  • 描述：所選取的需要同步的表。使用JSON的陣列描述，因此支援多張表同時抽取。當配置為多張表時，使用者自己需保證多張表是同一schema結構，OracleReader不予檢查表是否同一邏輯表。注意，table必須包含在connection配置單元中。
  • 必選：是
  • 預設值：無

同步案例結果彙報2：經測驗 在上面案例中：在Oracle上增加一個同test1一樣的test2表，然後在這個table選項裡面為："table": ["test1,test2"]。之後Oracle中的兩表 表中資料都被同步到了PostgreSQL中同結構（可以不同名）的表中！即：資料都被 全量 地同步了過來。

伍、column：

  • 描述：所配置的表中需要同步的列名集合，使用JSON陣列描述欄位資訊。使用者使用*代表預設使用所有列配置，例如[’ * ']。支援列裁剪，即列可以挑選部分列進行匯出。支援列換序，即列可以不按照表schema資訊進行匯出。支援常量配置，使用者需要按照JSON格式: ["id", "table", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"] id為普通列名，table為包含保留在的列名，1為整形數字常量，'bazhen.csy’為字串常量，null為空指標，to_char(a + 1)為表示式，2.3為浮點數，true為布林值。必須顯示填寫，不允許為空！
  • 必選：是
  • 預設值：無

陸、splitPk：

  • 描述：OracleReader進行資料抽取時，如果指定splitPk，表示使用者希望使用splitPk代表的欄位進行資料分片，DataX因此會啟動併發任務進行資料同步，這樣可以大大提供資料同步的效能。推薦splitPk使用者使用表主鍵，因為表主鍵通常情況下比較均勻，因此切分出來的分片也不容易出現資料熱點。目前splitPk僅支援整形、字串型資料切分，不支援浮點、日期等其他型別。如果使用者指定其他非支援型別，OracleReader將報錯！splitPk如果不填寫，將視作使用者不對單表進行切分，OracleReader使用單通道同步全量資料。
  • 必選：否
  • 預設值：無

柒、where：

  • 描述：篩選條件，OracleReader根據指定的column、table、where條件拼接SQL，並根據這個SQL進行資料抽取。在實際業務場景中，往往會選擇當天的資料進行同步，可以將where條件指定為gmt_create > $bizdate 。注意：不可以將where條件指定為limit 10，limit不是SQL的合法where子句。where條件可以有效地進行業務增量同步。 非常重要 非常重要 非常重要
  • 必選：否
  • 預設值：無

捌、querySql：

  • 描述：在有些業務場景下，where這一配置項不足以描述所篩選的條件，使用者可以通過該配置型來自定義篩選SQL。當使用者配置了這一項之後，DataX系統就會忽略table（它不可以和table一起配置），column這些配置型，直接使用這個配置項的內容對資料進行篩選，例如需要進行多表join後同步資料，使用select a,b from table_a join table_b on table_a.id = table_b.id 當使用者配置querySql時，OracleReader直接忽略table、column、where條件的配置。
  • 必選：否
  • 預設值：無
    在這裡插入圖片描述

玖、fetchSize：

  • 描述：該配置項定義了外掛和資料庫伺服器端每次批量資料獲取條數，該值決定了DataX和伺服器端的網路互動次數，能夠較大的提升資料抽取效能。注意，該值過大(>2048)可能造成DataX程式OOM。
  • 必選：否
  • 預設值：1024

拾、session：

  • 描述：控制寫入資料的時間格式，時區等的配置，如果表中有時間欄位，配置該值以明確告知寫入 oracle 的時間格式。通常配置的引數為：NLS_DATE_FORMAT,NLS_TIME_FORMAT。其配置的值為 json 格式，例如：
"session": [
              "alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'",
              "alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss'",
              "alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh24:mi:ss'",
              "alter session set TIME_ZONE='US/Pacific'"
            ]
  • 必選：否
  • 預設值：無

下面來看一下PostgresqlWriter部分的引數說明：

壹、jdbcUrl：

  • 描述：目的資料庫的 JDBC 連線資訊 ,jdbcUrl必須包含在connection配置單元中。注意：1、在一個資料庫上只能配置一個值。 2、jdbcUrl按照PostgreSQL官方規範，並可以填寫連線附加引數資訊
  • 必選：是
  • 預設值：無

貳、username：

  • 描述：目的資料庫的使用者名稱
  • 必選：是
  • 預設值：無

叄、password：

  • 描述：目的資料庫的密碼
  • 必選：是
  • 預設值：無

肆、table：

  • 描述：目的表的表名稱。支援寫入一個或者多個表。當配置為多張表時，必須確保所有表結構保持一致。注意：table 和 jdbcUrl 必須包含在 connection 配置單元中
  • 必選：是
  • 預設值：無

伍、column：

  • 描述：目的表需要寫入資料的欄位,欄位之間用英文逗號分隔。例如: "column": ["id","name","age"]。如果要依次寫入全部列，使用 * 表示, 例如: "column": ["*"]。注意：1、我們強烈不推薦你這樣配置，因為當你目的表欄位個數、型別等有改動時，你的任務可能執行不正確或者失敗。 2、此處 column 不能配置任何常量值
  • 必選：是
  • 預設值：否
  • 注1：如果這裡的column和上面的（reader）不一致則會報如下錯誤：
    在這裡插入圖片描述
  • 注2：如果這裡的column和上面的（reader）一致（即使只有部分欄位也是可以的）如下：（當然這裡同步的最終結果資料只有id這一個欄位有資料）
    在這裡插入圖片描述
    陸、preSql：
  • 描述：寫入資料到目的表前，會先執行這裡的標準語句。如果 Sql 中有你需要操作到的表名稱，請使用 @table 表示，這樣在實際執行 Sql 語句時，會對變數按照實際表名稱進行替換。比如你的任務是要寫入到目的端的100個同構分表(表名稱為:datax_00,datax01, ... datax_98,datax_99)，並且你希望匯入資料前，先對錶中資料進行刪除操作，那麼你可以這樣配置："preSql":["delete from @table"]，效果是：在執行到每個表寫入資料前，會先執行對應的 delete from 對應表名稱
  • 必選：否
  • 預設值：無

柒、postSql：

  • 描述：寫入資料到目的表後，會執行這裡的標準語句。（原理同 preSql ）
  • 必選：否
  • 預設值：無

捌、batchSize：

  • 描述：一次性批量提交的記錄數大小，該值可以極大減少DataX與PostgreSql的網路互動次數，並提升整體吞吐量。但是該 - 值設定過大可能會造成DataX執行程式OOM情況。
  • 必選：否
  • 預設值：1024

第⑤步：根據同步列印的資訊，思考問題
在上面兩部分的各自文件中有介紹對應Reader 和 Writer的實現原理，我們先來看一下：

1、OracleReader 實現原理：

①、OracleReader外掛實現了從Oracle讀取資料。在底層實現上，OracleReader通過JDBC連線遠端Oracle資料庫，並執行相應的sql語句將資料從Oracle庫中SELECT出來

②、OracleReader通過JDBC聯結器連線到遠端的Oracle資料庫，並根據使用者配置的資訊生成查詢SELECT SQL語句併傳送到遠端Oracle資料庫，並將該SQL執行返回結果使用DataX自定義的資料型別拼裝為抽象的資料集，並傳遞給下游Writer處理。對於使用者配置Table、Column、Where的資訊，OracleReader將其拼接為SQL語句傳送到Oracle資料庫；對於使用者配置querySql資訊，Oracle直接將其傳送到Oracle資料庫

2、PostgresqlWriter 實現原理：

①、PostgresqlWriter外掛實現了寫入資料到 PostgreSQL主庫目的表的功能。在底層實現上，PostgresqlWriter通過JDBC連線遠端 PostgreSQL 資料庫，並執行相應的 insert into ... sql 語句將資料寫入 PostgreSQL，內部會分批次提交入庫。PostgresqlWriter面向ETL開發工程師，他們使用PostgresqlWriter從數倉匯入資料到PostgreSQL。同時 PostgresqlWriter亦可以作為資料遷移工具為DBA等使用者提供服務

②、PostgresqlWriter通過 DataX 框架獲取 Reader 生成的協議資料，根據配置生成相應的SQL插入語句：
insert into... ; #(當主鍵/唯一性索引衝突時會寫不進去衝突的行)


注意：目的表所在資料庫必須是主庫才能寫入資料；整個任務至少需具備 insert into…的許可權，是否需要其他許可權，取決於你任務配置中在 preSql 和 postSql 中指定的語句。PostgresqlWriter和MysqlWriter不同，不支援配置writeMode引數。

下面是整個同步過程中的資訊列印，我們來看一下：
在這裡插入圖片描述

下面我這裡修改一下，直接採用querySql的方式如下：
在這裡插入圖片描述
我們也看到了這是一個預設的 全量同步 的方式，例如下面的一個場景：

在Oracle端的test1 有新的插入或者刪除的話，下次再次執行 python datax.py myOra2Postgres.json 就會是一個全部遷移。下面我們藉助於上面的preSql實現 偽增量 如下：
在這裡插入圖片描述

二、 MySQL 到 PostgreSQL 的同步

MySQL端準備如下：

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.06 sec)

mysql> create table mtest1 (id int,curtime timestamp);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into mtest1 values(1,now());
Query OK, 1 row affected (0.06 sec)

mysql> insert into mtest1 values(2,now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from mtest1;
+------+---------------------+
| id   | curtime             |
+------+---------------------+
|    1 | 2020-11-09 14:27:11 |
|    2 | 2020-11-09 14:27:14 |
+------+---------------------+
2 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql>

PostgreSQL端準備如下：

postgres=# create table ptest1 (id int,curtime timestamp);
CREATE TABLE
postgres=# \d
        List of relations
 Schema |  Name  | Type  | Owner 
--------+--------+-------+-------
 public | ptest1 | table | uxdb
(1 row)

postgres=# select * from ptest1 ;
 id | curtime 
----+---------
(0 rows)

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)

postgres=#

開始同步（MySQL到PostgreSQL的資料同步）：

第①步：建立同步所需的配置檔案（json格式）
使用方法為：

python datax.py -r {資料來源端的readername} -w {目的地端的writername}

# 我們這裡是MySQL到 PostgreSQL，於是命令如下：
python datax.py -r mysqlreader -w postgresqlwriter > myMys2Postgres.json

清理沒有用的資訊，得到模板配置檔案如下：

[uxdb@local64 bin]$ python datax.py -r mysqlreader -w postgresqlwriter > myMys2Postgres.json
[uxdb@local64 bin]$ vim myMys2Postgres.json 
[uxdb@local64 bin]$ cat myMys2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader", 
                    "parameter": {
                        "column": [], 
                        "connection": [
                            {
                                "jdbcUrl": [], 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "username": "", 
                        "where": ""
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": [], 
                        "connection": [
                            {
                                "jdbcUrl": "", 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "postSql": [], 
                        "preSql": [], 
                        "username": ""
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}
[uxdb@local64 bin]$

第②步：自行按需配置同步的配置json檔案

[uxdb@local64 bin]$ cat myMys2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader", 
                    "parameter": {
                        "column": ["id,curtime"], 
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/mywork"], 
                                "table": ["mtest1"]
                            }
                        ], 
                        "password": "123456", 
                        "username": "root", 
                        "where": ""
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": ["*"], 
                        "connection": [
                            {
                                "jdbcUrl":"jdbc:postgresql://127.0.0.1:5432/postgres", 
                                "table": ["ptest1"]
                            }
                        ], 
                        "password": "1", 
                        "postSql": [], 
                        "preSql": ["delete from @table"], 
                        "username": "uxdb"
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}
[uxdb@local64 bin]$

第③步：根據配置的json檔案，開始同步

python datax.py myMys2Postgres.json

使用效果如下圖所示：
在這裡插入圖片描述
第④步：根據官方外掛文件，詳解各模組引數設定
因為我們這裡的目標都是寫入到PostgreSQL資料庫，因此本文暫不討論其他的Writer模組。下面將結合一下官方文件：MysqlReader 外掛文件，點選前往 看面一下MySQLReader模組的引數說明：

壹、jdbcUrl：

  • 描述：描述的是到對端資料庫的JDBC連線資訊，使用JSON陣列描述，並支援一個庫填寫多個連線地址。之所以使用JSON陣列描述連線資訊，是因為阿里集團內部支援多個IP探測，如果配置了多個，MysqlReader可以依次探測ip的可連線性，直到選擇一個合法的IP。如果全部連線失敗，MysqlReader報錯。 注意，jdbcUrl必須包含在connection配置單元中。對於個人使用，JSON陣列填寫一個JDBC連線即可。jdbcUrl按照Mysql官方規範，並可以填寫連線附件控制資訊。具體請參看Mysql官方文件。
  • 必選：是
  • 預設值：無

貳、username：

  • 描述：資料來源的使用者名稱
  • 必選：是
  • 預設值：無

叄、password：

  • 描述：資料來源指定使用者名稱的密碼
  • 必選：是
  • 預設值：無

肆、table：

  • 描述：所選取的需要同步的表。使用JSON的陣列描述，因此支援多張表同時抽取。當配置為多張表時，使用者自己需保證多張表是同一schema結構，MysqlReader不予檢查表是否同一邏輯表。注意，table必須包含在connection配置單元中。
  • 必選：是
  • 預設值：無

伍、column：

  • 描述：所配置的表中需要同步的列名集合，使用JSON的陣列描述欄位資訊。使用者使用 *代表預設使用所有列配置，例如[’ * ']。支援列裁剪，即列可以挑選部分列進行匯出。支援列換序，即列可以不按照表schema資訊進行匯出。支援常量配置，使用者需要按照Mysql SQL語法格式: ["id", "table", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"] id為普通列名，table為包含保留在的列名，1為整形數字常量，'bazhen.csy’為字串常量，null為空指標，to_char(a + 1)為表示式，2.3為浮點數，true為布林值。
  • 必選：是
  • 預設值：無

陸、splitPk：

  • 描述：MysqlReader進行資料抽取時，如果指定splitPk，表示使用者希望使用splitPk代表的欄位進行資料分片，DataX因此會啟動併發任務進行資料同步，這樣可以大大提供資料同步的效能。推薦splitPk使用者使用表主鍵，因為表主鍵通常情況下比較均勻，因此切分出來的分片也不容易出現資料熱點。目前splitPk僅支援整形資料切分，不支援浮點、字串、日期等其他型別。如果使用者指定其他非支援型別，MysqlReader將報錯！如果splitPk不填寫，包括不提供splitPk或者splitPk值為空，DataX視作使用單通道同步該表資料。
  • 必選：否
  • 預設值：空

柒、where：

  • 描述：篩選條件，MysqlReader根據指定的column、table、where條件拼接SQL，並根據這個SQL進行資料抽取。在實際業務場景中，往往會選擇當天的資料進行同步，可以將where條件指定為gmt_create > $bizdate 。注意：不可以將where條件指定為limit 10，limit不是SQL的合法where子句。where條件可以有效地進行業務增量同步。如果不填寫where語句，包括不提供where的key或者value，DataX均視作同步全量資料。
  • 必選：否
  • 預設值：無

捌、querySql：

  • 描述：在有些業務場景下，where這一配置項不足以描述所篩選的條件，使用者可以通過該配置型來自定義篩選SQL。當使用者配置了這一項之後，DataX系統就會忽略table，column這些配置型，直接使用這個配置項的內容對資料進行篩選，例如需要進行多表join後同步資料，使用select a,b from table_a join table_b on table_a.id = table_b.id 當使用者配置querySql時，MysqlReader直接忽略table、column、where條件的配置，querySql優先順序大於table、column、where選項。
  • 必選：否
  • 預設值：無

注：這些東西和上面的OracleReader一模一樣，（如果你要是足夠資訊 完全可以看出他們到底是誰copy誰的關係）；至於PostgreSQLWriter就不再敘述了。

第⑤步：根據同步列印的資訊，思考問題
在上面兩部分的各自文件中有介紹對應Reader 和 Writer的實現原理，我們這裡主要來看一下MysqlReader：

1、MysqlReader 實現原理：

①、MysqlReader外掛實現了從Mysql讀取資料。在底層實現上，MysqlReader通過JDBC連線遠端Mysql資料庫，並執行相應的sql語句將資料從mysql庫中SELECT出來。不同於其他關係型資料庫，MysqlReader不支援FetchSize

②、MysqlReader通過JDBC聯結器連線到遠端的Mysql資料庫，並根據使用者配置的資訊生成查詢SELECT SQL語句，然後傳送到遠端Mysql資料庫，並將該SQL執行返回結果使用DataX自定義的資料型別拼裝為抽象的資料集，並傳遞給下游Writer處理。對於使用者配置Table、Column、Where的資訊，MysqlReader將其拼接為SQL語句傳送到Mysql資料庫；對於使用者配置querySql資訊，MysqlReader直接將其傳送到Mysql資料庫。

注：因為樣例使用基本上沒有太大區別，做到舉一反三非常簡單。不過需要注意一點：

1、注意看你的資料庫的版本，datax中預設的驅動是 mysql-connector-java-5.1.34.jar
在這裡插入圖片描述
2、其他幾種資料庫也可以在類似目錄datax/plugin/下找到，如上 若是比如你的mysql版本是8.0的,記得新增加一個8.0的驅動。加在該libs即可

三、 PostgreSQL 到 PostgreSQL 的同步

PostgreSQL源端準備如下：

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)

postgres=# \d
        List of relations
 Schema |  Name  | Type  | Owner 
--------+--------+-------+-------
 public | ptest1 | table | uxdb
(1 row)

postgres=# select * from ptest1 ;
 id |       curtime       
----+---------------------
  1 | 2020-11-09 14:27:11
  2 | 2020-11-09 14:27:14
(2 rows)

postgres=#

PostgreSQL目的端準備如下：

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)

postgres=#
postgres=# \d
Did not find any relations.
postgres=# create table test1 (id int,curtime timestamp);
CREATE TABLE
postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | test1 | table | uxdb
(1 row)

postgres=# select current_database();
 current_database 
------------------
 postgres
(1 row)

postgres=#

開始同步（PostgreSQL到PostgreSQL的資料同步）：

注：同步到同一個資料庫叢集 或 同一個資料庫中的不同表中都是可以的，我們這裡是同步到不同的資料庫叢集中

第①步：建立同步所需的配置檔案（json格式）
使用方法為：

python datax.py -r {資料來源端的readername} -w {目的地端的writername}

# 我們這裡是PostgreSQL到 PostgreSQL，於是命令如下：
python datax.py -r postgresqlreader -w postgresqlwriter > myPos2Postgres.json

清理沒有用的資訊，得到模板配置檔案如下：

[uxdb@local64 bin]$ python datax.py -r postgresqlreader -w postgresqlwriter > myPos2Postgres.json
[uxdb@local64 bin]$ vim myPos2Postgres.json 
[uxdb@local64 bin]$ cat myPos2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "postgresqlreader", 
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": [], 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "username": ""
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": [], 
                        "connection": [
                            {
                                "jdbcUrl": "", 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "postSql": [], 
                        "preSql": [], 
                        "username": ""
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}
[uxdb@local64 bin]$

第②步：自行按需配置同步的配置json檔案

[uxdb@local64 bin]$ cat myPos2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "postgresqlreader", 
                    "parameter": {
						"column": ["id","curtime"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:postgresql://127.0.0.1:5432/postgres"], 
                                "table": ["public.ptest1"]
                            }
                        ], 
                        "password": "1", 
                        "username": "uxdb"
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": ["id","curtime"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:postgresql://127.0.0.1:5433/postgres", 
                                "table": ["test1"]
                            }
                        ], 
                        "password": "1", 
                        "postSql": [], 
						"preSql": ["delete from @table"],
                        "username": "uxdb"
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}
[uxdb@local64 bin]$

第③步：根據配置的json檔案，開始同步

python datax.py myPos2Postgres.json

使用效果如下圖所示：
在這裡插入圖片描述

第④步：根據官方外掛文件，詳解各模組引數設定
因為我們這裡的目標都是寫入到PostgreSQL資料庫，因此本文暫不討論其他的Writer模組。下面將結合一下官方文件：PostgresqlReader外掛文件，點選前往 看面一下PostgresqlReader模組的引數說明：

壹、jdbcUrl：

  • 描述：描述的是到對端資料庫的JDBC連線資訊，使用JSON陣列描述，並支援一個庫填寫多個連線地址。之所以使用JSON陣列描述連線資訊，是因為阿里集團內部支援多個IP探測，如果配置了多個，PostgresqlReader可以依次探測ip的可連線性，直到選擇一個合法的IP。如果全部連線失敗，PostgresqlReader報錯。 注意，jdbcUrl必須包含在connection配置單元中。對於個人使用，JSON陣列填寫一個JDBC連線即可。jdbcUrl按照Mysql官方規範，並可以填寫連線附件控制資訊。具體請參看Postgresql官方文件。
  • 必選：是
  • 預設值：無

貳、username：

  • 描述：資料來源的使用者名稱
  • 必選：是
  • 預設值：無

叄、password：

  • 描述：資料來源指定使用者名稱的密碼
  • 必選：是
  • 預設值：無

肆、table：

  • 描述：所選取的需要同步的表。使用JSON的陣列描述，因此支援多張表同時抽取。當配置為多張表時，使用者自己需保證多張表是同一schema結構，PostgresqlReader不予檢查表是否同一邏輯表。注意，table必須包含在connection配置單元中。
  • 必選：是
  • 預設值：無

伍、column：

  • 描述：所配置的表中需要同步的列名集合，使用JSON的陣列描述欄位資訊。使用者使用 *代表預設使用所有列配置，例如[’ * ']。支援列裁剪，即列可以挑選部分列進行匯出。支援列換序，即列可以不按照表schema資訊進行匯出。支援常量配置，使用者需要按照Mysql SQL語法格式: ["id", "table", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"] id為普通列名，table為包含保留在的列名，1為整形數字常量，'bazhen.csy’為字串常量，null為空指標，to_char(a + 1)為表示式，2.3為浮點數，true為布林值。column必須使用者顯示指定同步的列集合，不允許為空！
  • 必選：是
  • 預設值：無

陸、splitPk：

  • 描述：PostgresqlReader進行資料抽取時，如果指定splitPk，表示使用者希望使用splitPk代表的欄位進行資料分片，DataX因此會啟動併發任務進行資料同步，這樣可以大大提供資料同步的效能。推薦splitPk使用者使用表主鍵，因為表主鍵通常情況下比較均勻，因此切分出來的分片也不容易出現資料熱點。目前splitPk僅支援整形資料切分，不支援浮點、字串、日期等其他型別。如果使用者指定其他非支援型別，PostgresqlReader將報錯！如果splitPk不填寫，包括不提供splitPk或者splitPk值為空，DataX視作使用單通道同步該表資料。
  • 必選：否
  • 預設值：空

柒、where：

  • 描述：篩選條件，PostgresqlReader根據指定的column、table、where條件拼接SQL，並根據這個SQL進行資料抽取。在實際業務場景中，往往會選擇當天的資料進行同步，可以將where條件指定為gmt_create > $bizdate 。注意：不可以將where條件指定為limit 10，limit不是SQL的合法where子句。where條件可以有效地進行業務增量同步。如果不填寫where語句，包括不提供where的key或者value，DataX均視作同步全量資料。
  • 必選：否
  • 預設值：無

捌、querySql：

  • 描述：在有些業務場景下，where這一配置項不足以描述所篩選的條件，使用者可以通過該配置型來自定義篩選SQL。當使用者配置了這一項之後，DataX系統就會忽略table，column這些配置型，直接使用這個配置項的內容對資料進行篩選，例如需要進行多表join後同步資料，使用select a,b from table_a join table_b on table_a.id = table_b.id 當使用者配置querySql時，PostgresqlReader直接忽略table、column、where條件的配置，querySql優先順序大於table、column、where選項。
  • 必選：否
  • 預設值：無

玖、fetchSize：

  • 描述：該配置項定義了外掛和資料庫伺服器端每次批量資料獲取條數，該值決定了DataX和伺服器端的網路互動次數，能夠較大的提升資料抽取效能。注意，該值過大(>2048)可能造成DataX程式OOM。。
  • 必選：否
  • 預設值：1024

注：這些東西和上面的OracleReader一模一樣，（如果你要是足夠資訊 完全可以看出他們到底是誰copy誰的關係）；至於PostgreSQLWriter就不再敘述了。

第⑤步：根據同步列印的資訊，思考問題
在上面兩部分的各自文件中有介紹對應Reader 和 Writer的實現原理，我們這裡主要來看一下PostgresqlReader：

1、PostgresqlReader 實現原理：

①、PostgresqlReader 外掛實現了從PostgreSQL讀取資料。在底層實現上，PostgresqlReader 通過JDBC連線遠端PostgreSQL資料庫，並執行相應的sql語句將資料從PostgreSQL庫中SELECT出來

②、PostgresqlReader 通過JDBC聯結器連線到遠端的PostgreSQL資料庫，並根據使用者配置的資訊生成查詢SELECT SQL語句，然後傳送到遠端PostgreSQL資料庫，並將該SQL執行返回結果使用DataX自定義的資料型別拼裝為抽象的資料集，並傳遞給下游Writer處理。對於使用者配置Table、Column、Where的資訊，PostgresqlReader將其拼接為SQL語句傳送到Mysql資料庫；對於使用者配置querySql資訊，PostgresqlReader直接將其傳送到PostgreSQL資料庫

DataX 的增量更新

從上面的例項中，我們也看到了DataX是不太支援 增量同步的，下面是一種藉助於DataX支援異構資料來源的特性實現的增量同步方式。其設計思路：只要從目標資料庫讀取一個最大值的記錄， 可能是DateTime 或者 RowVersion 型別， 然後根據這個最大值對源資料庫同步的表進行過濾， 然後再進行同步即可。（也還是全量同步的使用）因為 DataX 支援多種資料庫的讀寫， 一種相對簡單並且可靠的方式就是：

1、利用 DataX 的 Reader 去目標資料庫讀取一個最大值
2、將這個最大值用 TextFileWriter 寫入到一個 CSV 檔案
3、用 Shell 指令碼來讀取 CSV 檔案， 並動態修改全部同步的配置檔案
4、執行修改後的配置檔案， 進行增量同步（全量同步）

我們這裡直接在第三個例子（PostgreSQL2PostgreSQL）上實現：

第①步：建立同步所需的配置檔案（json格式）
使用方法為：

python datax.py -r {資料來源端的readername} -w {目的地端的writername}

# 我們這裡是PostgreSQL到 TXT檔案，於是命令如下：
python datax.py -r postgresqlreader -w txtfilewriter > myPos2txtfile.json

清理沒有用的資訊，得到模板配置檔案如下：

[uxdb@local64 bin]$ python datax.py -r postgresqlreader -w txtfilewriter > myPos2txtfile.json
[uxdb@local64 bin]$ vim myPos2txtfile.json 
[uxdb@local64 bin]$ cat myPos2txtfile.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "postgresqlreader", 
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": [], 
                                "table": []
                            }
                        ], 
                        "password": "", 
                        "username": ""
                    }
                }, 
                "writer": {
                    "name": "txtfilewriter", 
                    "parameter": {
                        "dateFormat": "", 
                        "fieldDelimiter": "", 
                        "fileName": "", 
                        "path": "", 
                        "writeMode": ""
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}
[uxdb@local64 bin]$

第②步：自行按需配置同步的配置json檔案

[uxdb@local64 bin]$ cat myPos2txtfile.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "postgresqlreader", 
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:postgresql://127.0.0.1:5433/postgres"], 
                                "querySql":["SELECT max(curtime) FROM public.test1"]
                            }
                        ], 
                        "password": "1", 
                        "username": "uxdb"
                    }
                }, 
                "writer": {
                    "name": "txtfilewriter", 
                    "parameter": {
                        "dateFormat": "yyyy-MM-dd HH:mm:ss", 
                        "fileFormat": "csv",
                        "fileName": "maxtimecsv", 
                        "path": "/home/uxdb/myDataX/datax/bin/", 
                        "writeMode": "truncate"
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}
[uxdb@local64 bin]$

第③步：根據配置的json檔案，開始同步

python datax.py myPos2txtfile.json

使用效果如下圖所示：
在這裡插入圖片描述
接下來，我將上面的myPos2Postgres.json 重修如下：

# 1、把這一行去掉："preSql": ["delete from @table"],
# 2、設定增量更新過濾條件

[uxdb@local64 bin]$ cat myPos2Postgres.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "postgresqlreader", 
                    "parameter": {
						"column": ["id","curtime"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:postgresql://127.0.0.1:5432/postgres"], 
                                "table": ["public.ptest1"]
                            }
                        ], 
                        "password": "1", 
                        "where":"",
                        "username": "uxdb"
                    }
                }, 
                "writer": {
                    "name": "postgresqlwriter", 
                    "parameter": {
                        "column": ["id","curtime"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:postgresql://127.0.0.1:5433/postgres", 
                                "table": ["test1"]
                            }
                        ], 
                        "password": "1", 
                        "postSql": [], 
                        "username": "uxdb"
                    }
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}
[uxdb@local64 bin]$

接下來，藉助於shell的文字操作，這一部分可以參見我的部落格：Shell指令碼的學習心得和知識總結（五）|檔案處理命令三劍客（sed、awk）

這個指令碼做的事情有：

1、讀取上面的csv檔案，得到過濾條件的值
2、使用shell替換myPos2Postgres.json，就是給它加上 where 條件
3、最終執行新的同步命令，並清理相關臨時檔案

現在先來看一下背景情況：
在這裡插入圖片描述
下面是該增量全量同步指令碼的使用效果：

第一種（目標資料庫表沒有資料），同步也就是全量遷移的方式：
在這裡插入圖片描述

第二種：在源資料庫表中插入資料，然後進行增量同步：
在這裡插入圖片描述

注：這只是一種 偽增量同步的方式，給大家作為參考：
在這裡插入圖片描述
其內容如下：

#!/bin/bash
### every exit != 0 fails the script
set -e

# 獲取目標資料庫最大資料時間，並寫入一個 csv 檔案
python /home/uxdb/myDataX/datax/bin/datax.py myPos2txtfile.json

if [ $? -ne 0 ]; then
  echo "minute_data_sync.sh error, can not get max_time from target db!"
  exit 1
fi
# 找到 DataX 寫入的文字檔案，並將內容讀取到一個變數中
RESULT_FILE=`ls maxtimecsv_*`
MAX_TIME=`cat $RESULT_FILE`
echo "$MAX_TIME"
# 如果最大時間不為 null 的話， 修改全部同步的配置，進行增量更新；
if [ "$MAX_TIME" != "null" ]; then
  # 設定增量更新過濾條件
  WHERE="curtime > '$MAX_TIME'"
  sed -i 's/\"where\"\:\"\"/\"where\"\:\"'"$WHERE"'\"/g' /home/uxdb/myDataX/datax/bin/myPos2Postgres.json

  # 刪除臨時檔案
  rm ./"$RESULT_FILE"
fi

python /home/uxdb/myDataX/datax/bin/datax.py /home/uxdb/myDataX/datax/bin/myPos2Postgres.json

相關文章