DB Table Creator

This node allows a user to create a new database table either manually or based on the data table spec of an 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.
Create table if it does not exist
If checked, the table is only created when it doesn't exist in the database. If the table exists and the option isn't enabled the node will fail with a database specific error message. If supported by the database the node creates a CREATE TABLE IF NOT EXISTS statement otherwise the node executes a query first to check if the table 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

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

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

Icon
A database connection to connect to the database.
Icon
An optional data table whose data table spec should be used to create the new database table.

Output Ports

Icon
The input database connection including flow variables with the schema and table name of the new created database table

Views

This node has no views

Workflows

  • No workflows found

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.