Read a SQL query from a text file and where clause data from another file and pass it to tHiveInput ?

[Anonymous]

I am trying to put all the queries in a text document like below .. 

Filename: table_1_queires.txt: 

select * from tbl_1 where col_1 = "'' and col_2 = ""

(\n)

select * from tbl_1 where col_3 = "" and col_4 = ""

Filename: table_2_queires.txt: 

select * from tbl_2 where col_1 = "'' and col_2 = ""

(\n)

select * from tbl_2 where col_3 = "" and col_4 = ""

and so on .. 

I am planning to design a job as below .. 

tFileInputDelimited -> tFlowtoIterate -> tHiveInput -> tJavaRow -> tLogRow

But, i also want to parameterize values in the where clause as well, which i am not sure how to achieve. 

File: params_data.txt

table_1_queires: 

    col_1 = "1"

    col_2 = "Hello"

    col_3 = "23.12"

    col_4 = "rgt65sd3"

table_2_queires: 

    col_1 = "32"

    col_2 = "Hi"

    col_3 = "2.124"

    col_4 = ""

Can i put one more tFileInputDelimited and read that params data which i want to pass it on the query ? I am not sure where (i.e in which component) should i merge this data .. so that query is formed and passed to tFlowtoIterate and then tHiveInput. 

Please suggest

[Anonymous]

Yes, you can do this. But why would you keep this data in files? Why not a database? With a database you can make it far easier to find the data and enable your requirements with key fields. You can do the same with files, but you have to read the whole file. With a database holding this data, your queries can be targeted to just the relevant rows. 

In order to build your SQL queries, you have to keep in mind that a SQL query in Talend is just a Java String. So you can use Java String manipulation methods to do this. Since you can build routines in Talend, it might make sense to build a couple of methods in a Talend routine (Java Class) to do this in a consistent way.

As a hint, if you are wanting to dynamically set WHERE Clauses, you will have to take into account the data type (Strings need quotes, numbers don't), will you always need to have your columns in the WHERE Clause (some of your examples suggest that the columns in the WHERE Clause will always be set) and what "key" will you want to use to replace when your column does need updating (maybe set a static value to replace, so for col_1 always used "#COL_1#"). 

To solve this you will need to use Java and you will discover issues with basic implementations (some of which I have highlighted above).