Target task

We have a user events table T. Below is its structure and part of its data:

Fields in table T:

Computing task:

Find the number of events under each type and that of distinct users who perform that type of event in the specified time period.

Techniques involved:

1. Use binary file storage instead of database storage.

2. Use parallel processing during traversal.

Sample code

1. Dump data from database and store it in a binary file

Stocked data: the data is retrieved from the database and written to a bin file:

A1 Connect to the database, retrieve data from table T and generate a cursor. @x option enables to automatically close database connection after data retrieval finishes.

A2 Export A1’s data to bin file T.btx. @b option enables writing data to a binary file.

Newly-increased data: the newly-increased data can be obtained using a filtering condition in SQL when there is any that needs to be appended to an existing bin file. @a option enables appending data to a bin file.

The newly-increased data can be identified through time stamp. Each day after 0 o’clock we append the newly-generated data in the past day to a bin file:

A1 Get data generated in the previous day through filtering condition and store it in a cursor.

A2 Fetch A1’s data from the cursor and append it to bin file T.btx. @a enables data appending; without it the existing bin file will be overwritten.

2. Perform grouping and aggregation on a bin file

Suppose we need to summarize data that falls in between 2022-03-15 and 2022-06-16:

A1 Generate cursor for bin file "T.btx". @m enables parallel processing through a multicursor; set the default number of parallel threads in esProc configuration file raqsoftconfig.xml, or just write f.cursor@m(n) where n represents the number of parallel threads. It is recommended that n should be less than the number of computer’s CPU cores, otherwise speed will be lower.

A2 Generate two variables – start and end – to filter data by the time stamp. The variables will be passed in through parameters in real-life computations.

A3 Perform filtering and grouping & aggregation on A1’s cursor. The grouping field is EventType; count(1) performs a simple count while icount(UserID) finds the number of unique UserIDs. Note that no matter how many operations are performed on a cursor, like multiple rounds of filtering, sorting or grouping, they will be executed together at one time – data is retrieved once through one traversal – to get the final result.

Execution result: