Thanks, Julian. That's exactly what I'm looking for.
On Thu, Jan 8, 2015 at 11:41 AM, Julian Hyde <[email protected]> wrote:
> Calcite is a library, not a server, so it doesn’t have any data. If you
> create a connection using jdbc:calcite: you get an empty connection — just
> a root schema, to which you can programmatically add schemas like this:
>
> Connection connection;
> CalciteConnection calciteConnection =
> connection.unwrap(CalciteConnection.class);
> calciteConnection.getRootSchema().add(“myschema”, new MySchema());
>
> But if you are running from sqlline, you can’t execute java code to build
> a schema. That’s why we added the JSON catalog facility. If you specify
> catalog=path/to/model.json as part of the connect string, Calcite will read
> the JSON document and build schemas.
>
> Here is an example that works if you are in the root directory of any
> calcite sandbox:
>
> HW10571:calcite.2 jhyde$ ./sqlline
> sqlline version 1.1.7
> sqlline> !connect
> jdbc:calcite:model=core/src/test/resources/hsqldb-foodmart-lattice-model.json
> admin admin
> 15/01/08 11:39:25 INFO HSQLDB4ACB0EFE7C.ENGINE: open start - state modified
> 15/01/08 11:39:32 INFO HSQLDB4ACB0EFE7C.ENGINE: Checkpoint start
> 15/01/08 11:39:32 INFO HSQLDB4ACB0EFE7C.ENGINE: Checkpoint end - txts: 279
> 0: jdbc:calcite:model=core/src/test/resources> !set incremental false
> 0: jdbc:calcite:model=core/src/test/resources> !tables
>
> +------------+--------------+--------------------------------+---------------+----------+-----------+-------------+------------+----------------------------+-----------------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME |
> TABLE_TYPE | REMARKS | TYPE_CAT | TYPE_SCHEM | TYPE_NAME |
> SELF_REFERENCING_COL_NAME | REF_GENERATION |
>
> +------------+--------------+--------------------------------+---------------+----------+-----------+-------------+------------+----------------------------+-----------------+
> | null | adhoc | m{27, 31} | TABLE
> | null | null | null | null | null
> | null |
> | null | adhoc | star | STAR
> | null | null | null | null | null
> | null |
> | null | foodmart | account | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_c_10_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_c_14_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_c_special_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_g_ms_pcat_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_l_03_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_l_04_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_l_05_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_lc_06_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_lc_100_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_ll_01_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | agg_pl_01_sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | category | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | currency | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | customer | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | days | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | department | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | employee | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | employee_closure | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | expense_fact | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | inventory_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | inventory_fact_1998 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | position | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | product | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | product_class | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | promotion | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | region | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | reserve_employee | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | salary | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | sales_fact_1997 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | sales_fact_1998 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | sales_fact_dec_1998 | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | store | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | store_ragged | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | time_by_day | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | warehouse | TABLE
> | null | null | null | null | null
> | null |
> | null | foodmart | warehouse_class | TABLE
> | null | null | null | null | null
> | null |
> | null | metadata | COLUMNS |
> SYSTEM_TABLE | null | null | null | null | null
> | null |
> | null | metadata | TABLES |
> SYSTEM_TABLE | null | null | null | null | null
> | null |
>
> +------------+--------------+--------------------------------+---------------+----------+-----------+-------------+------------+----------------------------+-----------------+
>
>
> And of course you can easily write your own catalog.json file with
> schemas that point to your data sources.
>
> Julian
>
>
> On Jan 8, 2015, at 11:03 AM, Jinfeng Ni <[email protected]> wrote:
>
> > I did some google search to try to figure out how to run query query
> > interactive in calcite's SULLEN, but did not find useful information.
> I'm
> > turing to this list for help.
> >
> > Basically, I want to run queries interactive in calcite. The way I
> > currently used is to add a JUnit test case, and run the JUnit. This is a
> > bit cumbersome. Then, I found calcite has a script of "sqlline" in the
> root
> > directory.
> >
> >
> > Here is the connection of saline:
> >
> > qlline version 1.1.7
> > sqlline> !connect jdbc:calcite: admin admin
> > 0: jdbc:calcite:> !list
> > 1 active connection:
> > #0 open jdbc:calcite:
> >
> > If I run a simple query like the following, sqlline complains TABLE not
> > found.
> >
> > 0: jdbc:calcite:> select * from "hr"."emps";
> > Jan 08, 2015 10:59:37 AM
> > org.apache.calcite.sql.validate.SqlValidatorException <init>
> > SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
> > 'hr.emps' not found
> > Jan 08, 2015 10:59:37 AM org.apache.calcite.runtime.CalciteException
> <init>
> > SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1,
> > column 15 to line 1, column 25: Table 'hr.emps' not found
> > Error: while executing SQL: select * from "hr"."emps" (state=,code=0)
> >
> > Questions I have:
> >
> > 1. How to list all the available schemas /databases/ tables in calcite's
> > sqlline?
> > 2. Is it possible to run queries interactive through sqlline?
> >
> > Thanks for any help!
>
>
