Running a File of SQL Statements with Talend Open Studio

Read in and execute a file of SQL statements, one per line, into a MySQL database using Talend Open Studio

Some ETL tools have a facility for running a block of SQL statements stored in a text file.  To achieve this functionality in Talend Open Studio, combine File and Database components.

Take a text file like this (insert_statements.txt)


INSERT INTO SR_DIM_FUND (FUND_NM, FUND_SOURCE_NM) VALUES ('carl1', 'aaa');
INSERT INTO SR_DIM_FUND (FUND_NM, FUND_SOURCE_NM) VALUES ('carl2', 'bbb');
INSERT INTO SR_DIM_FUND (FUND_NM, FUND_SOURCE_NM) VALUES ('carl3', 'ccc');

I've also tested


ALTER TABLE SR_DIM_FUND MODIFY FUND_LOAD_DT DATETIME NOT NULL

Create a job with a tFileInputDelimited component driving a tMySQLRow. This will need to be mediated by a tFlowToIterate. Each line of SQL is part of a flow that is converted and run as a variable query in tMySQLRow.

The following job is an example

Talend Open Studio: Processing a File of SQL Statements

 

tFileInputDelimited is a component that uses a single string field as a schema. I added this directly to the component, making it a built-in-property with a field "statement".

tFlowToIterate doesn't require any special configuration.

tMySQLRow uses the connection prepared in the opening subjob and uses the following for the query, rather than a hardcoded SQL string based on the schema added by Talend.


((String)globalMap.get("row1.statement"))

Use two subjobs to create and close the MySQL connection: tMySQLConnection and tMySQLClose.

tFileInputDelimited will drive the processing, setting a new "row1.statement" that provides the SQL run by tMySQLRow.