Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Loop over fields in a MySQL table to generate csv files

  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Default Loop over fields in a MySQL table to generate csv files

    Hi all,

    I am developing a transformation with Kettle that aims at outputing multiple csv tables from one single MySQL table.
    The goal is to create a DSPL archive for the Google Public Data Explorer. The csv tables are called slices and have to be precomputed for the Public Data Explorer to work.

    Basically, what I need to do is to loop over every field in my MySQL table (that looks like id, Age, Gender, Country, Population) and ouptput aggregated csv tables of the following kind:
    • SELECT Age, Gender, SUM(Population) GROUP BY Age, Gender
    • SELECT Age, Country, SUM(Population) GROUP BY Age, Country
    • SELECT Gender, Country, SUM(Population) GROUP BY Gender, Country
    And also
    • SELECT Age, SUM(Population) GROUP BY Age
    • SELECT Country, SUM(Population) GROUP BY Country
    • SELECT Gender, SUM(Population) GROUP BY Gender
    I don't want to hardcode it because I have more fields and I want to apply that same transformation to multiple tables.

    Do you think this kind of operation is doable with Kettle and how would you go about doing this? Your expertise would save me a lot of design time.
    Thank you!

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    I think you can achieve this using parameters in your sql statement.

    Mick

  3. #3
    Join Date
    Mar 2012
    Posts
    6

    Default

    Thank you for answering!
    Do you mean doing something like this :


    Code:
    public static void executeStatement(Connection con) {
       try {
          String SQL = "SELECT LastName, FirstName FROM Person.Contact WHERE LastName = ?";
          PreparedStatement pstmt = con.prepareStatement(SQL);
          pstmt.setString(1, "Smith");
          ResultSet rs = pstmt.executeQuery();
    
    
          while (rs.next()) {
             System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
          }
          rs.close();
          pstmt.close();
       }
       catch (Exception e) {
          e.printStackTrace();
       }
    }

    http://msdn.microsoft.com/en-us/libr...=sql.100).aspx

    I guess I need to use a script that will call these MySQL queries.
    Would you have a pentaho tutorial that you would recommand for that task?

  4. #4
    Join Date
    Mar 2012
    Posts
    6

    Default

    I found this resource that was really useful to me : http://type-exit.org/adventures-with...-a-k-a-kettle/

Tags for this Thread

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.