Bug description

I have connected Elasticsearch data with Superset, and can query all my indexes correctly. There is one issue, the dateField in my index is being converted to a String-type column (in Superset). When doing a select inside the SQLLab there is no error showing but when you try to visualize the column, a weird error appears I have tried the following:

-Update the dataset column type to date but I run into an error. -Changed the formatting of the date inside the window where we can edit the time format -Updated the formatting inside the chart to match my time format

Superset version: V3.1.2 superset dbapi: 0.2.11

The datetime format Elasticsearch Index: Mapping of the index/field "dateField": { "type": "date", "store": true, "format": "date_optional_time" },

Format in superset when using SQL Lab: 2024-01-11T14:52:21.973Z

How to reproduce the bug

Connect Elasticsearch data using elastic-dbapi with Superset , the data must include fields of type date. Select multiple columns and the column that represents the field inside Superset SQL Lab Save as dataset Go to datasets, click on the dataset you previously created then columns and you will notice the field has "String" as type

Screenshots/recordings

error

Superset version

master / 3.1.2

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

  • [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: dosubot[bot]

Hi @sarcoex! I'm here to help you with any bugs, questions, or contributions you have for the repository. Let me know how I can assist you!

To resolve the issue where the dateField in your Elasticsearch index is being converted to a String-type column in Superset, causing a parsing exception error when trying to visualize the column, you should use the DATETIME_PARSE function for conversion. This function is supported in Elasticsearch versions after 7.8.

Here is how you can address this:

  1. Upgrade Elasticsearch: Ensure that your Elasticsearch version is 7.8 or later.

  2. Use DATETIME_PARSE Function: Modify your query to use the DATETIME_PARSE function to convert the string to a DATETIME object.

  3. Set Elasticsearch Version in Superset: Fill in your Elasticsearch version number in the Other > VERSION setting in Superset. This will ensure that Superset uses the DATETIME_PARSE function for conversion.

Example:

SELECT
  DATETIME_PARSE(dateField, 'yyyy-MM-dd\'T\'HH:mm:ss.SSSZ') AS dateField
FROM
  your_index

This will convert the dateField from a string to a DATETIME object, allowing Superset to handle it correctly for visualization.

Additionally, ensure that your Superset configuration is correctly set up to handle Elasticsearch datetime conversions. The ElasticSearchEngineSpec class in Superset's codebase includes logic to handle datetime parsing using the DATETIME_PARSE function for Elasticsearch versions 7.8 and above. This ensures that datetime fields are correctly interpreted and visualized in Superset [1][2].

To continue talking to Dosu, mention @dosu.

Comment From: rusackas

We're no longer supporting 3.x and this has gone quite stale. Can you tell us if you're still facing this in 4.1.1 or 5.0.0rc1? I wish I had ElasticSearch to help troubleshoot, but feel free to give @dosu-bot more context and hopefully it can help.

Comment From: rusackas

OK, it's been a few monts, so I'll close this as inactive. Thanks!