Talend: tips and tricks part 2

In the first part of these entries we discussed how to test your expressions, the importance of optimizing the appearance of a tLogRow component and how to handle windows and views within Talend. This time around, we will be talking about the different ways to get components into your job, how to trace your dataflow and how to easily sync columns. As last time, this post will be useful for both starting and experienced users.

4. Getting components into your job

There are many ways to get components into your job. Most people search the palette (by either the search-function or by manually exploring the folders) and drag/drop the components into their job. You can achieve the same thing by simply clicking on a random place in your job and then type the name of the component. Obviously this is only recommended once you’re familiar with the different components and their names.

insert component

When working with metadata, you can use certain shortcuts to save a bit of time. Usually people just click on the metadata and then drop it onto their job. This will pop up a window allowing you to choose which type of component you want to use. Holding the Control-key while dragging the component will directly create an Output-component. Holding Control+Shift will result into an Input-component.

5. Syncing columns

Occasionally, you may have to change the schema of a certain component in the middle of development. This might affect other components in your job. In some cases, Talend asks if you want to propagate the changes you’ve made (to the other components).

propogate changes

You may accidently close this window, click “No” or not get this message at all, resulting in the following error: “The schema from the input link “youroutputlink” is different from the schema defined in the component”.

different schema error

When this happens, you can go to the basic settings of the component that has the error and click on “Sync columns”. The error should now be gone.

schema sync columns

6. Tracing your dataflow (Debug Run)

Lastly, I would like to say a few words about the debug run. In some cases we want to closely watch our dataflow in order to get a better understanding of what’s exactly happening. You can achieve this by running your job in debug mode. This can be done by clicking on the Run-window, then click on the “Debug Run” tab on the left side of the window and start it by clicking on “Traces Debug”.

debug run

The moment you open the “Debug run” tab, you’ll immediately see extra icons in your job. These magnifying glass icons indicate that details will be shown when you debug-run your job. The result should look something like this:

debug run example

You can Pause and Resume the run at any time. You can also add breakpoints if you like. Do this by right-clicking on a dataflow and then selecting “Show Breakpoint Setup”.

show breakpoint setup

This brings you to the “Breakpoint” tab of the data flow you clicked on. You can also go there by clicking on the specific flow and manually selecting “Breakpoint”. Let’s add a breakpoint to pause our run whenever we come across a record with “Bloom” as last name. Firstly, make sure to check the “Activate conditional breakpoint” option. After that, click on the plus-icon underneath the conditions. Then select the InputColumn we want to put our condition on, in our case this is “Last_name”, and add a value (“Bloom” in this example). The default Operation is “Equals”, which is the one we want. You can also specify an Operation if you need to, but this is unnecessary for this case.

component breakpoint properties

You can add multiple breakpoints if you like. Whenever you debug run your job now, it will stop at a record where the Last_name is “Bloom” (if any exist).

That’s it for now. Thank you for reading!

Talend: tips and tricks part 1

This blog contains some convenient tips and tricks that will make working with the open source tool Talend for data integration a lot more efficient. This blogpost will be especially useful for people who are just discovering this amazing tool, yet I am sure that people who have been using it for a while will also find it very helpful. These series of tips will be spread over multiple blog entries so make sure to check back often for future tips!

1. Testing expressions in the tMap component

Using the tMap component, you have the possibility to test your expressions. This way you can easily see whether or not the result is what you expected it to be. You can also use this to determine whether or not your expression will error. Let’s create an example.

We’ve got details of employees as input for our tMap. We would like the first name to be shown in uppercase. First of all, go into the expression builder by clicking the ellipsis next to your expression.

Ellipsis expression builder

To convert the first name to uppercase, we have to use the StringHandling function “UPCASE”. This will result in the following expression: StringHandling.UPCASE(employee.First_name)

After you’re done filling in test values, click on the “Test!” button and wait for the result. If everything goes as expected, you should see your first name in uppercase on the right side of the window.

Test expression builder tMap

2. Optimizing the appearance of the tLogRow component output

tLogRow is one of the most frequently used components. It is recommended that you learn how to optimize its use. Firstly, make sure that you always have the right appearance selected for your output. You can find this property in the basic settings of your tLogRow-component.

tLogRow modes

There are three types of Modes that you can choose between:

  • Basic

Basic will generate a new line for each record, separated by the “Field Separator” you’ve chosen (see image above). When using basic mode, I highly recommend to check the “Print header” option when working with multiple column records or multiple outputs, purely for visibility reasons.

basic mode output

  • Table (print values in cells of a table)

The table mode shows the records and their headers in a table-format, including the name of the component that generated this output (in our case: “tLogRow_1”). This emphasizes the importance of properly naming everything, especially when you have multiple components that generate output. In this case, it would have been better to rename our component to “EMPLOYEES”. Personally, I prefer this mode.

table mode output

  • Vertical (each row is a key value/list)

Vertical mode will show a table for each one of your records.

vertical mode output

The output mode you decide to use depends on what you’re trying to visualize. For example, when your goal is to show a single string, I would recommend using the basic mode. But when you have multiple table outputs (for example: departments, customers and employees in a single output), I’m certain the table mode would be the best option.

Sometimes your data is spread over multiple lines, resulting in an unclear output, like shown in the image below.

output with wrap

To force the output to put all the data on one single line, you can uncheck the “Wrap” option. This option is located underneath your output and will enable a horizontal scrollbar.

output without wrap

Do you also want to be able to get data regarding tweets using Talend, as shown in the image above? Read my previous blogpost and find out how!

3. Resetting windows and maximizing/minimizing them

Sometimes you accidently close a window and have a hard time finding a way to get it back. You can very easily reset your environment by clicking on “Window” – “Reset Perspective”.

reset perspective

You can see all of the views by clicking on “Windows” – “Show View” – “Talend”. Some of the views are not shown by default, such as “Modules”. Modules can be used to import .jar-files without having to restart your studio, which will most likely save you some time.

Lastly, because Talend is Eclipse-based, you have the possibility to maximize and minimize windows. I personally use this function when examining the output of a tLogRow-component including a lot of data. You can achieve this by either double-clicking on the window or by right-clicking on it and selecting “Minimize”/”Maximize”.

That’s it for now. I hope you enjoyed reading this blog and make sure to return soon for future blogs!

Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more Continue reading

Tips & Tricks in BIP

I have worked with BIP for a few months and I discover some tips & tricks around developing reports. If you develop too fast or you are unattended to some stuff, you would see some of following errors. I will give you a list where you have to be aware of.

Tips & Tricks 1:

If you create a new datasource and you type a new SQL-query, ended with a semicolon.

For example: SELECT * FROM dual ;

You will get an error message:

When you click on ‘Error Detail’, it returns: ORA-00911: invalid character.

Solution for this problem is that you remove your semicolon at the end and render your report again.

Tips & Tricks 2:

If you create a new template name without uploading a RTF-template first. This means that there is no RTF-template available under ‘layouts’. After you created a template name, you will upload your RTF-template into BIP.

In the picture below, you can see that drop-down box for ‘template’ is empty.

Overview Report:

You go back to tab ‘Layouts’ and you upload your RTF-template. Without returning to template name, you run your report and you will get following error.

Error message:

Error detail: Attribute missing in : ‘url’

Solution, you have to go back to edit your report. Go to your template name and assign your RTF-template that you have uploaded into BIP.

The reason why he gives this error is simple. You create a new template name but you didn’t assign a RTF-template to it. Because this was uploaded after you create a new template name. BIP will not automatically assign new RTF-template to your template name. That’s why you see this error. You have to do it manually.

Unless, if you first upload your RTF-template and then create a new template name. Now, he will take first RTF-template in the list and assign it to your template name. You change it if you want.

Next two tips & tricks is dependent on which version of BIP you’re working on. It seems that the problems in were solved or better, they have a workaround to avoid these problems.

Tips & Tricks 3:

This first problem I met, is that you always need to select a ‘default data source’ if you create a LOV (List Of Values). So I will explain the situation.

When you create a parameter in BIP, it is also possible to attach an LOV to this item. The LOV can be used to choose values dependent on tables in your schema. So if you create a new LOV, you have to choose explicitly a connection data source like ‘HR’ or something else you defined by JDBC Connection in the Admin tab.

You may not select ‘Default Data Source’ as connection because this will render following error:

Error detail: Parameter name: p_dual Can not establish database connection(null)

The reason why you would see this message is because the LOV will be build before there was a default data source connection. So you have to define explicitly your data source connection in

I noticed that the problem is fixed in and you don’t have to select explicitly your connection but you can select it as ‘Default Data Source’.

Tips & Tricks 4:

This topic will handle about more data sources concatenated. If you create more than one data source and you want them concatenated with unique row names.

A short explanation of what I have done. I created two data sources:

  • Name of 1ste datasource = DS_1
  • Name of 2nd datasource = DS 2

Note: In second data source is no ‘underscore’ presented but a space!

I changed the option to ‘Concatenated SQL Data Source’ as Main Data Set on the Data Model Tab. And checked ‘Make row names unique’.

When you run the report, You will get an error message like this:

Error detail: ‘=’ missing in attribute.

The problem is that it is not allowed to use a space in the data source name. So you can change this one or you can obtain to uncheck ‘Make row names unique’. I always choose for the first solution. Also to avoid problems later by the creation of my RTF-template.

Note: If you use a font that is not recognized on the server (where BIP is installed), BIP will use the default font type of Helvetica.