Oracle-CDC处理时间字段

FLinkCDC的Oracle连接器，将oracle的时间类型的字段进行了转化，导致和数据库中查询出来的不一致，但是由于需求，得到的数据需要和数据库中查询出来的数据保持一致。因此需要自己进行处理。测试的oralce版本为11g，12 和19版本的Oracle暂时没有测试，对于可能多出来的时间类型，需要再做处理。

需要添加 如下配置

 debeziumProperties.setProperty("converters", "dateConverters");
 debeziumProperties.setProperty("dateConverters.type", "com.yzh.cdc.oracle.OracleDateTimeConverter");

具体的OracleDateTimeConverter代码如下，可以参考 io.debezium.connector.oracle.OracleValueConverters 类，实际上自己定义的类，也会覆盖此类。

package com.yzh.cdc.oracle;

import io.debezium.spi.converter.CustomConverter;
import io.debezium.spi.converter.RelationalColumn;
import oracle.jdbc.OracleTypes;
import oracle.sql.INTERVALDS;
import oracle.sql.INTERVALYM;
import oracle.sql.TIMESTAMPLTZ;
import oracle.sql.TIMESTAMPTZ;
import org.apache.kafka.connect.data.SchemaBuilder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAdjuster;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class OracleDateTimeConverter implements CustomConverter<SchemaBuilder, RelationalColumn> {

    private static final ZoneId timestampZoneId = ZoneId.systemDefault();
    private static final Pattern TO_DATE = Pattern.compile("TO_DATE\\('(.*)',[ ]*'(.*)'\\)", Pattern.CASE_INSENSITIVE);
    private static final Pattern TO_TIMESTAMP = Pattern.compile("TO_TIMESTAMP\\('(.*)'\\)", Pattern.CASE_INSENSITIVE);
    private static final Pattern TEMP = Pattern.compile("TO_TIMESTAMP_TZ\\('(.*)'\\)", Pattern.CASE_INSENSITIVE);
    private static final Pattern TO_TIMESTAMP_TZ = Pattern.compile("TO_TIMESTAMP_TZ\\('(\\d+)-(\\d+)-(\\d+) (\\d+):(\\d+):(\\d+).(\\d+) ([+\\-])(\\d+):(\\d+)'\\)", Pattern.CASE_INSENSITIVE);
    private static final Pattern TO_TIMESTAMP_LTZ = Pattern.compile("TO_TIMESTAMP_TZ\\('(\\d+)-(\\d+)-(\\d+) (\\d+):(\\d+):(\\d+).('\\)|\\d+'\\))", Pattern.CASE_INSENSITIVE);
    private static final Pattern TIMESTAMP_OR_DATE_REGEX = Pattern.compile("^TIMESTAMP[(]\\d[)]$|^DATE$", Pattern.CASE_INSENSITIVE);
    private static final Pattern INTERVAL_DAY_SECOND_PATTERN = Pattern.compile("([+\\-])?(\\d+) (\\d+):(\\d+):(\\d+).(\\d+)");

    private final DateTimeFormatter dateFormatter = DateTimeFormatter.ISO_DATE;
    private final DateTimeFormatter timeFormatter = DateTimeFormatter.ISO_TIME;
    private final DateTimeFormatter datetimeFormatter = DateTimeFormatter.ISO_DATE_TIME;
    private final DateTimeFormatter timestampFormatter = DateTimeFormatter.ISO_DATE_TIME;

    private static final DateTimeFormatter TIMESTAMP_FORMATTER = new DateTimeFormatterBuilder()
            .parseCaseInsensitive()
            .appendPattern("yyyy-MM-dd HH:mm:ss")
            .optionalStart()
            .appendPattern(".")
            .appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, false)
            .optionalEnd()
            .toFormatter();

    private static final DateTimeFormatter TIMESTAMP_AM_PM_SHORT_FORMATTER = new DateTimeFormatterBuilder()
            .parseCaseInsensitive()
            .appendPattern("dd-MMM-yy hh.mm.ss")
            .optionalStart()
            .appendPattern(".")
            .appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, false)
            .optionalEnd()
            .appendPattern(" a")
            .toFormatter(Locale.ENGLISH);

    private static final DateTimeFormatter TIMESTAMP_TZ_FORMATTER = new DateTimeFormatterBuilder()
            .parseCaseInsensitive()
            .appendPattern("yyyy-MM-dd HH:mm:ss")
            .optionalStart()
            .appendPattern(".")
            .appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, false)
            .optionalEnd()
            .optionalStart()
            .appendPattern(" ")
            .optionalEnd()
            .appendOffset("+HH:MM", "")
            .toFormatter();

    @Override
    public void configure(Properties properties) {}

    @Override
    public void converterFor(RelationalColumn column, ConverterRegistration<SchemaBuilder> registration) {
        int sqlType = column.jdbcType();
        SchemaBuilder schemaBuilder = null;
        Converter converter = null;
        Object o = column.defaultValue();

        if (OracleTypes.TIMESTAMPTZ == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.TIMESTAMPLTZ == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.INTERVALYM == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.INTERVALYM == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.INTERVALDS == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.DATE == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.TIMESTAMP == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (OracleTypes.TIME == sqlType) {
            schemaBuilder = SchemaBuilder.string();
            converter = this::conver;
        }
        if (schemaBuilder != null) {
            registration.register(schemaBuilder, converter);
        }

    }

    private String conver(Object input) {
        LocalDateTime dateTime = null;
        if (input == null)
            return null;

        if (input instanceof TIMESTAMPLTZ) {
            String result = null;
            try {
                Connection conn = DriverManager.getConnection(OracleCDC.url, OracleCDC.username, OracleCDC.pass);
                result = ((TIMESTAMPLTZ) input).stringValue(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        return result;
        }

        if (input instanceof TIMESTAMPTZ) {
            TimeZone timeZone = null;
            try {
                Connection conn = DriverManager.getConnection(OracleCDC.url, OracleCDC.username, OracleCDC.pass);
                timeZone = ((TIMESTAMPTZ) input).getTimeZone();
                dateTime = ((TIMESTAMPTZ) input).timestampValue(conn).toInstant().atZone(timeZone.toZoneId()).toLocalDateTime();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return timestampFormatter.format(dateTime).replaceAll("T", " ") +timeZone.getID().replaceAll("GMT"," ");
        }

        final Matcher toTimestampMatcher = TO_TIMESTAMP.matcher(String.valueOf(input));
        if (toTimestampMatcher.matches()) {
            String dateText = toTimestampMatcher.group(1);
            if (dateText.indexOf(" AM") > 0 || dateText.indexOf(" PM") > 0) {
                dateTime = LocalDateTime.from(TIMESTAMP_AM_PM_SHORT_FORMATTER.parse(dateText.trim()));
            }
            else {
                dateTime = LocalDateTime.from(TIMESTAMP_FORMATTER.parse(dateText.trim()));
            }
            return datetimeFormatter.format(dateTime).replaceAll("T", " ");
        }

        final Matcher toDateMatcher = TO_DATE.matcher(String.valueOf(input));
        if (toDateMatcher.matches()) {
            dateTime = LocalDateTime.from(TIMESTAMP_FORMATTER.parse(toDateMatcher.group(1)));
            return datetimeFormatter.format(dateTime).replaceAll("T", " ");
        }

        final Matcher tempMatcher = TEMP.matcher(String.valueOf(input));
            if (tempMatcher.matches()){
                String dateText = tempMatcher.group(1);
                if (TO_TIMESTAMP_TZ.matcher( String.valueOf(input)).matches()) {
                    ZonedDateTime zonedDateTime = ZonedDateTime.from(TIMESTAMP_TZ_FORMATTER.parse(dateText.trim()));
                    ZoneId zone = zonedDateTime.getZone();
                    LocalDateTime localDateTime = zonedDateTime.withZoneSameInstant(zone).toLocalDateTime();
                    return timestampFormatter.format(localDateTime).replaceAll("T", " ") + " "+zone.getId();
                }
                if (TO_TIMESTAMP_LTZ.matcher( String.valueOf(input)).matches()){
                    ZonedDateTime zonedDateTime = ZonedDateTime.from(TIMESTAMP_TZ_FORMATTER.parse(dateText.trim()));
                    LocalDateTime localDateTime = zonedDateTime.withZoneSameInstant(timestampZoneId).toLocalDateTime();
                    return timestampFormatter.format(localDateTime).replaceAll("T", " ") + " "+timestampZoneId.toString();
                }
            }

    if(String.valueOf(input).startsWith("TO_DSINTERVAL('") || String.valueOf(input).startsWith("TO_YMINTERVAL('") ){
        String s = String.valueOf(input).substring(15, String.valueOf(input).length() - 2);
        final Matcher intervalDSMatcher = INTERVAL_DAY_SECOND_PATTERN.matcher(s);
        if (intervalDSMatcher.matches()){
            INTERVALDS interval = new INTERVALDS(s);
            return interval.stringValue();
        } else {
            INTERVALYM interval = new INTERVALYM(s);
            return interval.stringValue();
        }
    }
        return String.valueOf(input);
    }
}

请注意，对于FLink CDC 的Oracle 连接器来说，全量阶段和增量阶段所获得的原始数据是不一致的，猜测应该是 全量的时候是直接查询的数据库，只有增量的时读取的Oracle的归档日志，因此全量和增连更需要分开处理，上述的类可以同时做到。