One Star

[resolved] load data from amazon redshift to mysql

Hi all,
I can load 1 million data from tfileinputdelimited to tmysqloutput and also I can load data from tredshiftinput to tfileoutputdelimited via tmap transformation in talend.
But, I cannot able to load data from tredshiftinput to tmysqloutput via tmap in Talend. I am getting error as No operations allowed after statement closed.
I do not know why I am getting this error.
I am using windows 8.1 64 bit OS.
Mysql -Heidisql
Please tell me how to load data??
Please give me a solution.
1 ACCEPTED SOLUTION

Accepted Solutions
Four Stars

Re: [resolved] load data from amazon redshift to mysql

tPreJob-->OnComponentOk-->tMySQLCOnnection
- enable check box for use existing connection in tmysqloutput component
your job goes here
check example here
http://www.talendbyexample.com/talend-tprejob-tpostjob-components.html

Vaibhav
20 REPLIES
Moderator

Re: [resolved] load data from amazon redshift to mysql

Hi,
What's your build version? Could you please upload your job setting screenshots into forum which will be helpful for us to address your issue.
Please take a look at a related forum:http://www.talendforge.org/forum/viewtopic.php?id=36894
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.
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Hi,
Can you remove your tMySQL output component and connect to tLogRow and check if it is working...
What is the commit size for output DB component? try changing it to smaller chunks..
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi Sabrina,
Talend big data-5.6.
Thank you. I Have increased the parameter values in mysql. but still Its showing same error like before.But ,the thing is after 15 minutes 100 rows will load to mysql and then it will show error as follows:
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:463)
at com.mysql.jdbc.PreparedStatement.clearBatch(PreparedStatement.java:1100)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1397)
at project_1.refr_ms_test_0_1.refr_ms_test.tRedshiftInput_1Process(refr_ms_test.java:1368)
at project_1.refr_ms_test_0_1.refr_ms_test.runJobInTOS(refr_ms_test.java:1858)
at project_1.refr_ms_test_0_1.refr_ms_test.main(refr_ms_test.java:1715)
disconnected
Job refr_ms_test ended at 10:17 03/12/2014.

 
Please help me out.
Vinuta
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
I removed tmysqloutputcomponent and replaced with tlogrow. But,still It is not loading.
Commit size: 10000.
Regards,
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Can you use tPreJob-->tMySQLCOnnection flow and enable auto commit in the tMySQLCOnnection  component?
- use existing connection in tmysqloutput component 
and try again
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
But how to use tprejob component and what about tredshiftinput???
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

tPreJob-->OnComponentOk-->tMySQLCOnnection
- enable check box for use existing connection in tmysqloutput component
your job goes here
check example here
http://www.talendbyexample.com/talend-tprejob-tpostjob-components.html

Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
I have done what you have told. The rows loaded successfully.Thank you very much for your help. It took 27 minutes to load. Is it possible to reduce the time and make it load fast???
And one more thing if I want to load approximately 7 to 10 Million rows at time is this the same method or do we have other method??

Vinuta
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
Can you please tell me what was the problem before???
Regards,
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Great !!!
- First step of optimization you can try is to read all the data from the source and put into the flat file
- Read flat file and then insert data into the Tartget database
- Experiment with the batch size parameter
- There is also a concept of bulk load from flat file check with the bulk or ELT component, this will give you much improved results.
What was the problem earlier?
>> Can you show the properties of your earlier output component? there must be some connection issue...
Keep posting your performance improvement progress.. this will help others as well..
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
Can you please tell me more about batch size parameter and bulk load??
Regards,
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

- Batch size is number of rows sent to database for commit... this could be 1k,10k etc
- Bulk load is loading data from flat file directly into the database, this is usually a single component job, where you provide metadata, file path and db details.. this is much faster as compared to other methods.
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
Thank you so much. 
That´s true. If we load data from CSV file to database it takes very less time to load.
Batch size is commit size right which you are explaining previously that I can set those values in output db components in Advanced settings´???
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

You are correct.
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
I loaded data from tRedshiftinput to tmysqloutput using tprejob component. The data loaded successfully. But, when I checked the data in MySql database(Heidisql) has only 999700 rows instead of 1 million rows. totallz 3 rows are missing.
I do not what is the problem??I have not got any errors.
can you please tell me why the rows are missing??
The job design:

Regards,
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Hi Vinuta,
Those records might be rejected from database side... connect reject link to tlogrow/flat file from database output component, you will get the reason for rejection...
Vaibhav
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi, 
I try to connect tfileoutputdelimited component from tmysqloutput component. But, It showing main connection not reject link.
talend version:5.6
What to do??
Regards,
Vinuta
One Star

Re: [resolved] load data from amazon redshift to mysql

Hi,
Sorry for bothering you.
I have got the reject link.
I checked out extend insert in advanced settings. then I got it.
Regards,
Vinuta
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Ok... Good.
So whether the problem is solved or not?
Vaibhav
Four Stars

Re: [resolved] load data from amazon redshift to mysql

Ok... Good.
So whether the problem is solved or not?
Vaibhav