CloverETL examples

To download example package go to download page.

We assume that examples are unzipped to the CloverETL home directory. When executing examples from command line you should be in the examples/ExtExamples subdirectory (eg. /home/user/cloverETL/examples/ExtExamples).

(Older examples are not divided into four subdirectories and you should run them within their examples directory. Thus, in the commands the groups of caracters (../../) should be replaced by ../ only.)

Remember that (on Windows) you should use semicolons instead of colons in commands shown below.

When experimenting with database, presented example graphs expect that there exists database table “EMPLOYEE” with following structure:

create table EMPLOYEE (
  EMP_NO INTEGER,
  FIRST_NAME VARCHAR(80),
  LAST_NAME VARCHAR(80),
  PHONE_EXT VARCHAR(80),
  HIRE_DATE DATE,
  DEPT_NO VARCHAR(80),
  JOB_CODE VARCHAR(80),
  JOB_GRADE NUMERIC,
  JOB_COUNTRY VARCHAR(80),
  SALARY NUMERIC,
  FULL_NAME VARCHAR(80)
)

If you wish to experiment with unloading data from this table, you can use populate_employee_table.sql file (supplied with these examples) to insert some records into the table. There is also Oracle specific file populate_employee_table.ora.

DBInputTable component can be used for extracting data from database. It allows a SQL query to be specified and run against database. The result set produced by DB is then sent by the component to output port - and gets propagated to other processing nodes. It uses JDBC to communicate with DB.
You may wonder why to use CloverETL for processing data which is already in DB. It should be faster to perform any processing within DB itself. Well, it is not always true.
DBUnload component (as all other DB related components) needs to know which JDBC driver to use, what is the DB connection string and under what user name it should connect to database. These parameters can be provided directly to component (one by one) or special config file can be created from which component is able to read required info.
Following is an example of config file which can be used to connect to Borland Interbase DB:

dbDriver=interbase.interclient.Driver
dbURL=jdbc:interbase://localhost/home/projects/interbase/examples/database/employee.gdb
user=SYSDBA
password=masterkey

I hope that the parameters (their names) are self-explanatory. If not, read some JDBC related documentation.

In this example, the DBInputTable component connects to database and unloads all records from employee table. It then stores the records in employees.list.out file.

Executing:
java -cp "../../lib/cloveretl.engine.jar:../../lib/commons-logging.jar:../../lib/log4j-1.2.12.jar:../../lib/javolution.jar" org.jetel.main.runGraph -plugins ../../plugins graph/graphDBUnload.grf

DBOutputTable component does the opposite to DBInputTable, it populates database table with data processed by CloverETL framework. Similarly to DBInputTable, it requires some JDBC related parameters to be supplied. In order to be able to run this example, you have to create a DB table with the name EMPLOYEE_TMP and structure similar to EMPLOYEE table presented above.

Executing:
java -cp "../../lib/cloveretl.engine.jar:../../lib/commons-logging.jar:../../lib/log4j-1.2.12.jar:../../lib/javolution.jar" org.jetel.main.runGraph -plugins ../../plugins graph/graphDBLoad.grf

DBJoin component joins records from input port with records from database. Each pair of matching records is sent to transformation (can be written in four ways : external class, java source embedded in graph definition, summary transformation or in Transformation Language) to get output record. In this example for records from flat file are searched records from database where field lname on input has the same value as field last_name in database records.

Executing:
Prepare transform class:
javac -cp "../../cloveretl.engine.jar" javaExamples/TransformForDBjoin.java

Execute graph:
java -cp "../../lib/cloveretl.engine.jar:../../lib/commons-logging.jar:../../lib/log4j-1.2.12.jar:../../lib/javolution.jar:trans:../../lib/janino.jar" org.jetel.main.runGraph -plugins ../../plugins graph/graphDBJoin.grf

Graph graphDBLookup.grf has similar functionality as preceding graph, but you can see usage of Database Lookup Table.

Executing:
java -cp "../../lib/cloveretl.engine.jar:../../lib/commons-logging.jar:../../lib/log4j-1.2.12.jar:../../lib/javolution.jar:trans:../../lib/janino.jar" org.jetel.main.runGraph -plugins ../../plugins graph/graphDBLookup.grf