Re: Stored Proc to Relational Expression
Stamatis, you are correct. The goal of LINQ and Linq4j is to allow SQL-like expressions in procedural code, not to translate the procedural code into a different language. Linq4j is not under active development (although it part of Calcite, and we use it, and enhance it when necessary) so Linq4j will never to what Ravi wants. My Morel project [1] [2] in some ways continues where Linq4j left off. It is more ambitious, in that it aims to make the "relational" bits of the language indistinguishable from the "other" bits of the language. I chose to extend a functional language (Standard ML) rather than a procedural language because the impedance mismatch is lower. Morel may, at some point, generate Calcite extended relational algebra, and that would include the recursive queries. That would allow Morel queries to run on any back-end that Calcite supports. But even then, I don't think we would generate stored procedures (e.g. PL/SQL). When that happens, Morel will be, in my opinion, a very interesting an powerful front-end to Calcite. I just wrote a Morel program [3] to compute shortest paths and transitive closure in graphs. Morel is more like a query language than a programming language, so the program is a parameterized query that uses recursion. It is interesting to speculate how that query could be translated to a distributed data system like Flink or Spark. Julian [1] https://github.com/julianhyde/morel [2] http://blog.hydromatic.net/ [3] https://github.com/julianhyde/morel/commit/3912347d32b21d611e80bdd878aad24377cdbb9c On Wed, Apr 22, 2020 at 2:11 PM Stamatis Zampetakis wrote: > > I assume that by GitHub page you mean [1]. > For quite some time linq4j is part of calcite [2] so I think that [1] is > not maintained anymore. > The backlog in [1] also looks a bit obsolete. > Julian can correct me if I am wrong. > > In my previous email, I was talking about relational expressions (i.e., > RelNode [3]) and not Expression [4] and Statement [5]. > Furthermore, when I mentioned translating expressions to SQL what I had in > mind is RelToSqlConverter [6]. > Apologies for the confusion. > > I believe LINQ was introduced to bring procedural code and SQL closer and > not use the one to replace the other. > > Best, > Stamatis > > [1] https://github.com/julianhyde/linq4j > [2] https://github.com/apache/calcite/tree/master/linq4j > [3] > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/RelNode.java > [4] > https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Expression.java > [5] > https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Statement.java > [6] > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java > > On Wed, Apr 22, 2020 at 8:54 AM Ravi Kapoor wrote: > > > Yeah Stamatis, I know that as I can see the github page for linq4j that > > lots of task is in Backlog and Link-To-SQL Provider is one of them > > But the intention of the project is to make java objects as Queryable talk > > to DB. > > > > Expressions and various Statement used in linq4j is to generate the > > different constructs like where filter, join to talk the back-end systems > > as Sql Queries. > > > > But what about the normal While loop and If then else statements which is > > not used in the Query-able objects . > > Example could be below: > > > > > > LOOP > > SET heads = RAND() < 0.5; > > IF heads THEN > > SELECT 'Heads!'; > > SET heads_count = heads_count + 1; > > ELSE > > SELECT 'Tails!'; > > BREAK; > > END IF; > > END LOOP; > > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > > > > How linq4j going to convert normal while and If then else java expressions > > to sql query if they are not involved in computation of sql constructs like > > in lambda expression of filter. > > > > In one of the task in backlog I could see: > > *In the prototype LINQ-to-SQL provider, write a simple rule to recognize a > > select list and where clause and push them down to SQL.* > > > > I believe it was never intended for procedural code right? > > > > Thanks, > > Ravi > > > > > > On Wed, Apr 22, 2020 at 2:43 AM Stamatis Zampetakis > > wrote: > > > > > Hi Ravi, > > > > > > As Julian already mentioned, we are quite far from what you would like to > > > achieve. > > > > > > Nevertheless, I would like to mention that you can model certain kind of > > > loops and recursion using the RepeatUnion [1] and Spool [2] relational > > > expressions. Note that these APIs are experimental. > > > > > > Moreover, if your end goal is to push the computation in a DBMS, I have > > to > > > warn you that the code to translate these expressions back to SQL is not > > > there yet. > > > > > > Best, > > > Stamatis > > > > > > [1] > > > > > > > >
Re: Stored Proc to Relational Expression
I assume that by GitHub page you mean [1]. For quite some time linq4j is part of calcite [2] so I think that [1] is not maintained anymore. The backlog in [1] also looks a bit obsolete. Julian can correct me if I am wrong. In my previous email, I was talking about relational expressions (i.e., RelNode [3]) and not Expression [4] and Statement [5]. Furthermore, when I mentioned translating expressions to SQL what I had in mind is RelToSqlConverter [6]. Apologies for the confusion. I believe LINQ was introduced to bring procedural code and SQL closer and not use the one to replace the other. Best, Stamatis [1] https://github.com/julianhyde/linq4j [2] https://github.com/apache/calcite/tree/master/linq4j [3] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/RelNode.java [4] https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Expression.java [5] https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Statement.java [6] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java On Wed, Apr 22, 2020 at 8:54 AM Ravi Kapoor wrote: > Yeah Stamatis, I know that as I can see the github page for linq4j that > lots of task is in Backlog and Link-To-SQL Provider is one of them > But the intention of the project is to make java objects as Queryable talk > to DB. > > Expressions and various Statement used in linq4j is to generate the > different constructs like where filter, join to talk the back-end systems > as Sql Queries. > > But what about the normal While loop and If then else statements which is > not used in the Query-able objects . > Example could be below: > > > LOOP > SET heads = RAND() < 0.5; > IF heads THEN > SELECT 'Heads!'; > SET heads_count = heads_count + 1; > ELSE > SELECT 'Tails!'; > BREAK; > END IF; > END LOOP; > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > How linq4j going to convert normal while and If then else java expressions > to sql query if they are not involved in computation of sql constructs like > in lambda expression of filter. > > In one of the task in backlog I could see: > *In the prototype LINQ-to-SQL provider, write a simple rule to recognize a > select list and where clause and push them down to SQL.* > > I believe it was never intended for procedural code right? > > Thanks, > Ravi > > > On Wed, Apr 22, 2020 at 2:43 AM Stamatis Zampetakis > wrote: > > > Hi Ravi, > > > > As Julian already mentioned, we are quite far from what you would like to > > achieve. > > > > Nevertheless, I would like to mention that you can model certain kind of > > loops and recursion using the RepeatUnion [1] and Spool [2] relational > > expressions. Note that these APIs are experimental. > > > > Moreover, if your end goal is to push the computation in a DBMS, I have > to > > warn you that the code to translate these expressions back to SQL is not > > there yet. > > > > Best, > > Stamatis > > > > [1] > > > > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/RepeatUnion.java > > [2] > > > > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/Spool.java > > > > On Mon, Apr 20, 2020 at 8:08 PM Julian Hyde wrote: > > > > > Calcite relational expressions can represent SELECT, INSERT etc. but > not > > > procedural code. It’s a direction we could consider going. > > > > > > RexProgram is the closest thing we currently have to procedural code in > > > the algebra - single assignment of variables, use of variables in > > > expressions assigning to other variables - but it is a long way short > > > because there are no loops. > > > > > > > On Apr 19, 2020, at 12:47 PM, Ravi Kapoor > > > wrote: > > > > > > > > Hi Team, > > > > > > > > I have my use where I need to convert my dialect specific stored > > > procedure > > > > constructs like while loop, If then else to Rel expression > > > > > > > > Basically this can contain control flow statements like below > > > > > > > > DECLARE heads BOOL; > > > > DECLARE heads_count INT64 DEFAULT 0; > > > > LOOP > > > > SET heads = RAND() < 0.5; > > > > IF heads THEN > > > >SELECT 'Heads!'; > > > >SET heads_count = heads_count + 1; > > > > ELSE > > > >SELECT 'Tails!'; > > > >BREAK; > > > > END IF; > > > > END LOOP; > > > > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > > > > > > > > > > I can create a Java AST model from the linq4j provided by calcite > > however > > > > this is only going to generate Java Result and I believe its only > used > > by > > > > the calcite for relational expressions of enumerable calling > convention > > > > which is used by adapters which does not support core relational > > > operations > > > > right? > > > > > > > > Is there a way I can convert the stored proc constructs into some >
Re: Stored Proc to Relational Expression
Yeah Stamatis, I know that as I can see the github page for linq4j that lots of task is in Backlog and Link-To-SQL Provider is one of them But the intention of the project is to make java objects as Queryable talk to DB. Expressions and various Statement used in linq4j is to generate the different constructs like where filter, join to talk the back-end systems as Sql Queries. But what about the normal While loop and If then else statements which is not used in the Query-able objects . Example could be below: LOOP SET heads = RAND() < 0.5; IF heads THEN SELECT 'Heads!'; SET heads_count = heads_count + 1; ELSE SELECT 'Tails!'; BREAK; END IF; END LOOP; SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); How linq4j going to convert normal while and If then else java expressions to sql query if they are not involved in computation of sql constructs like in lambda expression of filter. In one of the task in backlog I could see: *In the prototype LINQ-to-SQL provider, write a simple rule to recognize a select list and where clause and push them down to SQL.* I believe it was never intended for procedural code right? Thanks, Ravi On Wed, Apr 22, 2020 at 2:43 AM Stamatis Zampetakis wrote: > Hi Ravi, > > As Julian already mentioned, we are quite far from what you would like to > achieve. > > Nevertheless, I would like to mention that you can model certain kind of > loops and recursion using the RepeatUnion [1] and Spool [2] relational > expressions. Note that these APIs are experimental. > > Moreover, if your end goal is to push the computation in a DBMS, I have to > warn you that the code to translate these expressions back to SQL is not > there yet. > > Best, > Stamatis > > [1] > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/RepeatUnion.java > [2] > > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/Spool.java > > On Mon, Apr 20, 2020 at 8:08 PM Julian Hyde wrote: > > > Calcite relational expressions can represent SELECT, INSERT etc. but not > > procedural code. It’s a direction we could consider going. > > > > RexProgram is the closest thing we currently have to procedural code in > > the algebra - single assignment of variables, use of variables in > > expressions assigning to other variables - but it is a long way short > > because there are no loops. > > > > > On Apr 19, 2020, at 12:47 PM, Ravi Kapoor > > wrote: > > > > > > Hi Team, > > > > > > I have my use where I need to convert my dialect specific stored > > procedure > > > constructs like while loop, If then else to Rel expression > > > > > > Basically this can contain control flow statements like below > > > > > > DECLARE heads BOOL; > > > DECLARE heads_count INT64 DEFAULT 0; > > > LOOP > > > SET heads = RAND() < 0.5; > > > IF heads THEN > > >SELECT 'Heads!'; > > >SET heads_count = heads_count + 1; > > > ELSE > > >SELECT 'Tails!'; > > >BREAK; > > > END IF; > > > END LOOP; > > > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > > > > > > > I can create a Java AST model from the linq4j provided by calcite > however > > > this is only going to generate Java Result and I believe its only used > by > > > the calcite for relational expressions of enumerable calling convention > > > which is used by adapters which does not support core relational > > operations > > > right? > > > > > > Is there a way I can convert the stored proc constructs into some > > canonical > > > form like Rel Tree and back to Stored proc of target dialect. > > > -- > > > > > > Thanks, > > > Ravi > > > > > -- Thanks, Ravi Kapoor +91-9818764564 kapoorrav...@gmail.com
Re: Stored Proc to Relational Expression
Hi Ravi, As Julian already mentioned, we are quite far from what you would like to achieve. Nevertheless, I would like to mention that you can model certain kind of loops and recursion using the RepeatUnion [1] and Spool [2] relational expressions. Note that these APIs are experimental. Moreover, if your end goal is to push the computation in a DBMS, I have to warn you that the code to translate these expressions back to SQL is not there yet. Best, Stamatis [1] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/RepeatUnion.java [2] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/core/Spool.java On Mon, Apr 20, 2020 at 8:08 PM Julian Hyde wrote: > Calcite relational expressions can represent SELECT, INSERT etc. but not > procedural code. It’s a direction we could consider going. > > RexProgram is the closest thing we currently have to procedural code in > the algebra - single assignment of variables, use of variables in > expressions assigning to other variables - but it is a long way short > because there are no loops. > > > On Apr 19, 2020, at 12:47 PM, Ravi Kapoor > wrote: > > > > Hi Team, > > > > I have my use where I need to convert my dialect specific stored > procedure > > constructs like while loop, If then else to Rel expression > > > > Basically this can contain control flow statements like below > > > > DECLARE heads BOOL; > > DECLARE heads_count INT64 DEFAULT 0; > > LOOP > > SET heads = RAND() < 0.5; > > IF heads THEN > >SELECT 'Heads!'; > >SET heads_count = heads_count + 1; > > ELSE > >SELECT 'Tails!'; > >BREAK; > > END IF; > > END LOOP; > > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > > > > I can create a Java AST model from the linq4j provided by calcite however > > this is only going to generate Java Result and I believe its only used by > > the calcite for relational expressions of enumerable calling convention > > which is used by adapters which does not support core relational > operations > > right? > > > > Is there a way I can convert the stored proc constructs into some > canonical > > form like Rel Tree and back to Stored proc of target dialect. > > -- > > > > Thanks, > > Ravi > >
Re: Stored Proc to Relational Expression
Calcite relational expressions can represent SELECT, INSERT etc. but not procedural code. It’s a direction we could consider going. RexProgram is the closest thing we currently have to procedural code in the algebra - single assignment of variables, use of variables in expressions assigning to other variables - but it is a long way short because there are no loops. > On Apr 19, 2020, at 12:47 PM, Ravi Kapoor wrote: > > Hi Team, > > I have my use where I need to convert my dialect specific stored procedure > constructs like while loop, If then else to Rel expression > > Basically this can contain control flow statements like below > > DECLARE heads BOOL; > DECLARE heads_count INT64 DEFAULT 0; > LOOP > SET heads = RAND() < 0.5; > IF heads THEN >SELECT 'Heads!'; >SET heads_count = heads_count + 1; > ELSE >SELECT 'Tails!'; >BREAK; > END IF; > END LOOP; > SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); > > > I can create a Java AST model from the linq4j provided by calcite however > this is only going to generate Java Result and I believe its only used by > the calcite for relational expressions of enumerable calling convention > which is used by adapters which does not support core relational operations > right? > > Is there a way I can convert the stored proc constructs into some canonical > form like Rel Tree and back to Stored proc of target dialect. > -- > > Thanks, > Ravi
Stored Proc to Relational Expression
Hi Team, I have my use where I need to convert my dialect specific stored procedure constructs like while loop, If then else to Rel expression Basically this can contain control flow statements like below DECLARE heads BOOL; DECLARE heads_count INT64 DEFAULT 0; LOOP SET heads = RAND() < 0.5; IF heads THEN SELECT 'Heads!'; SET heads_count = heads_count + 1; ELSE SELECT 'Tails!'; BREAK; END IF; END LOOP; SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row'); I can create a Java AST model from the linq4j provided by calcite however this is only going to generate Java Result and I believe its only used by the calcite for relational expressions of enumerable calling convention which is used by adapters which does not support core relational operations right? Is there a way I can convert the stored proc constructs into some canonical form like Rel Tree and back to Stored proc of target dialect. -- Thanks, Ravi
