โ05-27-2022 07:01 AM
Hi Team,
I have found one of the transformational rules applied on โdate fieldโ not getting exported to the final output file (i.e. excel file) when the pipeline has been executed.
Output preview on the snap does show the right data (i.e. expected one), pipeline validation also shows the right data however the file which gets generated after pipeline execution doesnโt get those details.
I have tried the following:
shift+validate
Itโs a simple pipeline consisting of
Snowflake Execute --> Mapper --> Excel Formatter --> File Writer
Sharing some screenshots to help it understand better:
(1) Pipeline with 4 snaps
(2) Snowflake execute output preview in JSON format (highlighted yellow fields needs transformation)
(3) Transformation Rules on Date fields
(4) Transformation Rule: $Expire_Date.toString().replace('{Timestamp=',"").replace('.000+0000}',"Z")
(5) Output preview after transformational rule
(6) Mapper output preview shows different data
โ Iโve observed certain times that the transformation rules wonโt work so I go inside the mapper, modify the rule a bit, bring it back to default rule so that I can save it (save is not enabled if there is no change thus this workaround)
(7) As shift+validate
didnโt work, had to change the transformation rule, save it and then this is the output preview on mapper snap:
(8) Settings under excel formatter snap for reference:
(9) Settings under File Writer snap for reference:
(10) File generated with default 50 records due to validate & execute
on File writer snap
(11) Downloaded the file that gets generated in step 10 and below is the output (expected)
(12) Executed Pipeline
(13) Output File of Executed pipeline
Iโve seen this happening numerous times particularly to fields containing timestamp in other pipelines too, am I doing something wrong over here? Any settings that needs to be changed either in Mapper, Excel Formatter or File Writer snap?
Thanking in advance for your valuable time and suggestions on this one.
Best Regards,
DT
Solved! Go to Solution.
โ05-30-2022 11:42 AM
The Snowflake snaps have a setting called Handle Timestamp and Date Time Data
. Unfortunately, its default setting, Default Date Time format in UTC Time Zone
, has less-than-ideal behavior. The object type of the timestamp objects is java.sql.Timestamp, which will result in the odd inconsistencies youโre seeing between validation vs execution, and itโs also not a very usable type on the SnapLogic expression language.
I suggest changing this setting to the other option, SnapLogic Date Time format in Regional Time Zone
. This will convert the timestamps to the type org.joda.time.DateTime, the type that the SnapLogic EL functions are designed to deal with.
This option will also produce a DateTime with the default time zone of the plex node, which can complicate things if the nodeโs default timezone isnโt UTC. But based on what youโve shown, your nodeโs default timezone is UTC, so this shouldnโt be a problem for you, fortunately.
After changing that setting, try using an expression like $Expire_Date.toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})
.
Hope that helps.
โ05-27-2022 08:04 AM
Hi @darshthakkar,
Since the field you are transforming is a timestamp ( datetime ), it is a best practice to transform the date and format it with the built-in method .toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})
instead of converting it to string and then manually replacing the timezone offset.
โ05-27-2022 08:25 AM
Thank you @j.angelevski for the direction.
I will try your suggestion and keep you posted on how it goes, appreciate your help though!
Rationale behind converting it to String was: I wanted to export the raw data and it didnโt allow, error was โFlatten the file before exportโ thus .toString()
was used. I wasnโt aware of the built-in method so THANK YOU for sharing that.
โ05-27-2022 08:52 AM
With the above approach, the result in Date is coming as โNullโ
Screenshots below for reference:
โ05-27-2022 10:18 AM
@darshthakkar try
Date.parse($Expire_Date.Timestamp).toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})
I believe that will do what you want