Tracking(sink datatype mapping): sink type mismatch for jdbc and Debezium

[wugouzi]

When doing sinking, RisingWave type may need to be mapped to different types based on type of sink(Debezium, jdbc, etc.) and target DBMS (MySQL, PostgreSQL, etc.). Below is a list of things that need to be done.

• For Date, Debezium maps it to int32, but jdbc requires a string.
• For Timestamp, Debezium maps it to int64, but jdbc requires a string. Related issue: Bug(sink): create tidb sink panic #9376
• Potential problem after Tracking(sink datatype mapping): Type missing when sink to MySQL, PostgreSQL or TiDB #9603
• test: add data types testing for postgres sink #9918
• test: add data types testing for mysql sink #9919

[StrikeW]

• For Date, Debezium maps it to int32, but jdbc requires a string.

I think you mean the JSON payload of Jdbc sink, after #9957 JDBC sink can support Date.

• For Timestamp, Debezium maps it to int64, but jdbc requires a string. Related issue: Bug(sink): create tidb sink panic #9376

#9957 added a flag to control how Sink converts the Timestamp in JSON format:

risingwave/src/connector/src/sink/mod.rs

Lines 340 to 342 in 487c4c6

	(DataType::Timestamp, ScalarRefImpl::Timestamp(v)) => match timestamp_handling_mode {
	TimestampHandlingMode::Milli => json!(v.0.timestamp_millis()),
	TimestampHandlingMode::String => json!(v.0.format("%Y-%m-%d %H:%M:%S%.6f").to_string()),

But I think we can use the string representation to unify the implementation of Kafka sink and JDBC sink (JSON payload). IMO, we don't need to stick to the data type mapping of the debezium‘s Postgres connector. Making the output JSON format consistent with Debzium JSON is enough. cc @neverchanje

[tabVersion]

IMO, we don't need to stick to the data type mapping of the debezium‘s Postgres connector. Making the output JSON format consistent with Debzium JSON is enough. cc @neverchanje

from my perspective, I accept any data format, as long as it can be ingested by the majority of downstream CDC tools. At this moment, I think sticking to debezium doc is the safest way.
Besides, if we adopt debezium type mapping table as our source of truth, there can be minor modifications when we align with customer's system in the future.

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[hzxa21]

The remaining work is tracked in #9603. I will close this issue for now. Feel free to reopen if needed.