ETL jobs

Permission: Jobs management

Groovy scripts can be used in ETL scripts so it's possible to write scripts to delete files on local disk (and other potentially dangerous tasks). Please be careful when writing scripts and with granting the Jobs management permission to users.

ETL engine contains several drivers for databases: Postgresql, MySQL, MSSQL, Oracle, DB2, Derby, H2, HSQLDB, Sybase, XSL. Not all JDBC drivers are included in Graphlytic.

ETL engine contains other drivers: CSV, Neo4j, Mail, Groovy, Log, Text, XPath and more. Using combination of drivers you can create many scenarios how to process or load data.

You can create ETL job by editing XML. Job can be planned to execute at defined date and time or to execute periodically. Job can also be executed immediately on demand.

See several examples of scenarios.

Create job

  1. Left menu - Jobs

  2. Click on create new job

  3. Fill the form:

    • name of job

    • if there is need to run job periodically enter "cron expression" into field "New trigger" and click on button "Add". If there is need to remove runner, select "X" icon. Example of cron expression:

      • 0 45 13 * * ? - starts every day at 13:45

      • 0 0/15 * * * ? - starts every 15 minutes

      • 0/30 * * * * ? - starts every 30 seconds

    • enter script text into area "script" (see example of ETL job in next paragraph)

    • confirm by pressing "Create job".

Example of ETL job

This ETL job only write some information into log file.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<description>Test log</description>
<connection id="logInfo" driver="log">
level=INFO
</connection>
<connection id="groovy" driver="script">language=groovy</connection>
<script connection-id="groovy">
etl.globals['sysStart'] = new Date().format("yyyy-MM-dd'T'HH:mm:ss")
</script>
<script connection-id="logInfo">
start: ${etl.globals['sysStart']}
</script>
</etl>

Cron expression

A cron expression is a string comprised of 6 or 7 fields separated by white space. Fields can contain any of the allowed values, along with various combinations of the allowed special characters for that field. The fields are as follows:

Field Name

Mandatory

Allowed Values

Allowed Special Characters

Seconds

YES

0-59

, - * /

Minutes

YES

0-59

, - * /

Hours

YES

0-23

, - * /

Day of month

YES

1-31

, - * ? / L W

Month

YES

1-12 or JAN-DEC

, - * /

Day of week

YES

1-7 or SUN-SAT

, - * ? / L #

Year

NO

empty, 1970-2099

, - * /

More examples can be found in the Quartz's library tutorial here: http://www.quartz-scheduler.org/documentation/quartz-2.3.0/tutorials/crontrigger.html

Manage Job

  1. Left menu - Jobs

  2. List of existing jobs is shown

  3. There are several icons next to the each job. You can execute several operations with job:

    • run - job is started immediately

    • view history of executions - view when was job started, when was finished and if there was an error during execution

    • update - you can change job details

    • delete - confirmation dialog is shown. Job is deleted after confirmation