Re: CSVREAD in H2

[Brian]

There are a couple of options - since it's not indexed, it may or not be any faster.
a.) If your DB has a jdbc driver, use a linked table, query the linked table and do the group/order by with H2.
b.) If you don't have a jdbc driver, you can treat CSVREAD() _as_ a table without re-persisting it.
ex: given a csv like:
COMMON,DIFF
"a",1
"a",2
"b",3
"b",4

In h2, this works fine:
select common,count(diff) from CSVREAD('test.csv') group by common order by common;

-Brian


On Friday, July 27, 2012 3:36:30 AM UTC-7, bharath phatak wrote:

I am totally new to H2 database and have been following your posts in learning H2 database and working with it. 

I am stuck with a problem and need your advice .

We treat H2 database as a tmpDB for running the query which are not supported by our underlying DB. Ex: Order by and Group By are not supported by our DB.

To get this done we can use normal way like running the basic query on our DB ,storing the results in csv format and bulk insert into H2(CSVREAD) and run the query once again on H2 to get the final results.

The problem with above approach is that it includes double I/O . The data can include million records. 

What we think: 

• Understand CSVREAD code and customize it to our needs. 
• The data keeps coming-in in buffer  .
• Without manipulating it,directly insert into H2 like CSVREAD.
• Assuming that CSVREAD reads data and  stores in a buffer before inserting into data, we need to implement CSVREAD code for inserting the data from buffer.

Please advice on how to proceed further. It will be very helpful if you could provide point us to the CSVREAD code.

Also please let us know if you have any better approach for bulk insert of data into H2 for buffer.

Thanks in advance.

Regards,

Bharath

[bharath phatak]

Thanks Brian for the reply.

I guess CSVREAD is the only option for bulk insert.  

Can we restrict reading number of rows from csvfile ?

Can we call CSVREAD in a loop till EOF to read limited number of lines ?

Something like: 

1.Reader keeps writting record to csv file.

2.If 1000 records present then insert into table else wait till 1000 records.

Regards,

Bharath

[Thomas Mueller]

Hi,

I guess CSVREAD is the only option for bulk insert.  

You can use CREATE TABLE ... AS SELECT ... FROM yourOwnFunctionReturningAResultSet(...)

Regards,

Thomas

[priyanka]

hi Thomson

  just 1 month back i have started using h2 database its really great

but iam facing problem with csv..As in document it has suggested the only way to insert bulk database is using csv

but when ever iam using csv...iam getting Io exception
CALL CSVREAD('test.csv');

IO Exception: "IOException reading test.csv"; SQL statement:
CALL CSVREAD('test.csv') [90028-168] 90028/90028 (Help)
          CALL CSVREAD('test.csv');

SELECT * FROM CSVREAD('test.csv');
IO Exception: "IOException reading test.csv"; SQL statement:
SELECT * FROM CSVREAD('test.csv') [90028-168] 90028/90028 (Help)

can you please suggest me correct procedure of using csv...and is csv really useful for bulk insert.

Thanks in advance