How are Arrow Data Fusion and Calcite related?

[miguelff]

I asked @andygrove a set of questions related to using Arrow Data Fusion and other tools to build a high level query language compiler to SQL for an ORM. His answers might be of interest to the rest of the community, which additionally might want to chime in with insight. The idea is to follow the conversation in here rather than privately

I've been thinking about creating a query compiler that can convert complex queries written in a high-level language like Prisma's representation into optimized SQL for different databases. I found Calcite quite interesting because it reuses relational algebra and optimization passes, and it supports various database backends through adapters like JDBC.

Then, I came across Apache Arrow DataFusion, which seems like an evolution over Calcite. However, it appears to focus more on building query engines for tabular format files rather than serving as front-ends for existing databases, like Calcite's JDBC adapter does.

In a later email, I told Andy that I found https://prql-lang.org, a higher level language that compiles to SQL and has a lot of overlapping with the initial query compiler I had in mind. He will refer to it below

I have a few clarifying questions: (answers inline as quotes)

• Am I making any wrong assumption about the applicability of Data Fusion?

DataFusion is very modular. You could use it just for SQL parsing for example and not use it for execution at all (this is what the Dask SQL project does - they compile SQL down to a logical plan then map that to Dask operations).

• Is any part of DataFusion suited for any of the moving pieces involved in transforming a higher level query language into a relational algebra, that can be transformed further into optimized SQL targeted at different databases (in their particular flavors)?

DF has a mature logical plan and logical plan optimization rules. It supports compiling SQL to logical plan. You could implement your own query language and map it to logical plan using the SQL compiler as a guide. I am not aware of any functionality for going from logical plan to SQL but it should be possible.

• What are some use cases for which Calcite is more adequate than data fusion, and viceversa? I saw no mention to Calcite in these docs, whatsover. Still, Calcite seems to be actively maintained on GitHub, and there's some clear overlapping between the two. 

Calcite is JVM-based. If you are building a JVM-based solution then Calcite may be a better fit. If you are working in Rust or Python (or other scripting languages) then DataFusion is maybe a better fit.

• Do you have any kind of feedback, question, opinion (even if you think it is a terrible idea) about what I introduced above? --a compiler of a higher level language to optimized SQL, to implement a ORM that can be ported to any runtime supporting WASM-- In particular, do you think it is in principle feasible?

It all sounds feasible. There are mixed opinions out there on whether ORMs are a good idea or not, but there is certainly a large number of people who like them. I have not spend any real time with WASM so I am not sure how much benefit that provides.

• Are there any other tools, or information, that I haven't mentioned, and that you are aware of, that could help anyhow at building something similar to what I introduced above?

You already mentioned PRQL in another email. Have you looked at the new Morel project that Julian Hyde (founder of Calcite) is working on? You may also be interested in https://substrait.io/

Following up the conversation in here so it's open to everyone. Thank you @andygrove! 🙇‍♂️

[alamb]

Some other comments:

Is any part of DataFusion suited for any of the moving pieces involved in transforming a higher level query language into a relational algebra, that can be transformed further into optimized SQL targeted at different databases (in their particular flavors)?

Like Andy says, it is less common to go from plan back to SQL -- however, DataFusion does have the ability to generate https://substrait.io/ plans, which is a developing standard for a lower level relational algebra represenation

What are some use cases for which Calcite is more adequate than data fusion, and viceversa? I saw no mention to Calcite in these docs, whatsover. Still, Calcite seems to be actively maintained on GitHub, and there's some clear overlapping between the two.

There is Rust vs Java difference, as @andygrove says. The other major difference that I know of is that Calcite focuses on the planning /frontend aspect but doesn't have an actual execution engine (so it doesn't execute plans directly). If you do more research and find out more that would be great

Good luck with the project. It sounds interesting!