Bucket Partitioner plugin

Lately I've been fooling around with the various PDI/Kettle plugin types while reading Matt Casters et al. book "Pentaho Kettle Solutions", and I reached the section on partitioners, which intrigued me.  Around the same time, someone had asked a StackOverflow and/or Pentaho Forums question on how to get at certain data from a "flat" XML file, meaning the data he was interested in was not contained in a parent tag, rather it was a certain number of fields AFTER a particular tag.

The solution I proposed was to use the row number to create a sort of "bucket", where the first N rows would be in the first bucket, the next N in the second bucket, and so on.  Then it occurred to me that this is just a different form of the Mod Partitioner, except you use the quotient of the division (over the number of partitions) rather than the remainder.

This seemed like an easy thing to implement since all the infrastructure code was already done by the ModPartitioner classes.  So I copied the source files into a new project, using a @PartitionerPlugin annotation on my BucketPartitioner class (the ModPartitioner, being part of Kettle core, has its plugin attributes located in the kettle-partition-plugins.xml file).

All I had to change was to add a "/ nrPartitions" expression to the getPartition() code :)

Anyway, the code is up on GitHub (with the plugin JAR), and when I ran it against the sample customers-100.txt CSV file into partitioned Text File Output steps:

I got the results I desired for a partition size of 4, with the first 4 rows (starting with row number 0, so the first "group" only had 3 rows in the bucket versus the usual 4) in file-A.txt, the second 4 rows in file-B.txt, etc.  I left the (% nrPartitions) expression in (as suggested by the authors) so that row 16 would be in bucket A and so on.

Now that I got my feet wet with partitioner plugins, I'm thinking about the following partitioners:

- A Number Range partitioner (much like -- and re-using code from -- the Number Range step)
- A String Difference partitioner (same algorithms available in Fuzzy Match & Calculator steps)
- An XPath-based partitioner?
- A dynamic partitioner of some kind

As always, I welcome all comments, questions, and suggestions :)  Cheers!

Creating a reproduction transformation using UDJC

I got some pretty good feedback about my previous UDJC step that creates a Data Grid model of a Kettle stream, but I noticed right away that the process for using it is a bit long.  You have to paste in the step, wire it into the trans, run the trans, remove the steps before the UDJC, then wire the Data Grid in place of the UDJC, delete the UDJC, and save the trans with a different name.

The Kettle API provides all of the above operations, so I thought I should upgrade the step to create a new transformation with the Data Grid step as the source, connected to the rest of the transformation (after the UDJC step).  Along the way I also found some issues with threading and metadata in the previous UDJC step, so I fixed them in the new one.

The code for the new "Create Reproduction Transformation" UDJC step is located on Gist here.  You "install" it the same way as the previous version, either by pasting the code into a new UDJC step, or by pasting it in from a transformation that already contains the filled-in UDJC step.  Then you wire it up in the stream:

Then run the transformation.  The transformation runs as if the step weren't there (it passes through all data), but a new transformation is created, containing the Data Grid step hooked up to the "rest of the transformation":

Looking at the Data Grid step, you can see the data from the stream:

One thing to remember is that the step the Data Grid is wired to might have referenced the previous step(s).  Because the metadata is different for each step, I couldn't interrogate the step to see if it referenced previous steps, and thus I couldn't change the setting.  This means if the step after the Data Grid references previous steps (like Merge Rows for example), you will have to edit it to reference the Data Grid instead.

Anyway, I hope this helps!  If you try it out, please let me know how/if it works, if it's useful, and if you'd like to see any other features.  Cheers!

Create a Data Grid from a PDI step's output

Sometimes when folks write Jira cases against PDI, they post sample transformations that reproduce the problem, which can be very helpful.  However, often the transformation references a database that PDI developers can't access, which renders the transformation not-so-helpful for debugging and reproduction purposes. We usually ask that the case writer create a Data Grid step or something that will recreate the offending data without requiring a database connection.  This can get complex and may be beyond the skills of some users.

To that end, I wrote a User Defined Java Class (UDJC) that will read in rows from a step and create a Data Grid step in the transformation that contains the meta and data necessary to recreate the problem.  The user can paste the UDJC step into their transformation, insert it after the step they wish to model, then run the transformation. At the end, a Data Grid step will be added to the transformation, and the user can substitute it into the transformation in place of the step as a row provider.

As an example, let's use the Get Step Plugins transformation from my previous post:

I can create a UDJC step and paste in the code (actually, I keep a transformation handy that contains only this step so I can easily cut-n-paste into transformations), then add it to the stream after Get Step Plugins:

After running or previewing the transformation, the Data Grid Step is created:

Then I insert the Data Grid after the Create Data Grid UDJC and remove the earlier steps:

Previewing the Input Steps step, I get the same results as I did with the Get Step Plugins source!

I added Undo support so you can use Ctrl-Z to undo the addition of the Data Grid step if you wish.  The code for the UDJC is located on Gist here.  I've already got ideas on how to make the "creating a reproduction transformation" process even easier, but I think this is a decent first step towards that.  Let me know what you think!

UDJC as Input step

Most times I see examples (including my own) of the User Defined Java Class (UDJC) PDI step, I see a Generate Rows step in front of it, to act as a "trigger" to get things moving, as UDJC is a Scripting step, not a proper Input step.  However, this is not a forced characteristic, meaning you can write a UDJC step that acts as an Input generator step!

The key to doing this is to not read in any rows using getRow(), and thus not to check for a null row.  You can assume that processRow will be called once, and you ensure it will ONLY be called once by calling setOutputDone() at the end of processRow(), followed by returning false.  The "Input Generator" version of the UDJC step I wrote to get all PDI Step Plugins (from my previous post) is on Gist here.  Here's a screenshot of an example using the Input version to filter on Input plugins:

Look ma, no Generate Rows! :)

Get Step Plugins UDJC

I like the Step Information dialog in the Help menu of Spoon, it lists all the information about the loaded step plugins.  It occurred to me that it might be nice to know this info while running a job or transformation, and while looking through the code, it turned out to be pretty easy.

To do this, I created a User-Defined Java Class (UDJC) step with the usual Generate Rows step as a trigger:

The code for the UDJC step is on Gist here.  The results are similar to the Step Information dialog:

This code can easily be adapted to fetch all plugins, or even to specify the type(s) on the incoming stream.

Parent-Child lookup

I'm writing this post in response to this question at StackOverflow (and the Pentaho forum post here), unfortunately as I am a new member at Stack Overflow they won't let me post images :-/

The question was:

I have a source table (from a MS DB) that contains a field "ParentID", which references the PK of "ID"

The target Postgres table is filled (new "ID" PK inserted from sequence) and the "ParentID" is set to null. The stream has a the new PK inserted within it.

So now at this stage i have a valid stream of

"ID","ParentID", "NewTargetID"
100   NULL     1
101   100      2

How do i do a "Stream Lookup" (or maybe something else) and lookup the values to create a new stream field to give me a new field of "NewParentID"

"ID","ParentID","NewTargetID","NewParentID"
100   NULL    1     NULL -< (How do i generate this ?)
101   100       2     1 -< (How do i generate this ?)

Thanks

David

My response is:

One way is to have two copies of the data, so you can use one as the lookup stream and one as the data stream:

 The "Lookup by parent" step looks like this:

I ran this with a Data Grid step containing the values from the OP, and got these results:

I'll try to get this example transformation up on Box or our Wiki.

PDI Pseudo-step: Google Docs Fusion Table Input

As far as I can tell, the current Google Docs plugin for PDI works on Spreadsheets and perhaps other Google Docs data sources, but not Fusion Tables (which at the time of this writing are currently in beta).  I whipped up a quick User-Defined Java Class (UDJC) step, the code is on Gist here.

A couple of notes:

1. The output fields (that I'm grabbing from the Fusion Table) are hardcoded in there right now
2. I didn't use the OAuth method of access, the username and password (along with table id) should be passed as input fields to the UDJC step.
3. The table id is available in the URL when you open the table in Google Docs.
4. You need the Google Docs plugin installed in PDI for this step to work.
5. Someday I'd like to integrate this with the real PDI Google Docs plugin :)