Looking for the Best Java Data Computation Layer Tool
This essay is a deep dive into 4 types of data computation layer tools (class libraries) to compare structured data computing capabilities and basic functionalities.
Join the DZone community and get the full member experience.Join For Free
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 a 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.
1. SQL Engine for Files
This type of tool uses files, such as CSV and XLS, like physical database tables and offers a 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 the JDBC interface. Below is a part of the tab-separated text file d:\data\Orders.txt:
The following block of code reads in all records of the file and print them out in the console:
The Java class library has 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, and aggregation.
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, and aggregation operations, so it would be better if the file size is not too large.
SQL statements do not support debugging and perform badly. CSVJDBCA supports only 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 a third-party tool is needed.
2. dataFrame Generic Functions Library
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 tool fails to attract many followers, hence the reason they are generally poorly developed. Tablesaw (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:
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 a 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 algorithm.
Structured data computations are always our focus. Below are several basic computations:
For computations like sorting and grouping and 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:
3. Lightweight Databases
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 an 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 a 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:
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 a database table). There are two ways to load the CSV file Orders.txt. The first uses Java to read in the CSV file, composes each row into an insert statement, and then executes the statements. This requires a lot of coeds. The second is manual. Users download the official maintenance tool sqlite3.ext and execute the following commands at the command line:
.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 algorithms conveniently and performs well.
In a word, SQLlite has the intrinsic characteristics of a SQL engine, which are good hot-deployment performance and poor debugging experience.
4. Professional Structured Data Computation Languages
They are designed for computing structured data, aiming to increase algorithm expression efficiency and execution performance and with the support for diverse data sources, convenient integration, algorithm hot-deploy, and algorithm debugging. There are not many of them. Scala, esProc, 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:
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:
Scala code for handling basic structured data computations is fairly simple:
Scala supports running the same code on a rich variety of data sources. It can be regarded as the improved Java, so it also boasts an excellent debugging experience. But, as a compiled language, Scala is hard to hot-deploy.
There is a tool that outperforms Scala. It is esProc, which is targeted at structured data computations. esProc offers a 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:
The esProc code for handling basic structure data computations is simple and easy to understand:
esProc also provides corresponding SQL syntax for skilled SQL programmers. The above algorithm of handling grouping and aggregation can be rewritten as this:
|str="$SELECT year(OrderDate),sum(Amount) from Orders.txt group by year(OrderDate)"|
An esProc algorithm 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):
The processing result:
The following esProc script file (con_days.dfx) is used to implement the above algorithm:
Then the esProc script file can be called from the block of Java code below:
Though the above algorithm contains complex computing logic, which is hard to express even with Scala or a lightweight database, esProc is easy to implement with richer related functions and agile syntax.
Placing an algorithm outside of the main application also allows programmers to edit and debug code on the specialized IDE. This helps implement algorithms with extremely complex logic. Take the filtering of the running total as an example:
Database table sales stores customers’ sales amounts. The main fields are client and 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 esProc script below to achieve the algorithm and then call it in Java:
esProc 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.
Opinions expressed by DZone contributors are their own.