[resolved] How to run a .sql file in Talend

Hi,
I have a .sql file which contains my ETL scripts. How can i trigger it directly from Talend.
I can run the statements directly from tMSSqlRow but i want to keep the scripts in a file and want Talend to pick the .sql file and execute it on SqlServer DB.
2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: [resolved] How to run a .sql file in Talend

Hi dnahata_infocepts,
From above solutions, is it useful and helpful for you?
Best regards
Sabrina

Hi,
I was able to achieve this using both the approaches:
1. From command prompt using sqlcmd
2. Reading the Sql File, storing it in a variable and passing the variable to tMSSqlRow
Thanks for your inputs.
Best Regards,
Diwakar
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

This is a long lasting bug in talend indeed. Mostly it helps to delete the file <studio install>/configuration/ComponentCache.javacache and restart studio.
There is a page which explains the install procedure of custom components and some trouble shouting.
https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
23 REPLIES
Moderator

Re: [resolved] How to run a .sql file in Talend

Hi,
Talend studio is a Java code generator, and your problem is similar with "how to execute sql-script file in Java", please refer to:
http://stackoverflow.com/questions/2071682/how-to-execute-sql-script-file-in-java
In Talend, you can write some Java as suggested in this page in tJavacomponent. Another solution can be to read all contents from the script file to a statement and execute it on tMssqlRow component.
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.
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

hi,
perhaps you can try tSystem to exe mysql via command line with something like :
    mysql db_name < script.sql> output.tab

hope it helps
regards
laurent
Moderator

Re: [resolved] How to run a .sql file in Talend

Hi dnahata_infocepts,
From above solutions, is it useful and helpful for you?
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: [resolved] How to run a .sql file in Talend

Hi dnahata_infocepts,
From above solutions, is it useful and helpful for you?
Best regards
Sabrina

Hi,
I was able to achieve this using both the approaches:
1. From command prompt using sqlcmd
2. Reading the Sql File, storing it in a variable and passing the variable to tMSSqlRow
Thanks for your inputs.
Best Regards,
Diwakar
Moderator

Re: [resolved] How to run a .sql file in Talend

Hi,
It is great, thanks for your feedback.
Best regards
Sbrina
--
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: [resolved] How to run a .sql file in Talend

Hi Sabrina,
Sorry to resurrect this thread so feel free to push me to open a new one if you feel so.
Basically I believe Talend is missing an important feature which is to be able to load internal resources. Keeping a sql file related to the project inside the project makes all sense to me. Then able to load that resource could be easily achieved just interacting with the file system once the job is exploded. Talend could even allow classpath:// resource loading meaning providing a directory where custom resources could be added and later on referred from --classpath from the shell/batch script java command invocation.
Does it make sense to you? It would look like a fgeature request.
Thanks,
- Nestor
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

It is possible to do. Please take a look to the component tSQLScriptParser.
I had the same problem and solved it:
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=2,1,1
Moderator

Re: [resolved] How to run a .sql file in Talend

Hi

Basically I believe Talend is missing an important feature which is to be able to load internal resources. Keeping a sql file related to the project inside the project makes all sense to me. Then able to load that resource could be easily achieved just interacting with the file system once the job is exploded. Talend could even allow classpath:// resource loading meaning providing a directory where custom resources could be added and later on referred from --classpath from the shell/batch script java command invocation.


So far, Talend don't have this feature yet. But you can take a look at a custom component developed and shared by jlolling at Talend Exchange portal, see if it addresses your need. Follows the instructions in this page to install a custom component.
Installing a custom component
Hope it will help you.
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: [resolved] How to run a .sql file in Talend

Hi jlolling,
Thanks for the component. How can I use it? I see it has an iterate what would be the syntax to run the statement in a tMSSqlRow component for example?
Thanks,
- Nestor
One Star

Re: [resolved] How to run a .sql file in Talend

Hi Sabrina,
I installed tSQLScriptParser but I can't understand how to access the "sql script" defined in it let us say from a t{DB}Row component. That is the question I was asking jlolling above. In tMySQLRow BTW you can define multiple statement and there is even a ticket opened for extending that support to tSqliteRow ( https://jira.talendforge.org/browse/TDI-20422 ) for example.
So as you can see even after responding my question about how to use tSQLScriptParser there are components like tSqliteRow which will not work for multiple insertions. You can test that with a literal in the component, a tSetGlobalVar or a context parameter/variable. For cases like that it makes sense to go to command line and feed a script containing the sql statements into the sqlite3 command. That technique will work with MSSQL (using sqlcomd) or any other DB because in reality all of the DBs relational or not support running batch script contained in a different file.
Hence it makes sense I believe(going back to my statement) to allow the creation of internal resources. Simply a new entry in Repository view called "Resources" which will be at the same level as Job Designs, Contexts, Code, SQL Templates and Metadata. This will allow the inclusion of any file in the project like a "commands.sql" which very easily could be referenced by internal components if it gets to the classpath or through the use of the current run directory at run time, but more importantly the file will be accessible to external tools that are optimized to run batch sql self contained in a "resource" file. Simply trigger the external import tool from Talend passing as a param the path in the file system for the "resource". The path could even be inferred really, we use standalone deployment of Talend so we invoke the shell script that the "export" creates and we load from a property file the root directory where the jobs are so it would be easy for us to reference any path included in the result of the "export" command.
This is the proposal then:
1. Allow adding a resource file below a "Resources" section from Repository View.
2. Allow export functionality to package the "Resources" section as a directory in the final zip (simply include it as a directory in the exploded export)
With just that feature we will solve the batch processing for all databases including why not any future NoSQL DBs that might not be supported today. For example CouchDB.
Makes sense?
Thanks!
- Nestor
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

It is easy, the iterator iterates over the statements in the script and you get the current statement via the return value e.g. ((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL")).
It is a very common technique in Talend to do this in this way. Please check in Studio the Outline view. A lot of components provides useful return values. Simple open a node in the Outline view to see what return values are available. You can drag& drop from this view!!
You can load the script from a file or put it into the sql editor text widget.
If you need more advanced help, please feel free to contect me at jan.lolling@cimt-ag.de.
It is actually designed for SQL. CouchDB is not an SQL database and need a json based syntax as fare as I know.
The tSQLScriptParser does not execute the statement themself, it need the help of a dedicated database component (typically the tXXXRow components).
One Star

Re: [resolved] How to run a .sql file in Talend

Thanks a lot jlolling,
Very useful component for MSSQL for example.
Unfortunately it won't work for sqlite though as I posted. Not because of the component but the impossibility to run multiple statements with tSqliteRow.
What do you think about the proposal to include a "Resources" directory? Worst case scenario we are planning just to have such directory mainained out of Talend and include it in the exported zip file at the time we release the code. That way we will have it available in the execution directory and then we can run the native tools to run batches.
Thanks again for your help!
- Nestor
One Star

Re: [resolved] How to run a .sql file in Talend

I have created a feature request ( https://jira.talendforge.org/browse/TDI-25384 ) to support inclusion of internal resources.
Thanks!
- Nestor
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

I guess you missed the point of my component. This component is designed for all database (also SQLite) which cannot run a script (=multiple statements at once). It iterates through a script loaded from a file or from the embedded text field. I am pretty sure, it works well for SQLite because the tSqliteRow has to run exactly one statement at the same time.
I think mostly it already exists through the Metadata. In your use case I would put the script code into the text field.
One Star

Re: [resolved] How to run a .sql file in Talend

Thanks for that jlolling!
Indeed it works as you stated. I have posted now about the usage of the component in http://thinkinginsoftware.blogspot.com/2013/04/talend-run-batch-sql-statements-from.html.
The problem is tSqliteRow does not use executeBatch() (and probably other dbs) so for many records it will be a little inefficient.
The feature request I opened will guarantee though that we rely on the efficiency of batch processing from any engine including nosql ones.
Again thank you so so much for this workaround because indeed it solved the original issue and my current issue!
Best regards,
- Nestor
One Star

Re: [resolved] How to run a .sql file in Talend

Hi jlolling,
I was trying to post in Talend Exchange but for some reason I can't. Just to report that the Jar needs to be imported in version 5.3.0 M3 Talend complaints about not finding the har which is indeed included in xml:
<IMPORT NAME="cimt.talendcomp.dbtools" MODULE="cimt.talendcomp.dbtools-1.0.jar" REQUIRED="true" />
And available in the file system:
ovider_5.3.0.M3_r99274/components/tSQLScriptParser
total 96
drwxrwxr-x 2 dev dev 4096 Apr 8 14:38 .
drwxr-xr-x 621 dev dev 20480 Apr 8 14:38 ..
-rw-rw-r-- 1 dev dev 53122 Apr 8 14:38 cimt.talendcomp.dbtools-1.0.jar
-rw-rw-r-- 1 dev dev 2297 Apr 8 14:38 tSQLScriptParser_begin.javajet
-rw-rw-r-- 1 dev dev 554 Apr 8 14:38 tSQLScriptParser_end.javajet
-rw-rw-r-- 1 dev dev 1512 Apr 8 14:38 tSQLScriptParser_icon32.png
-rw-rw-r-- 1 dev dev 2138 Apr 8 14:38 tSQLScriptParser_java.xml
-rw-rw-r-- 1 dev dev 458 Apr 8 14:38 tSQLScriptParser_messages.properties
dev@udesktop2:~$

I have seen this issue with other components before. Basically you need to "help" Talend using the Modules "View" and importing the jar file manually.
Should this be reported to Talend as a bug?
Thanks again,
-Nestor
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

This is a long lasting bug in talend indeed. Mostly it helps to delete the file <studio install>/configuration/ComponentCache.javacache and restart studio.
There is a page which explains the install procedure of custom components and some trouble shouting.
https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
One Star

Re: [resolved] How to run a .sql file in Talend

jlolling, Thanks a lot!
- Nestor
Five Stars

Re: [resolved] How to run a .sql file in Talend

Dear all,
I have been trying to use the tSQLScriptParser but it always output me 'null'.
I wrote all my SQL statements in a text file .sql. Each statement ends with a semicolon.
So I checked "run from script", select the .sql file. Link it to a MSSqlrow and put in the query field the (String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL")). But I end up with a Sql statement cannot be null.
Indeed, after logging the value with a tLogRow, the string value is null. As if the component cannot read/find the Sql statement in the file.
Copying the contents of .sql file into Sqlserver studio IDE works fine. No syntax error.
I have also tried to uncheck the "run from script" and write a simple sql such as "Use myDatabase" directly in the component (like the sample provided) but it fails with the same error.
Has it to do with encoding "utf-8" ? Wrong component version ?
Any idea ?
I am using TOS 5.3 on Win 2008 server and SQLServer 2008 database.
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

This is probably another bug in Talend. Under some circumstances Talend Studio does not create any code for a component and therefore all return values are null and the component does nothing.
To be sure this is the point in your case please do following:
1. Open the job
2. select the tSQLScriptParser component
3. switch to the Code tab (next to the Designer tab at the buttom of your job view)
4. check if you can see any code related to this component like:
				/**
* start
*/
int NB_ITERATE_tWarn_1 = 0; // for statistics
ok_Hash.put("tSQLScriptParser_1", false);
start_Hash
.put("tSQLScriptParser_1", System.currentTimeMillis());
currentComponent = "tSQLScriptParser_1";
int tos_count_tSQLScriptParser_1 = 0;
de.cimt.talendcomp.dbtools.SQLParser tSQLScriptParser_1 = new de.cimt.talendcomp.dbtools.SQLParser();
try {

Especially you should find the creation of the instance tSQLScriptParser_1.
If you do not find this code, please delete the file:
<studio>/configuration/ComponentCache.javacache
and restart your studio.
Five Stars

Re: [resolved] How to run a .sql file in Talend

Dear jlolling,
Thanks for spending time on my issue.
My job is actually missing the following part you mentionned.
int tos_count_tSQLScriptParser_1 = 0;
de.cimt.talendcomp.dbtools.SQLParser tSQLScriptParser_1 = new de.cimt.talendcomp.dbtools.SQLParser();
try {
I presume that missing piece is the SQLParser constructor.
So I performed what you said:
1 - Delete ComponentsCache.javacache file
2- Restart TOS
No luck, it is stil not working.
I have created a new job
tSQLScriptParser --> Iterate --> tJava
tJava component contains this piece of code
System.out.println((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL")) ;
running the job;
Here's the output:
Starting job Test at 14:41 30/08/2013.

connecting to socket on port 4009
connected
null
disconnected
Job Test ended at 14:41 30/08/2013.

I could always use a stored procedure or calling sqlcmd but I would like to avoid those options.
Any other idea to solve this issue ?
Thanks,
Lalaina
Seventeen Stars

Re: [resolved] How to run a .sql file in Talend

Yes thats what I worried about. In which way did you installed this component?
In case you have tried using Talend Exchange view in the Studio please try to install it manually and follow this advise:
https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
In case you have installed it this way, please try to use the Exchange view.
It is a bug in studio but unfortunately I could not figure out the reason and reproduce it for sure. The only thing I know is changing the way of installation mostly solves the problem.
One Star

Re: [resolved] How to run a .sql file in Talend

hello ,
how to get number of rows to be processes .(i.e the  schema is a row description. It defines the number of fields to be processed and passed on to the next component.).i want this schema as a log in a text file .