FastBit
  FastBit Front Page Research Publications Software Documentation Software Download Software License  

Organization: LBNL » CRD » SDM » FastBit » Documentation » Command Line Tool

FastBit IBIS Command Line Interface Document

IBIS [1] is an Implementation of Bitmap Indexing System named FastBit. This document explains the command line tool named ibis, which is a shorthand for Interactive Bitmap Index Search. Under FastBit, user data is organized into containers called tables and each table consists of an arbitrary number of rows and columns. In SQL algebra, the rows and columns are also called tuples and attributes. In this document, we will use the terms attribute and column interchangeably.

A table is physically organized into one or more data partitions, so that one column from a partition can comfortably fit in computer memory. Each data partition is stored in a directory on file systems and the command line tool ibis works with data directories.

An example

Take the dataset on falkland.jgi-psf.org as an example, the following command prints all the machine names (mchn) and the temperature values (tmpr) where the temperature is not one of the nominal values (55 for MegaBace and 60 for ABI).
/home/kwu/bin/ibis -c /home/kwu/bin/ibis.rc -v -q "select mchn, tmpr where ! (tmpr == 55 || tmpr == 60)"
On the particular machine, the most current version of the ibis executable is /home/kwu/bin/ibis. The file name following option -c is the configuration file name. Alternatively, one may directly specify the data directory on command line use '-d data-directory-path'. The particular file contains the current version of JGI trace data header information. The attribute names [2] are available in the data directories /psf/QC/Projects/IBIS/Datasets.

The main option is -q which is followed by a query string. The basic syntax follows that of SQL, however, only the basic features of the SQL's select statement is implemented. Here we will first mention a few limitations that might cause non-descriptive failures of ibis.

The option -v tells ibis to be verbose. If this option is not supplied, only the number of hits and the result of the select clause are printed. The result of the select clause may be appended to a file instead of printed to standard output. To use this option, specify '-output name'.

List of options

Here is the full list of options. Additional information about command line options, including those not mentioned here, please see the inline documentation of ibis.cpp.

Query statement syntax

The command line tool ibis supports a limited version of the SQL select statement. It recognizes four key words, SELECT, FROM WHERE and ORDER BY. The key words are not case-sensitive, neither are the names of variables or functions described below.

The key word SELECT must be followed by a list of attribute names or one of the supported functions, separated by commas (,). The attribute names must be from the available datasets. If any name is not in the available dataset, IBIS treats it same as no attribute provided. If no attribute is provided, the SELECT key must not be used. In which case, only the number of hits would be printed. The four functions each take one argument that must be a column name of an available dataset. The variables not appearing in any functions are implicitly passed to a SQL 'GROUP BY' clause and the functions are defined on the groups defined by this implicit 'GROUP BY' clause. For example, the select clause 'SELECT mchn, avg(q20), min(snra)' will order the selected records according to machine name (mchn), and for each machine the average Q20 score and the minimum SNRA value will be computed. NOTE that the current version of ibis.cpp does not support explicit a 'GROUP BY' clause.

The supported functions are:

The key word FROM must be followed by a list of table names. Conceptually, the data under the management of IBIS are organized into tables; and each table must have a name. The table names in this clause may contain wild cards, '%' and '_', where '%' matches zero or more any characters and '_' matches exactly one character as in SQL "LIKE" expression. If no table name is specified, the key word FROM must not be used. In which case, all know data table would be queried.

The key word WHERE must be followed by a set of range conditions joined by logical operators 'AND', 'OR', 'XOR', and '!'. A range condition can be one-sided as "A = 5" or "B > 10", or two-sided as "10 <= B < 20." The supported operators are = (alternatively ==), <, <=, >, and >=. The range condition that involves only one attribute with constant bounds are known as simple conditions, which can be very efficiently processed by IBIS. A range condition can also involve multiple attributes, such as, "A < B <= 5", or even arithmetic expressions, such as, "sin(A) + fabs(B) < sqrt(cx*cx+cy*cy)". Note all one-argument and two-argument arithmetic functions available in math.h are supported. The key word WHERE and the conditions following it are essential to a query and can not be ommited.

The select clause may also contain arithmetic expressions, e.g.,

-q "select pressure, sqrt(vx*vx+vy*vy+vz*vz) where temperature > 1000"

A select clause of "count(*)" will produce a result table of exactly one row and one column as dictacted by the SQL standard. One would have to examine the content of this trivial table to find out exactly how many hits are produced. To bypass this extra step, simply omitting the select clause in this case.

The key word ORDER BY is optional. If it appears, it can only be followed by list of column names, no wild cards, no arithmetic function. Furthermore, the column names in the ORDER BY clause must be a subset of columns specified as output from the SELECT clause.

Sample output

Here is a sample output produced on October 17, 2014 using the sample data star2002. The following sample output is produced in the directory tests.
$ scripts/star2002.sh
  <<<...output skipped...>>>
$ ../examples/ibis -d star2002 -q "select eventFile, avg(Pt) where primaryTracks > 2900" -v

Constructed a part named star2002
filter::sift2(SELECT eventFile, avg(Pt) FROM 1 data partition WHERE 2900 < ...) -- processing data partition star2002
From star2002 Where 2900 < primaryTracks --> 20
countQuery::evaluate -- duration: 0.014331 sec(CPU), 0.019357 sec(elapsed)
SELECT eventFile, avg(Pt) FROM T-star2002 WHERE primaryTracks > 2900 produced a table with 20 rows and 2 columns
-- the first 2 rows (of 20) from the result table for "SELECT eventFile, avg(Pt) FROM T-star2002 WHERE primaryTracks > 2900"
1811167, 128.994491577148
1811207, 42.6615257263184

tableSelect:: complete evaluation of SELECT eventFile, avg(Pt) FROM T-star2002 WHERE primaryTracks > 2900 took 0.029337 CPU seconds, 0.052798 elapsed seconds
/Users/john/src/ibis/examples/.libs/ibis -- total CPU time 0.037031 s, total elapsed time 0.081198 s

The number of hits is printed in the following line

From star2002 Where 2900 < primaryTracks --> 20

The SELECT clause produced the output with the following heading.

Query LT55J4AkJu400000 produces 32 distinct tuples of columns mchn,tmpr

In this particular case, it prints the machine name with the abnormal temperature, 'MegaBACE # MB 424', and the abnormal temperature values, which incidentally all appears to be multiple of 8.

Endnotes

  1. FastBit IBIS has been released as open source software under LGPL license. You may download a free copy from FastBit download page.
  2. Additional documentation about FastBit can be found on-line at FastBit website. Questions may be directed to fastbit-users@hpcrdm.lbl.gov (free subscription required, click here).