DB Table Creator

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 "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.

To change the input or output ports from a DB Connection to a DB Data port, click on ... in the bottom left corner of the node icon. In the menu go to Exchange ports and select the port to change.

Tick the "Use dynamic settings..." option to enable the dynamic settings behaviour. Once enabled 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 Keys 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, then the default mapping is used.
While in dynamic settings mode the "Columns" and "Keys" tabs become read-only to allow you a preview of the dynamic settings.

Options

Settings

Table Settings
The name of the table that should be created. The database (only visible if multiple database support is enabled) and schema name are optional.
Enable the "Create temporary table" option to create a temporary table. The naming schema e.g., if database and schema name are required as well as the scope of the temporary table and the time it exists depends on the database specific handling of temporary tables.
Existing Table Handling
Provides the following options to choose from:
  • Replace existing table: Removes the database table if it already exists. All previous data will be deleted.
  • Execute without changing existing table: Node executes without changing the existing database table. This is done by either executing a CREATE TABLE IF NOT EXISTS statement, if supported by the database, or issuing a query prior creation of the table to check if it already exists. All previous data will be preserved.
  • Fail during execution if table already exists: The node will fail with a database specific error message if a table with the same name already exists in the database.
Dynamic Settings
If checked, the dynamic settings are enabled, and the dynamic settings tabs are visible. The database table will be created based on the structure e.g., column names and types of the input table.
To enable this option, click on ... in the bottom left corner of the node icon followed by Add KNIME Data Table port. Afterwards, connect the desired table input to the newly added data input port.
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 Statement
The SQL statement to append after the regular create table statement e.g., DB specific storage information. The statement gets simply appended at the end of 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

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

Output Ports

Icon
Input DB Connection that can be changed to DB Data referencing the selected database table.
Icon
The unaltered input KNIME data table to be used in subsequent nodes.

Popular Predecessors

Views

This node has no views

Workflows

Links

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.