Help preserving integer value through tWriteJSONField

Anonymous — Sat, 16 Nov 2024 03:28:31 GMT

Hello,

Here is my job. It reads from a database, adds several properties, and publishes to Kafka.

A requirement is that one of the added properties contains the time in microseconds as an integer. I calculate this as

tstamp_usec = System.currentTimeMillis() * 1000 , and set it as a Long type in tMap.

tMap Map Editor output

This value looks good in the output from tLogRow_2.

0001203d-6a6e-46c0-b15f-760c6d5a6340|16-10-2017|0|a9d98a02-1dcd-023d-8973-55e2a3e13f23|edf98d6b-9744-4bdb-8c11-0f9a28bf5ca5|captains-log|bf201ac1-2b45-45b1-a83d-27c83351fad5|a97374c3-be6c-40dd-ae6e-617188de2b07|crm_onetime_load|crm|1579781507837000|acc_access_types_ith_product_holdings_1_c|id|16-10-2017|23-01-2020|UPDATE

In tWriteJSONField I start to encounter problems.

tWriteJSONField component configuration.

tWriteJSONField JSON tree, with tstamp_usec set as an integer.

tWriteJSONField schema, with tstamp_user set as Long type.

When I run the job, I get the following error in the log

Exception in component tWriteJSONField_1_In (Sugar_Test_v3_to_Kafka)
net.sf.json.JSONException: java.lang.NumberFormatException: For input string: "1579825342226000"
	at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:386)
	at sugar_one_time_load.sugar_test_v3_to_kafka_0_1.Sugar_Test_v3_to_Kafka.tWriteJSONField_1_InProcess(Sugar_Test_v3_to_Kafka.java:3786)
	at sugar_one_time_load.sugar_test_v3_to_kafka_0_1.Sugar_Test_v3_to_Kafka$1ThreadXMLField_tWriteJSONField_1_Out.run(Sugar_Test_v3_to_Kafka.java:2044)
Caused by: java.lang.NumberFormatException: For input string: "1579825342226000"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)

So it seems that tstamp_user is being treated as a String !!

If I change the type for tstamp_usec in the JSON tree to "number" or "float", the job runs but tstamp_user is formatted with an exponent, which does not meet my requirement that it be formatted as an integer.

{"eventid":"03a99823-7711-430e-ac31-bbf42047c311","requestid":"514a59d5-e19d-4b01-9d00-0b9f27a3e102","dests":["captains-log"],"tstamp_usec":1.579825639825E15,"origin":"crm","eventtype":"crm_onetime_load","message":{"table":"acc_access_types_ith_product_holdings_1_c","pk":"id","data":{"id":"0001203d-6a6e-46c0-b15f-760c6d5a6340","date_modified":"16-10-2017","deleted":"0","acc_access_types_ith_product_holdings_1acc_access_types_ida":"a9d98a02-1dcd-023d-8973-55e2a3e13f23","acc_access_types_ith_product_holdings_1ith_product_holdings_idb":"edf98d6b-9744-4bdb-8c11-0f9a28bf5ca5"},"date_modified2":"16-10-2017","date_sent":"23-01-2020","action":"UPDATE"}}

I need the following format:

"tstamp_usec":1579825639825

Can anyone help? It seems like I should be able to use "integer" in the JSON tree. Perhaps I'm doing something incorrectly.

Thanks,

Dave

Re: Help preserving integer value through tWriteJSONField

vapukov — Tue, 28 Jan 2020 23:19:12 GMT

Hi Dave

it is impossible to keep this value as an integer, just because Integer limit is - 2,147,483,647 (much smaller than your values)

1579825342226000
1579825342226
2147483647

but why you do not use Long in the scheme?

regards, Vlad

Re: Help preserving integer value through tWriteJSONField

Anonymous — Wed, 29 Jan 2020 14:59:41 GMT

Hi Vlad,

Thank you for responding. I see what you're saying. I'd incorrectly thought that since an integer in JSON didn't have the same limitation, that I could map it as an integer when mapping the JSON tree in tWriteJSONField.

So now I'm doing this:

tMap: use Long

tWriteJSONField JSON tree: use String or Number

tWriteJSONField: use Long

The value comes out of tWriteJSONField as 1.579825342226000E15, which I thought was a problem. But I found that I could publish that to Kafka, and the Kafka message displays the value as 1579825342226000, which is what I need.

Thank you,

Dave

topic Re: Help preserving integer value through tWriteJSONField in Talend Studio

Help preserving integer value through tWriteJSONField

Re: Help preserving integer value through tWriteJSONField

Re: Help preserving integer value through tWriteJSONField