Defining a long string variable

Eight Stars

Defining a long string variable

I need to store a long, multi-line string (i.e. containing newlines) and use it later in my job. Putting it in the globalMap makes sense, but what's the best way to get it there? Is there a component that allows me to enter a multi-line string? At the moment I have a tJava that builds it up line with string concatenations and \\n in the string but I'd rather just enter it literally with line breaks in an editor like you can for instance in the Query property of a SQL execution component.

 

The string is a SQL statement, which I will be using as a template to modify and run several variants later on in tHiveRow components.

 

I there a component that lets me enter a multi-line string with line breaks, and writes that string to the global map?

Employee

Re: Defining a long string variable

Hi,

 

   Since you are trying to use an SQL, why don't you remove the line break and store each SQL in single line. It will be easy to manage in long run that way.

 

   Now, a quick work around is to read the data file with a unknown delimiter (in the case of DB, the data will already have new line embedded in input string if you have given that way) and then assign it to a String variable as shown below.

image.png

 

The sample file is as shown below.image.png

 

The data is assigned directly to the variable and printed as shown in tJava component.

image.png

 

image.png

 

The printing of variable displays that any newline inside that variable is retained for further processing.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Eight Stars

Re: Defining a long string variable

I'm moving away from storing the SQL in a file. It is in a file right now, and I'm trying to get rid of the file and have the template SQL defined in the job. And we're using a tFileInputRaw which I guess is like a tFileInputDelimited with no delimiter.

 

The line ends in the SQL aren't really important to me. The important thing is that the SQL be readable and maintainable, and that means line breaks and indentation.

 

Imagine you had to put this SQL into a job:

 

 

select brand_code, count(*) products
  from ~~product_table~~
 group by brand_code
 order by brand_code
having count(*) > ~~threshold~~

You need to do a search and replace on ~~product_table~~ and ~~threshold~~ to insert configurable values later on. So you want to store that SQL in a string in the global map. Here's one way to do it:

 

StringBuilder template_sql = new StringBuilder{};

template_sql.apend("select brand_code, count(*) products\n"); template_sql.apend(" from ~~product_table~~\n"); template_sql.apend(" group by brand_code\n"); template_sql.apend(" order by brand_code\n"); template_sql.apend("having count(*) > ~~threshold~~\n");

globalMap.put("template_sql",template_sql.toString());

It's a bit ugly though. The SQL is not as readable as it was without all the Java syntax around it. What I want is an edit box that I can paste the text into just like the bare SQL, and have that stored in a string that I can use.

 

Here's another way.

globalMap.put("template_slq"
, "select brand_code, count(*) products\n"
+ "  from ~~product_table~~\n"
+ " group by brand_code\n"
+ " order by brand_code\n"
+ "having count(*) > ~~threshold~~\n"
);

That's a little bit tidier, although the reason I used a StringBuilder is I might have a bunch of optional joins that are based on flags so some of the .append calls are inside if statements.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now