Retrieve data from JSON file instead of database

[Kartik Kudada <ka...@exate.com.INVALID>]

Hi Calcite Dev,

I have the below requirements.
1. Get the result set from the Database
2.  convert to JSON, do some modifications to JSON
3. Again convert to ResultSet from JSON, and send it back to the user as a
resultset

I am not getting how to achieve this using calcite-core as JAR to used in
database tools like DBeaver.

I tried the below workaround but it did not work.
1. run the query to the database using Statement's executeQuery
2. convert ResultSet to a JSON file and store it locally.
3.  query from the local JSON file, using statement.executeQuery .
4. return resultset from step 3.

public ResultSet executeQuery(String sql) throws SQLException {

   ResultSet rs1 = super.executeQuery(sql); // step 1

   // Convert resultSet(rs1) to JSON and store it locally, where
model.json's directory is defined . //step 2

   // change in schema name in sql as per defined in model.json

   ResultSet rs2 = super.executeQuery(sql);  //step 3

  return res2;

}

As of now I am trying only select * from table query, not complex queries .

Please help me , how can i approach in this requirement . It worked
when JSON file is already present , but it fails with Null pointer
exception if storing and then running executeQuery on that.

(Tried sleeping current thred also, no luck)


Thanks in advance .

Regards,

Kartik

Re: Retrieve data from JSON file instead of database

[Julian Hyde <jh...@gmail.com>]

I have thought about building a similar capability. The question is, how to package it?

Maybe a ‘Replay Adapter’ that looks like a Schema, sits on top of another Schema (which is almost certainly backed by a JDBC adapter). The Replay Adapter has a key-value store of some kind, where the key is the text of a SQL query, and the value is a set of rows. When the Replay Adapter receives a query, it checks the key-value store, returns the rows if the key exists, otherwise executes the query on the back end and inserts the (key, value) into the store.

A good format for the key-value store would be a set of text files in a directory. Then you could manage results using git diff, git commit.

In addition to the mode where it generates the results only if the key does not exist, there could be modes where it fails if the key does not exist, and another mode where even if the key exists it will still generate results.

We would want the Replay Adapter to store the original query - which might include several tables in the Replay schema and perhaps other tables elsewhere - so this doesn’t exactly match the concept of an adapter, which works at the table level, not at the whole query level. We could hack it and make it work, nevertheless.

Another packaging for this thing would be a JDBC driver that “wraps” another JDBC driver.

Julian



> On Sep 28, 2022, at 8:31 AM, Kartik Kudada <ka...@exate.com.INVALID> wrote:
> 
> Hi Calcite Dev,
> 
> I have the below requirements.
> 1. Get the result set from the Database
> 2.  convert to JSON, do some modifications to JSON
> 3. Again convert to ResultSet from JSON, and send it back to the user as a
> resultset
> 
> I am not getting how to achieve this using calcite-core as JAR to used in
> database tools like DBeaver.
> 
> I tried the below workaround but it did not work.
> 1. run the query to the database using Statement's executeQuery
> 2. convert ResultSet to a JSON file and store it locally.
> 3.  query from the local JSON file, using statement.executeQuery .
> 4. return resultset from step 3.
> 
> public ResultSet executeQuery(String sql) throws SQLException {
> 
>   ResultSet rs1 = super.executeQuery(sql); // step 1
> 
>   // Convert resultSet(rs1) to JSON and store it locally, where
> model.json's directory is defined . //step 2
> 
>   // change in schema name in sql as per defined in model.json
> 
>   ResultSet rs2 = super.executeQuery(sql);  //step 3
> 
>  return res2;
> 
> }
> 
> As of now I am trying only select * from table query, not complex queries .
> 
> Please help me , how can i approach in this requirement . It worked
> when JSON file is already present , but it fails with Null pointer
> exception if storing and then running executeQuery on that.
> 
> (Tried sleeping current thred also, no luck)
> 
> 
> Thanks in advance .
> 
> Regards,
> 
> Kartik