PG上云之現(xiàn)代化圣旨背景

IT那活兒發(fā)布于2023-01-11 13:20 / 1931人閱讀

PG上云之現(xiàn)代化圣旨背景

背景

所謂圣旨:奉天承運(yùn)，皇帝詔曰,不可不從之，今昔一現(xiàn)代化圣旨駕到——此次上云與以往不同的是：源庫（Oracle庫）中存在ogg同步9表數(shù)據(jù)到其他Oracle庫中。故而在Oracle庫遷移到PG庫之后，還要把這9張表從PG庫同步到其他的Oracle庫中。并且要求要實(shí)時(shí)同步。

經(jīng)前期的查詢，這９張表包括分區(qū)表，并且源端（PG端）表的字段數(shù)量比目標(biāo)端（Oracle端）的表字段數(shù)量多。目前我們使用的跨IDC數(shù)據(jù)同步工具支持pg遷移到Oracle，但是它不支持分區(qū)表，也不支持表結(jié)構(gòu)不一致的表。所以這個(gè)方法肯定是抗旨的。采用DATAX工具也不可以接旨，因?yàn)閐atax不支持增量同步。

經(jīng)協(xié)商不要求實(shí)時(shí)同步，而是每天定時(shí)同步了。故datax接下了圣旨。Datax的原理之前有介紹過，在這就不重復(fù)介紹了，下面就直接看看datax是如何接旨的。

基于datax-web的部署

一、新建項(xiàng)目

二、添加任務(wù)

任務(wù)管理-->添加

這里所屬項(xiàng)目寫剛剛新建的項(xiàng)目tdwh，這里的cron分別指的是秒，分，時(shí)，日，月，周，年，這里是每天9點(diǎn)52分整調(diào)度該任務(wù)。每一張表都要為其配置一個(gè)json文件

▼▼▼
{
  "job": {
    "content": [
      {
        "writer": {
          "parameter": {
            "username": "$username",
            "column": [
              "account_id",
              "seat_id",
              "create_time",
              "state",
              "channel_cd",
              "exten_type",
              "ring_time",
              "end_time",
              "variable",
              "local_id",
              "bz",
              "seat_prop"
            ],
            "batchSize": 512,
            "connection": [
              {
                "table": [
                  "outcall.channel_call_seat"
                ],
                "jdbcUrl": "jdbc:oracle:thin:@$host:$port/$servicesname"
              }
            ],
            "preSql": [
              "truncate table outcall.channel_call_seat"
            ],
            "password": "$password"
          },
          "name": "oraclewriter"
        },
        "reader": {
          "parameter": {
            "username": "$username",
            "connection": [
              {
                "querySql": [
                  "select * from outcall.channel_call_seat"
                ],
                "jdbcUrl": [
                  "jdbc:postgresql://$host:$port/$databasename"
                ]
              }
            ],
            "password": "$password",
            "where": ""
          },
          "name": "postgresqlreader"
        }
      }
    ],
    "setting": {
      "speed": {
        "channel": 8
      }
    }
  }
}

這里表源端與目標(biāo)端結(jié)構(gòu)一致，如果不一致，那么就要注意了，需要把這個(gè)*寫成源端表的具體屬性列名

▼▼▼
"querySql": [
                  "select * from outcall.channel_call_seat"
                ],

column這個(gè)要成目標(biāo)端表的屬性列名

▼▼▼
"column": [
              "account_id",
              "seat_id",
              "create_time",
              "state",
              "channel_cd",
              "exten_type",
              "ring_time",
              "end_time",
              "variable",
              "local_id",
              "bz",
              "seat_prop"
            ],

部署好后還要點(diǎn)擊啟動(dòng)，然后才能自動(dòng)調(diào)度任務(wù)，也可以在操作里點(diǎn)擊執(zhí)行一次就立即調(diào)度任務(wù)

三、查看日志

執(zhí)行完之后可以查看日志

可以到數(shù)據(jù)已經(jīng)同步完成，耗時(shí)24s，讀寫失敗總數(shù)為0，證明已經(jīng)全部同步了，沒有數(shù)據(jù)丟失。

基于crontab調(diào)用python腳本的部署

1、編輯json文件

▼▼▼
cd  /pg_data1/datax/bin/tdwh_test
vi  call_sheet_info.json

寫入

▼▼▼
{
    "job": {
        "content": [
            {
                "writer": {
                    "parameter": {
                        "username": "$username", 
                        "column": [
                            "id",
                            "call_sheet_id",
                            "call_id",
                            "call_type",
                            "call_no",
                            "called_no",
                            "ring_time",
                            "ringing_time",
                            "begin_time",
                            "end_time",
                            "calltime_length",
                            "queue_time",
                            "queue",
                            "agent",
                            "exten",
                            "state",
                            "monitor_file_name",
                            "pbx",
                            "agent_name",
                            "call_state",
                            "province",
                            "district",
                            "ivrkey",
                            "hanguper",
                            "investigate",
                            "update_time",
                            "contact_order_id",
                            "lan_id",
                            "mechine_id",
                            "staff_id",
                            "tele_phone",
                            "area_id",
                            "channel_cd",
                            "quality_state",
                            "quality_time",
                            "quality_staff",
                            "quality_desc",
                            "sheet_cloud",
                            "acc_nbr_type"
                        ], 
                        "batchSize": 512, 
                        "connection": [
                            {
                                "table": [
                                    "outcall.call_sheet_info"
                                ], 
                                "jdbcUrl": "jdbc:oracle:thin:@$host:$port/$servicename"
                            }
                        ], 
                        "preSql": [
                            "truncate table outcall.call_sheet_info"
                        ], 
                        "password": "$password"
                    }, 
                    "name": "oraclewriter"
                }, 
                "reader": {
                    "parameter": {
                        "username": "$username", 
                        "connection": [
                            {
                                "querySql": [
                                    "select * from outcall.call_sheet_info"
                                ], 
                                "jdbcUrl": [
                                  "jdbc:postgresql://$host:$port/$databasename"
                                ]
                            }
                        ], 
                        "password": "$password", 
                        "where": ""
                    }, 
                    "name": "postgresqlreader"
                }
            }
        ], 
        "setting": {
            "speed": {
                "channel": 8
            }
        }
    }
}

注意：要給這個(gè)json文件可執(zhí)行權(quán)限

2、編輯crontab定時(shí)任務(wù)

▼▼▼
crontab -e

0 9 * * * python /pg_data1/datax/bin/datax.py /pg_data1/datax/bin/tdwh_test/call_sheet_info.json > /pg_data1/datax/bin/tdwh_test/call_sheet_info.log

每天9點(diǎn)整調(diào)度該任務(wù)實(shí)現(xiàn)數(shù)據(jù)全量同步。

總結(jié)