Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Replicating tables to Kafka with column names containing forward slashes

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Fergen
Former Employee
Former Employee

Replicating tables to Kafka with column names containing forward slashes

Last Update:

Aug 28, 2020 5:39:03 AM

Updated By:

Sonja_Bauernfeind

Created date:

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)

 

 

 

 



David_Fergen_20-1595347035853.jpeg

 



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.

David_Fergen_21-1595347035855.jpeg

 



2: Create a new Global Transformation.

David_Fergen_22-1595347035858.jpeg

 



3: Select Rename column as the type of transformation.

David_Fergen_23-1595347035862.jpeg

 



4: Indicate which tables and columns are in scope for the transformation.

David_Fergen_24-1595347035865.jpeg

 



5: Define a transformation expression.

David_Fergen_25-1595347035869.jpeg

 



6: Use the “replace” function to replace forward slashes with the desired character (or characters).  In the example the forward slash replaces an underscore.

David_Fergen_26-1595347035874.jpeg

 



7: Test the expression and then click OK to accept it.

David_Fergen_27-1595347035884.jpeg

 



8: Proceed with the Global Transformation definition.

David_Fergen_28-1595347035887.jpeg

 



9: Finish the Global Transformation then save changes to the task (not shown).

David_Fergen_29-1595347035889.jpeg

 



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

Labels (1)
Version history
Last update:
‎2020-08-28 05:39 AM
Updated by: