Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Table Input Step with SQL query not returning results after migration to Linux

  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Default Table Input Step with SQL query not returning results after migration to Linux

    Hi guys
    I wonder if you could help me please
    I have latest version Pentaho Data Integration 5.0.1 installed on windows 7

    I have a Table Input step which is running a moderately complex MySQL query, it has a number of joins.
    The Step is within a transformation which takes these results and sends them on to a Text file output

    Everything ran happily on my local machine.
    MySQL was installed on this machine too

    As soon as the database schema was migrated to MySQL Linux instance on a company central server. The Table step is now not bringing back any results.
    The Transformation runs but you can see nothing but '0' zeros everywhere in the step metrics.
    The connection setup on the Table Input step is definitely against the new Linux instance

    When I press the preview button nothing returns.
    I tried a simpler SQL query and results were returned from the Linux instance.

    When I cut and paste this complex query in the sql window of MY SQL Workbench 6.0CE session against the schema on the linux server the query does return results as expected, so I know the query is correct. It take time about 114 seconds.

    I am fairly new to Pentaho DI, its an amazing tool...so Im probably not doing things as efficiently as possible.
    I have a number of transformations that are setup like this, running within a job producing reports from fairly complex SQL statements

    I was wondering if any of the following reasons was the problem..

    I was hoping the experts out there could help me understand if this is a memory related issue down to the fact i'm running my jobs on PDI locally installed on windows, but connecting to remote linux server ?
    Should i set this up differently ?
    Do I need to get my linux adminstrator to allocate more memory to the mysql instance ?
    Can i update PDI memory usage somehow so it cope with bringing back results from complex SQL queries running on remote linux seerver ?
    Any help would be gratefully received...Thank you for your time

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    If you are not seeing any errors when you run the step, then it is most likely that the step is connecting to a database, running the query, and the database is returning 0 rows. Here are some things to check:

    1. Make *sure* that you are connecting to the database that you think you are connecting to. To confirm this, copy your existing transformation, simplify the copy so it just runs a "select * from <table>" on a small table, and confirm that you get the results you expect.
    2. Does your MySQL-on-Linux login have all of the privileges needed to view the data that you expect to see? A missing select privilege or something like that could cause 0 rows. (I'm not a MySQL expert, but often PDI problems are due to database configuration issues.)
    3. If you have any parameters in your Table Input step, are they being set properly?

    This is probably not a memory issue.

  3. #3
    Join Date
    Jan 2014
    Posts
    2

    Default

    Thank you so much for your reply Rob
    In response to your questions
    1. I can definitely confirm I am connecting to the database that I think I connecting to.
    I have tried select * from table on a small table and everything works


    2. I have confirmed that MySQL Linux login has all the privileges to view the data.
    Plus I did ran the exact same SQL query within MySQL Workbench against the Linux instance and results were returned as expected. But it does take about 115 seconds to run


    3. There are no parameters set in the Table Input Step.


    I know this is fairly complex SQL Query with joins, so I was wondering is there log i can switch on, that can at least guide me as to what PDI is doing as it sends the query off to the remote linux server.
    The fact when I try to run 'Preview Data' it returns no rows almost immediately suggest to me there may be some sort of memory requirement that query may be exceeding causing PDI to completely fall over...so if anybody has any further suggestions they will be greatfully

  4. #4
    Join Date
    Jul 2009
    Posts
    476

    Default

    Try this:

    1. Make a copy of your transformation.
    2. Remove all steps from the copy except the Table Input step.
    3. Run the simplified transformation in Spoon, with Log Level set to Debugging.
    4. In the Execution Results window beneath the transformation, under the Logging tab, look for a line like this:

    2014/04/09 16:31:36 - customers input.0 - SQL query : select * from public.businesses

    Check the SQL to see if it is what it should be.

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.