Problems with length of varchar using Dynamic Schema

Highlighted
Five Stars

Problems with length of varchar using Dynamic Schema

Hey guys, 

 

I'm currently using a Dynamic schema settings into a tPostgresqlInput in which I get the columns that i want from a query, and in the schema I define that it is dynamic. Just like in the picture below.

 

DynamicSchema.PNG

 

What's the problem? When I want to put the results into a tFileOutputDelimited it gets the job done, but when I want to write these results into a tPostgresqlOutput I have the following error, which is an exception of the length for my varchar column that cannot exceed 10485760. I don't know how to put the length of the output columns since they have a dynamic schema.

 

error.PNG

 

 

Has anyone deal with these kind of issues? 

 

 


Accepted Solutions
Five Stars

Re: Problems with length of varchar using Dynamic Schema

Hi again,

 

Sorry for my delay.

 

That didn't work for me Smiley Sad, I put the length limit into the tDBOutput component for the Dynamic output and I still got the error that I mencioned before. 

 

Anyways, I started to work in a different way and I solved it.

 

I'll explain what I've done.

first I've made the query that I wanted in a tDBInput component and in the schema I only added one dynamic output, just like in the picture I've shown on the first message. Then, I loaded the data directly into a tFileOutputDelimited which has no problems to load dynamic columns from a query. 

 

I checked on the file and it has exactly all the columns that I needed. Then I did the following job:

job.PNG

 

Where the tFileInputFullRow reads the first column (headers) of the plain text file that has the dynamic results. Then, in the tJavaRow I made the following:

Query.PNG

 

I created dynamically the query for the creation of the table that I wanted, where I set the length of each column of the table, then I save the query into a context variable and in the tDBrow i just execute the query which results in the creation of the dynamic table. 

 

Finally, I load the data from the dynamic query directly into the table and it has no problem with the lengths of the columns since I defined them before.

 

Thanks for your help  nikhilthampi!! 

 

Saludos! 

 

 


All Replies
Employee

Re: Problems with length of varchar using Dynamic Schema

Hi,

 

    Could you please check what is the maimum limit allowed in the target Postgresql table for varchar column?

 

     It could be because the target table might be having an upper cut off threshold.

 

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 :-)

Five Stars

Re: Problems with length of varchar using Dynamic Schema

Hi,

 

Thanks for the response!

 

I can't check that because I have check the option of "Erase table if exists and create" since this table will be created from scratch every time my process begins. So, I don't know anything about the columns or the maximum limit allowed for the columns. 

I was thinking and I checked that in a previous part of this whole process I created the a plain text with those values, I think I have to work with that file, like reading the headers and creating the table dynamically. 

 

 

 

 

 

Employee

Re: Problems with length of varchar using Dynamic Schema

Hi,

 

   Since you are loading the data to Varchar, set a maximum limit for the Varchar column in the tDBOutput component.

 

   Before passing the data to the DB, use a tMap to measure the length of the data and if it is beyond the allowed maximum limit specified in DB component, reject them and write it to a file for further verification for support team.

 

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 :-)

 

Five Stars

Re: Problems with length of varchar using Dynamic Schema

Hi again,

 

Sorry for my delay.

 

That didn't work for me Smiley Sad, I put the length limit into the tDBOutput component for the Dynamic output and I still got the error that I mencioned before. 

 

Anyways, I started to work in a different way and I solved it.

 

I'll explain what I've done.

first I've made the query that I wanted in a tDBInput component and in the schema I only added one dynamic output, just like in the picture I've shown on the first message. Then, I loaded the data directly into a tFileOutputDelimited which has no problems to load dynamic columns from a query. 

 

I checked on the file and it has exactly all the columns that I needed. Then I did the following job:

job.PNG

 

Where the tFileInputFullRow reads the first column (headers) of the plain text file that has the dynamic results. Then, in the tJavaRow I made the following:

Query.PNG

 

I created dynamically the query for the creation of the table that I wanted, where I set the length of each column of the table, then I save the query into a context variable and in the tDBrow i just execute the query which results in the creation of the dynamic table. 

 

Finally, I load the data from the dynamic query directly into the table and it has no problem with the lengths of the columns since I defined them before.

 

Thanks for your help  nikhilthampi!! 

 

Saludos! 

 

 

Employee

Re: Problems with length of varchar using Dynamic Schema

Good work!

 

Could you please mark your reply as the solution for this post? It will help other Talend community members during their reference.

 

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 :-)

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

How to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud reg...

Learn how to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud registries

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Services Publish to Talend Cloud

Learn how to publish your API Services to Talend Cloud

Watch Now

Cloud Data Warehouse Trends for 2019

Find out aboutCloud Data Warehouse Trends for 2019

Read