Monthly Archives: August 2009

Amazon's Pre Ordering of books sucks!

I pre-ordered a copy of the new, (first, only, best, and original) Pentaho book “Pentaho Solutions” by Roland Bouman and Jos van Dongen two weeks back.  Saw from a tweet that the book was shipping from Amazon.  Cool – had a look at the page.  Sure, they can ship today if I get my order in on time so I know they can ship it.

How about my pre order, which I would assume would go out before regular orders?  Won’t ship until next week?  Delivered by 9/11/2009?  Lesson learned – don’t pre order from Amazon.  :)

Good Riddens Ecto

I was using Ecto, a Mac OS X blog client for the past year or so.  Overall I really DIDN’T like it, except that it had the ability to allow me to “Paste” images into the blog using the clipboard.  You’d be surprised at how many blog clients still force you save a file on to your computer, then browse to it, and then upload it, then include it in your post.  A real drag when you’re trying to do a few simple software screen shots.

I upgraded to a new Mac recently and the Ecto license key I had didn’t come over from the old Mac.  According to the Ecto website, just email them and they’ll send it along.  I emailed on 8/11/2009 and no response.  Not good customer service at all. 

But I really wanted to blog about the encrypted variables; I downloaded and gave ScribeFire a try and I’m switching.  It can paste images same as Ecto and it’s built right into Firefox.  Excellent!

So… Ecto customer service really goofed on this one.  You’ve got a customer who isn’t happy, gave me an opportunity to find something I like better, and a chance to blog about the whole thing!

Encrypted Variables in PDI

Every once in a while, I get to sound like a royal arse in front of a customer by saying something “I know” to be true about Pentaho that isn’t.  Usually, this is a REALLY good thing because it’s usually some limitation, or Gotcha that existed in the product that has magically disappeared with the latest release.  The danger of open source is that these things can change underneath you quickly, without any official fan fare and leave you looking like a total dolt at a customer site.  Bad for consultants like me who are constantly having to keep up with extraordinarily fast product development.  Good for customers because they get extraordinarily fast product development.

One of these experiences, which I was absolutely THRILLED to look like a dolt about, was

“If you use variables for database connection information, the password will be clear text in kettle.properties.” 

A huge issue for many security conscious institutions.  Customers were faced with a choice: use variables which centrally manages the connection information to a database (good thing) but then the password is clear text (bad thing).  No longer!

Our good friend Sven quietly committed this little gem nearly 18 months ago. It’s been in the product since 3.0.2!  It allows encrypted variables to be decrypted in the password field for database connections.

Let’s test it out… our goal here is to make sure we can get a string “Encrypted jasiodfjasodifjaosdifjaodfj” which is a simple encrypted version of the password to be set as a regular ole variable but then be used as the “password” of a database connection.

We have a transformation that will set the variables, and then we’ll use that variable in the next transformation.

The first one sets the variable ${ENCRYPTED_PASSWORD} from a text file.  This string would be “lifted” from a .ktr after having been saved that represents the encrypted password.

Then we use it in the next transformation and select from a database, and outputs the list of tables in the database to a text file.

Output – works like a charm! 

Customers can now have the best of both worlds.   Centralize their variables for host/user/password using variables (including, kettle.properties) and keep those passwords away from casual hackers.  I say casual because PDI is open source so in order for someone to decrypted a password they only need know Java, and know where to find PDI SVN.  :)

As always, example attached: encrypted_variables.zip