topic Re: Timestamp datatype issue in Talend-Redshift in Talend Studio

Timestamp datatype issue in Talend-Redshift

Anonymous — Wed, 05 Jul 2017 06:15:40 GMT

Hello,

I have a Talend job which should load data CSV from my local drive to Target Database Redshift. I have created an empty table through a DDL query in Redshift UI. But while loading data into the table via Talend Dynamic Schema all rows goes into reject flow which says error "[Amazon][JDBC](10140) Error converting the value to Timestamp".

However when I manually try to insert same values in RedShift UI via Insert query(DML) then data is loaded successfully. Also when I try to keep the action on the table as "Drop table if exists and Create" then same data is loaded successfully but every field has datatype as "varchar" So can anyone please help me to understand why is it complaining about TimeStamp datatype in Talend.

Re: Timestamp datatype issue in Talend-Redshift

vapukov — Wed, 05 Jul 2017 09:16:21 GMT

~~try to change date pattern to proper for Redshift - "yyyy-MM-dd HH:mm:ss"~~

~~if You have in source other format - not sure (need) test Dynamic schema will work or not~~

~~in this case You may need manually convert date by dateParse / dateFormat functions~~

better to start from begin

what current date time format in file?

Re: Timestamp datatype issue in Talend-Redshift

Anonymous — Wed, 05 Jul 2017 14:30:01 GMT

Hello Vapukov,

Datetime format in CSV file is 2017-03-24T23:20:26.000Z . And to cut short my question is it possible to Load data of CSV file into Redshift Table wherein Datatype in the schema is Dynamic. If yes then how? Because currently, I am able to do the same thing in SQL Server without any such issues.

Re: Timestamp datatype issue in Talend-Redshift

Anonymous — Sun, 09 Jul 2017 18:51:54 GMT

Does anyone have any solution?

Re: Timestamp datatype issue in Talend-Redshift

vapukov — Sun, 09 Jul 2017 23:40:23 GMT

the simplest solution - do not use dynamic schema

Re: Timestamp datatype issue in Talend-Redshift

Anonymous — Mon, 10 Jul 2017 13:09:46 GMT

Well if I had to do that then I would not have posted for help on this forum in the first place But anyways I have managed to load data from CSV into redshift DB using Dynamic schema for all datatypes except for fields which have a timezone. It seems Redshift JDBC driver doesn't support timestamptz datatype anymore. So don't know how to load it without driver support.

Re: Timestamp datatype issue in Talend-Redshift

vapukov — Mon, 10 Jul 2017 13:27:36 GMT

it always pain

not only redshift (which is +- postgresql)

but try to check Hortonworks forums about problems with Avro and NiFi ... all same - date error always, int, long require manual edit avro schema and etc

fact simple - all software which positioning it self as "Dynamic" perfect work with String (Varchar and etc)

more or less work with int/long/double

sometime - You spend much more time for try to resolve simple problem - "" is not allowed for INT, than define fixed structure