GitLocker: The Coding Marketplace

Description:

parquery 0.2.8

ParQuery
ParQuery is a query and aggregation framework for parquet files, enabling very fast big data aggregations on any hardware (from laptops to clusters). ParQuery is used in production environments to handle reporting and data retrieval queries over hundreds of files that each can contain billions of records.
Parquet is a light weight package that provides columnar, chunked data containers that can be compressed on-disk. It excels at storing and sequentially accessing large, numerical data sets.
The ParQuery framework provides methods to perform query and aggregation operations on Parquet containers using Pandas. It also contains helpers to use pyarrow for serializing and de-serializing dataframes. It is based on an OLAP-approach to aggregations with Dimensions and Measures.
Visualfabriq uses Parquet and ParQuery to reliably handle billions of records for our clients with real-time reporting and machine learning usage. ParQuery requires pyarrow; for details see the requirements.txt.
Aggregation
A groupby with aggregation is easy to perform:
from parquery.aggregate import aggregate_pq
# assuming you have an example on-table Parquet file called example.Parquet
pa_table = aggregate_pq(
'example.Parquet',
groupby_col_list, # a list of the column names (dimensions) over which the aggregation results are presented
aggregation_list, # see the aggregation list explanation below
data_filter=data_filter, # see the aggregation list explanation below
aggregate=True, # a boolean that determines
as_df=False # results can be as a Pandas DataFrame (True) or a pyarrow object (False)
)

Aggregation List Supported Operations
The aggregation_list contains the aggregation operations, which can be:

a straight forward list of columns (a sum is performed on each and stored in a column of the same name)

['m1', 'm2', ...]

a list of lists where each list gives input column name and operation)

[['m1', 'sum'], ['m2', 'count'], ...]

a list of lists where each list additionally includes an output column name

[['m1', 'sum', 'm1_sum'], ['m1', 'count', 'm1_count'], ...]

sum
mean arithmetic mean (average)
std standard deviation
count
count_na
count_distinct
sorted_count_distinct

Data Filter Supported Operations
The data_filter is optional and contains filters to be applied before the aggregation. Push-down filtering is applied to enhance performance using the parquet characteristics. It balances numexpr evaluation and Pandas filtering for optimal performance.
It is a list that has a structure as follows:
data_filter = [[col1, operator, filter_values], ...]

We support the following operators:

in
not in
==
!=
>
>=
<
<=

The first two operators assume the filter_values to be a list of values (e.g. [1, 2, ...]), the others for it to be a direct value (e.g. 1 or "A").
Examples
# groupby column f0, perform a sum on column f2 and keep the output column with the same name
aggregate_pq('example.Parquet', ['f0'], ['f2'])

# groupby column f0, perform a count on column f2
aggregate_pq('example.Parquet', ['f0'], [['f2', 'count']])

# groupby column f0, with a sum on f2 (output to 'f2_sum') and a mean on f2 (output to 'f2_mean')
aggregate_pq('example.Parquet', ['f0'], [['f2', 'sum', 'f2_sum'], ['f2', 'mean', 'f2_mean']])

# groupby column f0, perform a sum on column f2 and keep the output column with the same name, while filtering column f1 on values 1 and 2 and where f0 equals 10
aggregate_pq('example.Parquet', ['f0'], ['f2'], data_filter=[['f1', 'in', [1, 2], ['f0', '==', 10]])

Serialization and De-Serialization
To serialize the pyarrow result easily for it to be transmitted through network traffic, we have transport functions available:
# create a serialized bugger from an aggregation result
from parquery.transport import serialize_pa_table, deserialize_pa_table
pa_table = aggregate_pq('example.Parquet', ['f0'], ['f2'])
buf = serialize_pa_table(pa_table)

# deserialize
pa_table = deserialize_pa_table(buf)

# convert a pyarrow table to pandas
df = pa_table.to_pandas()

Building & Installing
Clone ParQuery to build and install it
git clone https://github.com/visualfabriq/parquery.git
cd parquery
python setup.py build_ext --inplace
python setup.py install

Testing
pytest parquery