Content Metadata UDJC step (using Apache Tika)

I recently stumbled across the Apache Tika project, which is a content analysis toolkit that offers great capabilities such as extracting metadata from various documents.  Depending on the document type, various kinds of metadata are available.  Some examples of metadata include MIME type, last modified date, author, publisher, etc.

I think (more) content analysis would be a great capability to add the Pentaho suite (especially Pentaho Data Integration), so I set out to write a quick UDJC step using Tika, followed by a sample transformation to extract document metadata using that step:

The first thing I noticed when I started writing the UDJC code to interface with Tika is that most of the useful code for recognizing document types and outputting to various formats is buried as private in a class called TikaCLI.  It appears the current state of Tika is such that it is meant to be used as a command-line tool that can be extended by adding your own content types, output formats, etc.  However, for this test I just wanted to be able to use Tika programmatically from my code.  Since Tika is licensed under Apache 2.0, I simply grabbed the sections of code I needed from TikaCLI and pasted them into my UDJC step.

The actual UDJC step processing basically does the following:

1. Reads in a filename or URL and converts it (if necessary) to a URL
2. Calls Tika methods to extract metadata from the document at the URL
3. For each metadata item (a property/value pair), create a new row and add the property and value
4. If Tika throws any errors and the step is connected to a error-handling step, send the error row(s)

I ran the sample transformation on my Downloads directory, here is a snippet of the output:

If you know ahead of time which metadata properties you want, you can use a Row Denormaliser step to have the properties become field names, and their values be the values in those fields.  This helps reduce the amount of output, since the denormalizer will output one row per document, whereas the UDJC step outputs one row per metadata property per document.  For my example transformation (see above), I chose the "Content-Type" property to denormalise.  Here is the output snippet corresponding to the same run as above:

Tika does a lot more than just metadata extraction, it can extract text from document formats such as Microsoft Word, PDF, etc. and it can even guess the language (English, French, e.g.) of the content.  Adding these features to PDI would be a great thing, and if I ever get the time, I will create a proper "Analyze Content" step, using as many of Tika's features as I can pack in :)  We could even integrate the Automatic Documentation Output functionality by adding content recognizers and such for PDI artifacts like jobs and transformations.

The sample transformation is on GitHub here.  As always, I welcome your questions, comments, and suggestions. If you try this out, let me know how it works for you. Cheers!

Pentaho Data Integration 4.4 and Hadoop 1.0.3

While working with a few new Hadoop-based technologies (blog posts to come later), the need arose to get Pentaho Data Integration (PDI) and its Big Data plugin (source available on GitHub) working with an Apache Hadoop 1.0.3 cluster.  Currently, PDI 4.4 only supports the following distributions (and any distributions compatible with them):

• Apache Hadoop 0.20.x (hadoop-20)
• Cloudera CDH3u4 (cdh3u4)
• Cloudera CDH4 (cdh4)
• MapR (mapr)

The values in parentheses in the list above are the folder names under the Big Data plugin's "hadoop-configurations", each of which contains JARs and other resources needed to run PDI against a particular distribution.  To select a distribution for PDI to use, you edit the plugin.properties file in the Big Data plugin's root folder and set the "active.hadoop.configuration" property to one of the folder names above.  The default setting is for Apache Hadoop 0.20.x:

active.hadoop.configuration=hadoop-20

Apache Hadoop 1.0.3 is not compatible with the Apache Hadoop 0.20.x line, and thus PDI doesn't work with 1.0.3 out-of-the-box.  So I set out to find a way to make that happen.

First, I simply copied the hadoop-20 folder to a "hadoop-103" folder in the same directory (pentaho-big-data-plugin/hadoop-configurations/).  Then I replaced the following JARs in the client/ subfolder with the versions from the Apache Hadoop 1.0.3 distribution:

commons-codec-<version>.jar
hadoop-core-<version>.jar

and I added the following JAR from the Hadoop 1.0.3 distribution to the client/ subfolder as well:

commons-configuration-<version>.jar

Then I changed the property in plugins.properties to point to my new folder:

active.hadoop.configuration=hadoop-103

Then I started PDI and was able to use steps like Hadoop Copy Files and Pentaho MapReduce (see the Wiki for How-Tos).

NOTE: I didn't try to get all functionality working or tested.  Specifically, I didn't try anything related to Hive, HBase, Sqoop, or Oozie.  For Hive, I'm hoping the PDI client will work against any Hive server running on an Apache Hadoop 0.20.x cluster, or any compatible configuration.  If I test any of these Hadoop technologies, I will update this blog post.

If you try this procedure (for 1.0.3, 1.0.x, or any other Hadoop distribution), let me know if it works for you, especially if you had to do anything I haven't listed here :)  Cheers!

New PDI/Kettle project structure

In case you haven't heard, the Kettle project in Subversion has been restructured to be cleaner and to use Apache Ivy for dependency management.  This has been a long time coming, and PDI/Kettle is now more consistent with other Pentaho projects.  The "cut-over" from the old project to the new is scheduled to occur on Monday, January 14, 2013.

If you currently have changes in a working copy of Kettle trunk, you should not commit them into the new structure as it has changed.  For example, all the Kettle modules' source code used to reside in folders named src-<module_name>, such as src-core or src-db.  The modules have been reorganized such that you can check out and build individual modules if you choose.  So now each module has its own folder under the root, such as core/ and db/.  Inside these folders are src/ folders, which contain the same files and package structure as the old Kettle project.  So the files that used to be in src-core/ are now in core/src.

Other structural changes may impact your working copy as well. For example, the old "ui/" folder is now located at "assembly/package-res/", because "ui" is a Kettle module so the "ui" folder now contains the contents of "src-ui/".  The Ant build scripts have been updated to reflect this, and there is now a "create-dot-classpath" Ant target that will generate a ".classpath" and "<project_name>.launch" file to get you up and going in Eclipse.

For more information, consult the README.txt file in the root folder, as well as the readme.txt file in the plugins/ folder.

I wrote a quick Groovy script to try and provide a mapping of any changed files you may have in your current working copy, so you will know where to commit the changes. I tried to extend it to create diff files to be used as patches, but I could never get it to work very well, probably because I'm using Cygwin on Windows with its Subversion command-line client.  It shouldn't be too hard for Linux users familiar with Groovy to extend the following script to try and automate the patch creation process.

The script is very simple and looks like this:

def module_map = ['src-core':'core/src',
                  'src-db':'db/src',
                  'src-dbdialog':'dbdialog/src',
                  'src':'engine/src',
                  'src-jdbc':'jdbc/src',
                  'src-ui':'ui/src',
                  'src-plugins':'plugins',
                  'libext':'lib',
                  'ui':'assembly/package-res/ui']


'svn status'.execute().text.eachLine {line ->
    def svn_op = line.charAt(0)
    def old_fname = line.substring(1).trim()
    def path_segments = old_fname.split('/')
    def old_module = path_segments[0]
    def new_module = module_map.get(old_module)
    def file_mapping = new_module ? "$old_fname -> ${new_module}/${path_segments[1..-1].join('/')}" : old_fname
    println "${svn_op}\t${file_mapping}"
}

I also put the script on Gist here.

The module-map is the key to the location of the restructured files, the script simply calls "svn status" and for each changed file in your working copy, it will use the module-map to show the new location of the file.  Note that some files will not be mapped to a new location; this is either because the location hasn't changed, or because I forgot a mapping :-P

To use it, simply create a script called merge_helper.groovy (or whatever you want to call it) with the above contents and place it in your working copy of the old project structure.  Run the script with the command "groovy merge_helper.groovy" and it should show you output that will look something like this:

M       .classpath
M       src-plugins/market/src/org/pentaho/di/core/market/Market.java -> plugins/market/src/org/pentaho/di/core/market/Market.java
M       src-plugins/market/src/org/pentaho/di/core/market/messages/messages_en_US.properties -> plugins/market/src/org/pentaho/di/core/market/messages/messages_en_US.properties
M       src-plugins/market/src/org/pentaho/di/ui/spoon/dialog/MarketplaceDialog.java -> plugins/market/src/org/pentaho/di/ui/spoon/dialog/MarketplaceDialog.java
M       src-ui/org/pentaho/di/ui/spoon/job/JobGraph.java -> ui/src/org/pentaho/di/ui/spoon/job/JobGraph.java

If you have changes to ".classpath", be warned that there is no version-controlled file called ".classpath" any longer.  If you have new JARs or source folders to commit, please consult the README files for guidance on how to update the appropriate files. Also you can comment on this blog post with any questions about the migration process.

We hope you will find the new project structure easier to use, and the use of Apache Ivy will allow us to avoid many of the headaches that come with upgrading third-party JARs, especially to ensure that Pentaho products (which depend on each other) are using the same (or compatible) versions of their dependencies.

Cheers!

GroovyConsoleSpoonPlugin with JSR-223 Support

I recently gave a presentation of my GroovyConsoleSpoonPlugin (see earlier posts) to the Pentaho crew, and I got a lot of great feedback on it. Specifically, Pentaho Architect Nick Baker suggested it would be good to have the Groovy-ized API available as a step, so we could leverage it during transformation execution.  For the presentation I had made changes to the Kettle code to allow the adding of RowListeners and TransListeners, so the Groovy Console plugin could interact with a running transformation.  However, his suggestion makes a lot of sense, as I can keep the plugin a proper plugin, with no changes to Kettle code needed.

I thought about creating a Groovy scripting step and adding my staging code to make scripting easier.  However that involves a lot of setup and boilerplate code that already exists in other steps (such as the JavaScript and Script steps).  I still may do that, in order to leverage the full power of the Groovy language, but in the meantime it occurred to me that I could just create a script engine wrapper (using the javax.script.ScriptEngine interface, etc.) and use the experimental Script step as the vehicle.

So to that end I added GroovySpoonScriptEngine and GroovySpoonScriptEngineFactory classes, which wrap the existing plugin code inside a JSR-223 compliant scripting engine.  Then the Script step can execute Groovy code (with my staging code already injected) during a transformation.

To get Spoon to recognize my scripting engine, I had to add my plugin directory to the list of libraries when launching Spoon.  This is done in launcher/launcher.properties like so:

After starting Spoon, I created a transformation with a Generate Rows (for one row) with a field called "fld1" with a value of "hello".  Then I wired it to a Script step, which is wired to a Dummy step.

The key to selecting a scripting engine in the Script step is to name the step with an extension that corresponds to the engine name.  So for the Groovy scripting engine the step name ends in ".groovy", and for my GroovySpoonScriptEngine the step name must end in ".groovyspoon".  My sample transformation looks like this:

Inside the Script step I put some very simple code showing the use of the row data in GStrings, output variables, local variables (which are not returned to the step processor), and the Groovy-ized API available through the plugin:

The script step does have a bit of a limitation (due to its generic nature) that the output fields must be specified in the Fields table at the bottom of the dialog.  This is in contrast, for example, to the User Defined Java Class (UDJC) step, which can add values to the row on-the-fly (see previous posts).

Previewing the Dummy step gives the following result:

So now the plugin supports the Groovy-ized API in the Groovy Console, the command line, and the Script step.  With these three entry points, hopefully interacting with PDI will become alot easier and even more powerful!

I hope to get time to document the Groovy-ized API on the project's wiki.  In the meantime, take a look in the code at staging.groovy, that contains most of the methods, properties, etc. available.  In addition, of course, is the full Kettle API and all Groovy language features, so the sky's the limit when it comes to what can be done :)

The project is on GitHub under GroovyConsoleSpoonPlugin. As always, I welcome all comments, questions, and suggestions.  Cheers!

UDJC to Verify Transformations

The "Verify Transformation" capability of Pentaho Data Integration (aka Kettle) is very handy for spotting issues with your transformations before running them.  As a sanity check or as an auditing feature, I thought it would be nice to verify all transformations in the repository using a Kettle transformation.

To do this, I wrote a User Defined Java Class (UDJC) step to call the TransMeta.checkSteps() API method, just as the Spoon GUI's "Verify transformation" button does. However, instead of displaying a dialog, I put the same information out onto the stream.

In order to get access to the repository's transformations, I started with a Get Repository Names step.  Since Kettle jobs don't (currently) have the same verification functionality, I set the filter to only return transformations:

NOTE: The UDJC step will accept all incoming rows (Jobs, Transformations, etc.) but will only process those whose "meta" object is of type TransMeta.

To get access to the TransMeta object for each transformation, I used the Auto Documentation Output step, with the output type set to METADATA. This puts (among other things) a field on the stream containing the TransMeta object associated with each transformation:

I wired the autodoc step to my UDJC step and output the same fields as are available in the GUI version:

If this is determined to be a useful step, I may turn it into a proper step plugin, to remove the need for the "show_successful" field, and to instead provide a dialog box to let the user choose which fields (and their names) to put out on the stream.  UDJC steps are just an easy way for me to get basic functionality out there and try to get early feedback.

I created a dummy repository on GitHub so I could have a Downloads area where I will start storing sample transformations that contain UDJC steps, etc.  This is slightly easier than putting the UDJC code on Gist or Pastebin, especially in this case since there are multiple steps involved.  The direct link to the above transformation is here.

If you give this a try or otherwise have comments, I'm eager to hear them :)

Cheers!

Groovy Console Spoon Plugin Update

I've been working on the Groovy Console plugin for Spoon, and I seem to have been able to sidestep the PermGen issues (at least the ones I was having during my last post).  Also I added some more functionality, such as the ability to get JobMeta and JobGraph information using job, jobGraph, activeJob(), and activeJobGraph().

I really wanted to have the ability to do "automated" testing from this console, so I added a runTrans() helper method.  If you pass in a TransMeta object, it will run that transformation; otherwise, it will try to run the active transformation.

However, in implementing the runTrans() method I ran into a couple of issues:

1) The execution settings dialog pops up if you run Spoon.executeTransformation() with no arguments.

2) If you are looping and trying to run the transformation once during each loop, the executions will step on each other as they are executed asynchronously.

To fix 1, I faked a call to executeTransformation() using a replay date of the current time, which prevents the execution settings dialog from being displayed.  As far as getting the settings configured, that will be a little trickier. So runTrans() at the time of this writing works on transformations with no execution-specific settings, or on transformations that have already been run with execution-specific settings.  This is because the transformation is effectively replayed.

To fix 2, I put in a hack after the call to executeTransformation() to sleep for 10 msec while the trans is NOT running, then to sleep for 100 msec while the trans IS running.  This is a classic no-no for concurrency but it's in there to get things working in the happy-path case.  This turns runTrans() into a synchronous call, which is fine for my purposes in the console.

Using the database() and runTrans() methods, I was able to automate the testing of a single transformation using two separate database connections:

One thing to note is that I'm changing the step metadata using "back-door" API calls, so the transformation graph is not marked as "dirty".  If for some reason you need to save the changes you should be able to call some form of Spoon.save API method.

The updated code and downloads are on GitHub. Have fun!

Getting Groovy with PDI

I'm a huge fan of Pentaho Data Integration and also of the Groovy programming language.  Since PDI is written in Java and Groovy and Java are so tightly integrated, it seems only natural to bring some Groovy-ness into PDI :)

Some Groovy support already exists in the form of the Script step in the Experimental category.  This step allows any language that supports JSR-223 to be used in scripting a PDI step.  Also I believe there is some Groovy support in the Data Mining product in the Pentaho suite.

What I wanted was to offer a Groovy console to the Spoon user where he/she could interact with the PDI environment.  It turns out that Groovy's groovy.ui.Console class provides this capability.  So I created a Spoon plugin with a Tools menu overlay offering the Groovy console:

And before it opens (the first time takes a while as it loads all the classes it needs including Swing), it adds bindings to the singletons in the PDI environment:

- spoon: this variable is bound to Spoon.getInstance(), and thus offers the full Spoon API such as executeTransformation(), getActiveDatabases(), etc.

- pluginRegistry: this variable is bound to PluginRegistry.getInstance(), and offers the Plugin Registry API, such as findPluginWithName(), etc.

- kettleVFS: this variable is bound to KettleVFS.getInstance(), and offers such methods as getTextFileContent(), etc.

- slaveConnectionManager: this variable is bound to SlaveConnectionManager.getInstance(), and offers such methods as createHttpClient

In addition to the above singletons, I also added the following variables that are resolved when the console comes up:

- trans: This variable is resolved to Spoon.getInstance().getActiveTransformation()


- defaultVarMap: This variable is resolved to KettleVariablesList.getInstance().getDefaultValueMap()

- defaultVarDescMap: This variable is resolved to KettleVariablesList.getInstance().getDescriptionMap()

- transGraph: This variable is resolved to Spoon.getInstance().getActiveTransGraph());

Then I added a few helper methods:

- methods(Object o): This method returns o.getClass().getDeclaredMethods()

- printMethods(Object o): This method will print a line with information for each of the methods obtained from calling methods() on the specified object

- props(Object o): This is an alias for the properties() method

- properties(Object o): This method returns o.getClass().getDeclaredFields()

- printProperties: This method will print a line with information for each of the fields obtained from calling properties() on the specified object

- activeTrans: This method calls Spoon.getInstance().getActiveTransformation(), and is thus more up-to-date than using the "trans" variable.


- activeTransGraph: This method calls Spoon.getInstance().getActiveTransGraph(), and is thus more up-to-date than using the "transGraph" variable.

- database(String dbName): This method returns the DatabaseMeta object for the database connection with the specified name. It searches the active databases (retrieved from Spoon.getInstance().getActiveDatabases())


- step(String stepName): This method returns the StepMeta object for the step in the active transformation with the specified name.

- createdb(Map args): This method takes named parameters (passed to Java as a Map) of name, dbType, dbName, host, port, user, and password, and creates and returns a DatabaseMeta object

Once all the bindings are done, the console is displayed:

This allows a Groovy way of doing inspection of the PDI environment, but since almost all of the API is exposed in one way or another, you can also edit metadata.  For example, in the above transformation I'd like to run against two different database connections.  Without this plugin you run the transformation manually against one database connection, then edit the steps to switch the connection, then run the transformation again.  With the Groovy Console you can automate this:

Right now I'm having PermGen memory issues when running transformations, so until I get that figured out the console is probably best used for looking at and editing metadata rather than executing "power methods" like executeTransformation().

Besides swapping database connections, you can also create a new one using the createdb() helper method. Here's an example of this being done, as well as testing the connection and adding it to the active transformation:

Using the API, you should also be able to create new steps, transformations, etc.  I'd be interested to hear any uses you come up with, so please comment here if and how you've used this plugin.

The code and downloads are up on GitHub, just unzip the folder into plugins/spoon.  Cheers!

UPDATE: It appears that in PDI 4.3.0 there is an older version of the Groovy JAR in libext/reporting, which causes the console plugin not to work. You can try replacing that JAR with the one included in the plugin, but I'm not sure if the reporting stuff will continue to work.  In the upcoming release, the version of the Groovy JAR has been updated to 1.8.0.