You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@airavata.apache.org by "machristie (via GitHub)" <gi...@apache.org> on 2023/02/01 20:59:52 UTC

[GitHub] [airavata-data-catalog] machristie opened a new issue, #5: Investigate Apache Calcite for query rewriting

machristie opened a new issue, #5:
URL: https://github.com/apache/airavata-data-catalog/issues/5

   We want to use https://calcite.apache.org/ to take a high-level SQL query that is written against a metadata schema and translate that into the actual PostgreSQL query.
   
   From the design doc, we want to take something like this
   
   ```sql
   SELECT *
   FROM smilesdb
   WHERE created_date > '2020-01-01' AND absorb < 300.0
   ORDER BY created_date desc
   LIMIT 10;
   ```
   
   and transform it into this
   
   ```sql
   SELECT
       dp.*
   FROM
       data_product dp
       INNER JOIN data_product_metadata_schema dpms ON dp.data_product_id = dpms.data_product_id
       INNER JOIN metadata_schema ms ON ms.metadata_schema_id = dpms.metadata_schema_id
   WHERE
       nullif(metadata ->> 'absorb', '') :: float < 300.0
       AND created_date > '2020-01-01'
       AND ms.schema_name = 'smilesdb';
   ```
   
   Note: the query above is missing an authorization where clause, but I think for the initial investigation being able to produce the above query will be sufficient.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@airavata.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airavata-data-catalog] machristie commented on issue #5: Investigate Apache Calcite for query rewriting

Posted by "machristie (via GitHub)" <gi...@apache.org>.
machristie commented on issue #5:
URL: https://github.com/apache/airavata-data-catalog/issues/5#issuecomment-1478463547

   Some useful links that I found in my investigation:
   
   - [java - how to traverse sqlNode in calcite - Stack Overflow](https://stackoverflow.com/questions/60465600/how-to-traverse-sqlnode-in-calcite)
   - [SQL parsing in java using Apache-Calcite SQL parser.](https://gist.github.com/piyusht007/d3946045caa60abd839d4355f79624b5)
   - Calcite doesn't support PostgreSQL JSON functions. And PostgreSQL doesn't yet support the standard JSON functions, see https://www.depesz.com/2022/04/01/waiting-for-postgresql-15-sql-json-query-functions/
     - https://issues.apache.org/jira/browse/CALCITE-4739
     - [SQL language - JSON Functions](https://calcite.apache.org/docs/reference.html#json-functions)
   - [Apache Calcite Tutorial – Code Tinkering](https://codetinkering.com/apache-calcite-tutorial/)
   - https://raw.githubusercontent.com/zabetak/slides/master/2021/boss-workshop/apache-calcite-tutorial.pdf
   - [zabetak/calcite-tutorial](https://github.com/zabetak/calcite-tutorial)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@airavata.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airavata-data-catalog] machristie commented on issue #5: Investigate Apache Calcite for query rewriting

Posted by "machristie (via GitHub)" <gi...@apache.org>.
machristie commented on issue #5:
URL: https://github.com/apache/airavata-data-catalog/issues/5#issuecomment-1446458019

   I've been investigating how to integrate Calcite to generate the kind of metadata schema queries that we want to support. However, I've run into a problem. Calcite doesn't support PostgreSQL JSON functions. And PostgreSQL doesn't yet support the standard JSON functions, see https://www.depesz.com/2022/04/01/waiting-for-postgresql-15-sql-json-query-functions/
   
   All in all, I think it's going to be a lot of work to try to get Calcite to work with PostgreSQL the way we want. I'm going to try working with just parsing and validating the query and then writing my own code to generate the PostgreSQL query from the parsed information.
   
   So the next step is to see if I can register the metadata schemas as virtual tables with Calcite so it can properly parse and validate user supplied queries.
   
   see also 
   
   - https://issues.apache.org/jira/browse/CALCITE-4739
   - [SQL language - JSON Functions](https://calcite.apache.org/docs/reference.html#json-functions)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@airavata.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airavata-data-catalog] machristie closed issue #5: Investigate Apache Calcite for query rewriting

Posted by "machristie (via GitHub)" <gi...@apache.org>.
machristie closed issue #5: Investigate Apache Calcite for query rewriting
URL: https://github.com/apache/airavata-data-catalog/issues/5


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@airavata.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org