Hi,
I need to generate a list of tables in a database as well as the columns and their data types. Is there a node that can extract that information? I found node from Vernalis but it only seems to work for legacy database connections and possibly only extract list of tables for a schema.
Any ideas if there are nodes that would be able to extract the database metadata?
Hi @Willem,
For what kind of database are you trying to achieve this? Many, if not all, have a dedicated location where this this kind of information is stored.
To illustrate, for a PostgreSQL database I can query:
SELECT *
FROM information_schema.columns
by the means of a DB Query Reader node in KNIME. This returns me a list of all tables with their corresponding column name, data type among many other parameters.
@Willem with H2 databases it would be INFORMATION_SCHEMA.
Often you have a syntax like SHOW TABLES IN <name_of_schema> LIKE …
Thanks for the sugestions so far. My question is, when you connect to a database on the left hand of a DB Query Reader you can get list of database details that include schemas, tables and columns. How can we extract that without having to run a query. Surly the logic is already in the nodes to get that metadata from any database source we connect to.
Hi @Willem , this already exists.
Please take a look at the DB Query Reader below:
You need to click on the Refresh button (highlighted in red). It will retrieve the DB schema and they will be populated in the box that I highlighted in green.
You can then drag columns or tables from the box to your SQL Statement box and Knime will write the columns or tables names for you in the SQL Statement.
EDIT: Sorry @Willem , I misread your comments, so I understand that you already know about what I wrote above.
I’m not aware of any db nodes that does this. You would have to do what @ArjenEX or @mlauber71 have already suggested.
Alternatively, you could also use the Extract Table Spec node after the DB Query Reader node, it would give you the specs of the table that was generated by the DB Query Reader node.
If you look at the DB Table Selector node and you click on the Select a Table button you get the “Database Metadata Browser”
I am looking at getting this information. This would be very handy to create workflows that could check if tables already exists or if columns exists. One example would be to build a workflow that can be used to search for data in columns of a table. I know that different databases store this information, but this information is queried by the database nodes already, so if this metadata could be exported it won’t be needed to have logic to extract this database based on the database type. The information is already there, just need to be able to extract it into a JSON format or table format.
Why don’t want you run the query if I may ask? That happens all the time when you connect and retrieve data
br
Hi @Willem , I think it would be a great feature to have indeed, and to your point, it does look like some existing nodes from Knime is able to get the DB Metadata indeed, so it might be something that Knime could salvage and create a node that can return the schema of a database
Hi @Daniel_Weikert ,
I have a workflow currently used to search for string values in a database which returns list of tables that contains the data. This is useful when you have to figure out where data in a database is located. Would be great if I could get the meta data out as then the workflow can be modified to run independently to which database source you are connected to.
Oh ok so you fetch the list of tables but you also want to have the meta data for each table returned?
Thanks for clarification.
br
That is correct. For what I need to do I need to get the metadata from the source system, schema, table columns names as well as the data types.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
