Hey Askar, It worked exactly how you suggested.
``` private val sqlQuery: String = "SELECT list_of_columns FROM table_name WHERE predicate_column = 'predicate_value'" private val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder.build() private val planner: Planner = Frameworks.getPlanner(frameworkConfig) private val planner2: Planner = Frameworks.getPlanner(frameworkConfig) private val planner3: Planner = Frameworks.getPlanner(frameworkConfig) private val sqlNode: SqlNode = planner.parse(sqlQuery) println(sqlNode.getKind) private val sqlSelectStmt: SqlSelect = sqlNode.asInstanceOf[SqlSelect] private val setSelectColumnsQuery = "SELECT age" private val selectList = planner2.parse(setSelectColumnsQuery).asInstanceOf[SqlSelect].getSelectList private val setFromTableQuery = "SELECT employee" private val fromTable = planner3.parse(setFromTableQuery).asInstanceOf[SqlSelect].getSelectList sqlSelectStmt.setSelectList(selectList) sqlSelectStmt.setFrom(fromTable) private val finalQuery = sqlSelectStmt.asInstanceOf[SqlNode] println(finalQuery.toSqlString(SnowflakeSqlDialect.DEFAULT)) ``` The only caveat I see is the need for a new planner for every new query. Should I do something else or is this expected? On a different note, I wanted to ask about how I can write my own rules. Is it possible to enforce following rules: - Only return results above a certain threshold when using GROUP BY, for example (HAVING COUNT(col1) > 100). - Restrict the column on which joins can happen, or else throw an error (almost like analysisException in Spark) - Restrict the columns that can be passed in a select statement or else throw an error (like in the point above) Not sure if it is feasible, please feel free to suggest. :) Thanks again for your time! With regards, Soumyadeep Mukhopadhyay. On Sun, 2 Apr 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. >
