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. >
