Time range does not work with AWS ES (open distro)

[shadidchowdhury]

Describe the bug Time range does not work with AWS ES

To Reproduce Steps to reproduce the behavior:

1. Create a valid cube schema connected to AWS ES 7.7
2. Build a query with time range set to anything other than all time.
3. Run the query
4. See error

Expected behavior Generate SQL should use a date function compatible with AWS ES as documented https://opendistro.github.io/for-elasticsearch-docs/docs/sql/functions/

Currently it uses DATE_TRUNC which is not supported by open distro ES.

Screenshots

Error: "{\n \"error\": {\n \"reason\": \"Invalid SQL query\",\n \"details\": \"Failed to parse query due to offending symbol [:] at: 'SELECT\\n DATE_TRUNC('day', d_n_s_activity_red.timestamp:' <--- HERE... More details: Expecting tokens in {'SELECT', '('}\",\n \"type\": \"SyntaxAnalysisException\"\n },\n \"status\": 400\n}"

Version: v0.27.53 AWS ES 7.7

[ovr]

Hello @shadidchowdhury,

Yes, It's a bug. There are two issues in the ElasticSearchQuery dialect with Open Distro:

1. It doesn't support DATE_TRUNC, but X-Pack supports it. I think date_format can be used for Open Distro.
2. Cast ::datetime is not supported.

Probably, it's required to create another dialect for Open Distro and fix it in this dialect.

Thanks

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[shadidchowdhury]

@ovr actually changing db type to awselasticsearch helps little bit. It uses date_format in generated sql. In our cube schema we have a field with time as field type, but for in generated sql that field is formatted as yyyy-MM-dd'T'HH:mm:ss which causes the next parse exception.

ElasticsearchParseException[failed to parse date field [2021-07-01T00:00:00Z] with format [epoch_second]: [failed to parse date field [2021-07-01T00:00:00Z] with format [epoch_second]]]; nested: IllegalArgumentException[failed to parse date field [2021-07-01T00:00:00Z] with format [epoch_second]]; nested: NotSerializableExceptionWrapper[date_time_parse_exception: Failed to parse with all enclosed parsers]