JDBC Hook#
The jdbc
hook can be used to execute an arbitrary SQL statement at certain important lifecycle events during the project
execution of Flowman. This feature can be used to inform downstream system whenever a job has been finished by
setting semaphore variables or calling stored procedures.
Example#
job:
main:
hooks:
- kind: jdbc
# Embed a JDBC connection to use
connection:
kind: jdbc
driver: "com.mysql.cj.jdbc.Driver"
url: "jdbc:mysql://mysql-01/flowman"
username: "flowman"
password: "secret"
# Specify SQL statements to execute
jobStart: "INSERT INTO jobs VALUES('$project', '$job', '$phase', 'RUNNING')"
jobSuccess: "INSERT INTO jobs VALUES('$project', '$job', '$phase', '$status')"
targetStart: "INSERT INTO targets VALUES('$project', '$target', '$phase', 'RUNNING')"
targetSuccess: "INSERT INTO targets VALUES('$project', '$target', '$phase', '$status')"
Fields#
kind
(mandatory) (type: string):jdbc
connection
(mandatory) (type: string or connection): Either a reference (via name) or a directly embedded JDBC connection to be used to talk with the target database where the SQL commands are to be executed.jobStart
(optional) (type: string): SQL statement which should be executed when a job is started.jobFinish
(optional) (type: string): SQL statement which should be executed when a job has been finished, either successful or not.jobSuccess
(optional) (type: string): SQL statement which should be executed when a job has been successfully finished.jobSkip
(optional) (type: string): SQL statement which should be executed when a job is skipped.jobFailure
(optional) (type: string): SQL statement which should be executed when a job has failed.targetStart
(optional) (type: string): SQL statement which should be executed when a target is started.targetFinish
(optional) (type: string): SQL statement which should be executed when a target has been finished, either successful or not.targetSuccess
(optional) (type: string): SQL statement which should be executed when a target has been successfully finished.targetSkip
(optional) (type: string): SQL statement which should be executed when a target is skipped.targetFailure
(optional) (type: string): SQL statement which should be executed when a target has failed.
Variables#
In most scenarios, one wants to use environment variables in the SQL statements, for example to pass the job name or execution status. This is well-supported by Flowman. In addition to the normal environment, the following variables can be used:
project
- The name of the projectversion
- The version of the projectnamespace
- The name of the namespacejob
- The name of the jobtarget
- The name of the target (can only be used in target specific SQL statements)category
- The category of the entity which is being processed. Can belifecycle
,job
ortarget
kind
- The kind of the entity which is being processed.name
- The name of the entity which is being processed.phase
- The execution phase (VALIDATE
,CREATE
,BUILD
,VERIFY
,TRUNCATE
orDESTROY
)status
- The execution status (UNKNOWN
,RUNNING
,SUCCESS
,SUCCESS_WITH_ERRORS
,FAILED
,ABORTED
orSKIPPED
). Note that the execution status is only available at the end of the execution of a job or target.