-
Notifications
You must be signed in to change notification settings - Fork 335
Looking for the Best Java Data Computation Layer Tool
Sometimes it is inconvenient to perform computations for a JAVA project using the database, hence the data computation layer of Java is used. In this essay, we take a deep dive into four types of data computation layer tool (class libraries) to compare their structured data computation capabilities and basic functionalities, including integration & deployment, source data support, hot-deployment support, debugging, etc. Looking Looking for the Best Java Data Computation Layer Tool for details.
In most cases, structured data is computed within the database in SQL. In a few other cases where the database is either absent or unavailable, Java programmers need to perform computations with Java. Hardcoding, however, involves heavy workload. A more convenient alternative is the Java data computation layer tool (including library functions). The tool computes data and returns the result set. Here we examine the structured data computation abilities of several Java data computation layer tools.
This type of tool uses files, such as CSV and XLS, as physical database tables and offers JDBC interface upward to allow programmers to compute the tables in SQL statements. There are many products of this type, such as CSVJDBC, XLSJDBC, CDATA Excel JDBC and xlSQL, but none is mature enough to use conveniently. CSVJDBC is the most mature if we must pick one among them.
CSVJDBC is a free, open-source JAVA class library, which is naturally integration-friendly. Users just need to download a jar to integrate it with a JAVA program through JDBC interface. Below is a part of the tab-separated text file d:\data\Orders.txt:
OrderID Client SellerId Amount OrderDate
26 TAS 1 2142.4 2009-08-05
33 DSGC 1 613.2 2009-08-14
84 GC 1 88.5 2009-10-16
133 HU 1 1419.8 2010-12-12
…
The following block of code reads in all records of the file and print them out in the console:
package csvjdbctest;
import java.sql.*;
import org.relique.jdbc.csv.CsvDriver;
import java.util.Properties;
public class Test1 {
public static void main(String[] args)throws Exception {
Class.forName("org.relique.jdbc.csv.CsvDriver");
// Create a connection to directory given as first command line
String url = "jdbc:relique:csv:" + "D:\\data" + "?" +
"separator=\t" + "&" + "fileExtension=.txt";
Properties props = new Properties();
//Can only be queried after specifying the type of column
props.put("columnTypes", "Int,String,Int,Double,Date");
Connection conn = DriverManager.getConnection(url,props);
// Create a Statement object to execute the query with.
Statement stmt = conn.createStatement();
//SQL:Conditional query
ResultSet results = stmt.executeQuery("SELECT * FROM Orders");
// Dump out the results to a CSV file/console output with the same format
CsvDriver.writeToCsv(results, System.out, true);
// Clean up
conn.close();
}
}The JAVA class library has a rather good support for hot deployment. This is because it implements computations in SQL and can conveniently separate the SQL query from the Java code. Users can modify a SQL statement directly without compiling or restart the application.
Though CSVJDBC is excellent in JAVA integration and hot deployment, they are not the core abilities of a JAVA data computation layer tool. Structured data computation ability is at the heart of the tool, but the JDBC driver has a bad one.
CSVJDBC supports only a limited number of basic computations, including conditional query, sorting and grouping & aggregation.
//SQL:Conditional query
ResultSet results = stmt.executeQuery("SELECT * FROM Orders where Amount>1000 and Amount<=3000 and Client like'%S%' ");
//SQL:order by
results = stmt.executeQuery("SELECT * FROM Orders order by Client,Amount desc");
//SQL:group by
results = stmt.executeQuery("SELECT year(Orderdate) y,sum(Amount) s FROM Orders group by year(Orderdate)");
Set-oriented operations, subqueries and join operations are also part of the basic computations, but all of them are not supported. Even the several supported ones have a lot of problems. For instance, CSVJDBC requires that all file data be loaded into the memory for sorting and grouping & aggregation operations, so it would be better that the file size is not too large.
SQL statements do not support debugging and performs badly. CSVJDBCA supports only the CSV files. Other data formats, Database tables, Excel files and JSON data, should be converted to CSV files for processing. The conversion is cost inefficient as hardcoding or the third-party tool is needed.
This type of tool intends to simulate Python Pandas by providing generic data types similar to dataFrame, docking various data sources downward, and offering functional interface upward. Tablesaw, Joinery, Morpheus, Datavec, Paleo, Guava, and many others belong to this type. With Pandas being the successful precursor, this type of Java data computation layer tools fails to attract many followers, hence the reason they are generally poorly developed. Tablsaw (the latest version is 0.38.2), however, is the most developed one among them.
As a free, open-source JAVA class library, Tablesaw requires only the deployment of core jars and dependent packages to be integrated. The basic code generated by it is simple, too. To read in and print out all records of Orders.txt, for example, we have the following code:
package tablesawTest;
import tech.tablesaw.api.Table;
import tech.tablesaw.io.csv.CsvReadOptions;
public class TableTest {
public static void main(String[] args) throws Exception{
CsvReadOptions options_Orders = CsvReadOptions.builder("D:\\data\\Orders.txt").separator('\t').build();
Table Orders = Table.read().usingOptions(options_Orders);
System.out.println(orders.print());
}
}Besides CSV files, Tablsaw also supports other types of sources, including RDBMS, Excel, JSON and HTML. Basically, it can satisfy the daily analytic work. The functional computations bring in satisfactory debugging experience by supporting a set of functionalities, such as break a point, step, enter and exit, while reducing hot deployment performance by requiring recompilement for any change of the code.
Structured data computations are always our focus. Below are several basic computations:
// Conditional query
Table query= Orders.where(
Orders.stringColumn("Client").containsString("S").and(
Orders.doubleColumn("Amount").isGreaterThan(1000).and(
Orders.doubleColumn("Amount").isLessThanOrEqualTo(3000)
)
)
);
// Sorting
Table sort = Orders.sortOn("Client", "-Amount");
//Grouping & aggregation
Table summary = Orders.summarize("Amount", sum).by(t1.dateColumn("OrderDate").year());
// Joins
CsvReadOptions options_Employees = CsvReadOptions._builder_("D:\\\data\\\Employees.txt").separator('\\t').build();
Table Employees = Table._read_().usingOptions(options_Employees);
Table joined = Orders._joinOn_("SellerId").inner(Employees,**true**,"EId");
joined.retainColumns("OrderID","Client","SellerId","Amount","OrderDate","Name","Gender","Dept");For computations like sorting and grouping & aggregation operations that only need to take a few factors into consideration, the gap between Tablesaw and SQL are very small. If a computation, like conditional query and the join operation, involves a lot of factors, Tablesaw generates far more complicated code than SQL does. The reason is that JAVA, not intrinsically intended for structured data computing, must trade complicated code for an equal computing ability as SQL. Fortunately, the high-level language supports lambda syntax to be able to generate relatively intuitive code (though not as intuitive as SQL). The conditional query can be rewritten as follows:
Table query2=Orders.where(
and(x->x.stringColumn("Client").containsString("S"),
and(
x -> x.doubleColumn("Amount").isGreaterThan(1000),
x -> x.doubleColumn("Amount").isLessThanOrEqualTo(3000))));
The characteristics of lightweight databases are small, easy to deploy and convenient to integrate. Typical products of the lightweight databases include SQLite, Derby and HSQLDB. Let’s take a special look at SQLite.
SQLite is free and open source. Only one jar is needed to do the integration and deployment. It is not a stand-alone system and usually runs within a host application, such as JAVA, through API interface. When it runs on an external storage disk, it supports a relatively large data volume. When it runs in the memory, it performs better but supports smaller data volume. SQLite adheres to the JDBC standards. For example, to print out all records of orders table in the external database exl, we have the following code:
package sqliteTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String[] args)throws Exception {
Connection connection =DriverManager.getConnection("jdbc:sqlite/data/ex1");
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("select * from Orders");
printResult(results);
if(connection != null) connection.close();
}
public static void printResult(ResultSet rs) throws Exception{
int colCount=rs.getMetaData().getColumnCount();
System.out.println();
for(int i=1;i<colCount+1;i++){
System.out.print(rs.getMetaData().getColumnName(i)+"\t");
}
System.out.println();
while(rs.next()){
for (int i=1;i<colCount+1;i++){
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
}
}
}The support of SQLite for source data use is demanding. Data, no matter of what type, must be loaded to it for computation (Certain lightweight databases can directly identify CSV/Excel files as database table). There are two ways to load the CSV file Orders.txr. The first uses JAVA to read in the CSV file, composes each row into an insert statement and then execute the statements. This requires a lot of coed. The second is manual. Users download the official maintenance tool sqlite3.ext and execute the following commands at the command line:
sqlite3.exe ex1
.headers on
.separator "\\t"
.import D:\\\data\\\Orders.txt Orders
Though losing points in the source data support scalability, the database software reclaims them by winning the structured data computation test. It handles all basic operations conveniently and performs well.
// Conditional query
results = statement.executeQuery("SELECT * FROM Orders where Amount>1000 and Amount<=3000 and Client like'%S%' ");
// Sorting
results = statement.executeQuery("SELECT * FROM Orders order by Client,Amount desc");
// Grouping & aggregation
results = statement.executeQuery("SELECT strftime('%Y',Orderdate) y,sum(Amount) s FROM Orders group by strftime('%Y',Orderdate) ");
// Join operation
results = statement.executeQuery("SELECT OrderID,Client,SellerId,Amount,OrderDate,Name,Gender,Dept from Orders inner join Employees on Orders.SellerId=Employees.EId")
In a word, SQLlite has the intrinsic characteristics of a SQL engine, which are good hot-deployment performance and poor debugging experience.
They are designed for computing structured data, aiming to increase operation expression efficiency and execution performance and with the support for diverse data sources, convenient integration, script hot-deploy and code debugging. There are not many of them. Scala, esProc SPL and linq4j are the most used ones. Yet as linq4j is not mature enough, we just look at the other two.
Scala is made as a general-purpose programming language. Yet it has become famous for its professional structured data computation abilities, which encapsulate Spark’s distributed computing ability and non-framework, non-service-connected local computing ability. Scala runs on JVM, so it can be easily integrated with a JAVA program. To read in and print out all records of Orders.txt, for instance, we can first write the following TestScala.Scala script:
package test
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.DataFrame
object TestScala{
def readCsv():DataFrame={
// Only the jar is needed for a local execution, without configuring or starting Spark
val spark = SparkSession.builder()
.master("local")
.appName("example")
.getOrCreate()
val Orders = spark.read.option("header", "true").option("sep","\\t")
// Should auto-parse into the right data type before performing computations
.option("inferSchema", "true")
.csv("D:/data/Orders.txt")
// Should specify the date type for subsequent date handling
.withColumn("OrderDate", col("OrderDate").cast(DateType))
return Orders
}
}Compile the above Scale script file into an executable program (JAVA class) and then it can be called from a piece of JAVA code, as shown below:
package test;
import org.apache.spark.sql.Dataset;
public class HelloJava {
public static void main(String\[\] args) {
Dataset ds = TestScala.readCsv();
ds.show();
}
}Scala code for handling basic structured data computations is fairly simple:
//Conditional query
val condtion = Orders.where("Amount>1000 and Amount<=3000 and Client like'%S%' ")
//Sorting
val orderBy = Orders.sort(asc("Client"),desc("Amount"))
//Grouping & aggregation
val groupBy = Orders.groupBy(year(Orders("OrderDate"))).agg(sum("Amount"))
//Join operations
val Employees = spark.read.option("header", "true").option("sep","\\t")
.option("inferSchema", "true")
.csv("D:/data/Employees.txt")
val join = Orders.join(Employees,Orders("SellerId")===Employees("EId"),"Inner")
.select("OrderID","Client","SellerId","Amount","OrderDate","Name","Gender","Dept")
// Positions of records are changed after the join, a sorting will keep the order consistent with that of the result set obtained using the other tools
.orderBy("SellerId")
Scala supports running same code on a rich variety of data sources. It can be regarded as the improved JAVA, so it also boasts excellent debugging experience. But, as a compiled language, Scala is hard to hot-deploy.
There is a tool that outperforms Scala. It is esProc SPL, which is targeted at structured data computations. esProc offers JDBC interface to be conveniently integrated into a piece of Java program. For instance, below is the code for reading in and printing out all records of Orders.txt:
package Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class test1 {
public static void main(String\[\] args)throws Exception {
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection = DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str = "=file(\\"D:/data/Orders.txt\\").import@t()";
ResultSet result = statement.executeQuery(str);
printResult(result);
if(connection != null){
connection.close();
}
}
public static void printResult(ResultSet rs) throws Exception{
int colCount = rs.getMetaData().getColumnCount();
System.out.println();
for(int i=1;i<colCount+1;i++){
System.out.print(rs.getMetaData().getColumnName(i)+"\\t");
}
System.out.println();
while(rs.next()){
for (int i=1;i<colCount+1;i++){
System.out.print(rs.getString(i)+"\\t");
}
System.out.println();
}
}
}The SPL code for handling basic structure data computations is simple and easy to understand:
// Conditional query
str = "=T(\\"D:/data/Orders.txt\\").select(Amount>1000 && Amount<=3000 && like(Client,\\"\*S\*\\"))";
// Sorting
str = "=T(\\"D:/data/Orders.txt\\").sort(Client,-Amount)";
// Grouping & aggregation
str = "=T(\\"D:/data/Orders.txt\\").groups(year(OrderDate);sum(Amount))";
// Join operations
str = "=join(T(\\"D:/data/Orders.txt\\"):O,SellerId; T(\\"D:/data/Employees.txt\\"):E,EId).new(O.OrderID,O.Client,O.SellerId,O.Amount,O.OrderDate,E.Name,E.Gender,E.Dept)";
SPL also provides corresponding SQL syntax for skilled SQL programmers. The above task of handling grouping & aggregation can be rewritten as this:
str="$SELECT year(OrderDate),sum(Amount) from Orders.txt group by year(OrderDate)"
A SPL process can be stored as a script file, too, which further reduces the code coupling. Here is an example:
Duty.xlsx stores daily duty records. Usually, one person will be on duty for several continuous workdays and then switch the job to the next person. Based on this table, we want to get the detailed duty data for each person in turn. Below is the data structure:
The source table (Duty.xlsx):
| Date | Name |
|---|---|
| 2018-03-01 | Emily |
| 2018-03-02 | Emily |
| 2018-03-04 | Emily |
| 2018-03-04 | Johnson |
| 2018-04-05 | Ashley |
| 2018-03-06 | Emily |
| 2018-03-07 | Emily |
| … | … |
The processing result:
| Name | Begin | End |
|---|---|---|
| Emily | 2018-03-01 | 2018-03-03 |
| Johnson | 2018-03-04 | 2018-03-04 |
| Ashley | 2018-03-05 | 2018-03-05 |
| Emily | 2018-03-06 | 2018-03-07 |
| … | … | … |
The following SPL script file (con_days.dfx) is used to implement the above task:
| A | |
|---|---|
| 1 | =T("D:/data/Duty.xlsx") |
| 2 | =A1.group@o(name) |
| 3 | =A2.new(name, |
Then the SPL script file can be called from the block of JAVA code below:
…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection = DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call con_days()");
…
Though the above task contains complex computing logic, which is hard to express even with Scala or a lightweight database, SPL is easy to implement with richer related functions and agile syntax.
Placing a script outside of the main application also allows programmers to edit and debug code on the specialized IDE. This helps implement tasks with extremely complex logics. Take the filtering of the running total as an example:
Database table sales stores customers’ sales amounts. The main fields are client an amount. The task is to find the first N big customers whose sum of sales amount takes up at least half of the total amount and sort them in descending order by amount.
We can use the SPL script below to achieve the task and then call it in Java:
| A | B | |
|---|---|---|
| 1 | =demo.query(“select client,amount from sales”).sort(amount:-1) | Retrieve records and sort them in descending order |
| 2 | =A1.cumulate(amount) | Get the sequence of cumulative totals |
| 3 | =A2.m(-1)/2 | Get the last member, which is the total |
| 4 | =A2.pselect(~>=A3) | Get the position where the cumulative sum exceeds half the total |
| 5 | =A1(to(A4)) | Get targeted values by positions |
esProc SPL has the most powerful structured data computation capability. Apart from this, it also excels at many other aspects, including code debugging, data source support, big data computing ability and parallel processing support – which are explained in other essays.
SPL Resource： SPL Official Website | SPL Blog | Download esProc SPL | SPL Source Code