ETL Pre-Actions

Pre-Actions in an ETL application are tasks executed before the main ETL process starts. They are crucial for setting up the environment, validating data, and preparing resources necessary for a successful ETL job execution.

Ignore Action Error #

If checked, the ETL application will continue execution even if an error occurs during the pre-action. Useful for non-critical actions or when errors can be handled downstream.

Ignore While Execution #

If checked, this action will be ignored while the ETL application is running, often used for debugging purposes or temporarily disabling pre-actions.

Add Action #

Use this option to add a pre-action.

Once added, fill out the pre-action configurations as explained further.

Actions #

Select the type of action to be performed. Either SQL queries or Stored Procedures can be executed as pre-actions.

Connection #

Select or create a connection to the appropriate database based on the type of action being performed:

• For SQL: Select or create a connection to the database containing the data that will be queried or updated using the SQL query as a pre-action during application runtime.

• For Stored Procedure/Function: Select or create a connection to the database where the stored procedure or function resides.

A connection name can be selected from the list if you have created and saved connection details for JDBC earlier. Or create one as explained in the topic - JDBC Connection →

SQL Action #

Option to execute SQL at source level during runtime.

This can include Data Manipulation Language (DML) statements like INSERT, UPDATE, DELETE, or Data Definition Language (DDL) statements like SELECT, etc.

You can also update JDBC connections dynamically that are being used in Gathr applications using the Update Connection Configuration option.

Query #

Provide a SQL query to be executed as a pre-action for the application.

Each line can contain one query. However, if the Update Connection Configuration field is enabled, enter a single SELECT query, as multiple queries are not supported with this option in a single pre-action section.

To provide multiple queries, you can add another pre-action using the Add Action option.

Update Connection Configuration #

Disabled by default. Enable this option to dynamically update a JDBC connection based on the results of the SQL query provided above.

Target Connection #

Select a JDBC connection to be dynamically updated.

Select Query Examples #

A pre-defined SELECT query can be used to update the target connection.

select host, port, databaseName, username, password from your_table_name

Replace your_table_name with the actual table name containing the updated connection details for the target connection.

If any connection parameter retrieved from the table is null or empty, the application will fail with an appropriate error message.

Example 1:

The query selects specific columns host, port, databaseName, username, and password from the table named database_update.

The query will return a result set containing the values of host, port, databaseName, username, and password.

Example 2:

Additional to example 1, the WHERE clause filters rows from the database_update table where the id column equals 1.

If there is exactly one row in the database_update table where id equals 1, the query will return the values of host, port, databaseName, username, and password from that row.

If there are no rows with id equal to 1, the query will return an empty result set.

Stored Procedure Action #

Option to invoke a stored procedure or function stored in a database.

To do so, an appropriate JDBC connection should be provided.

Further, as per the established connection the relevant Stored Procedure or Function that needs to be invoked should be selected from the drop-down menu.

Operation #

• If the action type is set to Stored Procedure, select the specific Stored Procedure to execute.

• If the action type is set to Function, select the specific Function to execute.