Hello Claudia, Thank you for your interest in using jOOQ with Apache Hive, and for the head-start you're giving us regarding SQLite syntax being similar. I will comment in-line
2014-05-12 21:12 GMT+02:00 Claudia Isabel Núñez < [email protected]>: > *Hi,* > > I've been using jooq to write hive sql statement. I found SQLite to be > very similar to the Hive dialect, but there certain differences like the > offset clause, which is not supported in hive. > > I was wondering if there is a way to extend/update sql clauses of query > parts to update/change the expected behavior of jOOQ when toSQL() method is > used? > > I followed the recommendations on this one-year old topic: > > > https://groups.google.com/forum/?hl=es#!searchin/jooq-user/hive/jooq-user/Nt7cc7QnFqI/wslQw2DiyWIJ > We haven't given this feature request much attention in the last year, as the 16 currently supported RDBMS have generated enough traction for us. After a more in-depth analysis, we have concluded that externalising SQL dialect configuration to external feature sets will not really help users generate SQL dialects for new databases like Hive, nor will it work well with all the other features, such as the VisitListener SPI. In other words, (if at all) we had rather natively support the Hive dialect itself. The relevant feature request is here: https://github.com/jOOQ/jOOQ/issues/2340 A word of caution, however: We're generally a bit wary of supporting any of these SQLesque dialects, that deviate from the SQL standard in a similar extent as Hive or Cassandra's CQL, or Adobe's JCR-SQL2 do. Any jOOQ integration for those dialects might be a very leaky abstraction with 80% of the jOOQ API being unusable, and 50% of the Hive dialect being unavailable. However, this is not the last word, so chances for #2340 being implemented aren't zero :-) Any contribution would be greatly appreciated, of course... What has happened in the past is that jOOQ users have created a jOOQ-esque API dedicated for CQL. Some insight here: - http://blog.jooq.org/2014/01/27/introducing-cqlc-a-query-dsl-for-cassandras-cql-in-go-inspired-by-jooq/ - https://groups.google.com/forum/#!topic/jooq-user/Kmc0_ydszCA - http://relops.com/cqlc/ I believe ExecuteListener cannot be used in this case, because the sql > queries being constructed using the SQLite dialect are never executed > through jOOQ. I only extract the sql string using toSQL(). > You're right, but even if it were possible, the only place you could hook into would be the ExecuteListener.renderEnd() event, which will provide you the same SQL String as toSQL() itself. So, in both cases, the only way for you to patch the generated SQL is by using a parser or a regular expression. Both solutions seem to be a bit of a hack. > I tried using VisitListener but I haven't been able to found the Clause or > QueryPart associated with LIMIT or OFFSET. > True, that's a missing feature, thanks for pointing this out to us. I have added a feature request for this: https://github.com/jOOQ/jOOQ/issues/3261 > Another thing that is different between Hive and SQLite is the INSERT INTO > clause. In hive, you have to write the complete INSERT INTO TABLE > expression. This is another piece I would like to change/extend. > > Is there any way I could possibly accomplish my previous two use cases: > > 1) Getting rid of the OFFSET piece > 2) Adding "TABLE" to the INSERT INTO clause > Hmm, what about these: - INSERT OVERWRITE - INSERT .. PARTITION - INSERT .. IF NOT EXISTS - INSERT .. DIRECTORY -- 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]. For more options, visit https://groups.google.com/d/optout.
