Talend: tips and tricks part 3

It has been a while since we posted our Talend Tips and Tricks, but we will be starting a new series to show you different solutions and ideas for both beginners and expert users of Talend, for students, for IT professionals and everyone who’s interested in learning more about data integration

One last thing: we are also sharing all our tips and tricks on channels like Instagram, TikTok, LinkedIn and YouTube. Feel free to join the communities over there and keep up to date on Talend, SQL, Data Integration, Analytics and much more!

In this post:

• • • Talend: Component Connector Line Types
    • Talend/Java: Converting Java Data Types
    • Talend: Copying Schema between Components
    • Talend/SQL: Context Variables in SQL Queries
    • Talend/SQL: “Date” Data Type in SQL Statements

Component Connector Line Types

Talend is a low-code platform where engineers build data flows by setting up components and connecting them with connector lines. This provides an easy way to follow the flow in the design space in your Talend data job. Depending on the size of the job and personal preference you may choose to use straight lines or curved lines to decrease clutter.

Straight Connector Lines

To change your connector line type, go to the settings in the menu at Talend Studio > Preferences. Next, navigate to Talend > Appearance > Designer and untick the ‘use curved connection style for Job Designer’ for Straight Lines.

This option will always draw a straight line between connected components. This will often result in less connections overlapping each other and might make more lines overlap other components in busy jobs.

Curved Connector Lines

Curved style connection is by default ticked in the Talend preferences and results in connector lines only flowing horizontally or vertically with curved corners. This is the default option because it’s much easier to keep the components in your flow free from overlapping lines.

Converting Java Data Types

Talend Studio is essentially a Java code generator so knowing a little Java goes a long way in Talend Job Development. A common transformation in a Talend flow is converting between Data Types and the platform has a component called ‘tConvertType’ to autoCast between all data types. It is always good to understand what kind of conversions are performed in the background.

Below is a table with the most common data type conversions in Java, which the Talend component can also do for you. You can bookmark this page as a reference, for when you’re working on your data integration project.

Copying Schema between Components

A large part of a well-organized data integration project is analyzing and implementing the schema. In Talend we define a schema for each component in a flow, letting us freely transform between schema, column, and data type. While you can sync up schema for connected components and propagate changes, sometimes you want to re-use a schema later in a flow. For more intricate schema it is way faster to copy it over instead of rebuilding it.

First focus on analyzing the use-case of the schema and create it as complete as possible. After you’ve set it up once you can copy it from the component you’ve started at.

 

Then, open the schema of another component in which you want to use the copied schema. Next to the copy schema is the paste button, to paste in the schema you build before and save you a lot of time and effort in recreating it.

Talend / SQL: Context Variables in SQL Queries

Talend has many different database handling components which often use SQL to select, insert or update rows of data. These queries can often be automatically generated based on the schema read from the database but might require some finetuning by hand. Often to create a more loosely coupled solution which can dynamically be managed. An important part is using Context Values in your SQL queries.

Because Talend is a java code generator it parses SQL statements as a string, which means you need to exit the string to add in different variables from the context. Therefore, single and double quotation marks are something to be aware of while editing your SQL in a component.

A query always starts and end with double quotation marks. This ensures that the whole query is in a string format and Talend can parse the statement.

 

In this case the channel number is hardcoded in the query but that is not a dynamic solution. That is why we will use a Context Variable instead, which we can then dynamically load based on the requested data.

The steps to do this are:

• 1. Exit the string with a double quotation mark after the first single mark.
  2. Concatenate the context variable with a plus sign.
  3. Add the second plus sign to concatenate the rest of the query back on.
  4. Enter the string with a double quote and don’t forget to close the single quotes.
  5. Make sure your query still ends with the double quotation marks at the end.

 

Talend / SQL: “Date” Data Type in SQL Statements

A continuation of the previous tip is using a specific data type in a SQL statement, in this case the “Date” data type. As mentioned above the SQL query is in double quotes because it is parsed as a string by Talend and can use context variables. But sometimes a context variable is not string which means it needs some extra handling to be used inside a SQL statement.

We have created a context variable with the “Date” type and want to use it in a SQL query. Because Talend is Java code it will create a Java Date Object for that context variable, not just a string.

 

Talend has built in functions to parse the Java Date Object into a string format and that makes it usable in a SQL statement. It looks like this:

                    TalendDate.formatDate(“example_date_format”, context.variable)

This will parse a Java Date Object and format it to however you need to use it and output it to a string. The double quotes inside the function call won’t enter you back into the string for the SQL query since Talend parses this string for the example format only within the function.

Then you concatenate the string just like in the example before, keeping a close eye on exiting and entering the query string with the double quotation marks.

 

These are the 5 new Data Tips from IntoData. Soon we will post more Data Tips and educational content on our website. You can follow us on Instagram, TikTok, LinkedIn, YouTube and for more information don’t hesitate to contact us!

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