Four Stars

Talend Redshiftrow Error

Hi ,

 

Can anybody help me,while i am running talend job with copy command its throwing error like.

insufficient data left in message during my COPY from S3 to Redshift.

 

Can anybody help me.

 

 

Regards,

Prasanth

 

7 REPLIES
Eleven Stars

Re: Talend Redshiftrow Error

It is not a Talend error message
it is Redshift (Postgres) 

You can check,  what happens exactly by select data from sql_load_errors:

select 
     d.query, 
     substring(d.filename,14,20), 
     d.line_number as line, 
     substring(d.value,1,16) as value,
     substring(le.err_reason,1,48) as err_reason
from stl_loaderror_detail d, stl_load_errors le
where d.query = le.query and d.query = pg_last_copy_id(); 

https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html

-----------
Four Stars

Re: Talend Redshiftrow Error

Hey Vapukov Thank you for your reply.

 

But,i am able to run same copy command from Redshift and i am able to load data into redshift from S3. Where as using Talend  i am unable to load data into redhsift,throwing error like : [Amazon](500310) Invalid operation: insufficient data left in message;

 

Could you suggest me on this.

 

Regards,

Prasanth

Eleven Stars

Re: Talend Redshiftrow Error

same data? Smiley Wink

Same == prepared by talend, transferred to S3 and then imported by Redshift? or it "similar" data? (which You trust look same ... but we not trust nobody Smiley Happy )

 

 

did You run command from links? what it show?

-----------
Four Stars

Re: Talend Redshiftrow Error

Yes Vapukov.

Same data i used.When i am using copy command which is ran successfully into redshift.while i am trying to load same data using talend,some how it throwing an error on talend.

source file delimiter is '~' .

Also,please find below copy command which i used for redshift load.

 

copy xxxx.xxxxxxxxxxxxxxxxxxxxxxx
from 's3://xxxxxxxxxxxxxx/xxxx.txt'
iam_role 'xxxxxxxxxxxxxxxxxxxxxxxx/xxxxxxx'
ESCAPE ACCEPTANYDATE ACCEPTINVCHARS EMPTYASNULL FILLRECORD IGNOREBLANKLINES IGNOREHEADER 1 TRIMBLANKS dateformat'auto' NULL AS '\0' delimiter '~'

 

Please help me how to run this job through talend.

Thank you.

 

Regards,

Prasanth

 

 

 

 

 

Employee

Re: Talend Redshiftrow Error

May I ask, is this error specific to the Talend AWS Quickstart, or is it a generic error that you are encountering with Redshift and Talend?  If the latter, please post the question to the Design and Development forum.

Four Stars

Re: Talend Redshiftrow Error

Thank you for ypur reply.

 

i Just changed group.

 

Regards,

Prasanth

Four Stars

Re: Talend Redshiftrow Error

Hi,

 

While i am loading data from s3 bucket to redshift using copy command its throwing error as : Extra column(s) found .

 

Could please help me on this.

 

Thank you.