Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: transformation database configured at runtime

  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Default transformation database configured at runtime

    Does anyone know how to dynamically swap the database connection at runtime?

    Example:
    I have 10 remote databases that are all identical to each other. I want to run the exact same transformation/job against all of them to load into a central data warehouse. The number or location of remote dbs may change, so they cannot be "hardcoded" steps, but rather, driven from configuration data in central db.

    I can see this working by pulling a variable into EVERY sql statement ("Table Input"), thus causing the sql client to open a connection to a remote db, but this is not an elegant solution, as it cannnot take advantage of connection sharing/pooling, or security. THIS seems like part of the answer, but the connection to use is still hardcoded by the individual steps ( "Table Input" ).

    Using Spoon 4.1.0.

    Thank you, and what a great tool this community has produced!
    Last edited by neal2; 01-18-2011 at 12:19 PM.

  2. #2
    Join Date
    Sep 2007
    Posts
    834

    Default

    In the transformation to be executed for every database define the connection with variables instead of fixed values, for ex. ${HOST}, ${DBNAME}, etc.

    Create a transformation that iterates over the list of connections (a list that includes host, database name, etc.). and copy the rows to results.
    Then in a subsequent job (to be executed for every input row) copy the row, set the variables ${HOST}, ${DBNAME}, etc., and call the transformation.

    BTW this very same example is explained in one of the recipes of the first chapter of the incoming Pentaho Data Integration 4 Cookbook.

  3. #3
    Join Date
    Jan 2011
    Posts
    3

    Default

    Thank you for your response. I see from your post, as well as the link I posted, that you're defining a variables for a connection. I understand this, and can do this part easily. BUT, I still do not see a way to actually use these variables in a transformation. Just because these variables are available does not mean the individual steps of a transformation know how to use them properly. In the example of a "Table Input", the connection is selected from a drop down - there is no possibility of inputing a variable here. If there was, that might work ( provided kettle could associate the variable with a shared connection ). As it stands, a "Table Input" is hardcoded against a particular connection, defined at configruation time, not a variable defined at runtime.

  4. #4
    Join Date
    Sep 2009
    Posts
    810

    Default

    Yap,

    I just remember that a similar question came up on IRC today and I've created a sample. The sample uses a postgres connection and the connection details are determined at runtime by another transformation. I attach it in case you want to have a look.

    Cheers

    Slawo
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2007
    Posts
    834

    Default

    The name of the connection is fixed, that is correct.
    It's in the window where you define the connection where you have to use the variables.
    Choose the 'Connection type' and 'Access', and then fill the 'Settings' with variable names.
    For example, instead of typing localhost, type ${HOST_NAME}.

  6. #6
    Join Date
    Jan 2011
    Posts
    3

    Default

    BINGO! You both hit it right on the head! The connection configuration itself is where the variables go, not someplace within the transformation. Thank you, thank you!

  7. #7
    Join Date
    Sep 2009
    Posts
    810

    Default

    uhm...yeah, Maria was explaining and I had a sample ready by sheer coincidence. We tend to gang up on new forum members here

    Cheers

    Slawo
    Last edited by slawomir.chodnicki; 01-18-2011 at 02:06 PM.

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.