Is it possible to use Kafka Connector with object referencing schema?

Question

Let's say I needed to use Kafka Connect, and instead of using a nested schema structure, I wanted to use a schema with object references. This structure would use id values to link between various objects.

For example:

 {
    "insured": [
        {
            "id": "INS001",
            "typeCode": "Primary",
            "sequenceNumber": 1,
            "fullName": "Mr. Tim Carlieux",
            "legalEntityTypeCode": "Person"
        }
    ],
    "address": [
        {
            "id": "ADR001",
            "parentEntityName": "insured",
            "parentEntityId": "INS001",
            "typeCode": "Home",
            "sequenceNumber": 1,
            "line1": "19 8th Ave",
            "city": "Acity",
            "stateProvinceCode": "AZ",
            "county": "Acounty",
            "postalCode": 99999
        }
    ],
    "communication": [
        {
            "id": "COM001",
            "parentEntityName": "insured",
            "parentEntityId": "INS001",
            "phoneNumber": "222-3478543",
            "phoneTypeCode": "Phone"
        }
    ]
}

In the above example, I would use a combination of parentEntityName and parentEntityId from address and/or communication to determine which of the insured objects I was referencing.

Additionally, the objects in the address and communication sections may not always have insured as a parent - a communication could be related to any number of other objects.

I believe acquiring values for the id in each section should be straightforward, provided the underlying data has meaningful, relational structure - which I would expect from an RDBMS. But I'm not sure if populating the parentEntityName can be satisfied using a standard Connector.

Do typical Kafka Connectors lend themselves to this sort of structure?

Answer 1

Kafka Connect cannot do any such id lookup (without a Simple Message Transform, but that's not their intention)

You can use Kafka Streams or other stream processing libraries to "expand" the ID into a fully defined record before/after Connect processes the data. You don't need an RDBMS, either. A join against multiple KTables should work for simple ids