Export as Kettle Transformation XML Files

Export as Kettle transformation XML files

The KETTLE export type is useful if you want to use Pentaho Data Integrator (Kettle) to import data to PostgreSQL. With this type of export, Ora2Pg will generate one XML Kettle transformation file (.ktr) per table and add a line to manually execute the transformation in the output.sql file. For example:

ora2pg -c ora2pg.conf -t KETTLE -j 12 -a MYTABLE -o load_mydata.sh

will generate one file called 'HR.MYTABLE.ktr' and add a line to the output file (load_mydata.sh):

#!/bin/sh

KETTLE_TEMPLATE_PATH='.'

JAVAMAXMEM=4096 ./pan.sh -file $KETTLE_TEMPLATE_PATH/HR.MYTABLE.ktr -level Detailed

The -j 12 option will create a template with 12 processes to insert data into PostgreSQL. It is also possible to specify the number of parallel queries used to extract data from Oracle with the -J command line option as follows:

ora2pg -c ora2pg.conf -t KETTLE -J 4 -j 12 -a EMPLOYEES -o load_mydata.sh

This is only possible if there is a unique key defined on a numeric column or if you have defined the technical key to be used to split the query between cores in the DEFINED_PKEY configuration directive. For example:

DEFINED_PK      EMPLOYEES:employee_id

This will force the number of Oracle connection copies to 4 and define the SQL query as follows in the Kettle XML transformation file:

<sql>SELECT * FROM HR.EMPLOYEES WHERE ABS(MOD(employee_id,${Internal.Step.Unique.Count}))=${Internal.Step.Unique.Number}</sql>

The KETTLE export type requires that the Oracle and PostgreSQL DSN are defined. You can also activate the TRUNCATE_TABLE directive to force a truncation of the table before data import.

The KETTLE export type is an original work by Marc Cousin.