Moving the json data to sqlDatabase

[cesho]

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

[rmoff]

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

[cesho]

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

[rmoff]

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

[cesho]

@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.

[rmoff]

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

[whatsupbros]

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.

[cesho]

@rmoff exactly this is the scenario customer is expecting

[cesho]

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