Populate generated data to a database

Overview

This Guide is build on the Advanced Tutorial Data Generation. It shows how to populate data that was generated by a data generation task into a database by using a task stage hook.

Prerequisites

A configured data generation task that already generates data and writes SQL files to a file‑sink session.
A workflow that contains this data generation task.
A database connection that can be used to execute the generated SQL statements.

Data Generation Workflow Advanced Tutorial

This example from the tutorial (Data generation) shows how to run a data generation task template from a workflow and pass the resulting file-sink session ID to a hook.

Groovy

def taskRun = TaskTemplateRunner()
   .taskTemplate('Generate Tables')
   .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
   .run()

println taskRun

def sessionId = taskRun['file-sink.sessionId']

HookRunner()
   .hook('Print Generated Files')
   .parameter('sessionId', sessionId)
   .run()

The workflow executes the data generation task template Generate Tables. The result of the task execution is stored in the variable taskRun.

The data generation task writes its generated files to a file-sink session. The session ID of this file-sink session is available in the task result under the key file-sink.sessionId.

The workflow reads this value and stores it in the variable sessionId. The sessionId variable is then passed to the Print Generated Files hook, which prints the generated files during the workflow execution. The hook can use this session ID to access the files that were generated by the previous data generation task.

Steps

Step 1: Create a new Task Stage Hook

Create a new Task Stage Hook named Execute Generated SQL Files. Insert the following code:

Groovy

import groovy.json.JsonSlurper

def sessionId = taskStageHook.sessionId

def listUrl = "${workflow.fileSinkBaseUrl}/session/${sessionId}/list"
println "Calling URL for file list information: ${listUrl}"

def url         = new URL(listUrl)
def connection  = url.openConnection()
def responseCode = connection.responseCode

println "Response Code: ${responseCode}"

def statement = taskStageHook.connection.jdbcConnection.createStatement();
if (responseCode == 200) {
    def responseText = connection.inputStream.text
    println "Response text ${responseText}"

    statement.execute("SET search_path TO ${taskStageHook.schema}")

    def json = new JsonSlurper().parseText(responseText)
    json.each {
        def file = it.name

        if (file.endsWith(".sql")) {
            def downloadUrl = "${workflow.fileSinkBaseUrl}/session/${sessionId}/download/${file}"
            println "Downloading file: ${downloadUrl}"

            def fileUrl         = new URL(downloadUrl)
            def fileConnection  = fileUrl.openConnection()
            def content         = fileConnection.inputStream.text

            content.split(";").each { rawStmt ->
                def stmt = rawStmt.trim()
                if (stmt) {
                    println "Executing update: $stmt"
                    try {
                        statement.execute(stmt)
                    }catch (Exception e){
                      println "Failed to execute statements"
                      println " Schema: ${taskStageHook.schema}"
                      println " Statement: $stmt"
                      println " Fehlermeldung DB: ${e.message}"

                      throw new RuntimeException("Faild to execute SQL-Statement ${e.message}", e)
                    }
                }
            }
        }
    }
} else {
    println "Error: ${connection.errorStream.text}"
}

statement.close()

This Task Stage Hook uses four Parameters. Create them as follows:

Display Name	Description	Data Type	Default Value	Possible Values
sessionId		STRING		0 values
connection		CONNECTION		0 values
schema		STRING		0 values
fileSinkBaseUrl		STRING		0 values

Display Name

Description

Data Type

Default Value

Possible Values

sessionId

STRING

0 values

connection

CONNECTION

0 values

schema

STRING

0 values

fileSinkBaseUrl

STRING

0 values

sessionId – identifies the file‑sink session whose files should be processed.
connection – A database connection on which the generated SQL statements should be executed.
fileSinkBaseUrl – base URL of the file‑sink service (http://file-sink:8080).
schema – database schema that should be set as current schema before running the SQL.

Execution Flow Of The Task Stage Hook

This Task Stage Hook reads the SQL files which the Generator Task writes in the file-sink server. The SQL statements will be executed on the specified database connection. It also includes basic error handling for SQL execution.

The execution flow is as follows:

The hook gets the sessionId from the workflow which reads the sessionId from the previously executed data generation task.

Using sessionId and workflow.fileSinkBaseUrl, the hook builds the URL to list all files of the session. It logs the URL, opens an HTTP connection, and reads the HTTP response code from the file‑sink service.

Next, the hook obtains a JDBC connection from the configured connection parameter and creates a statement. This statement is used to send SQL commands to the target database.

The hook handles the HTTP response from the file-sink service as follows:

In the error case, no SQL is executed, but the statement is still closed at the end.
If the response is successful (200), the hook reads the response body as text and logs it for debugging purposes. It then uses JsonSlurper to parse the JSON into a Groovy data structure. The JSON is expected to be a list in which each element represents a file and provides at least a name property.

Before executing any application-specific SQL, the hook sets the database schema. This ensures that unqualified table names in the SQL statements are resolved against the schema provided by the schema parameter.

The hook then iterates over the parsed JSON list. For each file entry, it reads the file name from it.name and checks whether the file name ends with .sql. Other files, such as meta.json, that may exist in the same session are ignored.

For each .sql file, the hook constructs a download URL and logs it for traceability. It opens another HTTP connection, downloads the full file content, and reads it as text. The content is expected to contain one or more SQL statements separated by semicolons.

The hook splits the SQL content into individual statement fragments. Each fragment is trimmed to remove leading and trailing whitespace. Empty fragments, for example those caused by trailing semicolons or blank lines, are skipped.

Each non-empty SQL statement is logged before execution and then executed using the JDBC statement inside a try block.

If an exception occurs during execution, the hook:

logs a generic failure message,
logs the active schema,
logs the exact SQL statement that failed,
logs the database error message, and
throws a new RuntimeException that wraps the original exception.

This behavior ensures that enough detail is available in the logs to troubleshoot SQL errors. Because the exception is rethrown, the surrounding Workflow can detect that the hook failed.

After all files and statements have been processed, or after an HTTP error has been logged, the hook closes the JDBC statement to release database resources. If execution aborts with a RuntimeException, the statement is closed only if execution reaches the final line.

Step 2: Create Custom Parameter

If you have not already done so, create a custom parameter as follows:

Display Name: Current Schema
Variable: currentSchema
ParameterType: String

Step 3: Adding the Task Stage Hook to the Workflow

The next step is to add the Task Stage Hook to the Workflow. Go to the Workflow Run generation tables and insert the Code for the Hook as shown below.

Groovy

def taskRun = TaskTemplateRunner()
   .taskTemplate('Generate Tables')
   .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
   .run()

println "TASKRUN: ${taskRun}"

def sessionId = taskRun['file-sink.sessionId']

HookRunner()
   .hook('Print Generated Files')
   .parameter('sessionId', sessionId)
   .run()

/* NEW HOOK **************************************************************/
HookRunner()
   .hook('Execute Generated SQL Files')
   .parameter('sessionId', sessionId)
   .parameter('connection', 'Sample testing database as super user')
   .parameter('fileSinkBaseUrl', workflow.fileSinkBaseUrl)
   .parameter('schema', workflow.currentSchema)
   .run();

Step 4: Attach the Custom Parameter

Attach the Custom Parameter Current Schema to the workflow and choose your Target schema.

Step 5: Run the Workflow

NOTE: The workflow does not create tables, schemas, or columns. These must already exist before the workflow can be executed.

If the table, schema, and columns already exist, but some columns have constraints such as NOT NULL and no value is generated for them, the workflow will fail.

Benefits

This approach combines data generation and data population.

The data generation task remains responsible for creating the SQL files. The task stage hook is responsible for reading these files from the file-sink session and executing their SQL statements on the configured database connection.

This has the following practical benefits:

The generated SQL files can be inspected during and after task execution.
The target database connection and schema can be configured through hook and workflow parameters.
The same generated files can be used for troubleshooting, because the hook logs the processed files and the SQL statement that failed.
The generated data is written directly to the database as part of the same run. No manual import or additional execution step is required.

Conclusion

This workflow reflects the current development state for using generated data in a database population scenario. It provides a first solution that allows generated data to be used already. In future versions, the entire scenario will be improved and simplified.

The workflow first runs the data generation task template. It then reads the file-sink.sessionId from the task result and passes this value to the task stage hook. The hook uses the sessionId to retrieve the generated SQL files from the file-sink service, filters for .sql files, and executes their statements on the configured database connection.

After completing these steps, the workflow can generate data and populate it into an existing database schema in one run.

Before using this setup in a regular test process, verify that:

the target schema exists,
the required tables and columns exist,
all required columns receive valid values,
the configured database user has the required permissions, and
the generated SQL statements are compatible with the target database.