tDBInput Component and Schema

[HoS1]

Hi,

this is my first topic here, so i'd possibly put it in the wrong discussion and or i should not report the issue here (but where else?).

I've found an issue in TOS DI 7.2.1 (on macOS) that is extremely annoying and costs me a lot of time. In a job i use a tDBInput component with a query from a mysql table. The select is like so "select colA, colB, colC from myTable". After inspecting the schema, i see that the columns have a different order like so: colC, colA, colB. As far as i could see is that the schema's column order is derived from the create statement of the table, where colC is the first and colB is the last column in the table.

Also (at least in my TOS version), i can not rearrange the columns in the schema by moving them up or down.

What that leads to is that if you use another component like tMysqlOutput with an action on data "update", it could be, that you're updating the wrong data if colA is your key.

This is an extremely dangerous bug i think. So please be sure, that you order your columns in the select statement in the tDBInput component the same way, as the schema "tells" you after you run the query. If you don't pay extremely high attention on this, you could destroy your target data.

I hope you get my point. If not, feel free to ask.

Kind regards, Holger

[rmartin2]

Hi,

It's not a bug, just how it works !

For the list of columns declared in your schema, the builder will generate a select request based on your query.

From your query, it will say "this is the first column, I have to put in the first the first columns of the schema"

and so on...

It will not care how you named your column on your query, it will just put it in the first column, as it's supposed to be.

So if you want to use "perfectly" the metadata, you'll have to change the order. It's like filling data into an array. It's an array, not a HashMap.

My advice : create your query as needed, bring your schema from the metadata, and switch to built-in to arrange order.

Hope it helps !

Sincerely,

[HoS1]

... and exactly that's not working. I cannot arrange the order of the schema (build-in of course). I have to "accept" what talend "thinks" the schema is from the query and have to modify my query accordingly. You're not able to see this in the first place, as the schema and the columns matches to the DBColumns, but the content is wrong. That's dangerous an so it's not correct behavior.

Let me explain again:

1. create a table with colA, colB, colC (in this order)

2. select colC, colA, colB from table in tDBInput and examine the resulting schema

The schema should be colC, colA, colB in this order because that's what the query says. But it is colA, colB, colC, because it's derived from the column order in the table, not from the query. So you have to

3. change the query to select colA, colB, colC from table after you examine the schema in step 2.

If that's the correct behavior of talend, leave this as a hint for other users.

Kind regards, Holger

[rmartin2 · Accepted solution]

The generated schema is generated from your metadata (so your table), not your query.
"Guess schema" does it based on the metadata.
"Guess query" does it based on the metadata.

None of them use que "query" field because it's too complex.
Here's what you could find with a complex request : https://stackoverflow.com/questions/11806528/does-mysql-have-the-equivalent-of-oracles-analytic-func...

It's not true anymore on MySQL 8.0 but all other old MySQL DB may have that kind of query.
It's not possible to parse such things. Not without a huge waste of time in development.

But I agree, it's not straightforward. A tooltip should sure help on this buttons.

(I think your talking about that, correct me if I didn't understood correctly)

[HoS1]

i don't want to execute such complex queries as in the stackoverflow article you mentioned. What i've learned now, is: Whatever query you want to execute, you have to check that the column order in the schema is the order you selected and if not, the only thing you could (must) do, is to rewrite your query. So for me it's OK and i'll accept that.

Kind regards, Holger