Also sorry Julian Hyde I forgot to subscribe to the mailing list when I sent my question so I did not see your answer 🤦♂️. I'll have a look at your suggestions.
On Wed, Nov 10, 2021 at 3:45 PM Cyril Catheu <[email protected]> wrote: > Had a deeper look, I can answer my own questions now. > > 1. Adding a list of SqlFunction to a parser? > No need. The SqlParser will parse unknown symbols that look like function > calls as SqlCall nodes with SqlKind "OTHER_FUNCTION" and > SqlFunctionCategory "USER_DEFINED_FUNCTION". > > Note: I was actually getting confused by how functions identifiers are > escaped in the toString method. > For instance: select EXP(col), myMacro(col) from ... will be parsed then > returned as String as EXP(`col`), `myMacro`(`col`) from ... > Not sure to understand why the EXP is not escaped, but I was able to play > with the toSqlString method to get what I want. > > 2. Parse a SQL query, traverse the tree recursively, replace some nodes, > then get back a Sql string ? > Yes, it's a good use case for Calcite. A good inspiration was the Hoist > <https://calcite.apache.org/javadocAggregate/org/apache/calcite/tools/Hoist.html> > class. It replaces constants in q SQL string. > I implemented something similar that replaces my "USER_DEFINED_FUNCTION" > macros. > > It works like a charm. > Have a nice day. > > On Wed, Nov 3, 2021 at 9:53 PM Cyril Catheu <[email protected]> wrote: > >> Hey, >> >> I'm working on a product that executes arbitrary SQL on a "data source". >> A data source implements a SQL language, for instance, it can be MySQL, >> Pinot, BigQuery, etc... >> I'd like to introduce a macro language on top of the SQL. >> Very similar to what is done in grafana: >> https://grafana.com/docs/grafana/latest/datasources/mysql/#macros >> These macros would be resolved before the SQL is executed on the data >> source. >> >> A macro looks like a function but is a string replacement, for instance: >> __timeFilter(timeColumn, start, end) --> timeColumn>=start and >> timeColumn<end >> >> Grafana uses regex replacements, but I'm considering parsing the SQL with >> Calcite to apply the macros safely and manage nested macros/functions. >> >> --> Is this a good use case for Calcite? What seemed interesting to me is >> that Calcite has all those SqlDialect implementations. >> >> I had this flow in mind: >> 1. get the SqlDialect of my datasource >> *2.* add to this dialect my custom macros (add to an SqlFunction list? ) >> 3. parse >> 4. replace >> 5. rebuild >> >> I'm wondering if 2. is possible? I'm not sure to understand how I could >> add a list of SqlFunction to a dialect, or build a new dialect from an >> existing one dynamically. >> >> If you've taken the time to read this message, thanks a lot! >> Have a nice day. >> >> -- >> [image: Startree] <https://www.startree.ai/> >> >> Cyril de Catheu >> >> Software Engineer, StarTree >> >> [email protected] | +33 684-829-908 >> >> [image: Linkedin] <https://www.linkedin.com/in/cyril-de-catheu/>[image: >> Twitter] <https://twitter.com/deCatheu> >> >
