Incubator-superset: invalid function date_trunc when connecting to hive -- related to issue #7270

Created on 2 Jun 2020 · 4Comments · Source: apache/incubator-superset

Hello!

A year ago, @iannamkr open a issue #7270 on version 0.28.0 that was fixed on #7657.

Whatever, the error is still happening on version 0.36.0

The problem is when I try to create a time-series line chart. So, Superset uses 'date_trunc' function in hivesql group by clause and it raises an exception.

Actual results and How to reproduce the bug

This query example

SELECT CAST(varchar_timestamp_field AS TIMESTAMP) AS timestamps, value
FROM table

Results on

To explore the data and to create a chart, click on explore values:

So, select the Line chart style
After, select the timestamp as a timestamp column
So, set the time grain to hours.
Then, set the value as a metric with SUM aggregation function.

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

[X] I have checked the superset logs for python stacktraces and included it here as text if there are any.
[X] I have reproduced the issue with at least the latest released version of superset.
[X] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Looking to the fix on #7657, at 7 of June 2019, a new class was created on file db_engine_specs.py (on that version):

class HiveEngineSpec(BaseEngineSpec):

    """Reuses PrestoEngineSpec functionality."""

    engine = 'hive'
    max_column_name_length = 767

    time_grain_functions = {
        None: '{col}',
        'PT1S': "from_unixtime(unix_timestamp({col}), 'yyyy-MM-dd HH:mm:ss')",
        'PT1M': "from_unixtime(unix_timestamp({col}), 'yyyy-MM-dd HH:mm:00')",
        'PT1H': "from_unixtime(unix_timestamp({col}), 'yyyy-MM-dd HH:00:00')",
        'P1D': "from_unixtime(unix_timestamp({col}), 'yyyy-MM-dd 00:00:00')",
        'P1W': "date_format(date_sub(nianxian, CAST(7-from_unixtime(unix_timestamp(nianxian),'u') as int)), 'yyyy-MM-dd 00:00:00')",
        'P1M': "from_unixtime(unix_timestamp({col}), 'yyyy-MM-01 00:00:00')",
        'P0.25Y': "date_format(add_months(trunc(nianxian, 'MM'), -(month(nianxian)-1)%3), 'yyyy-MM-dd 00:00:00')",
        'P1Y': "from_unixtime(unix_timestamp({col}), 'yyyy-01-01 00:00:00')",
        'P1W/1970-01-03T00:00:00Z': "date_format(date_add(nianxian, INT(6-from_unixtime(unix_timestamp(nianxian), 'u'))), 'yyyy-MM-dd 00:00:00')",
        '1969-12-28T00:00:00Z/P1W': "date_format(date_add(nianxian, -INT(from_unixtime(unix_timestamp(nianxian), 'u'))), 'yyyy-MM-dd 00:00:00')"
    }

But, on new commit, at 8 of June of the same year (https://github.com/apache/incubator-superset/commit/95291facff5c49ce4c63da25ff3efaf4bf101822), was broken up the file db_engine_specs, and the function above was not included, am I right?

Thanks!

#bug

Source

nielsenrechia

All 4 comments

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.94. Please mark this comment with :thumbsup: or :thumbsdown: to give our bot feedback!

Links: app homepage, dashboard and code for this bot.