Search code examples
postgresqlpentahopentaho-data-integration

Pentaho ETL set schema in DB connection for postgres db

Pentaho ETL. , how do I specify schema name in DB connection? there is no place to type schema names. I need it as I am running ETL against multiple schemas located on one db. enter image description here

I need to collect data from multiple schemas in one DB, and I need to know how to specify them in DB connector so later I can replace them with variables so I can change connections dynamically.

Solution

  • Using the currentSchema variable added to the connection string suggested by @Bergi, you can add this option in the Options tab of the database connection:

    Options tab in Database Connection

    But I don't know if you are going to be able to use variables afterwards to change this dynamically. In my version (8.2) of PDI you can't, maybe in more modern versions you are going to be able, or they have added metadata injection capabilities to the DB connection (although I doubt that).

    You might be able to create different connections for each schema and pass the connection name using a variable or using metadata injection, that could be another solution.