You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@hop.apache.org by Phillip Brown <ph...@gmail.com> on 2023/10/26 03:53:41 UTC
variables, environments, databases, OH MY!
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.
Re: variables, environments, databases, OH MY!
Posted by Thad Guidry <th...@gmail.com>.
You might also consider alternative technology such as Ansible and its
playbooks and not use Hop (or only the portion you need). If you need a
gui for users than Rundeck could be helpful additionally where it can run
sets of playbooks based on user form input, etc.
Think about the entire automation needed and where. Updating tables is a
simple procedure that many tools besides Hop can perform. It might be best
to make the user experience more effortless at the expense of some
automation learning investment.
Best of luck!
-Thad
Re: variables, environments, databases, OH MY!
Posted by ph...@gmail.com.
Hi Deigo/Bart
Thanks for the replies
I am a big proponent of 'single source of truth'. The business application in this case is common across the businesses/databases, and in most respects they *are* separate environments, with almost no other scenario where I would need to be able to access across the different databases. My plan is to be able to do things like creating a pipeline, (get some business data and email it out, for example) which will satisfy the same requirement for all the businesses, so then I can just schedule 'hop-run' telling it which environment/database to run it in, and do that for one/some/all of the databases on a case-by-case basis. Then I have this where I need to be able to launch a workflow which will do the specific pipeline for each database, but it will depend on the first part of the workflow which databases it needs to happen in, and the information each database will need comes from the central repository.
Ideally, I'd like to only set things up once in a way that can satisfy both sets requirements.
I was hoping I could do it in a way where it is all in one workflow, but looks like I may have to break it up so that I have one controlling workflow, that launches a workflow/pipeline for each the individual databases via a call-out to another 'hop-run' with the database-specific environment. I can still have an environment per database, but each of those environments will have a shared config that points back to the central repository.
Cheers
Phil
On 26/10/23 3:36 pm, Diego Mainou <di...@bizcubed.com.au> wrote:
> Hi Phillip,
>
> This is what I would do
>
> Scenario 1
>
> Assumptions:
>
> * You have set a project folder with all your projects, environments,
> database connections, etc.
> * You have dset either a server or a container with hop
>
> Job:
>
> * Create a job that has the list of projects/environments
> * Trigger a container or curl to the server that performs the required
> jobs for each of the project/ environment combinations.
>
>
> i.e. you are running a loop on a multi environment setup.
>
> Scenario 2
>
> Assumptions:
>
> * You have setup some form of keystore
> * setup an account that can access the required keys
>
> Job
>
> * job has the list of projects/environments.
> * Retrieve the list of keys for the environment combination
> * Execute a loop that performs the required actions
>
> i.e. this is just a simple loop that manages the security in a way where
> you don't keep things out of the key vault
>
> Diego
>
>
> <https://www.bizcubed.com.au> Diego Mainou
> Product Manager
> M. +61 415 152 091
> E. diego.mainou@bizcubed.com.au <ma...@bizcubed.com.au>
>
> www.bizcubed.com.au <https://www.bizcubed.com.au>
> <https://www.bizcubed.com.au>
>
>
> ------------------------------------------------------------------------
> *From: *"Phillip Brown" <ph...@gmail.com>
> *To: *"users" <us...@hop.apache.org>
> *Sent: *Thursday, 26 October, 2023 2:53:41 PM
> *Subject: *variables, environments, databases, OH MY!
>
> 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.
>
>
>
Re: variables, environments, databases, OH MY!
Posted by Diego Mainou <di...@bizcubed.com.au>.
Hi Phillip,
This is what I would do
Scenario 1
Assumptions:
* You have set a project folder with all your projects, environments, database connections, etc.
* You have dset either a server or a container with hop
Job:
* Create a job that has the list of projects/environments
* Trigger a container or curl to the server that performs the required jobs for each of the project/ environment combinations.
i.e. you are running a loop on a multi environment setup.
Scenario 2
Assumptions:
* You have setup some form of keystore
* setup an account that can access the required keys
Job
* job has the list of projects/environments.
* Retrieve the list of keys for the environment combination
* Execute a loop that performs the required actions
i.e. this is just a simple loop that manages the security in a way where you don't keep things out of the key vault
Diego
[ https://www.bizcubed.com.au/ |
] Diego Mainou
Product Manager
M. +61 415 152 091
E. [ mailto:diego.mainou@bizcubed.com.au | diego.mainou@bizcubed.com.au ]
[ https://www.bizcubed.com.au/ | www.bizcubed.com.au ] [ https://www.bizcubed.com.au/ |
]
From: "Phillip Brown" <ph...@gmail.com>
To: "users" <us...@hop.apache.org>
Sent: Thursday, 26 October, 2023 2:53:41 PM
Subject: variables, environments, databases, OH MY!
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.
Re: variables, environments, databases, OH MY!
Posted by Bart Maertens <ba...@know.bi>.
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 <ph...@gmail.com>
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.
>
>