table name is not shown up in Kafka message

[RichJ]

I am using JSON format to replicate tables from SQL server to a topic of Confluent Kafka with the option "Wrap data messages with the Attunity Envelope".  However,  the table name is not shown up in the kafka message.   That causes confusion as there are a couple of tables in the same topic.   The sample message is as below:

{"magic": "atMSG", "type": "DT", "headers": null, "messageSchemaId": null, "messageSchema": null, "message": {"data": {"Col_1": 7, "Col_2": "AX", "Col_3": "BX"}, "beforeData": {"Col_1": 7, "Col_2": "A88", "Col_3": "B88"}, "headers": {"operation": "UPDATE", "changeSequence": "20211013220038760000000000000001413", "timestamp": "2021-10-13T22:00:38.767", "streamPosition": "0086632e:0000adba:0004", "transactionId": "0000000000000000000000066F0415E3", "changeMask": "06", "columnMask": "07", "transactionEventCounter": 3, "transactionLastEvent": true}}}

Thanks for help,

Richard

[john_wang]

Hello @RichJ ,

You may add table name, table schema, or other relevant information to the messages. The most easy way is using the Global Rules --> Transformation . I'm adding my screen samples for you too, you may set it up quickly by following my steps.

Feel free to let me know if you need any additional information.

Regards,

John.

[RichJ]

Hi John,

Thanks for your reply.  I followed your step, but still cannot see the table_name "Temp_Kafka".  My version is (2021.5.0.1011).   The output apache kafka message is :

{"magic": "atMSG", "type": "DT", "headers": null, "messageSchemaId": null, "messageSchema": null, "message": {"data": {"Col_1": 8, "Col_2": "A8", "Col_3": "B8"}, "beforeData": null, "headers": {"operation": "INSERT", "changeSequence": "20211014182658240000000000000245881", "timestamp": "2021-10-14T18:26:58.240", "streamPosition": "00866369:00018e37:0002", "transactionId": "0000000000000000000000066F06B053", "changeMask": "07", "columnMask": "07", "transactionEventCounter": 1, "transactionLastEvent": true}}}

The Global Rule I added is

Thanks again,

Richard

[RichJ]

Hi John,

It works out after I re-created the task and followed your steps. 

The table_name is now under "data" Json section.  It would be more suitable to put "table_name" under "headers" json section.  Can it be accomplished?   Should we alter export file:   "header_columns_settings": {    }, but what will be the exact syntax?

Thanks,

Richard

[john_wang]

Hello @RichJ ,

Glad to know it works for your now.

However the newly added column is designed to appear under "data" section. I totally agree with you the better position is under "headers" section however it's not available yet. The available headers columns list are:

I'd like to check internally and get back to you later.

Regards,

John.

[RichJ · Accepted solution]

FYI  .... John,

I can now make table name available in  Kafka message key, the format is a combination of schema and table name (e.g. "dbo+Employees").   

To archive that,  simply choose Message key as Schema and table name.

Regards,

Richard

[john_wang]

Hello @RichJ ,

Thanks for your update. Glad to hear you got it solved.

Regards,

John.

[john_wang]

Hello @RichJ and All,

In Replicate 2023.5 there are feature enhancement about the additional header columns includes the schema name and table name, see Qlik Replicate and Kafka target: How to add tables name in Kafka message.

Hope this helps.

Regards,

John.