How to work with timestamps with Elasticsearch

[Pablo Riutort]

Hello,

I'm working with Sentilo on a project called Smart UIB at the University Of Balearic Islands. We would like to connect Sentilo to Elasticsearch but we are having some issues.

Problem is Elasticsearch doesn't recognize data types from Sentilo properly, incoming timestamps from Sentilo are String type instead of date therefore Elasticsearch cannot index by time.

We tried changing the file "sentilo-agent-activity-monitor/src/main/resources/elasticsearch/sentilo-index-template.json" by putting the date format in 'basic_date_time_no_millis' (yyyyMMdd'T'HHmmssZ) as it comes specified here "https://www.elastic.co/guide/en/elasticsearch/reference/6.1/mapping-date-format.html", but it didn't worked. Also, we tried both Elasticsearch 6.2 and Elasticsearch 2.4.6 and we got the same result.

This is an example of data sended to Elasticsearch via Bulk API:

{ "index" : { "_index" : "<sentilo-{now/M{YYYY.MM}}>", "_type" : "data" }}
{"message":"13.6","timestamp":"22/05/2018T18:20:54",...,"time":1527013254785,"publisher":"UIB","publishedAt":1527013254785,...}

We cannot read either "timestamp", "time" or "publishedAt" keys as timestamps but as String, long and long.

Please, can you give us some advice on how we can connect Sentilo to Elasticsearch and what changes we may have to do in order to recognize timestamps?

Thank you.

[io.se...@gmail.com]

Hello Pablo,

The Sentilo template published on Github is correct.

Could you please double-check if the template is being applied to the index? Do you have a index named sentilo-2018.06?

Remember that templates are only applied at index creation time. Changing a template will have no impact on existing indices: https://www.elastic.co/guide/en/elasticsearch/reference/2.4/indices-templates.html

Also, to this moment we can only confirm full compatibility of Sentilo 1.7 against Elasticsearch 2.4, although it is likely that ES 5/6 should work.

Regards,

Sentilo Team.

[Pablo Riutort]

Hello, and thank you for your reply. 

We figured out a way to connect Sentilo v1.7.0 with Elasticsearch v6.3.0. We managed to create a new template for newer versions of Elasticsearch.

Many changes took place in last versions of Elasticsearch, specially in index building. According to the Elasticsearch guide there was a removal of mapping types, instead, there is just one "_doc" mapping for the index template, this is one of the main changes we made to the given template in Sentilo. Also, in order for this index to work properly we built a script that listens for upcoming connections to the _bulk API of Elasticsearch and does some changes to the data:

• Change timestamp from dd/mm/YYYY'T'HH:MM:SS to YYYY-mm-dd'T'HH:MM:SS 
• Map every index['_type'] value to '_doc' 
• Convert content of message to "float" 

You can check both the template and the script at https://github.com/SmartUIB/sentilo-to-elasticsearch. 

Thank you again for your reply. Best regards, 

Smart UIB.

[io.se...@gmail.com]

Awesome Pablo,

Thank you for this insight and for publishing this useful tool.

We'll catch up with Elasticsearch as soon as possible.

Regards,

Sentilo Team.