Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Pass large resultset between processes in xaction

  1. #1

    Question Pass large resultset between processes in xaction

    I am creating a prototype and looking for advice on how best to implement.

    I have an XAction (using 1.5.3 version) that runs a Relational Query and creates a resultset.

    The next process in the XAction is a custom class I wrote to ftp the results to an internal FTP server. (Using apache's FTPClient)

    I am concerned about memory. I noticed the documentation says there is a 'Keep connection alive' option. However I do not see that in the 1.5.3 release. I do see "Live resultset" and "in-memory resultset". I have selected live resultset.

    What is the datatype of the resultset? In my custom class what method do I use to get it: getInputStringValue, getInputValue, getInputStream? Ideally I want the live scrollable resultset so I can stream it to the FTP server without running out of memory.

    Unfortunately when using getInputStream the variable is null even though I know there is data in the resultset.

    any help is greatly appreciated.

    Brit

  2. #2

    Red face Pentaho SQLResultSet?

    It appears the resultset is passed as a pentaho class

    org.pentaho.data.connection.sql.SQLResultSet

    Are there any JavaDocs on these classes or an example on how to use it?

    It looks like some methods resemble a java ResultSet but return Object arrays instead of booleans.

    How should I be getting the column names and values from this object?

  3. #3
    Join Date
    May 2007
    Posts
    13

    Default

    I'm pretty new to Pentaho, but you might check svn: svn://source.pentaho.org/svnroot/pentaho/trunk/server/pentaho/src/org/pentaho/data/connection/sql/SQLResultSet.java

    Also, I've had success pulling column names in javascript using:

    var rsmd = resultset.getMetaData();
    var colHeaders = rsmd.getColumnHeaders();
    var colCount = rsmd.getColumnCount();
    var rowCount = rsCDRs.getRowCount();

    // and interrogating colHeaders[0][i].toString() where i is the column number from 0 to colCount - 1

  4. #4

    Default

    That does help me get the column names - thanks. Unfortunately i need to know the column data types as well. The Pentaho MetaData class returns an Object[][] but the only data is column name. So I have to either know all of the column information before hand (not good for dynamic queries) or somehow inspect and trap errors to determine datatype.

  5. #5

    Default Out of Memory Error

    Ran a query returning 1.8 million rows. The CPU stayed very low but memory grew by a 100MB every 10 minutes. I am connecting to a Oracle 10G RAC using the 'Live Resultset option'.

    I am taking the results and FTPing to a central file store. It looks like potentially there is a memory leak because of the nearly constant slow growth in memory. or it was just holding all of the records in memory and not releasing anything.

    This is still pre-release software so I bring it up here to hopefully have it addressed.

  6. #6

    Default Live Resultset is working

    This looks like it is working with the 'Live Resultset' option. I will do some more testing. Stepping through the code has helped quite a bit.

    Seems to be some undocumented features.

    For instance you can add the following

    <live><![CDATA[true]]></live>

    in the <component-definition> element in your action sequence and get the live resultset feature. This is important because not all of the design screens show it as an option (e.g. MQL editor).

    I was able to process about 2 million rows without a memory issue.

    Brit

  7. #7

    Default Live resultset postpones memory consumption

    Just a follow up to the previous posts. The 'live' resultset option postpones the storing of the data in memory but it still ultimately does because the type of resultset is scrollable.

    The number of records you can process depends on the amount of data retrieved and the available memory allocated to the jvm.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.