Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Database Connections

  1. #1
    Join Date
    Jun 2017
    Posts
    29

    Unhappy Database Connections

    Hello guys!

    The problem:

    I have a job with 30 transformations each with a connection (all the same connection).
    Now i have to change the password of the connection user.
    I would not like to have to change all the passwords in each transformation, however, i think it will be necessary in this case.

    The doubt:
    Is there any way to avoid this in the future?

    Thanks!

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi Lucas,

    Use a variable - set up in your kettle.properties file. It is then available to all transformations.

    eg source_user_password=yourpassword

    In you connection input, specify the variable in the field - ${source_user_password}.

    I set up - host name, port, userid and password using this method. If things change - then you only have to update one place for the change to be effective.

    HTH
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3
    Join Date
    Jun 2017
    Posts
    29

    Default

    Fantastic, tnewman!

    It really works fine.
    Another doubt: Where is the file kettle.properties? Because i want to take this file to another pentaho folder in another machine. hehe

    Thanks a lot!

    Quote Originally Posted by tnewman View Post
    Hi Lucas,

    Use a variable - set up in your kettle.properties file. It is then available to all transformations.

    eg source_user_password=yourpassword

    In you connection input, specify the variable in the field - ${source_user_password}.

    I set up - host name, port, userid and password using this method. If things change - then you only have to update one place for the change to be effective.

    HTH

  4. #4
    Join Date
    Aug 2011
    Posts
    236

    Default

    Lucas,

    On my linux machine it is in the '.kettle' (maybe hidden) directory.

    Thanks
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    kettle.properties lives in ~/.kettle on Linux or %USERPROFILE%\.kettle on Windows.
    That's why some people prefer adding a simple transformation at the beginning of the jobs that sets the variables -- then it doesn't become per-user setup.

    Another couple of hints:
    1) You can set password variables to an obfuscated value with the utility "encr" which can be found in your PDI directory. PDI is smart enough to de-obfuscate a value that it finds that starts with Encrypted
    2) Use JNDI. While you can't control the visibility of the password, it lives in one place, and becomes a bit more like ODBC. It also makes porting your transformations (and Jobs) to PDI server a lot easier.

  6. #6
    Join Date
    Nov 2009
    Posts
    688

    Default

    You can also specify the place of the .kettle directory with the KETTLE_HOME variable. Declare that in a System or User variable.
    You can also change the spoon.bat file and point the KETTLE_HOME to a specific directory where the .kettle dirictory is situated

  7. #7
    Join Date
    Jun 2017
    Posts
    29

    Default

    Thanks tnewman, gutlez and johanhammink!

    All your tips will be helpful.
    I will put them into practice and find out which fits best with my scenario.

    Thank you again!

  8. #8
    Join Date
    Jun 2017
    Posts
    29

    Default

    Gutlez,

    Can you give me some example where i marked with red, please?

    Thanks!

    Quote Originally Posted by gutlez View Post
    kettle.properties lives in ~/.kettle on Linux or %USERPROFILE%\.kettle on Windows.
    That's why some people prefer adding a simple transformation at the beginning of the jobs that sets the variables -- then it doesn't become per-user setup.

    Another couple of hints:
    1) You can set password variables to an obfuscated value with the utility "encr" which can be found in your PDI directory. PDI is smart enough to de-obfuscate a value that it finds that starts with Encrypted
    2) Use JNDI. While you can't control the visibility of the password, it lives in one place, and becomes a bit more like ODBC. It also makes porting your transformations (and Jobs) to PDI server a lot easier.

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Not really able to give you an example.

    Basically:
    Job
    - Transform (Set DB Vars)
    - - Data Grid (All the values that are needed for the DB: Username, Hostname, Port, etc)
    - - Set Variables
    - Transform (Do work here)
    - - ....


    If you set the DBPassword to a variable ${DBPassword} and that variable has a value of "Encrypted ABCDEF12345789" then PDI will try to de-obfuscate the password before it tries to use it.
    If you go in to the PDI directory, there's a program called encr that you can run from the command line.
    Code:
    encr -kettle password
    will give a response of
    Code:
    Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde
    if your DB User's password is password, then setting the variable to the value above will work.

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.