Export to Database options

Database parameters

Platform

The database platform type. The following platforms are currently supported:

• SQLite

• Generic ODBC Connection

• Oracle

• MySQL

• SQL Server

• PostgreSQL

• Azure Synapse Analytics

• Impala

• Spark

• Hive

• Teradata

• OpenText Gupta SQLBase

Server

The target database server IP address.

Port

The number of the specific process to connect to.

Database name

The name of the database you want to use (or the database file name if using SQLite).

Authentication

The method of authentication used to access the database.

Possible values are:

• O (User Name/Password)

• 1  (Windows Authentication)

User name

The login user name.

This parameter is mandatory only when User Name/Password has been selected as the authentication mode

Password

The login password.

This parameter is mandatory only when User Name/Password has been selected as the authentication mode.

Schema

The database schema.

Driver

ODBC driver.

Service name

Database service name of the Oracle database.

Bulk size for storing

Retrieves blocks of data of the size specified.

Additional options

Additional parameters which may be required by ODBC drivers (according to database used) can be expressed through this edit box with the following format: option1=value1;option2=value2.

The DSN value can also be inserted here, if present, with the following format: DSN=value.

Data to be exported

Select what type data you want to export from the list of options.

Possible options are:

• Association rules (produced by Hierarchical Basket Analysis and Similar Items Detector tasks)

• Auto regressive models (produced by Auto Regressive task)

• Clusters (produced by Label Clustering, Projection Clustering and Standard Clustering tasks)

• Cluster Labels (produced by Label Clustering and Projection Clustering tasks)

• Datasets (all import, data preparation and data pre-processing tasks)

• Discretization cutoffs (produced by Discretize task)

• Frequent itemsets (produced by Frequent Itemsets Mining task)

• Frequent sequences (produced by Sequence Analysis task)

• Results (produced by all data analysis tasks)

• Rules (produced by LLM tasks (Classification, Regression & One-Class), along with Decision Tree and Regression Tree tasks)

• Models (produced by Logistic, Linear, Neural Networks (Classification and Regression) and SVM (Classification and Regression).

• PCA eigenvectors (produced by the Principal Component Analysis task)

• Relevances (produced by classification and regression tasks).

Table basename

Enter a name for the database table that you want to create.

If no name is specified, the name of the table will correspond to the upstream task in your flow.

Advanced options

Insert mode

Specify how to manage data export operations when there is a data table with the same name already present. If there is a table with the same name, possible behavior can be:

• Insert (with drop) (0) - delete the existing table and recreate it with the data I am exporting

• Insert (append) (1) - add rows to the existing table containing the data I am exporting

• Update (2) - use data I'm exporting to update existing data, based on column matches specified by key attributes

• Insert/Update (3) - if there is a column match update the data in the existing table with the data I am exporting. If there is no match add rows to the existing table with the data I am exporting 

• Delete (4) - the data in the existing table that matches the imported data, according to the specified key attributes, will be deleted.

Add increment primary key

If selected, this option adds a column which contains an incremental number, which can be used as a key attribute, when a table is created. If required you can define a name (autoincname) for the column in the corresponding edit box, otherwise a default name will be provided.

This option is only available with Insert (drop).

If the Add increment primary key option has been selected, the Key attributes edit box is disabled.

Use also missing values in conditions on key variables

If selected, missing values in key attributes in the dataset lead to conditions on NULL.

Basically this means that "missing" is a condition to be met, and not simply the absence of a value.

Likewise, if not selected, any missing values will simply not be considered as conditions for any successive operations performed on the dataset.

Use input rules to determine conditions

If selected, input rules are used to determine query conditions instead of key attribute values. These rules can be manually inserted in a Rule Manager task, which is then connected to the Export to Database task.

This is particularly useful, for example, to define a range of values for a date or a range of continuous values.

This option can be used with all Insert Modes which require conditions (Update, Delete, Insert/Update).

Key attributes

Drag and drop the attributes you want to use as key attributes. This means that conditions for operations such as update or delete, will be applied to these attributes.

If the Add increment primary key option has been selected, this edit box is disabled.

Exported attributes

Drag and drop the attributes to be exported from the Available Attributes list on the left. If left empty, all attributes are exported.