MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.

I'm not curious

esProc Helps Process Heterogeneous Data sources in Java - Hive

Published on 22 October 14

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:


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:
esProc Helps Process Heterogeneous Data sources in Java - Hive - Image 1

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 which mAmount 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:
esProc Helps Process Heterogeneous Data sources in Java - Hive - Image 2

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


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


// 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.
This blog is listed under Data & Information Management Community

Related Posts:
Post a Comment

Please notify me the replies via email.

  • We hope the conversations that take place on will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url