1 ×

Database Connector (legacy)

KNIME Base Nodes version 4.0.1.v201908131444 by KNIME AG, Zurich, Switzerland

This node is part of the legacy database framework. For more information on how to migrate to the new database framework see the migration section of the database documentation.

This node creates a connection to an arbitrary JDBC database. You need to select an appropriate driver (if not available, add your vendor-specific driver under File > Preferences > KNIME > Database Driver) and the JDBC URL of the database (which is database specific). Login credentials can either be provided directly in the configuration or via credentials set on the workflow.

Options

Connection settings

Database Driver
Enter the database driver here; it is a Java class (including the package path). Additional driver can be registered via KNIME's preference page (Database Driver).
Database URL
Enter the database name here, for example jdbc:mysql://host:port/database_name
Workflow Credentials
If checked, the credentials (user and password) are used as defined on the workflow. The credential name links to the specified user and password. Those can be replaced or edit using the 'Workflow Credentials' menu available in the workflow context menu.
User Name
Your log-in user name for the database.
Password
Your log-in password for the database. Note, the password is de- and encrypted before handling it internally.
TimeZone
Select the TimeZone to convert the date, time or timestamp field into. The current implementation can't represent time zones. In order to support persisting those fields into a database, the time values can be changed according to the selected time zone, which is used as the offset (including the daylight saving time) for the original values: No Correction (use UTC) is used for workflows (created before 2.8) and doesn't apply any correction, Use local TimeZone uses the local time zone offset to correct the date field before reading or writing, and TimeZone: allows selecting the time zone to covert the date values into.
Allow spaces in column names
Check this option if the database can handle spaces in column names. Otherwise KNIME will replaces them by underscores.
Validate connection on close
Check this option if you want to validate the connection when closing the dialog.
Retrieve metadata in configure
This option controls subsequent nodes. Usually when a database node is configured it retrieves the metadata of the current table or query from the database for usage in subsequent nodes. If metadata retrieval takes quite some time it will slow down workflow configuration and execution noticeably, especially since metadata is retrieved in both configure and execute. In such cases it's better to switch this option off and only retrieve metadata during execute.

Advanced

Database Type

This option allows you to explicitly define the type of database the node connects to. Depending on the database type the sql dialect, the supported data types and functions such as insert, update and delete as well as the aggregation functions might differ.

The standard option is '<based on database URL>' where the database type is determined automatically based on the first part of the database URL. However some databases such as Hive and Impala share the same url schema but have different SQL dialects as well as offer different functionality e.g. different sets of aggregation functions. For these databases you have to manually select the appropriate type from the list of supported databases.

If your database type is not listed either select the '<based on database URL>' option or the 'default' option, which uses standard sql syntax and basic aggregation functions. The 'default' type is also used if the url is unknown.

You can always verify the database type via the 'Connection' tab of the nodes database out port.

Output Ports

A database JDBC connection

Best Friends (Incoming)

Best Friends (Outgoing)

Workflows

Installation

To use this node in KNIME, install KNIME Base Nodes from the following update site:

KNIME 4.0
Wait a sec! You want to explore and install nodes even faster? We highly recommend our NodePit for KNIME extension for your KNIME Analytics Platform.