You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Renato Marroquín Mogrovejo <re...@gmail.com> on 2015/02/23 00:42:22 UTC
Fwd: Parsing SQL
Hi there,
I have an application that reads data from HDFS using Spark but I need to
add specific relation filters programatically. I could do this by parsing
the SQL string but I thought that Apache Calcite could help on this. I
tried using this:
<code>
private static SqlParser getSqlParser(String sql) {
Quoting quoting = Quoting.DOUBLE_QUOTE;
Casing unquotedCasing = Casing.TO_UPPER;
Casing quotedCasing = Casing.UNCHANGED;
return SqlParser.create(
sql,
SqlParser.configBuilder().setQuoting(quoting)
.setUnquotedCasing(unquotedCasing)
.setQuotedCasing(quotedCasing).build());
}
</code>
with a query like "select *\n from \"foodmart\".\"sales_fact_1997\" as s\n
join \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"" (borrowed from
Calcite tests), but I get:
org.apache.calcite.sql.parser.SqlParseException: Encountered "select" at
line 1, column 1.
Was expecting one of:
"+" ...
Any help is highly appreciated! Thanks!
Renato M.
Re: Parsing SQL
Posted by Julian Hyde <ju...@hydromatic.net>.
If you traverse and modify the SQL parse tree you can achieve some things fairly easily but if you want to do several transformations or a complex transformation you will find that your code is more and more brittle.
Calcite has a much more powerful way of changing SQL queries: planner rules. The core of Calcite is a query-planning engine, which means converting SQL queries to relational algebra and applying rewrite rules to the algebra, optionally guided by a cost model.
Can you tell us more about the SQL transformations you want to achieve, and what is the target engine you will be sending the transformed queries to?
Julian
On Feb 23, 2015, at 1:14 AM, Renato Marroquín Mogrovejo <re...@gmail.com> wrote:
> Thanks Jiunn Jye! That worked.
> Could you please point me to some examples on how to define a SqlVisitor to
> traverse the parse tree and add my filters? Or is there any better way of
> accomplishing what I need?
> Thanks again for the help!
>
>
> Renato M.
>
> 2015-02-23 3:34 GMT+01:00 Jiunn Jye Ng <ji...@gmail.com>:
>>
>> works for me. quick copy paste based on what you have provided.
>>
>>
>> import org.apache.calcite.avatica.util.Casing;
>> import org.apache.calcite.avatica.util.Quoting;
>> import org.apache.calcite.sql.SqlNode;
>> import org.apache.calcite.sql.parser.SqlParseException;
>> import org.apache.calcite.sql.parser.SqlParser;
>>
>> public class TestSqlParser {
>>
>> public static void main(String[] args) {
>>
>> String sql = "select *\n from \"foodmart\".\"sales_fact_1997\" as s\n
> join
>> \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"";
>> Quoting quoting = Quoting.DOUBLE_QUOTE;
>> Casing unquotedCasing = Casing.TO_UPPER;
>> Casing quotedCasing = Casing.UNCHANGED;
>> SqlParser parser = SqlParser.create(sql,
>> SqlParser.configBuilder().setQuoting(quoting)
>> .setUnquotedCasing(unquotedCasing)
>> .setQuotedCasing(quotedCasing).build());
>>
>> try {
>> System.out.println("Here goes");
>> SqlNode sqlNode = parser.parseQuery();
>> System.out.println ("Done Parsing:" + sqlNode);
>> } catch (SqlParseException e) {
>> e.printStackTrace();
>> }
>> }
>> }
>>
>>
>> On Mon, Feb 23, 2015 at 7:42 AM, Renato Marroquín Mogrovejo <
>> renatoj.marroquin@gmail.com> wrote:
>>
>>> Hi there,
>>>
>>> I have an application that reads data from HDFS using Spark but I need
> to
>>> add specific relation filters programatically. I could do this by
> parsing
>>> the SQL string but I thought that Apache Calcite could help on this. I
>>> tried using this:
>>>
>>> <code>
>>> private static SqlParser getSqlParser(String sql) {
>>> Quoting quoting = Quoting.DOUBLE_QUOTE;
>>> Casing unquotedCasing = Casing.TO_UPPER;
>>> Casing quotedCasing = Casing.UNCHANGED;
>>> return SqlParser.create(
>>> sql,
>>> SqlParser.configBuilder().setQuoting(quoting)
>>> .setUnquotedCasing(unquotedCasing)
>>> .setQuotedCasing(quotedCasing).build());
>>>
>>> }
>>> </code>
>>>
>>> with a query like "select *\n from \"foodmart\".\"sales_fact_1997\" as
> s\n
>>> join \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"" (borrowed
> from
>>> Calcite tests), but I get:
>>>
>>> org.apache.calcite.sql.parser.SqlParseException: Encountered "select" at
>>> line 1, column 1.
>>> Was expecting one of:
>>> "+" ...
>>>
>>> Any help is highly appreciated! Thanks!
>>>
>>>
>>> Renato M.
>>>
Re: Parsing SQL
Posted by Renato Marroquín Mogrovejo <re...@gmail.com>.
Thanks Jiunn Jye! That worked.
Could you please point me to some examples on how to define a SqlVisitor to
traverse the parse tree and add my filters? Or is there any better way of
accomplishing what I need?
Thanks again for the help!
Renato M.
2015-02-23 3:34 GMT+01:00 Jiunn Jye Ng <ji...@gmail.com>:
>
> works for me. quick copy paste based on what you have provided.
>
>
> import org.apache.calcite.avatica.util.Casing;
> import org.apache.calcite.avatica.util.Quoting;
> import org.apache.calcite.sql.SqlNode;
> import org.apache.calcite.sql.parser.SqlParseException;
> import org.apache.calcite.sql.parser.SqlParser;
>
> public class TestSqlParser {
>
> public static void main(String[] args) {
>
> String sql = "select *\n from \"foodmart\".\"sales_fact_1997\" as s\n
join
> \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"";
> Quoting quoting = Quoting.DOUBLE_QUOTE;
> Casing unquotedCasing = Casing.TO_UPPER;
> Casing quotedCasing = Casing.UNCHANGED;
> SqlParser parser = SqlParser.create(sql,
> SqlParser.configBuilder().setQuoting(quoting)
> .setUnquotedCasing(unquotedCasing)
> .setQuotedCasing(quotedCasing).build());
>
> try {
> System.out.println("Here goes");
> SqlNode sqlNode = parser.parseQuery();
> System.out.println ("Done Parsing:" + sqlNode);
> } catch (SqlParseException e) {
> e.printStackTrace();
> }
> }
> }
>
>
> On Mon, Feb 23, 2015 at 7:42 AM, Renato Marroquín Mogrovejo <
> renatoj.marroquin@gmail.com> wrote:
>
> > Hi there,
> >
> > I have an application that reads data from HDFS using Spark but I need
to
> > add specific relation filters programatically. I could do this by
parsing
> > the SQL string but I thought that Apache Calcite could help on this. I
> > tried using this:
> >
> > <code>
> > private static SqlParser getSqlParser(String sql) {
> > Quoting quoting = Quoting.DOUBLE_QUOTE;
> > Casing unquotedCasing = Casing.TO_UPPER;
> > Casing quotedCasing = Casing.UNCHANGED;
> > return SqlParser.create(
> > sql,
> > SqlParser.configBuilder().setQuoting(quoting)
> > .setUnquotedCasing(unquotedCasing)
> > .setQuotedCasing(quotedCasing).build());
> >
> > }
> > </code>
> >
> > with a query like "select *\n from \"foodmart\".\"sales_fact_1997\" as
s\n
> > join \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"" (borrowed
from
> > Calcite tests), but I get:
> >
> > org.apache.calcite.sql.parser.SqlParseException: Encountered "select" at
> > line 1, column 1.
> > Was expecting one of:
> > "+" ...
> >
> > Any help is highly appreciated! Thanks!
> >
> >
> > Renato M.
> >
Re: Parsing SQL
Posted by Jiunn Jye Ng <ji...@gmail.com>.
works for me. quick copy paste based on what you have provided.
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
public class TestSqlParser {
public static void main(String[] args) {
String sql = "select *\n from \"foodmart\".\"sales_fact_1997\" as s\n join
\"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"";
Quoting quoting = Quoting.DOUBLE_QUOTE;
Casing unquotedCasing = Casing.TO_UPPER;
Casing quotedCasing = Casing.UNCHANGED;
SqlParser parser = SqlParser.create(sql,
SqlParser.configBuilder().setQuoting(quoting)
.setUnquotedCasing(unquotedCasing)
.setQuotedCasing(quotedCasing).build());
try {
System.out.println("Here goes");
SqlNode sqlNode = parser.parseQuery();
System.out.println ("Done Parsing:" + sqlNode);
} catch (SqlParseException e) {
e.printStackTrace();
}
}
}
On Mon, Feb 23, 2015 at 7:42 AM, Renato Marroquín Mogrovejo <
renatoj.marroquin@gmail.com> wrote:
> Hi there,
>
> I have an application that reads data from HDFS using Spark but I need to
> add specific relation filters programatically. I could do this by parsing
> the SQL string but I thought that Apache Calcite could help on this. I
> tried using this:
>
> <code>
> private static SqlParser getSqlParser(String sql) {
> Quoting quoting = Quoting.DOUBLE_QUOTE;
> Casing unquotedCasing = Casing.TO_UPPER;
> Casing quotedCasing = Casing.UNCHANGED;
> return SqlParser.create(
> sql,
> SqlParser.configBuilder().setQuoting(quoting)
> .setUnquotedCasing(unquotedCasing)
> .setQuotedCasing(quotedCasing).build());
>
> }
> </code>
>
> with a query like "select *\n from \"foodmart\".\"sales_fact_1997\" as s\n
> join \"hr\".\"emps\" as e\n on e.\"empid\" = s.\"cust_id\"" (borrowed from
> Calcite tests), but I get:
>
> org.apache.calcite.sql.parser.SqlParseException: Encountered "select" at
> line 1, column 1.
> Was expecting one of:
> "+" ...
>
> Any help is highly appreciated! Thanks!
>
>
> Renato M.
>