Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Text File Input - Running queries on Text File Input

  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Default Text File Input - Running queries on Text File Input

    Is it possible to run queries on a text file? If so how would you do that?

    I understand that I could import it into a table and run SQL command there, but curious if it is directly possible to do so on the text file

    Thanks.

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

    Default

    Hi.
    It depends on what you want to achieve using a query.
    You can apply a filter, sort, string operations to a text file using different steps.

    Mick

  3. #3
    Join Date
    Jul 2010
    Posts
    10

    Default

    All data in the text file is from one table. I want to run multiple FULL outer joins on the same table to be able to pivot data across several optional categories. The source database (Db2 AS400) does not support FULL outer joins.

    So my options are:
    1. Pull data in a text file and run SQL if possible directly on it.
    2. Pull it into our staging env. DB2 LUW 97 database and run queries over there.
    3. Join two categories at a time using multiple FULL outer Join commands in Kettle which will require several sorts also.

    I am thinking that text file might be the fastest.

    Other info about the SQL:
    I am trying to PIVOT using MAX(CASE WHEN pivotcol = 1 THEN c1 END) AS P1 since DB2 does not have a PIVOT Statement like SQL server.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It would be fine to run 2 (or more) queries on the AS400 and then do the join in Kettle.
    The most expensive operation is the sorting of the data so make sure to do that on the AS400.
    The "Merge Join" step can do the full outer join for you and it would be plenty fast too. "Certain" databases don't join too well and then Kettle might be faster actually :-)

    There isn't really a need to create the intermediary text file to do the join.

    Matt

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.