Hi Pedro, For the record, I’ve just implemented DATE_TRUNC and DATETIME_TRUNC support for BigQuery, which will be available with the next nightly builds.
I’ve checked again, it seems that BigQuery supports GROUP BY <select column indexes> as well, so https://github.com/jOOQ/jOOQ/issues/11820 won’t be necessary for your query to be translatable. Moving forward, in order to evaluate jOOQ, I think it would be best if you work with the extended trial license I’ve sent you and assemble a catalog of requirements. Again, things like that DATE_TRUNC() support are very easy to add, and we’ll usually just do it without any additional costs. Best Regards, Lukas From: [email protected] <[email protected]> On Behalf Of Lukas Eder Sent: Mittwoch, 26. Mai 2021 09:04 To: [email protected] Subject: RE: Portability snowflake to bigquery Hi Pedro, When you say "::" doesn’t work for bigquery, do you mean it doesn’t work on https://www.jooq.org/translate/ ? As far as I can tell, that’s not a problem: [cid:[email protected]] Have you tried it? Yes, DATE_TRUNC doesn’t work yet, indeed. Regarding your purchasing decision: I will be happy enrol you for an extended trial license to work with the latest jOOQ 3.15.0 SNAPSHOT version, which you will be able to get from here, no strings attached: https://www.jooq.org/download/versions I’ll reach out to you directly via a private email for an extended trial license key. Best Regards, Lukas From: 'Pedro Figueiredo' via jOOQ User Group <[email protected]<mailto:[email protected]>> Sent: Mittwoch, 26. Mai 2021 03:13 To: jOOQ User Group <[email protected]<mailto:[email protected]>> Subject: Re: Portability snowflake to bigquery Hello, take for your assistance. But if we see the documentation "::" this options doesn’t work for bigquery. and date_trunc works yet. Bellow there is an official documentation from google. Please help me to understand if can i buy the solution and much works for my case. For example from snowflake to bigquery you works for this situations and not for this ones. https://cloud.google.com/architecture/dw2bq/snowflake/snowflake-bq-sql-translation-reference.pdf On Tuesday, May 25, 2021 at 4:20:02 PM UTC-3 [email protected]<mailto:[email protected]> wrote: Hi Pedro, A quick check reveals that the DATE_TRUNC function isn’t yet supported for BigQuery. I’ll look into this soon, this week. This is a great example where you could work around the current limitation using a ParseListener, as I mentioned in my previous email. The cast is translated correctly. The GROUP BY 1, 2 semantics (referencing projected column expressions by index or alias from GROUP BY) is something we’ve been asked for a few times, also in the context of translating Teradata SQL to other RDBMS: https://github.com/jOOQ/jOOQ/issues/11820. I can’t promise this for the upcoming week, but we’re very keen on getting this to work for jOOQ 3.15. I hope this helps, Lukas From: 'Pedro Figueiredo' via jOOQ User Group <[email protected]<mailto:[email protected]>> Sent: Dienstag, 25. Mai 2021 16:56 To: jOOQ User Group <[email protected]<mailto:[email protected]>> Subject: Re: Portability snowflake to bigquery Thank you for fast assistance Lukas, I will show a example that it need to do. For example in snowflake : SELECT date_trunc('month',received_at)::date AS dt, name, count(distinct id) as events FROM tableA WHERE received_at >= '2018-09-01' GROUP BY 1,2 ORDER BY 1 Must convert to bigquery SELECT cast(date_trunc(received_at, month)as date) AS dt, name, count(distinct id) as events FROM tableA WHERE received_at >= '2018-09-01' GROUP BY 1,2 ORDER BY 1 is this already working on version 3.15, I would like to understand what is already working. And thanks again for your assistance, please if there is another channel to talk please let me know and I will explain what I need. On Tuesday, May 25, 2021 at 3:37:05 AM UTC-3 [email protected]<mailto:[email protected]> wrote: Hi Pedro, Thank you very much for your message and for your interest in our translation service. Both the Snowflake and BigQuery dialects are work in progress for the upcoming jOOQ 3.15 dialect, with BigQuery being almost ready, while Snowflake still requires quite a bit of work. The library's upcoming 3.15 version (available already to paying customers) has one key feature that will definitely improve the experience of the translation use-case: An SPI that allows you to hook into the parser and translate table, column, and predicate expressions that may not yet be supported out of the box: https://www.jooq.org/doc/dev/manual/sql-building/sql-parser/sql-parser-listener/ However, from our experience, 50% of the times when users find limitations in the parser, they are very easy for us to fix really quickly, so if you find any issues, just let us know either here or through the issue tracker: https://github.com/jOOQ/jOOQ/issues/new/choose Please let me know if I can be of any further assistance and I'll be very happy to help, Lukas From: 'Pedro Figueiredo' via jOOQ User Group <[email protected]<mailto:[email protected]>> Sent: Dienstag, 25. Mai 2021 05:32 To: jOOQ User Group <[email protected]<mailto:[email protected]>> Subject: Portability snowflake to bigquery Hello, I'm new here. I was searching for a portability solution than rewrite queries from snowflake to bigquery and I found JOOQ, looks like amazing. I try used https://www.jooq.org/translate/, works not perfectly, I would like to know if the payed version is better. Can you help me with this answer or I don't if this translate it's open to try modify the way I need. Thanks a lot, and your solution its pretty nice. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/557a4fc5-2fb3-4adf-baa8-6b73c8d0bb06n%40googlegroups.com<https://groups.google.com/d/msgid/jooq-user/557a4fc5-2fb3-4adf-baa8-6b73c8d0bb06n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/821fa0b2-aaa3-4a0c-a0eb-321bcb648370n%40googlegroups.com<https://groups.google.com/d/msgid/jooq-user/821fa0b2-aaa3-4a0c-a0eb-321bcb648370n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d7d47ccc-54dd-4557-9cdd-f602141a5a3bn%40googlegroups.com<https://groups.google.com/d/msgid/jooq-user/d7d47ccc-54dd-4557-9cdd-f602141a5a3bn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/AM7PR03MB6610388C835ED1FBE1F0287D9F249%40AM7PR03MB6610.eurprd03.prod.outlook.com<https://groups.google.com/d/msgid/jooq-user/AM7PR03MB6610388C835ED1FBE1F0287D9F249%40AM7PR03MB6610.eurprd03.prod.outlook.com?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/AM7PR03MB6610628B888CAACA2976FF769F249%40AM7PR03MB6610.eurprd03.prod.outlook.com.
