Structured Data Computing: the Focus of Routine Data Analysis

Essentially, the main content of various data analyses in our routine business is the structured data computing. For example:

l  Compute the link relative ratio and year-on-year comparison of each business branch’s monthly sales during a specified period of time.

Implementation approach: filter the sales data by time range, then group and summarize data by business branch, year and month, and at last, perform cross-row and –group ordered data computing.

l  Select stocks whose closing price has been increasing uninterruptedly for over 10 days. 

Implementation approach: Group daily transaction data by stocks and sort the data of each group by dates, compute the increasing amount of the share price and the number of days during which the share price increases uninterruptedly, and filter away the stocks that have been rising uninterruptedly for over 10 days.

l  Relate the data of different sources, like contract and payment information, to project payments schedule and find out the overdue projects. 

Implementation approach: Perform relational computing between heterogeneous data sources, then group, summarize and filter the data.

It can be seen that these routine problems of data analysis can be split into structured data operations including filtering, grouping, summarizing, sorting, ranking and relational computing.

Of course, we may need to solve data analysis problems of modeling or prediction occasionally. For example, find out goods that are closely related between each other, or predict which stock is supposed to rise, and the like. These operations require quite a lot of mathematical knowledge which ordinary staff is generally not likely to have. They are really important data analysis transaction, but they occupy only a very small part of routine data analysis.

Structured data computing is the focus. There are many tools that can perform it, like R language, Python, SQL and esProc.

R language provides dataframe data type for structured data computing. However, it was originally designed for collecting and analyzing scientific data, especially for performing matrix and vector computations. It is not professional for structured data computing.

In fact, dataframe is a newly-developed function of R language; its strong point is algorithms of modeling and prediction, such as regression analysis, ANOVA analysis, Agreement evaluation, and Bernoulli distribution, etc, which are seldom used in routine data analysis.

Pandas, Python's third party function library, can perform structured data computing. But it was also designed for collecting and analyzing scientific data instead of structured data computing, so it is not professional too. And similar to R language, the functions of Pandas center on modeling and prediction and are seldom used in routine data analysis.

We can see that, despite lots of tools for performing structured data computing, few can be regarded as truly professional. There are only one professional, SQL, the old brand computer language.

SQL was designed purely for structured data computing. It is professional and widely used.

Yet it also has drawbacks for routine data analysis. The most obvious ones are complicated application environment and being bad at ordered data computing. The installation, configuration, maintenance and management of SQL are very complicated. SQL data set hasn't inherent serial numbers and gets disadvantaged in ordered data computing, for example, the common problems in routine data analysis like link relative ratio, year-on-year comparison, fetching data in a relative interval, performing ranking during data grouping and getting records in the top and bottom, etc. Most of the examples we mentioned at the beginning involve ordered data computing. And though we can solve them with SQL, the operation will be quite difficult.

Similar to SQL, esProc is specially designed for structured data computing.

By comparison, esProc's application environment, installation and configuration are simple. esProc can fetch data from databases, and import structured data directly from Txt, logs and Excel. Moreover, esProc table sequence has inherent serial numbers, enabling it to perform ordered data computing easily. Unfortunately, in esProc, the syntax for external memory computing is different from that for in-memory computing, which requires different code. In this respect, SQL has better consistency in its syntax. 

Method of Computing Link Relative Ratio and Year-on-year Comparison in R Language

Cross-row and –group computation often involves computing link relative ratio and year-on-year comparison. Link relative ratio refers to comparison between the current data and data of the previous period. Generally, it takes month as the time interval. For example, compare the sales amount of April with that of March, and the growth rate we get is the link relative ratio of April. Hour, day, week and quarter can also be used as the time interval. Year-on-year comparison is the comparison between the current data and data of the corresponding period of the previous year. For example, compare the sales amount of April 2014 with that of April 2013 and compute the growth rate which is April's year-on-year comparison. Data of multiple periods are usually compared to find the variation trend in practical business.

Now let's look at the method of computing link relative ratio and year-on-year comparison in R language through an example.

Case description:

Compute the link relative ratio and year-on-year comparison of each month's sales amount during a specified period of time. The data come from orders table sales, in which column Amount contains order amount and column OrderDate contains order dates. Some of the data are as follows:

Code:
sales<-read.table("E:\\ salesGroup.txt",sep="\t", header=TRUE)
filtered<-subset(sales,as.POSIXlt(OrderDate)>=as.POSIXlt('2011-01-01 00:00:00') &as.POSIXlt(OrderDate)<=as.POSIXlt('2014-08-29 00:00:00'))
filtered$y<-format(as.POSIXlt(filtered$OrderDate),'%Y')
filtered$m<-format(as.POSIXlt(filtered$OrderDate),'%m')
agged<-aggregate(filtered$Amount, filtered[,c("m","y")],sum)
agged$lrr<- c(0, (agged$x[-1]-agged$x[-length(agged$x)])/agged$x[-length(agged$x)])
result<-agged[order(agged$m),]
result$yoy<-NA
for(i in 1:nrow(result)){
if(i>1 && result[i,]$m==result[i-1,]$m){
result[i,]$yoy<-(result[i,]$x-result[i-1,]$x)/result[i-1,]$x
}
}
Code interpretation:
1. The first four lines of code are easy to understand. read.table is used to read data from the table and subset to filter data, and two format functions are used to generate year and month respectively. Note that the beginning and ending time should be output dynamically from the console using scan function; here they are simplified as fixed constants.

After computing, some of the values of database frame filtered are:

2. agged<-aggregate(filtered$Amount, filtered[,c("m","y")],sum), this line of code summates the order amount of each month of each year. Note that in the code, the month must be written before the year though data are grouped by the year and the month according to business logic. Otherwise R language will perform grouping first by the month, then by the year, which will get result inconsistent with business logic and make data viewing inconvenient.

After computing, some of the values of data frame agged are:

3. agged$lrr<- c(0, (agged$x[-1]-agged$x[-length(agged$x)])/agged$x[-length(agged$x)])，this line of code computes link relative ratio. The result will be stored in the new column Irr. Business logic is (order amount of the current month – order amount of the previous month)\order amount of the previous month.

Note: [-N] in the code represents that the Nth row of data is removed. So agged$x[-1]means the first row of data is removed and agged$x[-length(agged$x)]means the last row of data is removed. By performing certain operation between the two, link relative ratio can be obtained indirectly. But the result won’t include the link relative ratio of the first month (i.e. January 2011), so a zero should be added to the code. We can see that the code logic and the business logic share some similarities but are quite different. The code is difficult to understand.

At this point, some of the values of data frame aggedare:

4. result<-agged[order(agged$m),], this line of code sorts data by the month and the year. Since the data of the year are ordered, we just need to perform sorting by the month. result$yoy<-NA initializes a new column which will be used to store the year-on-year comparison of sales amount.

Now the value of result is:

5. The loop judgment in the last four lines of code is to compute the year-on-year comparison. Business logic: (order amount of the current month – order amount of the previous month)\order amount of the previous month. Code logic: from the second line, if the month in the current line is the same as that in the previous line, the code will compute year-on-year comparison. Detailed code is result[i,]$yoy<-(result[i,]$x-result[i-1,]$x)/result[i-1,]$x. We can see that the code written in this way is easy to understand and its logic is quite similar to the business logic.

The only weakness of this piece of code is that it cannot use the loop function of R language, which makes it a little lengthy. But compared with the difficult operation of link relative ratio, maybe a longer but simple code is better.

The final results are as follows:

Summary:R language can compute link relative ratio and year-on-year comparison, but the operation of link relative ratio is difficult to understand and the code of year-on-year comparison is a little lengthy. The codes of both operations are not easy to learn.

The third-party solution

Python, esProc and Perl, all of which can perform structured data computation, can be used to handle this case. In the following, we'll briefly introduce esProc and Python's solutions.

esProc

esProc is good at expressing business logic freely with agile syntax. Its code is concise and easy, as shown below:

In the above code, groups function is used to group and summarize data by the year and the month. The derive functions in A4 and A6 generate link relative ratio and year-on-year comparison respectively.

As can be seen from the code, esProc also uses[-N]. Different from [-N] in R language, it doesn't represent removing the Nth row; it represents the Nth row counted from the current line. For example, [-1] is the previous line. In this way, the operation of link relative ratio can be simply expressed as (x-x[-1])/x[-1].But R language hasn't expressions for relative positions, which makes its code difficult to understand.

In the year-on-year comparison operation, esProc uses judgment function if in loop function, making it avoid the lengthy loop statement and its code simpler. While R language only has the judgment statement but hasn't the judgment function. This is the reason why its code is lengthy.

Finally, these are the computed results:

Python（Pandas）
Pandas is Python's third-party package. Its basic data type is created by imitating R’s dataframe but gets improved greatly. At present, its latest version is 0.14. Its code for handling this case is as follows:

sales = pandas.read_csv('E:\\salesGroup.txt',sep='\t')
sales['OrderDate']=pandas.to_datetime(sales.OrderDate,format='%Y-%m-%d %H:%M:%S')
filtered=sales[(sales.OrderDate>='2011-01-01 00:00:00') & (sales.OrderDate<='2014-08-29 00:00:00')]
filtered['y']=filtered.OrderDate.apply(lambda x: x.year)
filtered['m']=filtered.OrderDate.apply(lambda x: x.month)
grouped=filtered.groupby(['y','m'],as_index=False)
agged=grouped.agg({'Amount':[sum]})
agged['lrr']=agged['Amount'].pct_change()
result=agged.sort_index(by=['m','y'])
result.reset_index(drop=True,inplace=True)
result['yoy']=result.apply(lambda _:numpy.nan, axis=1)
for row_index, row in result.iterrows():
if(row_index>0 and result.ix[row_index,'m']==result.ix[row_index-1,'m']):
result.ix[row_index,'yoy']=(result.ix[row_index,'Amount']-result.ix[row_index-1,'Amount'])/result.ix[row_index-1,'Amount']

In the code, pct_change() function is used to directly compute the link relative ratio, which is more convenient than the method used by R language and esProc. But this kind of function is not universal and can only deal with isolated cases. When it is required to compute link relative ratio or year-on-year comparison, Pandas can only complete the task by combining div function and shift function, which makes its code more difficult to understand than R’s.

In computing year-on-year comparison, Pandas' code is as lengthy as R’s. This is because Pandas also cannot use if function in loop function. I’m afraid cooperation of apply function and lambda syntax is needed if we want to write simpler code.

Finally, let's look at the computed results:

Please pay attention to the following easy-to-get-wrong details:

1. The code must be sort_index(by=['m','y'])when we perform sorting by the month and the year. The simple form sort(m), which used in R language and esProc, is not allowed.

2. Pandas has the assignment syntax as result.loc[row_index,‘yoy’]=value. But when assigning value to a certain element in data frame, we should write the code as result.ix[row_index,'yoy']=value.

3.  When iterrows() is used to perform loop, its loop number row_index is index instead of row number. To make the row number conform to the index, reset_index() should be used to reset the indexes.

Several Methods to Compute Fibonacci Sequence with esProc

Fibonacci Sequence is also called “Rabbit Sequence”, because it can be described by a problem related to rabbits. After the second month of their life, a pair of rabbits can give birth to two little ones every month. If all the rabbits could live forever, then how many pair of rabbits are there by the nth month?

During the first and the second month, there is only one pair of rabbits. Then in the third month, the first pair of little ones will be born. Now there are two pairs of rabbits... By the nth month, the increased number of rabbits, compared with the previous month, equals to the total number of rabbits two months ago ( i.e. in the n-2th month). In this way, the total number of rabbits can be expressed by a series: 1, 1, 2, 3, 5, 8, 13... From the third number, each number is the sum of previous two ones.

The principle of this loop computation is that, from the first two terms 1,1, the value of a new term is created by cyclically calculating the sum of the last two terms.

With esProc, the loop computation will be operated in the following manner:

This method uses for n loop to complete looping execution of designated times. Since the values of the first and second term have been decided during the initial setting, the loop computation should be reduced by two times. A3 stores Fibonacci Sequence, B4 computes the sum of last two numbers, statement in C4 is used to add results from B4 into the series.

Computed results in A3 will be:

Or, for x is used in esProc to execute loop computation. If the value of x is true,looping execution of statement block will continue all through, as shown in this figure:

A6 stores Fibonacci Sequence. According to the statement in A7, looping execution begins when the number of terms is less than designated total numbers. Statement in B7, which directly works out new term  to add to the series, is the combination of that in B4 and C4. When computations are finished, results in A6 and A3 are the same.

Or, for looping statement can be replaced in esProc by loop function to realize loop computation:

During initial setting in A9, values of both the zero and first term are determined. With loop function in A10, each time when the value of next term is computed, it will be assigned to b;and the original value of b, i.e., the value of current term, will be assigned to a; at the same time, the current term of the series is reset to a. With loop parameter, the amount of code can be effectively reduced. Result in A10 is the same as that in A3.

Also, subprogram of esProc is employed to make computations:

The subprogram in the latter part of A13 is responsible for computing Fibonacci Sequence of designated number of terms. if statement is used in the subprogram to make judgment, classifying two cases in producing Fibonacci Sequence: if the number of terms is less than or equal to 2, 1 is filled in each term; if it is more than 2, call function with recursion in B14 to get the first n-1 terms, compute the sum of the last two terms and add result to the series. Results of A12 and A3 are the same. 

By comparing the above several computing methods, we find that for loop is simple and easy to understand; loop function boasts the least amount of code; and for subprogram, calling is convenient and reusability of the code is high, so it’s suitable for cases required to make the same repeated computations. In real-world applications, we can make different choices as needed. 

Computing the Online Time for Users with esProc (IV)

In last article we mentioned that IT engineers from the Web Company used esProc to code single-machine multi-threaded program which could handle large data volume and complex requirements. This leverages the full power of one multi-core multi-CPU machine. Now once again these engineers found a new issue: with the user numbers for the online application growing explosively, colleagues from the Operation Department complained that the online time computation program is still running too slow.

IT Engineers leverage esProc's multi-machine parallel computing capability, to split the task for multiple machines to complete. The performance problem is resolved successfully. The single machine parallel processing is shifted to multi-machine parallel processing, with relatively low cost for hardware and software upgrade.

First, let's review the way the user behavior information is recorded in the Web Company. Data was recorded in the log file. Everyday a separate log file is generated. For example, the following log file, “2014-01-07.log”, contains the users online actions on January 7, 2014. To compute the online time for user in the week of 2014-01-05 to 2014-01-11, we need to retrieve data from 7 log files:

logtime userid action
2014-01-07 09:27:56 258872799 login
2014-01-07 09:27:57 264484116 login
2014-01-07 09:27:58 264484279 login
2014-01-07 09:27:58 264548231 login
2014-01-07 09:27:58 248900695 login
2014-01-07 09:28:00 263867071 login
2014-01-07 09:28:01 264548400 login
2014-01-07 09:28:02 264549535 login
2014-01-07 09:28:02 264483234 login
2014-01-07 09:28:03 264484643 login
2014-01-07 09:28:05 308343890 login
2014-01-07 09:28:08 1210636885 post
2014-01-07 09:28:09 263786154 login
2014-01-07 09:28:12 263340514 get
2014-01-07 09:28:13 312717032 login
2014-01-07 09:28:16 263210957 login
2014-01-07 09:28:19 116285288 login
2014-01-07 09:28:22 311560888 login
2014-01-07 09:28:25 652277973 login
2014-01-07 09:28:34 310100518 login
2014-01-07 09:28:38 1513040773 login
2014-01-07 09:28:41 1326724709 logout
2014-01-07 09:28:45 191382377 login
2014-01-07 09:28:46 241719423 login
2014-01-07 09:28:46 245054760 login
2014-01-07 09:28:46 1231483493 get
2014-01-07 09:28:48 266079580 get
2014-01-07 09:28:51 1081189909 post
2014-01-07 09:28:51 312718109 login
2014-01-07 09:29:00 1060091317 login
2014-01-07 09:29:02 1917203557 login
2014-01-07 09:29:16 271415361 login
2014-01-07 09:29:18 277849970 login

Log files record, in chronological order, users’operation (action), user ID (userid) and the time when the actions took place (logtime) in the application. Users operations include three different types, which are login, logout and get/post actions.
The Operation Department provided the following requirements for computation of users online time:
1. Login should be considered as the starting point of online time, and overnight should be take into consideration.

2. If the time interval between any two operations is less than 3 seconds, then this interval should not be added to online time.

3. If after login, the time interval between any two operations is longer than 600 seconds, then the user should be considered as logged out.

4. If there is only login, without logout, then the last operation time should be treated as time for logout.

5. For users who completed a post operation, his/her current time online time will be tripled in computation.

To improve performance, the Web Company increased the number of server from the original number of 1 to 3. Accordingly, the following steps are needed to shift from single-machine parallel to multi-machine parallel:

The first step:Modify the esProc program for weekly log files processing. Divide user ID by3 and separate the weekly log file into 3 files according to the remainder. Every server would be processing one of these. This way the file size were reduced and file transfer time could be shortened.Later the three files were uploaded to three servers, using multiple parallel programs to do the computation. The actual program is as following:

Note in the last screenshot that, A6 used the @g option of export function to retrieve"log files for one week" into three binary files. During subsequent use of parallel processing time, the content of log files can be retrieved by blocks for different user. The use of @g option is to ensure the segmented data retrieval is aligned to group borders, removing the possibility for assigning data of the same user to two blocks. 

The second step:the single-machine multi-threaded program is unchanged. Let’s go back. 

Subroutine parameters are shown below. They are used to pass the log file name, block number and total number of blocks for the week when called by the main program.Here the log file name for the week, week file, was already one of the three segmented files corresponding to this machine.

The subroutine is as following:

The above screenshot illustrates that: 

1. As we previously used export@g to output the file in group according to different user ID, the use of @z option by cursor in A2 to handle specific block (value is block number) among total (value is total blocks) from file will retrieve the complete group for the same userID. Data for one user will not be split into two blocks. 

2. The code line in red box returns the resulting file as cursor to the main program.Since multi-machine parallel processing were used here, this cursor is remote cursor ( Read esProc's Documents for detailed introduction on remote cursor). 

The third step:writing main program for parallel computing, to call the parallel computing subroutine. As illustrated below, the main program called parallel tasks on tree machines, which effectively improved the performance for computation. 

The server list in the program could also be written into the configuration file, this way any subsequent increase or decrease of the server would be easy. 

Note: for specific measurements regarding esProc's performance gain with parallel computing, please refer to related test reports for esProc.

Notes on the above screen capture:

1. callx@ parameter specifies 3 servers from A1 to A3, to handle three log files B1 to B3.

2. The syntax of callx's input parameter, is to specify three servers through A5, and specify 6 parallel computing tasks for each server in A6.

3. Server list, server number, and the number of tasks for each server can be adjusted according to actual situation, to leverage full performance potential of the server.

The fourth step: implement the esProc server, and upload related program & data files. Refer to instructions on esProc for specific steps and methods. 

After the transformation to multi-machine parallel computing, the Operations Department found significant improvement in the computation speed of users online time. The cost of this transformation is much lower than that for application databases upgrade, especially, in the hardware part, only 2 additional PC Servers were needed.

So far, The Web Company finished implementation of esProc based user behavior analysis and computation platform. Its main advantages are:

1. The platform is easy to be adjusted with more complex algorithm for future, shortened the response time and saved labor costs from engineers.

2. It’s easy to scale out for even larger data amount in the future, with shortened project time and reduced cost of upgrade.

Using esProc to Compute the Online Time of Users (I)

Using esProc to Compute the Online Time of Users (II)

Computing the Online Time for Users with esProc(III)

Computing the Online Time for Users with esProc (III)

In last article we mentioned that IT engineers from the Web Company used esProc to code program which could handle large data volume and complex requirements. Not only could it meet the demands for online time computation, but also is relatively easy to be extended to with new conditions.

However, these engineers found that the single-threaded program does not take full advantage of the of the server’s computing power. Practice has proved that the use of esProc's multi-threading capability can take advantage of the server's quad dual core,or even more CPUs. The change from single-threaded to multi-threaded requires very little workload.

The Operation Department provided the following requirements for computation of users online time:
1. Login should be considered as the starting point of online time, and overnight should be take into consideration.
2. If the time interval between any two operations is less than 3 seconds, then this interval should not be added to online time.
3. If after login, the time interval between any two operations is longer than 600 seconds, then the user should be considered as logged out.
4. If there is only login, without logout, then the last operation time should be treated as time for logout.
5. For users who completed a post operation, his/her current time online time will be tripled in computation.
To shift from single-threaded computing to parallel computing,following steps needs to be done:
The first step: Adjust the log file preprocessor with the @g option of export function, to retrieve the log file for one week into asegmented binary file. In subsequent parallel processing, log file could be retrieved by block for different users. The use of @g option is to ensure the segmented data retrieval is aligned to group borders, removing the possibility for assigning data of the same user to two blocks. The actual procedures are as following:

The second step: Rewrite the online time computing program into a parallel subroutine.The part in the following red box is where we need to modify for parallel processing. Because different parallel tasks are used compute for different users, you can see that very little changes are required for parallel computing. The only change required, is to replace the use of files with different blocks from the binary file. 

First we need to add parameters to subroutine, to pass the log file name, block number and total number of blocks for the week when called by the main program.

 And then modify the program as following:

The above screenshot illustrates that:
1. As we previously used export@g to retrieve the file according to different user ID, the use of @zoption by cursor to handle specific block (value is block number) among total (value is total blocks) from file, as shown in the red box, will retrieve the complete group for the same userID. Data for one user will not be split into two blocks.

2. A16 returns the resulting file as cursor to the main program.
The third step:writing main program for parallel computing, to call the parallel computing subroutine. Because the total cores of the server CPU is 8,the IT engineers decided to use six threads for parallel computing. This take full advantage of multi-core CPUs to improve performance.

Note: for specific measurements regarding esProc's performance gain with parallel computing, please refer to related test reports for esProc.

Upon the meeting of this requirement, IT engineers from the Web Company are facing a new problem: the user numbers for the online application grew explosively. Colleagues from the Operation Department complained that the online time computation program is still running too slow. The single-machine, multi-threaded approach can no longer enhance the computing speed significantly.Can these IT engineers effectively solve the performance issue using esProc's parallel multi-machine computing capability?Is it too costly to transform to a multi-machine parallel mode?


Using esProc to Compute the Online Time of Users (I)

Using esProc to Compute the Online Time of Users (II)

Computing the Online Time for Users with esProc (IV)

Using esProc to Compute the Online Time of Users (I)

As the operator of an online system, the Web Company believes that the users’ time spent with their online application is a key analysis scenario. Specifically, the online time refers to the cumulative time a user spent with their online business application over a certain period of time. 

With the evolving of the company's online application, total number of users has grown and the task of user behavior analysis is becoming more complex. Here, we use the example of computing the online time for users to show the various computing scenarios, ranging from simple to complex. Hopefully this could serve as a reference for similar development projects. In fact, the following approach are also applicable for other categories of user behavior analysis, such as user’s activity level, user churn, etc..

Let’s start from the time when the application just went online. The Operation Department needed to know the user’s online time with their application every week. For this the engineers from IT department provided the following resolution. 

The user behavior information is recorded in log files in the Web Company. Everyday a separatelog file is generated. For example, the following log file, “2014-01-07.log”, contains the users online actions on January 7, 2014. 
To compute the online time for user in the week of 2014-01-05 to 2014-01-11, we need to retrieve data from 7 log files:
logtime userid action
2014-01-07 09:27:56 258872799 login
2014-01-07 09:27:57 264484116 login
2014-01-07 09:27:58 264484279 login
2014-01-07 09:27:58 264548231 login
2014-01-07 09:27:58 248900695 login
2014-01-07 09:28:00 263867071 login
2014-01-07 09:28:01 264548400 login
2014-01-07 09:28:02 264549535 login
2014-01-07 09:28:02 264483234 login
2014-01-07 09:28:03 264484643 login
2014-01-07 09:28:05 308343890 login
2014-01-07 09:28:08 1210636885 post
2014-01-07 09:28:09 263786154 login
2014-01-07 09:28:12 263340514 get
2014-01-07 09:28:13 312717032 login
2014-01-07 09:28:16 263210957 login
2014-01-07 09:28:19 116285288 login
2014-01-07 09:28:22 311560888 login
2014-01-07 09:28:25 652277973 login
2014-01-07 09:28:34 310100518 login
2014-01-07 09:28:38 1513040773 login
2014-01-07 09:28:41 1326724709 logout
2014-01-07 09:28:45 191382377 login
2014-01-07 09:28:46 241719423 login
2014-01-07 09:28:46 245054760 login
2014-01-07 09:28:46 1231483493 get
2014-01-07 09:28:48 266079580 get
2014-01-07 09:28:51 1081189909 post
2014-01-07 09:28:51 312718109 login
2014-01-07 09:29:00 1060091317 login
2014-01-07 09:29:02 1917203557 login
2014-01-07 09:29:16 271415361 login
2014-01-07 09:29:18 277849970 login

Log files record, in chronological order, users’operation (action), user ID (userid) and the time when the actions took place (logtime) in the application. Users operations include three different types, which are login, logout and get/post actions. 

The Operation Department provided the following requirements for computation of users online time:
1. Login should be considered as the starting point of online time, and overnight should be take into consideration. 
2. If the time interval between any two operations is less than 3 seconds, then this interval should not be added to online time. 
3. If after login, the time interval between any two operations is longer than 600 seconds, then the user should be considered as logged out. 
4. If there is only login, without logout, then the last operation time should be treated as time for logout. 

As the online application was just rolled out, the data volume for log file is relatively small. To compute on data fromlog files for 2014-01-05 to 2014-01-11, we could retrieve all data into memory in one batch, or out to a resulting file. Thus all codes here are written for in-memory computing.

The IT Department leverages esProc to meet the above requirements.

The actualcodes are as following:

The ideas for program design are:
1. First, retrieve all log files for the week ( 2014-01-05 to 2014-01-11 ) and merge them in chronological order. Sorting them according to userid and logtime. Add two extra fields, online time and login flag for subsequent calculations.

2. Online time is for computing of the interval between two operations by the same user. If difference between the operation time of current line and last action is less than 3 seconds, or if the userid of current operation does not equal to that of last one, then online time is directly set to 0.

3. Login flag is used to indicate a valid online time. If onlinetime does not exceed 10 minutes (600 seconds), or the type of operation is logout, then loginflag is set to true. Otherwise it’s set to false. If it’s login operation, then login flag is directly set to true.

4. Upon the resulting sorted table from previous steps, compute login flag again.If loginflag was originally set to false,then leave it to false. If the value wereoriginally set to true, then the type of last operation would result to different value. If the last operation were login, then loginflag should still be set to true, otherwise it should be set to false.

5. Upon the resulting sorted table from previous steps, group the data according to userid. Compute the sum of onlinetime for all records whoseloginflag is true. This is the total online time for the same user. 

6. Output the result in the last step to a file onlinetime.data.

The advantage of the above codes lies in the step-by-step way of computation, which is easy to maintain and modify.

After working for a while, a new problem was found: On the one hand, The Operation Department said that the original way for online time computation should be adjusted, with new conditions added. On the other hand, with the increase of users, the log files grow larger, which is too big to fit into memory in one batch. Well, how should the IT Departments cope with this change in the requirements? 


Using esProc to Compute the Online Time of Users (II)

Computing the Online Time for Users with esProc (III)

Computing the Online Time for Users with esProc (IV)