KNIME 3.6 Database Integrations preview - DB Auto-Binner error

KNIME Extensions
1

Going through the shiny new Database nodes and testing them on Teradata.
The Auto-Binner failed to configure, showing me this in the console:

Failure during query generation. Error: [Teradata Database] [TeraJDBC 15.10.00.35] [Error 3654] [SQLState HY000] Corresponding select-list expressions are incompatible.

I think this has something to do with the data type compatibility, feels like KNIME composed a script with a catch-all NULL clause and Teradata driver is complaining that it is not compatible with numeric data types for other clauses.
But I can’t see the generated SQL in new nodes, looks like it is tested before the node runs, so there is nothing in the KNIME log.

Is there a way to view the generated SQL for the new DB nodes?

1 Like
2

The generated SQL you can see in the output ports. As here (Now with Syntax highlighting!!! :heart: :knime:

image
image978×180 3.49 KB

3

Hi Dimitri,
thanks a lot for testing the new DB nodes on Teradata. Unfortunately if the node is not executing you will not see the SQL query in the outport view.
However you can enable the JDBC logging which will print out a lot of information including all the executed SQL statements into the KNIME log. To enable it open the dialog of the DB Connector node, go to the Advanced tab and enable the JDBC logger (see screenshot)

image
image503×729 15.7 KB

To open the KNIME log open KNIME and got to View->Open KNIME log. Once that is done you can post the problematic SQL query and the corresponding error message her or send it to me via personal message.

While you are on the Advanced page you can also enable the CASE expression and the DROP TABLE statement option in the Dialect capabilities section for Teradata. Especially the CASE support will shorten the generated SQL statements and allows you to bin on several columns at the same time.

Thanks for your help
Tobias

1 Like
4

Thank you, I’ve added the suggested config options and the binner now works fine.

However, the output for large numbers looks like this:

'[0,558,580]'
'(558,580,1,117,160]'
'(1,117,160,1,675,739]'
'(1,675,739,2,234,319]'
'(2,234,319,2,792,898]'

the commas are both bin boundary separators and thousands separators, so it is pretty hard to road

can the middle comma be preplaced with something else that is not ambiguous?

'[0 to 558,580]'
'(558,580 to 1,117,160]'
'(1,117,160 to 1,675,739]'
'(1,675,739 to 2,234,319]'
'(2,234,319 to 2,792,898]'
5

Thank you, Iris, this is an absolutely awesome feature, very handy.
Unfortunately it only works on successfully executed nodes, which is not the case here.

The syntax highlighting is great, but the code is not indented and is hard to read - I have to copy/paste it into my SQL editor to auto-format it to make it more readable.
It would be great if syntax highlighting is complemented with code formating and indenting, or have an extension that does it, e.g. something like this:

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.