{
"info": {
"author": "Carst Vaartjes",
"author_email": "cvaartjes@visualfabriq.com",
"bugtrack_url": null,
"classifiers": [
"Development Status :: 4 - Beta",
"Intended Audience :: Developers",
"Intended Audience :: Information Technology",
"Intended Audience :: Science/Research",
"License :: OSI Approved :: BSD License",
"Operating System :: Microsoft :: Windows",
"Operating System :: Unix",
"Programming Language :: Python",
"Programming Language :: Python :: 2",
"Programming Language :: Python :: 2.7",
"Programming Language :: Python :: 3",
"Programming Language :: Python :: 3.3",
"Programming Language :: Python :: 3.4",
"Programming Language :: Python :: 3.5",
"Topic :: Software Development :: Libraries :: Python Modules"
],
"description": "bquery\n======\n\n\n\nBquery is a query and aggregation framework for bcolz, enabling very fast yet out-of-core big data aggregations on any hardware (from laptops to clusters). Bquery is used in production environments to handle reporting and data retrieval queries over hundreds of files that each can contain billions of records.\n\nBcolz is a light weight package that provides columnar, chunked data containers that can be compressed either in-memory and on-disk. that are compressed by default not only for reducing memory/disk storage, but also to improve I/O speed. It excels at storing and sequentially accessing large, numerical data sets.\n\nThe bquery framework provides methods to perform query and aggregation operations on bcolz containers, as well as accelerate these operations by pre-processing possible groupby columns. Currently the real-life performance of sum aggregations using on-disk bcolz queries is normally between 1.5 and 3.0 times slower than similar in-memory Pandas aggregations. See the Benchmark paragraph below.\n\nIt is important to notice that while the end result is a bcolz ctable (which can be out-of-core) and the input can be any out-of-core ctable, the intermediate result will be an in-memory numpy array. This is because most groupby operations on non-sorted tables require random memory access while bcolz is limited to sequential access for optimum performance. However, this memory footprint is limited to the groupby result length and can be further optimized in the future to a per-column usage.\n\nAt the moment, only two aggregation methods are provided: sum and sum_na (which ignores nan values), but we aim to extend this to all normal operations in the future.\nOther planned improvements are further improving per-column parallel execution of a query and extending numexpr with in/not in functionality to further speed up advanced filtering.\n\nThough nascent, the technology itself is reliable and stable, if still limited in the depth of functionality. Visualfabriq uses bcolz and bquery to reliably handle billions of records for our clients with real-time reporting and machine learning usage.\n\nBquery requires bcolz. The user is also greatly encouraged to install numexpr.\n\nAny help in extending, improving and speeding up bquery is very welcome.\n\nUsage\n--------\n\nBquery subclasses the ctable from bcolz, meaning that all original ctable functions are available while adding specific new ones. First start by having a ctable (if you do not have anything available, see the '''bench_groupby.py''' file for an example.\n\n import bquery\n # assuming you have an example on-table bcolz file called example.bcolz\n ct = bquery.ctable(rootdir='example.bcolz')\n\nA groupby with aggregation is easy to perform:\n\n ctable.groupby(list_of_groupby_columns, agg_list)\n\nThe `agg_list` contains the aggregation operations, which can be:\n* a straight forward list of columns (a sum is performed on each and stored in a column of the same name)\n - `['m1', 'm2', ...]`\n- a list of lists where each list gives input column name and operation)\n - `[['m1', 'sum'], ['m2', 'count'], ...]`\n- a list of lists where each list additionally includes an output column name\n - `[['m1', 'sum', 'm1_sum'], ['m1', 'count', 'm1_count'], ...]`\n\n### Supported Operations\n* `sum`\n* `mean` arithmetic mean (average)\n* `std` standard deviation\n* `count`\n* `count_na`\n* `count_distinct`\n* `sorted_count_distinct`\n\n### Examples\n\n # groupby column f0, perform a sum on column f2 and keep the output column with the same name\n ct.groupby(['f0'], ['f2'])\n\n # groupby column f0, perform a count on column f2\n ct.groupby(['f0'], [['f2', 'count']])\n\n # groupby column f0, with a sum on f2 (output to 'f2_sum') and a mean on f2 (output to 'f2_mean')\n ct.groupby(['f0'], [['f2', 'sum', 'f2_sum'], ['f2', 'mean', 'f2_mean']])\n\n\nIf recurrent aggregations are done (typical in a reporting environment), you can speed up aggregations by preparing factorizations of groupby columns:\n\nctable.cache_factor(list of all possible groupby columns)\n\n # cache factorization of column f0 to speed up future groupbys over column f0\n ct.cache_factor(['f0'])\n\nIf the table is changed, the factorization has to be re-performed. This is not triggered automatically yet.\n\nBuilding & Installing\n---------------------\n\nClone bquery to build and install it\n\n```\ngit clone https://github.com/visualfabriq/bquery.git\ncd bquery\npython setup.py build_ext --inplace\npython setup.py install\n```\n\nTesting\n-------\n```nosetests bquery```\n\nBenchmark 1: Comparison to cytoolz and pandas\n----------\nShort benchmark to compare bquery, cytoolz & pandas \n```python bquery/benchmarks/bench_groupby.py```\n\nResults might vary depending on where testing is performed \n\nNote: ctable is in this case on-disk storage vs pandas in-memory \n\n```\nGroupby on column 'f0'\nAggregation results on column 'f2'\nRows: 1000000\n\nctable((1000000,), [('f0', 'S2'), ('f1', 'S2'), ('f2', '