Question about optiq

[Lisen Mu <im...@gmail.com>]

Hi,

Sorry to interrupt but I don't have access to post in optiq-dev for now so
I put it here.

What's the current status of optiq jdbc backend impl? Seems there is some
hard coded info in JdbcTable.

I'm looking into optiq to see the chance of using it in MySQLSE.

It seems to me that I shall convert Drill scanpop into Linq4j Expressions
and submit it to Optiq instead of creating sql string myself, to avoid both
handcrafted sql string concating and sql parsing stage of optiq. And maybe
other SE in the future if Optiq can support more backend.

But when I run LinqFrontJdbcBackTest(Is that the right test to run?) I
traced into JdbcTable which appears to be WIP. If jdbc backend is not
available, is there any other suggestion with optiq?

Thanks,

Re: Question about optiq

[Lisen Mu <im...@gmail.com>]

Julian,

Thanks for the detailed explanation.

On Tue, Apr 30, 2013 at 4:29 PM, Julian Hyde <ju...@gmail.com> wrote:

> Optiq just has basic support for reading from JDBC sources. Full support
> would be to take a tree of relational algebra operators and push as many of
> them down into a SQL query. Right now there's only one operator, JdbcTable,
> and as you noted it hard-codes the schema name as "foodmart". (That
> hard-coding would be easy to solve by adding another field to either
> JdbcTable or JdbcSchema.)
>
> JdbcTable is serving its purpose right now, because it allows us to read
> from a JDBC database into memory, and then execute queries on those
> in-memory tables. (See CloneSchema.) I've developed a set of operators that
> query in-memory tables using Java iterators (see the inner classes of
> JavaRules). These helped me to debug the planning process, and to start
> building in-memory materialized views, which are two of my key goals for
> Optiq.
>
> When we need to, we can build out the JDBC adapter incrementally by adding
> operators such as JdbcFilter and JdbcJoin; a JDBC calling-convention; and
> an operator that "converts" from JDBC calling-convention to the
> "enumerable" convention used by JavaRules by generating a SQL query from
> the tree of JdbcXxx operators under it and executing it using JDBC.
>
> Optiq is designed to support a wide variety of back-ends, but each one
> takes work, so there aren't that many right now. In addition to JDBC, there
> is a Splunk adapter, which supports push down of project and filter [
> https://github.com/julianhyde/optiq-splunk ]; there is Lingual, which
> supports Cascading [ https://github.com/Cascading/lingual ]; and though
> not really a "back end", there is the "Enumerable" adapter in JavaRules I
> mentioned earlier, which can run queries against any Java collection or
> iterator.
>
> LinqFrontJdbcBackTest isn't a bad test to run. You might also try
> JdbcTest.testCloneQueries(); this loads JDBC tables into memory and runs
> various SQL statements on the in-memory tables. To run these tests you need
> to load the FoodMart data set into a database such as MySQL first.
>
> Julian
>
> PS I've CC:ed optiq-dev, and you're now on the list.
>
> On Apr 26, 2013, at 3:39 AM, Lisen Mu <im...@gmail.com> wrote:
>
> > Hi,
> >
> > Sorry to interrupt but I don't have access to post in optiq-dev for now
> so
> > I put it here.
> >
> > What's the current status of optiq jdbc backend impl? Seems there is some
> > hard coded info in JdbcTable.
> >
> > I'm looking into optiq to see the chance of using it in MySQLSE.
> >
> > It seems to me that I shall convert Drill scanpop into Linq4j Expressions
> > and submit it to Optiq instead of creating sql string myself, to avoid
> both
> > handcrafted sql string concating and sql parsing stage of optiq. And
> maybe
> > other SE in the future if Optiq can support more backend.
> >
> > But when I run LinqFrontJdbcBackTest(Is that the right test to run?) I
> > traced into JdbcTable which appears to be WIP. If jdbc backend is not
> > available, is there any other suggestion with optiq?
> >
> > Thanks,
>
> --
> You received this message because you are subscribed to the Google Groups
> "optiq-dev" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to optiq-dev+unsubscribe@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

Re: Question about optiq

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

Optiq just has basic support for reading from JDBC sources. Full support would be to take a tree of relational algebra operators and push as many of them down into a SQL query. Right now there's only one operator, JdbcTable, and as you noted it hard-codes the schema name as "foodmart". (That hard-coding would be easy to solve by adding another field to either JdbcTable or JdbcSchema.)

JdbcTable is serving its purpose right now, because it allows us to read from a JDBC database into memory, and then execute queries on those in-memory tables. (See CloneSchema.) I've developed a set of operators that query in-memory tables using Java iterators (see the inner classes of JavaRules). These helped me to debug the planning process, and to start building in-memory materialized views, which are two of my key goals for Optiq.

When we need to, we can build out the JDBC adapter incrementally by adding operators such as JdbcFilter and JdbcJoin; a JDBC calling-convention; and an operator that "converts" from JDBC calling-convention to the "enumerable" convention used by JavaRules by generating a SQL query from the tree of JdbcXxx operators under it and executing it using JDBC.

Optiq is designed to support a wide variety of back-ends, but each one takes work, so there aren't that many right now. In addition to JDBC, there is a Splunk adapter, which supports push down of project and filter [ https://github.com/julianhyde/optiq-splunk ]; there is Lingual, which supports Cascading [ https://github.com/Cascading/lingual ]; and though not really a "back end", there is the "Enumerable" adapter in JavaRules I mentioned earlier, which can run queries against any Java collection or iterator.

LinqFrontJdbcBackTest isn't a bad test to run. You might also try JdbcTest.testCloneQueries(); this loads JDBC tables into memory and runs various SQL statements on the in-memory tables. To run these tests you need to load the FoodMart data set into a database such as MySQL first.

Julian

PS I've CC:ed optiq-dev, and you're now on the list.

On Apr 26, 2013, at 3:39 AM, Lisen Mu <im...@gmail.com> wrote:

> Hi,
> 
> Sorry to interrupt but I don't have access to post in optiq-dev for now so
> I put it here.
> 
> What's the current status of optiq jdbc backend impl? Seems there is some
> hard coded info in JdbcTable.
> 
> I'm looking into optiq to see the chance of using it in MySQLSE.
> 
> It seems to me that I shall convert Drill scanpop into Linq4j Expressions
> and submit it to Optiq instead of creating sql string myself, to avoid both
> handcrafted sql string concating and sql parsing stage of optiq. And maybe
> other SE in the future if Optiq can support more backend.
> 
> But when I run LinqFrontJdbcBackTest(Is that the right test to run?) I
> traced into JdbcTable which appears to be WIP. If jdbc backend is not
> available, is there any other suggestion with optiq?
> 
> Thanks,