Hi, Amihay, Glad to hear it is working for you. For the size of problem like regions and nations, it might be a lot better to denomalize the tables to avoid joins. This problem should be small enough that you can denomalize them without too much concern about space.
John On 4/2/13 6:51 AM, amihay gonen wrote: > thanks for the help, it is working now : > > here the command i use : > $ibis -v -q "SELECT nation.name <http://nation.name> FROM > nation,region where region.name <http://region.name>='AFRICA' and > nation.regionkey = region.regionkey " -o $outfile -d $db/nation -d > $db/region > > and the output : > Constructed a part named nation > Constructed a part named region > countQuery::evaluate -- Select count(*) From region Where region.name > <http://region.name> == "AFRICA" --> 1 > countQuery::evaluate -- duration: 0 sec(CPU), 0.0324917 sec(elapsed) > jNatural::count(From nation,region Where region.name > <http://region.name>='AFRICA' and nation.regionkey = region.regionkey > ) -- duration: 0.001 sec(CPU), 0.0315905 sec(elapsed) > select nation.name <http://nation.name> From nation,region Where > region.name <http://region.name>='AFRICA' and nation.regionkey = > region.regionkey -- duration: 0.001 sec(CPU), 0.0845362 sec(elapsed) > doQuaere -- "SELECT nation.name <http://nation.name> FROM > nation,region WHERE region.name <http://region.name>='AFRICA' and > nation.regionkey = region.regionkey " produced a table with 5 rows and > 1 column, took 0.003 CPU seconds, 0.200545 elapsed seconds > /home/agonen/Code/fastbit-ibis1.3.5/examples/.libs/lt-ibis -- total > CPU time 0.006 s, total elapsed time 0.224089 s > > > > On Tue, Apr 2, 2013 at 7:44 AM, K. John Wu <[email protected] > <mailto:[email protected]>> wrote: > > Hi, Amihay, > > Looks like you are mixing the syntax of two different join > expressions, please stay with one. For example, you join in SQL > expression might be something like > > -q "select nation.name <http://nation.name> from nation, region > where region.name <http://region.name> = > 'AFRICA' and name.regionkey = region.regionkey" > > > The -j option of ibis is a hack which probably should not be used > ever, if you really want to try, here is the equivalent of what is > shown above > > -j region nation regionkey name='AFRICA' '' nation.name > <http://nation.name> > > in the above command line > > region == table 1 > nation == table 2 > regionkey is the join column (i.e. region.regionkey = > nation.regionkey) > name='AFRICA' == condition on table 1 > '' == condition on table 2 > nation.name <http://nation.name> == column to output > > Anyway, -j option is a hack, please don't use it. The -q option > should process two-table join correctly following the SQL syntax. > > John > > > > > On 3/30/13 3:54 PM, amihay gonen wrote: > > Hi , I'm try to do join between two tables , but without success. > > any idea , what i'm doing wrong ? > > > > if i'm doing the following query : > > $ibis -v -q "SELECT nation.name <http://nation.name> > <http://nation.name> FROM > > nation,region where region.name <http://region.name> > <http://region.name>='AFRICA' " -o > > $outfile -d $db/nation -d $db/region -j nation region regionkey > > regionkey=region.regionkey regionkey=nation.regionkey > > > > i get : > > > > Constructed a part named nation > > Constructed a part named region > > filter::sift2(SELECT nation.name <http://nation.name> > <http://nation.name> FROM 1 data > > partition WHERE region ...) -- processing data partition region > > countQuery::evaluate -- Select count(*) From region Where > region.name <http://region.name> > > <http://region.name> == "AFRICA" --> 1 > > countQuery::evaluate -- duration: 0.001 sec(CPU), 0.000271094 > sec(elapsed) > > Warning -- filter::sift2(SELECT nation.name <http://nation.name> > <http://nation.name> FROM > > 1 data partition WHERE region ...) failed to append 1 row from > region, > > ierr = -13 > > Warning -- doQuaere(SELECT nation.name <http://nation.name> > <http://nation.name> FROM > > nation,region WHERE region.name <http://region.name> > <http://region.name>='AFRICA' ) failed > > to produce a result table > > countQuery::evaluate -- Select count(*) From nation Where > regionkey == > > region.regionkey --> 25 > > countQuery::evaluate -- duration: 0 sec(CPU), 0.000177332 > sec(elapsed) > > countQuery::evaluate -- Select count(*) From region Where > regionkey == > > nation.regionkey --> 5 > > countQuery::evaluate -- duration: 0 sec(CPU), 9.994e-05 sec(elapsed) > > jNatural::count(From nation Join region Using(regionkey) Where > ...) -- > > duration: 0 sec(CPU), 8.1054e-05 sec(elapsed) > > doJoin(From nation Join region Using(regionkey) Where > > regionkey=region.regionkey And regionkey=nation.regionkey) -- > counted > > 25 hits > > doJoin(From nation Join region Using(regionkey) Where > > regionkey=region.regionkey And regionkey=nation.regionkey) -- > > duration: 0 sec(CPU), 0.000648564 sec(elapsed) > > > > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] <mailto:[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
