Thursday, December 20, 2012

PDI: Pass Parameters to Jobs/Transformations

I had been working on trying to get a process to run for each file. I used the Get File Names step followed by the Copy rows to result step. I had placed this in front of my Text file input step, which is where you define the file for further processing.

That method produced a stream (that's what it's called in PDI) with each and every file and each and every record in those files. If I were just loading that into a table, it would have worked. However, I was assigning a identifier to each file using a database sequence. I needed a sequence for each file, but I wasn't getting it.

With some help and pointers from the ##pentaho IRC channel, I found this post (more on that one in the future), Run Kettle Job for each Row. I downloaded the sample provided to see how it worked.

The calc dates transformation just generates a lot of rows. Not much to see there. The magic, at least for me, was in the run for each row job entry.

Specifically, the Write to log step. (I have this need to see things, since I don't understand everything about the tool yet, Write to log provides me that ability.)

See date, better, ${date}? That's how you reference parameter and variables.

I ran the job and watched the date scroll by. Nice. Then I tried to plug it into my job.

Zippo. Instead of seeing, "this is my filename: /data/pentaho/blah/test.csv" in the log output, I just saw "this is my filename:" Ugh. I went back to the sample and plugged in my stuff. It worked. Yay. Went back to mine, it didn't. Gah! I tried changing the names, then I'd just see "this is my filename: ${new_parameter_name}" so it wasn't resolving to the value.

Finally...after comparing XML for the sample file and mine and finding no real differences, I just about gave up.

One last gasp though, I went to the IRC channel and asked if there was some way to see the job or transformation settings. No one was home. I tried right-clicking to bring up the context menu and there was Job Settings

Job Settings brought up this one:

date is defined there. I checked mine. Nothing defined. Added filename to mine, ran it, Success!

Wednesday, December 19, 2012

Learning Pentaho Data Integrator

aka Kettle, aka PDI.

I've recently taken on a data integration gig and I'll be using Pentaho Data Integrator (PDI). I've read about Pentaho for years but never got around to actually using it. I'm excited about the opportunity.

What is PDI?

...delivers powerful Extraction, Transformation and Loading (ETL) capabilities using an innovative, metadata-driven approach. With an intuitive, graphical, drag and drop design environment, and a proven, scalable, standards-based architecture, Pentaho Data Integration is increasingly the choice for organizations over traditional, proprietary ETL or data integration tools.

I'll be using the enterprise edition (EE), which is supported, similar to how RedHat works...I think.

This post if mainly for me, naturally. I'm going to list out the references I've found so far and add to it over time. Similar to what I do (err, did) for Learning OBIEE.

Actually, I'll just start with the helpful email I received after being added to the account.
I love that you can download and play with the software yourself. Of course the Community Edition (CE) is open source, so that makes sense. I'm not sure if you can get the EE version for free though.

There's a community page as well with links to a lot of great resources. So far, my favorite has to be the IRC channel hosted at freenode. Note, there are two hashtags as in ##pentaho. I've been lurking there for a few weeks and finally got up the nerve (what? me shy?) last week. HansVA and mburgess_pdi helped get me moving again on a particular problem. Good stuff.

I'm sure I'll add more as time goes on. That's it for now.

Update after original posting...
  • (Kettle, aka PDI) Wiki

Wednesday, December 12, 2012


Hard to believe the little monster will be 8 years old tomorrow. Where has the time gone? It was yesterday she was sitting in the grass trying not to touch the grass. (She is still trying to eat her foot...)

Looking back, it's been a good year for Kate. Early on she got an iPad to help her to communicate better. Later in the year, with help from many of you, we upgraded her AAC app to Proloquo2.

Not too long ago, Kris had said that she heard "popcorn" coming from the backseat on the way home from school one day. She'd follow up the use of the iPad with both hands pointing up and saying "pop pop." I sort of forced her to do this video, so it's not organic, but you get the point. A big win in my book.

One of the unintended consequences of the iPad is that Kate is now able to have independent play. She trolls YouTube for Dora the Explorer, SpongeBob, and other videos. This is important not only for her, but for her parents, who desperately needed a break. (We'd sit there with her watching Dora, and if it ended, she'd kinda lose her cool...not fun for any of us. Now we can watch normal TV, football, the Science Channel, etc.)

Word of caution about Dora and YouTube. Apparently a lot of people have way too much time on their hands...there's Ghetto Dora, Dora the ExploraChola, and a host of others that aren't fit for young children.

8 years old.

After a brief stall, we're moving forward again with Kate's care. By stall I don't mean she wasn't receiving therapy or anything, just that we felt kind of stuck in terms of her progress and getting her the right therapy.

On Monday we met with someone from CARD, the Center for Autism and Related Disabilities at the University of South Florida. One place that we have struggled is managing Kate's care: What should she get? How can we do better? What's her future like? I think the people at CARD will be able to help us with that aspect.

Now she's lying 8. (Really hard for me to believe). Fun times ahead I'm sure.

Now the sappy OOW I had a number of conversations about Kate. I even filmed a video segment with OTN on the origins of this site (the name really) and by the end it was all about Kate. If you look real close, you can see a tear. I'm sure the adult beverages consumed prior had nothing to do with it.

At the Pythian party, I ran into Martin and Jason. (Jason was the one, in 2010, who when I first met him, asked how Kate was doing). They told me how much they enjoyed reading about Kate, about how it affected them in their lives. (I'm sure I cried there too...what can you do?). Bjoern and I talked about her too. Actually, just about everyone asked about Kate. I think that is pretty cool.

So thank you from the Justice/ORACLENERD family for all of your support over the years and Happy Birthday to katezilla!