SQL Generation for BigQuery Seems To Be Failing

[carlos-omny]

I've created a super simple model for a table in my BigQuery database but the generated query from Cube throws a syntax error.

Here is the generated query: SELECT DATETIME_TRUNC(DATETIME('covid_patient_impact'.date, 'UTC'), DAY) 'covid_patient_impact__date_day', count('covid_patient_impact'.inpatient_beds_used_covid_coverage) 'covid_patient_impact__count' FROM test.sample_covid_patient_impact AS 'covid_patient_impact' GROUP BY 1 ORDER BY 1 ASC LIMIT 100

This part: DATETIME('covid_patient_impact'.date, 'UTC') is throwing the error Could not cast literal "UTC" to type TIME at [2:56]. How do I fix this issue? I've tried using a string data type for that column but the same SQL code is generated so the same error is thrown.

[pcasteran]

I had the same issue, it was due to the fact that my time column was of type DATE. Considering the generated SQL code, it seems that Cube assumes that time columns must be of type TIMESTAMP.

Until this issue is closed, a workaround is to add a CAST to TIMESTAMP in your dimension definition:

dimensions:
  - name: date
    sql: TIMESTAMP(sales_date)
    type: time

[paveltiunov]

Please see https://cube.dev/docs/reference/data-model/types-and-formats#time-1

[orgito]

Casting a datetime to timestamp causes bigquery to ignore table partitioning if the table is partitioned by the datetime column. That can cause a large impact on performance and monetary cost of the queries.