Blog

Talend Open Studio Tutorial - Part III

2010-04-03T09:08:14Z

Now that we've created a Job in Talend Open Studio (TOS) we need to start defining the source of our weather data and how it's structured. This definition then resides in our Repository and we can use it for as many jobs as we want.

In the top left of TOS, just under the Job name, you should see a section marked Metadata. Click the little triangle next to Metadata and then right click on File Delimited:

Click on "Create file delimited" to open the Metadata wizard. Enter a name for your source weather data (this can be anything you like; but no spaces). The other information is optional and once done click Next:

Step 2 identifies the file containing the weather data. Click Browse to locate your CSV file in the file system. The Format should be set to WINDOWS (unless you're using some other operating system). Once the file is located the File Viewer area should look something like the image below:

Click Next to move onto the next step. Step 3 defines how the individual fields in the data file are separated. The most common separator is a comma (hence Comma Separated Values or CSV). However TOS might default to a semi-colon field separator in which case you'll need to change to to a comma:

Here you can also specify whether the file has header or footer rows that should be ignored (Virtual Weather Station data files don't have a header row; but yours might). Once you're happy click the Refresh Preview button and you should see your data neatly formatted into columns as above. Click Next.

The final step in the wizard allows you to specify names for each of the columns of data. You could leave them as Column0, Column1 etc; but if you do you'll find it much harder later when it comes to map the source data onto the database table. This is what Step 4 looks like initially:

Click on each of the Column names and replace them with something meaningful. Here you'll need to know the order of each field in your data file and your weather station software documentation should be able to provide this for you. I know this will take a while; but believe me it will be worth it later. Here I've started to add some column names:

Once you've added all your column names click Finish and your navigation tree should look something like this:

That's the source for our weather data defined. In the next part we'll use that to do something useful.

Data Integration Tutorial - Part II

2010-03-17T14:33:24Z

In part II of this tutorial we'll take a look at creating our job in Talend Open Studio (TOS); but before we get started I just want to go into a bit more technical background stuff that we'll need along the way.

First off is the data file format. My weather software (Virtual Weather Station) stores its data in a file with each value separated by a comma. This type of file is referred to as a Comma Separated Values, or CSV, file. For this project to work you'll need to

Find out where on your PC your weather software stores its data file.
The format of the file, is it a CSV file or something similar. This project will work with any text based data file, you might just have to adjust things as you go along.
The order of the fields in the file. You should be able to find this from your weather station software documentation.
The position of the "key field" in the file.

The key field in this case is probably a field containing the date and time. For example in the VWS data file the key field is the first field and contains values in the format YYMMDDHHMI, for example 201003171442. The reason this is the key field is that it uniquely identifies each row of data - you can't have two rows with the same date and time. It's possible your data file might have the date and time split into two fields, in which case your "key" is a combination of the two. In database terms this key is called the Primary Key.

OK, that's enough chit chat, let's get down to some work. The first thing to do is to open Talend Open Studio (TOS). When you first open it you'll see something like this:

Before we can do anything we have to set up a repository by clicking the ... button indicated. This is simply where we'll be storing our project, which in this case will be locally on our PC:

Select a Local repository, enter your email address and choose where you want your workspace. I've put mine under my Documents folder; but you can leave yours in the default location if you wish. Click OK to set up the repository:

Now click the drop down and select "Create a new local project", then click Go!

Enter a name for your project, the description is optional. Then select the Generation language, here we're using Perl; but you can use Java if you want. However they'll be a step later on that is Perl specific so unless you know Java then stick to Perl. Don't be concerned that we'll be writing huge screeds of code, it's only a very small step that needs to be written in Perl. Finally click Finish to create the project.

Click the Open button to open the project in TOS:

Once you have the workspace open this is how it should look. The main areas we'll be working with are:

The repository. Here we set up information about our data, where it's coming from and where it's going to. Once this information is in the repository it can be used in other projects.
The Palette. This will contain all the methods we can use to manipulate the data. It's empty now, once we create a job it'll be populated.
The Job Design area. Here we'll manipulate the data, much more on this later.
Tabbed area. Here there are many tabs that will allow us to configure our data objects and methods and run our job.

Lastly let's create a Job. Right click on Job Designs (Repository top left) and select "Create job":

Enter the job details. You have to give the job a name, the rest is optional.

When you click Finish you'll be returned back to TOS, your Palette will be populated with all the many tools you can use and the Job Design area will become an active grid.

That's all for now. In the next installment we'll be creating some data objects and taking our first look at them in TOS.

Tutorial - Data Integration With Talend Open Studio

2010-03-16T20:17:28Z

This is the first in a series of tutorials on using Talend Open Studio to perform data integration tasks. Talend Open Studio (TOS) is a very flexible tool for manipulating data - taking it from here and putting it there; but the documentation is a bit dry and it's sometimes difficult to figure out just which bits do what. This tutorial is taken from a real life task and demonstrates some of the more complex aspects of using TOS to perform a real job.

So what's the job? The requirements were these:

I've a weather station running at home (see my site TulligWeather). The software that logs the weather data writes the information to a "comma separated values" (CSV) data file - one line of data every 5 minutes. My requirement was to find a reliable way to insert this into a database so that I can manipulate it further (e.g. draw charts; but that's another tutorial). Once the data is in a database it's easy to query it to find maximum or minimum values, averages, trends and the like.

If you want to follow this tutorial then you'll need these ingredients:

A MySQL database. I have MySQL installed on a separate (Linux) machine to my desktop; but you can install it on your Windows PC if you've got more than a Gb or so of RAM. Download the latest community version and run the installer. Make sure your remember your root password if you're prompted for one during installation. Many web hosting packages these days come with at least one MySQL database so you can use this instead of installing MySQL on your PC. If you want to use a web hosted database then you'll need to know the host name, user name and password for the database. Your web host should be able to help out with this information.
Talend Open Studio. I've been using the latest release of version 4 which is at M3 at the time of writing. However I've found it to be perfectly stable and fixes an annoying bug that exists in version 3.2. Download the Windows installer, it's a fair size at more than 230Mb, and install it.
Activestate Active Perl. This isn't strictly necessary - TOS can generate scripts in two languages, Java or Perl. Both of these are built into TOS; but the Perl is broken. You can do this whole project generating Java; but as I know Perl I prefer to use Perl. Once you've downloaded and installed Active Perl you'll need to run the Perl Package Manager and search for a package called DBD::MySQL. Mark this package for installation and then apply this to install the package. This will allow TOS to connect to your database using Perl.

Now you should have a MySQL database, TOS and Active Perl (including DBD::MySQL) installed on your PC. The next step will be to fire up TOS and create your project, more on this later.

Whilst I'm not a support service for MySQL, TOS or Active Perl installation let me know if you run into problems and I'll see if I can point you in the right direction.

Oracle Buys Sun

2009-04-20T20:15:33Z

For some days now it’s been speculated that IBM would purchase Sun Microsystems. However the news today that Sun is to be purchased by Oracle has far greater consequences than an IBM / Sun merger.

For a start Sun is a hardware manufacturer with a range of UNIX servers that target corporate IT infrastructure. Oracle, on the other hand, has no experience with box production and the corporate merger landscape is littered with companies that merged into areas they were not familiar with and came down to earth with a bump as a result. I would expect Oracle to hive off the hardware arm of Sun as a separate (and loss making?) business after which IBM can pick over the bones.

The rest of Sun Microsystems is a different prospect for Oracle. Sun own Java, the programming language and runtime that’s become pretty ubiquitous over the last ten years and can now be found on a huge range of every day devices from mobile phones, cars, washing machines; just about anything that needs a control system. Just how Oracle views Java could have far reaching consequences in a huge variety of market places.

Then there’s Sun’s other Open Source product set including the competitor for Microsoft Office; Open Office.org. Open Office has got to the point where it’s a serious alternative to the (very expensive) Office suite from Microsoft, and it won’t cost you a dime. Sun has been providing supporting services (if not hard cash) to the development of Open Office.org. Now I don’t know how much of a sink hole this has been for Sun; but it would be a great pity if Oracle abandon these efforts rather than continuing to promote and support development.

The MySQL database is another big question mark for Oracle. Purchased by Sun a few years ago MySQL could be seen as a direct competitor to Oracle’s database products, again at a fraction of the price. Removing MySQL from the market place could have been a big incentive for Oracle to purchase Sun. However the enterprise version of MySQL is only one part of the equation; the other part is what happens to the community version. For years now this has been the bedrock of many of the innovative open source projects that have made cloud computing what it is today. If Oracle should stop supporting the community edition of MySQL it will leave a huge hole in the open source development effort.

Down the line I predict this take over could have far reaching consequences for the shape of the IT industry. Let us know what your views are in the comments below..

Windows 7

2009-03-21T16:33:53Z

I don’t know if it was all the bad press that Vista got; but something seems to have spurred Microsoft into the action in developing the next version of its Windows operating system. Currently called Windows 7 it’s been around in a beta version for a few months now and yours truly has been having a look.

Are You Prepared For A Disaster?

2008-11-15T18:56:31Z

Imagine this scene. You come into work on a Monday morning to find that there was a fire that started in a neighbouring building over the weekend and your office was badly damaged by heat, smoke and water from the fire brigade. You're stood in the car park wondering what you're going to do to rescue your business.