Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Dimension_group w/ type "time" doesn't work with datatype: datetime for BigQuery

vfx
Userlevel 1

Consider the following code:


view: datetime {

  derived_table: {

    sql: SELECT CURRENT_DATETIME as dt;; # Bigquery dialect

  }



  dimension_group: dt {

    type: time

    timeframes: [

      date,

      month

    ]

    sql: ${TABLE}.dt ;;

    datatype: datetime

  }

}



explore: datetime {}

This is supposed to be pretty straightforward - a dimension group with datetime as underlying data type.


Looker generates the following SQL:


WITH datetime AS (SELECT CURRENT_DATETIME as dt)

SELECT 

	CAST(TIMESTAMP(FORMAT_TIMESTAMP('%F %T', datetime.dt , 'Asia/Kolkata')) AS DATE) AS datetime_dt_date

FROM datetime



GROUP BY 1

Which produces an error as the underlying type is not timestamp.


Failed to retrieve data - No matching signature for function FORMAT_TIMESTAMP for argument types: STRING, DATETIME, STRING. Supported signature: FORMAT_TIMESTAMP(STRING, TIMESTAMP, [string]) at [4:24]

This happens with other datatypes too. There is a way to overcome it by casting the datetime to timestamp in the ‘sql:’ element but this is a hack and produces redundant transformation.


Is it a bug?

icon

Best answer by izzy 23 July 2020, 23:34

View original

19 replies

fabio
Rank
Userlevel 7
Badge

I think this is just a discrepancy in nomenclature. “Datetime” in LookML represents a concept which may have different names in different SQL dialects.


On the other hand, the “datetime” type in BigQuery is actually rather clunky and difficult to work with - it represents a local time with no timezone information, which is (in my experience) not so useful in practice. is there a reason you’re not using a BigQuery timestamp instead?

fabio
Rank
Userlevel 7
Badge

Looking at our docs a bit more, I guess we have LookML datatypes of both datetime and timestamp. I think I’ll have to read into it a bit more!

vfx
Userlevel 1

Hi Fabio,


Thanks for taking the time to look at this. The reason why we are using DATETIME datatype is because the underlying Bigquery tables use fields of this type and they come from the original data sources.


Of course DATETIME fields can be converted back to timestamp while creating the views in Looker but then what is the use for the “datatype” parameter if it has to be converted to TIMESTAMP?


On a side note, the automatic conversion to TIMESTAMP (by using create view from table) also doesn’t seem to work properly with timezones, so I reported this separately as a potential bug here.


Cheers

vfx
Userlevel 1

I found more details.


The documentation mentions at least at two places that for dimension_group of type time the default datatype is datetime.



The default value for datatype is datetime.



By observing how looker writes SQL it seems the assumed default type (at least for BigQuery) is in fact timestamp. And even if datatype parameter is specified to be datetime it’s being ignored, which produces invalid SQL.

vfx
Userlevel 1

Guys,


I was just wondering if there is some progress on this. It seems somewhat foundational to me…

izzy
Userlevel 7
Badge +1

I think you’re right and that this is an issue with our default generated SQL— I’ll pass it along to engineering!

vfx
Userlevel 1

Great news! Thanks!

vfx
Userlevel 1

Hi Izzy,


Is there some news about this?

izzy
Userlevel 7
Badge +1

Hi hi,


Our engineers agreed that this is something to be fixed— Specifically, they thought it has to do with the way that bigquery treats the word “datetime”. In a lot of dialects, it’s synonymous with timestamp, and we treat it as such. In BigQuery, though, it appears to be different, and that’s the hitch.


We’ve prioritized it, but not urgently, since there is a workaround (casting) that’s not too in depth— Which is to say it will get fixed, but there are currently other projects prioritized higher in this development cycle.


This topic is linked to the bug, so I’ll know if there’s any progress made and update here. Thanks for checking in!

alexanderm

Hi, the workaround works and isn’t too painful, but the bug isn’t very obvious and puzzled me for quite a while. So it’d be nice to have a fix.


Cheers,


Alex

izzy
Userlevel 7
Badge +1

A fix will definitely come, since it’s been triaged & prioritized by engineering. It’s just a matter of when, or if there are other things in the queue ahead of it. Thanks for checking in on it! I’ll drop a line where when it’s fixed.

R

Just adding a +1 in looking for a fix for this.

jtopp

+1 From me as well!


As a bonus, you could do something like this:


TIMESTAMP(<datetime_column>, <database_timezone>)

(where <database_timezone> is the timezone specified in admin).


That way, you’d be enriching our datetime columns with timezone info! I think that’s actually a great value add, as well as a fix for an annoying bug. 😀

Kate_Fray
Rank

I have found that when trying to create a time dimension group parameter based on a date type that is atypical (not based on a datetime field), the best way to overcome this issue is to apply a datatype parameter as such:


dimension_group: created {

type: time

**datatype: date**

timeframes: [

  raw,

  time,

  date,

  week,

  month,

  quarter,

  year

]

sql: ${TABLE}._created_date ;;

}


This alleviates the error above.

vfx
Userlevel 1

Hi Kate,


It goes half way there 🙂 Doing the above makes looker to write the following:


CAST(CAST(<some_datetime> AS TIMESTAMP) AS DATE)


It’s great as:



  • doesn’t break


It’s not so great as:



  • removes the time from the datetime

  • only works in UTC - in every other timezone produces potentially wrong date


So all in all - doesn’t seem to be a workaround for the issue.

izzy
Userlevel 7
Badge +1

You’ll be pleased to know (especially @vfx) that we’ve just fixed this issue. It should be present in the next version of Looker.


We’ll be introducing a legacy feature flag as well as a model-level keyword parameter to control this, to make sure that folks can get the proper experience without breaking any code that has been relying on this incorrect behavior.


I’m being intentionally vague as there’s still time for things to change, so keep your eyes peeled for the details in the release notes for 7.14!

mwalle

This was the only thread I could find with any mention of 7.14 at this time, so I’m hijacking it just to ask @izzy if you know when the 7.14 relnotes will be published to https://docs.looker.com/relnotes


It’s July 30th which is the expected final release date for 7.12; but there are fixes we’ve been informed of in 7.14 that we’d love to see the published details of.

Jason_Comp_Three

@izzy didn’t see this feature in 7.14. Am I missing it or did it get bumped to the next release? Thanks!

jesse.carah
Rank
Userlevel 4

@Jason_Comp_Three . This was indeed addressed in 7.14. See the changelog here:



Fixed: Incorrect SQL for datetime fields for BigQuery dialects is no longer being generated.


Reply


Terms of Service