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

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

Anonymous — Tue, 19 Feb 2013 14:05:25 GMT

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.

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

Anonymous — Wed, 20 Feb 2013 02:36:58 GMT

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

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

Anonymous — Mon, 25 Feb 2013 15:13:20 GMT

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

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

Anonymous — Tue, 26 Feb 2013 02:18:29 GMT

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

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

Anonymous — Tue, 26 Feb 2013 06:49:04 GMT

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

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

Anonymous — Tue, 26 Feb 2013 07:36:36 GMT

Hi,
It is great, thanks for your feedback.
Best regards
Sbrina

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

nestoru — Tue, 02 Apr 2013 16:43:36 GMT

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

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

Anonymous — Tue, 02 Apr 2013 20:08:09 GMT

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

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

Anonymous — Wed, 03 Apr 2013 03:24:41 GMT

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

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

nestoru — Wed, 03 Apr 2013 05:02:18 GMT

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

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

nestoru — Wed, 03 Apr 2013 13:47:00 GMT

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

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

Anonymous — Wed, 03 Apr 2013 20:54:38 GMT

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).

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

nestoru — Thu, 04 Apr 2013 00:14:39 GMT

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

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

nestoru — Fri, 05 Apr 2013 19:59:52 GMT

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

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

Anonymous — Sat, 06 Apr 2013 14:02:45 GMT

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.

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

nestoru — Sat, 06 Apr 2013 17:03:42 GMT

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

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

nestoru — Mon, 08 Apr 2013 20:00:36 GMT

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

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

Anonymous — Tue, 09 Apr 2013 20:29:00 GMT

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

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

nestoru — Thu, 11 Apr 2013 19:52:35 GMT

jlolling, Thanks a lot!
- Nestor

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

Anonymous — Thu, 29 Aug 2013 22:57:37 GMT

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.