Dimension_group for type time

done
low_priority
visualizations
reply

(Jaya) #1

I am using time dimension_group to convert a datetime field from DB. The timeframe ‘time’ is coming to be correct but ‘date’ and other timeframes are giving errors.

I am connected to druid database.

Error -1 (00000) : Error while executing SQL “-- Looker Query Context ‘{“user_id”:39,“history_id”:39209,“instance_slug”:“82a527f9ed68303e47798a69a27a5da4”}’ SELECT CAST(FLOOR(gif_pick_complete.create_ts TO DAY) AS DATE) AS “gif_pick_complete.create_date” FROM druid.gifpickcomplete AS gif_pick_complete GROUP BY CAST(FLOOR(gif_pick_complete.create_ts TO DAY) AS DATE) ORDER BY CAST(FLOOR(gif_pick_complete.create_ts TO DAY) AS DATE) DESC LIMIT 10”: Remote driver error: RuntimeException: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 6, column 15 to line 6, column 56: Cannot apply ‘FLOOR’ to arguments of type ‘FLOOR(<VARCHAR>, <INTERVAL DAY>)’. Supported form(s): ‘FLOOR(<NUMERIC>)’ ‘FLOOR(<DATETIME_INTERVAL>)’ ‘FLOOR(<DATE> TO <TIME_UNIT>)’ ‘FLOOR(<TIME> TO <TIME_UNIT>)’ ‘FLOOR(<TIMESTAMP> TO <TIME_UNIT>)’ -> ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 6, column 15 to line 6, column 56: Cannot apply ‘FLOOR’ to arguments of type ‘FLOOR(<VARCHAR>, <INTERVAL DAY>)’. Supported form(s): ‘FLOOR(<NUMERIC>)’ ‘FLOOR(<DATETIME_INTERVAL>)’ ‘FLOOR(<DATE> TO <TIME_UNIT>)’ ‘FLOOR(<TIME> TO <TIME_UNIT>)’ ‘FLOOR(<TIMESTAMP> TO <TIME_UNIT>)’ -> CalciteContextException: From line 6, column 15 to line 6, column 56: Cannot apply ‘FLOOR’ to arguments of type ‘FLOOR(<VARCHAR>, <INTERVAL DAY>)’. Supported form(s): ‘FLOOR(<NUMERIC>)’ ‘FLOOR(<DATETIME_INTERVAL>)’ ‘FLOOR(<DATE> TO <TIME_UNIT>)’ ‘FLOOR(<TIME> TO <TIME_UNIT>)’ ‘FLOOR(<TIMESTAMP> TO <TIME_UNIT>)’ -> SqlValidatorException: Cannot apply ‘FLOOR’ to arguments of type ‘FLOOR(<VARCHAR>, <INTERVAL DAY>)’. Supported form(s): ‘FLOOR(<NUMERIC>)’ ‘FLOOR(<DATETIME_INTERVAL>)’ ‘FLOOR(<DATE> TO <TIME_UNIT>)’ ‘FLOOR(<TIME> TO <TIME_UNIT>)’ ‘FLOOR(<TIMESTAMP> TO <TIME_UNIT>)’


(Nicholas Wong) #2

In order to solve this problem, you’ll need to understand that Looker takes data from druid and does not store any data. Thus, the problem is probably coming from your database at Druid. Check the data type for this at your database… Else, you should contact druid and see what they say! Hope this helps. :slight_smile:


(Izzy) #3

What Nicholas said is true, but that doesn’t mean we’re totally out of luck here. Druid returns some kind of scary looking errors, but the important part is this:
From line 6, column 15 to line 6, column 56: Cannot apply ‘FLOOR’ to arguments of type ‘FLOOR(<VARCHAR>, <INTERVAL DAY>)’. Supported form(s): ‘FLOOR(<NUMERIC>)’ ‘FLOOR(<DATETIME_INTERVAL>)’ ‘FLOOR(<DATE> TO <TIME_UNIT>)’ ‘FLOOR(<TIME> TO <TIME_UNIT>)’ ‘FLOOR(<TIMESTAMP> TO <TIME_UNIT>)’

Basically, looks like you’re feeding the dimension group a varchar, when it’s hungry for a number, date, datetime, or time. What does your dimension group LookML look like?

I bet if it looks like this:

dimension_group: create {
type:time
datatype: date
timeframes: [date,raw,whatever]
sql: ${TABLE}.create_ts ;;
}

If you instead say

dimension_group: create {
type:time
datatype: date
timeframes: [date,raw,whatever]
sql: CAST(${TABLE}.create_ts AS DATE) ;;
}

Or similar, casting to whatever’s relevant, you might see this start working. See what I’m getting at there? The underlying datatype of create_ts is a varchar, which druid isn’t liking in the context of Lookers auto generated SQL to extract the timeframe. Casting it as a date explicitly might help.