Hitachi Vantara Pentaho Community Forums
Results 1 to 19 of 19

Thread: What is the standard setup for PROD/DEV/TEST Environment

  1. #1

    Default What is the standard setup for PROD/DEV/TEST Environment

    Wanting to get feedback on how people here setup their PDI environment when only using PDI. We currently have 1 machine dedicated for a single instance of PDI and 3 repositories for PROD/DEV/TEST in an Oracle back-end. This has caused issues when taking a job/transformation from one environment to the other because the way connection strings were named. We want to redesign our PDI environment and would like to know how others are handling this.

    thanks
    Daniel Bogesdorfer
    University of Pittsburgh

  2. #2
    Join Date
    May 2014
    Posts
    358

    Default

    In my current project we use JDBC connections where the URLs, ports, usernames and passwords all come from variables defined in kettle.properties.
    Personally I prefer to use JNDI connections, though.

  3. #3

    Default

    Hello Lukfi

    Thank you for the info.
    How could the JNDI connections be defined?
    I tried in the app server (context.xml and web.xml) but spoon is unable to read them! and we dont want to use jdbc.properties file
    can you help?
    Thanks

  4. #4

    Default

    Quote Originally Posted by Lukfi View Post
    In my current project we use JDBC connections where the URLs, ports, usernames and passwords all come from variables defined in kettle.properties.
    Personally I prefer to use JNDI connections, though.
    I don't think that you can use JNDI unless your running the server portions of pentaho (e.g. you need a java server?) We are running PDI as a standalone piece of their software on a Windows x64 box.

  5. #5
    Join Date
    May 2014
    Posts
    358

    Default

    In Spoon and Report Designer (for local development), you define JNDI connections in the simple-jndi/jdbc.properties file - for Spoon this file is in the PDI installation directory, for PRD it is under userhome/.pentaho.
    On the BI server, you can either define the connections on the level of the application server, or via the GUI as a JDBC connection. Then if you have reports and kettle jobs with JNDI connections running on the server, they will use these. Thanks to this, you may have three servers each pointing to a different database, but they may use the same Kettle repository and you deploy the same report files onto all of them.

  6. #6

    Default

    Lukfi - So if we are using jdbc connections we need to change over to jndi?

  7. #7

    Default

    Quote Originally Posted by dbogey View Post
    I don't think that you can use JNDI unless your running the server portions of pentaho (e.g. you need a java server?) We are running PDI as a standalone piece of their software on a Windows x64 box.
    Hi everyone,

    what do u mean "running the server portions of pentaho (e.g. you need a java server?)" ?!

    Is it possible to define the JNDI connections in Tomcat (DI server) and use them in spoon? we did the same for the BA server and it worked
    PS: The JNDI connections can be defined in simple-jndi/jdbc.properties, but we do not prefer to use it

    Thanks
    Last edited by PenBI; 03-30-2016 at 10:07 AM.

  8. #8

    Default

    Quote Originally Posted by PenBI View Post
    H

    what do u mean "running the server portions of pentaho (e.g. you need a java server?)" ?!


    Thanks
    We are not running the di server.

  9. #9

    Default

    Quote Originally Posted by Lukfi View Post
    In Spoon and Report Designer (for local development), you define JNDI connections in the simple-jndi/jdbc.properties file - for Spoon this file is in the PDI installation directory, for PRD it is under userhome/.pentaho.
    On the BI server, you can either define the connections on the level of the application server, or via the GUI as a JDBC connection. Then if you have reports and kettle jobs with JNDI connections running on the server, they will use these. Thanks to this, you may have three servers each pointing to a different database, but they may use the same Kettle repository and you deploy the same report files onto all of them.
    Hi Lukfi

    I defined the JNDI connections on the app server (DI server) but the spoon is unable to read them. i did them same on the BA server and it worked fine
    where might be the problem, can u help plz?

    Thanks

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

    Default

    Quote Originally Posted by dbogey View Post
    So if we are using jdbc connections we need to change over to jndi?
    No - you can continue to use JDBC and use variables in the kettle.properties file if you prefer.

    JNDI makes it a little easier, and some of the users on the board prefer the separation between tools by setting it up.

    JNDI is effectively a wrapper around JDBC that allows you to connect to a DB Connection Name, rather than specifying the DB Connection information. This also means that when you change your infrastructure passwords (You do do this regularly, right?), you only have to change it in the JNDI configuration once, rather than in each of Transforms.

    You need to build a distinct KETTLE_HOME for each environment that you want to have.
    In it, you need to build a jdbc.properties file that defines the JDBC Connection information for each name.
    KEY STEP: Name all your JNDI connections the same way. You do not want to have PROD_OLTP and DEV_OLTP in your jdbc.properties files. In your DEV jdbc.properties file, you'll have an entry OLTP and in your PROD jdbc.properties file, you'll also have a OLTP entry.

    Put the Dev and Test directories (with their jdbc.properties files) on any machine that will be used for development work. CAUTION: Passwords are in plain-text in these files! Do NOT use the same password for Production as you do for Dev or Test.

    You do NOT need to have a Java App server to use JNDI.

    For PenBI: You still have to put the jdbc.properties files on every development workstation, even with the JNDI connection defined on the server in the context.xml file.

  11. #11

    Default

    Quote Originally Posted by gutlez View Post

    For PenBI: You still have to put the jdbc.properties files on every development workstation, even with the JNDI connection defined on the server in the context.xml file.
    HI Gutlez,
    Thanks for the explanation.
    In our case we do not want to have any passwords in a plain-text. so we build a CustomObjectFactory to encrypt the passwords and used JNDI connction on the app srv, now on the BA srv it worked verywell without using the jdbc.properties file but not on the DI serv (spoon).
    Spoon is unable to read any JNDI connections (even the predefined one in the jdbc.properties file or context.xml, except the Sampledata)

    Do u have any advices, no passwords in clear text?
    Is there any configurations in spoon that should be made so it can use JNDI connections (defined on the app srv)?

    Thank you

  12. #12
    Join Date
    May 2014
    Posts
    358

    Default

    Spoon does not read connections which you define on the application server. They have to be defined in Spoon's own jdbc.properties file in its installation folder.

  13. #13
    Join Date
    Jul 2009
    Posts
    476

    Default

    There are two scripts in the PDI root folder called Encr.bat and encr.sh, for Windows and Linux/OSX, respectively. You can run them to encrypt a password that you don't want to store in plain text in kettle.properties. For example:

    Code:
    $ ./encr.sh -kettle thisIsASecretPassword
    Encrypted 746869734bcdd9fcad09d597909b18bd63c99dbdde
    Copy the result, including the "Encrypted " prefix, to kettle.properties, as the parameter value. Note that this encryption could be reversed by a knowledgeable attacker, but it's better than leaving the password in plain text.

    I don't know how, or if, this works with JNDI, but it does work with a plain kettle.properties file.

  14. #14

    Default jdbc.properties file ??

    Trying to wrap my head around this and would like to use this. A few questions. I'm getting the error:

    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occured while trying to connect to the database
    Required subelement 'driver'



    Where does the jdbc.properties file supposed to reside.

    Here's my

    APP26/type=javax.sql.DataSource
    APP26/driver=oracle.jdbc.driver.OracleDriver
    APP26/url=jdbcracle:thin:@APP26.UBER.COM:1555ds
    APP26/user=xxx
    APP26/password=xxx

    Quote Originally Posted by gutlez View Post
    No - you can continue to use JDBC and use variables in the kettle.properties file if you prefer.

    JNDI makes it a little easier, and some of the users on the board prefer the separation between tools by setting it up.

    JNDI is effectively a wrapper around JDBC that allows you to connect to a DB Connection Name, rather than specifying the DB Connection information. This also means that when you change your infrastructure passwords (You do do this regularly, right?), you only have to change it in the JNDI configuration once, rather than in each of Transforms.

    You need to build a distinct KETTLE_HOME for each environment that you want to have.
    In it, you need to build a jdbc.properties file that defines the JDBC Connection information for each name.
    KEY STEP: Name all your JNDI connections the same way. You do not want to have PROD_OLTP and DEV_OLTP in your jdbc.properties files. In your DEV jdbc.properties file, you'll have an entry OLTP and in your PROD jdbc.properties file, you'll also have a OLTP entry.

    Put the Dev and Test directories (with their jdbc.properties files) on any machine that will be used for development work. CAUTION: Passwords are in plain-text in these files! Do NOT use the same password for Production as you do for Dev or Test.

    You do NOT need to have a Java App server to use JNDI.

    For PenBI: You still have to put the jdbc.properties files on every development workstation, even with the JNDI connection defined on the server in the context.xml file.

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

    Default

    You should be able to put it in the KETTLE_HOME directory.
    You'll want a subdirectory called simple-jndi which contains jdbc.properties

    (Note: This is from memory - I haven't set this up in a long time... I've been using basic JDBC)

  16. #16
    Join Date
    May 2014
    Posts
    358

    Default

    The jdbc.properties does not reside in KETTLE_HOME, but in the *installation directory* (e.g. /opt/pentaho/data-integration/simple-jndi/jdbc.properties).

  17. #17

    Default

    Actually got JNDI to work on version 6. We are way behind on our versions (4x) and are going to 6x.

    Thanks for everyone's help on this!

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

    Default

    Quote Originally Posted by Lukfi View Post
    The jdbc.properties does not reside in KETTLE_HOME, but in the *installation directory* (e.g. /opt/pentaho/data-integration/simple-jndi/jdbc.properties).
    Actually it resides in the KETTLE_JNDI_HOME directory, which can be specified on the command line, allowing multiple JNDI files on a single server.

  19. #19
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by dbogey View Post
    Wanting to get feedback on how people here setup their PDI environment when only using PDI. We currently have 1 machine dedicated for a single instance of PDI and 3 repositories for PROD/DEV/TEST in an Oracle back-end. This has caused issues when taking a job/transformation from one environment to the other because the way connection strings were named. We want to redesign our PDI environment and would like to know how others are handling this.

    thanks
    Daniel Bogesdorfer
    University of Pittsburgh

    Hi,

    I know there have been many answers here but juste wanted to share my own configuration, as it works quite well:

    First note on JNDI:
    - JNDI provides a way to lookup java objects from some "store" with a logical name (the JNDI name). In case of database connections, we lookup objects of type javax.sql.Datasource from the store.
    - this "store" is named a JNDI Context. So somewhere in the application code, when you ask for a connections with JNDI name "jdbc/mySource", there is something like
    Context myContext = new InitialeContext() <--- get instance of the "store"
    Datasource myDatasource = myContext.lookup("jdbc/mySource").
    And then the application (Kitchen/Carte/Spoon/BA server/DI server) can use the Datasource to do great stuff. Moreover, note that this "lookup" cannot be done "remotely": the objects can be accessed only from the inside of the JVM housing the Context.

    But (there's a but) you need some thing to first create and populate this store (JNDI Context)! One must understand what creates this Context, and how, to understand how to configure it. There is merely 2 cases:
    1. your application is inside a web server: like the BA/DI server web application are in a Tomcat container
    -> In this case, the (Tomcat) container creates the JNDI Context and populates it using some configuration files when it starts. For Tomcat, the file context.xml is used to define the ressource to populate the JNDI Context. So this is file is to be used for configuration, and generally the names for JDBC ressource is "jdbc/myRessource".

    2. your application is stand alone (Spoon, Kitchen, Carte (not exactly) ). In this case you need a third party library to create the Context and populate it. For Pentaho, this library is simple-jndi (https://code.google.com/archive/p/os...impleJNDI.wiki , https://github.com/hen/osjava/tree/master/simple-jndi (actually pentaho use a fork of this project). So when Spoon starts, it call simple-jndi to build a JNDI Context. Then JNDI search into KETTLE_JNDI_HOME folder and parse every .properties/.xml files to populate the JNDI Context. The name of the file is important, since it is used to create the "url" for the JNDI name. If your file is named "myFile.properties", then the ressources defined in the file will be referred to "myFile/myRessource" as JNDI name. So the file HAS to be named jdbc.properties for the ressource to be named like "jdbc/myRessource", like on the server side.

    Sooooooo, the configuration is quite different on the server side than on the "client side" (or standalone side).

    Well, knowing that, here is how I configure my pentaho softs while having DEV/TEST/PROD environnements on server side, each having its own DB repository:
    1. you need a specific configuration for the server side for each environnement
    2. you need a specific configuration for client side, ALSO for each environnement (if you want to connect with Spoon to your DEV/TEST/PROD di-servers/slave carte servers!).
    3. Use JNDI for DB Connections. Every JNDI resource have the same name on each environnement! Like "jdbc/myOLTPSource1" NOT like "jdbc/myOLTPSource_dev".
    4. Use kettle.properties file to declare environnement variables for urls (like web services, FTP etc.), directories or path to other config files (like DATA=c:/mydatas) which will be used in PDI every time you need to refer to some directories. This file is environnement specific!
    5. For FTP/Mail or other stuffs needing authentification, dont put authentification variables in kettle.properties since these variable are set as system variable and accessible to the overall JVM (you can see them in JMX)! Put that in a credentials.properties file, and when you need these variables, read the file in PDI (job entry set variable-->form properties file). But in kettle.properties, put there the path to this credentials.properties file. Like "credentials.path=/my/secured/path/to/credentials.properties" and in your job use ${credentials.path} to load all its variables. These would be Kettle variable and accessible only to the job.
    -> this way, in TEST/QA/PROD environnement, your system operator can write and put this file in a safe place only accessible by the tomcat/admin user.
    -> since this is still kettle variable, you can even use enc.sh to obfuscate the passwords.

    So, on server side you have 3 physical different environnement (DEV server, TEST server, PROD server), so the configuration is quite easy since the configuration exists only once on each server.

    But, on client side (I mean your PC), if you want to have your DEV environnement configuration AND your TEST environnement configuration AND your PROD configuration (for browsing the repository, launching some remote jobs, scheduling on DI-Server etc.), you'll need to maintain many configurations on the same PC.
    What I did:

    1. Installation structure:
    /pentaho
    --/installation
    ---/5.4.0.1
    ----/data-integration
    (here the PDI application for version 5.4.0.1)
    ---/6.0.1
    ----/data-integration
    (here the PDI application for version 6.0.1)
    --/configuration
    ---/dev
    ----/launchers
    -----Launcher-Spoon.bat
    -----Launcher-Kitchen.bat
    -----Launcher-Carte.bat
    ----/.kettle
    -----kettle.properties
    -----repository.properties
    -----credentials.properties
    ----/simple-jndi
    -----jdbc.properties

    ---/test
    ( like dev sub structure)

    ---/prod
    (etc....)

    Here the files
    -----Launcher-Spoon.bat
    -----Launcher-Kitchen.bat
    -----Launcher-Carte.bat
    are merely wrapper files that:
    1. define environnement variables for the specific configuration (KETTLE_HOME, KETTLE_JNDI_HOME, version of pentaho to use)
    2. call the original Spoon.bat, Kitchen.bat with this configuration.

    Like that, you have specific configuration for each environnement, and can also use different PDI version on each environnement (for upgrades of pentaho life cycle!).

    The last tricky part is if you develop custom java libraries used in PDI: then you also have to change the class path in the spoon launcher on each environnement (since you might have one version of you library in DEV, and another version in TEST).

    This might be weird to have also the configuration for TEST and PROD on your PC, but since there is no web client for just explore repository, launch stuff, view logs other than Spoon, we needed them on our PC too.

    Final remark on encrypting password for Simple-jndi:
    - you can implement the interface Converter (https://github.com/hen/osjava/blob/m...Converter.java) to deal with encrypted properties in jdbc.properties. I'v done that wiring the jasypt EncryptedProperties into simple-jndi. So, in the Launcher-Spoon.bat, it first ask for my "encryption password" which is passed to spoon as variable. Then I can get this variable in my converter and use it to decrypt the jndi passwords on the fly.

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.