> 作者：燒雞太子爺
>
> 來源：[恆生LIGHT雲社群](https://developer.hs.net/thread/1794)

前面做了DataX介紹以及安裝，下面我們用一個例項來說明下DataX的執行，也整理了一些實際運用的過程中的注意點

## DataX執行

### 執行命令

```
（1）檢視模板：

python ${DATAX_HOME}\bin\datax.py -r 讀外掛型別-w 寫外掛型別

舉例: mysql匯入到orcale資料庫   python ${DATAX_HOME}\bin\datax.py -r mysqlreader -w oraclewriter
 （2）執行任務（ 透過讀json配置檔案配置執行）：
        python ${DATAX_HOME}\bin\datax.py {json配置檔案}
```

### 舉例（從pgsql資料傳輸到mysql）

（1）檢視模板

```
    python datax.py -r postgresqlreader -w mysqlwriter

DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
    Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
    Please refer to the mysqlreader document:
    https://github.com/alibaba/DataX/blob/master/mysqlreader/doc/mysqlreader.md
    Please refer to the postgresqlwriter document:
    https://github.com/alibaba/DataX/blob/master/postgresqlwriter/doc/postgresqlwriter.md
    Please save the following configuration as a json file and  use
    python {DATAX_HOME}/bin/datax.py {JSON_FILE_NAME}.json
    to run the job.
    {
        "job": {
        "content": [
        {
            "reader": {                      //讀入庫配置
                "name": "postgresqlreader",       //資料來源名稱
                "parameter": {               //資料庫配置資訊
                    "column": [],
                    "connection": [
                        {
                            "jdbcUrl": [],
                            "table": []
                        }
                    ],
                    "password": "",
                    "username": "",
                    "where": ""            //資料過濾條件
                }
            },
            "writer": {                     //寫入庫配置
                "name": "mysqlwriter", //資料庫名稱
                "parameter": {              //資料庫配置資訊
                    "column": [],
                    "connection": [
                        {
                            "jdbcUrl": "",
                            "table": []
                        }
                    ],
                    "password": "",
                    "postSql": [],         //任務執行後操作
                    "preSql": [],          //任務執行前操作
                    "username": ""  
                }
            }
        }
    ],
    "setting": {      //基本配置
        "speed": {    //流量控制
            "channel": "" //併發數,提供了通道(併發)、記錄流、位元組流三種流控模式
        }
    }
  }//                       還可以配置髒資料控制等配置
}
```

（2） 根據模板編輯配置

```
{
"job": {
    "setting": {
        "speed": {
            "channel": 3,
            "byte": 1048576
        },
        "errorLimit": {
            "record": 0,
            "percentage": 0.02
        }
    },
    "content": [{
        "reader": {
            "name": "postgresqlreader",
            "parameter": {
                "username": "authenticator",
                "password": "financegtn104",
                "column": [
                    "\"id\"",
                    "\"name\"",
                    "\"content_type_id\"",
                    "\"codename\""
                ],
                "splitPk": "",
                "connection": [{
                    "table": [
                        "public.auth_permission"
                    ],
                    "jdbcUrl": [
                        "jdbc:postgresql://10.20.64.241:5433/finance"
                    ]
                }]
            }
        },
        "writer": {
            "name": "mysqlwriter",
            "parameter": {
                "username": "root",
                "password": "hundsun1234",
                "column": [
                    "`id`",
                    "`name`",
                    "`content_type_id`",
                    "`codename`"
                ],
                "connection": [{
                    "table": [
                        "auth_permission"
                    ],
                    "jdbcUrl": "jdbc:mysql://10.20.64.151:3306/datax_test"
                }]
            }
        }
    }]
}
}
```

（3）執行Job

```
python ./bin/datax.py ./job/mysql2pgsql.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.

2020-08-05 13:01:22.518 [main] INFO  VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2020-08-05 13:01:22.524 [main] INFO  Engine - the machine info  =>
    osInfo: Oracle Corporation 1.8 25.181-b13
    jvmInfo:        Linux amd64 3.10.0-862.11.6.el7.x86_64
    cpu num:        8
    totalPhysicalMemory:    -0.00G
    freePhysicalMemory:     -0.00G
    maxFileDescriptorCount: -1
    currentOpenFileDescriptorCount: -1
    GC Names        [PS MarkSweep, PS Scavenge]
    MEMORY_NAME                    | allocation_size                | init_size              
    PS Eden Space                  | 256.00MB                       | 256.00MB               
    Code Cache                     | 240.00MB                       | 2.44MB                 
    Compressed Class Space         | 1,024.00MB                     | 0.00MB                 
    PS Survivor Space              | 42.50MB                        | 42.50MB                
    PS Old Gen                     | 683.00MB                       | 683.00MB               
    Metaspace                      | -0.00MB                        | 0.00MB

2020-08-05 13:01:22.541 [main] INFO  Engine -
{
    "content":[
            {
                    "reader":{
                            "name":"postgresqlreader",
                            "parameter":{
                                    "column":[
                                            "\"id\"",
                                            "\"name\"",
                                            "\"content_type_id\"",
                                            "\"codename\""
                                    ],
                                    "connection":[
                                            {
                                                    "jdbcUrl":[
                                                            "jdbc:postgresql://10.20.64.241:5433/finance"
                                                    ],
                                                    "table":[
                                                            "public.auth_permission"
                                                    ]
                                            }
                                    ],
                                    "password":"*************",
                                    "splitPk":"",
                                    "username":"authenticator"
                            }
                    },
                    "writer":{
                            "name":"mysqlwriter",
                            "parameter":{
                                    "column":[
                                            "`id`",
                                            "`name`",
                                            "`content_type_id`",
                                            "`codename`"
                                    ],
                                    "connection":[
                                            {
                                                    "jdbcUrl":"jdbc:mysql://10.20.64.151:3306/datax_test",
                                                    "table":[
                                                            "auth_permission"
                                                    ]
                                            }
                                    ],
                                    "password":"***********",
                                    "username":"root"
                            }
                    }
            }
    ],
    "setting":{
            "errorLimit":{
                    "percentage":0.02,
                    "record":0
            },
            "speed":{
                    "byte":1048576,
                    "channel":3
            }
    }
}
2020-08-05 13:01:22.557 [main] WARN  Engine - prioriy set to 0, because NumberFormatException, the value is: null
2020-08-05 13:01:22.559 [main] INFO  PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2020-08-05 13:01:22.559 [main] INFO  JobContainer - DataX jobContainer starts job.
2020-08-05 13:01:22.560 [main] INFO  JobContainer - Set jobId = 0
2020-08-05 13:01:22.676 [job-0] INFO  OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:postgresql://10.20.64.241:5433/finance.
2020-08-05 13:01:22.704 [job-0] INFO  OriginalConfPretreatmentUtil - table:[public.auth_permission] has columns:[id,name,content_type_id,codename].
2020-08-05 13:01:22.917 [job-0] INFO  OriginalConfPretreatmentUtil - table:[auth_permission] all columns:[
id,name,content_type_id,codename
].
2020-08-05 13:01:22.924 [job-0] INFO  OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (`id`,`name`,`content_type_id`,`codename`) VALUES(?,?,?,?)
], which jdbcUrl like:[jdbc:mysql://10.20.64.151:3306/datax_test?yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true]
2020-08-05 13:01:22.925 [job-0] INFO  JobContainer - jobContainer starts to do prepare ...
2020-08-05 13:01:22.925 [job-0] INFO  JobContainer - DataX Reader.Job [postgresqlreader] do prepare work .
2020-08-05 13:01:22.926 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] do prepare work .
2020-08-05 13:01:22.926 [job-0] INFO  JobContainer - jobContainer starts to do split ...
2020-08-05 13:01:22.927 [job-0] INFO  JobContainer - Job set Max-Byte-Speed to 1048576 bytes.
2020-08-05 13:01:22.929 [job-0] INFO  JobContainer - DataX Reader.Job [postgresqlreader] splits to [1] tasks.
2020-08-05 13:01:22.930 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] splits to [1] tasks.
2020-08-05 13:01:22.944 [job-0] INFO  JobContainer - jobContainer starts to do schedule ...
2020-08-05 13:01:22.947 [job-0] INFO  JobContainer - Scheduler starts [1] taskGroups.
2020-08-05 13:01:22.948 [job-0] INFO  JobContainer - Running by standalone Mode.
2020-08-05 13:01:22.955 [taskGroup-0] INFO  TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2020-08-05 13:01:22.958 [taskGroup-0] INFO  Channel - Channel set byte_speed_limit to -1, No bps activated.
2020-08-05 13:01:22.958 [taskGroup-0] INFO  Channel - Channel set record_speed_limit to -1, No tps activated.
2020-08-05 13:01:22.964 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2020-08-05 13:01:22.967 [0-0-0-reader] INFO  CommonRdbmsReader$Task - Begin to read record by Sql: [select "id","name","content_type_id","codename" from public.auth_permission
] jdbcUrl:[jdbc:postgresql://10.20.64.241:5433/finance].
2020-08-05 13:01:23.063 [0-0-0-reader] INFO  CommonRdbmsReader$Task - Finished read record by Sql: [select "id","name","content_type_id","codename" from public.auth_permission
] jdbcUrl:[jdbc:postgresql://10.20.64.241:5433/finance].
2020-08-05 13:01:23.365 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[402]ms
2020-08-05 13:01:23.366 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] completed it's tasks.
2020-08-05 13:01:32.966 [job-0] INFO  StandAloneJobContainerCommunicator - Total 24 records, 803 bytes | Speed 80B/s, 2 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 0.000s |  All Task WaitReaderTime 0.000s | Percentage 100.00%
2020-08-05 13:01:32.966 [job-0] INFO  AbstractScheduler - Scheduler accomplished all tasks.
2020-08-05 13:01:32.967 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] do post work.
2020-08-05 13:01:32.967 [job-0] INFO  JobContainer - DataX Reader.Job [postgresqlreader] do post work.
2020-08-05 13:01:32.967 [job-0] INFO  JobContainer - DataX jobId [0] completed successfully.
2020-08-05 13:01:32.968 [job-0] INFO  HookInvoker - No hook invoked, because base dir not exists or is a file: /home/dataX/datax/hook
2020-08-05 13:01:32.968 [job-0] INFO  JobContainer -
     [total cpu info] =>
            averageCpu                     | maxDeltaCpu                    | minDeltaCpu            
            -1.00%                         | -1.00%                         | -1.00%

[total gc info] =>
             NAME                 | totalGCCount       | maxDeltaGCCount    | minDeltaGCCount    | totalGCTime        | maxDeltaGCTime     | minDeltaGCTime   
             PS MarkSweep         | 0                  | 0                  | 0                  | 0.000s             | 0.000s             | 0.000s     
             PS Scavenge          | 0                  | 0                  | 0                  | 0.000s             | 0.000s             | 0.000s     
2020-08-05 13:01:32.969 [job-0] INFO  JobContainer - PerfTrace not enable!
2020-08-05 13:01:32.969 [job-0] INFO  StandAloneJobContainerCommunicator - Total 24 records, 803 bytes | Speed 80B/s, 2 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 0.000s |  All Task WaitReaderTime 0.000s | Percentage 100.00%
2020-08-05 13:01:32.969 [job-0] INFO  JobContainer -
任務啟動時刻                    : 2020-08-05 13:01:22
任務結束時刻                    : 2020-08-05 13:01:32
任務總計耗時                    :                 10s
任務平均流量                    :               80B/s
記錄寫入速度                    :              2rec/s
讀出記錄總數                    :                  24
讀寫失敗總數                    :                   0  
```

## DataX-Web執行

編輯操作太麻煩？是否有如ketter一樣提供圖形介面生產配置檔案，是否有任務統一管理？

DataX Web是DataX的整合視覺化頁面系統，選擇資料來源即可一鍵生成資料同步任務，支援批次創 建RDBMS資料同步任務，整合開源排程系統，支援分散式、增量同步資料、實時檢視執行日誌、監控執行器資源、KILL執行程式等 相較於kettle，DataX-Web直接透過頁面一鍵生成任務，實時檢視執行情況，同步效率更快等優勢

```
（1）環境準備
* 
    MySQL (5.5+) 必選，對應客戶端可以選裝, Linux服務上若安裝mysql的客戶端可以透過部署指令碼快速初始化資料庫
* 
    JDK (1.8.0_xxx) 必選
* 
    DataX 必選
* 
Python (2.x) (支援Python3需要修改替換datax/bin下面的三個python檔案，替換檔案在doc/datax-web/datax-python3下) 必選，主要用於排程執行底層DataX的啟動指令碼，預設的方式是以Java子程式方式執行DataX，使用者可以選擇以Python方式來做自定義的改造

（2）安裝
   1、原始碼編譯  2、官方編譯好的包。
    解壓包後，進入解壓後的目錄，找到bin目錄下面的 install.sh 檔案，如果選擇互動式的安裝，則直接執行./bin/install.sh
```

**注意點：**

```
官網的資料庫指令碼安裝的job_info表缺少user_id欄位，介面點選的時候會報錯找不到user_id欄位錯誤，手動增加user_id欄位，型別為int，長度為11。
Alert （ALTER TABLE job_info ADD  user_id int(11)；）
```

詳細參考教程：https://github.com/WeiYe-Jing/datax-web/blob/master/doc/datax-web/datax-web-deploy.md

***安裝包說明***

![1-1.png](https://developer.hs.net/storage/attachments/2021/12/22/G0NjgP3ojhAzZMuyRe9QGy20Z6AXSEuyiOPSnjYZ_thumb.png "4329")

最後在瀏覽器輸入IP：port就可以看到DataX-Web的頁面了

## DataX和kettle對比

1）Kettle擁有自己的管理控制檯，可以直接在客戶端進行etl任務制定，不過是CS架構，而不支援BS瀏覽器模式。DataX並沒有介面（DataX-Web已經擴充套件，支援BS）

2）支援的資料庫，都支援的比較齊全，kettle支援的應該更多，DataX是阿里開發，可以更好地支援阿里自身的資料庫系列，如ODPS、ADS等

3）Kettle已經加入BI組織Pentaho，加入後kettle的開發粒度和被關注度更進一步提升

4）DataX開源的支援粒度不高，關注度遠沒有kettle高，程式碼提交次數更是少的很。

5） 根據網上參考資訊，網友測試 kettle全量抽取較大資料量時，抽取時間長，對比測試 datax比kettle快。

## 其他

* DataX外掛開源還支撐其他擴充套件，可以下載原始碼自己編譯：
  Datax支援ElasticSearch Reader （官方已經支撐Writer，但不支援Reader）
  Datax 支援增量 postgresql writeMode update
  Datax支援Redis Writer
  擴充套件原始碼下載地址：https://github.com/WeiYe-Jing/datax-web/issues/253

* PostgreSql沒有支援增量配置，針對有修改的增量資料可以採用將增量資料放在臨時表中（與目標表同庫），透過表比對將修改的資料刪除再插入，新增資料直接插入

![1-2.png](https://developer.hs.net/storage/attachments/2021/12/22/pHbJ0mmRy2qloYXaTznHsTKOPb6CTlqKp6Aq7iR0_thumb.png "4328")

***

想向技術大佬們多多取經？開發中遇到的問題何處探討？如何獲取金融科技海量資源？

[恆生LIGHT雲社群](https://developer.hs.net/)，由恆生電子搭建的金融科技專業社群平臺，分享實用技術乾貨、資源資料、金融科技行業趨勢，擁抱所有金融開發者。

掃描下方小程式二維碼，加入我們！

![](https://developer.hs.net/storage/attachments/2021/12/20/vzjJlveZdvxcz6rUjIBaPMbRK36iL8jCjtMU5h97.png)

基於DataX的資料同步（下）-應用DataX進行資料同步

相關文章