Inquiry about how to return a result of 'show tables' by using any DB node

Hello.

I was wondering if there is a node could return a result of ‘show tables’ command.
But, the output port cannot be KNIME table data, it should be DB data.
So, I can’t use DB Query Reader node in this case.
I’d appreciate it if you could tell me what a node can do this.

Thanks,
hhkim

Hi @hhkim , is there any reason why categorized your thread under KNIME Server? I’m not seeing the relation with KNIME Server.

The “SHOW TABLES” command is not standard, meaning it’s not supported by every DB Systems.

But based on your comments, you may need to clarify what do you expect to see as result. What “DB data” are you expecting, and what difference do you understand or trying to make between a KNIME table data and DB data?

Any data that you read, regardless its source, be it from a text file, from an Excel file, from a parquet file, from any db table, to name a few example, will be retrieved and stored/presented as a Knime table. So, I’m not understanding what you expectation is.

Thank you for your reply.

On second thought, it would be better to categorize under KNIME Analytics Platform.

And I’m sorry for the confusion about output data format.
I was just wondering if there is a node that can return a result of ‘show tables’ command from DB.

Thanks,
hhkim

Hi @hhkim , thank you for moving the thread from KNIME Server to KNIME AP.

I’m trying to understand why you said that what you are trying to do can’t be done via the DB Query Reader. You should be able to execute any command there, provided that the command is supported by your DB System, and that your user has the proper privileges to execute that command - though I don’t have a set up currently to test SHOW TABLES specifically.

Alternatively, you should be able to query the information schema of the DB (different systems has different table names for this - it would be helpful if you indicated what DB system you are trying to query).

Hi @hhkim I was able to run a test, and I can confirm that running SHOW TABLES via the “DB Query Reader” node works. (Similarly, you can also run SHOW DATABASES)

2 ways to do this for it to work:
1- Specify in the connector the database you want to use
or
2- Run SHOW TABLES FROM your_database_name (for example SHOW TABLES FROM testdb)

The Preview will fail, because of the way Knime encapsulates the command in order to show a limited amount of rows, but it seems like Knime does this only for the preview. If you execute the node, it will run without any issues.

Note: Just wanted to re-iterate what I mentioned before: This works only on systems that support the command SHOW TABLES

4 Likes

Thank you so much!
I used DB Query Reader node and the result was returned :blush:

2 Likes