Search code examples
postgresqljdbcapache-kafkaapache-kafka-connectconfluent-platform

Kafka Connect - JSON Converter - JDBC Sink Connector - Column Type JSON

use case is to store entire message (which is JSON) and key as a record in table which has two columns 'id' and 'data'.

The database is Postgres and it supports column type as JSON.

As per this article, supported types in JSONConverter are string, int64, etc https://cwiki.apache.org/confluence/display/KAFKA/KIP-301%3A+Schema+Inferencing+for+JsonConverter

Is it possible to have type of data field as JSON which then can be stored in Postgres DB with column of type JSON.

schema = `{
"type":"struct",
"fields":[
    {"type":"string", "optional": false, "field":"id"},
    {"type":"string", "optional": false, "field":"data"}
]}`

Sample data payload is 

"payload": { "id": 10000, "data": {"hello":"world"} }

Above will store data as text and expects column to be of type text in Postgres. If the column on Postgres is of type JSON then the JDBC Sink Connector will throw an error.

Using JSON types on Postgres will help to create index on JSON fields and so forth. Is it possible to use JSONConverter along with JDBC Sink Converter appropriately for storing records with column type JSON.

Solution

  • JDBC Sink Connector doesn't support PostgreSQL json, jsonb types. It support number of primitive types, datetimes.

    At following page you can find mapping schema types to Databases types (PostgreSQL) https://docs.confluent.io/5.1.0/connect/kafka-connect-jdbc/sink-connector/index.html

    Although, JDBC Source Connector supports json, jsonb types in some part - columns of such type won't be mapped to STRUCT, but will be mapped to STRING type.