Solved

Field mapper not mapping all fields

  • 16 October 2023
  • 19 replies
  • 91 views
bsayed
Rank

I have a field mapper to remap camel case fields to snake case.
 

This is the message i am trying to save:

{
   "id": "e35fd529-613f-5e79-036f-f37c2801041d",
   "batchName": "SIT Axle3+ 9_12_test",
   "batchSize": 35,
   "numberOfAudited": 35,
   "batchType": "TRIP_BASED",
   "batchMode": "RANDOM",
   "batchFacility": "71",
   "batchDirection": "West",
   "batchPlaza": "",
   "batchLane": "",
   "fromTimestamp": 1694538060,
   "toTimestamp": 1694566800,
   "createdTimestamp": 1694626323,
   "createdBy": "mmccammon@etcc.com",
   "completedTimestamp": 1697039389,
   "completedBy": "asayed@etcc.com",
   "auditDetailList": [
      {
         "id": "a4f274d5-8de5-4c54-ae70-b6a498ab4e9e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "49dcbe2c-51a8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549374,
         "auditResult": false,
         "auditCorrections": [],
         "auditedTimestamp": 1694635817,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "67a5f5e3-acc8-4965-bf0b-657a6dafbe2e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "724a03aa-51a9-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549871,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694636317,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "4f57507e-31ed-4e65-bcb8-6a7d176baece",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "c4ef2560-51b1-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694553445,
         "auditResult": true,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694637274,
         "auditedBy": "ppradeep@etcc.com",
         "auditNote": "Bus",
         "isExcluded": false
      },
      {
         "id": "7262657b-9b2a-4fd4-8b6d-fd842df8830e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "03dbfde2-51b8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694556128,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "5",
               "auditedValue": "8"
            }
         ],
         "auditedTimestamp": 1694637409,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      }
   ]
}

However, when it goes through the field mapper, i am seeing some strange behavior. The fields is only mapped once when it has the same value as another instance:
 

Any idea why this is?

icon

Best answer by Bikram 18 October 2023, 21:03

View original

19 replies

saleempothiwala
Rank
Userlevel 4
Badge

@bsayed that is a strange behaviour. Please can you share the sample data in JSON and I can give it a try.

bsayed
Rank

@saleempothiwala here is the input  

{
   "id": "e35fd529-613f-5e79-036f-f37c2801041d",
   "batchName": "SIT Axle3+ 9_12_test",
   "batchSize": 35,
   "numberOfAudited": 35,
   "batchType": "TRIP_BASED",
   "batchMode": "RANDOM",
   "batchFacility": "71",
   "batchDirection": "West",
   "batchPlaza": "",
   "batchLane": "",
   "fromTimestamp": 1694538060,
   "toTimestamp": 1694566800,
   "createdTimestamp": 1694626323,
   "createdBy": "mmccammon@etcc.com",
   "completedTimestamp": 1697039389,
   "completedBy": "asayed@etcc.com",
   "auditDetailList": [
      {
         "id": "a4f274d5-8de5-4c54-ae70-b6a498ab4e9e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "49dcbe2c-51a8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549374,
         "auditResult": false,
         "auditCorrections": [],
         "auditedTimestamp": 1694635817,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "67a5f5e3-acc8-4965-bf0b-657a6dafbe2e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "724a03aa-51a9-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549871,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694636317,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "4f57507e-31ed-4e65-bcb8-6a7d176baece",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "c4ef2560-51b1-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694553445,
         "auditResult": true,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694637274,
         "auditedBy": "ppradeep@etcc.com",
         "auditNote": "Bus",
         "isExcluded": false
      },
      {
         "id": "7262657b-9b2a-4fd4-8b6d-fd842df8830e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "03dbfde2-51b8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694556128,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "5",
               "auditedValue": "8"
            }
         ],
         "auditedTimestamp": 1694637409,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      }
   ]
}

 

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

Not sure on this behaviour , i will check it.

 

To make it work , as a work around ,you can use same field mapper processor twice and it will resolve the issue.

bsayed
Rank

@Bikram I have tried to use 2 field mappers, however it only works for the next key value pair that are the same.

For example, try 2 field mappers with this json:

{
   "id": "e35fd529-613f-5e79-036f-f37c2801041d",
   "batchName": "SIT Axle3+ 9_12_test",
   "batchSize": 35,
   "numberOfAudited": 35,
   "batchType": "TRIP_BASED",
   "batchMode": "RANDOM",
   "batchFacility": "71",
   "batchDirection": "West",
   "batchPlaza": "",
   "batchLane": "",
   "fromTimestamp": 1694538060,
   "toTimestamp": 1694566800,
   "createdTimestamp": 1694626323,
   "createdBy": "mmccammon@etcc.com",
   "completedTimestamp": 1697039389,
   "completedBy": "asayed@etcc.com",
   "auditDetailList": [
      {
         "id": "a4f274d5-8de5-4c54-ae70-b6a498ab4e9e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "49dcbe2c-51a8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549374,
         "auditResult": false,
         "auditCorrections": [],
         "auditedTimestamp": 1694635817,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "67a5f5e3-acc8-4965-bf0b-657a6dafbe2e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "724a03aa-51a9-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694549871,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694636317,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      },
      {
         "id": "4f57507e-31ed-4e65-bcb8-6a7d176baece",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "c4ef2560-51b1-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694553445,
         "auditResult": true,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694637274,
         "auditedBy": "ppradeep@etcc.com",
         "auditNote": "Bus",
         "isExcluded": false
      },
      {
         "id": "7262657b-9b2a-4fd4-8b6d-fd842df8830e",
         "auditBatchId": "e35fd529-613f-5e79-036f-f37c2801041d",
         "eventId": "03dbfde2-51b8-11ee-9348-3cecefd01508",
         "eventType": "TRIP",
         "eventTimeStamp": 1694556128,
         "auditResult": false,
         "auditCorrections": [
            {
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"
            }
         ],
         "auditedTimestamp": 1694637409,
         "auditedBy": "ppradeep@etcc.com",
         "isExcluded": false
      }
   ]
}

There seems to be some correlation with the value of the field as well. 
if the field-value combination is the same, it skips it.

all three sections highlighted above are 

               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"

Bikram
Rank
Userlevel 5
Badge +1

@bsayed

 

just keep only this condition as given below 

 

${f:type() != "MAP" and f:type() != "LIST" and f:type() != "LIST_MAP" }

 

${str:toLower(str:replaceAll(f:name(), '([A-Z][a-z])', '_$1'))}

 

For me it works.

 

 

bsayed
Rank

@Bikram I've done as you suggested:
 

still same issue:
 

 

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

Please remove the str:upper condition and try.

I am attaching the pipeline , can you please import it and try and check if it helps.

 

 

1 Attachment
bsayed
Rank

@Bikram 
I did as you suggested:

 

Still the same issue:
 

I tried to import your pipeline:
 

Any other suggestions? is this a bug in version 3.22.0?

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

I am not sure if its bug or not in 3.22.0 and i am not sure if i can get the version 3.22.0 or not.

If its there i can try it.

Can you please create a dummy pipeline and send me the pipeline and let me try to import it and check how it behaves.

bsayed
Rank

@Bikram 
Here is the dummy pipeline.
when I preview it, I have the same issue as before:
 

 

1 Attachment
Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

Issue in the data collector version .

May i know why its very old version you are using :)

bsayed
Rank

@Bikram 
This is just what my company uses, I don't handle the version. 😀 Looks like we need to upgrade however

I tried solving this with a java script evaluator:

var records = sdc.records;
for (var i = 0; i < records.length; i++) {
  try {
    var auditDetailList = records[i].value.auditDetailList.value;
    updateKeysAndPaths(auditDetailList);

    sdc.output.write(records[i]);
  } catch (e) {
    // Send record to error
    sdc.error.write(records[i], e);
  }
}

function updateKeysAndPaths(obj) {
  var stack = [obj];

  while (stack.length > 0) {
    var current = stack.pop();

    if (Array.isArray(current)) {
      current.forEach(function (item) {
        if (typeof item === 'object') {
          stack.push(item);
        }
      });
    } else if (typeof current === 'object') {
      for (var key in current) {
        if (current.hasOwnProperty(key)) {
          var value = current[key];
          if (typeof value === 'object') {
            stack.push(value);
          }

          // Modify keys
          if (key === 'auditedField') {
            current.audited_field = current[key];
            delete current[key];
          } else if (key === 'auditedValue') {
            current.audited_value = current[key];
            delete current[key];
          } else if (key === 'originalValue') {
            current.original_value = current[key];
            delete current[key];
          } else if (key === 'sqpath' || key === 'dqpath') {
            // Update the path as necessary
            current[key] = current[key].replace('auditedField', 'audited_field');
            current[key] = current[key].replace('auditedValue', 'audited_value');
            current[key] = current[key].replace('originalValue', 'original_value');
          }
        }
      }
    }
  }
}

I got it to work in my browser console, however, this doesn't seem to work in streamsets. Is there any other workaround that will solve this issue, other than upgrading versions?

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

For now you just go ahead to use the same field mapper twice and proceed further. I will provide you the concrete result tomorrow .

Let me check why its behaving like this , when i am taking your input data , i am also getting the same issue.

Need to do some analysis on it .

 

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

In Streamsets you can use java script processor and use your code which is working fine in your console.

Just give a try.

bsayed
Rank

@Bikram 

Thanks you for your help, its greatly appreciated.

So you have the issue on the imported pipeline I sent you?
If so, then there must be a bug in 3.22.0 that is fixed somewhere along the way to 5.5.0

Also, using 2 field mappers in not a solution, because it only works twice in the same instance of key value pairs. 

ex:
if        
               "auditedField": "axle count",
               "originalValue": "3",
               "auditedValue": "4"

is seen 3 times, then I would need 3 field mappers.
if it is seen 4 times, then I would need 4 field mappers and so on.

Since I cannot guarantee how many instances of the above same key value pairs will exist in a message, adding 2 field mappers doesn't fully solve the issue.

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

We can handle it in a different way , i hope it will help you to fix the issue. 

bsayed
Rank

@Bikram 
what is your suggestion to handle it in a different way?

Bikram
Rank
Userlevel 5
Badge +1

@bsayed 

can you please give a try the attached pipeline and check if it helps

I did the necessary changes in your pipeline.

1 Attachment
bsayed
Rank

@Bikram 

Adding the pivoter → mapper is working. Thank you for your help in solving this issue!😀😀😀
Greatly appreciate it.

 

Reply


Terms & Conditions