-- 按 照票号+出票日期 取最新的数据
create table test.djj_temp_hsd_iets as
select 
    *
from  
(
select 
    a.stamp,
    a.dttm,
    a.ticketidentification_tktissuedate,
    a.ticketimage_ticketnumber,
    a.itinerary_ticketcoupon,
    row_number() over(partition by a.ticketidentification_tktissuedate, a.ticketimage_ticketnumber order by a.dttm desc) r
from 
    ods.cst_hsd_iets a
) t  
where  r=1 
;
    
    
-- 拆分json
drop table test.djj_temp_hsd_iets_01;    
create table test.djj_temp_hsd_iets_01 as
select 
    a.stamp,
    a.dttm,
    a.ticketidentification_tktissuedate issuedate, -- 出票日期
    substr(a.ticketimage_ticketnumber,1,13) ticketnumber, --票号
    get_json_object(itinerary, '$.CouponNumber') couponNumber,
    get_json_object(itinerary, '$.CouponStatus') couponStatus, -- 客票状态
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.AirlineCode') carrier_airlinecode, 
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.FlightNumber'), '$.Value') carrier_flightnumber,
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.FlightNumber'), '$.Suffix') carrier_flightnumber_suffix,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.AirlineCode') operating_airlinecode, -- 承运航司
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.FlightNumber'), '$.Value') operating_flightnumber, -- 承运航班号
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.FlightNumber'), '$.Suffix') operating_flightnumber_suffix, -- 承运航班号后缀
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Departure'), '$.Date') departure_date,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Departure'), '$.AirportCode') departure_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Arrival'), '$.AirportCode') arrival_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Open'), '$.OpenDeparture'), '$.AirportCode') open_departure_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Open'), '$.OpenArrival'), '$.AirportCode') open_arrival_airportcode,
    get_json_object(get_json_object(itinerary, '$.Open'), '$.OperatingCarrier') open_operatingcarrier
from 
    test.djj_temp_hsd_iets a
    LATERAL VIEW explode(split(
     regexp_replace(
         regexp_replace(
            itinerary_ticketcoupon, 
            '\\[|\\]' , ''),-- 将json数组两边的中括号去掉
            
              '\\}\\,\\{' , '\\}\\;\\{'),-- 将json数组元素之间的逗号换成分号
                
                 '\\;')-- 以分号作为分隔符(split函数以分号作为分隔)
          )  a as itinerary
    ;
    

-- 合并出发到达
drop table test.djj_temp_hsd_iets_02;    
create table test.djj_temp_hsd_iets_02 as    
select 
    a.issuedate,
    a.ticketnumber,
    a.couponnumber,
    a.couponstatus,
    coalesce(a.departure_airportcode, a.open_departure_airportcode) departure_airportcode,
    coalesce(a.arrival_airportcode, a.open_arrival_airportcode) arrival_airportcode
from  
    test.djj_temp_hsd_iets_01 a
    ;
    

-- 匹配出发到达城市    
drop table test.djj_temp_hsd_iets_03;    
create table test.djj_temp_hsd_iets_03 as     
select 
    a.issuedate,
    a.ticketnumber,
    a.couponnumber,
    a.couponstatus,
    a.departure_airportcode,
    a.arrival_airportcode,
    b.city_code departure_citycode,
    c.city_code arrival_citycode,
    coalesce(b.city_code, a.departure_airportcode) departure, -- 以防有的机场匹配不到城市
    coalesce(c.city_code, a.arrival_airportcode) arrival -- 以防有的机场匹配不到城市
from 
    test.djj_temp_hsd_iets_02  a 
left join 
    ods.t_airport_info_zp b on a.departure_airportcode=b.airport_code and b.dp='ACTIVE'
left join 
    ods.t_airport_info_zp c on a.arrival_airportcode=c.airport_code and c.dp='ACTIVE'
where 
    a.couponstatus<>'V'  -- 排除废票
    ;
    


-- 匹配出发到达城市    
drop table test.djj_temp_hsd_iets_04;    
create table test.djj_temp_hsd_iets_04 as      
select 
    t.issuedate,
    t.ticketnumber,
    t.seg_num,
    substr(origin,1,3) origin,
    substr(destination,-3,3) destination
from  
(
select 
    a.issuedate,
    a.ticketnumber,
    sum(1) over(partition by  a.issuedate, a.ticketnumber) seg_num, -- 总航段数
    concat_ws(',', collect_list(departure) over(partition by a.issuedate, a.ticketnumber order by a.couponnumber)) as origin,
    concat_ws(',', collect_list(arrival) over(partition by a.issuedate, a.ticketnumber order by a.couponnumber)) as destination,
    row_number() over(partition by a.issuedate, a.ticketnumber order by a.couponnumber desc) as r
from  
    test.djj_temp_hsd_iets_03 a
) t 
where 
    r=1
    and substr(origin,1,3)<>substr(destination,-3,3) -- 排除往返票
    and seg_num>1 -- 筛选多段的
    ;
    
    
select 
    issuedate,
    ticketnumber,
    seg_num,
    origin,
    destination
from  
    test.djj_temp_hsd_iets_04 a
    ;

-- 第二批
CREATE TABLE KAFKA_SOURCE_FIN_NC_BD_PSNDOC (
    op_type String,
    op_ts String,
    op_ts_time as cast(op_ts as timestamp(3)),
    current_ts String,
    pos String,
    before row<`PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` String,
    `AMCODE` String,
    `TS` String,
    `DR` String,
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` String,
    `TBM_PROP` String,
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String >,
    after row<`PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` String,
    `AMCODE` String,
    `TS` String,
    `DR` String,
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` String,
    `TBM_PROP` String,
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String >,
    data_row as
    case
    when op_type = 'D' then before
    else after
    end,
    watermark for op_ts_time as op_ts_time - interval '5' second
) with (
    'connector' = 'kafka',
    'topic' = 'SEM.NC.JXNC57.BD_PSNDOC',
    'scan.startup.mode' = 'earliest-offset',
    'properties.group.id' = 'pipeline_kafka2doris_3_test',
    'properties.bootstrap.servers' = 'jxbigdatakafka01.juneyaoair.com:9092,jxbigdatakafka02.juneyaoair.com:9092,jxbigdatakafka03.juneyaoair.com:9092',
    'properties.fetch.max.bytes' = '5242880',
    'properties.allow.auto.create.topics' = 'false',
    'properties.enable.auto.commit' = 'true',
    'properties.sasl.jaas.config' = 'org.apache.kafka.common.security.scram.ScramLoginModule required username="bigdatauser" password="bigdata@gd66";',
    'properties.sasl.mechanism' = 'PLAIN',
    'properties.security.protocol' = 'SASL_PLAINTEXT',
    'format' = 'json'
);

CREATE TABLE DORIS_SINK_FIN_NC_BD_PSNDOC (
    `PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` DECIMAL,
    `AMCODE` String,
    `TS` String,
    `DR` DECIMAL(10,0),
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` DECIMAL(38,0),
    `TBM_PROP` DECIMAL(38,0),
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String,
    `OP_TYPE` String,
    `OP_TS` String,
    `OGG_TIME` String,
    `GMT_CREATE` TIMESTAMP,
    PRIMARY KEY (`PK_PSNDOC`) NOT ENFORCED
) with (
    'connector' = 'doris',
    'fenodes' = '10.17.23.55:8030',
    'table.identifier' = 'ods.ods_fin_nc_bd_psndoc',
    'username' = 'juneyaoair_etl',
    'password' = 'juneyaoair_etl123!'
);

insert into DORIS_SINK_FIN_NC_BD_PSNDOC
select
    REPLACE(REPLACE(REPLACE(data_row.`PK_PSNDOC`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNDOC`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_CORP`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_CORP`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_PSNCL`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNCL`,
    REPLACE(REPLACE(REPLACE(data_row.`PSNCODE`,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNCODE`,
    REPLACE(REPLACE(REPLACE(data_row.`PSNNAME`,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNNAME`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_DEPTDOC`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_DEPTDOC`,
    REPLACE(REPLACE(REPLACE(data_row.`CLERKFLAG`,CHR(10),''),CHR(13),''),CHR(9),'') as `CLERKFLAG`,
    REPLACE(REPLACE(REPLACE(data_row.`CLERKCODE`,CHR(10),''),CHR(13),''),CHR(9),'') as `CLERKCODE`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_OM_JOB`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_OM_JOB`,
    REPLACE(REPLACE(REPLACE(data_row.`INDOCFLAG`,CHR(10),''),CHR(13),''),CHR(9),'') as `INDOCFLAG`,
    CAST(REPLACE(REPLACE(REPLACE(data_row.`PSNCLSCOPE`,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `PSNCLSCOPE`,
    REPLACE(REPLACE(REPLACE(data_row.`AMCODE`,CHR(10),''),CHR(13),''),CHR(9),'') as `AMCODE`,
    REPLACE(REPLACE(REPLACE(data_row.`TS`,CHR(10),''),CHR(13),''),CHR(9),'') as `TS`,
    CAST(REPLACE(REPLACE(REPLACE(data_row.`DR`,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `DR`,
    REPLACE(REPLACE(REPLACE(data_row.`SEALDATE`,CHR(10),''),CHR(13),''),CHR(9),'') as `SEALDATE`,
    REPLACE(REPLACE(REPLACE(data_row.`INNERCODE`,CHR(10),''),CHR(13),''),CHR(9),'') as `INNERCODE`,
    REPLACE(REPLACE(REPLACE(data_row.`MAXINNERCODE`,CHR(10),''),CHR(13),''),CHR(9),'') as `MAXINNERCODE`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF1`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF1`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF10`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF10`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF11`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF11`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF12`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF12`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF13`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF13`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF14`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF14`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF15`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF15`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF16`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF16`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF17`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF17`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF18`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF18`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF19`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF19`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF2`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF2`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF20`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF20`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF3`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF3`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF4`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF4`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF5`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF5`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF6`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF6`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF7`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF7`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF8`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF8`,
    REPLACE(REPLACE(REPLACE(data_row.`DEF9`,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF9`,
    REPLACE(REPLACE(REPLACE(data_row.`INDUTYDATE`,CHR(10),''),CHR(13),''),CHR(9),'') as `INDUTYDATE`,
    REPLACE(REPLACE(REPLACE(data_row.`JOBRANK`,CHR(10),''),CHR(13),''),CHR(9),'') as `JOBRANK`,
    REPLACE(REPLACE(REPLACE(data_row.`JOBSERIES`,CHR(10),''),CHR(13),''),CHR(9),'') as `JOBSERIES`,
    REPLACE(REPLACE(REPLACE(data_row.`OUTDUTYDATE`,CHR(10),''),CHR(13),''),CHR(9),'') as `OUTDUTYDATE`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_PSNBASDOC`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNBASDOC`,
    REPLACE(REPLACE(REPLACE(data_row.`DIRECTLEADER`,CHR(10),''),CHR(13),''),CHR(9),'') as `DIRECTLEADER`,
    REPLACE(REPLACE(REPLACE(data_row.`DUTYNAME`,CHR(10),''),CHR(13),''),CHR(9),'') as `DUTYNAME`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF1`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF1`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF10`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF10`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF11`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF11`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF12`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF12`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF13`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF13`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF14`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF14`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF15`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF15`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF16`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF16`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF17`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF17`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF18`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF18`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF19`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF19`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF2`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF2`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF20`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF20`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF3`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF3`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF4`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF4`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF5`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF5`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF6`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF6`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF7`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF7`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF8`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF8`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF9`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF9`,
    REPLACE(REPLACE(REPLACE(data_row.`INSOURCE`,CHR(10),''),CHR(13),''),CHR(9),'') as `INSOURCE`,
    REPLACE(REPLACE(REPLACE(data_row.`ISCALOVERTIME`,CHR(10),''),CHR(13),''),CHR(9),'') as `ISCALOVERTIME`,
    REPLACE(REPLACE(REPLACE(data_row.`OUTMETHOD`,CHR(10),''),CHR(13),''),CHR(9),'') as `OUTMETHOD`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_CLERKCLASS`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_CLERKCLASS`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_DUTYRANK`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_DUTYRANK`,
    REPLACE(REPLACE(REPLACE(data_row.`PK_PSNTYPE`,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNTYPE`,
    REPLACE(REPLACE(REPLACE(data_row.`POSTSTAT`,CHR(10),''),CHR(13),''),CHR(9),'') as `POSTSTAT`,
    REPLACE(REPLACE(REPLACE(data_row.`RECRUITRESOURCE`,CHR(10),''),CHR(13),''),CHR(9),'') as `RECRUITRESOURCE`,
    REPLACE(REPLACE(REPLACE(data_row.`REGULAR`,CHR(10),''),CHR(13),''),CHR(9),'') as `REGULAR`,
    REPLACE(REPLACE(REPLACE(data_row.`REGULARDATA`,CHR(10),''),CHR(13),''),CHR(9),'') as `REGULARDATA`,
    REPLACE(REPLACE(REPLACE(data_row.`SERIES`,CHR(10),''),CHR(13),''),CHR(9),'') as `SERIES`,
    CAST(REPLACE(REPLACE(REPLACE(data_row.`SHOWORDER`,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `SHOWORDER`,
    CAST(REPLACE(REPLACE(REPLACE(data_row.`TBM_PROP`,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `TBM_PROP`,
    REPLACE(REPLACE(REPLACE(data_row.`TIMECARDID`,CHR(10),''),CHR(13),''),CHR(9),'') as `TIMECARDID`,
    REPLACE(REPLACE(REPLACE(data_row.`WASTOPDATE`,CHR(10),''),CHR(13),''),CHR(9),'') as `WASTOPDATE`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF1`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF1`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF2`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF2`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF3`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF3`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF4`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF4`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF5`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF5`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF6`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF6`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF7`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF7`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF8`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF8`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF9`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF9`,
    REPLACE(REPLACE(REPLACE(data_row.`CORPDEF10`,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF10`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF21`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF21`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF22`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF22`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF23`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF23`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF24`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF24`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF25`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF25`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF26`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF26`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF27`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF27`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF28`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF28`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF29`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF29`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF30`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF30`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF31`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF31`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF32`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF32`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF33`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF33`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF34`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF34`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF35`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF35`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF36`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF36`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF37`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF37`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF38`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF38`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF39`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF39`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF40`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF40`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF41`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF41`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF42`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF42`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF43`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF43`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF44`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF44`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF45`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF45`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF46`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF46`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF47`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF47`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF48`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF48`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF49`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF49`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF50`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF50`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF51`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF51`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF52`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF52`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF53`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF53`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF54`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF54`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF55`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF55`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF56`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF56`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF57`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF57`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF58`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF58`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF59`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF59`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF60`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF60`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF61`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF61`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF62`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF62`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF63`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF63`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF64`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF64`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF65`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF65`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF66`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF66`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF67`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF67`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF68`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF68`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF69`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF69`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF70`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF70`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF71`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF71`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF72`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF72`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF73`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF73`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF74`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF74`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF75`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF75`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF76`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF76`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF77`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF77`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF78`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF78`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF79`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF79`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF80`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF80`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF81`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF81`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF82`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF82`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF83`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF83`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF84`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF84`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF85`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF85`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF86`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF86`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF87`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF87`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF88`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF88`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF89`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF89`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF90`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF90`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF91`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF91`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF92`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF92`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF93`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF93`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF94`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF94`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF95`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF95`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF96`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF96`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF97`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF97`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF98`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF98`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF99`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF99`,
    REPLACE(REPLACE(REPLACE(data_row.`GROUPDEF100`,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF100`,
    REPLACE(REPLACE(REPLACE(data_row.`ISRETURN`,CHR(10),''),CHR(13),''),CHR(9),'') as `ISRETURN`,
    REPLACE(REPLACE(REPLACE(data_row.`ISREFERENCED`,CHR(10),''),CHR(13),''),CHR(9),'') as `ISREFERENCED`,
    REPLACE(REPLACE(REPLACE(data_row.`ONPOSTDATE`,CHR(10),''),CHR(13),''),CHR(9),'') as `ONPOSTDATE`,
    REPLACE(REPLACE(REPLACE(data_row.`CREATETIME`,CHR(10),''),CHR(13),''),CHR(9),'') as `CREATETIME`,
    REPLACE(REPLACE(REPLACE(data_row.`CREATOR`,CHR(10),''),CHR(13),''),CHR(9),'') as `CREATOR`,
    REPLACE(REPLACE(REPLACE(data_row.`MODIFIER`,CHR(10),''),CHR(13),''),CHR(9),'') as `MODIFIER`,
    REPLACE(REPLACE(REPLACE(data_row.`MODIFYTIME`,CHR(10),''),CHR(13),''),CHR(9),'') as `MODIFYTIME`,
    REPLACE(REPLACE(REPLACE(data_row.`HROPERATOR`,CHR(10),''),CHR(13),''),CHR(9),'') as `HROPERATOR`,
    REPLACE(REPLACE(REPLACE(data_row.`PSNNAMEPINYIN`,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNNAMEPINYIN`,
    `op_type` as `OP_TYPE`,
    `op_ts` as `OP_TS`,
    `current_ts` as `OGG_TIME`,
    CURRENT_TIMESTAMP as `GMT_CREATE`
from KAFKA_SOURCE_FIN_NC_BD_PSNDOC;

-- 第二批
CREATE TABLE KAFKA_SOURCE_FIN_NC_BD_PSNDOC (
    op_type String,
    op_ts String,
    op_ts_time as cast(op_ts as timestamp(3)),
    current_ts String,
    pos String,
    before row<`PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` String,
    `AMCODE` String,
    `TS` String,
    `DR` String,
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` String,
    `TBM_PROP` String,
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String >,
    after row<`PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` String,
    `AMCODE` String,
    `TS` String,
    `DR` String,
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` String,
    `TBM_PROP` String,
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String >,
    data_row as
    case
    when op_type = 'D' then before
    else after
    end,
    watermark for op_ts_time as op_ts_time - interval '5' second
) with (
    'connector' = 'kafka',
    'topic' = 'SEM.NC.JXNC57.BD_PSNDOC',
    'scan.startup.mode' = 'earliest-offset',
    'properties.group.id' = 'pipeline_kafka2doris_3_test',
    'properties.bootstrap.servers' = 'jxbigdatakafka01.juneyaoair.com:9092,jxbigdatakafka02.juneyaoair.com:9092,jxbigdatakafka03.juneyaoair.com:9092',
    'properties.fetch.max.bytes' = '5242880',
    'properties.allow.auto.create.topics' = 'false',
    'properties.enable.auto.commit' = 'true',
    'properties.sasl.jaas.config' = 'org.apache.kafka.common.security.scram.ScramLoginModule required username="bigdatauser" password="bigdata@gd66";',
    'properties.sasl.mechanism' = 'PLAIN',
    'properties.security.protocol' = 'SASL_PLAINTEXT',
    'format' = 'json'
);

CREATE TABLE DORIS_SINK_FIN_NC_BD_PSNDOC (
    `PK_PSNDOC` String,
    `PK_CORP` String,
    `PK_PSNCL` String,
    `PSNCODE` String,
    `PSNNAME` String,
    `PK_DEPTDOC` String,
    `CLERKFLAG` String,
    `CLERKCODE` String,
    `PK_OM_JOB` String,
    `INDOCFLAG` String,
    `PSNCLSCOPE` DECIMAL,
    `AMCODE` String,
    `TS` String,
    `DR` DECIMAL(10,0),
    `SEALDATE` String,
    `INNERCODE` String,
    `MAXINNERCODE` String,
    `DEF1` String,
    `DEF10` String,
    `DEF11` String,
    `DEF12` String,
    `DEF13` String,
    `DEF14` String,
    `DEF15` String,
    `DEF16` String,
    `DEF17` String,
    `DEF18` String,
    `DEF19` String,
    `DEF2` String,
    `DEF20` String,
    `DEF3` String,
    `DEF4` String,
    `DEF5` String,
    `DEF6` String,
    `DEF7` String,
    `DEF8` String,
    `DEF9` String,
    `INDUTYDATE` String,
    `JOBRANK` String,
    `JOBSERIES` String,
    `OUTDUTYDATE` String,
    `PK_PSNBASDOC` String,
    `DIRECTLEADER` String,
    `DUTYNAME` String,
    `GROUPDEF1` String,
    `GROUPDEF10` String,
    `GROUPDEF11` String,
    `GROUPDEF12` String,
    `GROUPDEF13` String,
    `GROUPDEF14` String,
    `GROUPDEF15` String,
    `GROUPDEF16` String,
    `GROUPDEF17` String,
    `GROUPDEF18` String,
    `GROUPDEF19` String,
    `GROUPDEF2` String,
    `GROUPDEF20` String,
    `GROUPDEF3` String,
    `GROUPDEF4` String,
    `GROUPDEF5` String,
    `GROUPDEF6` String,
    `GROUPDEF7` String,
    `GROUPDEF8` String,
    `GROUPDEF9` String,
    `INSOURCE` String,
    `ISCALOVERTIME` String,
    `OUTMETHOD` String,
    `PK_CLERKCLASS` String,
    `PK_DUTYRANK` String,
    `PK_PSNTYPE` String,
    `POSTSTAT` String,
    `RECRUITRESOURCE` String,
    `REGULAR` String,
    `REGULARDATA` String,
    `SERIES` String,
    `SHOWORDER` DECIMAL(38,0),
    `TBM_PROP` DECIMAL(38,0),
    `TIMECARDID` String,
    `WASTOPDATE` String,
    `CORPDEF1` String,
    `CORPDEF2` String,
    `CORPDEF3` String,
    `CORPDEF4` String,
    `CORPDEF5` String,
    `CORPDEF6` String,
    `CORPDEF7` String,
    `CORPDEF8` String,
    `CORPDEF9` String,
    `CORPDEF10` String,
    `GROUPDEF21` String,
    `GROUPDEF22` String,
    `GROUPDEF23` String,
    `GROUPDEF24` String,
    `GROUPDEF25` String,
    `GROUPDEF26` String,
    `GROUPDEF27` String,
    `GROUPDEF28` String,
    `GROUPDEF29` String,
    `GROUPDEF30` String,
    `GROUPDEF31` String,
    `GROUPDEF32` String,
    `GROUPDEF33` String,
    `GROUPDEF34` String,
    `GROUPDEF35` String,
    `GROUPDEF36` String,
    `GROUPDEF37` String,
    `GROUPDEF38` String,
    `GROUPDEF39` String,
    `GROUPDEF40` String,
    `GROUPDEF41` String,
    `GROUPDEF42` String,
    `GROUPDEF43` String,
    `GROUPDEF44` String,
    `GROUPDEF45` String,
    `GROUPDEF46` String,
    `GROUPDEF47` String,
    `GROUPDEF48` String,
    `GROUPDEF49` String,
    `GROUPDEF50` String,
    `GROUPDEF51` String,
    `GROUPDEF52` String,
    `GROUPDEF53` String,
    `GROUPDEF54` String,
    `GROUPDEF55` String,
    `GROUPDEF56` String,
    `GROUPDEF57` String,
    `GROUPDEF58` String,
    `GROUPDEF59` String,
    `GROUPDEF60` String,
    `GROUPDEF61` String,
    `GROUPDEF62` String,
    `GROUPDEF63` String,
    `GROUPDEF64` String,
    `GROUPDEF65` String,
    `GROUPDEF66` String,
    `GROUPDEF67` String,
    `GROUPDEF68` String,
    `GROUPDEF69` String,
    `GROUPDEF70` String,
    `GROUPDEF71` String,
    `GROUPDEF72` String,
    `GROUPDEF73` String,
    `GROUPDEF74` String,
    `GROUPDEF75` String,
    `GROUPDEF76` String,
    `GROUPDEF77` String,
    `GROUPDEF78` String,
    `GROUPDEF79` String,
    `GROUPDEF80` String,
    `GROUPDEF81` String,
    `GROUPDEF82` String,
    `GROUPDEF83` String,
    `GROUPDEF84` String,
    `GROUPDEF85` String,
    `GROUPDEF86` String,
    `GROUPDEF87` String,
    `GROUPDEF88` String,
    `GROUPDEF89` String,
    `GROUPDEF90` String,
    `GROUPDEF91` String,
    `GROUPDEF92` String,
    `GROUPDEF93` String,
    `GROUPDEF94` String,
    `GROUPDEF95` String,
    `GROUPDEF96` String,
    `GROUPDEF97` String,
    `GROUPDEF98` String,
    `GROUPDEF99` String,
    `GROUPDEF100` String,
    `ISRETURN` String,
    `ISREFERENCED` String,
    `ONPOSTDATE` String,
    `CREATETIME` String,
    `CREATOR` String,
    `MODIFIER` String,
    `MODIFYTIME` String,
    `HROPERATOR` String,
    `PSNNAMEPINYIN` String,
    `OP_TYPE` String,
    `OP_TS` String,
    `OGG_TIME` String,
    `GMT_CREATE` TIMESTAMP,
    PRIMARY KEY (`PK_PSNDOC`) NOT ENFORCED
) with (
    'connector' = 'doris',
    'fenodes' = '10.17.23.55:8030',
    'table.identifier' = 'ods.ods_fin_nc_bd_psndoc',
    'username' = 'juneyaoair_etl',
    'password' = 'juneyaoair_etl123!'
);

insert into DORIS_SINK_FIN_NC_BD_PSNDOC
select
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_PSNDOC` else after.`PK_PSNDOC` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNDOC`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_CORP` else after.`PK_CORP` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_CORP`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_PSNCL` else after.`PK_PSNCL` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNCL`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PSNCODE` else after.`PSNCODE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNCODE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PSNNAME` else after.`PSNNAME` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNNAME`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_DEPTDOC` else after.`PK_DEPTDOC` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_DEPTDOC`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CLERKFLAG` else after.`CLERKFLAG` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CLERKFLAG`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CLERKCODE` else after.`CLERKCODE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CLERKCODE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_OM_JOB` else after.`PK_OM_JOB` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_OM_JOB`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`INDOCFLAG` else after.`INDOCFLAG` end,CHR(10),''),CHR(13),''),CHR(9),'') as `INDOCFLAG`,
    CAST(REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PSNCLSCOPE` else after.`PSNCLSCOPE` end,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `PSNCLSCOPE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`AMCODE` else after.`AMCODE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `AMCODE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`TS` else after.`TS` end,CHR(10),''),CHR(13),''),CHR(9),'') as `TS`,
    CAST(REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DR` else after.`DR` end,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `DR`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`SEALDATE` else after.`SEALDATE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `SEALDATE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`INNERCODE` else after.`INNERCODE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `INNERCODE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`MAXINNERCODE` else after.`MAXINNERCODE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `MAXINNERCODE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF1` else after.`DEF1` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF1`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF10` else after.`DEF10` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF10`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF11` else after.`DEF11` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF11`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF12` else after.`DEF12` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF12`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF13` else after.`DEF13` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF13`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF14` else after.`DEF14` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF14`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF15` else after.`DEF15` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF15`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF16` else after.`DEF16` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF16`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF17` else after.`DEF17` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF17`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF18` else after.`DEF18` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF18`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF19` else after.`DEF19` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF19`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF2` else after.`DEF2` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF2`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF20` else after.`DEF20` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF20`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF3` else after.`DEF3` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF3`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF4` else after.`DEF4` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF4`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF5` else after.`DEF5` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF5`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF6` else after.`DEF6` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF6`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF7` else after.`DEF7` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF7`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF8` else after.`DEF8` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF8`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DEF9` else after.`DEF9` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DEF9`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`INDUTYDATE` else after.`INDUTYDATE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `INDUTYDATE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`JOBRANK` else after.`JOBRANK` end,CHR(10),''),CHR(13),''),CHR(9),'') as `JOBRANK`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`JOBSERIES` else after.`JOBSERIES` end,CHR(10),''),CHR(13),''),CHR(9),'') as `JOBSERIES`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`OUTDUTYDATE` else after.`OUTDUTYDATE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `OUTDUTYDATE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_PSNBASDOC` else after.`PK_PSNBASDOC` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNBASDOC`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DIRECTLEADER` else after.`DIRECTLEADER` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DIRECTLEADER`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`DUTYNAME` else after.`DUTYNAME` end,CHR(10),''),CHR(13),''),CHR(9),'') as `DUTYNAME`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF1` else after.`GROUPDEF1` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF1`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF10` else after.`GROUPDEF10` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF10`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF11` else after.`GROUPDEF11` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF11`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF12` else after.`GROUPDEF12` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF12`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF13` else after.`GROUPDEF13` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF13`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF14` else after.`GROUPDEF14` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF14`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF15` else after.`GROUPDEF15` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF15`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF16` else after.`GROUPDEF16` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF16`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF17` else after.`GROUPDEF17` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF17`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF18` else after.`GROUPDEF18` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF18`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF19` else after.`GROUPDEF19` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF19`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF2` else after.`GROUPDEF2` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF2`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF20` else after.`GROUPDEF20` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF20`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF3` else after.`GROUPDEF3` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF3`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF4` else after.`GROUPDEF4` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF4`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF5` else after.`GROUPDEF5` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF5`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF6` else after.`GROUPDEF6` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF6`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF7` else after.`GROUPDEF7` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF7`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF8` else after.`GROUPDEF8` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF8`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF9` else after.`GROUPDEF9` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF9`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`INSOURCE` else after.`INSOURCE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `INSOURCE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`ISCALOVERTIME` else after.`ISCALOVERTIME` end,CHR(10),''),CHR(13),''),CHR(9),'') as `ISCALOVERTIME`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`OUTMETHOD` else after.`OUTMETHOD` end,CHR(10),''),CHR(13),''),CHR(9),'') as `OUTMETHOD`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_CLERKCLASS` else after.`PK_CLERKCLASS` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_CLERKCLASS`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_DUTYRANK` else after.`PK_DUTYRANK` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_DUTYRANK`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PK_PSNTYPE` else after.`PK_PSNTYPE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PK_PSNTYPE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`POSTSTAT` else after.`POSTSTAT` end,CHR(10),''),CHR(13),''),CHR(9),'') as `POSTSTAT`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`RECRUITRESOURCE` else after.`RECRUITRESOURCE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `RECRUITRESOURCE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`REGULAR` else after.`REGULAR` end,CHR(10),''),CHR(13),''),CHR(9),'') as `REGULAR`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`REGULARDATA` else after.`REGULARDATA` end,CHR(10),''),CHR(13),''),CHR(9),'') as `REGULARDATA`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`SERIES` else after.`SERIES` end,CHR(10),''),CHR(13),''),CHR(9),'') as `SERIES`,
    CAST(REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`SHOWORDER` else after.`SHOWORDER` end,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `SHOWORDER`,
    CAST(REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`TBM_PROP` else after.`TBM_PROP` end,CHR(10),''),CHR(13),''),CHR(9),'') AS DECIMAL) as `TBM_PROP`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`TIMECARDID` else after.`TIMECARDID` end,CHR(10),''),CHR(13),''),CHR(9),'') as `TIMECARDID`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`WASTOPDATE` else after.`WASTOPDATE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `WASTOPDATE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF1` else after.`CORPDEF1` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF1`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF2` else after.`CORPDEF2` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF2`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF3` else after.`CORPDEF3` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF3`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF4` else after.`CORPDEF4` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF4`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF5` else after.`CORPDEF5` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF5`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF6` else after.`CORPDEF6` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF6`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF7` else after.`CORPDEF7` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF7`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF8` else after.`CORPDEF8` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF8`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF9` else after.`CORPDEF9` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF9`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CORPDEF10` else after.`CORPDEF10` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CORPDEF10`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF21` else after.`GROUPDEF21` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF21`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF22` else after.`GROUPDEF22` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF22`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF23` else after.`GROUPDEF23` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF23`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF24` else after.`GROUPDEF24` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF24`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF25` else after.`GROUPDEF25` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF25`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF26` else after.`GROUPDEF26` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF26`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF27` else after.`GROUPDEF27` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF27`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF28` else after.`GROUPDEF28` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF28`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF29` else after.`GROUPDEF29` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF29`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF30` else after.`GROUPDEF30` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF30`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF31` else after.`GROUPDEF31` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF31`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF32` else after.`GROUPDEF32` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF32`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF33` else after.`GROUPDEF33` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF33`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF34` else after.`GROUPDEF34` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF34`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF35` else after.`GROUPDEF35` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF35`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF36` else after.`GROUPDEF36` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF36`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF37` else after.`GROUPDEF37` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF37`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF38` else after.`GROUPDEF38` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF38`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF39` else after.`GROUPDEF39` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF39`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF40` else after.`GROUPDEF40` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF40`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF41` else after.`GROUPDEF41` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF41`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF42` else after.`GROUPDEF42` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF42`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF43` else after.`GROUPDEF43` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF43`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF44` else after.`GROUPDEF44` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF44`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF45` else after.`GROUPDEF45` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF45`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF46` else after.`GROUPDEF46` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF46`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF47` else after.`GROUPDEF47` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF47`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF48` else after.`GROUPDEF48` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF48`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF49` else after.`GROUPDEF49` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF49`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF50` else after.`GROUPDEF50` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF50`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF51` else after.`GROUPDEF51` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF51`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF52` else after.`GROUPDEF52` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF52`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF53` else after.`GROUPDEF53` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF53`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF54` else after.`GROUPDEF54` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF54`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF55` else after.`GROUPDEF55` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF55`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF56` else after.`GROUPDEF56` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF56`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF57` else after.`GROUPDEF57` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF57`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF58` else after.`GROUPDEF58` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF58`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF59` else after.`GROUPDEF59` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF59`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF60` else after.`GROUPDEF60` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF60`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF61` else after.`GROUPDEF61` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF61`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF62` else after.`GROUPDEF62` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF62`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF63` else after.`GROUPDEF63` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF63`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF64` else after.`GROUPDEF64` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF64`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF65` else after.`GROUPDEF65` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF65`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF66` else after.`GROUPDEF66` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF66`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF67` else after.`GROUPDEF67` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF67`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF68` else after.`GROUPDEF68` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF68`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF69` else after.`GROUPDEF69` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF69`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF70` else after.`GROUPDEF70` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF70`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF71` else after.`GROUPDEF71` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF71`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF72` else after.`GROUPDEF72` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF72`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF73` else after.`GROUPDEF73` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF73`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF74` else after.`GROUPDEF74` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF74`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF75` else after.`GROUPDEF75` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF75`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF76` else after.`GROUPDEF76` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF76`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF77` else after.`GROUPDEF77` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF77`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF78` else after.`GROUPDEF78` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF78`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF79` else after.`GROUPDEF79` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF79`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF80` else after.`GROUPDEF80` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF80`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF81` else after.`GROUPDEF81` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF81`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF82` else after.`GROUPDEF82` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF82`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF83` else after.`GROUPDEF83` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF83`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF84` else after.`GROUPDEF84` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF84`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF85` else after.`GROUPDEF85` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF85`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF86` else after.`GROUPDEF86` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF86`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF87` else after.`GROUPDEF87` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF87`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF88` else after.`GROUPDEF88` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF88`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF89` else after.`GROUPDEF89` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF89`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF90` else after.`GROUPDEF90` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF90`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF91` else after.`GROUPDEF91` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF91`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF92` else after.`GROUPDEF92` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF92`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF93` else after.`GROUPDEF93` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF93`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF94` else after.`GROUPDEF94` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF94`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF95` else after.`GROUPDEF95` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF95`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF96` else after.`GROUPDEF96` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF96`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF97` else after.`GROUPDEF97` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF97`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF98` else after.`GROUPDEF98` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF98`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF99` else after.`GROUPDEF99` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF99`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`GROUPDEF100` else after.`GROUPDEF100` end,CHR(10),''),CHR(13),''),CHR(9),'') as `GROUPDEF100`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`ISRETURN` else after.`ISRETURN` end,CHR(10),''),CHR(13),''),CHR(9),'') as `ISRETURN`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`ISREFERENCED` else after.`ISREFERENCED` end,CHR(10),''),CHR(13),''),CHR(9),'') as `ISREFERENCED`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`ONPOSTDATE` else after.`ONPOSTDATE` end,CHR(10),''),CHR(13),''),CHR(9),'') as `ONPOSTDATE`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CREATETIME` else after.`CREATETIME` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CREATETIME`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`CREATOR` else after.`CREATOR` end,CHR(10),''),CHR(13),''),CHR(9),'') as `CREATOR`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`MODIFIER` else after.`MODIFIER` end,CHR(10),''),CHR(13),''),CHR(9),'') as `MODIFIER`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`MODIFYTIME` else after.`MODIFYTIME` end,CHR(10),''),CHR(13),''),CHR(9),'') as `MODIFYTIME`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`HROPERATOR` else after.`HROPERATOR` end,CHR(10),''),CHR(13),''),CHR(9),'') as `HROPERATOR`,
    REPLACE(REPLACE(REPLACE(case when op_type = 'D' then before.`PSNNAMEPINYIN` else after.`PSNNAMEPINYIN` end,CHR(10),''),CHR(13),''),CHR(9),'') as `PSNNAMEPINYIN`,
    `op_type` as `OP_TYPE`,
    `op_ts` as `OP_TS`,
    `current_ts` as `OGG_TIME`,
    CURRENT_TIMESTAMP as `GMT_CREATE`
from KAFKA_SOURCE_FIN_NC_BD_PSNDOC;