Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Can we use multiple database connections in a single step?

  1. #1
    Join Date
    Jun 2010
    Posts
    114

    Question Can we use multiple database connections in a single step?

    Is there a way to use multiple database connections in a single step?

    For example, if we have a query like
    SELECT COUNT(DISTINCT S_ID) FROM STUDENT WHERE S_ID NOT IN (SELECT DISTINCT T_ID FROM TEACHER)
    Suppose, STUDENT and TEACHER are tables from different databases, Can we write a query using both tables in a single (table input) step?
    (As the table input step takes only one db connection, I am wondering if there is a way)

    Thanks,
    Om

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    The answer is no, (mostly)

    It might work if your DB system supports mixing schemas/db_names and you classify the table names with the schemas/db_names. I vaguely remember that something like that was supported by Postgres or MySQL...

    This obviously works only if both DBs are hosted by the same Server/Instance...

    The query would be rewritten to

    SELECT COUNT(DISTINCT S_ID) FROM DBNAME1.STUDENT WHERE S_ID NOT IN (SELECT DISTINCT T_ID FROM DBNAME2.TEACHER)

    Cheers

    Slawo
    Last edited by slawomir.chodnicki; 07-14-2010 at 03:05 PM.

  3. #3
    Join Date
    Oct 2007
    Posts
    107

    Default

    I don't think you can do this but a workaround would be to have this within a view or stored procedure inside one of your database and simply call that view/stored procedure from PDI; that would work.

  4. #4
    Join Date
    Sep 2007
    Posts
    834

    Default

    no, you can't do that.

    One way for doing this is with a Table Input for getting the s_id from Student. Then, a database lookup for looking up the ids in Teacher (using a different connection).
    then a filter for keeping the ids of interest,
    etc.

  5. #5
    Join Date
    Oct 2007
    Posts
    107

    Default

    I guess it depends on the database you're using. I know that with MySQL and Microsoft SQL Server you can do this within a view without any problem so performing a SELECT * FROM YOURVIEW would actually return what you're looking for.

  6. #6
    Join Date
    Jun 2010
    Posts
    114

    Default

    Quote Originally Posted by CHamel View Post
    I guess it depends on the database you're using. I know that with MySQL and Microsoft SQL Server you can do this within a view without any problem so performing a SELECT * FROM YOURVIEW would actually return what you're looking for.
    We are currently using DB2.

  7. #7
    Join Date
    Oct 2007
    Posts
    107

    Default

    Hmmm, you would have to try, not sure about this one.

    Otherwise, Maria's solution is the way to go.

  8. #8
    Join Date
    Jun 2010
    Posts
    114

    Lightbulb

    Quote Originally Posted by CHamel View Post
    Hmmm, you would have to try, not sure about this one.

    Otherwise, Maria's solution is the way to go.
    Thanks Chris & Maria
    Yep, the alternate, as mentioned by Maria would be using different step for each connection.

    I am a newbie and am not sure if many people go through this kind of scenario. But, if so, may be adding a feature where we could use more than one connection in a step, would help the transformation from not turning out into a complex one.

  9. #9
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    well, I guess you are expecting too much here. The input step executes SQL against your DB. If your DB cannot handle cross db/schema selects, you're out of luck on this one. I cannot think of any sane way to implement this. If you often find that you'd like to reference data from different DBs within a single SQL query you should redesign your DB or bring the data together in a single DB first. That's what Kettle is good for

    Cheers

    Slawo

  10. #10

    Default

    This is a common scenario when you build a DW, You may need to pull data from multiple heterogeneous data sources. The normal way to go about this situation is to use a look up or use a Join. PDI Comes with different type of Join and LookUp steps. You need to decide which step suits you the best

    ~JC
    www.di-solution.com

  11. #11
    Join Date
    Jun 2010
    Posts
    114

    Default

    Thanks Slawomir,JC

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.