FYI - Druid devs are proposing to embed Calcite in Druid, so that Druid has a native SQL interface.
I think Gian’s plan makes sense; its architecture is similar in a lot of ways with how we are integrating with Phoenix. Calcite’s Druid adapter will still exist, still be useful, and in fact I expect that Druid devs will end up building on it. I replied on the thread: https://groups.google.com/forum/?pli=1#!topic/druid-development/3npt9Qxpjr0 <https://groups.google.com/forum/?pli=1#!topic/druid-development/3npt9Qxpjr0> Julian > Begin forwarded message: > > From: Gian Merlino <g...@imply.io> > Subject: [druid-dev] [Proposal] Built-in SQL for Druid > Date: October 12, 2016 at 9:50:48 AM PDT > To: druid-developm...@googlegroups.com > Reply-To: druid-developm...@googlegroups.com > > Inspired by the Calcite Druid adapter > (https://groups.google.com/d/topic/druid-development/FK5D162ao74/discussion > <https://groups.google.com/d/topic/druid-development/FK5D162ao74/discussion>) > I've been playing around with something similar that lives inside of the > Druid Broker. It seems promising, so in this proposal I'm suggesting we > include an official SQL server inside Druid itself. > > I am hoping that we can: > > 1) Use Calcite for SQL parsing and optimizing, and use Avatica > (https://calcite.apache.org/docs/avatica_overview.html > <https://calcite.apache.org/docs/avatica_overview.html>) for the server and > the JDBC client. > 2) Like the official Calcite Druid adapter, have a set of rules that push > down filters, projections, aggregations, sorts, etc into normal Druid queries. > 3) Unlike the official Calcite Druid adapter, use Druid objects (like > DimFilter, ExtractionFn, etc) as model classes, since it avoids extra code, > helps with type safety, and speeds up development. > 4) Have this all run on the Broker, which would then make normal Druid > queries to data nodes. > 5) Work towards being able to push down more and more SQL into normal Druid > queries over time. > > Current status > > If people are interested in this proposal then I'll clean up the code a bit > and do a PR. Currently it's a rough prototype. Some things that do work: > > 1) Avatica handler running at /druid/v2/sql/ + Avatica JDBC driver > 2) Determining column types with segment metadata queries > 3) Pushing down operator sequences that look like filter -> project -> > aggregate -> project -> sort into groupBy, timeseries, and select queries as > appropriate > 4) Using "intervals" to filter on time when appropriate > 5) LIKE, range, equality, and boolean filters > 6) SUM, MIN, MAX, AVG, COUNT, COUNT DISTINCT > 7) Some extraction fns like SUBSTRING, CHAR_LENGTH > 8) GROUP BY FLOOR(__time TO gran) for time-series > 9) Arithmetic post-aggregations > 10) Filtered aggregations using CASE or using FILTER(WHERE ...) > 11) Semi-joins like SELECT ... WHERE xxx IN (SELECT ...) can run by > materializing the inner result on the broker and applying it to the outer > query as a filter. Obviously doesn't always work, but it works sometimes (and > it works more often than pulling the lefthand side into the Broker…). > > Non-exhaustive list of things that don't work: > > 1) Pushing down filter after aggregate (HAVING) > 2) Push down of anything without a native Druid analog, like multi-column > extraction fns, aggregation of expressions, window functions, etc. > 3) Any extraction fns other than SUBSTRING, CHAR_LENGTH > 4) A lot of time stuff, like x + INTERVAL, FLOOR(__time TO MONTH) = x, etc. > 5) Query time lookups > 6) Select with pagination – only the first 1000 results are used > 7) Any sort of memory usage controls on the Broker side > > FAQ > > 1) Why another SQL on Druid thing? There's already, like, 7 of them. > > I think the fact that there are 7 of them means there's clearly some value in > having a built-in implementation. Partially this is so we can hopefully share > some work between the projects. Partially this is because Druid doesn't > support some things that are needed for well rounded SQL support (like > multi-column extraction fns, aggregations of expressions, etc) and having the > SQL layer inside the Druid repo will make it possible to develop those sorts > of features hand in hand with the SQL planner rules. > > Btw, the 7 that I counted are, in alphabetical order, Calcite > (https://calcite.apache.org/docs/druid_adapter.html > <https://calcite.apache.org/docs/druid_adapter.html>), Drill > (https://groups.google.com/d/msg/druid-development/FK5D162ao74/EnYDjASWCQAJ > <https://groups.google.com/d/msg/druid-development/FK5D162ao74/EnYDjASWCQAJ>), > Druid's own simple grammar (added in 2013, removed in > https://github.com/druid-io/druid/pull/2090 > <https://github.com/druid-io/druid/pull/2090>), Hive > (https://cwiki.apache.org/confluence/display/Hive/Druid+Integration > <https://cwiki.apache.org/confluence/display/Hive/Druid+Integration>), PlyQL > (http://plywood.imply.io/plyql <http://plywood.imply.io/plyql>), Sparkline > (https://github.com/SparklineData/spark-druid-olap > <https://github.com/SparklineData/spark-druid-olap>), and Sql4D > (https://github.com/srikalyc/Sql4D <https://github.com/srikalyc/Sql4D>). > > 2) Is the proposed SQL language actually SQL or is it "SQL-like"? > > In terms of what can be efficiently pushed down to Druid queries, it's > "SQL-like". A lot of common SQL features aren't supported – although I think > it makes sense to add more over time. Technically Calcite does speak full > SQL, but a lot of it at the start would get planned as pulling all the raw > data into the Broker and processing it in Calcite's interpreter. > > 3) Why not use the Druid adapter in Calcite? > > Calcite's Druid adapter doesn't depend on any Druid jars; it implements the > query language and protocol using its own set of model and client classes. > For a builtin approach I wanted to be able to use Druid's own Query, > DimFilter, ExtractionFn, etc in a type-safe way, and wanted to use the query > code that already exists in Druid for discovering and querying data nodes. I > think this will also help speed up development of Druid features that allow > more SQL to be pushed down. > > 4) Can we share work between a builtin Druid SQL and the other SQL on Druid > adapters that people are working on? > > Hopefully! I think it would make sense if a builtin Druid SQL could be used > for whatever Druid supports natively, and external SQL on Druid adapters > could be used when users want to do something that Druid doesn't support. > Sharing the work needed to translate "whatever Druid supports natively" into > Druid queries would help everyone. > > Hive and Drill already use Calcite internally, and I hope it's workable to > stuff Druid's own rules into their planners without changing too much. If > those projects are comfortable embedding druid-server then that should work > straight away. If they aren't comfortable embedding druid-server (perhaps > understandably) then we could bite the bullet and work on a light(er) weight > druid-client jar that has just enough to give us the benefit of type > checking, and does not include all the heavy Druid functionality. > > If you're working on one of those projects, feedback is greatly appreciated. > > 5) What happens when parts of the SQL query can't be converted to a native > Druid query? > > Calcite is rad and runs the parts that can't be pushed down through an > interpreter on the Druid Broker. Of course this means that if you use > constructs that are close to the data and can't be pushed down, like grouping > on CONCAT(foo, bar) or aggregating SUM(3 * bar), potentially a surprisingly > large amount of data will be pulled out into the Broker. This is not great > behavior and something should be done about that… > > 6) What about JOINs? > > I don't know, maybe it makes sense for Druid to have query types usable for > joins in the future. But it doesn't now; the closest thing is query-time > lookups, which is like a broadcast join. Without native join support in > Druid, it makes more sense to pull data out of Druid into another system > (like Drill or Hive or Spark) and do the join there. Even if Druid did > support native joins, there's still some value in using an external execution > engine to join Druid data with data from some other system. Filters and > aggregations can still potentially be pushed down, depending on the query. > > 7) JDBC works, but what about ODBC? > > Avatica's home page says work on an ODBC client has not yet started. The page > at https://hortonworks.com/hadoop-tutorial/bi-apache-phoenix-odbc/ > <https://hortonworks.com/hadoop-tutorial/bi-apache-phoenix-odbc/> is > interesting, since the Phoenix Query Server is also Avatica based, so maybe > that work could be useful? However, it doesn't seem to be open source, and > when I tried to get the binary to work, the Windows ODBC setup thing crashed > after calling getTableTypes. Maybe someone at Hortonworks can comment :) > > Gian > > -- > You received this message because you are subscribed to the Google Groups > "Druid Development" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to druid-development+unsubscr...@googlegroups.com > <mailto:druid-development+unsubscr...@googlegroups.com>. > To post to this group, send email to druid-developm...@googlegroups.com > <mailto:druid-developm...@googlegroups.com>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/druid-development/CACZNdYAVv8WLP1Qw4dzzf60e-3CwzP_%3DFmWeOd_2OvPsfcw8Ag%40mail.gmail.com > > <https://groups.google.com/d/msgid/druid-development/CACZNdYAVv8WLP1Qw4dzzf60e-3CwzP_%3DFmWeOd_2OvPsfcw8Ag%40mail.gmail.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>.