Hi Phil,
>>> The bit I am having trouble with is the 'for each database'.
Basically, you want to have a mixed set of database parameters: some are
static and remain mostly the same (hostname, port), but some need to be
dynamic: I guess the database name, probably the username and password.
We plan to integrate with secret stores in a future release, which will
make this entire process easier. Until then, the approach described below
should work.
What you could do is set up a project and environment, with a default
environment configuration file (or set of configuration files, one per
database).
More information on how to create projects and environments can be found in
the docs [1].
Users can specify a project and environment by selecting them from the
dropdown menus in Hop Gui, or through the "-j" (project) and "-e" (flags)
in hop-run [2].
A setup that could work is:
1) create a project with an "incomplete" set of database connection
variables: the hostname, database name, port as mentioned earlier.
2) let users specify the database username and password to use for a
specific connection, e.g. as parameters to a workflow or pipeline or in
their own environment configuration file.
This personal environment file could be personal, or could follow a
convention, e.g. ${PROJECT_HOME}/config/user-configuration.json, so it is
transparent and the process is exactly the same for all users.
You don't want to keep this username password in your project's git
history, so that would need to be added to your ".gitignore".
Whether the username and password values are specified in an environment
config file or are passed as parameters, they will take a format like
"${PRM_USER}", "${PRM_PASSWORD}".
You can use these variables in your database connection, and let users
specify the correct values in runtime (either through that personal
environment configuration file or through parameters).
This approach will allow you to centrally manage the static or general
database connection parameters, while users will be responsible for their
own authentication parameters.
[1]
https://hop.apache.org/manual/latest/projects/projects-environments.html#_create_an_environment
[2] https://hop.apache.org//manual/latest/hop-run/index.html
Regards,
Bart
On Thu, Oct 26, 2023 at 5:53 AM Phillip Brown <[email protected]>
wrote:
> Hi all
>
> They say the best way to learn something new is to use it solve a
> specific problem. I am wanting to do that with Apache Hop, but am stuck
> on a particular part.
>
> Requirement:
>
> I am responsible for managing a number of application databases for our
> various business units. We have people who support those applications
> who periodically require the ability to update data in specific tables.
> To facilitate that I have implemented a system where they can log a
> request for update access into a central repository, and periodically
> that repository will be synced with the various databases. The access
> they are granted is (mostly) temporary, and expires after a number of
> days. The system to do the syncing currently uses a mix of shell and
> python, and works pretty well. Additionally, the passwords used in each
> of the target databases are different.
>
> the basic flow is:
>
> 1) get a list of databases which need to be synced (based on recent
> requests and/or expiry) from the repository
>
> 2) for each database, get a list of what should be in that database from
> the repository
>
> 3) then in the target database, remove anything that shouldn't be there,
> and add anything that isn't there
>
> The bit I am having trouble with is the 'for each database'. I expect I
> am going to have to use variables, and thought I might be able to use an
> environment per database, but I can't see how (or if) you can tell Hop
> to use a different environment once you've started a workflow or a
> pipeline. So somehow it will need to be all one environment, but I don't
> really want to have a squillion different variables.
>
> So, any guidance in what pattern to use in setting this up would be
> appreciated.
>
> Thanks and regards
>
> Phil Brown.
>
>
