1

Is there a open source file based (NOT in-memory based) JDBC driver for CSV files? My CSV are dynamically generated from the UI according to the user selections and each user will have a different CSV file. I'm doing this to reduce database hits, since the information is contained in the CSV file. I only need to perform SELECT operations.

HSQLDB allows for indexed searches if we specify an index, but I won't be able to provide an unique column that can be used as an index, hence it does SQL operations in memory.

Edit:

I've tried CSVJDBC but that doesn't support simple operations like order by and group by. It is still unclear whether it reads from file or loads into memory.

I've tried xlSQL, but that again relies on HSQLDB and only works with Excel and not CSV. Plus its not in development or support anymore.

H2, but that only reads CSV. Doesn't support SQL.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Varun Achar
  • 14,781
  • 7
  • 57
  • 74
  • If you want to keep the table in memory, why do you need an index? Sounds like HSQLDB is what you are looking for. How big are those CSV files (number of rows)? –  Sep 27 '11 at 13:04
  • I don't need an in-memory. I want a driver that reads CSV file and performs operations there itself, not after loading it into memory. In load testing a file of size `60MB` took `300MB` of physical memory (as showed by task manager of windows) while performing operations using `hsqldb`! No of rows around 230K.Thanks – Varun Achar Sep 27 '11 at 13:09
  • 1
    Did you try to change the cache value (cache_rows and cache_size) for the TEXT table in HSQLDB? Setting the cache value very low might improve the memory consumption: http://hsqldb.org/doc/2.0/guide/texttables-chapt.html#texttables_globalprops-sect –  Sep 27 '11 at 13:41
  • 1
    CsvJdbc does now support ORDER BY and GROUP BY and http://csvjdbc.sf.net describes the memory usage. For queries containing ORDER BY or a GROUP BY plus an aggregate functions, all rows are read into memory in order to do the sorting or grouping. For other types of queries, only one row is held in memory at a time. – Simon C Apr 18 '13 at 17:36

5 Answers5

3

You can solve this problem using the H2 database.

The following groovy script demonstrates:

  1. Loading data into the database
  2. Running a "GROUP BY" and "ORDER BY" sql query

Note: H2 supports in-memory databases, so you have the choice of persisting the data or not.

// Create the database
def sql = Sql.newInstance("jdbc:h2:db/csv", "user", "pass", "org.h2.Driver")

// Load CSV file
sql.execute("CREATE TABLE data (id INT PRIMARY KEY, message VARCHAR(255), score INT) AS SELECT * FROM CSVREAD('data.csv')")

// Print results 
def result = sql.firstRow("SELECT message, score, count(*) FROM data GROUP BY message, score ORDER BY score")

assert result[0] == "hello world"
assert result[1] == 0
assert result[2] == 5

// Cleanup
sql.close()

Sample CSV data:

0,hello world,0
1,hello world,1
2,hello world,0
3,hello world,1
4,hello world,0
5,hello world,1
6,hello world,0
7,hello world,1
8,hello world,0
9,hello world,1
10,hello world,0
Mark O'Connor
  • 76,015
  • 10
  • 139
  • 185
  • I don't have a primary key column in the data; neither can i guarantee a candidate key from the data. Will this still work? Can I create an additional column that will act as a auto generated primary key? Thanks for your help. – Varun Achar Sep 28 '11 at 06:13
  • Just remove the "PRIMARY KEY" constraint and it will still work. Creating an additional column would require you to select into a second table. The "SELECT * FROM CSVREAD.." construct appears to require that the number of columns in the CSV file matches the columns in the database table. – Mark O'Connor Sep 28 '11 at 17:54
1

If you check the sourceforge project csvjdbc please report your expierences. the documentation says it is useful for importing CSV files.

Project page

stacker
  • 68,052
  • 28
  • 140
  • 210
  • I have checkout out csvjdbc. The operations allowed on csvjdbc are very limited. I was unable to do simple operations like `order by` and `group by`. – Varun Achar Sep 27 '11 at 12:52
1

This was discussed on Superuser https://superuser.com/questions/7169/querying-a-csv-file.

You can use the Text Tables feature of hsqldb: http://hsqldb.org/doc/2.0/guide/texttables-chapt.html

csvsql/gcsvsql are also possible solutions (but there is no JDBC driver, you will have to run a command line program for your query).

sqlite is another solution but you have to import the CSV file into a database before you can query it.

Alternatively, there is commercial software such as http://www.csv-jdbc.com/ which will do what you want.

Community
  • 1
  • 1
Simon C
  • 1,977
  • 11
  • 14
0

To do anything with a file you have to load it into memory at some point. What you could do is just open the file and read it line by line, discarding the previous line as you read in a new one. Only downside to this approach is its linearity. Have you thought about using something like memcache on a server where you use Key-Value stores in memory you can query instead of dumping to a CSV file?

FloppyDisk
  • 1,693
  • 16
  • 25
  • Each user can have different multiple `csv` files.This data needs to be persisted since it can be requested by user anytime in the future. I can't keep all this data in memory. Thanks – Varun Achar Sep 27 '11 at 18:52
  • @VarunAchar: Not being familiar with what kind of data you are dealing with or the space you are working in, do users frequently request old data or do they tend to grab the newest information most of the time? Reason I ask is, if they tend to go for the newest information you could take a staggered approach to the problem. Newly generated info goes into memcached/memory for fast retrieval and the rest sits in the database. – FloppyDisk Sep 27 '11 at 19:24
  • Problem is that simple operations like sorting goes to the DB. This causes the data to get refreshed. Whereas I don't want to perform a refresh, I only want to sort the data in the csv. If I do it on the database, I'll need to add additional filters so that the correct data, which matches the data in the csv, gets sorted, whereas if it was possible on csv, I don't need to hit the database at all. Thanks for the help. – Varun Achar Sep 28 '11 at 06:08
0

You can use either specialized JDBC driver, like CsvJdbc (http://csvjdbc.sourceforge.net) or you may chose to configure a database engine such as mySQL to treat your CSV as a table and then manipulate your CSV through standard JDBC driver.

The trade-off here - available SQL features vs performance.

  • Direct access to CSV via CsvJdbc (or similar) will allow you very quick operations on big data volumes, but without capabilities to sort or group records using SQL commands ;
  • mySQL CSV engine can provide rich set of SQL features, but with the cost of performance.

So if the size of your table is relatively small - go with mySQL. However if you need to process big files (> 100Mb) without need for grouping or sorting - go with CsvJdbc. If you need both - handle very bif files and be able to manipulate them using SQL, then optimal course of action - to load the CSV into normal database table (e.g. mySQL) first and then handle the data as usual SQL table.

Vladimir Kroz
  • 5,237
  • 6
  • 39
  • 50