* 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

...

Expected result:

undefined

It would be much simpler if we use esProc SPL. 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:


A

1

$()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:

Structured Text Computing with esProc

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.