DB Query Reader

Executes an entered SQL query and returns the result as KNIME data table.

Options

Settings

SQL Statement

SQL editor to enter the SQL statement that returns the result data. This node supports complex queries, but may not be used for anything but queries. It can be also used to execute none "SELECT" statements that return any data such as a "DESCRIBE" statement etc.
To open code completion press <CTRL> + <SPACE>. Table and columns names are only suggested if the metadata is available e.g. shown in the Database Metadata Browser. To perform undo press <CTRL> + Z and for re-do <CTRL> + Y.
If the node consumes a lot of memory you might want to decrease the "Fetch size" in the "Advanced" tab of the corresponding connector node. The fetch size specifies how many rows are fetched from the database server into KNIME per network round trip. The higher the number the more memory is consumed. However the smaller the number the more network round trips are necessary to fetch large results. Also not all JDBC driver support this option and thus might ignore it.

Database Metadata Browser

The Database Metadata Browser allows you to browse the database metadata and to select a table or view. Click the Refresh button at the bottom right to re-fetch the metadata from the database. The data is grouped by database schema and type e.g. view or table. By clicking on an entity it shows contained elements. If you click on a table or view the dialog will fetch the column names and types of the selected element. To select a table or view select the name and click OK or double click the element. To search for a particular table or view enter the name or parts of the name into the search field and click on the Magnifying Glass button or press enter.

Flow Variable List

The list contains the flow variables that are currently available at the node input. Double clicking any of the entries will insert the respective identifier at the current cursor position (replacing the selection, if any).

Input Type Mapping

This tab allows you to define rules to map from database types to KNIME types.

Mapping by Name

Columns that match the given name (or regular expression) and database type will be mapped to the specified KNIME type.

Mapping by Type

Columns that match the given database type will be mapped to the specified KNIME type.

Views

This node has no views

Workflows

• [01.CX] Combining Siloed Legacy Data with Running App's Back-End DatabaseKNIME Hub
• 00_xplan sf dbconv-insert via xport-AccountKNIME Hub
• 0009582-6KNIME Hub
• 01_Database_Simple_IO_ExampleKNIME Hub
• 01_Database_Simple_IO_ExampleKNIME Hub
• Show all 465 workflows

Links

• DB Table Selector does not display all tablesby mlauber71 on 2019-10-18

  forum.knime.com/p/72723
• Accessing huge Microsoft Access DBby izaychik63 on 2020-03-05

  forum.knime.com/p/83492
• Failure in Executing Oracle Procedure from DB Query Readerby AnotherFraudUser on 2021-05-02

  forum.knime.com/p/122486
• Extract Date from postgresql databaseby izaychik63 on 2022-12-12

  forum.knime.com/p/182982

Developers