DB Table Creator

This Node Is Deprecated — This version of the node has been replaced with a new and improved version. The old version is kept for backwards-compatibility, but for all new workflows we suggest to use the version linked below.

This node allows a user to create a new database table either manually or based on the specification of a KNIME input data table (see "Use dynamic settings" below). The node supports advanced options such as null or not null, primary key or unique keys, default SQL type mapping based on column name or KNIME data type, and default key definitions based on column names.

Tick the "Use dynamic settings" option to enable the dynamic settings behavior. Once enable it allows to dynamically define the SQL type of the columns as well as the assignment of columns to primary or unique keys. All dynamic settings which are made via the "Dynamic Type Settings" and "Dynamic Column Settings" tab are applied to the data table specification of the input data table.
The mappings defined in the Name-Based SQL Type Mapping have a higher priority than the mappings defined in the KNIME-Based SQL Type Mapping. If no mapping is defined in both Name-Based SQL Type mapping and KNIME-Based SQL Type Mapping, then the default mapping is used.
While in dynamic settings mode the "Columns" and "Keys" tab become read-only to allow you a preview of the dynamic settings.

Options

Settings

Schema: The optional schema of the table.
Table name: The name of the table.
Create temporary table: If checked, a temporary table is created. The scope of the created table and time it exists depends on the database specific handling of temporary tables.
Fail if table exists: If checked and the table already exists the node will fail with a database specific error message. Otherwise the node will either execute a CREATE TABLE IF NOT EXISTS statement if supported by the database or issue a query prior creation of the table to check if it already exists.
Use dynamic settings: If checked, the dynamic settings are enabled and the dynamic settings tabs are visible.
The "Columns" and "Keys" tab become read-only to allow you a preview of the dynamic settings.

Columns

While in dynamic settings mode the "Columns" tab becomes read-only to allow you a preview of the dynamic settings.

Column Name: The name of the column.
Column Type: The type of the column. Double-click to edit or add a new SQL type.
Not Null: If checked, the column cannot be null.

Keys

While in dynamic settings mode the "Keys" tab becomes read-only to allow you a preview of the dynamic settings.

Key Name: The name of the key
Key Columns: The columns used to define the key
Primary Key: If checked, the key is a primary key. Otherwise, it is a unique key.

Additional Options

SQL Statment: The SQL statement to append after the regular create table statement e.g. db specific storage information. The statement gets simply appended to the automatically generated create table statement and executed as a single statement.

Dynamic Type Settings

Name-Based SQL Type Mapping

In the Name-Based SQL Type Mapping, a user can define the default SQL type mapping for a set of columns based on the column names. Use the "Add" button to add a new row containing the name pattern of the columns that should be mapped. The name pattern can either be a string with wildcard or a regular expression. The mappings defined in the Name-Based SQL Type Mapping have a higher priority than the mappings defined in the KNIME-Based SQL Type Mapping.

Supported wildcards are * (matches any number of characters) and ? (matches one character) e.g. KNI* would match all strings that start with KNI such as KNIME whereas KNI? would match only strings that start with KNI followed by a fourth character. Double click the "Name Pattern" cell to edit the pattern.

KNIME-Based SQL Type Mapping

In the KNIME-based SQL Type Mapping, a user can define the default SQL type mapping based on KNIME data type. Use the "Add" button to add a new row containing the KNIME data type that should be mapped.

Name Pattern

The name pattern of the columns.

RegEx

Check this option if the search pattern is a regular expression, otherwise it is treated as string with wildcards ('*' and '?').

SQL Type

The SQL type that should be mapped to the columns. Double-click to edit or add a new SQL type.

KNIME Type

The KNIME data type of the columns.

Dynamic Keys Settings

Dynamic Keys Settings: The Dynamic Keys Settings can be used to dynamically define the key definitions based on the column names. Use the "Add" button to add a new row containing the name pattern of the columns that should be used to define a new key. The name pattern can either be a string with wildcard or a regular expression. Take a look at the description of "Name-Based SQL Type Mapping" for examples of supported wildcards. Double click the "Name Pattern" cell to edit the pattern.
Name Pattern: The name pattern of the columns.
RegEx: Check this option if the search pattern is a regular expression, otherwise it is treated as string with wildcards ('*' and '?').
Key Name: The name of the key.
Primary Key: If checked, the key is a primary key. Otherwise, it is a unique key

Input Ports

: DB Connection to connect to the database.
: An optional KNIME data table whose data table specification should be used to create the new database table.

Output Ports

: The input DB Connection including flow variables with the schema and table name of the new created database table.

Popular Predecessors

Popular Successors

Views

This node has no views

Workflows

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.

Installation

To use this node in KNIME, install the extension KNIME Database from the below update site following our NodePit Product and Node Installation Guide:

v5.3

A zipped version of the software site can be downloaded here.

Plugin provider: KNIME AG, Zurich, Switzerland

Plugin version: 5.3.2.v202409010954

On NodePit since: 2024-07-09

Last update: 2024-11-20

Tags: Deprecated

KNIME versions: Since v3.7

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!