topic Re: Export data from a SQL file to a postgresql database in Talend Studio

Export data from a SQL file to a postgresql database

Anonymous — Sat, 16 Nov 2024 11:51:40 GMT

Hello,
I have a problem ..
I have got a SQL file wich contains some queries in order to insert data in a database (this file is obtained by a dump from phpMyAdmin). Those data come from MySQL database and I have to integrate them to my Postgresql database..
I dont know which component I can use to do that : I have the "tposgresqlrow" but I don't know how to use it.
I'm new in Talend, so if someone can help me ...
Sorry for my English witch it would be very bad..
Thanks

Re: Export data from a SQL file to a postgresql database

Anonymous — Wed, 23 Oct 2013 17:37:56 GMT

I guess you mean with the word query also insert statements.
You can use the component tSQLScriptParser + tPostgresqlRow to do this job.
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=1,1,1
tSQLScriptParser -- iterate --> tPostgresqlRow: use as SQL code only this: ((String) globalMap.get("tSQLScriptParser_1_STATEMENT_SQL"))
This component can read the script from a file or from the input field.
Please keep in mind, the component parse the whole script at once and keeps all statements in the memory.
If you have a dump, this could contains millions of insert statements. To avoid memory space problems I recommend splitting a huge dump file into smaller files with about 1000 statements per file. You should create a job processing one file and another job iterating through all files and calling the one-file-job.

Re: Export data from a SQL file to a postgresql database

Anonymous — Thu, 24 Oct 2013 08:33:24 GMT

Thanks for the quick response!
But I don't have the component tSQLScriptParser in my Palette...
I'm working with Talend Open Studio for Data Integration version 5.3.1

Re: Export data from a SQL file to a postgresql database

Anonymous — Thu, 24 Oct 2013 09:34:40 GMT

Sorry I didn't have understand that the component tSQLScriptParser was a custom component..
I install this component like it's described here: https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
When I intent to use this component, I have an error : "Module cimt.talendcomp.dbtools-1.0.jar required"
So I follow the instructions in this page to install a custom component : Installing a custom component :

If you get a missing jars error, when using the component in a Job, try the following procedure:
Install the component again (without uninstalling the component installed previously).
Delete the file <Talend Studio installation dir>\configuration\ComponentCache.javacache and restart Talend Studio.

But I still have the same error message...
Someone has encountered the same problem for installing a custom component ??
Thanks

Re: Export data from a SQL file to a postgresql database

Anonymous — Thu, 24 Oct 2013 12:33:19 GMT

Finally I succeed to install the JARs file correctly using the Modules "View" and importing the jar file manually..
I don't have the error message "Module cimt.talendcomp.dbtools-1.0.jar required" and when I run the job I have got the exit code 0.
Nevertheless, the job doesn't write in my database and I don't have error messages???
I don't understand why...

Re: Export data from a SQL file to a postgresql database

Anonymous — Fri, 08 Nov 2013 13:27:26 GMT

Hello,
I don't have succeeded to use the tSQLScriptParser component..
When I run my Job in Talend, Idon't have any error messages but any data is inserted in my database..
You can see my Job and the result after running my job in the attachments..
Thanks for the help..

Re: Export data from a SQL file to a postgresql database

Anonymous — Sat, 09 Nov 2013 16:53:10 GMT

Did you set in the Query of the tPostgresqlRow: ((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL"))
This return value contains the current statement.
Please check if the tSQLScriptParser has the number 1, otherwise you have to change the number in the term above.
((String)globalMap.get("tSQLScriptParser_2_STATEMENT_SQL"))
The best way is to drag & drop this return value from the Outline view (open the node of the parser component)

Re: Export data from a SQL file to a postgresql database

_AnonymousUser — Thu, 23 Jan 2014 17:06:56 GMT

Hello
Do you know if it's possible to use this compenent and set some DBMS_MVIEW.REFRESH requests ?
I'd like to use script to refresh automatically some materialized views.
I tried also to use DROP VIEW and CREATE MATERIALIZED VIEW commands (with EXECUTE IMMEDIATE prefix) but it fails too...
Any idea on how to do this ?
thanks