CODEWITHSUNDEEP
jdbcapache-kafkaapache-kafka-connectconfluent-platform
ajkush
ajkush

Reputation: 589

Kafka connect database source connector : how to copy data from foreign key

I am using the database source connector to move data from my Postgres database table to Kafka topic. I have an orders table having a foreign key with customers table using customerNumber field.

Below is the connector which is copying the orders to Kafka but without customers data into JSON. I am looking how could I construct the complete object of orders with customers into JSON.

enter image description here

and the connector is :

{
"name": "SOURCE_CONNECTOR",
"config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
    "transforms.createKey.type": "org.apache.kafka.connect.transforms.ValueToKey",
    "connection.password": "postgres_pwd",
    "transforms.cast.type": "org.apache.kafka.connect.transforms.Cast$Value",
    "transforms.cast.spec": "amount:float64",
    "tasks.max": "1",
    "transforms": "cast,createKey,extractInt",
    "transforms.extractInt.type": "org.apache.kafka.connect.transforms.ExtractField$Key",
    "batch.max.rows": "25",
    "table.whitelist": "orders",
    "mode": "bulk",
    "topic.prefix": "data_",
    "transforms.extractInt.field": "uuid",
    "connection.user": "postgres_user",
    "transforms.createKey.fields": "uuid",
    "poll.interval.ms": "3600000",
    "sql.quote.identifiers": "false",
    "name": "SOURCE_CONNECTOR",
    "numeric.mapping": "best_fit",
    "connection.url": "url"
    }
  }

Upvotes: 2

Views: 1222

Answers (1)

Iskuskov Alexander
Iskuskov Alexander

Reputation: 4375

You can use Query-based ingest. Just specify query config option.

Upvotes: 1

Related Questions