|FastBit Front Page||Research Publications||Software Documentation||Software Download||Software License|
Organization: LBNL » CRD » SDM » FastBit » Documentation » Command Line Tool
IBIS  is an Implementation of Bitmap Indexing
System named FastBit.
This document explains the command line tool named
which is a shorthand for
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.
falkland.jgi-psf.orgas 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
-cis 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  are available in the data directories
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.
-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
-a[ppend] data_dir [[to] output_directory or partition_name]
-ais checked to see it is a directory first. If it is a directory name, then it is used as a directory name, otherwise, the second argument is used to find a data partition with the same name (with a case-insensitive search).
Note: If no second argument is provided, ibis attempts to use the meta tags contained in the table to generate a name. If no meta tags are found, it will generate a random name.
More information about loading data can be found in dataLoading.html.
-b may be followed by one of three types of
arguments, an integer to indicate the number of simultaneous
threads to use while building the indexes, a bare indexing option to indicate
the type of indexes to build for all columns, or a name-option pair to
indicate indexing option for a subset of columns. An
indexing option is a string for the following form,
<binning nbins=1000/> <encoding range ncomp=2/>".
Additional ifnormation about the indexing option is available at indexSpec.html.
A name-option pair starts with a column name or a name pattern,
followed by the semicolon ':' and an indexing option string. There is
no need to put spaces around the semicolon, in fact, ibis.cpp prefers
to have no spaces or any other separator around the semicolon.
The name pattern may use SQL wild cards or c-shell wild cards. The
following example name-option pair
<binning precision=2/>" will index all
columns whose names starting with the letter "c" with the indexing option
Multiple -b options may appear on the command line. The name-option pairs are used in the order as they appear on the command line. Only the last bare indexing option is used if any is specified. The integer values following -b are added together. An option -b appearing by itself is treated the same as '-b 1', therefore multple bare -b options will inrease the number of threads used to build indexes. For example, the following set of -b options,
-b "a%:<binning none/>" -b ".b*:<encoding interval-equality/>" -b -b -b "bit-slice"will build indexes with two threads. All columns with 'a' as the first letter of their name will be index without binning (i.e., "<binning none/>"). Columns whose name does not match the first pattern will be compared to the second pattern, all those with names containing b as the second letter will be indexed with the option "<encoding interval-equality/>". The columns whose names do not match either one of the two patterns will be indexed with the option "bit-slice".
Since building indexes is an expensive operation, FastBit by default
only builds new index for a column if no index currently exists. To
force FastBit to remove existing indexes and rebuild them from
scratch, specify option
-z in addition.
Note: The numbre of threads to use depends heavily on the amount of memory available. Indexes are built one column of a data partition at a time. Each thread works one a column of a partition at any given time, and the index for the column is built completely in-memory before written to disk. If your data partitions are large, then each threads will require a lot of memory to complete it work. The default number of threads to use for index building is one (1).
ibiswill also recursively traverse the directories to find directory pairs with the same name and the matching
-part.txtfiles. Each such pair defined a partition. If different data partitions have the same name, only the last one will be kept.
Using a pair of directories for a data partition was intended to improve reliability and reduce the transition time when appending data. In most cases, it is fine to use only one directory for each data partition, in which case, one simply do not specify "DataDir2". More information about the configuration file is available in dataLoading.html.
-d[atadir] data_dir [backup_dir]
-qor use a query file containing the select statements followed by ';' in a query file using the option
-f. There are also a small number of other commands that can be used in the interactive mode. Type "help" in the interactive mode to see a list.
ibisto the named file. The file is opened in append mode, therefore the existing content is preserved. The only message that may still be printed to standard output is something indicating the name of the message file.
NOTE: this file contains the error messages and other
information. If option
-o is also specified, the file
specified in that option will contain the results of select statements.
ibisto evaluate the number of hits without first performing an estimation.
-estimation-only are mutually exclusive, the one that
appears later will overwrite the one that appears early on the same
ibisto append the result of select statements to the named file/directory. The program
ibisscans for the letter b to determine whether or not to output results in binary first. When outputting in binary format, the name is taken to the directory to contain the output. This binary format is usable by FastBit. If the letter b is not found,
ibiswill write the selected data in ASCII format. If it finds the letter h in this option, it will also write a header before writing the ASCII data.
NOTE: the results from multiple select clauses on the same
command line will be written to the same output file or directory.
When outputting in the binary format, the new results is always
appended, however, when writing in ASCII format, the existing file
content can be erased if the additional opton
-z is also
NOTE: When outputting ASCII data with header, the results from each select clause will have its own header since different select clause might have different columns.
NOTE: When outputting binary data, the resulting directory will contain a superset of all columns. A new set of data is always assumed to be a new set of rows to the data table. Any missing values, such as those existing columns that do not appear in the new set or new columns that do not appear in the old rows, will be assumed to be NULL values.
NOTE: this option controls the output of the results from
select statments only. Other messages, such as errors, progress
information, and debug information, may be redirected with option
-p[rint] [Parts|Columns|Distributions|column-name [: conditions]]
-q[uery] [SELECT ...] [FROM ...] WHERE ...
-qmust be quoted in order for them to be perceived as one argument.
ibisto verify the RIDs can be used in queries of their own. If the optional file name is present, the RIDs will also be written into the named file.
ibisto reorder the data in the specified directory. An optional list of column name may be used to specify which columns are to be used as sorting keys. If no key is specified, the integer valued columns will be used and the one with the smallest range of values will be used first.
-zcan be used together with
-yto tell IBIS to zap the inactive rows away.
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. In the printout, the number of selected records is printed at the end. This is equivalent to adding 'count(*)' to the end of every (non-empty) select clause.
The supported functions are:
ibisto run out of memory and crash!
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.
ibis::part::readTDC (Tue Jun 14 03:30:32 2005 UTC) --- failed to open file "/home/xhe/TRAC/tmp/dir1/-part.txt" -- No such file or directory buildTables: directory /home/xhe/TRAC/tmp/dir1 does not contain a valid
-part.txtfile or contains an empty table. Make sure the parameter Number_of_events has the correct value Completed construction of an ibis::part named 200506. 1766540 records each with 36 attributes ./ibis: batch mode, log level 1 Tables: 200506 Query: select mchn, tmpr where ! (tmpr == 55 or tmpr == 60) doQuery (Tue Jun 14 03:31:13 2005 UTC) --- processing query ! (tmpr == 55 or tmpr == 60) on table 200506 query[LT55J4AkJu400000]::setWhereClause -- WHERE "! (tmpr == 55 or tmpr == 60)" query[LT55J4AkJu400000]::setSelectClause -- SELECT mchn,tmpr ibis::column[200506.Tmpr](INT)::readIndex -- the basic index was read (/home/xhe/TRAC/tmp/dir1/200506) in 0 sec(CPU), 0.00101352 sec(elapsed) query[LT55J4AkJu400000]::estimate -- time to compute the bounds: 0.01 sec(CPU), 0.00476098 sec(elapsed). query[LT55J4AkJu400000]::estimate -- # of hits for query "! (tmpr == 55 or tmpr == 60)" is 0 doQuery (Tue Jun 14 03:31:13 2005 UTC) --- number of hits in [0, 0] query[LT55J4AkJu400000]::evaluate -- time to compute the 15744 hits: 0 sec(CPU), 0.000848532 sec(elapsed). query[LT55J4AkJu400000]::evaluate -- user kwu SELECT mchn,tmpr FROM 200506 WHERE ! (tmpr == 55 or tmpr == 60) ==> 15744 hit(s). doQuery (Tue Jun 14 03:31:13 2005 UTC) --- number of hits = 15744 doQuery:: evaluate(! (tmpr == 55 or tmpr == 60)) took 0.00626969 sec(elapsed) ibis::part::getRIDs -- number of RIDs (0) does not match the size of the mask (1766540) Tue Jun 14 03:31:13 2005 UTC Warning -- query[LT55J4AkJu400000]::getRIDs -- got 0 row IDs from table 200506, expected 15744 ibis::part::getRIDs -- number of RIDs (0) does not match the size of the mask (1766540) ibis::column[200506.MCHN](KEY)::readIndex -- the basic index was read (/home/xhe/TRAC/tmp/dir1/200506) in 0 sec(CPU), 0.000895739 sec(elapsed) Query LT55J4AkJu400000 produces 32 distinct tuples of columns mchn,tmpr MegaBACE # MB424 -128 MegaBACE # MB424 -120 MegaBACE # MB424 -112 MegaBACE # MB424 -104 MegaBACE # MB424 -96 MegaBACE # MB424 -88 MegaBACE # MB424 -80 MegaBACE # MB424 -72 MegaBACE # MB424 -64 MegaBACE # MB424 -56 MegaBACE # MB424 -48 MegaBACE # MB424 -40 MegaBACE # MB424 -32 MegaBACE # MB424 -24 MegaBACE # MB424 -16 MegaBACE # MB424 -8 MegaBACE # MB424 0 MegaBACE # MB424 8 MegaBACE # MB424 16 MegaBACE # MB424 24 MegaBACE # MB424 32 MegaBACE # MB424 40 MegaBACE # MB424 48 MegaBACE # MB424 56 MegaBACE # MB424 64 MegaBACE # MB424 72 MegaBACE # MB424 80 MegaBACE # MB424 88 MegaBACE # MB424 96 MegaBACE # MB424 104 MegaBACE # MB424 112 MegaBACE # MB424 120 Cleaning up table 200506 Cleaning up the file manager Total pages accessed through read(unistd.h) is 145
The number of hits is printed in the following line
query[LT55J4AkJu400000]::evaluate -- user ... ==> 15744 hit(s).
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.