Thanks for your quick reply. I plan to convert the IncidentBeginTime to unix epoch timestamps in my source data anyway - which should also work with FastBit.
Based on your input, I've simplified my query to be: SELECT IncidentBeginMonth, Priority, count(*) Executing this results in the following unhelpful error: tableSelect:: select(IncidentBeginMonth, Priority, count(*), ) failed on table T-era I cranked up the logging (-v=10), but I don't see any details that give me any clue as to what is wrong. Thanks again, Michael On Fri, Aug 17, 2012 at 2:56 PM, K. John Wu <[email protected]> wrote: > > Hi, Michael, > > Thanks for your interest in FastBit. I would encourage you to give > FastBit a try. As for your specific example, > > SELECT IncidentBeginMonth, Priority, count(*) FROM events WHERE > IncidentBeginTime BETWEEN '2011-01-01 00:00:00' AND '2012-12-31 > 23:59:59' GROUP BY IncidentBeginMonth, Priority ORDER BY > IncidentBeginMonth, Priority > > Here are a couple things to note: > > - FastBit does not have explicit support for date and time, you will > need to convert that to something else > > - You can specify the "group-by" clause and the "order-by" clause as > is to the command line tool ibis. It should behave as you expect. > > Due to the presence of function 'count(*)' in the select clause, a > natural interpretation is that the user wants to count the number of > entries for each distinct combinations of IndidentBeginMonth and > Priority. In this case, the group by clause in redundant - it > specifies the same information already implied in the select clause. > The order by clause tell the system in what order to output the > results. The default is to order the columns as they appeared in the > select clause - your example illustrate this default behavior. > Therefore, the order by clause could be neglected as well. > > Hope this helps. > > John > > > > On 8/17/12 1:34 PM, Michael Beauregard wrote: > > Hi all, > > > > I'm building an analytical iOS application that currently uses sqlite > > to slice and dice about 2.5MB (30K rows) of data. I've been able to > > get performance to an acceptable level by relying upon many sql > > indices, one for each slice/dice combination. Moreover, the queries > > would still be too slow unless I ensure each query has its own > > "covering" index. This approach has resulted in an explosion in the db > > size due to the vast indexing required for performance. A 2.5MB > > dataset becomes 45MB when sufficiently indexed. This strategy has > > taken us pretty far, but is at its performance and scalability limits. > > > > My customer recently informed me that we now need to go well > > beyond (at least 10x) our initial scale requirement. Plus he > > apparently needs to add more ways to slice and dice the data, > > basically leading to further explosion of the already unacceptable sql > > index size. This essentially means that I'm not able to continue with > > my current architecture and I'm looking for other options. > > > > I stumbled upon FastBit today while researching various options and > > I'm really intrigued by what I see. However, I don't understand how I > > can express the required queries because of FastBit's implicit > > group-by functionality. Perhaps I'm just missing something obvious, > > but it seems like I need explicit control over the group-by clause. > > > > For instance, here is a query that determines the number of events > > that occurred in each month, broken down by month and priority of the > > event: > > > > SELECT IncidentBeginMonth, Priority, count(*) FROM events WHERE > > IncidentBeginTime BETWEEN '2011-01-01 00:00:00' AND '2012-12-31 > > 23:59:59' GROUP BY IncidentBeginMonth, Priority ORDER BY > > IncidentBeginMonth, Priority > > > > The result is displayed in a stacked bar chart with one bar per month > > and each bar broken down by event priority. This query executes in > > around 200ms on 30K rows on an iPad 3 which is fast enough, but only > > barely. > > > > I would really appreciate if someone could help me figure out how to > > produce an equivalent query in FastBit. I have already imported my > > data and have been trying various queries using the ibis command, but > > I'm kind of stuck. > > > > Thanks for taking the time to read and consider my question. > > > > Michael > > > > > > > > > > _______________________________________________ > > 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
