Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Managing Database connections

  1. #1
    Join Date
    Jan 2011
    Posts
    21

    Default Managing Database connections

    I have noticed that people seem to recommend not using the repository and just sticking with flat files. Is there a way to save a few db connections so I don't have to enter them every time I start a new transform?

    Is there a way to easily switch a transform from one database connection to another? At least if my connections were saved it would be a little easier to just open the database steps and select a different connection.

    The Book Pentaho 3.2 Data Integration says you can "share" your database connection so it will be available in future transforms but I can't get this to work and wonder if you have to be using the repository to make this work which would make sense.

    I prefer my transforms saved as files but don't like having to edit my transforms so heavily just to switch to a different database, like from dev to prod.

  2. #2
    Join Date
    Nov 2008
    Posts
    143

    Default

    You can always create a connection based on a jndi data source.
    After that, all you gotta do is change the connection definition in the datasource file. Then, all steps that use this connection, will start using the database you configured.
    That's how we do it here.

    To share a connection, you need to use the other tab (not design). Find the connection you mean to share, click with the right mouse button and share it!

  3. #3

    Default

    To not enter the connections over and over again you have to share them (go into your transformation, change to View-Tab, select the connection, right mouse click, share connection). This will create a file shared.xml in your KETTLE_HOME directory, which contains shared objects (you may share other objects than connections).

    You will soon run into the problem that your development environment has different settings than the production environment. So you have to make the shared connections based on your environment. Meaning the connection name remains the same, but the settings are different.

    The following setups provide solutions to this problem:
    * Use JNDI configuration (jdbc.properties) as already mentioned by renatopb. Drawback - the location where the JNDI file is located is "fixed". You may change the location - but the shell scripts kitchen/pan/spoon do not provide standard means to set the directory. So you have to change those scripts and provide an additional variable.
    * Provide a variable in kettle.properties (example: SHARED_OBJECTS_FILE=...) and set the shared object file in each job/transformation using this variable. So you can switch between different settings by changing the variable in the kettle.properties file.
    * You switch between different shared.xml by settings KETTLE_HOME
    * You change all settings in shared.xml (with the exception of the connection name) to variables and set those variables in kettle.properties. (example: <server>${my.host}</server>). --> add the variable name in the kettle.properties file: my.host=127.0.0.1

  4. #4
    Join Date
    Feb 2009
    Posts
    296

    Default

    I just use variables for that and put them into the .kettle/kettle.properties files.
    You will have to use the same database software in all your environments (as the variable can't change the connection type from MySQL to Oracle) but that is usually no problem at all.

    Just name your variables wisely. I use a scheme like this:
    com.mycompany.mydepartment.projectname.sourcedatabse.schema
    com.mycompany.mydepartment.projectname.sourcedatabse.user
    com.mycompany.mydepartment.projectname.sourcedatabse.password

    Thus I can have many projects with many databases in the same file.
    On your production system you would just have the neccessary variables for the jobs you've deployed.
    Fabian,
    doing ETL with his hands bound on his back

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.