DB Table Structure Creator

Options

Database name

The name of the database (or catalogue) to use.

Schema name

The database schema to read the table from. If not provided, the default schema of the database will be used.

Table name

Name of the database table to read data from. Must match the table name as it appears in the selected schema.

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.

If the database table exists

If no table with the given name exists in the database a new table will be created. However if a table with the entered name already exists in the database, one of the following operations is performed depending on the selected option:

• Replace existing table: Overwrites an existing database table. All previous data will be deleted.
• Execute without changing existing table: The node will not change the table.
• Fail during execution: The node will fail if a table with the same name already exists in the database.

Columns

• Column name: The name of the column.
• Column type: The type of the column.
• Not null: If checked, the column cannot be null.

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.

• Name pattern: The name pattern of the columns.
• Pattern type: Choose how the match columns.
  • Regex: Use a regex expressions to select multiple columns.
  • Wildcard: Use a string with wildcard expressions. Supported wildcards are '*' (matches zero or more characters) and '?' (matches exactly one character).
• SQL type: The SQL type that should be mapped to the columns.
• Not null: If checked, the column cannot be null.

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. The mappings defined in the KNIME-Based SQL Type Mapping have a lower priority than the mappings defined in the Name-Based SQL Type Mapping.

• KNIME type: KNIME type the columns should be mapped from.
• SQL type: The SQL type that should be mapped to the columns.
• Not null: If checked, the column cannot be null.

Columns preview

• Column name: The name of the column.
• Column type: The type of the column.
• Not null: If checked, the column cannot be null.

Keys

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

Dynamic keys

The Dynamic Keys Settings can be used to dynamically define the key definitions based on the column names.

• Name pattern: The name pattern of the columns.
• Pattern type: Choose how to match columns.
  • Regex: Use a regex expressions to select multiple columns.
  • Wildcard: Use a string with wildcard expressions. Supported wildcards are '*' (matches zero or more characters) and '?' (matches exactly one character).
• Key name: The name of the key.
• Primary key: If checked, the key is a primary key. Otherwise, it is a unique key.

Keys preview

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

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.

Views

This node has no views

Workflows

• DB SQL - H2 Database, create database and additional tablesKNIME Hub
• How to Modify Database TablesKNIME Hub

Links

• No links available

Developers