Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Replace a connection string in database query component with a variable?

Hi All,

I'm currently operating in an environment where I am going to be extracting ~25 tables from a source system hosted on Heroku. Due to how Heroku operates with its managed Postgres instances, they are able to change the database URL provided to our Heroku application. When this happens, I currently need to go through each of the query database table components and manually change each connection string.

My first attempt to solve this was by creating environment variables (one for development/test, and one for production) that hold the respective database URLs. Unfortunately, it appears that every time I replace the suggested "jdbc:postgresql://<host>/<database>" default string with ${DB_CONN_STRING}, it resets back to "jdbc:postgresql://<host>/<database>".

Does anyone have any advice or other workarounds to this? It would greatly help!

2 Community Answers

Matillion Agent  

Ian Funnell —

Hi Alex,

Yes, you can certainly paramterize the URL using variables, with ${variable_name} syntax. Here are some notes on how variables work.

  • Matillion always uses the variable’s default value for validation.
  • Default values are specific to an Environment. If you have created more than one Environment (e.g. dev/test/production), you will need to specify a default value for every Environment.
  • Variables can be updated at runtime, meaning that a component which failed validation may nevertheless still run successfully.
  • During development, it’s good practice to get the component working using hardcoded values. Once you have seen it working, you can then replace the hardcoded values with variables as required.

Note that updates you make to the value of a variable do not persist beyond the execution of the job that made the update. Here’s an example:

  • Job A runs, and includes a Python Script which calls context.updateVariable(..) to update the value of a variable.
  • Later in the execution, a component inside Job A runs. It sees the updated value.
  • Job B starts (maybe while job A is still running, or maybe afterwards)
  • All components inside job B see the default value of the variable.
  • Job B must make its own changes to the variable if necessary, using context.updateVariable(..) inside a Python script. Those changes will not be visible to job A if it’s still running.

Also please note that the only way to change the default value of a variable is by hand through the web user interface. Matillion does not have an API endpoint for updating the default value of a variable.

Best regards,
Ian


Alex Cano —

Hi Ian,

I appreciate you writing back. I've done the following:

1) Created an environment variable, ${CONNECTION_STRING}, that I populated w/ the connection string for that environment's database (dev for dev, prod for prod). I have ensured that using the values themselves, without variable replacement, result in successful connection.

2) Tried to place ${CONNECTION_STRING} in the "connection url" field in the database query component. This field ignores my input and resets back to the default value of "jdbc:postgresql://<host>/<database>".

Fortunately, it appears to be some kind of naming collision. I was able to name my variable ${MY_CONNECTION_STRING}, and it appeared to result in the behavior that I was expecting. Perhaps the variable name ${CONNECTION_STRING} is being used behind the scenes for this component somehow?

Either way, I found a workaround. Thanks!

Post Your Community Answer

To add an answer please login