https://dagster.io/ logo
#integration-bigquery
Title
# integration-bigquery
k

Kazushi Nagayama

08/10/2023, 12:49 PM
i'm trying to define partitioned tables by date, but getting error because dagster tries to run this query:
Copy code
DELETE FROM `mytable` WHERE
DATE(PARSE_DATETIME('%F %T', date)) >= '2023-08-09 00:00:00' AND DATE(PARSE_DATETIME('%F %T', date)) < '2023-08-10 00:00:00'
j

jamie

08/10/2023, 2:47 PM
do you have a specific error message you can share? I can try to help debug. our test suite that runs a bunch of different partition tests against a real BQ db is looking fine, so it’s hard to say anything for certain without some more info
k

Kazushi Nagayama

08/14/2023, 8:52 AM
@jamie
Copy code
Could not cast literal "2023-08-09 00:00:00" to type DATE at [2:15]
j

jamie

08/14/2023, 2:19 PM
ah ok so this issue here is that you’re comparing a DATE type on the left and a TIMESTAMP type on the right and bigquery can’t automatically convert a TIMESTAMP to a DATE (annoying). So i think if you change the left of your comparison to a TIMESTAMP it should work. I think this would do it:
Copy code
PARSE_TIMESTAMP('%F %T', date))
k

Kazushi Nagayama

08/14/2023, 2:36 PM
yes, but i want this to be a date-partitioned table.
ideally dagster should just take date-typed columns for date partitioned tables
which would match the bigquery partition
at least that would be intuitive for a bigquery user 🙂
j

jamie

08/14/2023, 3:45 PM
i totally see where your coming from - it’s a bit tricky to do that on the backend though, and at this point would be a breaking change to modify the format of the partition key that’s included in the query, so i dont see us being able to make any changes for a while
k

Kazushi Nagayama

08/15/2023, 5:42 AM
i see
i also noticed that dagster doesn't actually create date-partitioned tables in bigquery even when the dagster asset it date-partitioned. i guess this is future work for you guys?
4 Views