Code Examples of Handling Large Text Files in esProc

To deal with a large text file that can’t be wholly held in the memory, you need to import it in segments and process each segment separately. It’s knotty. Sometimes even multithreaded parallel processing is needed so as to increase performance. But since most of the programming languages don’t support basic class libraries, it’s extremely complicated to realize those implementations.

Yet being a professional structured data handling tool, esProc can serve as the class library with its support for cursor functions, as well as the ability of ensuring the integrity of each imported segment of file, simplifying the parallel algorithm and handling large files in an easy way. The Java main program can access the esProc result through JDBC. Find more details from How to Use esProc as the Class Library for Java.

Below are some typical examples of handling large text files in esProc.

Querying large files

The size of sales.txt exceeds the maximum memory capacity. The task is to query this file according to parameters. Below is the source file:

The code:

Open the file whose size is larger than the available memory size in the form of the cursor; then use the select function to query data and return result set as a cursor. The Java main program can access the result set in the JDBC stream style.

startDate and endDate are the input parameters, for example, a given time period from 2010-01-01 to 2010-12-31. Here’s the result:

To speed up the retrieval, you can use the multithreading technique by adding @m option, that is, =file(“D: \\sOrder.txt”).cursor@tm(). But the multithreaded parallel retrieval can’t ensure the original data order.

Parallel query processing

The sales.txt file stores a large number of ordering records. The requirement is to perform a high-performance conditional query. Condition: sellerid=1 and client=TAS and orderdate>=2013.

The code:

Explanation:

Set 4 parallel threads that each retrieve one block of the file, perform the query and return result to the main program that is responsible for concatenating the returned result sets. Here the select function performs the query on cursor and returns cursor too. Usually the query result set is small enough that can be retrieved into the memory with fetch function.

The cursor function divides the file by bytes into 4 blocks, which have approximately the same sizes, and retrieves only one block. By giving up the head line and complementing the tail line, esProc is capable of ensuring that each block is retrieved with complete rows.

According to test, it takes only 24 seconds to perform a query on a file of 3.4G size using the above program. For more details, see Performance Test of esProc File Traversal Algorithm.

Data sorting

Based on the sales.txt file, perform sorting by client code in descending order and by the year and the month in ascending order.
The code:

Use “-” to represent sorting in descending order. The year and the month need to be obtained through computation.
Result:

Grouping and aggregation

Calculate the annual sales amount and the annual number of orders for each salesperson.

The code:

The groups function can perform grouping and aggregation simultaneously. ~ stands for each group or the current group. count(~) is equal to count(OrderID).

Result:

This example assumes that the source file is large but the aggregate result set is relatively small. If the result set is too big to be held by the available memory, you can use groupx function to perform the group and aggregate operations and return result as a cursor, using the following code: A1.groupx(SellerId,year(OrderDate);sum(Amount),count(~))

Data filtering by group

Here’s a large file sales.txt that is ordered by SellerId. Suppose you want to find out the records of the salespeople whose sales amounts are greater than 10000.

The code:

Run a loop to retrieve a set of data with the same SellerId from A1’s cursor each time and perform data handling to calculate the sales amount for each salesperson (SellerId) and write the corresponding record into the file result.txt if the calculation result is greater than 10000.

The statement for cs;x means fetching a set of records with the same x field from cursor cs at a time. This requires that data be already sorted by x. In this example the file has been sorted. But if it hasn’t, it needs to be first sorted with sortx function.

The result.txt file stores the result. Here’s a selection of it:

Parallel grouping and aggregation

Based on the large file sales.txt, group records of the year 2013 by client and sellerid and calculate the number of orders and sales amount for each group.

The code:

Explanation:

Set the number of parallel threads as 4. Each thread retrieves one segment of the large file to filter out the records of the year 2013, perform grouping and aggregation and return result to the main program, which concatenates all the returned result sets and performs a second grouping and aggregation.

Parallel file comparison

The f1.txt file is large, but f2.txt can be all imported into the memory. You need to find out the common rows of the two files. Below are the source files:

f1.txt:

f2.txt:

The code:

Split the first file into 4 segments and handle the 4 generated cursors concurrently. The returned cursor results will be combined later. The conj function combines the returned result sets; @x option means concatenating the cursors and @m option means parallel processing.

According to tests performed on a large file of 2.77G size and a small file of 39.93M size under equal hardware environments, it takes an average of 85 seconds to complete the comparison task with serial computation but only an average of 47 seconds to get it done with parallel computation. The performance is nearly doubled.

Parallel data matching

The file1.txt file stores a great number of strings, from which you need to find lines of data ending with “.txt” and write them into result.txt. Below is a selection of the source file:

The code:

Issue 4 parallel threads that each access a part of the file with cursor and search each cursor in A1 for lines ending with “.txt”. The select function performs the query and the like function finds matching strings. @c option indicates that the matching is case-insensitive, and _1 stands for the first field.

Apart from the like function, you can also use a regular expression to perform the string matching. That is, A2.(~.regex@c(“^c:\\\\windows.*(?<!\\\\(.txt)$)”)).

Getting distinct values in parallel

Obtain a client list according to sales.txt.

The code:

Open the file in the form of parallel cursors and retrieve only the Client field which will be grouped in the next step. @m means parallel processing. When performing group operation on grouping fields, the groups function will return a set of values of the grouping field.

Result:

Extended explanation: This example assumes that the result set is relatively small. If the result set is sufficiently large, you can use groupx function to export the cursor data to another file, as shown below:

Order-related set operations

Two large files f1.txt and f2.txt are already sorted by Name and Dept. You need to find out the intersection of the two files. Below are the source files:

f1.txt:

f2.txt:

If files are already sorted, use merge algorithm to achieve the result that set operations can get and a better performance. Here’s the code:

The merge function combines data together, in which @x means cursors are being handled, and @i is used for getting intersection, @u for union and @d for difference.

Result:

Related computing

emp.txt is a tab-separated text file whose EId field corresponds to sales.txt’s SellerId field. You need to add and align emp.txt’s Name, Dept and Gender fields to sales.txt.

emp.txt:

The code:

The join function performs join operations and changes the table names to s and e. It performs inner join by default but uses @1 to do a left join and @f to get a full join. It can deal with cursors by adding @x option. Since joining cursors requires that data be sorted, here sortx function is used to perform a sort. After the join is done, get the desired fields from the joining result to form a new two-dimensional structured table. Here’s the result:

Multilevel association

There are 5 source files, in which Orders is a large fact table and Customer, Product, Region and Supplier are small dimension tables. You need to find out the orders whose clients and suppliers are in the same region, group them by city and calculate the number of orders and order amount in each city.

Their relationships are as follows:

The code:

Import every file and establish the relationships between the fact table and each of the dimension table; launch 4 parallel threads to filter out the desired orders according to the relationships and group and aggregate them; and finally perform a second grouping and aggregation on the concatenated result sets. The switch function establishes a foreign key relationship, and the conj function combines the result sets returned from the threads.

Comparison between a large file and a small file

Here are a large file f1.txt and another file f2.txt that can be entirely imported into the memory. You need to find out the common rows of both files.

The code:

You can import the small file into the memory, create a hash table and perform the set operation. This can increase the efficiency greatly. The primary function is used to set a primary key, with @i used to create the hash index. The find function queries the common data of cursor A1 and cursor B1, which is equal to getting the intersection. Here’s the result:

Extended explanation:

1.To get the difference, A3’s code can be =A1.select(!B1.find(~._1)) .

2.To get the union of file1 and file2, you can first find out the difference and then combine it with file 2.