Extension : DB SQL Input with DB SQL Output

KNIME Extensions
1

Hi All,

I would like to create a Knime extension that takes in a input port that is a DB Table Selector and then in the python code executes SQL statement (that generates a SQL table) and then have the output port be a DB Table Selector.

I tried to look for example of extensions, or even the Knime Python APIs for SQL but I have not been able to find it. Can someone help answer the following questions please:

  1. Is it possible to have a “DB Table Selector” as an input port to a python extension?

  2. Is there a KNIME API that allows me to get the SQL Table/Schema name from the “DB Table Selector” input port?

  3. Is there a KNIME API that allows me to execute a SQL query? (I would do a create table using the input port table)

  4. Is there a KNIME API that allows me to have an “DB Table Selector” output port that I would set with the table I created in my query

I have been able to do this with Dataiku because using their Python APIs (e…g, SQLExecutor2, DataSet etc). Does KNIME have the same capabilities?

Thanks
PJ

2

I found the Python Knime API and so I can ask my question more precisely.

The input table according to the docs can be accessed by: knime.scripting.io.input_tables[0]

Can the “input_tables” represent a “DB Data port”?

In which case, what is the API to get “DB Data object” - in particular, I want to get the source SQL table name.

3

I do not think this is possible. You will have to load the data into KNIME or use an SQL driver from within Python (use the new (KNIME 4.6+) Python Script node and bundled Python version to read Parquet file into KNIME, export it again, put it into SQLite database and read it back – KNIME Community Hub).

There is the DB Query Extractor – KNIME Community Hub node where I am currently not sure if you could easily extract the source name also. But I think you could extract a Flow Variable from DB Table Selector – KNIME Community Hub to get the selected schema and table and use that further down the road - see Figure 7:

https://docs.knime.com/latest/analytics_platform_flow_control_guide/index.html#img-flow-variables-tab-dialog

1 Like