topic Re: [resolved] Talend _Vertica_Bulk Load - String _Date handling in Talend Studio

[resolved] Talend _Vertica_Bulk Load - String _Date handling

Anonymous — Mon, 27 Mar 2017 23:47:10 GMT

Hi,
while try to load the below data using tVerticaoutputbulkexec, record 3, with null values for d1 date column is getting rejected.
ID|d1|d2|Val
1|20071231|20071226|dt
2|20070302|20171226|dt
3||20171231|dt3
Output:
[statistics] connected
1|12-31-2007|12-26-2007|8
2|03-02-2007|12-26-2017|8
3||12-31-2017|0
Number of records inserted into table hls_others.test_Date by tVerticaOutputBulkExec_2_tVBE: 2
[statistics] disconnected

Error :
COPY: Input record 3 has been rejected (Invalid date format '' for column 2 (d1).Invalid input syntax for date: ""). Please see /Out/TestDate_Rej.dlm, record 1 for the rejected record. This record was read from STDIN
i am using below logic to handle null but still this record is getting error out
(row2.d1.equals(null)||row2.d1.equals("") ||row2.d1.length()==0)? null :
TalendDate.parseDate("yyyyMMdd",row2.d1)
to avoid this, i am handling null and still it is throwing an error.
Mapping Design

mapping
(row2.d1.equals(null)||row2.d1.equals("") ||row2.d1.length()==0)? null :
TalendDate.parseDate("yyyyMMdd",row2.d1)

Pl Advise how to handle this situation ?

Re: [resolved] Talend _Vertica_Bulk Load - String _Date handling

vapukov — Tue, 28 Mar 2017 00:59:01 GMT

try this:

(row2.d1=null||row2.d1.equals("") ||row2.d1.length()==0)? null :TalendDate.parseDate("MM-dd-yyyy",row2.d1)

You can read function descriptions in tMap
for the TalendDate.parseDate - yyyyMMdd is expected pattern of source string

Re: [resolved] Talend _Vertica_Bulk Load - String _Date handling

Anonymous — Tue, 28 Mar 2017 05:10:04 GMT

the issue is due to null; verticabulkload is using copy table STDIN <path> and if the column Is null then it is getting rejected to error file.
resolved with following workaround.
TalendDate.isDate(row1.d1,"yyyyMMdd")?TalendDate.parseDate("yyyyMMdd",row1.d1):TalendDate.parseDate("yyyyMMdd","99991231")
But i am eager to hear from our community for the solution " how to populate nulls for date columns while using verticabulkloadexe ".

Re: [resolved] Talend _Vertica_Bulk Load - String _Date handling

Anonymous — Tue, 28 Mar 2017 22:24:16 GMT

I was able to load and handle the null values for dates column. Its my understanding issue on the property settings in VerticaBulkExec.

Here is the answer for loading null values for date columns in Vertica using tVerticaBulkExec component.
To check the input column and assign the null values.[ there is many way to check and assign. couple of options is here]
TalendDate.isDate(row1.d2,"yyyyMMdd")?TalendDate.parseDate("yyyyMMdd",row1.d2):null
or row2.d1=null||row2.d1.equals("") ||row2.d1.length()==0)? null :TalendDate.parseDate("MM-dd-yyyy",row2.d1) or
[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] (!Relational.ISNULL(row1.d1) &&
!"".equalsIgnoreCase(row1.d1) )? TalendDate.parseDate("yyyyMMdd",row1.d1): null [/font]

[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] this will give an empty string for null columns. and you should handle this in advanced setting Null String "". [/font]
[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] Default value for this setting is "null"[/font]

Thanks