为何Kafka JDBC Connect插入数据为CLOB而非VARCHAR2及解决方法

Hey there, let's break down why you're seeing CLOB columns instead of VARCHAR2, and fix both the type mapping and manual table insertion problems.

Why Columns Become CLOB Instead of VARCHAR2 #

Avro's string type doesn't enforce a length limit, which means it can hold arbitrarily large text. By default, the Confluent JDBC Sink Connector maps unbounded Avro strings to Oracle's CLOB type to avoid truncation issues. This is why auto-create mode generates CLOB columns instead of VARCHAR2.

For your manual table scenario, the likely issue is a mismatch between your table's column definitions (case sensitivity, type alignment) and how the connector expects to write data.

Solutions to Get VARCHAR2 Columns #

1. Define Column Types Directly in the Avro Schema #

You can add custom sqlType properties to your Avro schema fields to tell the connector exactly which Oracle type to use. This is the most straightforward approach.

Update your producer's Avro schema string to include these properties:

String flightSchema = "{\"type\":\"record\",\"name\":\"Flight\",\"fields\":[" +
    "{\"name\":\"flight_id\",\"type\":\"string\",\"sqlType\":\"VARCHAR2(50)\"}," +
    "{\"name\":\"flight_to\",\"type\":\"string\",\"sqlType\":\"VARCHAR2(3)\"}," +
    "{\"name\":\"flight_from\",\"type\":\"string\",\"sqlType\":\"VARCHAR2(3)\"}" +
"]}";

• Replace the lengths (50, 3) with values that fit your data needs.
• Delete your existing FLIGHTS4 table (if it's using CLOBs).
• Keep auto.create=true in your connector config, restart the connector, and send new data. The table will now be created with VARCHAR2 columns.

2. Explicit Column Type Mapping in Connector Config #

If you don't want to modify your Avro schema, use the connector's column.types property to map Avro fields to Oracle types directly. Add this line to your Kafka Connect config:

column.types=flight_id:VARCHAR2(50),flight_to:VARCHAR2(3),flight_from:VARCHAR2(3)

This overrides the default type mapping, forcing the connector to use VARCHAR2 for those fields even with unbounded Avro strings.

Fixing Manual Table Insertion Issues #

If you want to use a manually created table and avoid auto-create:

1. Match Case Sensitivity: Oracle defaults to uppercase table/column names. Make sure your manual table uses the exact uppercase names the connector expects:

   CREATE TABLE FLIGHTS4 (
       FLIGHT_ID VARCHAR2(50),
       FLIGHT_TO VARCHAR2(3),
       FLIGHT_FROM VARCHAR2(3)
   );
   

2. Update Connector Config: Set auto.create=false and auto.evolve=false (unless you need schema evolution).
3. Check Logs: If data still isn't inserting, look at the Kafka Connect worker logs for errors—common issues include permission problems, type mismatches, or missing columns.

Additional Tips #

• Oracle VARCHAR2 Limits: For Oracle 12c+, the default max VARCHAR2 length is 4000 bytes. If you need longer strings, set MAX_STRING_SIZE=EXTENDED in your Oracle instance to use up to 32767 bytes.
• Upgrade Connector: Ensure you're using the latest version of the Confluent JDBC Sink Connector—older versions may have limited Oracle type mapping support.
• Validate Schema: Use the Schema Registry UI to confirm your updated Avro schema (with sqlType properties) is registered correctly.

内容的提问来源于stack exchange，提问作者Alfred