The query planner optimizes it. Why would you want a compiler to optimize SQL? The nature of your data affects how it is optimized! The declarative statement to retrieve data must be interpreted based upon the nature of that data. You can't pre-optimize without knowing something about your data, in which case, you are basically storing some of the information outside of the database.
I don't understand why anyone would prefer SQL to that for anything beyond a simple SQL query. And it's not just my opinion: industry at large uses Spark for production with complex queries. SQL is for analysts.
SQL is just a DSL; it is not the only or primary API for Spark, and there's nothing magical about it. If you ditch it you can get your type safety, composability, and testability back, like so:
See those case classes that neatly encapsulate business objects? Add to that functional transforms that concisely express typical operations like filtering, mapping, and so on, you get something that is simply superior to SQL.
Any ORM provides the same features for any SQL database. There is nothing special going on here. If perhaps the database autogenerated a bunch of classes, maybe that’s interesting? I think some projects have introspected a database and created all the boilerplate language classes before.
There is nothing magical about wrapping database objects in language classes. This has been happening forever.
No, it does not. Furthermore, if you're using an ORM you're not programming in SQL any more. You're using a poor man's Spark. Spark lets you drop down to SQL in all its APIs too.
I don't understand your argument if you're comfortable with ORMs.