Kettle 4.2.0 Stable: Table Input does full table read

[DMurray3]

Hi all,

I am working with a large census data set (+/- 14.9 millon rows by 103 columns of varibale data type columns) in MySql which I managed to upload and transform from a text file. The large table in MySql has no index.

I am currently trying to create a transformation to summarize/aggregate of the above large dataset/table with some 19 columns of data and one meassure, by doing a sort and then group by and some other transform steps. The problem I am encountering is that the initial Table Input Step is reading the full table on inception not showing me any advance on the "Excecution Results" pane; some 2 or three hours pass and then for some strange reason, the server connection is crashing.

I have also tried getting the DB to do the heavy work, by specifying the "sort" and "group by" in the "Table Input Step"; I get the same server connection crash after some 2 or 3 hours.

I have alternatively tried to create the summarization/aggregation via sql scritp directly in MySql: same problem.

I am working on a W/Ultimate 64bit, MySql 5.5.16 64bit as well; my box is a Core i7 with 8 Gb of RAM; I have modified the spoon.bat memory allocation to 2gb.

I originally was trying the above against a MS SQL Server 2008 R2; similar problems encountered.

Can anyone advise what configuration in Kettle or in MySQL I should be adjusting to allow the extensive process to run correctly.

Kind regards, DMurray3

[slawomir.chodnicki]

hey DMurray3,

if you're reading all the data you need to ask the DB to generate results in chunks instead of trying to do a full read, before returning results. The above sounds to me like the JDBC driver was accumulating all rows in memory, eventually exhausting available RAM. If that is the issue, you can fix it by trying to use a result streaming cursor (it is an option on the db connection dialog). If enabling that leads to strange errors, please replace the mysql driver jar from libext/JDBC with the one from PDI 4.1. The upgraded 5.x driver causes some trouble with some versions of MySQL servers.

HTH

Cheers
Slawo

[gutlez]

The large table in MySql has no index.

This will cause you problems...
Even if you are only returning a few of the rows, you have to do a full table scan in order to do it.

Add some indexes, and then pare down the amount of data that you are trying to return, (eg. Select Count(...) from table where Col3=n group by col3) even if it's a lot less than what you want to have. Build out your aggregated data, and you should have a lot more success.

[MattCasters]

Google gives 1.710.000 results for a query with MySQL JDBC "out of memory".

The first time I ever tried to do anything meaningful with a big table I ran out of memory with Kettle on MySQL.
It then took me around 2 years to find a proper work-around in the form of cursor emulation or result streaming.
It's hardly documented, sort of a hack since MySQL lacks proper support for cursors in the engine itself.

I don't think indexes influence that process at all although cranking back the MySQL JDBC driver to 3.1 (from PDI 4.1 or 4.2.2) might solve the issue.

[DMurray3]

Hi Slawo,

You hit the problem right on the button.... Using the streaming cursor on my "mysql" coonection worked like a charm, with no need to change jdbc connector!!.

I followed your suggestions and was able to avoid the continuous crashes, and got my transform to process the full +14million rows in 29 minutes...which brought smiles and tears...

Will the same approach work independent of the db I use (let say with MS SQl Server 2008)?

Again many thanks for your interest and the excellent asist.

Kind regards, DMurray

hey DMurray3,

if you're reading all the data you need to ask the DB to generate results in chunks instead of trying to do a full read, before returning results. The above sounds to me like the JDBC driver was accumulating all rows in memory, eventually exhausting available RAM. If that is the issue, you can fix it by trying to use a result streaming cursor (it is an option on the db connection dialog). If enabling that leads to strange errors, please replace the mysql driver jar from libext/JDBC with the one from PDI 4.1. The upgraded 5.x driver causes some trouble with some versions of MySQL servers.

HTH

Cheers
Slawo