利用OGG实现Oracle到Kafka到Greenplum的增量数据同步

PORT 7809

extract extn
userid C##GGADMIN@ora12c,password GGadmin_123
DISCARDFILE ./dirrpt/extn.dsc, APPEND, MEGABYTES 1024
EXTTRAIL  ./dirdat/na
GETUPDATEBEFORES
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
DYNAMICRESOLUTION
--源库是一个CDB，本次测试抽取pdb_test下面的两张表
table pdb_test.test.ccps_traderecord;
table pdb_test.test.ccps_unnormal_traderecord;

extract dpen
RMTHOST 192.168.11.165, MGRPORT 7809
RMTTRAIL ./dirdat/na
DYNAMICRESOLUTION
TABLE pdb_test.test.*;

add extract extn, TRANLOG, BEGIN NOW
add exttrail ./dirdat/na, EXTRACT extn, MEGABYTES 200

ADD EXTRACT dpen, EXTTRAILSOURCE ./dirdat/na
ADD RMTTRAIL ./dirdat/na, EXTRACT dpen, MEGABYTES 200

bootstrap.servers=localhost:9092
acks=1
reconnect.backoff.ms=1000

value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
# 100KB per partition
batch.size=16384
linger.ms=0
max.request.size = 5024000
send.buffer.bytes = 5024000

gg.handlerlist = kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
#The following resolves the topic name using the short table name
gg.handler.kafkahandler.topicMappingTemplate=oggtopic
#The following selects the message key using the concatenated primary keys
#gg.handler.kafkahandler.keyMappingTemplate=${primaryKeys}
gg.handler.kafkahandler.format=json
gg.handler.kafkahandler.SchemaTopicName=mySchemaTopic
gg.handler.kafkahandler.BlockingSend =false
gg.handler.kafkahandler.includeTokens=false
gg.handler.kafkahandler.mode=tx

goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE

gg.log=log4j
gg.log.level=INFO

gg.report.time=30sec

#Sample gg.classpath for Apache Kafka
gg.classpath=dirprm/:/home/oracle/kafka/libs/*
#Sample gg.classpath for HDP
#gg.classpath=/etc/kafka/conf:/usr/hdp/current/kafka-broker/libs/*

javawriter.bootoptions=-Xmx512m -Xms512m -Djava.class.path=ggjava/ggjava.jar

port 7809

replicat repn
getenv(JAVA_HOME)
getenv(CLASSPATH)
getenv(PATH)
getenv(LD_LIBRARY_PATH)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB LIBFILE libggjava.so SET property=./dirprm/kafka.props
SOURCEDEFS /home/oracle/ogg/dirdef/source.def
REPORTCOUNT EVERY 1 MINUTES, RATE
--GROUPTRANSOPS 10000
MAP pdb_test.test.ccps_traderecord, TARGET ccps.ccps_traderecord;
MAP pdb_test.test.ccps_unnormal_traderecord, TARGET ccps.ccps_unnormal_traderecord;

add replicat repn, exttrail ./dirdat/na

bootstrap.servers=localhost:9092

listeners=PLAINTEXT://localhost:9092
zookeeper.connect=localhost:2181

clientPort=2181

[oracle@gpmaster kafka]$ bin/zookeeper-server-start.sh -daemon config/zookeeper.properties
[oracle@gpmaster kafka]$ jps
18001 Jps
2505 QuorumPeerMain

[oracle@gpmaster kafka]$ bin/kafka-server-start.sh config/server.properties 
[oracle@gpmaster kafka]$ jps
18016 Kafka
2505 QuorumPeerMain
18458 Jps

[oracle@gpmaster bin]$ kafka-topics.sh --list --zookeeper localhost:2181
__consumer_offsets
mySchemaTopic
oggtest
oggtopic
test

[oracle@gpmaster bin]$ kafka-console-producer.sh --broker-list localhost:9092 --topic test
>test kafka!!!!
>

[oracle@gpmaster bin]$ kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic test     
test kafka!!!!

from kafka import KafkaConsumer
import json
import psycopg2
import time
import re

dbconn = psycopg2.connect(dbname = "devin",user = "ccps",password = "xjlove1224",host = "192.168.11.165",port = "5432")

consumer = KafkaConsumer('oggtopic',group_id = "oggtopic",auto_offset_reset='earliest',bootstrap_servers=['localhost:9092'])
for msg in consumer:
    message = msg.value
    mesg = message.decode('utf-8')
    msglist = re.findall(r'(.*?}})',mesg)  --切分

/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释 
* {"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"I","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.146000","pos":"00000000000026312368","after":{"UTR_ID":923628,"UTR_NO":"200818181648559171322","UTR_MER_NO":191713,"UTR_GW_NO":19171301,"UTR_MER_ORDERNO":"1597745803378","UTR_CURRENCY":"USD","UTR_AMOUNT":"0.01","UTR_BANK_CODE":null,"UTR_CHA_CODE":null,"UTR_RETURNURL":null,"UTR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","UTR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","UTR_PAYSTARTTIME":"2020-08-18 18:16:48.233971000","UTR_PAYTIME":null,"UTR_PAYENDTIME":null,"UTR_ERRORCODE":"50000","UTR_ERRORREASON_OUT":"Unpaid","UTR_ERRORREASON_IN":"Unpaid","UTR_MER_REMARK":"2fang","UTR_RISKINFO":null,"UTR_SETSCORE":null,"UTR_TOTALSCORE":null,"UTR_PASSRISKINFO":null,"UTR_PM_ID":1,"UTR_CARDNO_ENCRYPT":null,"UTR_NOTICEURL":null,"UTR_BILL_ADDRESS_WHITE":null,"UTR_BILL_ADDRESS_EXTRA1_WHITE":null,"UTR_LOGO_URL":null,"UTR_LANGUAGE":null,"UTR_PAY_EXPIRATIONTIME":null,"UTR_MCC":null,"UTR_PM_NAME":"Credit Card","UTR_TRADE_BANK_NAME":null,"UTR_EXPIRATION_TIME":null}}{"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.167000","pos":"00000000000026316287","before":{},"after":{"UTR_NO":"200818181648559171322","UTR_CARDNO_ENCRYPT":"GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk="}}{"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"D","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.167001","pos":"00000000000026316959","before":{"UTR_ID":923628,"UTR_NO":"200818181648559171322","UTR_MER_NO":191713,"UTR_GW_NO":19171301,"UTR_MER_ORDERNO":"1597745803378","UTR_CURRENCY":"USD","UTR_AMOUNT":"0.01","UTR_BANK_CODE":null,"UTR_CHA_CODE":null,"UTR_RETURNURL":null,"UTR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","UTR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","UTR_PAYSTARTTIME":"2020-08-18 18:16:48.233971000","UTR_PAYTIME":null,"UTR_PAYENDTIME":null,"UTR_ERRORCODE":"50000","UTR_ERRORREASON_OUT":"Unpaid","UTR_ERRORREASON_IN":"Unpaid","UTR_MER_REMARK":"2fang","UTR_RISKINFO":null,"UTR_SETSCORE":null,"UTR_TOTALSCORE":null,"UTR_PASSRISKINFO":null,"UTR_PM_ID":1,"UTR_CARDNO_ENCRYPT":"GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk=","UTR_NOTICEURL":null,"UTR_BILL_ADDRESS_WHITE":null,"UTR_BILL_ADDRESS_EXTRA1_WHITE":null,"UTR_LOGO_URL":null,"UTR_LANGUAGE":null,"UTR_PAY_EXPIRATIONTIME":null,"UTR_MCC":null,"UTR_PM_NAME":"Credit Card","UTR_TRADE_BANK_NAME":null,"UTR_EXPIRATION_TIME":null}}{"table":"CCPS.CCPS_TRADERECORD","op_type":"I","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.168000","pos":"00000000000026318029","after":{"TR_ID":787160,"TR_NO":"200818181648559171322","TR_MER_ORDERNO":"1597745803378","TR_MER_NO":191713,"TR_GW_NO":19171301,"TR_CURRENCY":"USD","TR_AMOUNT":0.01,"TR_STATUS":-1,"TR_TRADE_RATE":0.0100,"TR_SPP_CURRENCY":"USD","TR_SPP":0,"TR_AGENT_NO":0,"TR_AGENT_RATE":null,"TR_AGENT_SPP_CURRENCY":null,"TR_AGENT_SPP":null,"TR_RESEVER_RATE":0.1000,"TR_RATE_VALUE":1,"TR_BANKCURRENCY":"USD","TR_BANKAMOUT":0.01,"TR_BANK_CODE":"OPBank","TR_CHA_CODE":121,"TR_ISDELAY":0,"TR_CHA_RATE":0.0100,"TR_BANK_SPP_CURRENCY":"USD","TR_BANK_SPP":1.00,"TR_CHA_SETT_BANK":null,"TR_BANKORDERNO":null,"TR_BANKRETURNCODE":null,"TR_BANKINFO":null,"TR_PAYSTARTTIME":"2020-08-18 18:16:48.139000000","TR_PAYENDTIME":null,"TR_DATETIME":"2020-08-18 18:16:48.346618000","TR_BANKDATETIME":null,"TR_RETURNURL":null,"TR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","TR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","TR_CHECKED":0,"TR_CHECKDATETIME":null,"TR_REFUNDMENT":0,"TR_PROTEST":0,"TR_CONGEAL":0,"TR_DELIVERY":null,"TR_REFUNDMENT_AMOUNT":0,"TR_PROTEST_AMOUNT":0,"TR_EXP_AMOUNT":0,"TR_CONGEAL_AMOUNT":0,"TR_UNSETT_AMOUNT":null,"TR_TS_ID":0,"TR_TS_STATUS":0,"TR_RS_ID":0,"TR_RS_STATUS":0,"TR_AS_ID":0,"TR_AS_STATUS":0,"TR_QUERYNO":null,"TR_AUTHORIZELD":null,"TR_BATCHNO":null,"TR_TERMINALNO":null,"TR_ISDCC":0,"TR_TS_BATCH":0,"TR_ISTSCHECK":0,"TR_RS_BATCH":0,"TR_ISRSCHECK":0,"TR_ISDAYCHECK":0,"TR_ISEXPLAIN":-2,"TR_ISFRAUD":0,"TR_ISCOMPLAIN":0,"TR_INF_TYPE":2,"TR_ISLOCK":0,"TR_FEE_FAIL_MER":0,"TR_FEE_SUCCESS_MER":0,"TR_FEE_SUCCESS_AFTER_MER":0,"TR_IS_BACK_MER":0,"TR_IS_BACK_AFTER_MER":0,"TR_FEE_FAIL_AGENT":0,"TR_FEE_SUCCESS_AGENT":0,"TR_FEE_SUCCESS_AFTER_AGENT":0,"TR_IS_BACK_AGENT":0,"TR_IS_BACK_AFTER_AGENT":0,"TR_FEE_FAIL_CHA":1,"TR_FEE_SUCCESS_CHA":1,"TR_FEE_SUCCESS_AFTER_CHA":1,"TR_IS_BACK_CHA":1,"TR_IS_BACK_AFTER_CHA":1,"TR_REMARK":"2fang","TR_BANKTRADETIME":null,"TR_CARDTYPE":1,"TR_REFERENCE":"200818181648559171322","TR_IS_REPAY":0,"TR_RISKINFO":null,"TR_SETSCORE":0,"TR_TOTALSCORE":98.00,"TR_PASSRISKINFO":null,"TR_PM_ID":1,"TR_AUTH_TYPE":0,"TR_SEND_CR_FLAG":null,"TR_SEND_MER_FLAG":null,"TR_REFCHAPRO_FLAG":null,"TR_CLEAR_AMOUNT":null,"TR_CLEAR_TIME":null,"TR_CLEAR_STATUS":null,"TR_BANK_CHARGES":null,"TR_CLEAR_CURRENCY":null,"TR_AUTH_OP_TIME":null,"TR_DEBITTIME":"2020-08-18 18:16:48.346618000","TR_SETT_RATE":1,"TR_SETT_AMOUNT":0.01,"TR_SETT_CURRENCY":"USD","TR_TRANRETURNCODE":null,"TR_TRANINFO":null,"TR_WHETHER_3D":"0","TR_ECI_VALUE":null,"TR_ISAUTH":0,"TR_SETTLEMENT":0,"TR_NOTICEURL":null,"TR_NOTICESTATUS":null,"TR_CHARESERVERATE":0.0100,"TR_REFUSEAMOUNT":0,"TR_REFOUNDAMOUNT":0,"TR_BAILAMOUNT":null,"TR_IS_MER_REFOUND":0,"TR_IS_MER_REFUSE":0,"TR_SETT_SPP":0,"TR_BILL_ADDRESS":"OPBANK 账单地址","TR_PAY_SOLUTIONS":null,"TR_PAY_INSTALLCOUNT":null,"TR_PAY_INSTALLRATE":null,"TR_SPP_RATE":1,"TR_RATE_INSTALLRATE":0,"TR_MER_LOGOURL":null,"TR_CLEAR_BATCHNO":null,"TR_CLEAR_REMARK":null,"TR_IS_MER_REFOUND_PART":0,"TR_BILL_ADDRESS_WHITE":null,"TR_BILL_ADDRESS_EXTRA1_WHITE":null,"TR_PRIOR_CHANNEL":0,"TR_SETTLEMENT_BANK":null,"TR_SUB_BATCHNO_BANK":null,"TR_SUB_BANK_NO":null,"TR_BANK_REMARK":null,"TR_CONTRACTTYPE":302,"TR_SETTLEMENTBODY":8,"TR_CHANNELBODY":2,"TR_SETTLEMENTAMONT":null,"TR_TSINGLEFEE":null,"TR_TREFUNDFEE":null,"TR_TRATE":null,"TR_TRATERETURN":null,"TR_TAXATION":null,"TR_MIN_TRADE_FEE":0,"TR_BANK_RATE_HKD":7.75081,"TR_SETT_RATE_HKD":7.75081,"TR_RETURN_AVS":null,"TR_RETURN_CVV":null,"TR_TYPE":1,"TR_SETT_STATUS":null,"TR_REFUND_LOCK":null,"TR_MCC":5722,"TR_UPDATE_ORDER_STATUS":0,"TR_MANUAL_LIQUIDATION_REMARK":null,"TR_OP_ENTITY":1,"TR_TRADE_BANK_NAME":null,"TR_CLEAR_RATE":null,"TR_INTERCHANGE_FEE":null,"TR_SCHEME_FEE":null,"TR_ACQUIRER_FEE":null,"TR_CLEAR_TRADE_RATE":null,"TR_CROSS_BORDER_FEE":null,"TR_AVS_OUT_RESULT":null,"TR_CAVV":null,"TR_BANK_PID":null,"TR_EXPIRATION_TIME":null,"TR_TCHARGEBACK_FEE":null,"TR_TCONGEAL_FEE":null,"TR_FXAJD_FEE":null,"TR_TOTHER_FEE":null}}{"table":"CCPS.CCPS_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.168001","pos":"00000000000026320941","before":{},"after":{"TR_NO":"200818181648559171322","TR_STATUS":1,"TR_BANKORDERNO":null,"TR_BANKRETURNCODE":"80000","TR_BANKINFO":"Transaction Approved1","TR_BANKDATETIME":"2020-08-18 18:16:48.360706000","TR_CHECKED":0,"TR_CHECKDATETIME":"2020-08-18 18:16:48.000000000","TR_QUERYNO":null,"TR_AUTHORIZELD":null,"TR_BATCHNO":null,"TR_TERMINALNO":null,"TR_AUTH_TYPE":0,"TR_DEBITTIME":"2020-08-18 18:16:48.360706000","TR_TRANRETURNCODE":"10000","TR_TRANINFO":"Transaction Approved","TR_WHETHER_3D":"0","TR_ECI_VALUE":null,"TR_NOTICESTATUS":2,"TR_PAY_SOLUTIONS":"Transaction Approved3","TR_BANK_REMARK":";;","TR_RETURN_AVS":null,"TR_RETURN_CVV":null,"TR_UPDATE_ORDER_STATUS":0}}
*/
{"table":"CCPS.CCPS_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:16:01.042146","current_ts":"2020-08-18T18:16:06.549000","pos":"00000000000026321695","before":{},"after":{"TR_NO":"160627142027150120836","TR_RS_ID":1000779,"TR_RS_STATUS":2,"TR_UPDATE_ORDER_STATUS":0}}

insert into CCPS_UNNORMAL_TRADERECORD(UTR_ID, UTR_NO, UTR_MER_NO, UTR_GW_NO, UTR_MER_ORDERNO, UTR_CURRENCY, UTR_AMOUNT, UTR_BANK_CODE, UTR_CHA_CODE, UTR_RETURNURL, UTR_WEBSITE, UTR_SUBMITURL, UTR_PAYSTARTTIME, UTR_PAYTIME, UTR_PAYENDTIME, UTR_ERRORCODE, UTR_ERRORREASON_OUT, UTR_ERRORREASON_IN, UTR_MER_REMARK, UTR_RISKINFO, UTR_SETSCORE, UTR_TOTALSCORE, UTR_PASSRISKINFO, UTR_PM_ID, UTR_CARDNO_ENCRYPT, UTR_NOTICEURL, UTR_BILL_ADDRESS_WHITE, UTR_BILL_ADDRESS_EXTRA1_WHITE, UTR_LOGO_URL, UTR_LANGUAGE, UTR_PAY_EXPIRATIONTIME, UTR_MCC, UTR_PM_NAME, UTR_TRADE_BANK_NAME, UTR_EXPIRATION_TIME) values (923628, '200818181648559171322', 191713, 19171301, '1597745803378', 'USD', '0.01', NULL, NULL, NULL, 'http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp', 'http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay', '2020-08-18 18:16:48.233971000', NULL, NULL, '50000', 'Unpaid', 'Unpaid', '2fang', NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Credit Card', NULL, NULL);
update CCPS_UNNORMAL_TRADERECORD set UTR_CARDNO_ENCRYPT = 'GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk=' where UTR_NO = '200818181648559171322'
delete from CCPS_UNNORMAL_TRADERECORD where UTR_ID = 923628
insert into CCPS_TRADERECORD(TR_ID, TR_NO, TR_MER_ORDERNO, TR_MER_NO, TR_GW_NO, TR_CURRENCY, TR_AMOUNT, TR_STATUS, TR_TRADE_RATE, TR_SPP_CURRENCY, TR_SPP, TR_AGENT_NO, TR_AGENT_RATE, TR_AGENT_SPP_CURRENCY, TR_AGENT_SPP, TR_RESEVER_RATE, TR_RATE_VALUE, TR_BANKCURRENCY, TR_BANKAMOUT, TR_BANK_CODE, TR_CHA_CODE, TR_ISDELAY, TR_CHA_RATE, TR_BANK_SPP_CURRENCY, TR_BANK_SPP, TR_CHA_SETT_BANK, TR_BANKORDERNO, TR_BANKRETURNCODE, TR_BANKINFO, TR_PAYSTARTTIME, TR_PAYENDTIME, TR_DATETIME, TR_BANKDATETIME, TR_RETURNURL, TR_WEBSITE, TR_SUBMITURL, TR_CHECKED, TR_CHECKDATETIME, TR_REFUNDMENT, TR_PROTEST, TR_CONGEAL, TR_DELIVERY, TR_REFUNDMENT_AMOUNT, TR_PROTEST_AMOUNT, TR_EXP_AMOUNT, TR_CONGEAL_AMOUNT, TR_UNSETT_AMOUNT, TR_TS_ID, TR_TS_STATUS, TR_RS_ID, TR_RS_STATUS, TR_AS_ID, TR_AS_STATUS, TR_QUERYNO, TR_AUTHORIZELD, TR_BATCHNO, TR_TERMINALNO, TR_ISDCC, TR_TS_BATCH, TR_ISTSCHECK, TR_RS_BATCH, TR_ISRSCHECK, TR_ISDAYCHECK, TR_ISEXPLAIN, TR_ISFRAUD, TR_ISCOMPLAIN, TR_INF_TYPE, TR_ISLOCK, TR_FEE_FAIL_MER, TR_FEE_SUCCESS_MER, TR_FEE_SUCCESS_AFTER_MER, TR_IS_BACK_MER, TR_IS_BACK_AFTER_MER, TR_FEE_FAIL_AGENT, TR_FEE_SUCCESS_AGENT, TR_FEE_SUCCESS_AFTER_AGENT, TR_IS_BACK_AGENT, TR_IS_BACK_AFTER_AGENT, TR_FEE_FAIL_CHA, TR_FEE_SUCCESS_CHA, TR_FEE_SUCCESS_AFTER_CHA, TR_IS_BACK_CHA, TR_IS_BACK_AFTER_CHA, TR_REMARK, TR_BANKTRADETIME, TR_CARDTYPE, TR_REFERENCE, TR_IS_REPAY, TR_RISKINFO, TR_SETSCORE, TR_TOTALSCORE, TR_PASSRISKINFO, TR_PM_ID, TR_AUTH_TYPE, TR_SEND_CR_FLAG, TR_SEND_MER_FLAG, TR_REFCHAPRO_FLAG, TR_CLEAR_AMOUNT, TR_CLEAR_TIME, TR_CLEAR_STATUS, TR_BANK_CHARGES, TR_CLEAR_CURRENCY, TR_AUTH_OP_TIME, TR_DEBITTIME, TR_SETT_RATE, TR_SETT_AMOUNT, TR_SETT_CURRENCY, TR_TRANRETURNCODE, TR_TRANINFO, TR_WHETHER_3D, TR_ECI_VALUE, TR_ISAUTH, TR_SETTLEMENT, TR_NOTICEURL, TR_NOTICESTATUS, TR_CHARESERVERATE, TR_REFUSEAMOUNT, TR_REFOUNDAMOUNT, TR_BAILAMOUNT, TR_IS_MER_REFOUND, TR_IS_MER_REFUSE, TR_SETT_SPP, TR_BILL_ADDRESS, TR_PAY_SOLUTIONS, TR_PAY_INSTALLCOUNT, TR_PAY_INSTALLRATE, TR_SPP_RATE, TR_RATE_INSTALLRATE, TR_MER_LOGOURL, TR_CLEAR_BATCHNO, TR_CLEAR_REMARK, TR_IS_MER_REFOUND_PART, TR_BILL_ADDRESS_WHITE, TR_BILL_ADDRESS_EXTRA1_WHITE, TR_PRIOR_CHANNEL, TR_SETTLEMENT_BANK, TR_SUB_BATCHNO_BANK, TR_SUB_BANK_NO, TR_BANK_REMARK, TR_CONTRACTTYPE, TR_SETTLEMENTBODY, TR_CHANNELBODY, TR_SETTLEMENTAMONT, TR_TSINGLEFEE, TR_TREFUNDFEE, TR_TRATE, TR_TRATERETURN, TR_TAXATION, TR_MIN_TRADE_FEE, TR_BANK_RATE_HKD, TR_SETT_RATE_HKD, TR_RETURN_AVS, TR_RETURN_CVV, TR_TYPE, TR_SETT_STATUS, TR_REFUND_LOCK, TR_MCC, TR_UPDATE_ORDER_STATUS, TR_MANUAL_LIQUIDATION_REMARK, TR_OP_ENTITY, TR_TRADE_BANK_NAME, TR_CLEAR_RATE, TR_INTERCHANGE_FEE, TR_SCHEME_FEE, TR_ACQUIRER_FEE, TR_CLEAR_TRADE_RATE, TR_CROSS_BORDER_FEE, TR_AVS_OUT_RESULT, TR_CAVV, TR_BANK_PID, TR_EXPIRATION_TIME, TR_TCHARGEBACK_FEE, TR_TCONGEAL_FEE, TR_FXAJD_FEE, TR_TOTHER_FEE) values (787160, '200818181648559171322', '1597745803378', 191713, 19171301, 'USD', 0.01, -1, 0.01, 'USD', 0, 0, NULL, NULL, NULL, 0.1, 1, 'USD', 0.01, 'OPBank', 121, 0, 0.01, 'USD', 1.0, NULL, NULL, NULL, NULL, '2020-08-18 18:16:48.139000000', NULL, '2020-08-18 18:16:48.346618000', NULL, NULL, 'http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp', 'http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay', 0, NULL, 0, 0, 0, NULL, 0, 0, 0, 0, NULL, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, -2, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, '2fang', NULL, 1, '200818181648559171322', 0, NULL, 0, 98.0, NULL, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-08-18 18:16:48.346618000', 1, 0.01, 'USD', NULL, NULL, '0', NULL, 0, 0, NULL, NULL, 0.01, 0, 0, NULL, 0, 0, 0, 'OPBANK 账单地址', NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL, NULL, 302, 8, 2, NULL, NULL, NULL, NULL, NULL, NULL, 0, 7.75081, 7.75081, NULL, NULL, 1, NULL, NULL, 5722, 0, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
update CCPS_TRADERECORD set TR_STATUS = 1,TR_BANKORDERNO = NULL,TR_BANKRETURNCODE = '80000',TR_BANKINFO = 'Transaction Approved1',TR_BANKDATETIME = '2020-08-18 18:16:48.360706000',TR_CHECKED = 0,TR_CHECKDATETIME = '2020-08-18 18:16:48.000000000',TR_QUERYNO = NULL,TR_AUTHORIZELD = NULL,TR_BATCHNO = NULL,TR_TERMINALNO = NULL,TR_AUTH_TYPE = 0,TR_DEBITTIME = '2020-08-18 18:16:48.360706000',TR_TRANRETURNCODE = '10000',TR_TRANINFO = 'Transaction Approved',TR_WHETHER_3D = '0',TR_ECI_VALUE = NULL,TR_NOTICESTATUS = 2,TR_PAY_SOLUTIONS = 'Transaction Approved3',TR_BANK_REMARK = ';;',TR_RETURN_AVS = NULL,TR_RETURN_CVV = NULL,TR_UPDATE_ORDER_STATUS = 0 where TR_NO = '200818181648559171322'
update CCPS_TRADERECORD set TR_RS_ID = 1000779,TR_RS_STATUS = 2,TR_UPDATE_ORDER_STATUS = 0 where TR_NO = '160627142027150120836'

devin=> select TR_STATUS,TR_BANKRETURNCODE,TR_BANKDATETIME,TR_CHECKDATETIME,TR_TRANINFO,TR_BANK_REMARK from CCPS_TRADERECORD where TR_NO = '200818181648559171322';
 tr_status | tr_bankreturncode |      tr_bankdatetime       |  tr_checkdatetime   |     tr_traninfo      | tr_bank_remark 
-----------+-------------------+----------------------------+---------------------+----------------------+----------------
         1 | 80000             | 2020-08-18 18:16:48.360706 | 2020-08-18 18:16:48 | Transaction Approved | ;;
(1 row)