Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 28, 2020 5:39:03 AM
Jul 21, 2020 11:59:21 AM
SAP and other database applications may include tables with column names that include forward slashes. These can cause unexpected (and unwanted) results when replicating to Kafka.
Resolution:
Many databases support the use of forward slashes as part of a column name. Applications such as SAP commonly utilize them. The following simple table defined in DB2 LUW will serve as the example for this scenario:
db2 => create table kafkatest (col1 int, "/COL2" int, "/COL/C3" int, col4 int)
db2 => insert into kafkatest values(1,1,1,1)
db2 => insert into kafkatest values(2,2,2,2)
Without any special attention being paid to the columns with forward slashes, Qlik Replication will accept the “kafkatest” table as a valid source table and will replicate the data to Kafka. The resulting JSON messages sent to Kafla will be formatted like this example:
{
"magic": "atMSG",
"type": "DT",
"headers": null,
"messageSchemaId": null,
"messageSchema": null,
"message": {
"data": {
"COL1": 1,
"COL4": 1
},
"COL2": 1,
"COL": {
"C3": 1
},
"beforeData": null,
"headers": {
"operation": "REFRESH",
"changeSequence": "",
"timestamp": "",
"streamPosition": "",
"transactionId": "",
"changeMask": null,
"columnMask": null
}
}
Notice: The COL1 and COL4 (in blue) can be found within the “data” object. /COL2 and /COL/C3 (in red) are part of the Kafka message but are outside of the “data” object.
Using a Task Global Transformation:
Replication tasks often contain many tables in a single task, some or all of which could include one or more columns with forward slashes. The forward slashes, wherever they are in use, can convert using a single task Global Transformation. This can be defined through the Designer UI as shown here:
1: Open Global Transformations for the current task.
2: Create a new Global Transformation.
3: Select Rename column as the type of transformation.
4: Indicate which tables and columns are in scope for the transformation.
5: Define a transformation expression.
6: Use the “replace” function to replace forward slashes with the desired character (or characters). In the example the forward slash replaces an underscore.
7: Test the expression and then click OK to accept it.
8: Proceed with the Global Transformation definition.
9: Finish the Global Transformation then save changes to the task (not shown).
The resulting JSON output to Kafka with the Global Transformation from the sample table will look like this:
{
"magic": "atMSG",
"type": "DT",
"headers": null,
"messageSchemaId": null,
"messageSchema": null,
"message": {
"data": {
"COL1": 1,
"_COL2": 1,
"_COL_C3": 1,
"COL4": 1
},
"beforeData": null,
"headers": {
"operation": "REFRESH",
"changeSequence": "",
"timestamp": "",
"streamPosition": "",
"transactionId": "",
"changeMask": null,
"columnMask": null
}
}
}
The columns with forward slashes now contain underscores and all of the columns are now accounted for within the “data” object (in blue).