Connection Keep-Alive for SQL Temp Table

CloverDX Platform
1

I am writing to a SQL Temp Table in Redshift as advised by AWS here: http://docs.aws.amazon.com/redshift/lat … bles-.html

Issue is that I need to keep the Connection alive after I create the temp table and continue with the data load from S3 and Update to the source table from the temp table. If I close the Connection the temp table gets deleted. How should I design this in Clover to keep the Connection alive through these operations?

2

Hi Jesse,
in CloverETL, there is indeed a way how to achieve what you described in your update. By default, each thread gets its own connection instance, thus avoiding issues when multiple components read/write from/to a DB using the same connection definition. However, this behavior can be changed by setting the Thread-Safe Connection check box to FALSE in the connection definition (Advanced tab). As a result, multiple components will share a single connection instance in the graph run. Let me demonstrate with the following example:

Based on your update, let me break the scenario down into these stages of the graph run:

  • Creation of the temp table (by using DBExecute)

  • Loading data from S3 into the temp table (by using DBOutputTable, among other components of course)

  • Inserting the content of the temp table into a persistent table (by using DBExecute)

If the Thread-Safe Connection checkbox was set to TRUE, CloverETL would create a new connection instance for the step #1 and would close it when the step #1 finishes. As a result, the SQL temp table would get deleted after step #1 and the graph would error out as there would not be any table to write to in the step #2.
If the Thread-Safe Connection checkbox was set to FALSE, CloverETL would open up a single connection instance that would not get closed until the step #3 finishes.
Note: setting the Thread-Safe Connection checkbox to FALSE is generally not recommended due to potential data conflicts and deadlocks. However, it might be beneficial in your particular case. Suggested is to assign each component (that is using the connection definition) with a different phase in order to prevent concurrent actions from being performed.
Regards,

3

Perfect! Thanks Vladimir, works as expected.