topic Re: dynamic query from external file in Talend Studio

dynamic query from external file

Anonymous — Wed, 20 May 2020 15:30:30 GMT

Dear Experts,

Please suggestions needed.

I have a requirements say, need to create sql query for each columns which is defined in .txt file.

my .txt file will look like below:

tbl_name1=tbl1

cl1=name

cl2=zipcode

note: columns is not static may be tomorrow new column can be added.

now, what i want to do is:

read the key, value from the file, and store in list may be list..or etc

get the first column. in this case name

and, create a sql query ( select name from tbl1)

next, get the next column, in this case zipcode and create query.......like that need make it dynamic...and if future new columns comes we dont want to edit/change the code, it should form a query.

Re: dynamic query from external file

PhilHibbs — Wed, 20 May 2020 17:38:27 GMT

I don't think the flow link column structure can be dynamic. It has to be compiled into the Java.

What you could do is construct a query that returns an array of values (if your database has an array data type) in a single column. Or, a key field followed by an array of values that can change based on the config file.

Re: dynamic query from external file

Anonymous — Thu, 21 May 2020 05:07:56 GMT

is it possible?

let say i have something in my text file [c1,c2,c3]

convert it as array list

count number of items in array list

and get the first item from the array list -> create a sql query "select AL[0] from tbl1" > pass to tJavaRow and execute

and get the next item from the array list -> create a sql query "select AL[1] from tbl1" > pass to tJavaRow and execute

this process do untill the array list end.

Re: dynamic query from external file

PhilHibbs — Thu, 21 May 2020 20:06:05 GMT

I would do it by constructing a SQL statement that retrieves an array or structure.

If you have this in your file:

c1,c2,c3

then for example the query might look like this:

"SELECT ("+row1.columnlist+") FROM db.tablename"

In HiveQL, for example, that will return a JSON structure like this:

{"col1":"0300-819231","col2":"ababc@abc.com","col3":null}

You could then unpack that JSON using a tExtractJSONFields component.

Other databases might vary in their behaviour.

Alternatively, this query would return an array:

"SELECT ARRAY("+row1.columnlist+") FROM db.tablename"

That would return an array of values rather than a JSON object.

Re: dynamic query from external file

PhilHibbs — Thu, 21 May 2020 20:14:25 GMT

Defending against injection attacks is also a consideration, if you don't absolutely trust the file containing the column list.

https://xkcd.com/327/