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的归档日志,因此全量和增连更需要分开处理,上述的类可以同时做到。