Moving the json data to sqlDatabase

Hello

I need to move a json message(where the scheme is not embedded ). I have a json message with only payload. I had tried jdbc sink connector but it failed. Is there any other possible connectors to move data from a topic to sql database?

The config i had used for my message is below:

class: io.confluent.connect.jdbc.JdbcSinkConnector
tasksMax: 1
config:

connection.url: jdbc:sqlserver://sqlsrv-tam-test.database.windows.net:1433;database=****;

connection.user: *******

connection.password:********

topics: fake.person.json.v0.dev

pk.mode: record_key

pk.fields: fake_key

auto.create: "false"

delete.enabled: "true"

key.converter: org.apache.kafka.connect.storage.StringConverter

key.converter.schemas.enable: "false"

value.converter: org.apache.kafka.connect.json.JsonConverter

value.converter.schemas.enable: "false"

insert.mode: insert

transforms: dropPrefix

transforms.dropPrefix.type: org.apache.kafka.connect.transforms.RegexRouter

transforms.dropPrefix.regex: fake\.person\.json\.v0\.(.*)

transforms.dropPrefix.replacement: $1

table.name.format: dev

Message format:

{
“name”: {
“firstName”: “Kafka”,
“lastName”: “connect”
},
“age”: 1,
“birthDate”: {
“year”: 2021,
“month”: 11,
“day”: 26
},
“idNumber”: “000000”,
“gender”: “male”,
“address”: {
“streetAddress”: “apache”,
“streetNumber”: “1”,
“code”: “774432 61”,
“locality”: “confluent”,
“municipality”: {
“name”: “kafka”,
“code”: “2021”
},
“region”: {
“name”: “helloo”,
“code”: “2021”,
“letter”: “C”
}
},
“phoneNumber”: “00000000”,
“emailAddress”: “cesho@gmail.com
}

Thanks,
Cesho

You’re going to need to pre-process the message to add a schema (and also flatten it into a form it can be written to RDBMS).

See The JDBC Sink Connector and Schemas for more details

Thanks @rmoff for the reply. Do we have any other way to add schema to topic other than using a ksqldb. Can you provide any links for handling this situation?

Thanks,
Cesho

You could use Kafka Streams, or a custom Single Message Transform.

@rmoff but I need to send this message as string to the database. The product team doesn’t want to flatten or do anything. they just need the message as it as.

So you want to put the entire lump of JSON in a single VARCHAR field in the database?

1 Like

hi @cesho, you can use this simple SMT to convert your JSON payload to a JSONString to put it to a database field: GitHub - an0r0c/kafka-connect-transform-tojsonstring: transform-to-json-string is a Single Message Transformation (SMT) for Apache Kafka® Connect to convert a given Connect Record to a single JSON String. It's an UNOFFICIAL community project.

2 Likes

@rmoff exactly this is the scenario customer is expecting

thank you @whatsupbros for reply. My record contains only a payload and there is no schema. You can view the message above