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 <soumyamy...@gmail.com>
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.
