Hello,
I have a query which is passing to the component as a parameter and I need to fetch the table names in that query .
eg :
select * from tableA inner join table B on col1=col2
I need the output as table A and table B
Can you help us on how to fetch the table names from the query
Hello @Benakesh_yadav,
and welcome to KNIME Community!
Don’t think this is easy task to implement on your own (see here). I suggest to leverage python integration in KNIME and use sql-metadata library to do this. See here:
Br,
Ivan
Hi @Benakesh_yadav , as @ipazin says, there is no “out of the box” solution for this, and python is your best bet.
However, I have a (currently private) component that uses the python sqlparse library and the some additional KNIME processing using Column Expressions and I believe it can achieve what you are wanting to do.
I haven’t released it publicly because it is still experimental, although I’ve been using it on my own projects for the past year.
Let me know if you are able to use python, and can install sqlparse (e.g pip install sqlparse or equivalent), and this is of interest to you, I’ll see if I can put it into a form that is suitable for public releasing.
btw, which version of KNIME are you using? My component is currently using KNIME 5.3.3, but if you need an earlier version, I’ll have to port it backwards as some nodes may have changed.
I have uploaded a demo workflow to the hub:
It uses my SQL Statement Inspector component, which as I mentioned before is experimental, so please don’t rely on it without doing your own testing!
To use this component, you must have Column Expressions and the Python Extension installed, and also have the sqlparse python library installed in your python environment.
https://sqlparse.readthedocs.io/en/latest/
Here is a demo of the component’s output:
There are two outport ports. The upper presents a list of all the “tokens” (component parts) found in the SQL statement, along with the categories that it determines the token belongs to.
The lower port is a list of all table names/view names found in the statement, so your example:
select * from tableA inner join tableB on col1=col2
It returns the following tokens list:
and the tables as follows:
Unfortunately the parsing isn’t particularly fast. I may look into that in future, but for now it at least appears to work albeit relatively slowly.
