KNIME Documentation

Run a custom SQL query ​

Use the DB Query, DB Query Reader, or DB SQL Executor node when the visual DB nodes do not cover your use case and you need to write SQL directly.

Before you begin ​

Modify an incoming query with DB Query ​

The DB Query node modifies the SQL query from an incoming DB Data connection. Use it to wrap or extend the upstream query with additional SQL.

  1. Connect a DB Query node to a DB Data output port.
  2. Click the node once to open a compact SQL editor, then click the expand icon to open the full configuration.
  3. Write your SQL statement in the editor. The placeholder #table# represents the incoming query and is replaced with the upstream subquery at execution time.
  4. Click Evaluate to test the statement and preview the result under Output preview.
  5. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

TIP

The Input tab lists the columns and flow variables available from the incoming connection. Switch to the Database tab to browse the database schema.

Execute a standalone SQL query with DB Query Reader ​

The DB Query Reader node executes a SQL statement and returns the result as a KNIME data table. It does not wrap the query in a subquery and accepts any statement that returns rows — including DESCRIBE TABLE and other non-SELECT statements.

  1. Connect a DB Query Reader node to a DB Connection output port.
  2. Click the node once to open a compact SQL editor, then click the expand icon to open the full configuration.
  3. Write your SQL statement in the editor.
  4. Click Evaluate to test the statement and preview the result under Output preview.
  5. Optionally, configure Type Mapping to override how database types map to KNIME types. See Type mapping.
  6. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

TIP

Use DB Query Reader instead of chaining DB Query when your database does not support subqueries.

Execute a non-returning SQL statement with DB SQL Executor ​

Use the DB SQL Executor node to run SQL statements that do not return data, such as CREATE, DROP, or ALTER.

  1. Connect a DB SQL Executor node to a DB Connection output port.
  2. Click the node once to open the SQL editor and enter your SQL statement.
  3. Click Apply and Execute to save the configuration and run the node.

Next steps ​