Hello again Soumyadeep, Another thing I forgot to talk about. For your use case, maybe something like Apache FreeMarker will be a better fit as Calcite is a bit too heavy-handed for that, I feel :)
- Askar On Sun, 2 Apr 2023 at 18:37, Soumyadeep Mukhopadhyay <[email protected]> wrote: > Hello Askar, > > Thank you so much for taking the time to compile the list I shall try them. > :) > > With regards, > Soumyadeep Mukhopadhyay. > > On Sun, Apr 2, 2023 at 8:59 PM Oscar Mulin <[email protected]> wrote: > > > Hey Soumyadeep, > > I think that can work with a few caveats. > > 0) Use the Planner from Frameworks > > < > > > https://calcite.apache.org/javadocAggregate/org/apache/calcite/tools/Frameworks.html > > > > > 1) Parse the “template query" into a syntax tree (a root SqlNode) but do > > not validate it > > 2) Cast the root SqlNode into a SqlSelect > > < > > > https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSelect.html > > > > > (can > > verify that it's a SELECT by using getKind()) > > 3) Use the setters from SqlSelect to modify the children of root > SqlSelect > > node. > > 4) Unparse the root query back into a string (SqlNode.unparse()). > > > > Now the tricky part is 3), as you have to set proper SqlNode types as > > children of the SELECT node for column names, table names and for WHERE > > predicate. I don't remember them properly, but what you can do is parse a > > proper query (again, don't validate it as you'll then need table > metadata), > > and check the kinds (getKind()) of children SqlNode's and replace them > with > > your replacements using setters of root SqlSelect node. > > > > This should work, but I'm not 100% certain as I'm unable to check right > > now. > > > > PS: Take care about dialects. Dialects are used in unparsing as a > > "configuration" of SqlWriter, and can unparse the syntax tree differently > > based on the dialect you have chosen. > > > > Kind regards, > > Askar Bozcan > > > > On 2 Apr 2023, at 14:56, Soumyadeep Mukhopadhyay <[email protected]> > > wrote: > > > > Hello All, > > > > I have just heard of Apache Calcite and was exploring the possibilities. > I > > wish to achieve the following, and wanted to check if my hunch is > correct: > > - Use a template to build SQL queries, like use jinja-sql or even pebble > > (interpret the SqlNode tree kind of structure from my template and then > add > > the necessary fields like table name and group by fields from an input) > > > > So what I am expecting is "SELECT ? FROM ? WHERE ?" would be inside a > query > > template (in Jinja-sql it may look like "SELECT {{select_fields | > sqlsafe}} > > FROM {{table_name | sqlsafe}} WHERE {{where_clause | sqlsafe}}" and > values > > like 'select_fields' would be substituted at run-time from a processing > > engine like https://pypi.org/project/Jinja2/, but for Java) and the > output > > would be "SELECT col1 FROM table1 WHERE col1 IS NOT NULL" with some > dialect > > (like Snowflake or Big-Query). > > > > Is this possible? Any recommendations or suggestions are welcome. Even if > > the approach feels wrong please let me know. :) > > Thank you for your time and consideration. > > > > With regards, > > Soumyadeep Mukhopadhyay. > > >
