A Code Example of External Memory Grouping in esProc

In data analysis, we often need to group data and then compute the aggregate value for each group, or perform other computations on each group. esProc allows using groups function to compute aggregate values for groups of data, as well as grouping records of a table with group function for use in subsequent computations. However, external memory grouping is required when data being grouped is big and cannot be loaded to the memory in its entirety and thus the above-mentioned methods for data summarizing and grouping become useless.

A table with big data containing1,000,000 records is used to simulate call duration records of mobile phone users. It is stored in the binary file PhoneBill:

Mobile phone numbers used for the simulation are 8-digit integers whose first four digits are the fixed 1234 and the rest is randomly generated. The records start on a randomly generated day of August in 2014. Values of call duration are integers randomly generated with a 90 percent chance of being one minute. The maximum limit is 20 minutes. After the data file is prepared, B11 fetches the first 1,000 rows:

Perform the following computations based on the data in PhoneBill:

①     Compute total call duration of all users and their average call duration in each day of August.

②     Compute every user’s total call duration in August.

③     Store each day’s call records in a file.

④     Find the numbers of five users who make the longest call in total for each day of August.

To solve the first problem, data needs to be grouped and summarized by DateTime. As there are only 31 days in the month of August, the result set won’t contain big data and the aggregate operation can be performed in the memory:

A2 generates a cursor for the binary text data in A1. A3 groups and summarizes data from the cursor. The result is as follows:

Since there is not so much data in the result, the cursor data only needs to be traversed once. Here groups function is adequate to perform the group and aggregate operation and external memory grouping is not necessary. In fact the result set can be directly acquired without the cursor, thanks to the reasonable amount of data. For more related information, please see esProc External Memory Computing: Principle of Grouping. To compute the average call duration in a day, we need to first get the total call duration and the total number of calls in this day. The result of A4 is as follows:

It is a different case for the second task. As the number of users is far more than the number of days in August, we should consider if the result of data grouping and summarizing can be returned to the memory all at once. There are 10,000 users at most in this example, which, actually, is not a large number. It is merely used to illustrate the computation of big result set. Here we suppose the memory can only hold 1,000 records:

groupn function is needed to handle the grouping and summarizing of big data. The function fetches data from cursor and performs group operation in batches according to the pre-specified number of buffer rows, using the external memory. By Phone and according to the specified 1,000 buffer rows, A3 groups and aggregates cursor data, computing the total call duration in the month. The result of grouping and summarizing big data is still a cursor. Fetching data from it is no different to fetching data from any other cursor. The result of A3 is as follows:

A4 fetches the records of the first 1,000 users. Their total call duration is as follows:

By the way, if data in the cursor hasn’t been entirely fetched, cs.close() function needs to be called to clean the temporary files from the external memory in time.

The third task requires grouping data by the DateTime first before storing data of each day in a file:

Different from other grouping operations on big data, groupn function requires specifying group numbers directly in the grouping expression. In A3, the dates in DateTime are used as the group numbers. Different from the previous result of data grouping and summarizing, A3’s function returns a sequence of cursors. Each cursor corresponds to a group:

The fourth and fifth lines of code store cursor data in each day in a file. A6 selects four days’ data, and A7 fetches the first 1,000 records from it:

For the fourth problem, group data by DateTime, and then group and summarize data of each group. Finally, select the desired mobile phone numbers from the aggregate results:

A3 directly specifies dates of DateTime as group numbers in the grouping expression, and returns a sequence of cursors:

The code from the fourth to sixth line loops through cursor data of each group to compute the total call duration per user per day, and according to the aggregate result, finds records of users whose call duration is in the top five. Please note that when aggregate function topx is used to sort data in descending order, just add a negative sign before the sorting expression, like –TotalDuration in B5. Select data of five numbers that have made the longest call and store it in the table sequence in B3. After loops are finished, the final result can be viewed in B3 as follows:

An alternative choice is to use the files generated from handling the third problem, instead of grouping all the original data. 

Grouping In-memory Data Using esProc: Code Examples

Using esProc, it is quite convenient to group data in memory. There are several main types of grouping based on how data is grouped. Here we’ll illustrate equal grouping, alignment grouping and enumeration grouping respectively with an example.

Equal grouping

That data is grouped by one or more certain fields (or one or more computed fields derived from fields) of the current data set is equal grouping. By this method each group is a subset of the original data set.

Case description: Group sales orders by the year.

Data description: The original data is as follow: 

The above data set (table sequence) can be imported from a database or a file. For example:

A1=file("E:/sales.txt").import@t()

esProc code:

A2=A1.group(year(OrderDate))

Computed result:

Code explanation:

1.In this example, the grouping criterion comes from the OrderDate field. The order dates can be converted to the years with year(OrderDate), then data of the same year will be grouped together.

2.There may be more than one field for grouping data. For example, the data could be grouped by both the year and the sellerID to put records of each seller in each year into a group. Below is the code for doing it: A1.group(year(OrderDate),SellerId)

3.Often, the grouped data are to be aggregated, like getting each year’s sales amount based on A2’s data. The code is:

A2.new(year(OrderDate):y,~.sum(Amount):a)

Computed result is as follows:

Or we can combine grouping and summarizing into one step:

A1.group(year(OrderDate):y,~.sum(Amount):a)

Alternatively, we can choose the groups function with better performance yet less flexibility:

A1.groups(year(OrderDate):y; sum(Amount):a)

Of course, sometimes we have to perform grouping and aggregate separately in order to reuse the code and improve computational efficiency, like the scenario in which one of A2’s group needs filtering and another one requires relational computing. In another scenario, the summarized data of a certain group is unusual and worth further study, then this group can be used directly in the subsequent computations without the need of filtering it again.  

4. By default, esProc’s group function will group data using hash algorithm. But for ordered data, the comparison of adjacent rows, which is equivalent to merge operation, may have higher performance. This approach can be implemented by using @o option with group function. For example:

A1.group@o(year(OrderDate),SellerId)

Alignment grouping

Equal grouping groups data by the field(s) coming from within the dataset. If the grouping criterion is one or more fields of another data set, a user-defined array, or a parameter list, etc., the grouping model will be referred as an alignment grouping.

Different from the equal grouping, alignment grouping may produce empty subsets, which means no members in the original data can satisfy a certain grouping condition. It may also lead to incomplete grouping, that is, there may be members that will not appear in any group. Neither would happen with equal grouping. 

Case description: Group the orders table according to the list of best 10 sellers selected by KPIs.

Ungrouped data set:

The orders table in the previous example will also be used here. Data is stored in A1.

The best 10 sellers list is stored in B1 as follows:

The list of sellers may come from an intermediate table, or be generated by a piece of code. It’s not important how it is produced in this example.

esProc code:

A1.align@a(B1:empID,SellerId)

Computed result:

Code explanation:

1. In this example, the grouping criterion (list of sellers) is outside of the data set being grouped. After data is grouped, each group contains only the data of one seller, and groups are arranged according to the order of members in the sellers list. 

2. Because sellers in the orders table outnumber the best sellers, some of the orders will not appear in any group. We can use function option @n to store those orders in one additional group, as shown below:

A1.align@a@n(B1:empID,SellerId)

This group will be put in the end:

3. Sometimes not all members of the grouping criterion will fall in the data set to be grouped. For instance, the grouping criterion is “a list of newly-employed sellers”. In this case, it’s normal to produce empty groups. If we modify the first record of the list into empID=100, the result will be:

Enumeration grouping 

The grouping criterion for enumeration grouping is even more flexible. It could be any boolean expression. The records satisfying the value of the expression will be put into the same group.

Similar to alignment grouping, this is also the incomplete grouping as it probably produces empty subsets or a result in which some members are not included in any group. Moreover, this type of grouping may have the result that certain members appear in more than one group. 

Case description: Dividing orders into four groups, they are: A. order amount is less than 1,000; B. order amount is less than 2,000; C. order amount is less than 3,000; D. order amount is less than 10,000. Special requirement: data cannot be grouped repeatedly, that is, if an order has been in group A, it must not be put into group B, C, or D.

Ungrouped data set:

The orders table in previous examples will still be used. Data is stored in A1.

esProc code:

A2=["?<=1000","?<=2000","?<=3000","?<=10000"]

A3=A1.enum(A2,Amount)

Computed result:

Case explanation:

1. In this example, grouping criteria are multiple flexible expressions. Each record will be compared with each of the expressions. Those records that can match the same expression will be put into the same group. Groups are arranged according to the order of the grouping criteria as well.

2. By default, enumeration grouping will not produce duplicate members in the result. Because after group A’s data is selected out, expression B will be matched with the rest of the records, as this example has shown so far. But the use of function option @r allows us to match expression B with all records, which will produce duplicate members. For example the result of A3=A1.enum@r(A2,Amount) is as follows: 

Likewise, if values of an enumeration expression fall outside of the data to be grouped, it will correspond to an empty group. Besides, if certain records cannot match any expression, function option @n can be used to group these surplus records together. 

esProc External Memory Computing : Principle of Grouping

After data are imported from a data table, we often need to group them as required, or work out grouping and summarizing result. In esProc, groups function is used to compute the result of data grouping and summarizing; or group function can be used to first group the data, then perform further analysis and computation.

But the case will be different in processing huge data, for the records cannot be loaded to the memory all together and distributed into each group. Other times the number of groups is huge and the grouping and summarizing result cannot even be returned all at once. In these two occasions, the external memory grouping is required.

1. Grouping with cursor by directly specifying group numbers

Let’s create a big, simple data table containing employee information, which includes three fields: employee ID, state and birthday. The employee IDs are generated in order and the states are written in abbreviations obtained arbitrarily from the STATES table of demo database; birthdays are the dates selected arbitrarily within 10,000 days before 1994-1-1.The data table will be stored as a binary file for convenience. 

Altogether 1,000,000 rows of data are generated. The result of reading the 50,001^th ~51,000^th rows of data with cursor can be seen in C10 as follows:

In the following, we’ll take the generated data file, BirthStateRecord, as an example to explore how to group data in cursor computing by directly specifying group numbers. Because the data of the big data table cannot be loaded all together into the memory, we cannot group them as we do with the data from an ordinary table sequence. To solve this problem, esProc offers cs.groupn(x) function which can distribute the records in cursor cs according to the computed results of expression x into groups with specified serial numbers and return a sequence of cursors. For example:

To explain the way in which cs.groupn(x) function performs grouping by using external memory to specify group numbers, Click in the debug area of the toolbar to execute the code step by step until A6. A2 creates a cursor with the binary data file BirthStateRecord. A4 creates a sequence using the states’ abbreviations in STATES table.A5 uses groupn function to group data of the cursor; in this process, we need to find the corresponding serial numbers of the states in A4 and make them the group numbers. During the execution of groupn function in A5, a temporary file will be generated for each group to store the grouping result and a sequence of temporary file cursors will be returned as follows:

While the code in A5 is executed, external files are generated in the directory of temporary files:

In the execution of groupn function, the number of temporary files equals that of the groups of records. We can import data from one of the temporary files in another cellset:

The data A2 imports are as follows:

It can be seen that the data of a temporary file is, in fact, the employee information of a state. Here it is the employee information of the state of Missouri. The name of a temporary file is generated arbitrarily by the esProc program and managed by it.

Click  on the toolbar in the previous cellset file and go on with the execution of this cellset. In A8, when all file cursors are closed, the temporary files will be deleted automatically. A6 fetches from the first file cursor the employee information of the state of Alabama as follows:

A7 works out the grouping and summarizing result of the 22^nd group using groups function, that is, the number of employees from the state of Michigan:

Actually A6 and A7 fetch data from two groups respectively. When the data of a file cursor have been fetched, the corresponding temporary file will be deleted automatically.

It is thus clear that a sequence consisting of temporary file cursors will be returned in grouping records of cursors using directly specified group numbers. Each file cursor contains the records of a group which can be further processed.

2. Grouping and summarizing result sets of huge data

When grouping data of cursors, most of the time we needn’t to get the detailed data of each group. What we only need is the grouping and summarizing result. To get the number of employees of each state from BirthStateRecord, for example, we use groups function to compute the grouping and summarizing result: 

Thus we can get the result in A3:

Here we notice that the groups function for grouping and summarizing will return a table sequence as the result after the computation is completed. In processing massive data, sometimes they need to be divided into a lot of groups and the result set of grouping and summarizing is too big to be returned all at once. This includes the following cases: the telecom company makes statistics of each customer’s bill; online shopping malls make statistics by categories about the sales of each kind of commodity, and the like. In these cases, the use of groups function may result in a memory overflow. We can use groupx(x:F,…;y:F,…;n) function instead to perform grouping and summarizing by making use of external memory. In the function, n represents the number of rows in buffer area. For example:

Still, the code is executed step by step until A4. In A3, groupx function uses external memory to perform grouping and summarizing. In cursor computing, groupx function is used in the operations of both grouping and summarizing with external memory and grouping by directly specified group numbers. The difference of the two operations lies in the parameters. A3 performs grouping by employees’ birthdays, then summate the number of employees born on each of the dates. During the computation, the number of rows in buffer area is set as 1,000. The result returned by A3 is a cursor as follows:

After the code in A3 is executed, external files will be generated in the directory of temporary files:

The data of one of the temporary files can be imported:

The data A2 imports are as follows:

The data of A3 is as follows:

It can be seen that each temporary file is the result of grouping and summarizing a part of the data according to employees’ birthdays. A larger cursor composed of all temporary files will be merged and returned by esProc. When the temporary files are generated, esProc will select a group number suitable for computing, so the rows of data in the temporary files will be a little more than the number of rows we set in buffer area. Special attention is needed in this point.

Go on with the execution of the cellset in the previous cellset file. When cursors are closed in A5, the temporary files will be automatically deleted. A4 fetches the first 1,000 birthdays from the cursor generated in A3 and counts the number of employees born on each birth date, as shown below:

esProc Improves Text Processing – Insert Summary values into Grouped Data

The usual way to insert summary values into the grouped data is to process data group by group. Import a group of data, append them and their summary value to a new file and then do the same with the next group, and so on. But it is not easy to realize this in hard coding. esProc, however, supports group cursor with which a whole group of data can be imported automatically. The following example will show how esProc deals with this kind of computation.

The log webdata.log has three columns separated by commas. The first column is the identifier for grouping data. The other two columns hold numerical values. Some of the data are as follows:

Notice that the first and fourth group has the same identifier for grouping data.

Now we are asked to insert the average value of the second column and an empty row between each group, as shown below:

esProc code for doing this task:

A1=file("E: \\webdata.log").cursor(;",")

This line of code opens the log file in the form of a cursor. cursor function returns a cursor object according to the corresponding file object. In the function, comma is used as the column separator (default separator is tab) and default column names are _1,_2…_n, in which _1 is the column to mark data grouping. We can also specify the column names like cursor(groupName,data1,data2;",").

The code only creates cursor objects but does not import the data. The data importing will be started by for statement or fetch function.

B1=file("e:\\result.txt"). This line of code creates a file object for storing the computed results.

A2:for A1;_1

This line of code fetches data from the cursor in A1 by loop, importing a group of data with the same first column (the name is _1) each time. It is in this step that data are really imported into the memory. 

The for statement here is worth special attention. for cs,n means fetching n rows from cursor cs at a time. While for cs;x means fetching a group of records with the same x field from cursor cs in which data need to be grouped beforehand by x. In this example, the data are already grouped. But if the data are ungrouped, they can be prepared them by using other esProc functions (like sortx, a function for sorting cursors).

The x in the statement for cs;x can be an expression, according to which multiple rows will be imported each time uninterruptedly until the expression changes. For example, for A14 ;left(_1,4) will judge the first four characters of the first column according to the expression and corresponding records will be classified into the same group until the characters change .

B2-B4 is the loop body of for statement in A2. The loop body processes every group of data in the same way. Its working scope, as can be seen from the cellset, is represented by indentation rather than by parentheses or other identifiers like begin/end. What's more, the loop variable can be represented by the name of the cell where for statement resides, which, in this example, means A2 represents the records of the current group. Seen in debug mode, the value of A2 in the first-run loop is as follows:

B2=B1.export@a(A2;",")

This line of code appends A2 to the defined file object. export function exports a group of records to the file, in which @a option means appending. In order to keep consistent with the source data, comma is used here as the separator (though the default separator is tab). Open result.txt after the first loop and we can see the following data:

B3=A2._1+"_avg,"+string(A2.avg(_2))+"\r\n"

This line of code is used to piece together the summarizing string. A2._1 represents the first column of the current group. Its value is "webcat_service" as with the first group. The expression A2.avg(_2) means getting the average value of the second column of the current group. The value is 2.25 as with the first group. string function will formatting the variable of floating point type into the string.

For the first group of data, B3’s value is this:

B4=B1.write@a(B3)

This line of code appends B3 to the result file. Both export function and write function can write data into a new file. The former writes structured data into the file, whereas the latter writes strings or an array of strings into the file. @a option appends data, which is preceded by writing the carriage return into the file.

At this point, the above script has finished processing all data. The final result can be viewed in result.txt as follows:

esProc Simplifies SQL-style Computations – Multi-layered Data Grouping with Specified Criteria

During database application development, we are often faced with complicated SQL-style computations, to which the multi-layered data grouping with specified criteria belong. In SQL, the key method for realizing the operation is to group the source data according to specified criteria using left join statement. The problem is that this method usually involves handling data grouping and summarizing, inter-row computations, completing data, and, moreover, multi-layered data. So we need to write rather complicated SQL statements to express it.

In esProc, the operation can be realized with simple and easy code. Its ability will be shown through the following example.

Here is a table – stocklog – in which all the warehouse-in and -out records of various products every day are stored. Now we are asked to produce a stock report of all the products for every day of a specified time period. Some of the records in stocklog are as follows:

In the table, if the INDICATOR value of a record is null, it is a warehouse-in record; if the INDICATOR value is ISSUE, it is a warehouse-out record. Note that though some dates are missing, which means there are no corresponding records in these days, the stock report must include all the dates continuously.

The stock report includes the following categories for each product each day: the opening stock (Open), warehouse-in quantity (Enter), stock in its highest level (Total), warehouse-out quantity (Issued) and the closing stock (Close). The "Open" of the current day is the "Close" of the day before; "Enter" and "Issued" come from stocklog; "Total" is equal to "Open+Enter"; "Close" is equal to "Open+Enter-Issued" or "Total-Issued".

esProc script is shown below:

A1：Query the database and compute the total Enter and toal Issued of each product each day based on stocklog. As only data grouping and summarizing is needed in this step and the computation is simple, a SQL statement can be used to perform it. Notice that the two parameters – start and end – correspond respectively to the two quotation marks in the SQL statement and represent the time periods passed from the external, which may be a Java program or a reporting tool. Suppose values of start and end are 2014-04-01 and 2014-04-10 respectively, result of A1 will be as follows:

A2=A1.group(Lname)

This line of code groups the result of A1 by Lname, with each group being all the records of the Enter and Issued of each product each day of the specified time period. Please note it is not necessary to summarize each group of data. Result of A2 is shown in the left part of the following figure and detail data of each group are listed to the right.

esProc provides two functions for grouping data – groups and group. Similar to SQL's group by statement, groups groups and summarizes data. While group only groups data without summarizing them, which is a function SQL hasn't.

The final result should include the stock statistics of all days during the time period specified by start and end. But, in the source data, not all days have the warehouse-in and -out records, thus the result of A2 should be aligned with the continuous dates. The following code is to generate the time sequence first.

B2=periods(start,end,1)

periods function can be used to create a time sequence, which requires three parameters: start, end and interval. By default, a sequence of dates will be generated. By using other options, a time sequence of years, seasons, months and ten-day periods can also be created. Result of A3 is as follows:

A3=for A2. This is a loop statement, which performs loop on the result of A2, with each loop aiming at a product.

B3-B6 is a loop body that aligns each product's warehouse-in and -out records with the time sequence in B2 and then computes each product's stock statistics each day and finally append the result to B6. Note that a loop body in esProc is represented visually by an indentation instead of the braces or identifiers like begin/end.

B3=A3.align(A3,Date)

This line of code aligns the current product's warehouse-in and -out records with the time sequence in B2. Note that A3 wears two hats; it is both a loop statement and a loop variable, that is, the current product'’s warehouse-in and -out records. Take item3 as an example, the left part of the following figure shows the records before alignment and the right part shows the records after it: 

B4>c=0

It assigns an initial value – zero – to the variable c, which represents the Open field in each record of the current product. The Open field value of the initial date is zero and will be modified continuously in B5.

B5=B3.new(A3.Lname:Lname,B2(#):LDate, c:Opening, Enter,(b=c+Enter):Total,Issue,(c=b-Issue):Close)

This line of code computes the stock statistics. B3.new(…) means creating a new table sequence, that is, the stock statistics of the current product, based on the result of B3. The new table sequence has 7 fields:

A3.Lname:Lname ---- Fetch Lname field from A3 – the warehouse-in and -out records of the current product. The new field is named Lname.

B2 (#):LDate ---- Insert the time sequence in B2 into the new table sequence in order and make it a new field with the name LDate. Note that # represents the record numbers in A3 and B2(N) represents the Nth record in B2. So B2(#) means inserting B2 into the new table sequence according to the record numbers in A3.

c:Open ---- Make variable c the value of Open field. In the first record, c is zero.

Enter ---- Take the Enter field in B3 directly as a new field. Because the new table sequence is created based on the result of B3, it is unnecessary to rename the new field as Lname field was named.

(b=c+Enter):Total ---- Compute Total field according to the formula Open+Enter. The expression here is surrounded by parentheses to make it clearer.

Issue --- Take Issue field in B3 directly as a new field

(c=b-Issue):Close --- Compute Close field according to the formula Total-Issued. Note that variable c has been modified so that it will be qualified for computing the next record as the value of Open field, which is got according to the business rule that “Open” of the current day is equal to “Close” of the day before.

Take item 3 as example, result of B5 is as follows:

B6=@|B5

Continuously, this line of code appends the result of B5 to the current cell B6, which is represented by @. The final result is as follows:

B6 is the final result of this example.

In addition, the esProc script can be called by the reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result in the form of ResultSet to the Java main program. Please refer to related documents for details.