Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

We are using Talend 4.2.2r63143 enterprice edition, stuck up with the below Exception.
Exception in component tVerticaOutputBulkExec_1_tVBE
com.vertica.util.PSQLException: Something unusual has occured to cause the driver to fail. Please report this exception.
at com.vertica.Driver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at dev_sam.j_mysql_2_vertica_error_0_1.j_MySQL_2_Vertica_Error.tMysqlInput_1Process(j_MySQL_2_Vertica_Error.java:988)
at dev_sam.j_mysql_2_vertica_error_0_1.j_MySQL_2_Vertica_Error.runJobInTOS(j_MySQL_2_Vertica_Error.java:1268)
at dev_sam.j_mysql_2_vertica_error_0_1.j_MySQL_2_Vertica_Error.main(j_MySQL_2_Vertica_Error.java:1142)
Caused by: java.lang.NullPointerException
at com.vertica.core.v3.QueryExecutorImpl.receiveRFQ(Unknown Source)
at com.vertica.core.v3.QueryExecutorImpl.processResults(Unknown Source)
at com.vertica.core.v3.QueryExecutorImpl.execute(Unknown Source)
at com.vertica.core.v3.ConnectionFactoryImpl.runSetupQuery(Unknown Source)
at com.vertica.core.v3.ConnectionFactoryImpl.runInitialQueries(Unknown Source)
disconnected
at com.vertica.core.v3.ConnectionFactoryImpl.openConnectionImpl(Unknown Source)
at com.vertica.core.ConnectionFactory.openConnection(Unknown Source)
at com.vertica.jdbc2.AbstractJdbc2Connection.<init>(Unknown Source)
at com.vertica.jdbc3.AbstractJdbc3Connection.<init>(Unknown Source)
at com.vertica.jdbc3g.Jdbc3gConnection.<init>(Unknown Source)
at com.vertica.Driver.makeConnection(Unknown Source)
... 6 more
Job j_MySQL_2_Vertica_Error ended at 04:15 10/05/2013.
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
There is a Null Pointer Exception. Could you please give us your work flow and component setting screenshots so that we can address your issue quickly.
Please see the online component reference: tVerticaOutputBulkExec

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

I am trying to connect to Vertica 6, but in the component i am able to see only Version till 4.1. I have attached the Job Screen shot.
The flow is like below
tMySQL=>tMap=>tVerticaOutputBulkExec.
MySQL and vertica DBs are in different Network
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
We are using Talend 4.2.2r63143 enterprice edition

The version 4.2.2 you are using is very old. Vertica 6 is supported since version 5.0, please download the latest version 5.3.0 and test if it works, if you want to upgrade product, please report a ticket in support portal, our colleagues will help you and show you the detailed instructions.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi

Im getting below exception when using vertica component ,the job flow is
tfileinputdelimited--->tmap--->tsort---->tuniqrow---->tvericaoutputbulkexec

Exception in component tVerticaOutputBulkExec_2_tVBE
java.lang.RuntimeException: No record was loaded into component tVerticaOutputBulkExec_2_tVBE
at master_project.ip_bill_cdr_6a_0_1.IP_BILL_CDR_6a.tFileList_1Process(IP_BILL_CDR_6a.java:10707)
at master_project.ip_bill_cdr_6a_0_1.IP_BILL_CDR_6a.runJobInTOS(IP_BILL_CDR_6a.java:11625)
at master_project.ip_bill_cdr_6a_0_1.IP_BILL_CDR_6a.main(IP_BILL_CDR_6a.java:11493)
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
From the error "No record was loaded into component tVerticaOutputBulkExec", it seems there is no processed row in the work flow.
Did you use tlogrow to take a testing in order to see if the data is normal?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi sabrina,

Yes, i checked it with tlogrow the dataflow is normal.
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
That means something wrong with the setting of tVerticaOutputBulkExec. Is it OK for tVerticaOutput?
A trouble screenshot will be helpful.
In addition, while using tVerticaOutputBulkExec, be sure to set the right field delimitor(default is ";") according to the source data file.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi,
I tried with tverticaoutput the data is getting inserted properly ,i dont know why bulk loading is not working im not able to upload images
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
You should register and log in as a Community member first, then you'll get a Image upload box that allows to upload screen captures and images up to 200KB.
For registered users, please click on "post reply" (not through the quick post").
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi ,

For data with row size 1000 i could load it using tverticaoutputbulkexec component ,but when data size is more i am getting such an error. Do you think it is the reason ,is it not possible to load huge data through bulk component
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
Is it same result with tVerticaOutputBulk and tVerticaBulkExec components? I suspect your DB Vertica don't support for Bulk Mode.
Could you please check it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi

row1.START_TSTAMP.equals("")?null:TalendDate.parseDate("MM-dd-yy,HH:mm:ss", row1.START_TSTAMP)
using the above getting no record was loaded into the vertica bulk loader component.
vertica database does not allow null value to be inserted to timestamp field,how to handle this ,what default value should be filled in null part.
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
vertica database does not allow null value to be inserted to timestamp field,how to handle this ,what default value should be filled in null part.

You can give a default value, such as
row1.START_TSTAMP==null || row1.START_TSTAMP.isEmpty())?"TalendDate.parseDate("MM-dd-yy,HH:mm:ss", "default value"):TalendDate.parseDate("MM-dd-yy,HH:mm:ss", row1.START_TSTAMP).
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi
Thx for date handling solution. And what i have observed while loading through tverticaoutputbulkexec component ,if there is any null values for numeric data type the component gives "no record was loaded" error but the same data and file generated through this can be loaded through copy command and tverticaoutput component is able to load same data even though it has null values. why is it so ?
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi,
The tverticaoutputbulkexec component is failing to handle null values if any in the column i am getting "no record was loaded into the component". The tverticaoutput has no such issues with null its inserting successfully . why is it a problem in bulk component?
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi banu,
Sorry for delay.
What talend product build version are you using? We had such similar issue before, however, it had been fixed.
https://jira.talendforge.org/browse/TDI-4750
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi

Im using version 5 (TOS_DI-r99271-V5.2.2)..
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi banu,
I'm going to make a testing and investigation on it. Will come back to you asap.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi sabrina,
Thank u Smiley Happy will be waiting for answer.
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi banu,
Would you mind taking a try your issue on talend 5.3.1 to see if this issue repro?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi sabrina,

okay will try this issue and post , if it exists.
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
I will track your issue. It will be appreciated to give your feedback to us.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi Sabrina,
I have a problem with a compenent (tFileOutputExcel_5). My job Talend, recovers data from two Excel files and puts the result into another excel file. merging the two files is by tmap.
the error is:
Exception in component tFileOutputExcel_5
jxl.write.biff.RowsExceededException: The maximum number of rows permitted on a worksheet been exceeded
at jxl.write.biff.WritableSheetImpl.getRowRecord(WritableSheetImpl.java:1070)
at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:1046)
Can you help me?
Marwa
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
jxl.write.biff.RowsExceededException: The maximum number of rows permitted on a worksheet been exceeded

The exception imply that when loading data to the Excel table, the data exceeds the maximum capacity of excel table.
How much data loaded into excel?
The maximum of Excel table should be 65,536 rows.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

hi sabrina,

okay will try this issue and post , if it exists.

Have you tried it?
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
I'm have run the job which is like this tFileList->tFileInputDelimited->tMap->tSortRow->tUniqRow->tVerticaOutput.
Problem : it is inserting a 18 files rows to vertica table but after that its giving following error.
Exception in component tMap_1
java.lang.NullPointerException
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1380)
at java.text.DateFormat.parse(DateFormat.java:355)
at routines.TalendDate.parseDate(TalendDate.java:816)
at gnh.ip_bill_load_vertica_0_1.IP_BILL_LOAD_VERTICA.tFileList_1Process(IP_BILL_LOAD_VERTICA.java:9036)
at gnh.ip_bill_load_vertica_0_1.IP_BILL_LOAD_VERTICA.runJobInTOS(IP_BILL_LOAD_VERTICA.java:11219)
at gnh.ip_bill_load_vertica_0_1.IP_BILL_LOAD_VERTICA.main(IP_BILL_LOAD_VERTICA.java:11087)
I have checked null value for date in tMap, so im not getting for what reason this error is!! can anybody help please??
Regards,
akki
Moderator

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hi,
Is there any compile error in your job? If so, you should switch "Design" to "Code" in studio to see what's error in java code. Then correct your component setting to fix this issue.
The component setting screenshots will be preferred.
In addition that, it suggest you should open a new topic for issue so that other community users have chance to give a help.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

please how can i increase it to 2000000 rows?
Community Manager

Re: Error - tVerticaOutputBulkExec component - MySQL 2 Vertica migration

Hello mouadrouass
Take a look at the below article to know more details about this Excel limitation:
https://community.talend.com/t5/Design-and-Development/Maximum-rows-permitted-on-a-worksheet-been-ex...
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business