esProc Helps Process Heterogeneous Data sources in Java - Hive

It is easy for Java to connect to Hive using JDBC. But the computational ability of Hive is less than that of SQL in other databases. So to deal with uncommon computations, data should be retrieved before further operation is performed using Java. Thus the code for will be complicated.

But if esProc is used to help with the Java programming, the complicated operation as a result of using Hive in Java will become simpler. The following example will show how esProc works with Java in detail. orders is a table in Hive containing the detailed data of sales orders. Now it is required to compute the year-on-year comparison and link relative ratio. The data is as follows:

ORDERID CLIENT     SELLERID AMOUNT ORDERDATE

1       UJRNP      17     392  2008/11/2 15:28

2       SJCH         6       4802         2008/11/9 15:28

3       UJRNP      16     13500       2008/11/5 15:28

4       PWQ         9       26100       2008/11/8 15:28

5       PWQ         11     4410         2008/11/12 15:28

6       HANAR     18     6174         2008/11/7 15:28

7       EGU 2       17800       2008/11/6 15:28

8       VILJX         7       2156         2008/11/9 15:28

9       JAYB          14     17400       2008/11/12 15:28

10     JAXE          19     19200       2008/11/12 15:28

11     SJCH         7       13700       2008/11/10 15:28

12     QUICK      11     21200       2008/11/13 15:28

13     HL    12     21400       2008/11/21 15:28

14     JAYB          1       7644         2008/11/16 15:28

15     MIP  16     3234         2008/11/19 15:28

…

Link relative ratio refers to comparison between the current data and data of the previous period, using month as the time interval. For example, divide the sales figure in April by that in March and we get the link relative ratio of April. Year-on-year comparison is the comparison between the current data and data of the corresponding period of the previous year, which means, for example, dividing the sales figure of April 2014 by that of April 2013. Since Hive provides no window functions, it cannot complete the computation unless using nested SQL. But Hive supports very poor subquery and usually the computation should be performed outside of the database. With esProc, however, the computation can be realized easily. The code is as follows:

A1：Connect to the database through JDBC using the datasource Hive defined in advance.

A2：Query the data in the database by the time period using external parameters begin and end. Such as begin="2011-01-01 00:00:00", end="2014-07-08 00:00:00" (i.e. the current date which can be obtained using now() function).

A3:Group orders by the year and the month and sum up to get the sales of each month.

A4:Add a new field Irr, which is the monthly link relative ratio. The expression is mAmount/mAmount[-1], in whichmAmount represents the sales in the current time period and mAmount[-1] represents the sales in the previous one. Note that the link relative ratio of the initial month (January of 2011) is empty.

A5:Sort the data in A4 by the month and the year before we compute the year-on-year comparison. Complete code should be =A4.sort(m,y). But since A4 has been sorted by the year, here we just need to sort it by the month, that is A4.sort(m), which has a better performance.

A6:Add another new field yoy, which is the year-on-year comparison of the monthly sales figure. The expression is if(m==m[-1],mAmount/mAmount[-1],null), which means the year-on-year comparison is valid only between the same months of the two time periods. The year-on-year comparison of each month in the initial year (the year of 2011) is empty.

A7：Sort the data in A6 by the year in descending order and by the month in ascending order. Note that the data is valid up to July of 2014. The result is as follows:

A8：Close Hive database connection.

A9：Return the result.

This block of code can be called by Java using esProc JDBC to get the final result (the above esProc program will be saved as test.dfx). The code for this is as follows:

          // create a connection usingesProcjdbc

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

// call the esProc program (the stored procedure); test is the file name of dfx

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?,?)");

// set the parameters

st.setObject(1,"2011-01-01 00:00:00");//begin

st.setObject(1,"2014-07-08 00:00:00");//end

// execute esProc stored procedure

st.execute();

// get the result set

ResultSet set = st.getResultSet();

It is the same way in which esProc accesses Hive and other ordinary databases. Just configure their JDBC while detailed process is omitted here.

         

Comparative Test Report on esProc, Hive, Impala Clusters (part VI)

6. Hermeneutic Analysis

In general, by observing the results tested from each use case, the data features may be concluded as below:

1.In most cases, the performances of three take on a downgrading trend, that is, esProc ranks top, Impala takes second place, Hive is worst. esProc comfortably stays ahead of Impala, and is several times higher than Hive in performance.

2.The case of big group is somewhat special. Hive presents a lot better performance than Impala. In the use case for common group, Impala is also inferior to Hive.

3.Impala is not sensitive to computation amount, and it degrades slowly even if the computation amount continues to grow.

The reasons why these three tools present the above features come here as follows: 

1. esProc's performance takes the top level, which could benefit from hard disk IO. esProc enables direct access to hard disk bypass HDFS, while Hive/Impala depends on HDFS in this regard. As the most time for big data computation is consumed on the hard disk, esProc may gain the performance boosts of hard disk IO. Of course, this advantage of esProc makes sense only for small and middle scales of clusters, because in large cluster environment, esProc still ensures the data safety by HDFS or by other redundancy performance.

2. In most cases, Impala demonstrates better performance than Hive, which could involve data exchange. Impala supports in-memory computation, it can exchange data in memory; while Hive only supports out-memory computation, it has to do a data exchange by hard disk. In terms of exchange data, Impala's performance is higher than that of Hive at least by an order of magnitude, but from its overall performance, the gap of 3-90 times just like what Cloudera declared does not appear at all, generally only 2-3 times as it surpasses over Hive. 

3. When operating a big group, Impala is far more inferior to Hive. In the use case for common group, Impala also performs worse than Hive on rare occasion. Both cases appear as long as there is a large mass of data. As you think, Impala only provides a support for in-memory computation, so you can guess this phenomenon isdue to the fact that the data volume is massive enough to reach the limit of memory. At this time, JVM needs to proceed frequent memory exchanges. In fact, the memory overflow will appear in Impala when data amount further increases. Unless additional physical memory is available, the computation can't be achieved.

4. Impala is not sensitive to the computation amount, and it degrades slowly even if the computation amount continues to grow. This is because Impala supports dynamical native code generation, while Hive and esProc is interpreted by JAVA with a big gap of executingefficiency. The big data computation, however, mainly consumes the most time on the hard disk IO, not code execution Impala's advantage in native code generation does not often help improve its overall performance. 

Comparative Test Report on esProc, Hive, Impala Clusters (part V)

5.4 Use Case for dimension Table across Nodes

This case is used for testing performance of foreign key join over big data. The dimension table in this case is big enough to exceed the memory size. At this time, it will be segmented to be loaded into several sub-nodes.

Use Case Number Description:

Tested Results :

Data Features:

1. esProc, Impala and Hive degrade in turn, and esProc shows an evident advantage.

2. Data type also produces a very minimal impact on the performances of three test objects.  

5.5 Use Case for Big Group

This case is used for testing performance of big data grouping, and the number of grouped result goes far beyond the physical memory that the computation can’t be done within  memory.

Use Case Description: 

Tested Results 

Data Features:

1. As we can see, the change has taken place in the rank. esProc, Hive, Impala degrade in turn, esProc still significantly takes the lead, and Impala present a worst performance. 

2. The performance of Impala will decrease sharply when there is a huge mass of the data.

3. Data type also produces a very minimal impact on the performances of three test objects.

Comparative Test Report on esProc, Hive, Impala Clusters (part IV)

5.3 Joining Use Case

This case is used for testing performance of foreign key join over big data, involving of integer, digital and string join as well as single-level and multilevel.

Use Case Number Description:

 Tested Results:

Data Features:
1.esProc, Impala and Hive degrade in turn. At top level of performances, esProc is nearly twice times higher than Impala, while 4-6 times than Hive. 

2.Impala lacks sensitivity to computation amount. As you imagine, if the computing computation amount continues to increase, its performance will exceed that of esProc. 

3.When the number of levels needed to be joined increases, the performances of both esProc and Impala have unapparent changes, but Hive’s performance will be reduced significantly.

Comparative Test Report on esProc, Hive, Impala Clusters (part III)

5.2 Use Case for Group

This case is used for testing performance of big data grouping, while separately taking integer, digital, string, date and other grouping into account. The number of grouped results in this case is relatively small, less than physical memory of node.

Use Case Number Description:

Tested Results:

Data Feature:

In general, esProc, Impala and Hive degrade in turn. At top level of performances, esProc is 2-3 times higher than Impala, while 3-4 times higher than Hive. Apart from Use cases 32, 36, 40 which are used for wide table with large amount data at this point, Hive presents higher performance than Impala.

Comparative Test Report on esProc, Hive, Impala Clusters (part II)

5. Test Case

To make you easy to understand, the operation logic of any test will be described in SQL. During the test, Hive and Impala implement SQL statements directly, while in esProc, we will write the codes to achieve SQL function-equivalent operations.

5.1 Use Case for Scan

This case is used for testing performance of full-table scanning of big data, while separately considering simple counting, integer summary, float  summary and numeric summary, as well as filtering of integer, digital, string and date, and other functions.

Use case description:

Tested Results

Note: Time unit is in seconds.

Data Fetures:

1. esProc, Impala and Hive degrade in turn, at top level of performance, esProc is 2-3 times higher than Impala, and 3-4 times than Hive.

2. Computing amount has a little impact on the performances of three test objects.

3. Data type also produces a very minimal impact on the performances of three test objects.

Comparative Test Report on esProc, Hive, Impala Clusters (part I)

1. Objective

By making separate tests on computation capabilities of esProc, Hive and Impala clusters as they are running in the same hardware environment, to demonstrate different performances by comparison.

2. Test Content and Method

Cluster Scale: 4 Nodes.

Data volume: 125G wide fact table and 143G narrow fact table are used as primary data for testing, whose data volume is considerably larger than the physical memory of computer node.

Algorithm classification: Total five typical SQL algorithms: scan, group, join, join big dimension table across nodes and big group, are separately tested. What you should note here is that, the purpose of adopting these simple algorithms is only to help understand testing process and intuitively demonstrate comparative performances, but not means esProc and SQL are fully consistent. In fact, both of them lay stress on different key functions, that is to say, esProc is good at computation with relatively more complex business logic, while SQL is fit to operate some common complex computations. Complex algorithm in SQL will be executed with different plan, which is out of manual control, and thus not good for comparison. We'll not do such test.

Category of use cases: A number of sets of use cases will be designed for test process according to table width, data types and computation amount.

Storage structure: Row-based storage.

Note: The Comparative Test Process for esProc, Hive, Impala Clusters is annexed to this report. For specific data structure, test code, test reproducibility and other contents, refer to this document.

3. Environment Description

Hardware:

       Number of PCs：4

       CPU：Intel Core i5 2500(4 Cores)

       RAM：16G

       HDD：2T/7200rpm

       Ethernet adapter：1000M

Software:

       OS: CentOS6.4

       JDK：1.7

       Hadoop/hdfs 2.2.0

Test Objects:

       Hive  0.11.0

       esProc     3.1

       Impala    1.2.0

4. Data Description

Data scale is defined based on the exported files.

The file formats that help demonstrate the highest performance of each object are used here, of which, esProc uses the proprietary binary files; Hive and Impala use the text files.

4.1 Data Table and Associative Table

Fact Table T1

      

This is a wide Table, used to simulate the fact case with a number of fields, which is designed with 100 fields.

      

Fact TableT2

      

This is narrow Table T2, used to simulate a fact case with lesser fields, which is designed with 11 fields.

The fact table is primary data source in this test, which will be used during scanning, grouping, joining processes.

Dimension Tables DL2, DL6, DD2, DD6, DC2, DC6

The dimension table is only used to test the usecase of join (and multi-level join) operation. These dimension tables will join with the fact table, also join with each other with smaller amount of data.

      

DC11, as a dimension table across nodes, must be loaded as segmented into memory of different computers because the table is too large to load into one computer. Cluster needs to load DC11 across nodes to complete the computation.

4.2 . Data Scale