esProc 35 No.
14 View •
How to Conveniently Query Text Data with SQL in Java
Key words: Text data Java SQL Query
Java doesn’t support querying a text file directly in SQL. A usual workaround is to create a temporary database table, parse the text data write it to database, and then perform retrieve, filter, group or other operations in SQL.
It’s complicated and difficult because:
1. The configuration of Java development environment is complicated and thus requires a lot of skills; the Java script is long.
2. Database needs to be in place to use SQL and temporary tables should be created. It’s simple but inconvenient.
3. It takes long to solve temporary and ad hoc needs; and is particularly hard for certain scenarios where databases cannot be installed and code is written all in Java.
Here’s an example: to get orders where a single amount is greater than 10000 after 1996-07-23. Below is a sample of the source data:
O_ORDERKEY O_CUSTKEY O_ORDERDATE O_TOTALPRICE
10262 RATTC 1996-07-22 14487.0
10263 ERNSH 1996-07-23 43818.0
10264 FOLKO 2007-07-24 1101.0
10265 BLONP 1996-07-25 5528.0
10266 WARTH 1996-07-26 7719.0
10267 FRANK 1996-07-29 20858.0
10268 GROSR 1996-07-30 19887.0
10269 WHITC 1996-07-31 456.0
10270 WARTH 1996-08-01 13654.0
In this case I vote for esProc because it has the ability to directly query and compute a text file or an Excel file in a SQL-like way. That’s simple and convenient. A one-liner to achieve the above query goal with esProc:
$()select * from /workspace/orders.txt where O_ORDERDATE>=date('1996-07-23') and O_TOTALPRICE >10000
Instead of using Java directly, we can deal with many text /Excel data query scenarios conveniently with the help of esProc SPL. Find more examples in:
esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.