Hi,
Thanks a lot for the explanation, I could get a count query executed.
I am currently testing joins in FastBit and I want to test a join between
two tables using jRange class. This is the query I want to run.
SELECT count(*) FROM part1, part2 WHERE 9000 <= part1.col1 - part2.col2 <=
5 and part1.col3>3 and part2.col4<4000;
I have created two parts part1 and part2 from data that is converted to
binary form from ardea.
1) ibis::part part1("/fastbitdata/table1", 0, false);
2) ibis::part part2("/fastbitdata/table2", 0, false);
Then I have created four columns col1, col2, col3, col4 from data in part1
and part2.
3) ibis::column* col1= part1.getColumn("miles");
4) ibis::column* col2= part1.getColumn("gas");
5) ibis::column* col3= part2.getColumn("mpg");
6) ibis::column* col4= part2.getColumn("rate");
I have tried running this.
7) ibis::jRange *jR = new ibis::jRange(part1, part2, *col1,*col2, 9000, 5,
qEx1, qEx1, &sele, &frm, 0);
where
8) ibis::selectClause sele =ibis::selectClause("count (*)");
9) ibis::fromClause frm = ibis::fromClause("from part1, part2");
and
10) ibis::qExpr* qEx1((ibis::qExpr *) "col3>3");
11) ibis::qExpr* qEx2((ibis::qExpr *) "col4<4000");
But when this is run, it exits with an error code when parsing qExpr.
- Can you please help me in running this query properly.
- And also how to get the output of this execution from jR?
- Does fastbit supports joins between four tables? As I have seen it
supports only joins between two tables. I want to know whether fastbit can
run a query as follows.
select count(*) as c
from store_sales
,household_demographics
,time_dim, store
where store_sales.ss_sold_time_sk = time_dim.t_time_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and store_sales.ss_store_sk = store.s_store_sk
and time_dim.t_hour = 8
and time_dim.t_minute >= 30
and household_demographics.hd_dep_count = 5
and store.s_store_name = 'ese'
order by c
limit 100;
I am new to C++ and would be grateful if you can help me in solving these
issues.
Thank you.
On 18 January 2017 at 21:52, John Wu <[email protected]> wrote:
> Hi, Sasini,
>
> The logic behind the result set size is as follows:
>
> "Select count(miles) ... Where miles>15000" returns a SQL table of
> with 1 row
>
> "Select miles ... where miles>15000" returns table with 247 rows
>
> The content of the returned table in the first case is a single value
> (i.e., count(miles)), while the content in the second case is a list
> of 247 miles values.
>
>
> Regarding the behavior of fastBit.get_qualified_longs, the intention
> is for you to use the query handle as a mask to retrieve values from
> the named column. The more general use case is for one to issue a
> select query with where-clause only and then retrieve the values from
> the selected rows for other computation. This is meant for users who
> want to do more in-depth analysis with their own code. Clearly, this
> behavior does not follow the SQL standard.
>
> John
>
>
> On 1/18/17 1:21 AM, sasini madhumali wrote:
> > Hi John,
> >
> > Thank you for quick response and it helped me to understand what is
> > happening there.
> >
> > I have another issue with running a COUNT query using Java Native
> > Interface that FastBit has provided.
> >
> > If I run "FastBit.QueryHandle handle =
> > fastBit.build_query("count(miles)", "truck_mileage_index",
> > "miles>15000");"
> > The following method returns a value(247) as the result set size which
> > is greater than 1.
> > int cnt = fastBit.get_result_size(handle);
> > For "FastBit.QueryHandle handle = fastBit.build_query("miles",
> > "truck_mileage_index", "miles>15000");" also it gives 247.
> > Shouldn't that 'result size' be 1 and its value is 247 for the
> > COUNT(miles) while 'result size' is 247 for the second query.
> >
> > Though I run "FastBit.QueryHandle handle =
> > fastBit.build_query("miles", "truck_mileage_index", "miles>15000");",
> > It gives the values of column "gas" from the following method.
> > long[] c = fastBit.get_qualified_longs(handle,"gas");
> > Shouldn't that query's result set consists only with the qualified
> > 'miles' column? How does it give the qualified 'gas' values when I
> > have not include the gas column anywhere in the query ? Does it
> > return all columns in the table which satisfy where clause as the
> > result set ?
> >
> > Therefore I feel like the method "fastbit.build_query" does not
> > consider the select clause I provide and it takes a default value. It
> > would be great if you can explain me those.
> >
> > Thanks,
> > Sasini
> >
> >
> > On 16 January 2017 at 23:42, John Wu <[email protected]> wrote:
> >> Dear Sasini,
> >>
> >> Thanks for your interest in our work.
> >>
> >> The implementation of "COUNT(miles)" does read the values of miles. If
> >> you want to avoid this use "COUNT(*)".
> >>
> >> In terms of execution time, I would expect the three versions to go
> >> from fast to slow as follows:
> >> (1) "SELECT COUNT(*) WHERE gas>1500"
> >> (2) "SELECT miles WHERE gas>1500"
> >> (3) "SELECT COUNT(miles) WHERE gas>1500"
> >>
> >> Here is a caveat, if the variable miles contains NULL values,
> >> COUNT(miles) will skip those NULL values, while "COUNT(*)" would have
> >> no way of knowing about the NULL values and couldn't skip them.
> >>
> >> John
> >> _______________________________________________
> >> FastBit-users mailing list
> >> [email protected]
> >> https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
> > _______________________________________________
> > FastBit-users mailing list
> > [email protected]
> > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
> >
> _______________________________________________
> FastBit-users mailing list
> [email protected]
> https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
>
_______________________________________________
FastBit-users mailing list
[email protected]
https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
