Hi, Amihay, Yes, FastBit does support two-table joins. Only certain types of multitable join could be transformed into two-table joins effectively. I have not studied TPCH careful enough to know which queries might have trouble.
In terms of underlying algorithms, the all current joins are implemented using sort-merge join. In may cases, hash join could be faster or consume less resources. John On 3/8/13 12:56 AM, amihay gonen wrote: > Hi John , I've removed the directory and repeat the process and it > seems fine now. > > Regarding the TPCH , thanks for making me aware of fastbit limitation . > > Here is what I intend to do , let me know what do you think : > 1. date - can be save as number (epoch ) and translate back and forth. > 2. I've saw the fastbit can handle 2 table joins , so i guess I'll be > able to perform mutili-tables join in several phases? > > 3. I've some questions to understand better how the code works, i > didn't find documentation (maybe I've missed it) > a. how the group by operations are done ? > b.how the join algorithm works ? > > > thanks. > > > On Wed, Mar 6, 2013 at 8:30 AM, K. John Wu <[email protected] > <mailto:[email protected]>> wrote: > > Hi, Amihay, > > I just took the three rows you included in the message and tried it > with your ardea command line. Things seem to have completed > successfully in my macbook. In these three rows, the value of > returnflag are all 'N'. Not sure what platform you are using.. > > Maybe your run of ardea has encountered some errors. If you have > captured the print out from this command line, would you mind share it > with me? > > Not sure what you plan to do with the data files, but one thing you > should know is that there are many queries from TPCH that can not be > handled by FastBit. For example, FastBit does not deal with date as > nicely as DBMS. FastBit does not do multi-table joins neither. > > John > > > On 3/5/13 2:51 PM, amihay gonen wrote: > > Hi , > > I'm trying to make environment for testing TPCH queries on > fastbit (if > > anyone has those queries "translated" to ibis query format it > will be > > great). > > > > I've started by trying to convert row data (1G rows) to col data > using > > ardea tool > > > > On the big table lineitem , the row data is about 780M and the col > > data got 1.8G ,this was strange , by looking into the directory > where > > the col files are i see the following : > > > > -rw-rw-r--. 1 agonen agonen 211M Mar 6 00:07 comment > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 commitdate > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 discount > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 linenumber > > -rw-rw-r--. 1 agonen agonen 104M Mar 6 00:07 linestatus > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 orderkey > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 partkey > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 price > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 quantity > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 receipdate > > -rw-rw-r--. 1 agonen agonen 958M Mar 6 00:07 returnflag <--- > too big > > for varchar(1) > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 shipdate > > -rw-rw-r--. 1 agonen agonen 256M Mar 6 00:07 shipinstuct > > -rw-rw-r--. 1 agonen agonen 202M Mar 6 00:07 shipmode > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 suppkey > > -rw-rw-r--. 1 agonen agonen 69M Mar 6 00:07 tax > > -rw-rw-r--. 1 agonen agonen 1.6K Mar 6 00:07 -part.txt > > > > the returnflag ,for some reason contain the all record. > > > > I'm using the fastbit-ibis1.3.5 and the command to convet the csv to > > col is : > > > > ~/Code/fastbit-ibis1.3.5/examples/ardea -d > > ~/Code/fastbit_col_data/lineitem.tbl -b \| -m > > "orderkey:int,partkey:int,suppkey:int,linenumber:int,quantity: > > > > float,price:float,discount:float,tax:float,returnflag:text,linestatus:key,shipdate:int,commitdate:int,receipdate:int,shipinstuct:text,shipmode:key,comment:text" > > -t ~/Code/rowdata/lineitem.tbl > > > > > > the base file look like that: > > > > > > 1|155190|7706|1|17|21168.23|0.04|0.02|N|O|1996-03-13|1996-02-12|1996-03-22|DELIVER > > IN PERSON|TRUCK|egular courts above the| > > > > 1|67310|7311|2|36|45983.16|0.09|0.06|N|O|1996-04-12|1996-02-28|1996-04-20|TAKE > > BACK RETURN|MAIL|ly final dependencies: slyly bold | > > > > 1|63700|3701|3|8|13309.60|0.10|0.02|N|O|1996-01-29|1996-03-05|1996-01-31|TAKE > > BACK RETURN|REG AIR|riously. regular, express dep| > > > > > > any idea ? > > > > thanks amihay > > > > > > _______________________________________________ > > 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
