Scripts

Automated post processing & advanced metrics collection

You can use Scripts to execute java code or to invoke a stored procedure on your target data lake e.g. Redshift or Snowflake after every flow run. You can setup different script to run based on the status of the flow run.

To obtain the connection to the target data lake instance use db.getTargetDBConnection() and db.getSourceDBConnection() to get the connection to the source instance provided you use any relational datasource as the input for the flow.

Setup Script Page

1. Script Name

2. Script Status

3. Schedule Type

4. Script written in Java and/or Groovy

5. View Script Run History

Variables Available

The following are the variables available to the script

Variable	Description
flowCode	Flow code
outputEventType	Table name in the target DB
inputEventType	Table/Object name in the source system
inputType	Input Connector Type e.g. Oracle, MySQL
outputType	Output Connector Type e.g. Oracle, MySQL
logger	Log4j logger for debug purpose
db	An instance of FlowScript

Redshift Stored Procedure Exception Handling

When calling a stored procedure on redshift the connection would become stale when an exception occurs. If you want to execute another procedure or SQL from the catch block, use db.resetAndGetTargetDBConnection() to close the existing stale connection and get a new connection for perform additional calls.

Sample Script to invoke a Stored Procedure in Redshift

    try {
      Map result = SQL.createCall(db.getTargetDBConnection(), "sp_cp_1()")
              .execute();
    } catch (Exception e) {
      try {
        String msg = "Error-" + System.currentTimeMillis() + ": " + e.getMessage();
        // call resetAndGetTargetDBConnection instead of getTargetDBConnection,
        // as the connection is getting stale in case of exception
        SQL.createCall(db.resetAndGetTargetDBConnection(), "sp_cp_y(?)")
                .bind(1, msg)
                .registerOutParameter(1, java.sql.Types.VARCHAR).execute();
      } catch (Exception exp) {
        logger.error(exp);
      }
    }

Invoking REST API from Scripts

Apart from performing JDBC calls, you can use Unirest to make any REST web-service call from scripts

Map result = Unirest.post("https://example.com")
    .basicAuth("user", "password")
    .asObject(Mapord.class)
    .getBody();