Friday, November 19, 2010

Running pentaho transformation stored in DB reposiotry from Java

Maven Configuration required for kettle

<properties>
        <pentaho.kettle.version>4.0.1-GA</pentaho.kettle.version>
</properties>


<dependency>
            <groupId>pentaho.kettle</groupId>
            <artifactId>kettle-core</artifactId>
            <version>${pentaho.kettle.version}</version>
        </dependency>
        <dependency>
            <groupId>pentaho.kettle</groupId>
            <artifactId>kettle-db</artifactId>
            <version>${pentaho.kettle.version}</version>
        </dependency>
        <dependency>
            <groupId>pentaho.kettle</groupId>
            <artifactId>kettle-engine</artifactId>
            <version>${pentaho.kettle.version}</version>
        </dependency>
        <dependency>
            <groupId>pentaho.kettle</groupId>
            <artifactId>kettle-ui-swt</artifactId>
            <version>${pentaho.kettle.version}</version>
        </dependency>
        <dependency>
            <groupId>pentaho-library</groupId>
            <artifactId>libformula</artifactId>
            <version>1.1.7</version>
            </dependency>
        <dependency>
            <groupId>org.codehaus.janino</groupId>
            <artifactId>janino</artifactId>
            <version>2.5.16</version>
        </dependency>
        <dependency>
            <groupId>rhino</groupId>
            <artifactId>js</artifactId>
            <version>1.7R2</version>
        </dependency>
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>1.4.1</version>
        </dependency>
  <!-- Assuming the tranformation is stored in MySql database-->
      <dependency>
            <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.17</version>
      </dependency>


Steps for running the Kettle transformation from Java code

Assumption the transformation are stored in the database repository

  • Initialize the kettle environment

                     KettleEnvironment.init();

  • Create a instance of kettle database repository
                     KettleDatabaseRepository repository = new KettleDatabaseRepository();
  • Create a instance of database meta

                    DatabaseMeta databaseMeta = new DatabaseMeta(
      type, type, "", host, databaseName, port, userName, password );
                 
                      type --> Data base type like "MySql"  
                      host --> database server name
                      databaseName --> Schema name
                      port --> Database port e.g 3306 for MySql
                      userName, Password --> To connect to the given database 

  • Create a instance of kettle repository  meta
                      KettleDatabaseRepositoryMeta kettleDatabaseMeta = new KettleDatabaseRepositoryMeta(
      repositoryId, repositoryName, "Transformation description", databaseMeta );

                     repositoryId --> Repository Id
                     repositoryName --> Repository name
                     databaseMeta --> DatabaseMeta defined above
  • Initialize the repository

                     repository.init( kettleDatabaseMeta );
  • Connect to the repository default user name password is "admin", "admin" 

                     repository.connect( repUserName, repPassword );
  • Load the root directory

                     RepositoryDirectoryInterface directory = repository.loadRepositoryDirectoryTree();
  • Read the saved transformation  meta data using the directory and the transformation name
                     TransMeta transformationMeta = repository.loadTransformation(
        transformationName, directory, null, true, null ) ;
  • Create a new transformation object using the transformation meta

                     Trans trans = new Trans( transformationMeta );

  • Set any transformation parameters 

                      trans.setParameterValue( parameterName, parameterValue);

  • Execute the transformation

                     trans.execute( null ); // You can pass arguments instead of null.
  • Wait till the transformation finish

                     trans.waitUntilFinished();
  • Check for any errors during the execution                    
                     if ( trans.getErrors() > 0 ) {                    
                               //Errors running transformation.
                     }else{
                               //Transformation run successfully.
                     }