Talend: Schema compatibility check

Most of the time when talking about Talend jobs, people think of standard ETL (Extract, Transform, Load). But in some cases there’s the need to check the incoming data before loading them into the target rather than just transforming it. We refer to this process as E-DQ-L (Extract, Data Quality, Load).

One of the things that you might want to check before loading is schema compatibility. For example: you expect to get a String that’s 5 long. If you, for any reason, receive a String that is larger than 5, it will generate an error. Or perhaps you expect a percent (in format BigDecimal like 0.19), but you receive it as a string (“19%”). This example will result into a failing job with an error saying “Type mismatch: cannot convert from dataType to otherDataType”.

Before I continue this blog I would like to emphasize that all the solutions below are possible with the Data Integration version of Talend, except for the last one. The last option requires a Talend Data Quality license.

Let’s create an example case: We want to extract data on a regular basis from a third-party source which we cannot fully trust in terms of schema-settings. We know how many columns we can expect and we have a rough idea of what it contains, but we do not fully trust the source to not give incompatible data. We want to load the records that are valid and we want to separately store the ‘corrupt’ data for logging purposes. I’ve gathered several solutions for this problem:

  1. Use rejected flow on an input-component

One thing you can do is reject the records as soon as you import them. Disable “die on error” on the basic settings tab of you input-component and then right-click it and select “Reject”. The rows will be rejected based on the schema of the file. In the example below we put phone number as an integer and as you can see 1 records is begin rejected. This is because the phone number contains characters and therefore cannot be read as an integer. If you did not disable the “die on error”-option then this component would make the job fail.

reject on input

  1. In case of the target being a database: use rejected links

You can also choose to directly input the data into your database, but to reject any rows that would create an error. You can then create a separate flow to determine what to do with these rejected records.

In your database output component (for example tOracleOutput) change the following:

  • Basic settings: Uncheck “Die on error”
  • Advanced settings: Uncheck “Use batch size”

Now, right-click on your component and select “Row-Reject” and connect it to an output-component. The output you’ll receive will be the rejected rows and what error would have been generated if you tried inserting them, as you can see in the picture below.

rejected rows databank

  1. Use a tFilter-component

You can make the data go through a filter-component before inserting it into your target. You can (manually) decide what’s allowed to go through. This can be useful when your destination is not a database, in which case option 1 is most likely not available.

Schema compatibilty check with tFilterRox

A tFilterRow-component also has the possibility to output the rejected rows, including the reason why they got rejected. You can enable this by right-clicking on your filter and selecting “Row-Reject”. An example of rejected rows by the filter:

rejected rows tFilterRow

Note – You can also use self-defined routines in the tFilterRow-component by checking “Use advanced mode”. This can be useful when you want to check whether or not converting is possible. For example: you could define a routine called “isInterger” that returns true if the conversion is valid and false if it’s impossible.

  1. Use a tSchemaComplianceCheck-component

Another way of making sure that your schema is compatible is by using the tSchemaComplianceCheck-component. Unfortunately, this component is only integrated in the Data Quality version of Talend.

It’s a very easy component to use. The only thing you have to do is connect the incoming data to the tSchemaComplianceCheck-component and then continue its flow to the destination source. You can get the rejected rows the same way as previously (by right clicking on it and then selecting “Row->Reject”).

tSchemaComplianceCheck job

The rejected rows and their error message look like this:

rejected rows tSchemaCompatibilty

That’s it for now. There’s probably a lot of other ways of checking schema compatibility. Feel free to comment if you know any. Thank you for reading!

Change the owner at runtime in Apex

One of our clients needed an application for the follow-up of the production in their plants. The main feature of the application was to show all the data of the current year, but the client also wanted to be able to consult the historical data. Unfortunately the historical data was not in the same schema as the current data.

The two schemas:

  • SCHEMA_A
  • SCHEMA_A_HISTORY

As you could already guess, the second schema contained the historical data. Each year, the old data was moved from SCHEMA_A to SCHEMA_A_HISTORY.

As SCHEMA_A was our parsing schema, we had all the privileges on the tables with the current data, but we didn’t have access to the historical data. We could have created separate screens for the historical data, but we decided to switch owner at runtime when the user wanted to see the historical data.
For this, we also need the select privilege on schema_a_history

In APEX we can do this with just a single line of code:

apex_application.g_flow_owner := 'SCHEMA_A_HISTORY';

This line is best placed under “shared components > security > database session > initialization code”; this way it is fired every time APEX takes up a database session from the session pool:

Knipsel

In older versions of APEX this field was called “Virtual Private Database”.

As you can see in the screenshot, we have used an application item. When the users asks to see the historical data, we fire a process that changes the value of this item from ‘SCHEMA_A’ to ‘SCHEMA_A_HISTORY’.

That’s it, just a quick tip that can save you a lot of time!

Oracle Forms 12c?

Some people wonder whether there will be a 12c version of Oracle Forms or not.

Instead of making guesses, the best way is to ask the people who know best.
So, after getting this question, I asked Michael Ferrante, Oracle Forms Product manager.

Here’s his answer:

Version 12 of Forms and Reports is tentatively planned for releases sometime in CY2015.  At this time, we (Oracle) are not permitted to share exact dates, but I can tell you that developers are hard at work with the new version.  Currently, Forms is in very early stages of its development cycle, but already contains many new features and enhancements to existing features.  This new version, of course will also offer updated certifications against newer Operating Systems, newer Java versions, newer browsers, and newer Oracle database versions.

If you want to stay updated on Oracle Forms, check the following sources on a regular base:

In case you visit Oracle Open World, check the following session:

Oracle Forms sneak peek (Moscone South, room 306 – 9/29/14, 17:15 – 18:00)

New in Java 8 : Consumers and Predicates : a simple introduction

The java.util.function package is new in Java 8 and contains interfaces that are used for lambda expressions and functional references. In this blog, I give a brief introduction of  2 interfaces of this package :

  • Consumer
  • Predicate

For the examples in this blog, we have a list of invoices with name, amount and category :

public class Invoice {
   private String name,amount,category;
   public Invoice(String name,String amount,String category) {
     super;
     this.name=name;
     this.amount=amount;
     this.category=category;
   }
   public String getName() {
     return this.name;
   }
   public String getAmount() {
     return this.amount;
   }
   public String getCategory() {
     return this.category;
   }
}

To generate a list of invoices, we’ll use the following method:

public static List<Invoice> generateInvoices()  {
   List<Invoice> list = new ArrayList<Invoice>();
   list.add(new Invoice("Oracle","1000","SOFTWARE"));
   list.add(new Invoice("Microsof","30000","HARDWARE"));
   list.add(new Invoice("Apple","5000","SOFTWARE"));
}

Consumer

A Consumer is an interface that ‘consumes’ an object. It takes an argument and does something with it. It does not return a result.

The Consumer interface has 2 methods :

  • void accept(T t) : contains the code that is executed on t
  • default Consumer<T> andThen(Consumer<? super T> after) : This method returns a consumer that is executed after the previous one and enables you to execute a chain of consumers.

For this demo, we are using the (new in Java8) method of the Collection API :

Collection.forEach(Consumer<? super T> action)

This method executes the consumer ‘action’ on every item of the collection.

First we create 2 methods that each return a Consumer object. The first will print the name of the invoice, the second prints the amount.

Finally we use these 2 methods in a Collection.foreach method.


public static Consumer<Invoice> printName() {
    return new Consumer<Invoice>() {
         public void accept(Invoice invoice) {
           System.out.println(invoice.getName());
         }
    };
}

public static Consumer<Invoice> printAmount() {
    return new Consumer<Invoice>() {
         public void accept(Invoice invoice) {
           System.out.println(invoice.getAmount());
         }
    };
}

generateInvoices().forEach(printName().andThen(printAmount());

As you can see in the last line, first the printName() is executed, and then the printAmount(). This line will print the following :
Oracle
1000
Microsoft
3000
Apple
5000

When an error occurs in the foreach method, an exception is thrown, and further processing of the List stops.

Predicate

A Predicate is an interface that is used to assign lambda expressions. It has a functional method :

boolean Test(T t )

Predicates are used as stream operations. Stream operations can be executed on Collections in order to execute complex data processing queries. But in this blog we’ll keep it simple, we just want to select all the invoices with category=’HARDWARE’, and put them in a new List.

Using a predicate in combination with the new Streams API, will simplify and shorten our code and make it more readable.

First we define our predicate, and then we’ll use it on our List of invoices. The stream method will filter the List using our predicate, and then collect the items that fulfill the predicate in a new List.

public static Predicate<Invoice> isHardware() {
     return i -> i.getCategory().equals("HARDWARE");
}

List<Invoice> listHardware = generateInvoices().stream.filter(isHardware()).collect(Collectors.<Invoice>toList());

Our new list will now contain 1 invoice, the one from Microsoft which has ‘HARDWARE’ as category.
As you can see, Predicate is a class that contains a function that we can pass to other classes. Actually it is just a reference to a function, AKA ‘a function reference’.
With Streams, you also sort and map data, before collecting, but that’s for another blog.

So that’s it for now. I hope this blog has shown that, by using Consumers and Predicates, our code will become shorter, cleaner and more readable.

 

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!

New in Java 8 : Default and static methods in interfaces

Default method’s  (aka Defender methods) in interfaces are new in Java 8. They enable you to define a default implementation of a method in the interface itself.

If an interface is implemented by several classes, it’s hard to add method’s afterwards, as it will break the code and require all implementing classes to define the method as well. Adding a method to the interface, and defining a default implementation for it, will resolve this problem.

Here’s a code example :


public Interface Draw {

public void drawCircle();

   default public void drawRectangle() {

      System.out.println("draw a rectangle");

   }

}

Implementing classes that have not defined the drawRectangle() method, will print “draw a rectangle” when drawRectangle() is executed on them.

Interfaces that extend this interface can

  • define nothing, in which case the method will be inherited
  • declare the default method again with no implementation, which will make it abstract
  • Redefine the default method so it get’s overridden

These default methods were added to Java in order to be able to implement the new Streams API. As they needed to update the Collection interface, adding the stream() and parallelStream() methods. If they didn’t had the default method, they should have updated all classes that implement the Collection interface.

Static methods

Also new in Java 8 is the use of static method’s in an Interface.

So now, drawRectangle()  could also be defined as a static method, but that would give the impression that it is a utility or helper method, and not part of the essential core interface. So in that case, it’s better to go for the default method.

You could argument that an abstract class would have done the job as well. But as Java has single inheritance, that choice would narrow down our the design possibilities. And as the poster above your bed is shouting every day : ‘Favor composition over inheritance!!’ right ? So we want to avoid inheritance anyway.

So what will happen if you try to implement 2 interfaces with the same default methods ? Well, you will get the following compile time error :

Duplicate default methods named [methodname] with the parameters () and () are inherited from the types [interface1] and [interface2]

To avoid this error, choose an implementation of one of the interfaces :

interface Draw{
   default void circle() {
     System.out.println("draw circle");
   }
}
interface Print{
   default void circle() {
     System.out.println("print circle");
   }
}

class MyClass implements Draw, Print {
   @Override
   public void circle() {
     Draw.super.circle();
   }
}

That’s it, a quick overview of this new feature in Java 8.