topic Re: Complex SQL query in tDBInput in Talend Studio

Complex SQL query in tDBInput

stephbzr — Fri, 15 Nov 2024 22:17:50 GMT

Hello,

I have input data from a tMap. I need to use some columns (in bold in the query below) to run a SQL query from a tDBInput "Lookup". I have several thousand rows of input so I don't want to use the "Reload at each row" option as it takes too long. I just need to find a method to correctly inject my tMap data into the query and get the result.

The query :

select distinct a.ID_FAM,

into :idFam%, :idAy%, :idOrg1%, :IdProd$

from AY a, FPROD fp, PROD P, ORG O, DCSE D

where a.AY_SS = 'TALEND_COL1'

and a.ID_FAM = fp.ID_FAM

and fp.ID_ORG = a.ID_ORG

and fp.ID_ORG = P.ID_ORG

and fp.ID_PROD = P.ID_PROD

and fp.ID_ORG = O.ID_ORG

and D.DCSE_NOM = 'NOEMIE'

and ((('NOEMIE' <> 'NOEMIE' and D.DCSE_PP = 0) and fp.ID_PROD = 'SANTE') or (('NOEMIE' = 'NOEMIE' or D.DCSE_PP = 1) and (fp.ID_PROD = O.ORG_PROD or P.PROD_PRESTATION_SANTE = 1)))

and fp.ID_FAM = a.ID_FAM

and ((a.AY_DATNAIS = decode('TALEND_COL2', 1, to_date('TALEND_COL2', 'dd/mm/yyyy'), a.AY_DATNAIS)) or (a.AY_DATNAIS2 = 'TALEND_COL2'))

and a.AY_RNG = TALEND_COL3

and exists (select 'a'

from ADC ad

where ad.ID_AY = a.ID_AY

and ad.ID_PROD = fp.ID_PROD

and nvl(ad.id_fprod, fp.id_fprod) = fp.id_fprod

and to_date(TALEND_COL4', 'DD/MM/RR') between ad.ADC_DATDEB and nvl( ad.ADC_DATRAD, sysdate+1))

and to_date('TALEND_COL4', 'DD/MM/RR') between fp.FP_DATADH and nvl( fp.FP_DATRAD, sysdate+1);

A small illustration of what I have started to do :

I moved the conditions requiring input values after the select except for TALEND _COL4 where I wrote the data myself because obviously that's why I need your help.

Re: Complex SQL query in tDBInput

jlolling — Tue, 06 Dec 2022 23:33:57 GMT

Whats wrong with the Reload Each Row option? You can use globalMap values to take care the lookup-select provides fast the expected data by instrumenting the where clause.

And BTW, thousands of rows are not a problem for the tMap if you can limit the columns to the really needed. I have used lookups with 20 millions rows and use Load at once!

Re: Complex SQL query in tDBInput

stephbzr — Tue, 06 Dec 2022 23:45:57 GMT

Hello @Jan Lolling ,

Thank you very much for your feedback. I would like to benefit from it to have the same results as you in terms of speed. It's true that I'm missing out on an interesting option, but when I tested it my processing time is getting much longer. Which scared me. I don't think I'm using it properly so I'm willing to show you how I do it.

For information, the input columns "output_data2" represent about 4000 rows for the file I am processing. Here is an overview of my input columns

It takes 1 minute in total when I don't add globalMap as in the screenshots in my first comment. To increase the performance of the query a bit, I added the distinct after the select and enabled the advanced settings slider. After adding globalMap, it has been 10 minutes since I ran the job and it still doesn't finish...

Re: Complex SQL query in tDBInput

jlolling — Tue, 06 Dec 2022 23:54:41 GMT

One issue is also the tDBInput component for the lookup does not use an external connection. If not the component establish for EACH row a new connection and this slows down a lot.

Take care you put a tDBConnection before the entire subjob with the mentioned tMap.

The next thing is you have to take care the where condition is supported by an index. Your query actually does not allow using an index because the where condition use the given values inside of functions and this prevents using index. I would recommend using a helper table to simplify the select.

E.G. you could collect all repo_datro which will occur in your source and build a temporary table containing all the related records and now you have a very fast table for the lookup.

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 00:01:54 GMT

Thank you for this information, but I admit that I have difficulty in understanding. Or at least to imagine it. If you have a demonstration or an illustration, that would be great. In any case, I will try to think about it.

Re: Complex SQL query in tDBInput

Anonymous — Wed, 07 Dec 2022 02:33:28 GMT

I'm curious, where is this data coming from? Is it all from the same database? If so, why not carry out the join in a single query? A database is built for effective querying of data. If it is all in the same DB, there is no point bringing it out to carry out a join.

If this data is not all in the same DB, then if you do not want to fire a query for every one of your main rows, you can just load all of the data from your sources and join in a tMap. Now, there may be some memory issues doing it this way, but it is worth giving it a try.

I am also confused by your SQL. You have a couple of conditions that do make any sense. For example,.....

('NOEMIE' <> 'NOEMIE' and D.DCSE_PP = 0)

.... will always be false. 'NOEMIE' is always equal to 'NOEMIE'.

Likewise, this will be true all the time....

('NOEMIE' = 'NOEMIE' or D.DCSE_PP = 1)

....since 'NOEMIE' is always equal to 'NOEMIE' and since you are using an "OR" it doesn't matter about the D.DCSE_PP = 1.

These extra conditions that achieve nothing will not help your performance.

Re: Complex SQL query in tDBInput

jlolling — Wed, 07 Dec 2022 07:37:41 GMT

Yes, there are some aliases missing. Actually you are right, why the hack there are output records?

Actually I would expect an SQL error because of the ambiguous columns!

Re: Complex SQL query in tDBInput

jlolling — Wed, 07 Dec 2022 07:45:09 GMT

Reload Each Row option only make sense if you get this way exactly these records related to the incoming records of the main flow. Or you want to have multiple output records based of one input record.

You have to explain your use case a little bit deeper.

I guess the explanation with the tDBConnection is quite clear or not?

I mean add a tDBConnection to the job and use it before you trigger the subjob containing your mentioned tDBInput . Than reference this tDBConnection from your lookup tDBInput. This will prevent the job from establishing every record a new connection.

The other thing with the temp table can only be more concrete if we understand your use case.

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 09:41:58 GMT

Hello @Richard Hall and @Jan Lolling,

The data is in a file that I need to process and integrate into a single database. So in Talend, the process is tJavaFlex --> tMap --> tDBOutput. The columns are extracted from the file with substring() in tJavaFlex. To come back to the complex query, the objective is that using the data (columns), execute it. If this one returns a result (to check that a member exists in the base using its identifier, date of birth... to put in the request) then I define the value of another column.

So then what I'll do is :

So for example with this data in input of the tJavaFlex, the query should return no rows (tested in bdd) and return 1 in the other column.

As described above, to inject the first columns into Talend, I removed the rows from the query to place the relevant fields in front of the SELECT (and defined in the schema). I then assigned the data from my tJavaFlex to each of the schema columns (in tMap).

But as you have understood, I don't know how to proceed to do the same with :

and to_date('18/05/1990', 'DD/MM/RR') between ad.ADC_DATDEB and nvl(ad.ADC_DATRAD, sysdate+1))

and to_date('18/05/1990', 'DD/MM/RR') between fp.FP_DATADH and nvl( fp.FP_DATRAD, sysdate+1)

This is what is currently blocking me. This is the first time I've encountered a query like this. Usually when it's a condition like "col1 = value" then I do as with the first columns of this query. But this is different because it is "col1 between and". I can't take col1 and put it in front of SELECT because otherwise what do I do with "between and"?

I thank you for the remark about the confusing query. I should have left the original one, because 'NOEMIE' is also a data of a column in my file. So I put 'NOEMIE' because I am 100% sure that all my rows have this value and it takes away a difficulty to insert a column from my tMap in my query. But this is not the case for TALEND_COL4 where in this case the dates are variable and not fixed.

Re: Complex SQL query in tDBInput

Anonymous — Wed, 07 Dec 2022 11:12:04 GMT

OK, lets go through this in stages. Can you test this query (it may require a few tweaks) and replace your query with this one? Your query had some unnecessary sections and wasn't efficient. You even had a cartesian join in there. The DCSE table was not joined to anything and no data was being returned from it.

SELECT distinct a.ID_FAM, a.AY_SS, a.AY_DATNAIS, a.AY_DATNAIS2, a.AY_RNG

FROM AY a INNER JOIN FPROD fp ON a.ID_FAM = fp.ID_FAM AND

a.ID_ORG = fp.ID_ORG

INNER JOIN PROD P ON fp.ID_ORG = P.ID_ORG AND

fp.ID_PROD = P.ID_PROD

INNER JOIN ORG O ON fp.ID_ORG = O.ID_ORG

INNER JOIN ADC ad ON a.ID_AY = ad.ID_AY

WHERE a.AY_SS = 'TALEND_COL1' AND

((fp.ID_PROD = 'SANTE') OR (fp.ID_PROD = O.ORG_PROD OR P.PROD_PRESTATION_SANTE = 1)) AND

((a.AY_DATNAIS = decode('TALEND_COL2', 1, to_date('TALEND_COL2', 'dd/mm/yyyy'), a.AY_DATNAIS)) OR (a.AY_DATNAIS2 = 'TALEND_COL2')) AND

a.AY_RNG = TALEND_COL3 AND

ad.ID_PROD = fp.ID_PROD AND

nvl(ad.id_fprod, fp.id_fprod) = fp.id_fprod AND

to_date(TALEND_COL4', 'DD/MM/RR') between ad.ADC_DATDEB and nvl( ad.ADC_DATRAD, sysdate+1)) AND

to_date('TALEND_COL4', 'DD/MM/RR') between fp.FP_DATADH and nvl( fp.FP_DATRAD, sysdate+1);

This should be all you need. Obviously, I haven't been able to test it. So you may need to tweak it, but not by much. This is based on the SQL you showed in your first post. There are some Talend variables in here (I've kept everything named the same), so you will have to modify this with values to test it. This query should be significantly quicker.

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 11:48:37 GMT

Thank you very much @Richard Hall , the SQL query works very well and is more optimised. As expected with these values (4 columns), it returns nothing. But with other values, it can return a row.

So I come back to the main problem, what method can I use to inject the values from the tMap into the SQL query of the tDBInput.

Re: Complex SQL query in tDBInput

Anonymous — Wed, 07 Dec 2022 12:26:18 GMT

If you want to inject values, you HAVE to use the "Reload Each Row" option. Otherwise, how will the values be selected?

However, there is another way. How many rows are returned if you leave out the WHERE clause options which use these injected values? I suspect that you can carry out your lookup by simply loading ALL of the data returned and use the tMap to join to this data in order to filter it down.

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 13:05:10 GMT

Ok @Richard Hall. Is the setting in tMap as below correct?

I used a filter because I need to run the query for only a few rows (about 4000) out of 20000.

Query with WHERE clauses return at most one row. It is about finding a member thanks to the data I inject from the tMap.

Re: Complex SQL query in tDBInput

Anonymous — Wed, 07 Dec 2022 13:19:14 GMT

OK, I think there is some confusion here. How many rows of data are in your main source (the file)? I assume you are saying that there are 20,000 in your lookup and that you are reducing this to 4000 with your filter in the tMap. Out of interest, why aren't you filtering the number of rows from 20,000 to 4000 in your SQL?

If you are only using 4000 rows in your lookup, you may as well just load all of the data once and use the tMap joins to get to the data you need. When you join your Main to your Lookup, it will exclude any data that does not meet that join. So, it will be quicker than firing the query on every row.

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 13:42:35 GMT

@Richard Hall My tMap input data is about 20,000 lines. I just need to run the query on 4000 rows, the one with type 223 or 255.

My result after injecting the values (of more than 4000 rows) as input to the SQL query should be like this:

These are not of course the real data, but the query must return for each line, the member's identifier if it exists otherwise null. I don't usually have problems with tDBInput except for this query.

I usually do it like this, a simple example. The original query :

"SELECT id_org

from org

where org_noc = TALEND_COL1"

In tDBInput :

In tMap

And I have my exit for the 4000 lines

In the current case, this is really difficult because the CLAUSE concerned by the input value is not of the type :

WHERE col1 = TALEND_COL1. But within the SQL query itself...

Re: Complex SQL query in tDBInput

stephbzr — Wed, 07 Dec 2022 14:51:06 GMT

I tested by injecting the data with globalMap (as in my screenshot in the last comment). As expected it works, but the execution time of the job went from 1-2mn to 27mn. This is not surprising as if it loads the SQL query on each line, with 4000 lines it will take a long time.

Re: Complex SQL query in tDBInput

Anonymous — Wed, 07 Dec 2022 15:48:04 GMT

You don't need to inject the values and Reload Each Row. Just use the query without the injected values and join inside the tMap. Let the tMap joins deal with finding the rows that you need. 20,000 rows is not too large for a lookup if it is just a single query. When you do not select Reload Each Row, it loads all of the rows and stores them in memory in the job. Then, when every Main row comes through, they are each matched against the lookup data in memory.