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