Unable to load data from elasticsearch that contain date with timezone

1

Hi Guru’s,

Im running my own research on utilization of dremio into our production data analytics. During my research, I found an issue that not sure how to solve it.

I notice that dremio unable to load data from elasticsearch that contain date with timezone. Below are the error :

Below are the format for documentSet data.iso_8601 from ElasticSearch side :

dremio.issue_2.2.png747×36 5.39 KB

FYI, the input data are like this :

I’ve found a link that seems to be related to this issue Elasticsearch datetime problems but didn’t seem to get response on that.

So far now I’m keen to use dremio as middle man between ElasticSearch as main data lake and client BI tools. Therefore I would highly appreciate if your end have any suggestion or recommendation on above issue if it’s possible to be fix.

2

Hi @Carillpower

Can you please send us the server.log and the profile of the failed query

Dremio Logs
Share a Query Profile

Thanks
@balaji.ramaswamy

3

5543ac43-905d-4db6-a23e-262dad638c83.zip (4.5 KB)

Please find the attachment.

Thanks in advance for your help

4

server.zip (12.3 KB)

Please find the attachment.

Thanks an advance for the help

5

Ho @Carillpower

We have couple of enhancements we are working on to address this issue. We are actively working on getting our next MAJOR release 3.1 out and then will probably prioritize this into one of the future releases.

Thanks
@balaji.ramaswamy

6

Hi Balaji,

Would like to check with you, if i need to manipulate the datetime format from the source before storing into elasticsearch, could you share some example what kind of format that can be accepted from dremio?

Thanks and appreciate the effort

7

Hi @Carillpower

Do you have multiple formats of dates in that Elastic field even though you have defined a certain format in the mapping?

Thanks
@balaji.ramaswamy

8

Hi @balaji.ramaswamy ,

Sorry for the late reply, so far out date format been stream as yyyy-MM-dd HH:mm:ss z .

If dremio have it’s default format that are accepted , I can try to ask my developer to try change to that format before streaming into ES.

Thanks

9

Hi @Carillpower

What’s the ES version? Is this the only date format in the actual data?

Thanks
@balaji.ramaswamy

10

Hi @Carillpower,

Depending on which Elasticsearch version you’re using, you may have documents under the same mapping with different date formats. In early versions, you can update date format on an existing date field (see https://github.com/elastic/elasticsearch/issues/25271)

You may try updating the mapping in Elastic with specific date format string that Dremio complains about in the error. Elastic’s documentation on how to do this: https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html#multiple-date-formats

11

Yes you are correct, im using latest version of ES 6.5

12

Hi Ben,

You are correct, check my earlier post. I’ve already set multiple format for the date.

Thanks though

13

Hi @Carillpower,

My apologies for not looking closely enough at the top of the thread. I notice that the value in the example Elastic document you provided includes “UTC” in the iso_86901 field. It looks like Elastic parses this fine, but let me verify that Dremio does as well, and I’ll get back to you.

14

Was this ever resolved?