Support Questions
Find answers, ask questions, and share your expertise

How to convert unix timestamp to datetime in Apache NiFi?

Hi everyone!
In my FlowFile, using components, I listen to logs from Squid in the form of txt. Then I add columns and separate the log elements. The output file is csv, because in the future it will be added to the Hive database.
The problem occurs when I want to change the type of the "datetime" variable from unix timestamp to the usual date time.

Based on this solution: https://community.hortonworks.com/articles/131320/using-partitionrecord-grokreaderjsonwriter-to-pars...

This is how the log sent by Squid looks like using a proxy:

1518442283.483 161 127.0.0.1 TCP_MISS/200 103701 GET http://www.cnn.com/ matt DIRECT/199.27.79.73 text/html

This is Grok Expression (in GrokReader😞
%{NUMBER:timestamp}\s+%{NUMBER:duration}\s%{IP:client_address}\s%{WORD:cache_result}/%{POSINT:status_code}\s%{NUMBER:bytes}\s%{WORD:request_method}\s%{NOTSPACE:url}\s(%{NOTSPACE:user}|-)\s%{WORD:hierarchy_code}/%{IPORHOST:server}\s%{NOTSPACE:content_type}

The following links include:
β†’ General appearance of components: https://imgur.com/a/fn2Q03N
β†’ Settings for the UpdateRecord component: https://imgur.com/a/E8gfMj8
β†’ Settings for the UpdateRecord/GrokReader(nifi_mid): https://imgur.com/a/l8He75D
β†’ Settings for the UpdateRecord/CSVRecordSetWriter: https://imgur.com/a/aqiZ98M

My problem is described on stackoverflow: https://stackoverflow.com/questions/48885675/conversion-unix-timestamp-attribute-to-normal-date

13 REPLIES 13

@Davide Isoardi where and how can I set these values (in this case user and datetime) to be able to transform them?

Expert Contributor

in your place I would have:

  • convert record to JSON not to CSV
  • use EvaluateJsonPath to set the attribute from the JSON in the contentFile
  • use updateAttribute to transform datetime and user
  • use ReplaceText to replace all contentFile with the altered attribute
  • write the contentFile to FS

Expert Contributor

in your place I would have:

  • convert record to JSON not to CSV
  • use EvaluateJsonPath to set the attribute from the JSON in the contentFile
  • use updateAttribute to transform datetime and user
  • use ReplaceText to replace all contentFile with the altered attribute
  • write the contentFile to FS

The input file is in the text/html format, and the output file must be in csv format, because it will feed the database.