DB Nodes Table spec

[ajc]

Knimers,

is it possible to get DB query metadata as per the DB table spec tab into a knime table?

[Daniel_Weikert]

I guess you don’t want or can’t simply read the table into KNIME first and then use the default table spec node?
br

[ajc]

yup, as a workaround that’s what I am doing. I limit (filter) to get one 1 row and then use the standard spec node. Two drawbacks with this method:

1. You lose the DB type Name column from the spec.
2. Adds a little extra time to the process as you have to execute the query again to get the spec.

thanks for the suggestion.

[mlauber71]

@ajc a database system typically has some sort of meta data information you can query. In the case of H2 it is INFORMATION_SCHEMA.

H2 java based database example – mlauber71

With big data it can be DESCRIBE (EXTENDED)

Create HIVE table and show the way it was created and describe the structure...

So if you know how to access the structure you can extract the information.

[ajc]

Thanks @mlauber71, in my use case I am looking to get this table spec from a query, not an existing table.

[mlauber71]

What you can do is extract the query from the query (which might be complicated), then materialize it as an empty table and then extract the structure from there. Not very elegant but it might get you the final structure of a sequence of queries. You might have to play around with it.

DB Query Extractor

School of duplicates - and how to deal with them - H2 version – mlauber71

This workflow demonstrates several methods to import one or many CSV file...

I use it with big data tables. I might construct a scaled down example.

[tobias.koetter]

Hello @ajc ,
great idea. I have created a ticket (internal number AP-18075) for a new node that has a DB Data input port and a KNIME table as output which contains the information from the DB Spec tab.
Bye
Tobias