Jooq: Add support for Google BigQuery

[lukaseder]

This SQL dialect might be worth exploring:

• https://developers.google.com/bigquery
• https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

Some specialties that are worth keeping in mind:

• [ ] String literals and other types of literals work a bit differently: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical
• [ ] There is no support for special characters in identifiers, even in quoted ones (quoting is purely to allow using reserved words as identifiers, such as GROUP)
• [ ] Identifiers are case sensitive irrespective of quoting
• [ ] Identifier qualification is quite interesting, with objects being prefixable by
  
  
  
  • [ ] project
  
  
  • [ ] dataset
  
  
  • [ ] region
  
  
  • [ ] pseudo schemas (e.g. the INFORMATION_SCHEMA, which isn't really a schema)
  
  
  • [ ] combinations of the above
  
  
  • [ ] not at all, e.g. when outside of the FROM clause
  
  
• [ ] Array and struct formats are serialised as JSON and can't be bound as bind variables individually. We'll either have to bind array components like this: [?, ?], or just inline all array and struct bind variables
• [x] BOOL or INT64 types don't support NULL bind variables. We need to bind NULL as a VARCHAR, and cast back to the desired type.
• [ ] NUMERIC literals need a keyword, see https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#numeric_literals
• [ ] CREATE PROCEDURE can work with the standard procedural language implementations, but CREATE FUNCTION needs to generate JavaScript...

[ttjordan]

Is there any update on this?

[lukaseder]

@ttjordan : Not so far. Do you have any specific use-case in mind?

[ttjordan]

I am using Bigquery and I am writing code to generate String representations of Bigquery queries : for example Select x where y etc. I was considering using jOOQ just to generate a query and then obtain its String representation. I don't need db connectors or any other functionality. Do you think jOOQ would be appropriate for this task?

[lukaseder]

From what I can tell, BigQuery's understanding of SQL is very limited, and using jOOQ would be a great exaggeration:
https://developers.google.com/bigquery/query-reference?hl=de

In fact, a similar situation can be seen with Cassandra:
http://blog.jooq.org/2014/01/27/introducing-cqlc-a-query-dsl-for-cassandras-cql-in-go-inspired-by-jooq/

I guess I will close this ticket as it doesn't make sense to implement it. But maybe, you could implement a similar API as CQLC? Ben Hood (@0x6e6562, the author of CQLC) might give you some heads-up in how to take the "good parts" that are suitable for limited NoSQL / NewSQL syntaxes. We'd certainly write a feature article on our blog, should you Open Source it!

[0x6e6562]

The main motivation for me to write CQLC was to deal with Cassandra CQL in Go, so with JOOQ being a JVM library, this wasn't going to help much. That said, even if I was looking for boilerplate reduction in a JVM Cassandra/CQL app, I wouldn't have tried to use JOOQ directly. This is because whilst CQL resembles SQL, they are not the same thing and hence you would run into many problems trying to marry up two different concepts that weren't meant to work with the same abstraction.

JOOQ is brilliant if you are dealing with SQL, but here's the thing: the reason why it is so good at handling SQL databases is because it embraces the power of SQL. Because the SQL-first paradigm permeates through its design, JOOQ is not going to be good for non-SQL concepts. If you try to bend JOOQ into something that only looks like SQL on the surface, you will spend a lot of time trying to get JOOQ to produce the right results. And then when you've spent all of that effort, you're only going to get 10% of the power of JOOQ.

My suggestion is to use JOOQ as inspiration, rather than an implementation.

Feel free to get in touch if you'd like to discuss this any further.

[jscinoz]

For what it's worth, since the last update on this issue, Bigquery has introduced a new SQL dialect that is compliant with the SQL:2011 standard (& has a few extensions beyond that) and rebranded their previous dialect as "Legacy SQL"

[lukaseder]

Thanks for the update, @jscinoz. Indeed this now looks more promising: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

We'll review this - perhaps there's some synergy after all in supporting this dialect along with the new Google Spanner SQL dialect.

This might be done in the jOOQ Enterprise Edition

[kprochazka]

@lukaseder is there any update on this ?

[lukaseder]

@kprochazka No. If there was, this issue would be updated :-)

[lukaseder]

For the record, we're currently evaluating the additiona of a variety of "cloud SQL" dialects, including Amazon Aurora ones, BigQuery is a candidate, and maybe others. I currently cannot make any promises as to when this will ship.

[lukaseder]

Tackling this now for jOOQ 3.15

[lukaseder]

Spanner support is moved to a separate issue: #11454